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

Xtabakcup

*

Installation

*

-1. Compile and install percona-xtrabackup

Yum-y install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr\

Bison libtool zlib-devel libgcrypt-devel libcurl-devel crypt* libgcrypt*\

Python-sphinx openssl imake libxml2-devel expat-devel ncurses5-devel ncurses-devle vim-common libgpg-error-devel libidn-devel

NOTE:2.2.16 requires two dependent packages for python-sphinx openssl.

-2. Install related plug-ins

Yum-y install perl-DBI

Yum-y install perl-DBD-MySQL

Yum-y install perl-Time-HiRes

Yum-y install perl-IO-Socket-SSL

-3. Compile and install components

Http://dist.schmorp.de/libev/

# tar xvzf libev-4.22.tar.gz

#. / configure

# make

# make install

# tar xvzf mysql-boost-5.7.15.tar.gz (for compile time)

-- 4. Install xtrabackup

Tar xvzf percona-xtrabackup-2.4.4.tar.gz

Cd percona-xtrabackup-2.4.4.tar.gz

Cmake. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql\

-DSYSCONFDIR=/etc\

-DWITH_BOOST=/home/mysql-5.7.15/boost

Make

Make install

-Test results

# xtrabackup-version

Xtrabackup version 2.4.4 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: df58cf2)

-4. Backup preparation work

Permissions and connections

Xtrabackup requires permissions to connect to the database and datadir operations.

Xtrabackup or innobackupex are designed to have two types of user rights during use:

1. System user, which is used to call innobackupex or xtrabackup

two。 Database users, users used in the database

Required permissions: connect to the service to perform backups and require read,write and execute permissions on the datadir. The following permissions are required in the database:

RELOAD and LOCK TABLES permissions are used to execute FLUSH TABLES WITH READ LOCK.

In order to get the binary log location, REPLICATION CLIENT

CREATE TABLESPACE permissions in order to import table, user table level recovery

SUPER permissions are backed up in slave to start and shut down slave threads

Mysql > CREATEUSER'bkpuser'@'localhost' IDENTIFIED BY's3cret'

Mysql > GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON*.*TO'bkpuser'@'localhost'

Mysql > FLUSH PRIVILEGES

-5. Full backup and incremental backup

Full backup

$innobackupex-user=DBUSER-password=DBUSERPASS / data/backups

From the information, you will find that the backup was created at / data/backups/2016-10-2500-00-09

Internal mechanism: when backing up, innobackupex will call xtrabackup to back up innodb tables and copy all table definitions, tables from other engines (MyISAM,MERGE,CSV,ARCHIVE).

Incremental backup

Incremental backup is to reduce space usage and backup time.

The implementation of incremental backup depends on the LSN (log sequence number) on the innodb page, and each modification to the database will cause the LSN to increase itself.

Incremental backups copy all data pages after the specified LSN

$innobackupex-- user=DBUSER-- password=DBUSERPASS-target-dir=/data/backups/1-- incremental-basedir=/data/backups/2016-10-2500-00-09 /

There is an incremental backup new directory under the 1 directory.

Then create an incremental backup

Create an incremental backup based on incremental backup 1, which is recorded as incremental backup 2.

$innobackupex-user=DBUSER-password=DBUSERPASS-target-dir=/data/backups/2-incremental-basedir=/data/backups/1/2016-10-25 million 10-10-11 /

Incremental backup alternative

# you can use the method of specifying-incremental-lsn instead of-incremental-basedir to create incremental backups.

# innobackupex-incremental / data/backups-incremental-lsn=1291135

# innobackupex-incremental / data/backups-incremental-lsn=1358967

-6. Recover data

Prepare incremental backup

1. You need to prepare everything first, but only redo committed transactions, do not roll back uncommitted transactions, and then apply to complete transactions, which only redo committed transactions and do not roll back uncommitted transactions

two。 Roll back uncommitted transactions

If the uncommitted transaction has been rolled back, incremental backups can no longer be applied

In full, use-redo-only to do only committed transactions and do not roll back uncommitted transactions

Innobackupex-- apply-log-- redo-only / data/backups/2016-10-2500-00-09

Apply the first incremental backup

Innobackupex-- apply-log-- redo-only / data/backups/2016-10-250000-09-- incremental-dir=/data/backups/1/2016-10-250010-10-11 /

Apply the second incremental backup

Innobackupex-- apply-log / data/backups/2016-10-250000-09-- incremental-dir=/data/backups/2/2016-10-250016-30-32 /

Output the results and note the changes in LSN:

162026 22:08:43 InnoDB: Shutdown completed; log sequence number 1358967

161026 22:08:43 innobackupex: completed OK!

Roll back uncommitted transactions

When all incremental backups are applied, you need to roll back all transactions to complete (uncommitted transactions need to be rolled back if-redo-only is added in the last step, and if not executed, the service will handle uncommitted transactions during the service startup phase).

Innobackupex-- apply-log / data/backups/2016-10-2500-00-09

Restore operation

Service mysqld stop

Delete all data in the database data directory or back up all data in the data directory

Restore:

Innobackupex-- copy-back / data/backups/2016-10-2500-00-09

Change the permissions of the database directory

Chown-R mysql:mysql data/mysqldb

Start the database

Service mysqld start

Restore order:

# complete application log

[root@client103] # innobackupex-- apply-log / tmp/backup/2014-01-11 15-37-31 /

# the first incremental backup of the application log. After the log is applied, it will be merged into full recovery, and full recovery will be used.

[root@client103] # innobackupex-- apply-log / tmp/backup/2014-01-1111 / 15-37-31 /-- incremental-dir=/tmp/backup/2014-01-11 / 15-45-06 /

# apply the log for the second incremental backup and merge it into the full

[root@client103] # innobackupex-- apply-log / tmp/backup/2014-01-11 15-37-31 /-- incremental-dir=/tmp/backup/2014-01-11 15-46-33 /

# at this time, the two incremental backups are actually merged into full backup, and you only need to use full backup to restore.

# simulate data failure [delete the data directory of the database and perform the following command restore]

[root@client103] # innobackupex-- copy-back / tmp/backup/2014-01-11 15-37-31 /

# the default permissions of the restored data are problematic, so you need to modify the permissions of the data directory, as follows

[root@client103] # chown-R mysql:mysql / var/lib/mysql/

# at this point, the data recovery is complete. Restart the database service and test it. Everyone test by yourself, if you have any questions, leave a message and feedback!

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.