Centos uses xtrabackup to back up and restore MySQL

Centos uses xtrabackup to back up and restore MySQL

1. Scope of use of xtrabackup

Xtrabackup: used for hot backup of innodb and xtradb engine data tables. Other types of tables cannot be backed up or the data table structure cannot be backed up;

Innobackupex: a perl script encapsulated by xtrabackup, which provides the ability to back up the myisam table (mainly used in this script ).

2. Install xtrabackup

Wgethttps: // response

Rpm ivhpercona-xtrabackup-2.1.2-612.rhel6.i686.rpm

Note: you can install the rpm dependency package as instructed.

3. After installing the software, you can directly use it.

Eg1: Full backup database

Innobackupex -- user = root -- password = xxxxx -- defaults-file =/etc/my. cnf -- database = phpcms/mybak/

Note:

/Etc/my. cnf, You need to configure the datadir path

[Mysqld]

Datadir =/usr/local/mysql/data # Add database files to the end of the [mysqld] Unit

---------------------------------------------------------------------------

-- Database = phpcms # indicates the database to be backed up. If it is not added, it indicates backing up all databases (both full backup)

/Tmp/# destination location of the Backup

---------------------------------------------------------------------------

[Root @ mysql 2015-10-19_21-06-56] # ll # backup information

Total 18468

-Rw-r -- 1 root 260 Oct 19 backup-my.cnf

-Rw-r ----- 1 root 18874368 Oct 19 21:06 ibdata1

Drwxr-xr-x 2 root 16384 Oct 19 21:06 phpcms

-Rw-r -- 1 root 13 Oct 19 21:06 xtrabackup_binary

-Rw-r -- 1 root 23 Oct 19 21:06 xtrabackup_binlog_info

-Rw-r ----- 1 root 89 Oct 19 21:06 xtrabackup_checkpoints

-Rw-r ----- 1 root 2560 Oct 19 21:06 xtrabackup_logfile

Eg2: Backup Packaging

Innobackupex -- user = root -- password = xxxxxxx -- defaults-file =/etc/my. cnf -- database = phpcms -- stream = tar/mybak>/mybak/phpcms.tar

Note:

-- Stream = tar # indicates the packaging method.

Eg3: complete backup and compress

Innobackupex -- user = root -- password = xxxxxxx -- defaults-file =/etc/my. cnf -- database = phpcms -- stream = tar/mybak/| gzip>/mybak/phpcms.tar.gz

Eg4: Remote Backup Packaging

Innobackupex -- user = root -- password = xxxxxx -- defaults-file =/etc/my. cnf -- database = phpcms -- stream = tar/mybak/| ssh root@192.168.1.124 cat ">"/tmp/phpcms.tar

Note:

Root@192.168.1.124's password:> log scanned up to (1668412)

[01] Streaming./ibdata1

> Log scanned up to (1668412)

> Log scanned up to (1668412)

> Log scanned up to (1668412) # scanning is ongoing. During this period, you need to enter the password of the remote backup host (the real environment still passes the certificate acceptance)

Eg5: Incremental Backup

Innobackupex -- user = root -- password = xxxxxx -- database = phpcms -- incremental

-- Incremental-basedir =/mybak/2015-10-19_21-06-56 // mybak/add/

# Incremental backup is mainly determined by the lsn representation (for the innodb engine)

[Root @ mysql 2015-10-19_21-06-56] # cat xtrabackup_checkpoints

Backup_type = full-prepared # global backup

From_lsn = 0

To_lsns = 1668412

Last_lsn = 1668412 # End position

Compact = 0

[Root @ mysql 2015-10-19_21-48-20] # cat xtrabackup_checkpoints

Backup_type = incremental # incremental Backup

From_lsn = 1668412 # The "start position" is the same as the "End position" above.

To_lsns = 1768416

Last_lsns = 1768416

Compact = 0

Note:

Note: innobackupex Incremental backup is only for engines that support transactions such as InnoDB. For engines such as MyISAM, it is still full backup.

4. data restoration

Eg1: complete Backup recovery:

Step 1: Delete the database to be recovered

/Etc/init. d/mysql stop

Mv/usr/local/mysql/data // usr/local/mysql/data/

Mkdir/usr/local/mysql/data/# The directory must be empty when it is restored.

Step 2: Apply the logs in the backup file to the data file in the backup file.

Innobackupex -- user = root -- password = xxxxxx -- defaults-file =/etc/my. cnf -- apply-log/mybak/2015-10-19_21-06-56/

Here -- apply-log indicates that the log is applied to the data file, and then the data in the backup file is restored to the database:

-- Defaults-file: the destination location for data restoration

Innobackupex -- user = root -- password = xxxxxx -- defaults-file =/etc/my. cnf -- copy-back/mybak/2015-10-19_21-06-56/

-- Copy-back indicates data recovery. After the data recovery is complete, you need to modify the permissions of the relevant files to start the mysql database properly.

Chown-R mysql: mysql/usr/local/mysql/data/

Service mysql start

Note: if the data is on the remote host, first copy the full backup to the local host. If the data is a tar package, You Need To unpackage the package first. The unpackage command is tar-izxvf dbbackup20110809.tar, the-I parameter must be used here. Then perform the recovery operation.

Eg2: restore a single database

1. cp/etc/my. cnf/mybak/

2. vim/mybak/my. cnf

3. vim/mybak/my. cnf

Datadir =/usr/local/mysql/data/phpcms # Make sure the directory is empty

4. perform the following operations:

Innobackupex -- user = root -- password = xxxxxx -- defaults-file =/mybak/my. cnf -- apply-log/mybak/2015-10-19_21-06-56/

Innobackupex -- user = root -- password = xxxxxx -- defaults-file =/mybak/my. cnf -- copy-back/mybak/2015-10-19_21-06-56/

5. copy the recovered data to the specified directory.

Eg3: Incremental Backup recovery:

The Incremental Backup recovery steps are basically the same as the full backup recovery steps, but the process of applying logs is slightly different. During Incremental Backup recovery, all incremental backups are first applied to the full backup data file, and then the data in the full backup is restored to the database. The command is as follows:

Application's first Incremental Backup

Innobackupex -- user = root -- password = xxxxxx -- defaults-file =/etc/my. cnf -- apply-log/mybak/2015-10-19_21-06-56/-- incremental-dir =/mybak/add/2015-10-19_21-48-20/

Application's second Incremental Backup

Innobackupex -- user = root -- password = xxxxxx -- defaults-file =/etc/my. cnf -- apply-log/mybak/2015-10-19_21-06-56/-- incremental-dir =/mybak/add/2015-10-19_21-48-54/

Restore data from the full backup to the database

Innobackupex -- user = root -- password = xxxxxx -- defaults-file =/etc/my. cnf -- copy-back/mysqlbackup/full/2015-10-19_21-06-56/

Here, -- incremental-dir specifies the location of the incremental backup to be restored