MySQL Backup and restore

Source: Internet
Author: User
Tags prepare mysql backup

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


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


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)


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.