MySQL uses Binlog incremental backup + Restore instance

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

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

Java Code[root @BlackGhost  mysql]# cat mysql-bin.index      ./mysql-bin.000001      ./mysql-bin. 000002      ./mysql-bin.000003     ./mysql-bin. 000004     ./mysql-bin.000005      ./mysql-bin. 000006    

[email protected] mysql]# cat Mysql-bin.index./mysql-bin.000001./mysql-bin.000002./ 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

Java Code[Root@BlackGhost 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 //The following is part of the content, in fact, some of the SQL statements to manipulate the data #

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_posthread_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://www.linuxidc.com/Linux/2012-09/70815.htm

MySQL uses Binlog incremental backup + Restore instance