Small database full + incremental backup

1. Create a mock all database

Mysql>create database test;

2. Create a simulation of all the tables

Mysql>create TABLE student (ID INT) NOT null UNIQUE PRIMARY KEY, name VARCHAR (a) NOT NULL,

Sex varchar (4), birth year, Department varchar (+), address varchar (50));

3. Inserting data into the table

Mysql>insert into student VALUES (901, ' Zhang boss ', ' Male ', 1985, ' computer Department ', ' Haidian District, Beijing ');

INSERT into student VALUES (902, ' Zhang Dick ', ' Male ', 1986, ' Chinese department ', ' Beijing Changping District ');

INSERT into student VALUES (903, ' Zhang San ', ' Female ', 1990, ' Chinese department ', ' Hunan province Yongzhou ');

INSERT into student VALUES (904, ' John Doe ', ' Male ', 1990, ' English Department ', ' Liaoning province Fuxin ');

INSERT into student VALUES (905, ' Harry ', ' Female ', 1991, ' English Department ', ' Xiamen, Fujian Province ');

INSERT into student VALUES (906, ' King vi ', ' Male ', 1988, ' computer department ', ' Hunan province Hengyang ');

4. Querying all records of the student table

Mysql> SELECT * from student;

+-----+--------+------+-------+------------+--------------+

| ID | name | sex | Birth | Department | Address |

+-----+--------+------+-------+------------+--------------+

| 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |

| 902 | Double Dick |  Male | 1986 | Chinese Department | Beijing Changping District |

| 903 | Zhang San |  Women | 1990 | Chinese Department | Hunan Province Yongzhou |

| 904 | John Doe |  Male | 1990 | English Department | Liaoning Province Fuxin |

| 905 | Harry |  Women | 1991 | English Department | Xiamen, Fujian |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |

+-----+--------+------+-------+------------+--------------+

5. Back Up all libraries

[Email protected] ~]# mysqldump-uroot-p--master-data=2--flush-logs--all-databases--lock-all-tables >/root/alld Atabses.sql

6. View the contents of a full backup

[email protected] ~]# less Alldatabases.sql

7. Delete the previous binary log (optional, delete the previous recommended backup)

Mysql>purge BINARY LOGS to ' mysqld-bin.00000x '

8. View the current binary log

Mysql>show binary logs;

9. Modify the data in the student table (simulate the first day)

Mysql>use student;

Mysql>delete from student where sex = ' male ';

10. Scrolling logs

Mysql>flush logs;

11. Do an incremental backup

[Email protected] ~]# cd/var/lib/mysql/

[Email protected] mysql]# Mysqlbinlog mysqld-bin.0000x >/root/mon-incremental.sql

At this point the mysqld-bin.0000x is the newest binary log before a binary log because the scroll log is made

12. Modify the data in the student table (simulate the next day)

Mysql>use student;

Mysql>insert into student (Name) VALUES (' Godben ');

13. The simulation database is completely lost (because the binary log is normally not on the same disk as the data, so the binary log is copied elsewhere)

[[email protected] mysql]# CP mysqld-bin.00001x/root (newest binary)

[Email protected] mysql]# rm-fr./*

14. Start restoring a full backup, and the result of the 4th step after the restore

[Email protected] ~]# Mysql-uroot-p < Alldatabases.sql

15. Import the first incremental backup, followed by the 9th step result

[Email protected] ~]# Mysql-uroot-p < Mon-incremental.sql

16. Import the last binary log, restore to the moment the database was lost, import as the 12th step result

[Email protected] ~]# Mysqlbinlog mysqld-bin.0000x | Mysql-uroot-p

This article is from the "Godben" blog, make sure to keep this source http://godben.blog.51cto.com/8919725/1695572

Small database full + incremental backup