I. What is Incremental backup?
1. What is Incremental backup-Incremental backup is to back 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.
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
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 ././
[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 = "18:00:00" -- stop-date = "23:00:00"/var/lib/ mysql/mysql-bin.000002 // below is part of the content, actually, some SQL statements that operate on data #
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_pos1_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.
