Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to back up and restore data incrementally by mysql". In daily operation, I believe many people have doubts about how to back up and restore data incrementally by mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to back up and restore data incrementally by mysql". Next, please follow the editor to study!

Complete:

[root@L112 backup] # innobackupex-- defaults-file=/etc/my.cnf-- no-timestamp-- user=root-- password=123456-- socket=/var/lib/mysql/mysql.sock / backup/mysqldump/full/back_26-05-2017

Incremental backup 1

(based on the last complete), the backup subdirectory is under back_26-05-2017.

[root@L112 backup] # innobackupex-defaults-file=/etc/my.cnf-no-timestamp-user=root-password=123456-socket=/var/lib/mysql/mysql.sock-incremental/ backup/mysqldump/incremental/back_26-05-2017

-- incremental-basedir=/backup/mysqldump/full/back_26-05-2017

Incremental backup 2

(based on the previous incremental backup back_26-05-2017), the backup subdirectory is under back_27-05-2017:

[root@L112 backup] # innobackupex-defaults-file=/etc/my.cnf-no-timestamp-user=root-password=123456-socket=/var/lib/mysql/mysql.sock-incremental/ backup/mysqldump/incremental/back_27-05-2017

-- incremental-basedir=/backup/mysqldump/incremental/back_26-05-2017

View the results of the backup:

Complete:

[root@L112 full] # ll

Total 0

Drwxr-xr-x. 6 root root 188 May 26 16:33 back_26-05-2017

Two incremental backups:

[root@L112 incremental] # ll

Total 0

Drwxr-xr-x. 6 root root 214May 26 09:42 back_26-05-2017

Drwxr-xr-x. 6 root root 214May 27 15:27 back_27-05-2017

The following simulates data loss:

Delete liuwenhe Library

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | liuwenhe |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

Mysql > drop database liuwenhe

Query OK, 2 rows affected (0.05sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

As shown above, the liuwenhe library has been deleted.

The specific recovery process is shown below.

Restore:

The whole process is divided into three steps, which can be understood as: first, pour the data from the incremental backup into the full backup, and then use the full backup to restore it.

The first step is to redo the committed logs under all backup directories. Note that the last incremental backup does not have-- redo-only, and the data is finally in a full backup, such as:

Innobackupex-apply-log-redo-only BASE-DIR

Innobackupex-apply-log-redo-only BASE-DIR-incremental-dir=INCREMENTAL-DIR-1

Innobackupex-apply-log BASE-DIR-incremental-dir=INCREMENTAL-DIR-2

Or

Add the user name and password, as well as the configuration file used:

Innobackupex-defaults-file=/opt/mysql/my.cnf-user=root-password=***-apply-log-redo-only BASE-DIR

Innobackupex-defaults-file=/opt/mysql/my.cnf-user=root-password=***-apply-log-redo-only BASE-DIR-incremental-dir=INCREMENTAL-DIR-1

Innobackupex-defaults-file=/opt/mysql/my.cnf-user=root-password=***-apply-log BASE-DIR-incremental-dir=INCREMENTAL-DIR-2

BASE-DIR refers to the full directory, INCREMENTAL-DIR-1 refers to the first incremental backup, INCREMENTAL-DIR-2 refers to the second incremental backup, and so on. One thing to note here is that there is no redo-only option for the incremental backup in the last step! Also, you can use-- use_memory to improve performance.

After the above statement is executed successfully, the final data is under BASE-DIR (that is, full directory).

My specific operation:

[root@L112 full] # innobackupex-- apply-log-- redo-only / backup/mysqldump/full/back_26-05-2017

[root@L112 full] # innobackupex-apply-log-redo-only / backup/mysqldump/full/back_26-05-2017/--incremental-dir=/backup/mysqldump/incremental/back_26-05-2017 /

[root@L112 full] # innobackupex-- apply-log / backup/mysqldump/full/back_26-05-2017 /-incremental-dir=/backup/mysqldump/incremental/back_27-05-2017 /

Step 2: roll back the outstanding logs, that is, those that have been flushed to disk but have not yet been committed.

Innobackupex-apply-log BASE-DIR

My specific operation:

[root@L112 full] # innobackupex-- apply-log / backup/mysqldump/full/back_26-05-2017 /

After the above execution, the backup files in BASE-DIR are fully ready.

The last step: copy. This step requires shutting down the database and deleting the data file and log file:

Innobackupex-copy-back BASE-DIR

My specific operation is:

1) turn off the mysql service and record the permissions of the relevant directories and files under the data file directory

[root@L112 data] # service mysql stop

[root@L112 data] # ll

Total 28748

-rw-rw----. 1 mysql mysql 18874368 May 27 17:15 ibdata1

-rw-rw----. 1 mysql mysql 5242880 May 27 17:15 ib_logfile0

-rw-rw----. 1 mysql mysql 5242880 May 17 17:20 ib_logfile1

-rw-r-. 1 mysql root 65782 May 27 17:15 L112.err

Drwx-. 2 mysql root 4096 May 17 17:12 mysql

Drwx-. 2 mysql mysql 4096 May 17 17:12 performance_schema

Drwx-. 2 mysql root 6 May 17 17:12 test

2) Delete everything in the directory of mysql data files

[root@L112 data] # rm-rf *

3) perform recovery operation

[root@L112 full] # innobackupex-- copy-back / backup/mysqldump/full/back_26-05-2017 /

Among them

-- copy-back means to copy back so that the original backup file still exists.

-- move-back stands for moving back so that the original backup files are gone, saving space.

4) the most important step, you need to modify the directory permissions, referring to the previously recorded.

[root@L112 data] # chown-R mysql:root mysql/

[root@L112 data] # chown-R mysql:root liuwenhe/

[root@L112 data] # chown-R mysql:root test/

[root@L112 data] # chown mysql:mysql ibdata1

[root@L112 data] # chown mysql:mysql ibdata0

[root@L112 data] # chown mysql:mysql ib_logfile1

5) start the database:

[root@L112 data] # / etc/init.d/mysqld start

At this point, the recovery process is complete.

As for only full recovery, it is relatively simple: two steps

1.apply-log application log to ensure consistency

2.copy-back, restore.

The details are as follows:

Innobackupex-- defaults-file=/opt/mysql/my.cnf-- user=root-- password=***-- use-memory=4G-- apply-log / backup/mysql/data/2013-10-299-05-25

Innobackupex-- defaults-file=/opt/mysql/my.cnf-- user=root-- password=***-- copy-back / backup/mysql/data/2013-10-299-05-25

From what can be seen, recovery is divided into two steps, the first step is apply-log, in order to speed up, it is generally recommended to set-- use-memory, after this step is completed, the backup files under the directory / backup/mysql/data/2013-10-29 09-05-25 are ready.

The second step is copy-back, which copies the backup files to the original data directory.

After the restore is complete, be sure to check that the owner and permissions of the data directory are correct.

At this point, the study of "how to incrementally back up and restore data by mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.