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

This article mainly tells you how to achieve mysql backup and data consistency check through xtrabackup. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope that this article on how to achieve mysql backup and data consistency check through xtrabackup can bring you some practical help.

Xtrabackup is a free database hot backup software open source by percona, which can back up the databases of InnoDB and XtraDB storage engines non-blocking (table locks are also required for MyISAM backups).

Compared with mysqldump backup, mysqldump is a logical backup, backup and recovery speed is slow, but backup files take up less space. While Xtrabackup is a physical backup, directly copy related files, backup and restore fast, backup files take up a lot of space.

After Xtrabackup installation is complete, two of the more important backup tools are innobackupex and xtrabackup. Among them, innobackupex is the encapsulation and function extension of xtrabackup by perl script.

Installation of Xtrabackup

Wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar

Tar zxvf percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz

Preparation for Xtrabackup backup

Xtrabackup requires operation rights to connect to the database and datadir, which mainly involves two types of user rights: 1, system users (used to execute xtrabackup or innobackupex) and 2, database users (users used in the database).

Create a backup user

Mysql > CREATE USER 'bkuser'@'ip' identified by' password'

Mysql > grant reload,lock tables,replication client on *. * 'bkuser'@'ip' identified by' password'

Mysql > flush privileges

Start to be ready.

Innobackupex-defaults-file=/etc/my.cnf-user=DBUSER-password=DBUSERPASS-slave-info-safe-slave-backup-parallel=4-no-timestamp-backup-rsync / path/to/BACKUP-DIR

-- scenarios where the parameter slave-info applies: suppose there are master library An and slave library B now. Now you want to add another standby library C, and let slave library C use master library An as master. Because the main library An is a production library, the pressure is generally high, so we back up a database on standby library B, then take the backup to C CVM and import it to C library, and then execute the change master command on C CVM: where master_host is the ip of A, and master_log_file and master_log_pos are the values in this xtrabackup_slave_info.

-- rsync this parameter is generally used when distributed database clusters

After the backup is created, the backup data at this time cannot be used for restore. You need to roll back uncommitted things, redo committed things, and keep database files consistent.

Innobackupex-apply-log-use-memory 4G / path/to/BACKUP-DIR

-- use-memory: specify the memory that can be used in the preparatory phase. The more memory, the faster the speed. The default is 10MB.

Recover data

# you need to ensure that the data directory is empty before restoring the data, and shut down the service before restoring.

[root@centos] # innobackupex-- defaults-file=/etc/my.cnf-- copy-back / path/to/BACKUP-DIR

# after restoring, the default is root permission, so modify the group and owner.

Chown-R mysql.mysql / data/dbdata

/ etc/rc.d/init.d/mysqld start

Xtrabackup incremental backup and recovery

# it should be noted that a complete backup is required before incremental backup, otherwise incremental backup is meaningless. And incremental backup can only be applied to InooDB or XtraDB tables. For MyISAM tables, incremental backups are the same as full backups.

[root@Vcentos] # innobackupex-defaults-file=/etc/my.cnf-user=root-password= PWD-incremental / backup/-incremental-basedir=/path/to/BACKUP-DIR

#-incremental / backup/ specifies the directory where incremental backup files are backed up

#-incremental-basedir specifies the directory of the last full or incremental backup

Check the location of binlog and datadir to prevent misoperation of binlog during mv datadir and affect recovery (binlog and datadir must be placed separately)

Show variables like'% log_bin%'

Show variables like'% datadir%'

Prepare incremental backup

Preparing an incremental backup requires two steps

You need to prepare everything first, but only redo the submitted things, and do not roll back the uncommitted things. Then apply to everything, which only redoes the submitted things and does not roll back the uncommitted things.

Roll back uncommitted items

In full, use-- redo-only to redo only committed things and not roll back uncommitted things.

[root@Vcentos] # innobackupex-- apply-log-- redo-only / path/to/BACKUP-DIR

Apply incremental backup

[root@Vcentos] # innobackupex-- apply-log / path/to/BACKUP-DIR-- incremental-dir=/path/to/BACKUP-DIR

When an incremental backup is applied, it can only be applied in the order of the backups, and if the order is wrong, the backup is not available. You can use xtrabackup-checkpoints to determine the order.

If the backup is not the last incremental backup, add-- redo-only

Restore incremental backup

[root@centos] # innobackupex-- defaults-file=/etc/my.cnf-- copy-back / path/to/BACKUP-DIR

For more information, please see https://max.book118.com/html/2017/0522/108428582.shtm

Innobackupex backup + full recovery of binlog logs see https://blog.csdn.net/zhang123456456/article/details/72954519

Mysql master-slave replication data consistency checksum repair method and automation implementation see https://blog.csdn.net/hangxing_2015/article/details/52585855

For more information on xtrabackup backup and restore, please see https://www.cnblogs.com/zhoujinyi/p/5893333.html.

For more information on mysql single table backup and recovery, please see https://blog.csdn.net/u012104666/article/details/80407953

Innobackupex-defaults-file=/etc/my.cnf-databases= ""-user-password-parallel=4 / mysql_backup/backup

Tar-cvf-xxx | pigz-p 8 > xxx.tar.gz

Tar-- use-compress-program=pigz-xvpf xxx.tar.gz

Innobackupex-defaults-file=/etc/my.cnf-use-memory=1G-apply-log xxx

Innobackupex-defaults-file=/etc/my.cnf-user=-password=-copy-back / mysql_fullbackup/xxx

Binary log format backup slave library

How to use xtrabackup to achieve mysql backup and data consistency check will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.