MySQL Backup and restore

MySQL backup and restore one, data backup 1. How to Back up

1) Full backup: Full backup of the database

2) Incremental backup: Files changed since the last backup

3) Differential Backup: The file that was modified after the last complete

Recommendation: Full volume + increment = January full, 1-week increments

2. Backup classification

1) Physical backup

Cold backup: After data is closed

Hot backup: When the data is running state

2) Logical Backup

Backing up logical objects (tables, libraries)

Second, full backup 1. Features

1) Data integrity

2) Larger data

3) long time, repetitive data

Packaging, CP, mysqldump

Case:

1. Preparing the data

New hehe database mysql> create databases hehe;

Add a table and format to the Hehe Database mysql>create table hehe.a (user char (4), password char (8), primary key (user));

Insert in-table data mysql> insert into HEHE.A values (' zhangs ', ' 123 ');

Insert in-table data mysql> insert into HEHE.A values (' Lisi ', ' 123 ');

2. Backup mode one: (Cold Backup)

Close the database/etc/init.d/mysqld stop

Backing Up the database tar-zcvf/root/mysql.bak/usr/local/mysql/data/

Mode two: (Hot backup)

Mysqldump-u root-p--all-database >/root/mysql.sql//Hot backup, database does not need to stop, back up all databases

3. Analog failure

Delete the hehe database by mistake mysql> drop DB hehe;

4. Recovery mode One: (Cold recovery)

Close the database/etc/init.d/mysqld stop

TAR-ZXVF/ROOT/MYSQL.BAK-C/

Mode two: (Heat recovery)

Enter Data Execution command mysql> Source/root/mysql.sql

Mode three: (Heat recovery)

Recovering data mysql-u Root-p </root/mysql.sql

Three, incremental backup

1.Mysql does not have an incremental backup method, it needs to be incremental by binary logs (record all change operations)

Case:

1) Prepare the data (IBID. preparation data)

2) Open Binary log

Edit MySQL Database configuration file vim/etc/my.cnf

Restart MySQL service/etc/init.d/mysqld restart

3. Full backup Method One: (Cold Backup)

/etc/init.d/mysqld stop

Backing Up the database tar-zcvf/root/mysql.bak/usr/local/mysql/data/

Mode two: (Hot backup)

Mysqldump-u root-p--all-database >/root/mysql.sql//Hot backup, database does not need to stop, back up all databases

4. Data increase

View binary Files ls/usr/local/mysql/data/

Intercept binary files prepare to increase data mysqladmin-u root-p Flush-logs

View binary Files ls/usr/local/mysql/data/

Login database Add data mysql> INSERT into HEHE.A values (' Wang ', ' 123 ');

Generate a new binary file mysql> flush log;

Exit Database View ls/usr/local/mysql/data/

5. Incremental backup (the data that needs to be restored is mysql-bin.000032 here)

Copy the added data cp/usr/local/mysql/data/mysql-bin.000032/root/

View the/root/directory (the newly added data has been successfully backed up in the following cases)

6. Data deletion

Login database mistakenly delete mysql> delete hehe.a from haha.a where user= ' Lisi ';

7. Incremental Restore

Restore deleted new Data Mysqlbinlog mysql-bin.000018 | Mysql-u root-p

View Database select * from Hehe.a;

Note: incremental restore succeeds

8. Verification

Log on to the database to see if the data is restored mysql> select * from Hehe.a;

Note: If you do incremental data backup must intercept the binary data before the increment, you should intercept the binary data once the backup is complete, and remember that the incremental data that needs to be restored when the data is lost is the binary data that was truncated before the increment

MySQL Backup and restore