Mysql Backup and restore guaranteed data consistency (5) _mysql

In the previous article we mentioned hot copies (the hot copy of MySQL backup and restore), and hot copies were backed up using the mysqlhotcopy command when MySQL or other database services were running. This article explains how to ensure data consistency. Now suppose there is a situation where we always back up the database in the wee hours, assuming that a database exception occurs after the wee hours, and that data loss is caused. We have already backed up the data before the wee hours, but the data will be lost in the early hours of the morning when the anomaly occurs (without binlog). Fortunately InnoDB storage engine support transactions, but also support Binlog, the early morning to the time of the exception of the data can be backed up through the log file. Therefore, the log file is very important and very critical. We backup not only to back up the data, but also to backup the binaries if conditions permit. Of course, after you've backed up the data, you can empty the binaries, but if you're looking for a longer term, like the recovered data isn't what we want, we need to back up the binaries. It is also important to remember that recovery data needs to be transferred to a test database and not done in a production environment. There is no problem in testing the test library, and then do it in the production environment.
schematic

Ensure data consistency simulation
The first step is to validate the data

[Root@serv01 databackup]# RM-RF *
[root@serv01 databackup]# ls

mysql> use larrydb;
Database changed
mysql> 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 two, back up your data

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

The third step is to empty the log because a backup has been made, so you do not need the previous log

 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.000005 | 9860 | | 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 |
+------------------+-----------+ 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 Fourth, 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> deletes 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]# date Tue Sep 10 19:38:24 CST 2013

 

Step fifth, simulate data loss, delete library

[Root@serv01 data]# rm-rf/usr/local/mysql/data/larrydb/mysql> show databases; +--------------------+
|
Database | +--------------------+
| Information_schema | | Game | | Hello | | mnt | | MySQL | | Performance_schema | |
Test | +--------------------+ 7 rows in Set (0.00 sec) [Root@serv01 data]# cd/usr/local/mysql/data/[root@serv01 data]# ll Tota L 28736 drwx------. 2 mysql mysql 4096 Sep 19:14 game drwx------. 2 MySQL mysql 4096 Sep 7 00:43 hello-rw-rw----. 1 mysql mysql 18874368 Sep 19:36 ibdata1-rw-rw----. 1 mysql mysql 5242880 Sep 19:36 ib_logfile0-rw-rw----. 1 mysql mysql 5242880 Sep 4 23:39 ib_logfile1 drwxr-xr-x. 2 mysql mysql 4096 Sep 18:35 mnt drwxr-xr-x. 2 mysql mysql 4096 Sep 4 23:39 mysql-rw-rw----. 1 MySQL mysql 998 Sep 19:37 mysql-bin.000001-rw-rw----. 1 MySQL 19:34 mysql-bin.index drwx------. 2 mysql mysql 4096 Sep 4 23:39 performance_schema-rw-r-----. 1 mysql root 26371 Sep 19:30 serv01.host.com.err-rw-rw----. 1 mYsql MySQL 5 Sep 18:36 serv01.host.com.pid drwx------. 2 MySQL mysql 4096 Sep 7 00:13 test #可以使用mysqlbinlog命令查看日志文件 [root@serv01 data]# mysqlbinlog mysql-bin.000001
show databases; +--------------------+
|
Database | +--------------------+
| Information_schema | | Game | | Hello | | mnt | | MySQL | | Performance_schema | |
Test |
+--------------------+ 7 rows in Set (0.00 sec) mysql> drop database larrydb;

 Query OK, 0 rows affected (0.01 sec)

Step sixth, import the data before the update

[Root@serv01 databackup]# mysql-uroot-p123456 < larrydb.sql 
ERROR 1050 (42S01) at line 33:table ' larrydb '. ' Clas S ' already exists
[root@serv01 databackup]# mysql-uroot-p123456 < larrydb.sql mysql> use 

larrydb;
Database changed
mysql> select * from Stu;
+------+---------+------+
| sid | sname | CID
| +------+---------+------+
| 1 | larry01 | 1 |
| 2 | larry02 | 2 |
+------+---------+------+
2 rows in Set (0.00 sec)

mysql> select * from class;
+------+--------+
| cid | cname |
+------+--------+
| 1 | linux |
| 2 | Oracle |
+------+--------+
2 rows in Set (0.00 sec)

Step seventh, recover data from the log

[Root@serv01 data]# mysqlbinlog--stop-datetime "2013-09-10 19:37:45" mysql-bin.000001 |
mysql-uroot-p123456 mysql> select * from Stu; +------+---------+------+
| Sid | sname |
CID | +------+---------+------+
| 1 | Larry01 |
1 |
+------+---------+------+ 1 row in Set (0.00 sec) mysql> SELECT * from class; +------+-------+
| CID |
CNAME | +------+-------+
| 1 | Linux | | 2 | Dab | | 3 |
Devel |
+------+-------+ 3 rows in Set (0.00 sec) #规律: The Point-in-time of recovery (or the point after the commit) is the point at which the accident occurred plus a second. [Root@serv01 data]# mysqlbinlog--stop-datetime "2013-09-10 19:37:46" mysql-bin.000001 |
mysql-uroot-p123456 mysql> select * from Stu; +------+----------+------+
| Sid | sname |
CID | +------+----------+------+
| 1 | larry007 |
1 |
+------+----------+------+ 1 row in Set (0.00 sec) mysql> SELECT * from class; +------+-------+
| CID |
CNAME | +------+-------+
| 1 | Linux | | 2 | Dab | | 3 | Devel | | 3 |
Devel | +------+-------+ 4 rows in Set (0.00 sec) [Root@serv01 data]# Mysqlbinlog mysql-bin.000001 # at 7131 #130910 19:37:45 server ID 1 end_log_pos 7240 Query thread_id=20 exec_time=996 error_code=0 SET TI
mestamp=1378813065/*!*/;
Update Stu Set sname= "larry007" where sid=1/*!*/; # at 7240 #130910 19:37:45 server ID 1 end_log_pos 7312 Query thread_id=20 exec_time=996 error_code=0 SET timestamp=137881
3065/*!*/;
COMMIT/*!*/;
DELIMITER;
# End of log file ROLLBACK/* Added by Mysqlbinlog * *;
 /*!50003 SET completion_type= @OLD_COMPLETION_TYPE * *;

The above is the entire content of this article, do not know whether we have harvested, contact the content of the previous several to understand, learning effect will be better OH