使用innobackupex备份MySQL数据库
安装innobackupex
安装yum扩展源：
[root@adailinux ~]# rpm -ivh http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
安装innobackupex
[root@adailinux ~]# yum install -y percona-xtrabackup
进行全量备份
说明： 使用xtrabackup进行备份需要使用mysql用户，该用户需要有备份的权限。
创建mysql用户
创建mysql用户：[root@adailinux ~]# mysql -uroot -p'123456'Welcome to the MySQL monitor.mysql> grant RELOAD, LOCK TABLES, REPLICATION CLIENT on *.* to 'bakuser'@'localhost' identified by '123456';Query OK, 0 rows affected (0.13 sec)#该用户只需要有备份权限即可，所以在创建用户时只授予其部分权限mysql> flush privileges；Query OK, 0 rows affected (0.05 sec)#刷新！mysql> quitBye
全量备份
创建一个目录用于存放备份文件：[root@adailinux ~]# mkdir /data/backup开始备份：[root@adailinux ~]# innobackupex --defaults-file=/etc/my.cnf --user=bakuser --password='123456' -S /tmp/mysql.sock /data/backup至此，备份完成！
说明：
--defaults-file：指定mysql的配置文件（该参数必须放在首位） --user：指定用于备份的mysql用户 --password：mysql用户密码 -S：=socket，指定MySQL的socket文件（也可以使用-h，该参数非必须！）查看备份文件：
[root@adailinux ~]# cd /data/backup/[root@adailinux backup]# ls2017-08-23_08-49-22[root@adailinux backup]# ls 2017-08-23_08-49-22/backup-my.cnf mysql test xtrabackup_infoibdata1 performance_schema xtrabackup_checkpoints xtrabackup_logfile
注： ibdata1为核心文件，其中存放的是：储存格式；INNODB类型数据状态下，ibdata用来储存文件的数据，而库名的文件夹里面的那些表文件只是结构而已。
恢复数据库
在恢复数据库之前需要先停止MySQL服务！
终止mysql服务：[root@adailinux backup]# /etc/init.d/mysqld stopShutting down MySQL.. SUCCESS!
恢复准备：
[root@adailinux backup]# mv /data/mysql /data/mysql.bak[root@adailinux backup]# mkdir /data/mysql[root@adailinux backup]# chown mysql:mysql /data/mysql#进行备份前将原/data/mysql中文件清空#在此只为学习，所以只对该文件进行更名，然后重建该目录并更改权限
开始恢复：
匹配数据文件：
[root@adailinux backup]# innobackupex --use-memory=256M --apply-log /data/backup/2017-08-23_08-49-22
说明： 进行数据库恢复时，先匹配用于恢复的数据文件。
--use-memory：指定执行数据库恢复操作时的运行内存（添加该选项的目的通过指定其运行内存来加快恢复速度，可不加该参数）。 --apply-log：指定要恢复的数据文件（来自备份文件）恢复：
[root@adailinux backup]# innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup/2017-08-23_08-49-22/至此，数据库恢复完成！
说明：
--copy-back：指定用于恢复的数据文件目录增量备份
增量备份是在全量备份的基础上进行的。
注： 该过程根据man文档进行操作。Incremental Backups with innobackupex As not all information changes between each backup, the incremental backup strategy uses this to reduce the storage needs and the duration of making a backup. This can be done because each InnoDB page has a log sequence number, LSN, which acts as a version number of the entire database. Every time the database is modified, this number gets incremented. An incremental backup copies all pages since a specific LSN. Once the pages have been put together in their respective order, applying the logs will recreate the process that affected the database, yielding the data at the moment of the most recently created backup. Creating an Incremental Backups with innobackupex First, you need to make a full backup as the BASE for subsequent incremental backups: $ innobackupex /data/backups This will create a timestamped directory in /data/backups. Assuming that the backup is done last day of the month, BASEDIR would be /data/back‐ ups/2013-03-31_23-01-18, for example. NOTE: You can use the innobackupex --no-timestamp option to override this behav‐ ior and the backup will be created in the given directory. If you check at the xtrabackup-checkpoints file in BASE-DIR, you should see something like: backup_type = full-backuped from_lsn = 0 to_lsn = 1626007 last_lsn = 1626007 compact = 0 recover_binlog_info = 1 To create an incremental backup the next day, use the --incremental option and provide the BASEDIR: $ innobackupex --incremental /data/backups --incremental-basedir=BASEDIR and another timestamped directory will be created in /data/backups, in this example, /data/backups/2013-04-01_23-01-18 containing the incremental backup. We will call this INCREMENTAL-DIR-1. If you check at the xtrabackup-checkpoints file in INCREMENTAL-DIR-1, you should see something like: backup_type = incremental from_lsn = 1626007 to_lsn = 4124244 last_lsn = 4124244 compact = 0 recover_binlog_info = 1 Creating another incremental backup the next day will be analogous, but this time the previous incremental one will be base: $ innobackupex --incremental /data/backups --incremental-basedir=INCREMENTAL-DIR-1 Yielding (in this example) /data/backups/2013-04-02_23-01-18. We will use INCREMENTAL-DIR-2 instead for simplicity. At this point, the xtrabackup-checkpoints file in INCREMENTAL-DIR-2 should contain something like: backup_type = incremental from_lsn = 4124244 to_lsn = 6938371 last_lsn = 7110572 compact = 0 recover_binlog_info = 1 As it was said before, an incremental backup only copy pages with a LSN greater than a specific value. Providing the LSN would have produced directo‐ ries with the same data inside: innobackupex --incremental /data/backups --incremental-lsn=4124244 innobackupex --incremental /data/backups --incremental-lsn=6938371 This is a very useful way of doing an incremental backup, since not always the base or the last incremental will be available in the system. WARNING: This procedure only affects XtraDB or InnoDB-based tables. Other tables with a different storage engine, e.g. MyISAM, will be copied entirely each time an incremental backup is performed. Preparing an Incremental Backup with innobackupex Preparing incremental backups is a bit different than full ones. This is, per‐ haps, the stage where more attention is needed: • First, only the committed transactions must be replayed on each backup. This will merge the base full backup with the incremental ones. • Then, the uncommitted transaction must be rolled back in order to have a ready-to-use backup. If you replay the committed transactions and rollback the uncommitted ones on the base backup, you will not be able to add the incremental ones. If you do this on an incremental one, you won’t be able to add data from that moment and the remaining increments. Having this in mind, the procedure is very straight-forward using the --redo-only option, starting with the base backup: innobackupex --apply-log --redo-only BASE-DIR You should see an output similar to: 160103 22:00:12 InnoDB: Shutdown completed; log sequence number 4124244 160103 22:00:12 innobackupex: completed OK! Then, the first incremental backup can be applied to the base backup, by issu‐ ing: innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1 You should see an output similar to the previous one but with corresponding LSN: 160103 22:08:43 InnoDB: Shutdown completed; log sequence number 6938371 160103 22:08:43 innobackupex: completed OK! If no --incremental-dir is set, innobackupex will use the most recent subdi‐ rectory created in the basedir. At this moment, BASE-DIR contains the data up to the moment of the first incremental backup. Note that the full data will always be in the directory of the base backup, as we are appending the increments to it. Repeat the procedure with the second one: innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2 If the “completed OK!” message was shown, the final data will be in the base backup directory, BASE-DIR. NOTE: --redo-only should be used when merging all incrementals except the last one. That’s why the previous line doesn’t contain the --redo-only option. Even if the --redo-only was used on the last step, backup would still be consistent but in that case server would perform the rollback phase. You can use this procedure to add more increments to the base, as long as you do it in the chronological order that the backups were done. If you merge the incrementals in the wrong order, the backup will be useless. If you have doubts about the order that they must be applied, you can check the file xtra‐ backup_checkpoints at the directory of each one, as shown in the beginning of this section. Once you merge the base with all the increments, you can prepare it to roll back the uncommitted transactions: innobackupex --apply-log BASE-DIR Now your backup is ready to be used immediately after restoring it. This preparation step is optional. However, if you restore without doing the pre‐ pare, the database server will begin to rollback uncommitted transactions, the same work it would do if a crash had occurred. This results in delay as the database server starts, and you can avoid the delay if you do the prepare. Note that the iblog* files will not be created by innobackupex, if you want them to be created, use xtrabackup –prepare on the directory. Otherwise, the files will be created by the server once started.Restoring Incremental Backups with innobackupex After preparing the incremental backups, the base directory contains the same as a full one. For restoring it you can use: innobackupex --copy-back BASE-DIR and you may have to change the ownership as detailed on restor‐ ing_a_backup_ibk.
创建增量备份
备份1：
[root@adailinux mysql]# innobackupex --defaults-file=/etc/my.cnf --user=bakuser --password='123456' --incremental /data/backup --incremental-basedir=/data/backup/2017-08-23_08-49-22[root@adailinux backup]# ll总用量 0drwx------ 7 root root 235 8月 23 22:13 2017-08-23_08-49-22drwx------ 6 root root 210 8月 23 22:12 2017-08-23_21-56-12
备份2：
[root@adailinux backup]# innobackupex --defaults-file=/etc/my.cnf --user=bakuser --password='123456' --incremental /data/backup --incremental-basedir=/data/backup/2017-08-23_21-56-12[root@adailinux backup]# ll总用量 0drwx------ 7 root root 235 8月 23 22:13 2017-08-23_08-49-22drwx------ 6 root root 210 8月 23 22:12 2017-08-23_21-56-12drwx------ 7 root root 223 8月 23 22:13 2017-08-23_22-02-36
合并备份文件
[root@adailinux backup]# innobackupex --apply-log --redo-only /data/backup/2017-08-23_08-49-22[root@adailinux backup]# innobackupex --apply-log --redo-only /data/backup/2017-08-23_08-49-22 --incremental-dir=/data/backup/2017-08-23_21-56-12[root@adailinux backup]# innobackupex --apply-log /data/backup/2017-08-23_08-49-22 --incremental-dir=/data/backup/2017-08-23_22-02-36
注意： 关于--redo-only参数，在进行增量备份文件的合并时，除了 在进行最后一份增量文件的合并时不需要添加该参数 外，其余都要加上该参数！！！
恢复
[root@adailinux backup]# innobackupex --copy-back /data/backup/2017-08-23_08-49-22