Detailed explanation of mysql data backup and recovery

This article uses mysql5.5 centos6.5 64-bit (I use rpm to install mysql, and the database installation directory is the default)

1. Precautions for data backup

Read lock problem: Once the database (or a certain table) performs a read lock operation, it will affect the write operation of the database, so the database cannot be written at this time. The reason why the read lock operation is performed during data backup is to ensure the integrity and consistency of the backup data, and it will be unlocked automatically after the data backup is completed.

Update log problem: A new log file is generated every time the data is backed up. In this way, if the database suddenly crashes after a period of backup, the data in that time period can be restored through the bin-log log. . Data recovery steps: restore the backed up data + restore the data in the latest bin-log log

2. Use mysqldump for book backup

mysqldump: command to back up the data in the database into a text file. The structure of the table and the data in the table will be stored in the generated text file.

The working principle of the mysqldump command: It first finds out the structure of the table that needs to be backed up, and then generates a CREATE statement in the text file. Then, convert all records in the table into an INSERT statement. Then through these statements, you can create tables and insert data.

Back up the test database to the /tmp/mysql_back/ directory

 [root@localhost tmp]# mysqldump -uroot -p111111 test -l -F > '/tmp/mysql_back/test.sql';

Parameter interpretation:

1. mysqldump mysql data backup command

2. -uroot -p111111 User name and password

3. test: The name of the database to be backed up

4. -l: Read lock control

5 , -F: Generate a new log file

After executing this statement, you will find that a new bin-log log mysql-bin.000002

6 will be generated in the /var/lib/mysql directory. ,/tmp/mysql_back/test.sql: The location and name of the generated backup file

3. Data recovery

1. Backup data recovery:

　[root@localhost tmp]# mysql -uroot -p111111 test -v -f</tmp/mysql_back/test.sql

　-v: View the detailed information of the import

　-f: If an error is encountered during the import process, you can skip it and continue executing the following statements.

2. Restore the data in the bin-log log:

All records in the mysql-bin.000002 log under /var/lib/mysql

[ root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002 |mysql -uroot -p111111 test;

3. Restore the data at the specified location in the bin-log log

Passed Position position restoration data

BEGIN
/*!*/;
# at 175
#170206 22:55:48 server id 1  end_log_pos 263     Query    thread_id=17    exec_time=0    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1486392948/*!*/;
insert into t1 values(10)
/*!*/;
# at 263
#170206 22:55:48 server id 1  end_log_pos 290     Xid = 178
COMMIT/*!*/;
# at 290
#170206 22:55:54 server id 1  end_log_pos 358     Query    thread_id=17    exec_time=0    error_code=0
SET TIMESTAMP=1486392954/*!*/;
BEGIN
/*!*/;
# at 358
#170206 22:55:54 server id 1  end_log_pos 446     Query    thread_id=17    exec_time=0    error_code=0
SET TIMESTAMP=1486392954/*!*/;
insert into t1 values(12)
/*!*/;
# at 446
#170206 22:55:54 server id 1  end_log_pos 473     Xid = 179
COMMIT/*!*/;
# at 473
#170206 22:56:42 server id 1  end_log_pos 547     Query    thread_id=17    exec_time=0    error_code=0
SET TIMESTAMP=1486393002/*!*/;
truncate t1

Restore the data in the bin-log log with position positions 172 and 473 (end_log_pos)

[root@localhost mysql]# mysqlbinlog --no-defaults mysql -bin.000002 --start-position="175" --stop-position="473"|mysql -uroot -p111111 test;

4. Restore the data of the specified time period in the bin-log log

Format: Replace 3 with --start-position="175" with --start-date="2016-12-30 21:30:34"

--stop-position="473" is replaced with --stop-date="2016-12-30 23:30:34"

The above is the detailed content of Detailed explanation of mysql data backup and recovery. For more information, please follow other related articles on the PHP Chinese website!