Although MySQL logical backup often due to lock table and other defects are criticized, in fact, in the downtime window time, if the amount of data is not too high, do a logical backup is quite simple ~
Preparatory work:
1. Target repository (back up data from this library) first to have sufficient permissions to the account (the temporary root can be used to delete the end);
2. The corresponding tool should be installed, such as Mydumper;
3. Prepare the script/command, don't worry about it in the test environment Walkthrough ~
After everything is ready, you can wait for the downtime.
Here's a note for backup & Restore commands:
Backup:
Nohup mysqldump-h
Nohup mydumper-h A few points to note:
1. Mydumper cannot export objects such as views, triggers, stored procedures, etc., so use mysqldump to derive the structure of various objects;
2. Mydumper can do multi-threaded export, so the specific data, Mydumper will be much more than mysqldump;
3.--set-gtid-purged=off is mainly applicable to open the Gtid database, if not open, then this option is removed;
4. If you are backing up from a library, then remember to add the--dump-slave parameter to dump the slave status information;
Recovery:
Nohup mysql-u<user>-p<password>-h
Nohup myloader-u <user>-P <password>-H A few points to note:
1. Myloader between the command to pay attention to the space;
2. The-e parameter of the Myloader writes the SQL in the recovery data into the Binlog, which can be used to synchronize or troubleshoot or other;
More content: http://www.wangzhanjianshegs.com/ website Construction
Logical backup of MySQL (mydumper+mysqldump)