Home>

schematic diagram

Guaranteed data consistency simulation

The first step is to verify the data

[[email protected] databackup] #rm -rf *
[[email protected] 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)

The second step is to back up the data

[[email protected] databackup] #mysqldump -uroot -p123456 --database larrydb>larrydb.sql
[[email protected] databackup] #ll larrydb.sql
-rw-r--r--. 1 root root 2613 sep 10 19:34 larrydb.sql

The third step is to clear the log.Because a backup has been made,So no previous log is 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.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 |
+ ------------------ + ----------- +
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)

The fourth step is to update the 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)
[[email protected] data] #date
tue sep 10 19:38:24 cst 2013

The fifth step is to simulate data loss.Delete library

[[email protected] 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)
[[email protected] data] #cd/usr/local/mysql/data /
[[email protected] data] #ll
total 28736
drwx ------. 2 mysql mysql 4096 sep 10 19:14 game
drwx ------. 2 mysql mysql 4096 sep 7 00:43 hello
-rw-rw ----. 1 mysql mysql 18874368 sep 10 19:36 ibdata1
-rw-rw ----. 1 mysql mysql 5242880 sep 10 19:36 ib_logfile0
-rw-rw ----. 1 mysql mysql 5242880 sep 4 23:39 ib_logfile1
drwxr-xr-x. 2 mysql mysql 4096 sep 10 18:35 mnt
drwxr-xr-x. 2 mysql mysql 4096 sep 4 23:39 mysql
-rw-rw ----. 1 mysql mysql 998 sep 10 19:37 mysql-bin.000001
-rw-rw ----. 1 mysql mysql 19 sep 10 19:34 mysql-bin.index
drwx ------. 2 mysql mysql 4096 sep 4 23:39 performance_schema
-rw-r -----. 1 mysql root 26371 sep 10 19:30 serv01.host.com.err
-rw-rw ----. 1 mysql mysql 5 sep 10 18:36 serv01.host.com.pid
drwx ------. 2 mysql mysql 4096 sep 7 00:13 test
#You can use the mysqlbinlog command to view the log file
[[email protected] data] #mysqlbinlog mysql-bin.000001
mysql>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)

The sixth step is to import the data before the update

[[email protected] databackup] #mysql -uroot -p123456<larrydb.sql
error 1050 (42s01) at line 33:table "` larrydb`.`class` "already exists
[[email protected] 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)

The seventh step is to restore data based on the log

[[email protected] 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)
#Regulation:The point in time for recovery (or the time point after the commit) is the point at which the accident occurred plus one second.
[[email protected] 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)
[[email protected] 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 timestamp=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=1378813065/*! * /;
commit
/*! * /;
delimiter;
#end of log file
rollback/* added by mysqlbinlog * /;
/*! 50003 set completion_type [email protected]_completion_type * /;
  • Previous Understanding of this keyword in js
  • Next C # method for connecting to the database and updating the database