Percona-xtrbackup for multiple instances (), perconaserver

Percona-xtrbackup for multiple instances (), perconaserver

Background

Percona-xtrabackup is a backup tool developed by percona. It mainly has two tools: xtrabackup and innobackupex. Innobackupex encapsulates xtrabackup and is a perl script. This document is relatively simple. innobackupex is used to back up data of 3306 instances and restore the data to 3307 instances. It also briefly introduces how to use this backup to restore data.

You can install percona-xtrbackup using binary, source code, and yum. This article mainly uses yum for installation. The steps are as follows:

 yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm  yum install percona-xtrabackup.x86_64 yum search percona

For other installation instructions, visit the official website: Workshop.

cd /mkdir datacd datamkdir mkdir backup

Create three directories in the backup Directory: mkdir {conf, incremental, full}
The specific functions of the three directories are as follows: conf: stores custom my. cnf configuration information full: stores the first full backup data
Incremental: stores incremental backup data backup my. cnf to the conf directory

cp /etc/my.cnf /data/backup/conf/3306.cnfcp /etc/my.cnf /data/backup/conf/3307.cnf

3306. Save cnf as it is. 3307. cnf needs to be modified. Add "datadir =/data/mysql/mysql_3307/data/" under the [mysqld] node /". To facilitate data recovery. Common parameters:

-- User: mysql user -- password: user password -- defaults-file: Specifies my. cnf file path. If this parameter is not specified, the default mysql path is read. cnf file-socket: socket file corresponding to the mysql instance

Backup operations 1. Full backup the first backup is a full backup, which is also the basis of Incremental backup.

innobackupex --user=root --password=123456 --socket=/tmp/mysql_3306.sock  --defaults-file=/data/backup/conf/3306.cnf  /data/backup/full/ 

Back up all data in the database to the/data/backup/full/directory for the first time. a subdirectory with the current timestamp is generated under the/data/backup/full/directory, 1. If you do not want to generate a timestamp subdirectory, you can use the -- no-timestamp parameter so that it does not automatically generate a timestamp subdirectory, therefore, the backup data will be stored under/data/backup/full. For full backup, you only need to specify the user name, password, and backup path for backup. The innobackupex: completed OK is displayed! The backup is successful.

Figure 1

Directory file after full backup, 2.

Figure 2

Mysql data DIRECTORY file, 3.

Figure 3

You can compare the directory files in figure 2 and Figure 3. The files generated by xtrabackup include backup-my.cnf, xtrabackup_checkpoints, xtrabackup_info, and xtrabackup_lofile.

File description:

Backup-my.cnf: mainly records Mysql parameters used in innobackupex.

# This MySQL options file was generated by innobackupex. # The MySQL server [mysqld] innodb_checksum_algorithm=innodb innodb_data_file_path=ibdata1:12M:autoextend innodb_log_files_in_group=2 innodb_log_file_size=50331648 innodb_page_size=16384 innodb_undo_directory=. innodb_undo_tablespaces=0

Xtrabackup_checkpoints: records the Backup Type and the start and end lsn locations. Backup_type has two types: full-prepared (full backup) and incremental (incremental Backup ).

backup_type = full-prepared from_lsn = 0 to_lsn = 8234580547 last_lsn = 8234580547 compact = 0

Xtrabackup_info: records mysql-related information.

uuid = 3d090541-6649-11e4-bb2a-000c295bd3a3 name = tool_name = innobackupex tool_command = --user=root --password=... --incremental /data/backup/incremental/ --incremental-base=/data/backup/incremental/2014-11-07_14-24-54/ --defaults-file =/data/backup/conf/3306.cnf --socket=/tmp/mysql_3306.sock tool_version = 1.5.1-xtrabackup ibbackup_version = xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: ) server_version = 5.6.21-log start_time = 2014-11-07 14:41:52 end_time = 2014-11-07 14:42:27 lock_time = 2 binlog_pos = innodb_from_lsn = 8234579864 innodb_to_lsn = 8234580547 partial = N incremental = Y format = file compact = N compressed = N

Xtrabackup_logfile: xtrabackup your own log file, which is not directly visible in the new version.

2. Create new data, create tables, and write data as new data.

use test;create table t3(col1 int,col2 int);

Write the following new data:

insert into t3(col1,col2)value(1,1);insert into t3(col1,col2)value(2,1);insert into t3(col1,col2)value(3,1);insert into t3(col1,col2)value(4,1);insert into t3(col1,col2)value(5,1);insert into t3(col1,col2)value(6,1);insert into t3(col1,col2)value(7,1);insert into t3(col1,col2)value(8,1);insert into t3(col1,col2)value(9,1);insert into t3(col1,col2)value(10,1);

Note: You can delete and update data, and then observe the table files under the directory after the first Incremental backup, compare with the full backup table files, and analyze the differences.

3. The first Incremental backup is the first Incremental backup. The Incremental Backup backs up the latest data based on the last backup. The statement is as follows:

innobackupex --user=root --password=123456 --socket=/tmp/mysql_3306.sock  --defaults-file=/data/backup/conf/3306.cnf  --incremental /data/backup/incremental/ --incremental-basedir=/data/backup/full/2014-11-07_14-06-47

Parameter description:

-- Incremental: Specifies the directory for storing this incremental backup -- incremental-basedir: the directory of the last backup

After the initial Incremental backup is completed, a new directory is generated under the specified directory. The files in the directory are different from those in the full backup file.

4. The second Incremental Backup

Backup is performed on the basis of the second backup (Incremental Backup.

innobackupex --user=root --password=123456 --socket=/tmp/mysql_3306.sock  --defaults-file=/data/backup/conf/3306.cnf  --incremental /data/backup/incremental/  --incremental-basedir=/data/backup/incremental/2014-11-07_14-21-25

Recovery operation 1. Perform full backup operations with two parameters:

-- Apply-log: Creates a new transaction log that reads innodb configuration information from the backup-my.cnf file. -- Redo-only: Read-only committed transactions. This parameter is not required for the last incremental merge.

Preparations before full Backup Recovery

innobackupex  --apply-log --redo-only /data/backup/full/2014-11-07_14-06-47

2. Merge the data from the first Incremental backup to the full backup

innobackupex  --apply-log --redo-only  /data/backup/full/2014-11-07_14-06-47 --incremental-dir=/data/backup/incremental/2014-11-07_14-21-25

3. Merge the data from the second full backup to the full backup.

innobackupex --apply-log  /data/backup/full/2014-11-06_14-52-38/ --incremental-dir=/data/backup/incremental/2014-11-07_14-23-52

Note: The -- redo-only parameter is not required in the last merge operation. 4. mysql must be stopped when the MySQL service is stopped for recovery. Therefore, the MySQL3307 instance must be stopped.

/usr/local/mysql/bin/mysqld_multi stop 3307

If you cannot stop mysql, run kill-9 mysql3307. 5. Restore data.

innobackupex --defaults-file=/data/backup/conf/3307.cnf --copy-back /data/backup/full/2014-11-06_14-52-38/ 

6. permission settings: Set the permission for the mysql data DIRECTORY.

chown -R mysql:mysql   /data/mysql/mysql_3307/data

7. Start the mysql3307 instance

/usr/local/mysql/bin/mysqld_mutil start 3306

Check whether the instance is started:

/usr/local/mysql/bin/mysqld_mutil report

8. Check whether the recovery is successful.

Log on to the database and check whether the table data in the two databases is consistent.

In addition, you can use this backup to restore 3306 of the data and simulate data loss through the following operations.

1. Simulate data loss

drop database test;

2. Stop the 3306 instance 3. Restore data

<span style="font-size:14px;">innobackupex --defaults-file=/data/backup/conf/3306.cnf --copy-back /data/backup/full/2014-11-06_14-52-38/ </span>

4. Set permissions in the data directory of mysql

chown -R mysql:mysql   /data/mysql/mysql_3307/data 

5. Start the 3306 instance and check that all tables and data in the test database are restored.

Note:

1. If the disk space is sufficient during backup, we recommend that you create a copy for each backup to prevent backup data exceptions.

2. Pay attention to the merging sequence during recovery. For example, full backup> Incremental Backup 2> Incremental Backup 2, the sequence cannot be disordered. Otherwise, data inconsistency will be used.

Problems encountered and solutions 1. innobackupex: Error: Original data directory 'xxx' is not empty!

You can rename the original data directory to delete the existing data directory.

Mv/data/mysql/mysql_3307/data/mysql/mysql_3307/data_bak_20141107

2./ibdata1 can't be opened in read-write mode

 

Figure 4

View the users and user groups of the data and sub-directories. For root users and user groups, perform the following operations:

Chown-R mysql: mysql/data/mysql/mysql_3307/data

3. InnoDB: Cannot create./ib_logfile101

The following error occurs:

10:24:05 12406 [ERROR] InnoDB: Cannot create./ib_logfile101 10:24:05 12406 [ERROR] Plugin 'innodb' init function returned error. 10:24:05 12406 [ERROR] Plugin 'innodb' registry as a storage engine failed. 10:24:05 12406 [ERROR] Unknown/unsupported storage engine: InnoDB 10:24:05 12406 [ERROR] Aborting

To solve the previous problem, delete or rename all the ib _ * files in the data directory. We recommend that you rename the files.