Mysql uses binlog incremental backup to restore instance _ MySQL

Mysql uses binlog incremental backup to restore the instance bitsCN.com

Mysql uses binlog incremental backup to restore an instance

I. What is incremental backup?

Incremental backup backs up new data. If you have 10 GB of data in a database, you can add 10 MB of data every day. the database must be backed up once every day. do you need to back up so much data? We only need to back up the added data. Obviously, I only need to back up the added data. This reduces the burden on the server.

2. enable binlog

Vi my. cnf

Log-bin =/var/lib/mysql/mysql-bin.log, if so log-bin = mysql-bin.log is under the datadir directory by default

[Root @ BlackGhost 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 mysql-bin is started, one or more files are generated.

Mysql-bin.000002 files store the data that is added to the database every day, where the incremental data of all databases is located.

3. check what is in a file like a mysql-bin.000002

[Root @ BlackGhost mysql] # mysqlbinlog/var/lib/mysql/mysql-bin.000002 & gt;/tmp/add. SQL

Java code

[Root @ BlackGhost mysql] # cat/tmp/add. SQL // the following file is generated based on mysql-bin (partial content)

/*! 40019 SET @ session. max_insert_delayed_threads = 0 */;

/*! 50003 SET @ OLD_COMPLETION_TYPE = @ 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 startup

# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.

ROLLBACK /*! */;

Binlog'

Bytes

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 /*! * // The test database is used here.

Set timestamp = 1285766975 /*! */;

SET @ session. pseudo do_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 ('cccccccccccccccccc ')

/*! */;

# 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 ('cccccccccccccccc ')

/*! */;

# At 370

#100929 21:35:25 server id 1 end_log_pos 440 Query 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 /*! * // Here 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 600 Xid = 205

COMMIT /*! */;

DELIMITER;

# End of log file

ROLLBACK/* added by mysqlbinlog */;

/*! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE */;

And an 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

IV. incremental backup and incremental restoration

1. incremental backup

Now that we know that the newly added data in mysql is in a file like mysql-bin, we only need to back up the file like mysql-bin.

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

2. incremental restoration: some common and useful

A) restore-start-date,-stop-date by time

[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 | mysql-u root-p

Check 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 = "23:00:00"/var/lib/mysql/mysql-bin.000002

// The following is part of the content, which is actually some SQL statements that operate on 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 ('cccccccccccccccc ')

/*! */;

# At 370

#100929 21:35:25 server id 1 end_log_pos 440 Query 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), restore based on the starting position,-start-position,-stop-position

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

// View the inserted content, which is the same in root)

[Root @ BlackGhost 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 does the number come from?

# At 370

#100929 21:35:25 server id 1 end_log_pos 440 Query thread_id = 1 exec_time = 0 error_code = 0

Set timestamp = 1285767325 /*! */;

The above red bold is, one is start-position, the other is stop-position

C) restore the database by name-d

Here is a lowercase d. do not mix it with-D in mysqldump. Haha.

[Root @ BlackGhost mysql] #/usr/local/mysql/bin/mysqlbinlog-d test/var/lib/mysql/mysql-bin.000002

For details, refer to)

D), which is divided into-h based on the IP address of the database.

[Root @ BlackGhost mysql] #/usr/local/mysql/bin/mysqlbinlog-h 1921381.102/var/lib/mysql/mysql-bin.000002

For details, refer to)

E) according to the port occupied by the database,-P

Sometimes, we use port 3306 for mysql. Note that it is an uppercase P

[Root @ BlackGhost mysql] #/usr/local/mysql/bin/mysqlbinlog-p 13306/var/lib/mysql/mysql-bin.000002

For details, refer to)

F) restore-server-id based on the database serverid

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

[Root @ BlackGhost mysql] #/usr/local/mysql/bin/mysqlbinlog-server-id = 1/var/lib/mysql/mysql-bin.000002

For details, refer to)

Note: I have mentioned the above examples one by one. In fact, they can be arranged and combined. For example

[Root @ BlackGhost 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

5. follow-up

One unpleasant thing about incremental backup is that files such as mysql-bin will be added every time mysql is started. if you don't care about them, it will take a long time, it will fill up 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 and clear incremental backup

Mysql-bin.index index, because the incremental data is not necessarily in a file such as a mysql-bin000, at this time, we will find an incremental file such as mysql-bin according to the mysql-bin.index.

If binlog-do-db = test1 is configured in mysql, only data in the database test1 is available in the incremental backup.

BitsCN.com