Shulou(Shulou.com)06/01 Report--

Although the logical backup of MySQL is often criticized because of defects such as locking tables, in fact, during the downtime window, if the amount of data is not too high, it is quite simple to make a logical backup.

Preparatory work:

1. The target database (backing up data from this database) must first have an account with sufficient permissions (temporary root is fine ~ delete it after use)

two。 The corresponding tools should be installed, such as mydumper

3. If you are ready for the script / command, please rehearse it in the test environment.

When everything is ready, you can wait for the shutdown.

Write down the backup & restore commands here:

Backup:

Click (here) to collapse or open

Nohup mysqldump-h-u-p-triggers-routines-events-set-gtid-purged=OFF-B-d > / home/backup/.sql &

Nohup mydumper-h-P 3306-u-p-t 4-m-B-o / home/backup/mydumper_ & several points to note:

1. Mydumper can not export views, triggers, stored procedures and other objects, so use mysqldump to export the structure of various objects.

2. Mydumper can do multithreaded export, so when importing specific data, mydumper will be much more than mysqldump.

3.-- set-gtid-purged=OFF is mainly applicable to databases with GTID open. If it is not open, remove this option.

4. If you are backing up from the library, remember to add the-- dump-slave parameter to dump the slave status information.

Restore:

Click (here) to collapse or open

Nohup mysql-u-p-h-e "source / home/backup/.sql" &

Nohup myloader-u-p-h-e-B-d / home/backup/mydumper_/ & several points to note:

1. Pay attention to the spaces between myloader commands

2. The-e parameter of myloader writes the SQL of data recovery into binlog, which can be used to synchronize or troubleshooting or other.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.