XtraBackup是Percona公司的開源項目，用以實現類似Innodb官方的熱備份工具InnoDB Hot Backup的功能，它支持在線熱備份(備份時不影響數據讀寫)。XtraBackup有很多功能和優點：例如支持全備、增量備份、部分備份；支持壓縮備份；備份不影響數據讀寫、事務等，但是也有缺陷不足：例如不支持離線備份、不支持直接備份到磁帶設備、不支持Cloud Back，MyISAM的備份也會阻塞。不過這些小瑕疵不影響XtraBackup成為一款流行的MySQL備份工具。另外，請注意XtraBackup只支持Linux平台，不支持Windows平台。
1、Percona Xtrabackup的安裝
(1)下載Xtracbackup軟體(俺的OS環境是CentOS 6.5 X64)
[root@mydb01 ~]# wget http://ftp.tu-chemnitz.de/pub/linux/dag/redhat/el6/en/x86_64/rpmforge/RPMS/libev-4.15-1.el6.rf.x86_64.rpm
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.12-1.el6.x86_64.rpm
(2)安裝依賴包及軟體
[root@mydb01 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Setting up Install Process
Package 4:perl-5.10.1-144.el6.x86_64 already installed and latest version
Package 4:perl-devel-5.10.1-144.el6.x86_64 already installed and latest version
Package libaio-0.3.107-10.el6.x86_64 already installed and latest version
Package 4:perl-Time-HiRes-1.9721-144.el6.x86_64 already installed and latest version
Package perl-DBD-MySQL-4.013-3.el6.x86_64 already installed and latest version
Resolving Dependencies
[root@mydb01 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
warning: libev-4.15-1.el6.rf.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY
Preparing… ########################################### [100%]
1:libev ########################################### [100%]
[root@mydb01 ~]# ls
[root@mydb01 perconaxtrabackupForEL6]# rpm -ivh percona-xtrabackup-24-2.4.12-1.el6.x86_64.rpm
warning: percona-xtrabackup-24-2.4.12-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing… ########################################### [100%]
1:percona-xtrabackup-24 ########################################### [100%]
2、Percona Xtrabackup的備份還原數據
(1)完全備份
[root@mydb01 ~]# grep “datadir” /etc/my.cnf#查看數據目錄所在的位置
datadir=/usr/local/mysql/data
[ root@mydb01~]# innobackupex –user=root –password=131468 /tmp/mysql-backup/
[ root@mydb01~]# ls /tmp/mysql-backup/#查看全備份的內容
2018-12-26_05-25-22
(2)增量備份
[root@mydb01 ~]# innobackupex –user=root –password=123 –incremental /tmp/mysql-backup/ –incremental-basedir=/tmp/mysql-backup/2018-12-26_05-25-22/
[ root@mydb01 ~]# innobackupex –user=root –password=123 –incremental /tmp/mysql-backup/ –incremental-basedir=/tmp/mysql-backup/2018-12-26_05-30-01/
[ root@mydb01 ~]# ls /tmp/mysql-backup/
2018-12-26_05-25-22 2018-12-26_05-30-01 2018-12-26_05-34-08
(3)還原數據
①停止mysql服務,然後清空數據文件目錄,恢復完成後再設置許可權
[ root@mydb01 ~]# service mysqld stop
[ root@mydb01 ~]# mv /var/lib/mysql /usr/lib/mysql.bak #這個是模擬不小心將數據刪掉
[ root@mydb01 ~]# mkdir /var/lib/mysql
[ root@mydb01 ~]# chown mysql. /var/lib/mysql -R
②還原數據
[ root@mydb01 ~]# innobackupex –apply-log –redo-only /tmp/mysql-backup/2018-12-26_05-25-22/#讀取全備份的事務日誌,不回滾
[ root@mydb01 ~]# innobackupex –apply-log –redo-only /tmp/mysql-backup/2018-12-26_05-25-22/ –incremental-dir=/tmp/mysql-backup/2018-12-26_05-30-01/#將第一次增量備份的事務日誌導入到讀取全備份的事務日誌中,不回滾
[ root@mydb01 ~]# innobackupex –apply-log –redo-only /tmp/mysql-backup/2018-12-26_05-25-22/ –incremental-dir=/tmp/mysql-backup/2018-12-26_05-34-08/#將第二次增量備份的事務日誌導入到讀取全備份的事務日誌中,不回滾
[ root@mydb01 ~]# innobackupex –apply-log /tmp/mysql-backup/2018-12-26_05-25-22/#回滾事務日誌
[ root@mydb01 ~]# innobackupex –copy-back /tmp/mysql-backup/2018-12-26_05-25-22/#還原數據
[ root@mydb01 ~]# chown mysql. /var/lib/mysql -R #修改許可權
[ root@mydb01 ~]# systemctl start mysql#啟動服務
以下文章點擊率最高
Loading…