MySQL backup and recovery

1. MySQL logical backup backs up the database into a file, which facilitates viewing and editing. In MySQLk, use mysqldump to complete logical backup;
1. Back up the specified database or tables in the database;
Mysqldump [options] db_name [tables]
2. Back up a specified database or multiple databases;
Mysqldump [options] -- database DB1 [DB2 DB3. ......]
3. Back up all databases;
Mysqldump [options] -- all-database
Example;
1. Back up all databases:
Mysqldump-uroot-p -- all-database & gt; all. SQL
2. Back up the product database;
Mysqldump-uroot-p product & gt; product. SQL
3. Back up the code table in the product database;
Mysqldump-uroot-p product code & gt; code. SQL
4. Back up the code and list tables in the product database;
Mysqldump-uroot-p product code list & gt; code_list. SQL
......
2. Full recovery;
1. Restore the database;
Mysql-uroot-p dbname <bakfile
2. Redo the backup log;
Mysqlbinlog binlog-file | mysql-u root-p
The complete example is as follows;
1. Back up the database on the first day;
Mysqldump-uroot-p-l-F product> product. SQL
-L: Add a read lock to all tables.-F: generate a new log file.
2. Insert a part of new data on the next day;
3. On the third day, the database suddenly fails and the data cannot be accessed. The backup must be restored;
Mysql-uroot-p product <product. SQL
4. After the restoration is complete, the data is obtained during the first day of backup. In this case, use mysqldump to restore the binlog after mysqldump backup:
Mysqlbinlog localhost-bin.000012 | mysql-uroot-p product
3. Restore Based on Time points;
1. If a misoperation occurs at on the third day, you can use the following statement to recover data from backup and binlog to before the fault:
Mysqlbinlog -- stop-date = "8:59:59"/var/log/mysql/bin.123456 | mysql-uroot-pmypwd
2. Skip the fault point and continue recovery;
Mysqlbinlog -- start-date = "9:01:00"/var/log/mysql/bin.123456 | mysql-uroot-pmypwd
4. location-based recovery;
Recovery is similar to restoration based on time points, but more accurate. Multiple SQL statements may be executed at the same time point. The recovery procedure is as follows;
1. Run the following command in shell;
Mysqlbinlog -- start-date = "8:55:00" -- stop-date = "9:05:00"/var/log/mysql/bin.123456>/tmp/mysql_restore. SQL
At this time, a small text file is created under tmp, edit it, and find the location numbers before and after the error statement, for example, the front and back numbers are 198765 and 198869 respectively;
2. After recovering the previous backup file, run the following command;
Mysqlbinlog -- stop-position = "198765"/var/log/mysql/bin.123456 | mysql-uroot-pmypwd
Mysqlbinlog -- start-position = "198769"/var/log/mysql/bin.123456 | mysql-uroot-pmypwd
Row 1: restores all transactions at the stop position;
Row 2: restores all transactions from the given starting position until the end of the binary log;
**************************************** **************************************** **************
Physical backup and recovery;
Physical backup is faster than logical backup. Physical backup is divided:
1. Cold backup: Stop the MySQL service and cp data. Stop MySQL during recovery. Restore data files at the operating system level and restart the MySQL service, use mysqlbinlog to restore all binlogs since backup.
2. Hot Backup: Different Storage engine methods are also different;
A. MyISAM storage engine adds A read lock to the table to be backed up, and then cp data files to the backup directory. The methods are as follows;
Method 1: Use the mysqlhotcopy tool;
Mysqlhotcopy is a mysql hot backup tool, which is used as follows;
Mysqlhotcopy db_name [/path/to/new_directory]
Method 2: manually lock table copy;
Mysql & gt; flush tables for read;
Then cp data files are stored in the backup directory;
B. InnoDB Storage engine;
The ibbackup tool can hot back up InnoDB Storage engine-type databases, but it charges fees, which is not studied here.