本文主要向大家介绍了MySQL数据库之Percona XtraBackup热备份实践 ，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。

实验环境及操作步骤:

一、操作系统

[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 3306tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      18591/mysqld       [root@DB-SERVER mysql-5.5.32]#

MySQL配置文件为:

[client]
port            = 3306
socket          = /application/mysql-5.5.32/tmp/mysql.sock
[mysqld]
port            = 3306
socket          = /application/mysql-5.5.32/tmp/mysql.sock
datadir         = /application/mysql-5.5.32/data
basedir         = /application/mysql-5.5.32
tmpdir         = /application/mysql-5.5.32/tmp
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
innodb_log_file_size = 256M #没有这个参数恢复后启动会报错，所以这里设置下innodb_log_file_size=256
server-id       = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
interactive-timeout

鉴于没有设置innodb_log_file_size出现错误为:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes

解决方法:

对于使用了默认 my.cnf（一般教程都会教你使用support-files/my-medium.cnf）的Mysql服务来说

如果中间使用了innodb的话，innodb默认的log file大小是56M

如果你的配置文件使用了类似my-innodb-heavy-4G.cnf作为配置文件的话。

Mysql可以正常启动，但innodb的表无法使用

在错误日志里你会看到如下输出：

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes

现在需要做的事情就是把原来的 innodb 的ib_logfile×备份到一个目录下，然后删除掉原来的文件，重启 mysql。

你会看到ib_logfile*大小变成了你配置文件中指定的大小。

my-innodb-heavy-4G.cnf的话（log file 的大小是256M：innodb_log_file_size = 256M）

你会看到很多个268435456大小的文件。

所以我在MySQL的配置文件中使用了该参数，并设置为256M

修改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]        <span class="token punctuation"    

本文由职坐标整理并发布，希望对同学们学习MySQL有所帮助，更多内容请关注职坐标数据库MySQL数据库频道！