Shulou(Shulou.com)06/01 Report--

Experimental scenario:

Two mysql instances, one port is 3306 and the other port is 3307.

Now do an incremental backup on 3306 and restore the backup to 3307.

Backup:

A) level 0 backup

Innobackupex-default-file=/usr/local/mysql/my.cnf-user=root-password=root-socket=/usr/local/mysql/mysql.sock / tmp/backup/zero-no-timestamp-parallel=4

B) level 1 backup

1. Operate mysql instances to add data

Mysql-S / usr/local/mysql/mysql.sock-P 3306-p

Show databases

Use lxm

Create table T1 as select * from t

2. Create a level 1 backup based on level 0 backup

Innobackupex-default-file=/usr/local/mysql/my.cnf-user=root-password=root-socket=/usr/local/mysql/mysql.sock-incremental / tmp/backup/one-incremental-basedir=/tmp/backup/zero-no-timestamp-parallel=4

C) level 2 backup

1. Continue to operate mysql instances and add data

Mysql-S / usr/local/mysql/mysql.sock-P 3306-p

Show databases

Use lxm

Create table T2 as select * from t

2. Create a level 2 backup based on level 1 backup

Innobackupex-default-file=/usr/local/mysql/my.cnf-user=root-password=root-socket=/usr/local/mysql/mysql.sock-incremental / tmp/backup/two-incremental-basedir=/tmp/backup/one-no-timestamp-parallel=4

Stop mysql3307 to clear the files under / var/lib/mysql3307

Service mysql3307 stop

Cd / usr/local/mysql3307

Mv data data.bak

Restore:

A) prepare incremental backup

1. When you apply a level 0 backup to restore an incremental backup, remember to use the-- redo-only parameter to do only committed transactions and do not roll back uncommitted transactions. Remember: if you have rolled back an uncommitted transaction, you can no longer apply incremental backups.

Innobackupex-defaults-file=/usr/local/mysql3307/my.cnf-apply-log-redo-only-use-memory=10M / tmp/backup/zero

2. Apply level 1 backup to level 0 backup

Innobackupex-defaults-file=/usr/local/mysql3307/my.cnf-apply-log-redo-only-use-memory=10M / tmp/backup/zero-incremental-dir=/tmp/backup/one/

3. Apply level 2 backup to level 0 backup, because it is the last incremental backup, there is no need to add-redo-only, so the last increment is also applied to the full backup.

Innobackupex-defaults-file=/usr/local/mysql3307/my.cnf-apply-log-use-memory=10M / tmp/backup/zero-incremental-dir=/tmp/backup/two/

4. When an incremental backup is restored, a level 0 backup is applied to roll back things that are not committed, so there is no need to add-- redo-only parameter

Innobackupex-defaults-file=/usr/local/mysql3307/my.cnf-apply-log-use-memory=10M / tmp/backup/zero

B) restore incremental backup

Innobackupex-defaults-file=/usr/local/mysql3307/my.cnf-copy-back / tmp/backup/zero

Verify the recovery result

Change the subordinate group of the restored data directory to mysql, start mysql3307, and check whether the data is the same as that in the mysql instance:

Mysql-S / usr/local/mysql3307/mysql.sock-P 3307-uroot-p

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | lxm |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

Mysql > use lxm

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > show tables

+-+

| | Tables_in_lxm |

+-+

| | t |

| | T1 |

| | T2 |

+-+

3 rows in set (0.00 sec)

Mysql > select count (1) from T1

+-+

| | count (1) | |

+-+

| | 14 |

+-+

1 row in set (0.00 sec)

Mysql > select count (1) from T2

+-+

| | count (1) | |

+-+

| | 14 |

+-+

1 row in set (0.00 sec)

Note:

Incremental backup (xtrabackup will only affect the tables of xtradb or innodb, while tables of other engines will only copy the whole file during incremental backup, there will be no difference. )

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.