##

MySQL CASE 1:MySQL Shell Command Line Integration

Today, I met a very interesting thing. A customer's mysql has a data set of 2.5TB, but he wants to back up the database through mysqldump. After several tests, he found that the speed of backup and recovery is very slow. I think everyone familiar with MySQL database knows that mysqldump works in single thread, so the backup speed is very slow. Finally, he came to me for consultation, and I provided him with two methods. The first one is to use xtrabackup, and the second one is to use. After discussion and combining with some existing requirements, mysql shell is finally chosen as the backup and recovery tool. This will also be what I want to talk about. When customers recover data, they want the recovery process to be executed in the background of the system. In the past, when we resumed, we all logged in through mysqlsh and then executed the loadDump command. How can we make the recovery command execute in the background?

We need to use the following command:

[root@mydb01 shellbk]# mysqlsh --mysql -uroot -proot -h localhost -P3306 -- util loadDump /mysql/backup/shellbk &
[1] 7068
[root@mydb01 shellbk]# WARNING: Using a password on the command line interface can be insecure.
Loading DDL and Data from '/mysql/backup/shellbk' using 4 threads.
Opening dump...
Target is MySQL 8.0.34. Dump was produced from MySQL 8.0.34
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
Executing common postamble SQL
100% (37.98 MB / 37.98 MB), 30.38 MB/s, 22 / 22 tables done
Recreating indexes - done
22 chunks (200.00K rows, 37.98 MB) for 22 tables in 2 schemas were loaded in 1 sec (avg throughput 28.61 MB/s)
0 warnings were reported during the load.

[1]+  Done                    mysqlsh --mysql -uroot -proot -h localhost -P3306 -- util loadDump /mysql/backup/shellbk

We can get more cases from official website, which also tells us that we must read the official website document carefully.

REF: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-command-line-integration.html