Brief introduction:

Mysqldump is used to back up the database. This is a logical backup, so it is safe to use this backup method, and it is easy to use cross-platform and version. Logical backup takes a long time to back up and restore data when your database is large, so keep improving and use physical backup. Mysqlhotcopy is also not available because the online database tables use a hybrid engine MyISAM and Innodb. This tool is quite useful, but it only backs up MyISAM. Percona XtraBackup is an open source backup tool developed by Percona for physical hot backup of MySQL database. Innobackupex supports MyISAM and Innodb backups under XtraBackup. Install XtraBackup

shell > yum -y install https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4 -1.el6.x86_64.rpmCopy the code

# or

shell > wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4 -1.el6.x86_64.rpm shell > yum -y install epel-release shell > yum -y install rsync libev-devel numactl-devel Perl-dbd-mysql shell > RPM -ivh percona-xtrabackup-24-2.4.4-1.el6.x86_64. RPMCopy the code

Dependencies need to be resolved

Ii. Innobackupex is fully prepared

shell > innobackupex --user=user --password=passwd --no-timestamp ./2016/09-29/`date +%H-%M`.dbnameCopy the code

# –no-timestamp If not added, a timestamp is generated in the backup directory where the backup data is stored

/2016/09-29/ back up the entire database to a directory named./2016/09-29/

# although can use – stream = tar | gzip > way directly compressed and packaged, but backup rate has decreased, so I’d rather fast backup, and then to deal with packaging, compression.

# Complete scripts

shell > vim script/backupdb.sh #! /bin/bash # 0 0 */2 * * sh /root/script/backupdb.sh # author: Wang XiaoQiang 2016/09/29 dbname='alldatabases' dbuser='user' dbpassword='passwd' backup_dir='/data/backup_db' backup_log='/root/script/logs/backup.log' ymtime=`date +%Y-%m` dtime=`date +%d` wlog(){ echo -e "`date "+%F %T"` DBname:  $1 State: $2\n" >> $backup_log } [ !  -d $backup_dir/$ymtime ] && mkdir -p $backup_dir/$ymtime cd $backup_dir/$ymtime && /usr/bin/innobackupex --user=$dbuser  --password=$dbpassword --no-timestamp $dbname > /dev/null 2>&1 if [ $? -eq 0 ]; then wlog $dbname 'Backup success.' dbakfile=$dbname.$dtime.tar.gz tar zcf $dbakfile $dbname --remove-files [ $? -eq 0 ]  && wlog $dbakfile 'Packaging success.' || wlog $dbakfile 'Packaging failed.' else wlog $dbname 'Backup failed.' fi # EndCopy the code

Innobackupex full recovery

CD /data/backup_db switch to the backup directory shell > mv /usr/local/mysql-5.5.52/data data.old /usr/local/mysql-5.5.52/data data.old /usr/local/mysql-5.5.52/data data.old Innobackupex --apply-log allDatabase 161011 12:07:33 completed OK! 161011 12:07:33 completed OK! Innobackupex --copy-back alldatabase --defaults-file /etc/my.cnf Error: innobackupex --copy-back alldatabase Datadir must be specified. The default configuration file /etc/my.cnf was specified. Datadir = /usr/local/mysql-5.5.52/data shell > innobackupex 161011 12:17:52 completed OK! 161011 12:17:52 completed OK! Shell > ll -d /usr/local/mysql-5.5.52/data drwxr-x-- 6 root root 4096 10月 11 12:17 /usr/local/mysql-5.5.52/data shell > Mysql /usr/local/mysql-5.5.52/data shell > /etc/init.d/mysql.server startCopy the code

Backup restored! # Review the entire recovery process and find a problem: everything in the previous database is gone… Innobackupex only works with a backup of one MySQL library other than the system library. Databases can be used to specify which database is backed up. The result is all databases. # 1, use Innobackupex to backup the whole database, then restore to a new database, then use mysqldump to backup the single database, and then import it into the database that needs to be restored (take the recovery time into consideration). # 2. Use Innobackupex to backup MySQL Server with only one business library and restore it to MySQL Server with only one business library. If you want to back up a database and the database is small, you are advised to use mysqldump for backup.

3. Add Innobackupex

Mysql > create database test_incre character set utf8; mysql> create database test_incre character set utf8; mysql> create table test_incre.incre(id int(10) not null,name varchar(20)); mysql> insert into test_incre.incre values(1,'Wang XiaoQiang'); mysql> quit shell > innobackupex --user=user --password=password --database=test_incres --no-timestamp /data/backup_db/test_increCopy the code

Databases are used to compare du-sh with databases. Databases are used to compare du-sh with databases.

Mysql > insert into test_incre. Incre values(2,'Xiao GuaiShou'); mysql> quit shell > innobackupex --user=user --password=password --database=test_incres --no-timestamp --incremental-basedir=/data/backup_db/test_incre --incremental /data/backup_db/test_incre01Copy the code

# –incremental-basedir=/data/backup_db/test_incre Specifies the full backup path for the first time # –incremental parameter Description This is a single incremental and specifies the incremental path

Mysql > create table du-sh; mysql > create table du-sh; mysql > create table du-sh; mysql > create table du-sh

Mysql > insert into test_incre. Incre values(3,'King'); mysql> quit shell > innobackupex --user=user --password=password --database=test_incres --no-timestamp --incremental-basedir=/data/backup_db/test_incre01 --incremental /data/backup_db/test_incre02Copy the code

Incremental-basedir specifies the first incremental directory and then the second incremental directory

Shell > cat test_incre/xtrabackup_checkpoints # backup_type This is a complete setup, From_lsn The value ranges from 0 to 1601917. Backup_type = full-backupED froM_Lsn = 0 TO_Lsn = 1601917 last_lsn = 1601917 Compact = 0 Recover_binlog_info = 0 shell > cat test_incre01/xtrabackup_checkpoints # backup_type This is an increase, From_lsn Start from LSN 1601917 to LSN 1602789 backup_TYPE = Incremental froM_Lsn = 1601917 TO_lsn = 1602789 last_lsn = 1602789 compact = 0 recover_binlog_info = 0 shell > cat test_incre02/xtrabackup_checkpoints # backup_type hint; This is an increase, From_lsn Start from LSN 1602789 to 1603652 backup_TYPE = Incremental froM_lsn = 1602789 to_lsn = 1603652 last_lsn = 1603652 compact = 0 recover_binlog_info = 0Copy the code

Databases are full backups for MyISAM, and there is no databases at all.

Add a script

4. Innobackupex backup restore

CD /data/backup_db shell > mv /usr/local/mysql-5.5.52/data Innobackupex --apply-log -- redore-only test_incre -- redore-only Shell > innobackupex --apply-log --redo-only test_incre -- increment-dir =/data/backup_db/test_incre01 # --incremental-dir specifies the first incremental directory (absolute path) shell > innobackupex --apply-log test_incre --incremental-dir=/data/backup_db/test_incre02 Shell > innobackupex --copy-back test_incre # The directory is datadir specified in my.cnf shell > chown -r mysql.mysql /usr/local/mysql-5.5.52/data shell > /etc/init.d/mysql.server Shell > mysql -uuser -ppassword -a mysql> select * from test_incre. Incre; +----+----------------+ | id | name | +----+----------------+ | 1 | Wang XiaoQiang | | 2 | Xiao GuaiShou | | 3 | King | +----+----------------+Copy the code

# OK, no problem!