MySQL backup and recovery to ensure data consistency

Hot copy is to back up data using the mysqlhotcopy command when MySQL or other database services are running. This article explains how to ensure data consistency. Now

Hot copy is to back up data using the mysqlhotcopy command when MySQL or other database services are running. This article explains how to ensure data consistency. Now

I. Data Consistency

In the previous article, we mentioned hot copy (MySQL hot copy for backup and recovery), which means that MySQL or other database services use the mysqlhotcopy command for backup when running. This article explains how to ensure data consistency. Now let's assume that there is a situation where we always back up the database in the early morning. We assume that a database exception occurs after the early morning and data loss occurs. In this way, we have backed up the data before the early morning, but the data from the early morning to the abnormal time will be lost (without binlog ). Fortunately, the InnoDB Storage engine supports transactions and Binlog. data generated during the period from the early morning to the time when an exception occurs can be backed up through log files. Therefore, log files are very important and critical. We need to not only back up data, but also back up binary files if conditions permit. Of course, after you back up the data, you can clear the binary file. But for the long term, for example, if the recovered data is not what we want, we need to back up the binary file. Remember that data recovery must be transferred to the test database instead of in the production environment. If there is no problem in the test database, do it in the production environment.

II

3. Data Consistency Simulation

Step 1: verify the data

[Root @ serv01 databackup] # rm-rf * [root @ serv01 databackup] # lsmysql> use larrydb; Database changedmysql> show tables; + ------------------- + | Tables_in_larrydb | + ------------------- + | class | stu | + ------------------- + 2 rows in set (0.00 sec) mysql> select * from class; + ------ + -------- + | cid | cname | + ------ + -------- + | 1 | linux | 2 | Oracle | + ------ + -------- + 2 rows in set (0.00 sec) mysql> select * from stu; + ------ + --------- + ------ + | sid | sname | cid | + ------ + --------- + ------ + | 1 | larry01 | 1 | 2 | larry02 | 2 | + ------ + --------- + ------ + 2 rows in set (0.00 sec)

Step 2: Back up data

[Root @ serv01 databackup] # mysqldump-uroot-p123456 -- database larrydb> larrydb. SQL [root @ serv01 databackup] # ll larrydb. SQL-rw-r --. 1 root 2613 Sep 10 :34 larrydb. SQL

Step 3: Clear logs. Because the logs have been backed up, the previous logs are not needed.

Mysql> show binary logs; + ------------------ + ----------- + | Log_name | File_size | + ------------------ + ----------- + | mysql-bin.000001 | 27320 | mysql-bin.000002 | 1035309 | mysql-bin.000003 | 1010 | mysql-bin.000004 | 22809 | | mysql-bin.000006 | 5659 | mysql-bin.000007 | 126 | mysql-bin.000008 | 10087 | mysql-bin.000009 | 8293 | mysql-bin.000010 | 476 | mysql-bin.000011 | 218 | mysql-bin.000012 | 126 | mysql-bin.000013 | 1113 | | mysql-bin.000014 | 1171 | mysql-bin.000015 | 126 | mysql-bin.000016 | 107 | mysql-bin.000017 | 107 | mysql-bin.000018 | 13085 | + ------------------ + ----------- + 18 rows in set (0.00 sec) mysql> reset master; Query OK, 0 rows affected (0.01 sec) mysql> show binary logs; + ------------------ + ----------- + | Log_name | File_size | + ------------------ + ----------- + | mysql-bin.000001 | 107 | + ------------------ + ----------- + 1 row in set (0.00 sec)

Step 4: update data

Mysql> insert into class values (3, 'devel'); Query OK, 1 row affected (0.01 sec) mysql> update class set cname = "dab" where cid = 2; query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from class; + ------ + ------- + | cid | cname | + ------ + ------- + | 1 | linux | 2 | dab | 3 | Devel | + ------ + ------- + 3 rows in set (0.00 sec) mysql> select * from stu; + ------ + --------- + ------ + | sid | sname | cid | + ------ + --------- + ------ + | 1 | larry01 | 1 | 2 | larry02 | 2 | + ------ + --------- + ------ + 2 rows in set (0.00 sec) mysql> delete from stu where cid = 2; Query OK, 1 row affected (0.00 sec) mysql> update stu set sname = "larry007" where sid = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from stu; + ------ + ---------- + ------ + | sid | sname | cid | + ------ + ---------- + ------ + | 1 | larry007 | 1 | + ------ + ---------- + ------ + 1 row in set (0.00 sec) [root @ serv01 data] # dateTue Sep 10 19:38:24 CST 2013

For more details, please continue to read the highlights on the next page:

Related reading:

Implementation of MySQL backup and recovery

MySQL backup: mylvmbackup introduction and use

Using mysqldump in Linux to back up a MySQL database as an SQL File

Use mysqldump in Linux to regularly back up MySQL Databases

Disk management-LVM