Backup and recovery in MySQL

MySQL backup

The logical backup in MySQL is to back up the data in the database as a text file, and the backed up files can be viewed and edited. In MySQL, use the Mysaldump tool to complete the backup. There are 3 ways to call Mysqldump:

• Backs up the specified database, or some tables in this database.

shell>mysqldump [option] db_name [tables]

• To back up one or more of the databases specified

shell>mysqldump [option]--database DB1 [DB2 DB3 ...]

• Back up all databases

shell>mysqldump [option]--all-database

If you do not specify any tables in the database, all tables in all databases are exported by default. Here are some examples of using the Mysqldump tool for backup (1) Back up all databases:

[Email protected] ~]$ mysqldump-uroot-p--all-database > All.sql

(2) backing up the database test

[Email protected] ~]$ mysqldump-uroot-p Test > Test.sql

(3) Back up the table EMP under the database test

[[email protected] ~]$ mysqldump-uroot-p test emp > Emp.sql

(4) Back up the database test under the table EMP and dept

[[email protected] ~]$ mysqldump-uroot-p Test EMP Dept > emp_dept.sql

(5) Back up the database test all the tables are comma-delimited text, backed up to/tmp:

[[email protected] ~]$ mysqldump-uroot-p-t/tmp test emp--fields-terminated-by ', '

  MySQL recovery the recovery in MySQL can be divided into three types: Full recovery , Point-in-time recovery , and location-based recovery . Full Recoverymysqldump Recovery is also very simple, the backup as input execution, the specific syntax is as follows:

Mysql-uroot-p dbname < Bakfile

Note that after restoring the backup, the data is not complete and the logs executed after the backup need to be redo, with the following syntax:

Mysqlbinlog Binlog-file | Mysql-uroot-p

Point-in-time recoverydue to misoperation, such as mistakenly deleted some tables, then the use of full recovery is useless, because there are still errors in the log operation of the statement, we need to revert to the wrong operation before the state, and then skip the error operation statement, and then recover the statements executed later to complete our recovery. This recovery is called incomplete recovery, and in MySQL, incomplete recoveries are based on point-in-time recovery and location-based recovery, respectively. here are the steps for point-in-time recovery(1) If the error occurred 10 o'clock in the morning, the following statements can be used to restore the backup and Binlog data to the previous failure:

Shell>mysqlbinlog--stop-date= "20150929 9:59:59"/var/log/mysql/bin.123456 | Mysql-uroot-pmypwd

(2) to skip the point in time of failure, continue to perform the subsequent binlog, complete the recovery.

Location-based Recoveryis similar to a point-in-time recovery, but more precise because there may be many SQL statements executing at the same point in time. The steps to recover are as follows: (1) Execute the following command under the shell:

Shell>mysqlbinlog--start-date= "20150929 9:55:00"--stop-date= "20150929 10:05:00"/var/log/mysql/bin.123456 >/ Tmp/mysql_restore.sql

This command will create a small text file in the/tmp directory, edit the file, and locate the position number before and after the error statement, for example, the front and rear position numbers are 565512 and 565515 respectively. (2) After restoring the previous backup file, enter the following from the command line:

Shell>mysqlbinlog--stop-position= "551212"/var/log/mysql/bin.123456 | Mysql-uroot-pmypwdshell>mysqlbinlog--start-position= "551215"/var/log/mysql/bin.123456 | Mysql-uroot-pmypwd

the first line above reverts to all transactions until the stop location. The next line restores all transactions from the given starting position until the end of the binary log. Because the output of Mysqlbinlog includes a set TIMESTAMP statement before each SQL statement is recorded, the recovered data and the associated MySQL log will reflect the original time the transaction was executed.  Do not forget to give a praise Oh! ~

Backup and recovery in MySQL