MySQL 备份与数据恢复

1、mysqldump备份结合binlog日志恢复

MySQL备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在MySQL故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。

1.1 binlog介绍

mysql的二进制日志记录着该数据库的所有增删改的操作日志(前提是要在自己的服务器上开启binlog)，还包括了这些操作的执行时间。为了显示这些二进制内容，我们可以使用mysqlbinlog命令来查看。

1.1.1 Binlog的用途

主从同步
恢复数据库

1.1.2 开启binary log功能

通过编辑my.cnf中的log-bin选项可以开启二进制日志；形式如下：

1	log-bin [=DIR/[filename]]

其中，DIR参数指定二进制文件的存储路径；filename参数指定二级制文件的文件名，其形式为filename.number，number的形式为000001、000002等。每次重启mysql服务或运行mysql> flush logs;都会生成一个新的二进制日志文件，这些日志文件的number会不断地递增。除了生成上述的文件外还会生成一个名为filename.index的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引

配置保存以后重启mysql的服务器，用

1	mysql> show variables like 'log_bin';

查看bin-log是否开启，如图：

查看产生的binary log 注：查看binlog内容是为了恢复数据
bin-log因为是二进制文件，不能通过文件内容查看命令直接打开查看，mysql提供两种方式查看方式，在介绍之前，我们先对数据库进行一下增删改的操作，否则log里边数据有点空。

#mysql -uroot -p -e "reset master"
#mysql -uroot -p -e "create database test"
#mysql -uroot -p -e "use test;create table tb1(id int primary key auto_increment,name varchar(20))"
#mysql -uroot -p -e "insert into test.tb1(name) values('lisi')"
#mysql -uroot -p -e "insert into test.tb1(name) values('zhangsan')"

重新开始一个新的日志文件

#mysql -uroot -p -e "flush logs"
#mysql -uroot -p -e "delete from test.tb1 where id=2"
#mysql -uroot -p -e "insert into test.tb1(name) values('tom')"
# mysql -uroot -p -e "select * from test.tb1"
Enter password: 
+----+------+
| id | name |
+----+------+
|  1 | lisi |
|  3 | tom  |
+----+------+

查看MySQL Server上的二进制日志

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1087 |
| mysql-bin.000002 |       673 |
+------------------+-----------+

1.1.3 查看二进制日志信息

查看二进制日志信息的命令：

语法格式：

1	SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

查看二进制日志中的事件

默认显示可找到的第一个二进制日志文件中的事件，包含了日志文件名、事件的开始位置、事件类型、结束位置、信息等内容

+——————+——+—————-+———–+————-+———————————————————————————-+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+——————+——+—————-+———–+————-+———————————————————————————-+
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.13-log, Binlog ver: 4 | //此事件为格式描述事件
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| mysql-bin.000001 | 219 | Query | 1 | 313 | create database test | //为查询事件
| mysql-bin.000001 | 313 | Anonymous_Gtid | 1 | 378 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| mysql-bin.000001 | 378 | Query | 1 | 520 | use test; create table tb1(id int primary key auto_increment,name varchar(20)) |
| mysql-bin.000001 | 520 | Anonymous_Gtid | 1 | 585 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| mysql-bin.000001 | 585 | Query | 1 | 653 | BEGIN | //为查询事件，事务开始
| mysql-bin.000001 | 653 | Table_map | 1 | 702 | table_id: 110 (test.tb1) | //为表映射事件
| mysql-bin.000001 | 702 | Write_rows | 1 | 747 | table_id: 110 flags: STMT_END_F | //为我们执行的insert事件
| mysql-bin.000001 | 747 | Xid | 1 | 778 | COMMIT /* xid=2052 */ | //Xid时间是自动提交事务的动作
| mysql-bin.000001 | 778 | Anonymous_Gtid | 1 | 843 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| mysql-bin.000001 | 843 | Query | 1 | 911 | BEGIN |
| mysql-bin.000001 | 911 | Table_map | 1 | 960 | table_id: 110 (test.tb1) |
| mysql-bin.000001 | 960 | Write_rows | 1 | 1009 | table_id: 110 flags: STMT_END_F |
| mysql-bin.000001 | 1009 | Xid | 1 | 1040 | COMMIT /* xid=2055 */ |
| mysql-bin.000001 | 1040 | Rotate | 1 | 1087 | mysql-bin.000002;pos=4 | //为日志轮换事件，是我们执行flush logs开启新日志文件引起的。

查看指定的二进制日志中的事件

1
mysql> show binlog events in 'mysql-bin.000002';
+——————+—–+—————-+———–+————-+—————————————+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+——————+—–+—————-+———–+————-+—————————————+
| mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.13-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| mysql-bin.000002 | 219 | Query | 1 | 287 | BEGIN |
| mysql-bin.000002 | 287 | Table_map | 1 | 336 | table_id: 110 (test.tb1) |
| mysql-bin.000002 | 336 | Delete_rows | 1 | 385 | table_id: 110 flags: STMT_END_F |
| mysql-bin.000002 | 385 | Xid | 1 | 416 | COMMIT /* xid=2068 */ |
| mysql-bin.000002 | 416 | Anonymous_Gtid | 1 | 481 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| mysql-bin.000002 | 481 | Query | 1 | 549 | BEGIN |
| mysql-bin.000002 | 549 | Table_map | 1 | 598 | table_id: 110 (test.tb1) |
| mysql-bin.000002 | 598 | Write_rows | 1 | 642 | table_id: 110 flags: STMT_END_F |
| mysql-bin.000002 | 642 | Xid | 1 | 673 | COMMIT /* xid=2071 */ |

该命令还包含其他选项以便灵活查看

mysql> show binlog events in 'mysql-bin.000002' from 219 limit 1,3;
+------------------+-----+-------------+-----------+-------------+---------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                            |
+------------------+-----+-------------+-----------+-------------+---------------------------------+
| mysql-bin.000002 | 287 | Table_map   |         1 |         336 | table_id: 110 (test.tb1)        |
| mysql-bin.000002 | 336 | Delete_rows |         1 |         385 | table_id: 110 flags: STMT_END_F |
| mysql-bin.000002 | 385 | Xid         |         1 |         416 | COMMIT /* xid=2068 */           |

SHOW BINARY LOGS 等价于 SHOW MASTER LOGS
PURGE BINARY LOGS用于删除二进制日志，如：
PURGE BINARY LOGS TO ‘mysql-bin.00010’; //把这个文件之前的其他文件都删除掉
PURGE BINARY LOGS BEFORE ‘2016-08-28 22:46:26’;//把指定时间之前的二进制文件删除了
RESET MASTER 与 RESET SLAVE
前者清空index文件中列出的所有二进制日志，重置index文件为空，并创建一个新的二进制日志文件，一般用于MASTER首次启动时。后者使SLAVE忘记其在MASTER二进制日志文件中的复制位置，它会删除master.info、relay-log.info 和所有中继日志文件并开始一个新的中继日志文件，以便于开始一个干净的复制。在使用RESET SLAVE前需先关闭 SLAVE复制线程。

上述方式可以查看到服务器上存在的二进制日志文件及文件中的事件，但是想查看到文件中具体的内容并应于恢复场景还得借助mysqlbinlog这个工具。

语法格式：

1	mysqlbinlog [options] log_file ...

输出内容会因日志文件的格式以及mysqlbinlog工具使用的选项不同而略不同。

mysqlbinlog的可用选项可参考man手册。

二进制日志文件的格式包含行模式、语句模式和混合模式（也即有服务器决定在什么情况下记录什么类型的日志），基于语句的日志中事件信息包含执行的语句等，基于行的日志中事件信息包含的是行的变化信息等。混合模式的日志中两种类型的事件信息都会记录。

为了便于查看记录了行变化信息的事件在当时具体执行了什么样的SQL语句可以使用mysqlbinlog工具的-v（–verbose）选项，该选项会将行事件重构成被注释掉的伪SQL语句，如果想看到更详细的信息可以将该选项给两次如-vv，这样可以包含一些数据类型和元信息的注释内容，如

先切换到binlog所在的目录下

1
2
3

#mysqlbinlog mysql-bin.000001
#mysqlbinlog -v mysql-bin.000001 
#mysqlbinlog -vv mysql-bin.000001

另外mysqlbinlog和可以通过–read-from-remote-server选项从远程服务器读取二进制日志文件，这时需要一些而外的连接参数，如-h，-P，-p，-u等，这些参数仅在指定了–read-from-remote-server后有效。
无论是本地二进制日志文件还是远程服务器上的二进制日志文件，无论是行模式、语句模式还是混合模式的二进制日志文件，被mysqlbinlog工具解析后都可直接应用与MySQL Server进行基于时间点、位置或数据库的恢复。

1.1.4 数据恢复

下面我们就来演示如何使用binlog恢复之前删除数据（id=2那条记录）

注意：在实际生产环境中，如果遇到需要恢复数据库的情况，不要让用户能访问到数据库，以避免新的数据插入进来，以及在主从的环境下，关闭主从。

查看binlog文件，从中找出delete from test.tb1 where id=2

1 2	# cd /usr/local/mysql/data/ # mysqlbinlog -v mysql-bin.000002

显示结果

# at 219
#160913 20:59:51 server id 1 end_log_pos 287 CRC32 0x1a97741b Query thread_id=42 exec_time=0 error_code=0
SET TIMESTAMP=1473771591/!/;
SET @@session.pseudo_thread_id=42/!/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/!/;
SET @@session.sql_mode=1075838976/!/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/!/;
/!\C utf8 *//!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/!/;
SET @@session.lc_time_names=0/!/;
SET @@session.collation_database=DEFAULT/!/;
BEGIN
/!/;
# at 287
#160913 20:59:51 server id 1 end_log_pos 336 CRC32 0x930ab248 Table_map: test.tb1 mapped to number 125
#at 336
#160910 23:17:43 server id 1 end_log_pos 385 CRC32 0xdede3eb7 Delete_rows: table id 110 flags: STMT_END_F
BINLOG ‘
FyTUVxMBAAAAMQAAAFABAAAAAG4AAAAAAAEABHRlc3QAA3RiMQACAw8CPAAC2t7UdQ==
FyTUVyABAAAAMQAAAIEBAAAAAG4AAAAAAAEAAgAC//wCAAAACHpoYW5nc2Futz7e3g==
‘/!/;
### DELETE FROM test.tb1
### WHERE
### @1=2
### @2=’zhangsan’
# at 385
#160910 23:17:43 server id 1 end_log_pos 416 CRC32 0x7881c9da Xid = 2068
COMMIT/!/;

从中可以看出delete事件发生position是287，事件结束position是416

恢复流程：

直接用bin-log日志将数据库恢复到删除位置287前,然后跳过故障点,再进行恢复下面所有的操作,命令如下

由于之前没有做过全库备份，所以要使用所有binlog日志恢复，所以生产环境中需要很长时间恢复，导出相关binlog文件

1
2
3

#mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 > /opt/mysql-bin.000001.sql
#mysqlbinlog --stop-position=287 /usr/local/mysql/data/mysql-bin.000002 > /opt/287.sql
#mysqlbinlog --start-position=416 /usr/local/mysql/data/mysql-bin.000002 > /opt/416.sql

删除test数据库

1	mysql>drop database test;

利用binlog恢复数据

#mysql -uroot -p123456< /opt/mysql-bin.000001.sql 

#mysql -uroot -p123456< /opt/287.sql 

#mysql -uroot -p123456< /opt/416.sql

恢复完成后，我们检查下表的数据是否完整

mysql> select * from test.tb1;
+----+----------+
| id | name     |
+----+----------+
|  1 | lisi     |
|  2 | zhangsan |
|  3 | tom      |
+----+----------+

Ok完整的都恢复过来了

mysqlbinlog 选项示例

常见的选项有以下几个：

选项	描述
–start-datetime	从二进制日志中读取指定时间戳或者本地计算机时间之后的日志事件。
–stop-datetime	从二进制日志中读取指定时间戳或者本地计算机时间之前的日志事件。
–start-position	从二进制日志中读取指定position 事件位置作为开始。
–stop-position	从二进制日志中读取指定position 事件位置作为事件截至。

1.2 mysqldump介绍

mysqldump是mysql用于备份和数据转移的一个工具。它主要产生一系列的SQL语句，可以封装到文件，该文件包含有所有重建你的数据库所需要的 SQL命令如CREATE DATABASE，CREATE TABLE，INSERT等等。可以用来实现轻量级的快速迁移或恢复数据库。

mysqldump 是将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。

mysqldump一般在数据量很小的时候（几个G）可以用于备份。当数据量比较大的情况下，就不建议用mysqldump工具进行备份了。

数据库的导出

导出对象说明：

mysqldump可以针对单个表、多个表、单个数据库、多个数据库、所有数据库进行导出的操作

#mysqldump [options] db_name [tbl_name …] //导出指定数据库或单个表
#mysqldump [options] –databases db_name … //导出多个数据库
#mysqldump [options] –all-databases //导出所有

导出数据库test

1	# mysqldump -uroot -p --flush-logs test > /opt/test.sql

–flush-logs这个选项就会完整备份的时候重新开启一个新binlog

数据库的导入

1	# mysql -uroot -p test < /opt/test.sql

在前面我们介绍了mysql的binlog和mysqldump工具，下面我们来学习如何实现mysqldump全库备份+binlog的数据恢复

环境准备与备份还原：
检查开启binlog
先创建一些原始数据

mysql> reset master;
mysql> create database test_db;
mysql> use test_db;
mysql> create table tb1(id int primary key auto_increment,name varchar(20));
mysql> insert into tb1(name) values('tom1');
mysql> insert into tb1(name) values('tom2');
mysql> commit;
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
+----+------+

1.2.1 方案：mysqldump全库备份+binlog还原

1.2.1.1 mysqldump备份方案

每周一凌晨1点全库备份

1.2.1.2 备份步骤

①创建备份目录

1 2	# mkdir /opt/mysqlbackup # mkdir /opt/mysqlbackup/daily

②全库备份

这里我们模拟周一的完整备份数据库任务

[root@localhost data]# mysqldump -uroot -p --flush-logs test_db > /opt/mysqlbackup/test_db_2016_09_12.sql

[root@localhost data]# ls -l /opt/mysqlbackup/
-rw-r--r--. 1 root root 1871 Sep 13 21:06 test_db_2016_09_12.sql

备份mysqldump全库备份之前的binlog日志文（注：生产环境中可能不只一个binlog文件）

1 2	# cp /usr/local/mysql/data/mysql-bin.000001 /opt/mysqlbackup/daily/ # mysql -uroot -p -e "purge binary logs to 'mysql-bin.000002'"

模拟下操作失误,将数据修改错误了。

mysql> use test_db;
mysql> delete from tb1 where id=1;
mysql> commit;
mysql> insert into tb1(name) values('tom3');
mysql> commit;

备份自mysqldump之后的binlog日志文件

1	cp /usr/local/mysql/data/mysql-bin.000002 /opt/mysqlbackup/daily/

上面的模拟的误操作是删除了id=1的记录

③现在我们使用mysqldump的全库备份和binlog来恢复数据

使用mysqldump的备份进行全库恢复

1	# mysql -uroot -p test_db < /opt/mysqlbackup/test_db_2016_09_12.sql

查询一下数据

[root@localhost ~]# mysql -uroot -p -e "select * from test_db.tb1"
Enter password: 
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
+----+------+

从显示结果可以看到使用mysqldump备份将数据还原到了备份时的状态，刚才删除的数据（id=2）恢复回来了，但备份后产生的数据却丢失了所以还得利用binlog进一步不原

因为删除是在全库备份后发生的，而mysqldump全库备份时使用–flush-logs选项，所以只需要分析全库备份后的binlog即mysql-bin.000002。

1
mysql> show binary logs;
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000002 | 1853 |
+——————+———–+

查看mysql-bin.000002中的事件，可以看到有删除事件

1
mysql> show binlog events in 'mysql-bin.000002';
| mysql-bin.000002 | 219 | Query | 1 | 294 | BEGIN |
| mysql-bin.000002 | 294 | Table_map | 1 | 346 | table_id: 118 (test_db.tb1) |
| mysql-bin.000002 | 346 | Delete_rows | 1 | 391 | table_id: 118 flags: STMT_END_F |
| mysql-bin.000002 | 391 | Xid | 1 | 422 | COMMIT /* xid=2739 */

使用mysqlbinlog 命令可以查看备份的binlog文件的详细事件。

恢复流程：我们直接用bin-log日志将数据库恢复到删除位置前,然后跳过故障点,再进行恢复删除后的所有操作。

1	# mysqlbinlog -v /opt/mysqlbackup/daily/mysql-bin.000002

我们先用mysqlbinlog命令找到delete那条语句的位置

#at 219
#160911 17:19:55 server id 1 end_log_pos 294 CRC32 0x84590493 Query thread_id=66 exec_time=0 error_code=0
SET TIMESTAMP=1473585595/!/;
SET @@session.pseudo_thread_id=66/!/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/!/;
SET @@session.sql_mode=1075838976/!/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/!/;
/!\C utf8 *//!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/!/;
SET @@session.lc_time_names=0/!/;
SET @@session.collation_database=DEFAULT/!/;
BEGIN
/!/;
# at 294
#160911 17:19:55 server id 1 end_log_pos 346 CRC32 0x5cdccf9e Table_map: test_db.tb1 mapped to number 118
# at 346
#160911 17:19:55 server id 1 end_log_pos 391 CRC32 0x320c4935 Delete_rows: table id 118 flags: STMT_END_F
BINLOG ‘
uyHVVxMBAAAANAAAAFoBAAAAAHYAAAAAAAEAB3Rlc3RfZGIAA3RiMQACAw8CPAACns/cXA==
uyHVVyABAAAALQAAAIcBAAAAAHYAAAAAAAEAAgAC//wBAAAABHRvbTE1SQwy
‘/!/;
### DELETE FROM test_db.tb1
### WHERE
### @1=1
### @2=’tom1’
# at 391
#160911 17:19:55 server id 1 end_log_pos 422 CRC32 0x5e4a6699 Xid = 2739
COMMIT/!/;

通过mysqlbinlog命令所显示的结果可以看到误操作delete的开始postion为219，结束position是422。

从二进制日志中读取指定position=219事件位置作为截至，即把数据恢复到delete删除前

1	# mysqlbinlog --stop-position=219 /opt/mysqlbackup/daily/mysql-bin.000002 \| mysql -u root -p

从二进制日志中读取指定position=422事件位置作为开始，即跳过删除事件，恢复删除事件之后对数据的正常操作

1	# mysqlbinlog --start-position=422 /opt/mysqlbackup/daily/mysql-bin.000002 \| mysql -u root -p

查看恢复结果：

# mysql -uroot -p -e "select * from test_db.tb1"
Enter password: 
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
|  3 | tom3 |
+----+------+

从上面显示可以看出数据恢复到正常状态

生产环境中Mysql数据库的备份是周期性重复的操作，所以通常是要编写脚本实现，通过crond计划任务周期性执行备份脚本

1.2.1.3 mysqldump备份方案：

周日凌晨1点全库备份
周一到周六凌晨每隔4个小时增量备份一次
设置crontab任务，每天执行备份脚本

crontab–e

#每个星期日凌晨1:00执行完全备份脚本

1	0 1 * * 0 /root/mysqlfullbackup.sh >/dev/null 2>&1

#周一到周六每隔4个小时增量备份一次

1	0 /4 * 1-6 /root/mysqldailybackup.sh >/dev/null 2>&1

mysqlfullbackup.sh脚本内容：

[root@localhost ~]# cat mysqlfullbackup.sh 
#!/bin/sh
# Name:mysqlFullBackup.sh

# 定义数据库目录
mysqlDir=/usr/local/mysql

# 定义用于备份数据库的用户名和密码
user=root
userpwd=123456
dbname=test_db

# 定义备份目录
databackupdir=/opt/mysqlbackup
[ ! -d $databackupdir ]&& mkdir $databackupdir

# 定义邮件正文文件
emailfile=$databackupdir/email.txt

# 定义邮件地址
email=root@localhost.localdomain

# 定义备份日志文件
logfile=$databackupdir/mysqlbackup.log
DATE=`date -I`

echo "" > $emailfile
echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile
cd $databackupdir

# 定义备份文件名
dumpfile=mysql_$DATE.sql
gzdumpfile=mysql_$DATE.sql.tar.gz

# 使用mysqldump备份数据库，请根据具体情况设置参数
$mysqlDir/bin/mysqldump -u$user -p$userpwd --flush-logs -x $dbname > $dumpfile

# 压缩备份文件
if [ $? -eq 0 ]; then
tar czf $gzdumpfile $dumpfile >> $emailfile 2>&1
echo "BackupFileName:$gzdumpfile" >> $emailfile
echo "DataBase Backup Success!" >> $emailfile
rm -f $dumpfile
else
echo "DataBase Backup Fail!" >> $emailfile
fi

# 写日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile

# 发送邮件通知
cat $emailfile | mail -s "MySQL Backup" $email

mysqldailybackup.sh增量备份脚本内容：

[root@localhost ~]# cat mysqldailybackup.sh 
#!/bin/sh
# Name:mysqlDailyBackup.sh

# 定义数据库目录和数据目录
mysqldir=/usr/local/mysql
datadir=$mysqldir/data

# 定义用于备份数据库的用户名和密码
user=root
userpwd=123456

# 定义备份目录，每日备份文件备份到$dataBackupDir/daily
databackupdir=/opt/mysqlbackup
dailybackupdir=$databackupdir/daily
[ ! -d $dailybackupdir ]&& mkdir -p $databackupdir/daily

# 定义邮件正文文件
emailfile=$databackupdir/email.txt

# 定义邮件地址
email=root@localhost.localdomain

# 定义日志文件
logfile=$databackupdir/mysqlbackup.log
echo "" > $emailfile
echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile
#

# 刷新日志，使数据库使用新的二进制日志文件
$mysqldir/bin/mysqladmin -u$user -p$userpwd flush-logs
cd $datadir

# 得到二进制日志列表
filelist=`cat mysql-bin.index`
icounter=0
for file in $filelist
do
icounter=`expr $icounter + 1`
done
nextnum=0
ifile=0
for file in $filelist
do
binlogname=`basename $file`
nextnum=`expr $nextnum + 1`

# 跳过最后一个二进制日志（数据库当前使用的二进制日志文件）
if [ $nextnum -eq $icounter ]; then
echo "Skip lastest!" > /dev/null
else
dest=$dailybackupdir/$binlogname

# 跳过已经备份的二进制日志文件
if [ -e $dest ]; then
echo "Skip exist $binlogname!" > /dev/null
else
# 备份日志文件到备份目录
cp $binlogname $dailybackupdir
if [ $? -eq 0 ]; then
ifile=`expr $ifile + 1`
echo "$binlogname backup success!" >> $emailfile
fi
fi
fi
done
if [ $ifile -eq 0 ];then
echo "No Binlog Backup!" >> $emailfile
else
echo "Backup $ifile File(s)." >> $emailfile
echo "Backup MySQL Binlog OK!" >> $emailfile
fi

# 发送邮件通知
cat $emailfile | mail -s "MySQL Backup" $email

# 写日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile

2、使用xtrabackup进行MySQL数据库备份

前面介绍mysqldump备份方式是采用逻辑备份，其最大的缺陷就是备份和恢复速度都慢，对于一个小于50G的数据库而言，这个速度还是能接受的，但如果数据库非常大，那再使用mysqldump备份就不太适合了。

这时就需要一种好用又高效的工具，xtrabackup就是其中一款，号称免费版的InnoDB HotBackup。

Xtrabackup实现是物理备份，而且是物理热备

目前主流的有两个工具可以实现物理热备：ibbackup和xtrabackup；ibbackup是商业软件，需要授权，非常昂贵。而xtrabackup功能比ibbackup还要强大，但却是开源的。因此我们这里就来介绍xtrabackup的使用。

Xtrabackup提供了两种命令行工具：

xtrabackup：专用于备份InnoDB和XtraDB引擎的数据；

innobackupex：这是一个perl脚本，在执行过程中会调用xtrabackup命令，这样用该命令即可以实现备份InnoDB，也可以备份MyISAM引擎的对象。

Xtrabackup是由percona提供的mysql数据库备份工具，特点：

备份过程快速、可靠；
备份过程不会打断正在执行的事务；
能够基于压缩等功能节约磁盘空间和流量；
自动实现备份检验；
还原速度快。

官方链接地址：http://www.percona.com/software/percona-xtrabackup ；可以下载源码编译安装，也可以下载适合的RPM包或使用yum进行安装或者下载二进制源码包。

2.1 安装xtrabackup

2.1.1 下载xtrabackup

1	wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

2.1.2 解压

1	tar zxf percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

2.1.3 进入解压目录

1	cd percona-xtrabackup-2.4.4-Linux-x86_64/

2.1.4 复制bin下的所有程序到/usr/bin

1	[root@localhost percona-xtrabackup-2.4.4-Linux-x86_64]# cp bin/* /usr/bin/

Xtrabackup中主要包含两个工具：
xtrabackup：是用于热备份innodb, xtradb表中数据的工具，支持在线热备份，可以在不加锁的情况下备份Innodb数据表，不过此工具不能操作Myisam引擎表；
innobackupex：是将xtrabackup进行封装的perl脚本，能同时处理Innodb和Myisam，但在处理Myisam时需要加一个读锁。
由于操作Myisam时需要加读锁，这会堵塞线上服务的写操作，而Innodb没有这样的限制，所以数据库中Innodb表类型所占的比例越大，则越有利。

2.1.5 安装相关插件

1	# yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5 -y

2.1.6 下载percona-toolkit并安装

1
2
3

# wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm

# rpm -vih percona-toolkit-2.2.19-1.noarch.rpm

下面就可以启动备份了

2.2 使用xtrabackup备份

方案：xtrabackup完全备份+binlog增量备份

创建备份目录

1	mkdir -p /opt/mysqlbackup/{full,inc}

full：全备存放的目录；inc：增量备份存放的目录

2.2.1 完全备份

基本语法：

innobackupex –user=DBUSER –password=DBUSERPASS /path/to/BACKUP-DIR/

执行下面的命令进行完全备份：

1	innobackupex --user=root --password=123456 /opt/mysqlbackup/full

注： –defaults-file=/etc/my.cnf 指定mysql的配置文件my.cfg，如果指定则必须是第一个参数。
/path/to/BACKUP-DIR/指定备份所存放的目标目录，备份过程会创建一个以当时备份时间命名的目录存放备份文件。
出现如下提示。表示成功

备份后的文件：

在备份的同时，备份数据会在备份目录下创建一个以当前日期时间为名字的目录存放备份文件：

各文件说明：

(1)xtrabackup_checkpoints ——备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；

每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。

(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

(3)xtrabackup_binlog_pos_innodb ——二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。

(4)xtrabackup_binary ——备份中用到的xtrabackup的可执行文件；

(5)backup-my.cnf ——备份命令用到的配置选项信息；

在使用innobackupex进行备份时，还可以使用–no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据

注意：相关选项说明：

–user指定连接数据库的用户名，

–password指定连接数据库的密码，

–defaults-file指定数据库的配置文件，innobackupex要从其中获取datadir等信息；

–database指定要备份的数据库，这里指定的数据库只对MyISAM表有效，对于InnoDB 数据来说都是全备（所有数据库中的InnoDB数据都进行了备份，不是只备份指定的数据库，恢复时也一样）；

/opt/mysqlbackup/full是备份文件的存放位置。

注意：备份数据库的用户需要具有相应权限，如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：

mysql> create user 'bkpuser'@'localhost' identified by '123456';

mysql> revoke all privileges,grant option from 'bkpuser'@'localhost';

mysql> grant reload,lock tables,replication client, process on *.* to 'bkpuser'@'localhost';

mysql> flush privileges;

至此全备完全成功，然后向mysql某个库插入几条数据，然后进行增量备份

对完全备份的后数据库更改进行二进制日志增量备份：

查看完全备份时binlog日志位置(position)：

模拟数据库修改：

2.2.2 增量备份二进制文件

1	# mysqlbinlog --start-position=2378 /usr/local/mysql/data/mysql-bin.000023 > /opt/mysqlbackup/inc/`date +%F`.sql

2.3 xtrabackup还原数据库

模拟数据库损坏：

我这里直接使用删除数据目录文件来模拟损坏。

1	# rm -fr /usr/local/mysql/data/*

2.3.1 还原完全备份

①准备(prepare)一个完全备份

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。

innobakupex命令的–apply-log选项可用于实现上述功能。如下面的命令：

–apply-log指明是将日志应用到数据文件上，完成之后将备份文件中的数据恢复到数据库中：

1	# innobackupex --apply-log /opt/mysqlbackup/full/2016-09-12_11-29-55/

注：/opt/mysqlbackup/full/2016-09-12_11-29-55/备份文件所在目录名称

如果执行正确，其最后输出的几行信息通常如下：

在实现“准备”的过程中，innobackupex通常还可以使用–use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。

innobackupex命令的–copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。

②还原数据库语法

1	# innobackupex --copy-back /opt/mysqlbackup/full/2016-09-12_11-29-55/

这里的–copy-back指明是进行数据恢复。数据恢复完成之后，需要修改相关文件的权限mysql数据库才能正常启动。

如果执行正确，其输出信息的最后几行通常如下：

请确保如上信息的最行一行出现“completed OK!”。

③修改还原后的数据目录权限

当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如mysql，否则，在启动mysqld之前还需要事先修改数据文件的属主和属组。如：

1	# chown -R mysql:mysql /usr/local/mysql/data/

④重启动MySQL

1	# systemctl restart mysqld

⑤验证还原后的数据

mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
+----+------+

2.3.2 还原增量备份

为了防止还原时产生大量的二进制日志，在还原时可临时关闭二进制日志后再还原：

1 2	mysql> set sql_log_bin=0; mysql> source /opt/mysqlbackup/inc/2016-09-12.sql

重新启动二进制日志并验证还原数据

1	mysql> set sql_log_bin=1;

验证数据是否恢复回来

2.3.3 Xtrabackup的“流”及“备份压缩”功能

Xtrabackup对备份的数据文件支持“流”功能，即可以将备份的数据通过STDOUT传输给tar程序进行归档，而不是默认的直接保存至某备份目录中。要使用此功能，仅需要使用–stream选项即可。如：

1	# innobackupex --user=root --password="123456" --stream=tar /opt/mysqlbackup/full/ \| gzip >/opt/mysqlbackup/full/full_`date +%F_%H%M%S`.tar.gz