首页 > 代码库 > Percona XtraBackup热备份实践
Percona XtraBackup热备份实践
实验环境:
一、操作系统
[root@DB-SERVER ~]# cat /etc/redhat-release
CentOS release 6.8 (Final)
[root@DB-SERVER ~]# uname -a
Linux DB-SERVER 2.6.32-642.el6.x86_64 #1 SMP Tue May 10 17:27:01 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
[root@DB-SERVER ~]#
二、MySQL和percona版本及安装
[root@DB-SERVER tools]# ll
total 5
-rw-r--r-- 1 root root 5691656 Apr 5 2015 cmake-2.8.8.tar.gz
-rw-r--r-- 1 root root 24596474 Apr 5 2015 mysql-5.5.32.tar.gz
-rw-r--r-- 1 root root 5664452 Oct 17 2015 percona-xtrabackup-2.3.2-1.el6.x86_64.rpm
[root@DB-SERVER tools]#
①MySQL安装
[root@DB-SERVER ~]# cd /opt/tools/
[root@DB-SERVER tools]# tar xf cmake-2.8.8.tar.gz
[root@DB-SERVER cmake-2.8.8]# ./configure
[root@DB-SERVER cmake-2.8.8]# gmake
[root@DB-SERVER cmake-2.8.8]# gmake install
[root@DB-SERVER cmake-2.8.8]# cd ..
[root@DB-SERVER tools]# yum install ncurses-devel -y
[root@DB-SERVER tools]#groupadd mysql
[root@DB-SERVER tools]#useradd mysql -s /sbin/nologin -M -g mysql
[root@DB-SERVER tools]#tar zxf mysql-5.5.32.tar.gz
[root@DB-SERVER tools]#cd mysql-5.5.32
[root@DB-SERVER mysql-5.5.32]#
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \
-DMYSQL_DATADIR=/application/mysql-5.5.32/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0
[root@DB-SERVER mysql-5.5.32]#make && make install
[root@DB-SERVER mysql-5.5.32]#cd ..
[root@DB-SERVER tools]#cp mysql-5.5.32/support-files/my-small.cnf /etc/my.cnf
[root@DB-SERVER tools]#echo ‘export PATH=/application/mysql/bin:$PATH‘>>/etc/profile
[root@DB-SERVER tools]#chown -R mysql:mysql /application/mysql/data/
[root@DB-SERVER tools]#cd /application/mysql/scripts/
[root@DB-SERVER scripts]#./mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data/ --user=mysql
[root@DB-SERVER scripts]#cd /opt/tools/mysql-5.5.32
[root@DB-SERVER mysql-5.5.32]#cp support-files/mysql.server /etc/init.d/mysqld
[root@DB-SERVER mysql-5.5.32]#chmod +x /etc/init.d/mysqld
[root@DB-SERVER mysql-5.5.32]#netstat -lntup|grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 18591/mysqld
[root@DB-SERVER mysql-5.5.32]#
修改root登陆了密码:
[root@DB-SERVER mysql-5.5.32]#/application/mysql/bin/mysqladmin -u root password ‘new-password‘
[root@DB-SERVER mysql-5.5.32]#
②percona的安装
[root@DB-SERVER ~]# cd /opt/tools/
[root@DB-SERVER tools]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.2/binary/redhat/6/x86_64/percona-xtrabackup-2.3.2-1.el6.x86_64.rpm
[root@DB-SERVER tools]#
安装依赖库
[root@DB-SERVER tools]#yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
[root@DB-SERVER tools]# wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm
[root@DB-SERVER tools]#rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
[root@DB-SERVER tools]#rpm -ivh percona-xtrabackup-2.3.2-1.el6.x86_64.rpm
[root@DB-SERVER tools]#mkdir -p /databackup/xtrabackup
[root@DB-SERVER tools]#mkdir -p /databackup/xtrabackuplog
[root@DB-SERVER tools]
③创建用于实践的数据库并插入数据
CREATE DATABASE opark;
CREATE TABLE `person` (
`number` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO person (number,name,birthday) VALUES ("0001", "John Poul", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0002", "John Hock", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0003", "Rick Hock", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0004", "Rick stone", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0005", "John Green", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0006", "John Halk", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0007", "Rick rose", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0008", "Rick kate", NOW());
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| opark |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use opark;
Database changed
mysql> select * from person;
+--------+------------+------------+
| number | name | birthday |
+--------+------------+------------+
| 1 | John Poul | 2016-09-18 |
| 2 | John Hock | 2016-09-18 |
| 3 | Rick Hock | 2016-09-18 |
| 4 | Rick stone | 2016-09-18 |
| 5 | John Green | 2016-09-18 |
| 6 | John Halk | 2016-09-18 |
| 7 | Rick rose | 2016-09-18 |
| 8 | Rick kate | 2016-09-18 |
+--------+------------+------------+
8 rows in set (0.01 sec)
mysql>
④创建备份用户和授权
mysql>grant SELECT,RELOAD,SHOW DATABASES,SUPER,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,EVENT,FILE on *.* to backup@‘localhost‘ identified by ‘MANAGER‘;
三、实战备份操作
（1）全备与恢复
①.全备操作
[root@DB-SERVER ~]# cd /databackup/
[root@DB-SERVER databackup]# ll
total 4
drwx------ 6 root root 4096 Sep 18 01:40 2016-09-18_01-40-28
[root@DB-SERVER databackup]#
[root@DB-SERVER databackup]# innobackupex --user=backup --password=‘MANAGER‘ /databackup/
160918 02:48:10 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
160918 02:48:13 Backup created in directory ‘/databackup//2016-09-18_02-48-10‘
160918 02:48:13 [00] Writing backup-my.cnf
160918 02:48:13 [00] ...done
160918 02:48:13 [00] Writing xtrabackup_info
160918 02:48:13 [00] ...done
xtrabackup: Transaction log of lsn (1609238) to (1609238) was copied.
160918 02:48:13 completed OK!
[root@DB-SERVER databackup]#
[root@DB-SERVER databackup]# ll
total 8
drwx------ 6 root root 4096 Sep 18 01:40 2016-09-18_01-40-28
drwx------ 6 root root 4096 Sep 18 02:48 2016-09-18_02-48-10
[root@DB-SERVER databackup]#
②.全备恢复操作
对于一般恢复，都是直接用备份文件还原，如果我们这里也是直接运用该备份文件，则可能会导致一些意想不到的问题，比如:备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件处于不一致的状态，我们现在就是要通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。所以我们要用如下命令进行检查
[root@DB-SERVER databackup]# innobackupex --apply-log /databackup/2016-09-18_02-48-10/
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 1609740
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1609750
160918 02:53:59 completed OK!
[root@DB-SERVER databackup]#
关闭数据库，模拟数据丢失，可以将MySQL的数据目录删除，我们这里为了演示方便，直接把数据目录改名
[root@DB-SERVER ~]# /etc/init.d/mysld stop
Shutting down MySQL. SUCCESS!
[root@DB-SERVER ~]# cd /application/mysql-5.5.32/
[root@DB-SERVER mysql-5.5.32]# ll
total 84
drwxr-xr-x 2 mysql mysql 4096 Sep 17 23:36 bin
-rw-r--r-- 1 mysql mysql 17987 Jul 2 2013 COPYING
drwx------ 6 mysql mysql 4096 Sep 18 02:54 data
drwxr-xr-x 6 mysql mysql 4096 Sep 18 01:40 data.backup
drwxr-xr-x 2 mysql mysql 4096 Sep 17 23:36 docs
drwxr-xr-x 3 mysql mysql 4096 Sep 17 23:36 include
-rw-r--r-- 1 mysql mysql 7470 Jul 2 2013 INSTALL-BINARY
drwxr-xr-x 3 mysql mysql 4096 Sep 17 23:36 lib
drwxr-xr-x 4 mysql mysql 4096 Sep 17 23:36 man
drwxr-xr-x 10 mysql mysql 4096 Sep 17 23:36 mysql-test
-rw-r--r-- 1 mysql mysql 2496 Jul 2 2013 README
drwxr-xr-x 2 mysql mysql 4096 Sep 17 23:36 scripts
drwxr-xr-x 27 mysql mysql 4096 Sep 17 23:36 share
drwxr-xr-x 4 mysql mysql 4096 Sep 17 23:36 sql-bench
drwxr-xr-x 3 mysql mysql 4096 Sep 17 23:36 support-files
drwxr-xr-x 2 mysql root 4096 Sep 18 02:54 tmp
[root@DB-SERVER mysql-5.5.32]# mv data data.backup
[root@DB-SERVER mysql-5.5.32]# mkdir data
[root@DB-SERVER mysql-5.5.32]#
[root@DB-SERVER mysql-5.5.32]# ll data
total 2
[root@DB-SERVER mysql-5.5.32]#
目录data下没有数据，接下来执行恢复操作
[root@DB-SERVER databackup]# innobackupex --copy-back /databackup/2016-09-18_02-48-10/
160918 02:57:41 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0)
160918 02:57:41 [01] Copying ib_logfile0 to /application/mysql-5.5.32/data/ib_logfile0
160918 02:57:41 [01] ...done
160918 02:57:42 [01] Copying ./opark/db.opt to /application/mysql-5.5.32/data/opark/db.opt
160918 02:57:42 [01] ...done
160918 02:57:42 [01] Copying ./test/db.opt to /application/mysql-5.5.32/data/test/db.opt
160918 02:57:42 [01] ...done
160918 02:57:42 completed OK!
[root@DB-SERVER databackup]#
再看MySQL数据目录，数据目录data下数据已经过来了
[root@DB-SERVER mysql-5.5.32]# ll data
total 116756
-rw-r----- 1 root root 18874368 Sep 18 02:57 ibdata1
-rw-r----- 1 root root 50331648 Sep 18 02:57 ib_logfile0
-rw-r----- 1 root root 50331648 Sep 18 02:57 ib_logfile1
drwx------ 2 root root 4096 Sep 18 02:57 mysql
drwx------ 2 root root 4096 Sep 18 02:57 opark
drwx------ 2 root root 4096 Sep 18 02:57 performance_schema
drwx------ 2 root root 4096 Sep 18 02:57 test
-rw-r----- 1 root root 429 Sep 18 02:57 xtrabackup_info
[root@DB-SERVER mysql-5.5.32]#
启动数据库
[root@DB-SERVER mysql-5.5.32]# /etc/init.d/mysld start
Starting MySQL. ERROR! The server quit without updating PID file (/application/mysql-5.5.32/data/DB-SERVER.pid).
[root@DB-SERVER mysql-5.5.32]#
出现这样的错误一般就是data目录下的数据用户名和和属组不是mysql，所以这里修改下:
[root@DB-SERVER mysql-5.5.32]# chown -R mysql.mysql data
[root@DB-SERVER mysql-5.5.32]# ll data
total 116760
-rw-r----- 1 mysql mysql 1025 Sep 18 03:00 DB-SERVER.err
-rw-r----- 1 mysql mysql 18874368 Sep 18 02:57 ibdata1
-rw-r----- 1 mysql mysql 50331648 Sep 18 02:57 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Sep 18 02:57 ib_logfile1
drwx------ 2 mysql mysql 4096 Sep 18 02:57 mysql
drwx------ 2 mysql mysql 4096 Sep 18 02:57 opark
drwx------ 2 mysql mysql 4096 Sep 18 02:57 performance_schema
drwx------ 2 mysql mysql 4096 Sep 18 02:57 test
-rw-r----- 1 mysql mysql 429 Sep 18 02:57 xtrabackup_info
[root@DB-SERVER mysql-5.5.32]#
再启动运行/etc/init.d/mysqld start
[root@DB-SERVER mysql-5.5.32]# /etc/init.d/mysld start
Starting MySQL.. SUCCESS!
[root@DB-SERVER mysql-5.5.32]# mysql -uroot -proot -hlocalhost
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| opark |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use opark;
Database changed
mysql> select * from person;
+--------+------------+------------+
| number | name | birthday |
+--------+------------+------------+
| 1 | John Poul | 2016-09-18 |
| 2 | John Hock | 2016-09-18 |
| 3 | Rick Hock | 2016-09-18 |
| 4 | Rick stone | 2016-09-18 |
| 5 | John Green | 2016-09-18 |
| 6 | John Halk | 2016-09-18 |
| 7 | Rick rose | 2016-09-18 |
| 8 | Rick kate | 2016-09-18 |
+--------+------------+------------+
8 rows in set (0.00 sec)
mysql>
说明:
innobackup的--copy-back选项用于执行恢复操作，它是通过复制所有数据相关文件至MySQL数据目录，因此，需要清空数据目录。我这里是将其重命名，然后再重建目录。最主要最后一步是将其权限更改
（2）.增量备份
innobackupex --user=backup --password=‘MANAGER‘ --incremental /databackup/incrementdir --incremental-basedir=/databackup/2016-09-18_03-35-06/
①.向数据库中添加数据:
INSERT INTO person (number,name,birthday) VALUES ("0009", "Reh Hat", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0010", "pyhton study", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0008", "Linux system", NOW());
mysql> select * from person;
+--------+--------------+------------+
| number | name | birthday |
+--------+--------------+------------+
| 1 | John Poul | 2016-09-18 |
| 2 | John Hock | 2016-09-18 |
| 3 | Rick Hock | 2016-09-18 |
| 4 | Rick stone | 2016-09-18 |
| 5 | John Green | 2016-09-18 |
| 6 | John Halk | 2016-09-18 |
| 7 | Rick rose | 2016-09-18 |
| 8 | Rick kate | 2016-09-18 |
| 9 | Reh Hat | 2016-09-18 |
| 10 | pyhton study | 2016-09-18 |
| 8 | Linux system | 2016-09-18 |
+--------+--------------+------------+
11 rows in set (0.00 sec)
②.删除数据
mysql> delete from opark.person where name="Reh Hat";
Query OK, 1 row affected (0.01 sec)
mysql> select * from person;
+--------+--------------+------------+
| number | name | birthday |
+--------+--------------+------------+
| 1 | John Poul | 2016-09-18 |
| 2 | John Hock | 2016-09-18 |
| 3 | Rick Hock | 2016-09-18 |
| 4 | Rick stone | 2016-09-18 |
| 5 | John Green | 2016-09-18 |
| 6 | John Halk | 2016-09-18 |
| 7 | Rick rose | 2016-09-18 |
| 8 | Rick kate | 2016-09-18 |
| 10 | pyhton study | 2016-09-18 |
| 8 | Linux system | 2016-09-18 |
+--------+--------------+------------+
10 rows in set (0.00 sec)
mysql>
[root@DB-SERVER databackup]# innobackupex --user=backup --password=‘MANAGER‘ --incremental /databackup/incrementdir --incremental-basedir=/databackup/2016-09-18_03-09-33
160918 03:23:25 Executing UNLOCK TABLES
160918 03:23:25 All tables unlocked
160918 03:23:25 Backup created in directory ‘/databackup/incrementdir/2016-09-18_03-23-22‘
160918 03:23:25 [00] Writing backup-my.cnf
160918 03:23:25 [00] ...done
160918 03:23:25 [00] Writing xtrabackup_info
160918 03:23:25 [00] ...done
xtrabackup: Transaction log of lsn (1611092) to (1611092) was copied.
160918 03:23:25 completed OK!
[root@DB-SERVER databackup]#
[root@DB-SERVER databackup]# ll incrementdir/
total 4
drwx------ 6 root root 4096 Sep 18 03:23 2016-09-18_03-23-22
[root@DB-SERVER databackup]# ll
total 24
drwx------ 6 root root 4096 Sep 18 01:40 2016-09-18_01-40-28
drwx------ 6 root root 4096 Sep 18 02:53 2016-09-18_02-48-10
drwx------ 6 root root 4096 Sep 18 03:05 2016-09-18_03-05-15
drwx------ 6 root root 4096 Sep 18 03:09 2016-09-18_03-09-33
drwxr-xr-x 3 root root 4096 Sep 18 03:23 incrementdir
drwxr-xr-x 2 root root 4096 Sep 18 03:16 xtrabackuplog
[root@DB-SERVER databackup]#
其中，--incremental-basedir指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/data/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。
需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。
③.增量备份的恢复，如果需要恢复的话需要做如下操作
[root@DB-SERVER databackup]# [root@DB-SERVER databackup]# innobackupex --apply-log --redo-only /databackup/2016-09-18_03-35-06/
[root@DB-SERVER databackup]# innobackupex --apply-log --redo-only /databackup/2016-09-18_03-35-06/ --incremental-dir=/databackup/incrementdir/2016-09-18_03-38-06/
如果存在多次增量备份的话，就需要多次执行.如
innobackupex --apply-log --redo-only BACKUPDIR
innobackupex --apply-log --redo-only BACKUPDIR --incremental-dir=INCREMENTDIR-1
innobackupex --apply-log --redo-only BACKUPDIR --incremental-dir=INCREMENTDIR-2
BACKUP是全备目录，INCREMENTDIR是增量备份目录，上面是有2次增量备份，如果存在多次增量备份，则需要多次运行如上的命令
（3）.Xtrabackup的备份压缩
Xtrabackup对备份的数据文件支持“流”功能，即可以将备份的数据通过STDOUT传输给tar程序进行归档，而不是默认的直接保存至某备份目录中。要使用此功能，仅需要使用--stream选项即可。如：
1
innobackupex --user=backup --password=‘MANAGER‘ --stream=tar /databackup/ | gzip > /databackup/`date +%F_%H-%M-%S`.tar.gz
本文出自 “平平淡淡才是真” 博客，请务必保留此出处http://ucode.blog.51cto.com/10837891/1853422
Percona XtraBackup热备份实践