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?
- [[email protected] mysql]# Cat/tmp/add.sql //The following is a file based on Mysql-bin (some content)
- /*!40019 SET @ @session. max_insert_delayed_threads=0*/;
- /*!50003 SET @o[email protected] @COMPLETION_TYPE, completion_type=0*/;
- DELIMITER /*!*/;
- # at 4
- #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
- # Warning:this Binlog is not closed properly. Most probably mysqld crashed writing it.
- ROLLBACK/*!*/;
- BINLOG '
- 6d2jta8baaaazgaaagoaaaabaaqans4xljuwlwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
- Aaaaaaaaaaaaaaaaaadopanmezgnaagaegaebaqeegaauwaegggaaaaicagc
- '/*!*/;
- # at 106
- #100929 21:29:35 Server ID 1 end_log_pos 134 intvar
- SET insert_id=16/*!*/;
- # at 134
- #100929 21:29:35 Server ID 1 end_log_pos 237 Query thread_id=1 exec_time=0 error_code=0
- Use test/*!*/; //This is the test database
- SET timestamp=1285766975/*!*/;
- SET @ @session. pseudo_thread_id=1/*!*/;
- SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=1, @ @session. Unique_checks=1/*!*/;
- SET @ @session. sql_mode=0/*!*/;
- SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;
- /*!\c UTF8 *//*!*/;
- SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=33/*!*/;
- SET @ @session. lc_time_names=0/*!*/;
- SET @ @session. collation_database=default/*!*/;
- INSERT into AA (name) VALUES (' CCCCCCCCCC ')
- /*!*/;
- # at 237
- #100929 21:32:21 Server ID 1 end_log_pos 265 intvar
- SET insert_id=12/*!*/;
- # at 265
- #100929 21:32:21 Server ID 1 end_log_pos 370 Query thread_id=1 exec_time=0 error_code=0
- SET timestamp=1285767141/*!*/;
- Insert into user (name) values (' CCCCCCCCCC ')
- /*!*/;
- # at 370
- #100929 21:35:25 Server ID 1 end_log_pos thread_id=1 exec_time=0 error_code=0
- SET timestamp=1285767325/*!*/;
- BEGIN
- /*!*/;
- # at 440
- #100929 21:35:25 Server ID 1 end_log_pos 468 intvar
- SET insert_id=45/*!*/;
- # at 468
- #100929 21:35:25 Server ID 1 end_log_pos 573 Query thread_id=1 exec_time=0 error_code=0
- Use blog/*!*/; //This is the blog database
- SET timestamp=1285767325/*!*/;
- Insert INTO city (CityName) VALUES (' asdf ')
- /*!*/;
- # at 573
- #100929 21:35:25 Server ID 1 end_log_pos Xid = 205
- COMMIT/*!*/;
- DELIMITER;
- # End of log file
- ROLLBACK/ * Added by Mysqlbinlog */;
- /*!50003 SET [email protected]_completion_type*/;
Here's another important index file is Mysql-bin.index
- [email protected] mysql]# cat Mysql-bin.index
- ./mysql-bin.000001
- ./mysql-bin.000002
- ./mysql-bin.000003
- ./mysql-bin.000004
- ./mysql-bin.000005
- ./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?
- [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
- Here are some of the things that are actually some SQL statements that manipulate data
- # at 237
- #100929 21:32:21 Server ID 1 end_log_pos 265 intvar
- SET insert_id=12/*!*/;
- # at 265
- #100929 21:32:21 Server ID 1 end_log_pos 370 Query thread_id=1 exec_time=0 error_code=0
- SET timestamp=1285767141/*!*/;
- Insert into user (name) values (' CCCCCCCCCC ')
- /*!*/;
- # at 370
- #100929 21:35:25 Server ID 1 end_log_pos thread_id=1 exec_time=0 error_code=0
- SET timestamp=1285767325/*!*/;
- BEGIN
- /*!*/;
- # at 440
- #100929 21:35:25 Server ID 1 end_log_pos 468 intvar
- SET insert_id=45/*!*/;
- # at 468
- #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