MySQL uses Binlog incremental backup to restore instances

Source: Internet
Author: User
Tags rollback

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?
  1. [[email protected] mysql]# Cat/tmp/add.sql //The following is a file based on Mysql-bin (some content)
  2. /*!40019 SET @ @session. max_insert_delayed_threads=0*/;
  3. /*!50003 SET @o[email protected] @COMPLETION_TYPE, completion_type=0*/;
  4. DELIMITER /*!*/;
  5. # at 4
  6. #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
  7. # Warning:this Binlog is not closed properly. Most probably mysqld crashed writing it.
  8. ROLLBACK/*!*/;
  9. BINLOG '
  10. 6d2jta8baaaazgaaagoaaaabaaqans4xljuwlwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
  11. Aaaaaaaaaaaaaaaaaadopanmezgnaagaegaebaqeegaauwaegggaaaaicagc
  12. '/*!*/;
  13. # at 106
  14. #100929 21:29:35 Server ID 1 end_log_pos 134 intvar
  15. SET insert_id=16/*!*/;
  16. # at 134
  17. #100929 21:29:35 Server ID 1 end_log_pos 237 Query thread_id=1 exec_time=0 error_code=0
  18. Use test/*!*/; //This is the test database
  19. SET timestamp=1285766975/*!*/;
  20. SET @ @session. pseudo_thread_id=1/*!*/;
  21. SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=1, @ @session. Unique_checks=1/*!*/;
  22. SET @ @session. sql_mode=0/*!*/;
  23. SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;
  24. /*!\c UTF8 *//*!*/;
  25. SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=33/*!*/;
  26. SET @ @session. lc_time_names=0/*!*/;
  27. SET @ @session. collation_database=default/*!*/;
  28. INSERT into AA (name) VALUES (' CCCCCCCCCC ')
  29. /*!*/;
  30. # at 237
  31. #100929 21:32:21 Server ID 1 end_log_pos 265 intvar
  32. SET insert_id=12/*!*/;
  33. # at 265
  34. #100929 21:32:21 Server ID 1 end_log_pos 370 Query thread_id=1 exec_time=0 error_code=0
  35. SET timestamp=1285767141/*!*/;
  36. Insert into user (name) values (' CCCCCCCCCC ')
  37. /*!*/;
  38. # at 370
  39. #100929 21:35:25 Server ID 1 end_log_pos thread_id=1 exec_time=0 error_code=0
  40. SET timestamp=1285767325/*!*/;
  41. BEGIN
  42. /*!*/;
  43. # at 440
  44. #100929 21:35:25 Server ID 1 end_log_pos 468 intvar
  45. SET insert_id=45/*!*/;
  46. # at 468
  47. #100929 21:35:25 Server ID 1 end_log_pos 573 Query thread_id=1 exec_time=0 error_code=0
  48. Use blog/*!*/; //This is the blog database
  49. SET timestamp=1285767325/*!*/;
  50. Insert INTO city (CityName) VALUES (' asdf ')
  51. /*!*/;
  52. # at 573
  53. #100929 21:35:25 Server ID 1 end_log_pos Xid = 205
  54. COMMIT/*!*/;
  55. DELIMITER;
  56. # End of log file
  57. ROLLBACK/ * Added by Mysqlbinlog */;
  58. /*!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?
  1. [Email protected] mysql]#/usr/local/mysql/bin/mysqlbinlog--start-date="2010-09-29 18:00:00"
  2. --stop-date="2010-09-29 23:00:00"/var/lib/mysql/mysql-bin.000002
  3. Here are some of the things that are actually some SQL statements that manipulate data
  4. # at 237
  5. #100929 21:32:21 Server ID 1 end_log_pos 265 intvar
  6. SET insert_id=12/*!*/;
  7. # at 265
  8. #100929 21:32:21 Server ID 1 end_log_pos 370 Query thread_id=1 exec_time=0 error_code=0
  9. SET timestamp=1285767141/*!*/;
  10. Insert into user (name) values (' CCCCCCCCCC ')
  11. /*!*/;
  12. # at 370
  13. #100929 21:35:25 Server ID 1 end_log_pos thread_id=1 exec_time=0 error_code=0
  14. SET timestamp=1285767325/*!*/;
  15. BEGIN
  16. /*!*/;
  17. # at 440
  18. #100929 21:35:25 Server ID 1 end_log_pos 468 intvar
  19. SET insert_id=45/*!*/;
  20. # at 468
  21. #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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.