Xtrabackup物理备份Mysql（Innodb引擎）-全备份和增量备份

环境：Vmware Workstation 10，CentOS-7-x86_64-DVD-1511.iso，Xshell 4.0，ip：192.168.216.138.

参照Linux_Mysql 安装笔记安装部署Mysql。

数据库准备

[root@localhost ~]# mysql -uroot -p

创建一个备份用户，并授予权限

安装xtrabackup

[root@localhost ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/tarball/percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz

[root@localhost ~]# tar -zxvf percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz -C /usr/local/

[root@localhost ~]# mv /usr/local/percona-xtrabackup-2.4.7-Linux-x86_64/ /usr/local/xtrabackup

[root@localhost ~]# xtrabackup

-bash: xtrabackup: 未找到命令

[root@localhost ~]# echo “export PATH=$PATH:/usr/local/xtrabackup/bin” >> /etc/profile

[root@localhost ~]# source /etc/profile

[root@localhost ~]# xtrabackup

xtrabackup version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
Open source backup tool for InnoDB and XtraDB

================================================

全备份

查看数据

[root@localhost ~]# ll /var/lib/mysql

总用量 110604
-rw-rw—-. 1 mysql mysql 56 6月 9 11:02 auto.cnf
-rw-rw—-. 1 mysql mysql 12582912 6月 9 18:34 ibdata1
-rw-rw—-. 1 mysql mysql 50331648 6月 9 18:34 ib_logfile0
-rw-rw—-. 1 mysql mysql 50331648 6月 9 11:02 ib_logfile1
drwx——. 2 mysql mysql 4096 6月 9 11:02 mysql
srwxrwxrwx. 1 mysql mysql 0 6月 9 11:02 mysql.sock
drwx——. 2 mysql mysql 4096 6月 9 11:02 performance_schema
drwx——. 2 mysql mysql 49 6月 9 18:32 xtra_test

开始全备份
注意：下面命令中“–”是两个“-”，csdn显示有误。

[root@localhost ~]# innobackupex –defaults-file=/etc/my.cnf –user=backup –password=backup –socket=/var/lib/mysql/mysql.sock /data/mysql_backup

… …
xtrabackup: Transaction log of lsn (1633865) to (1633865) was copied.
170609 18:46:30 completed OK!

查看备份

[root@localhost ~]# ll /data/mysql_backup/2017-06-09_18-46-28/

总用量 12312
-rw-r—–. 1 root root 418 6月 9 18:46 backup-my.cnf
-rw-r—–. 1 root root 12582912 6月 9 18:46 ibdata1
drwxr-x—. 2 root root 4096 6月 9 18:46 mysql
drwxr-x—. 2 root root 4096 6月 9 18:46 performance_schema
-rw-r—–. 1 root root 113 6月 9 18:46 xtrabackup_checkpoints
-rw-r—–. 1 root root 492 6月 9 18:46 xtrabackup_info
-rw-r—–. 1 root root 2560 6月 9 18:46 xtrabackup_logfile
drwxr-x—. 2 root root 49 6月 9 18:46 xtra_test

利用 –apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。

[root@localhost ~]# innobackupex –apply-log /data/mysql_backup/2017-06-09_18-46-28/

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown…
InnoDB: Shutdown completed; log sequence number 1634353
170609 18:52:33 completed OK!

查看备份

[root@localhost ~]# ll /data/mysql_backup/2017-06-09_18-46-28/

总用量 131092
-rw-r—–. 1 root root 418 6月 9 18:46 backup-my.cnf
-rw-r—–. 1 root root 12582912 6月 9 18:52 ibdata1
-rw-r—–. 1 root root 50331648 6月 9 18:52 ib_logfile0
-rw-r—–. 1 root root 50331648 6月 9 18:52 ib_logfile1
-rw-r—–. 1 root root 12582912 6月 9 18:52 ibtmp1
drwxr-x—. 2 root root 4096 6月 9 18:46 mysql
drwxr-x—. 2 root root 4096 6月 9 18:46 performance_schema
-rw-r—–. 1 root root 113 6月 9 18:52 xtrabackup_checkpoints
-rw-r—–. 1 root root 492 6月 9 18:46 xtrabackup_info
-rw-r—–. 1 root root 8388608 6月 9 18:52 xtrabackup_logfile
drwxr-x—. 2 root root 49 6月 9 18:46 xtra_test

停止mysql

[root@localhost ~]# service mysqld stop

Redirecting to /bin/systemctl stop mysqld.service

删除数据

[root@localhost ~]# mv /var/lib/mysql /var/lib/mysql_backup

[root@localhost ~]# mkdir /var/lib/mysql

数据还原

[root@localhost ~]# innobackupex –defaults-file=/etc/my.cnf –copy-back /data/mysql_backup/2017-06-09_18-46-28/

170609 18:57:51 completed OK!

查看还原数据

[root@localhost ~]# ll /var/lib/mysql

总用量 122892
-rw-r—–. 1 root root 12582912 6月 9 18:57 ibdata1
-rw-r—–. 1 root root 50331648 6月 9 18:57 ib_logfile0
-rw-r—–. 1 root root 50331648 6月 9 18:57 ib_logfile1
-rw-r—–. 1 root root 12582912 6月 9 18:57 ibtmp1
drwxr-x—. 2 root root 4096 6月 9 18:57 mysql
drwxr-x—. 2 root root 4096 6月 9 18:57 performance_schema
-rw-r—–. 1 root root 492 6月 9 18:57 xtrabackup_info
drwxr-x—. 2 root root 49 6月 9 18:57 xtra_test

修改数据所有者

[root@localhost ~]# chown -R -v mysql:mysql /var/lib/mysql

changed ownership of “/var/lib/mysql” from root:root to mysql:mysql

Selinux对mysql放行

[root@localhost ~]# chcon -R -t mysqld_db_t /var/lib/mysql

[root@localhost ~]# setenforce 0

查看还原数据所属者

[root@localhost ~]# ll /var/lib/mysql

总用量 122892
-rw-r—–. 1 mysql mysql 12582912 6月 9 18:57 ibdata1
-rw-r—–. 1 mysql mysql 50331648 6月 9 18:57 ib_logfile0
-rw-r—–. 1 mysql mysql 50331648 6月 9 18:57 ib_logfile1
-rw-r—–. 1 mysql mysql 12582912 6月 9 18:57 ibtmp1
drwxr-x—. 2 mysql mysql 4096 6月 9 18:57 mysql
drwxr-x—. 2 mysql mysql 4096 6月 9 18:57 performance_schema
-rw-r—–. 1 mysql mysql 492 6月 9 18:57 xtrabackup_info
drwxr-x—. 2 mysql mysql 49 6月 9 18:57 xtra_test

重启mysql

[root@localhost ~]# service mysqld start

Redirecting to /bin/systemctl start mysqld.service

[root@localhost ~]# setenforce 1

查看恢复数据

mysql> select * from user;

+—-+———-+———-+
| id | username | password |
+—-+———-+———-+
| 1 | hahrfdah | lzg123 |
| 2 | zhangsan | 12345678 |
+—-+———-+———-+
2 rows in set (0.00 sec)

至此全备份完毕。

================================================

增量备份

先做全量备份
注意：下面命令中“–”是两个“-”，csdn显示有误。

[root@localhost ~]# innobackupex –defaults-file=/etc/my.cnf –user=backup –password=backup –socket=/var/lib/mysql/mysql.sock /data/mysql_full

170609 19:11:50 completed OK!

查看备份

[root@localhost ~]# ll /data/mysql_full/2017-06-09_19-11-48/

总用量 12312
-rw-r—–. 1 root root 418 6月 9 19:11 backup-my.cnf
-rw-r—–. 1 root root 12582912 6月 9 19:11 ibdata1
drwxr-x—. 2 root root 4096 6月 9 19:11 mysql
drwxr-x—. 2 root root 4096 6月 9 19:11 performance_schema
-rw-r—–. 1 root root 113 6月 9 19:11 xtrabackup_checkpoints
-rw-r—–. 1 root root 490 6月 9 19:11 xtrabackup_info
-rw-r—–. 1 root root 2560 6月 9 19:11 xtrabackup_logfile
drwxr-x—. 2 root root 49 6月 9 19:11 xtra_test

添加数据

增量备份

[root@localhost ~]# innobackupex –defaults-file=/etc/my.cnf –user=backup –password=backup –socket=/var/lib/mysql/mysql.sock –incremental –incremental-basedir=/data/mysql_full/2017-06-09_19-11-48/ /data/mysql_increment

xtrabackup: Transaction log of lsn (1637485) to (1637485) was copied.
170609 19:20:36 completed OK!

查看增量备份

[root@localhost ~]# ll /data/mysql_increment/2017-06-09_19-20-35/

总用量 332
-rw-r—–. 1 root root 418 6月 9 19:20 backup-my.cnf
-rw-r—–. 1 root root 311296 6月 9 19:20 ibdata1.delta
-rw-r—–. 1 root root 44 6月 9 19:20 ibdata1.meta
drwxr-x—. 2 root root 4096 6月 9 19:20 mysql
drwxr-x—. 2 root root 4096 6月 9 19:20 performance_schema
-rw-r—–. 1 root root 117 6月 9 19:20 xtrabackup_checkpoints
-rw-r—–. 1 root root 575 6月 9 19:20 xtrabackup_info
-rw-r—–. 1 root root 2560 6月 9 19:20 xtrabackup_logfile
drwxr-x—. 2 root root 75 6月 9 19:20 xtra_test

[root@localhost ~]# innobackupex –apply-log –redo-only /data/mysql_full/2017-06-09_19-11-48/

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown…
InnoDB: Shutdown completed; log sequence number 1634372
InnoDB: Number of pools: 1
170609 19:24:48 completed OK!

[root@localhost ~]# innobackupex –apply-log –redo-only /data/mysql_full/2017-06-09_19-11-48/ –incremental-dir=/data/mysql_increment/2017-06-09_19-20-35/

170609 19:26:05 completed OK!

停止mysql

[root@localhost ~]# service mysqld stop

Redirecting to /bin/systemctl stop mysqld.service

清空数据

[root@localhost ~]# mv /var/lib/mysql /var/lib/mysql_increment

[root@localhost ~]# mkdir /var/lib/mysql && ll /var/lib/mysql

总用量 0

数据还原

[root@localhost ~]# innobackupex –defaults-file=/etc/my.cnf –copy-back /data/mysql_full/2017-06-09_19-11-48/

170609 19:29:02 completed OK!

修改数据所有者

[root@localhost ~]# chown -R -v mysql:mysql /var/lib/mysql

changed ownership of “/var/lib/mysql” from root:root to mysql:mysql

[root@localhost ~]# ll /var/lib/mysql

总用量 110608
-rw-rw—-. 1 mysql mysql 56 6月 9 19:32 auto.cnf
-rw-r—–. 1 mysql mysql 12582912 6月 9 19:32 ibdata1
-rw-rw—-. 1 mysql mysql 50331648 6月 9 19:32 ib_logfile0
-rw-rw—-. 1 mysql mysql 50331648 6月 9 19:32 ib_logfile1
drwxr-x—. 2 mysql mysql 4096 6月 9 19:29 mysql
srwxrwxrwx. 1 mysql mysql 0 6月 9 19:32 mysql.sock
drwxr-x—. 2 mysql mysql 4096 6月 9 19:29 performance_schema
-rw-r—–. 1 mysql mysql 575 6月 9 19:29 xtrabackup_info
drwxr-x—. 2 mysql mysql 49 6月 9 19:29 xtra_test

[root@localhost ~]# chcon -R -t mysqld_db_t /var/lib/mysql

[root@localhost ~]# setenforce 0

重启mysql

[root@localhost ~]# service mysql start

Redirecting to /bin/systemctl start mysql.service

[root@localhost ~]# setenforce 1

查看增量备份

mysql> select * from user;

+—-+———-+———-+
| id | username | password |
+—-+———-+———-+
| 1 | hahrfdah | lzg123 |
| 2 | zhangsan | 12345678 |
| 3 | fffffff | 55146 |
| 4 | gggffgg | 6655555 |
+—-+———-+———-+
4 rows in set (0.00 sec)

至此增量备份完毕。