How to recover data and restore data with mysqldump

1. Backup:

• Write a script:

!/bin/bash` echo ‘dump begin‘ Now=$(date +%d-%m-%Y--%H:%M:%S) //获取当前时间 File=backup-$Now.sql.gz //组成文件名 mysqldump -u zc_test -pzc_test2016 -h test.rtdream.com --port 3307 --all-databases  --single-transaction --routines --add-drop-table --quick --default-character-set=utf8  --flush-privileges | gzip > /root/mysql_backup/$File echo ‘dump end‘

• Among the mysqldump parameters:
• -his the hostname to be connected
• -pis the port to connect to
• --all-databasesIndicates that all databases are backed up
• --single-transactionis to send a BEGIN command before dump, if it is not possible to add an error:mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user ‘XXXXXX‘@‘XXXXXX‘ for table ‘XXXXXX‘ when using LOCK TABLES‘
• --routinesInstructions for backup procedures and functions
• --add-drop-tableAdd a drop table in front of each create table
• --default-character-set=utf8When backing up the data encoded with UTF8, it is said that there will be a coding error when the view is not restored
• --flush-privilegesTo back up a database named MySQL with the Refresh permission command
• --quick mysqldumpYou can retrieve and dump table content by row, or you can retrieve the entire contents of a table and buffer the content in memory before dumping. If you are dump a large table, there may be a problem in the buffer. The--quick option forces the contents of the table to be retrieved and dumped on rows without first buffering the content
• | gzipis to compress the content directly.

2. Restore Backup

• command line into MySQL:mysql -u root -p
• ' Source ' Your backup file path ' * (note that the MySQL version of the backup is the same as the original, the original 5.7 is now 5.7, otherwise prone to problems) *

How to recover data and restore data with mysqldump