MySQL uses Binlog incremental backup to restore instances

MySQL uses Binlog incremental backup to restore instances

Zhang Ying published in 2010-09-29

Category directory: MySQL

One, what is incremental backup

An incremental backup is a backup of the newly added data. If you have a database, 10G of data, every day will increase the 10M of data, the database is backed up every day, so much data is not to be backed up? Or just backing up the added data, it's clear that I just need to back up the added data. This reduces the burden on the server.

Second, enable Binlog

VI my.cnf

Log-bin=/var/lib/mysql/mysql-bin.log, if that's the case Log-bin=mysql-bin.log defaults to the DataDir directory

[email protected] mysql]# ls |grep mysql-bin
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.000006
Mysql-bin.index

After startup, a file such as Mysql-bin will be generated, with one or more additions per boot.

mysql-bin.000002 such files are stored in the database every day to increase the data, all the data increment of the database in this area.

Third, see what's in the mysql-bin.000002 file.

[Email protected] mysql]# mysqlbinlog/var/lib/mysql/mysql-bin.000002 >/tmp/add.sql

View copy print?

2. [[email protected] mysql]# Cat/tmp/add.sql //The following is a file based on Mysql-bin (some content)
4. /*!40019 SET @ @session. max_insert_delayed_threads=0*/;
6. /*!50003 SET @o[email protected] @COMPLETION_TYPE, completion_type=0*/;
8. DELIMITER /*!*/;
10. # at 4
12. #100929 21:23:52 Server ID 1 end_log_pos 106 start:binlog v 4, Server v 5.1.50-log created 100929 21:23:52 at Startu P
14. # Warning:this Binlog is not closed properly. Most probably mysqld crashed writing it.
16. ROLLBACK/*!*/;
18. BINLOG '
20. 6d2jta8baaaazgaaagoaaaabaaqans4xljuwlwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
22. Aaaaaaaaaaaaaaaaaadopanmezgnaagaegaebaqeegaauwaegggaaaaicagc
24. '/*!*/;
26. # at 106
28. #100929 21:29:35 Server ID 1 end_log_pos 134 intvar
30. SET insert_id=16/*!*/;
32. # at 134
34. #100929 21:29:35 Server ID 1 end_log_pos 237 Query thread_id=1 exec_time=0 error_code=0
36. Use test/*!*/; //This is the test database
38. SET timestamp=1285766975/*!*/;
40. SET @ @session. pseudo_thread_id=1/*!*/;
42. SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=1, @ @session. Unique_checks=1/*!*/;
44. SET @ @session. sql_mode=0/*!*/;
46. SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;
48. /*!\c UTF8 *//*!*/;
50. SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=33/*!*/;
52. SET @ @session. lc_time_names=0/*!*/;
54. SET @ @session. collation_database=default/*!*/;
56. INSERT into AA (name) VALUES (' CCCCCCCCCC ')
58. /*!*/;
60. # at 237
62. #100929 21:32:21 Server ID 1 end_log_pos 265 intvar
64. SET insert_id=12/*!*/;
66. # at 265
68. #100929 21:32:21 Server ID 1 end_log_pos 370 Query thread_id=1 exec_time=0 error_code=0
70. SET timestamp=1285767141/*!*/;
72. Insert into user (name) values (' CCCCCCCCCC ')
74. /*!*/;
76. # at 370
78. #100929 21:35:25 Server ID 1 end_log_pos thread_id=1 exec_time=0 error_code=0
80. SET timestamp=1285767325/*!*/;
82. BEGIN
84. /*!*/;
86. # at 440
88. #100929 21:35:25 Server ID 1 end_log_pos 468 intvar
90. SET insert_id=45/*!*/;
92. # at 468
94. #100929 21:35:25 Server ID 1 end_log_pos 573 Query thread_id=1 exec_time=0 error_code=0
96. Use blog/*!*/; //This is the blog database
98. SET timestamp=1285767325/*!*/;
100. Insert INTO city (CityName) VALUES (' asdf ')
102. /*!*/;
104. # at 573
106. #100929 21:35:25 Server ID 1 end_log_pos Xid = 205
108. COMMIT/*!*/;
110. DELIMITER;
112. # End of log file
114. ROLLBACK/ * Added by Mysqlbinlog */;
116. /*!50003 SET [email protected]_completion_type*/;

Here's another important index file is Mysql-bin.index

1. [email protected] mysql]# cat Mysql-bin.index
2. ./mysql-bin.000001
3. ./mysql-bin.000002
4. ./mysql-bin.000003
5. ./mysql-bin.000004
6. ./mysql-bin.000005
7. ./mysql-bin.000006

Four, incremental and incremental restores

1, incremental backup

Now that we know, MySQL inside the new added data in mysql-bin such a file, we just have to mysql-bin such a file to be backed up.

cp/var/lib/mysql/mysql-bin*/data/mysql_newbak/

2, incremental restore, speak a few common, more useful

A), according to the time to restore--start-date,--stop-date

[Email protected] mysql]#/usr/local/mysql/bin/mysqlbinlog--start-date= "2010-09-29 18:00:00"--stop-date= " 2010-09-29 23:00:00 "/var/lib/mysql/mysql-bin.000002 |mysql-u root-p

Look at the data according to the conditions

View copy print?

2. [Email protected] mysql]#/usr/local/mysql/bin/mysqlbinlog--start-date="2010-09-29 18:00:00"
4. --stop-date="2010-09-29 23:00:00"/var/lib/mysql/mysql-bin.000002
6. Here are some of the things that are actually some SQL statements that manipulate data
8. # at 237
10. #100929 21:32:21 Server ID 1 end_log_pos 265 intvar
12. SET insert_id=12/*!*/;
14. # at 265
16. #100929 21:32:21 Server ID 1 end_log_pos 370 Query thread_id=1 exec_time=0 error_code=0
18. SET timestamp=1285767141/*!*/;
20. Insert into user (name) values (' CCCCCCCCCC ')
22. /*!*/;
24. # at 370
26. #100929 21:35:25 Server ID 1 end_log_pos thread_id=1 exec_time=0 error_code=0
28. SET timestamp=1285767325/*!*/;
30. BEGIN
32. /*!*/;
34. # at 440
36. #100929 21:35:25 Server ID 1 end_log_pos 468 intvar
38. SET insert_id=45/*!*/;
40. # at 468
42. #100929 21:35:25 Server ID 1 end_log_pos 573 Query thread_id=1 exec_time=0 error_code=0

b), according to the starting position to restore,--start-position,--stop-position

[Email protected] mysql]#/usr/local/mysql/bin/mysqlbinlog--start-position=370--stop-position=440/var/lib/mysql/ mysql-bin.000002 |mysql-u Root-p

View inserted content, root a) is the same

[Email protected] mysql]#/usr/local/mysql/bin/mysqlbinlog--start-position=370--stop-position=440/var/lib/mysql/ mysql-bin.000002

--start-position=370--stop-position=440 Where did the numbers come from?
# at 370
#100929 21:35:25 Server ID 1 end_log_pos thread_id=1 exec_time=0 error_code=0
SET timestamp=1285767325/*!*/;

Above the red bold is, one is start-position, one is stop-position

c), restore-D according to the database name

Here is the lowercase d, please do not confuse it with the-D in Mysqldump. Ha ha.

[Email protected] mysql]#/usr/local/mysql/bin/mysqlbinlog-d test/var/lib/mysql/mysql-bin.000002

View the content, please refer to a)

D), according to the IP of the database to divide-H

[Email protected] mysql]#/usr/local/mysql/bin/mysqlbinlog-h 192.1681.102/var/lib/mysql/mysql-bin.000002

View the content, please refer to a)

e), according to the port occupied by the database to split-p

Sometimes, our MySQL is not necessarily 3306 port, note is uppercase P

[Email protected] mysql]#/usr/local/mysql/bin/mysqlbinlog-p 13306/var/lib/mysql/mysql-bin.000002

View the content, please refer to a)

f) To restore--server-id according to the database ServerID

In the database configuration file, there is a serverid and ServerID in the same cluster cannot be the same.

[Email protected] mysql]#/usr/local/mysql/bin/mysqlbinlog--server-id=1/var/lib/mysql/mysql-bin.000002

View the content, please refer to a)

Note: The above example, I am a one said, in fact, can be arranged in combination. For example

[Email protected] mysql]#/usr/local/mysql/bin/mysqlbinlog--start-position= "2010-09-29 18:00:00"-D test-h 127.0.0.1 /var/lib/mysql/mysql-bin.000002 |mysql-u Root-p

Five, follow-up

Incremental backup, a bit annoying, is mysql-bin such a file, each time you start MySQL will increase some, if you do not control him, the time is long, he will fill your disk.

./mysqldump--flush-logs-u Root MyBlog >/tmp/myblog.sql

Back up the MyBlog database and clear the data about MyBlog in the incremental backup

./mysqldump--flush-logs-u Root--all-databases >/tmp/alldatabase.sql

Back up all databases, clear incremental backups

Mysql-bin.index index role, because the increment of data is not necessarily in a mysql-bin000 such a file, this time, we will be based on Mysql-bin.index to find mysql-bin such a delta file.

If MySQL does this configuration binlog-do-db=test1, the incremental backup will only have the data test1 this database

Http://blog.51yip.com/mysql/1042.html

MySQL uses Binlog incremental backup to restore instances