开启mysql的binlog功能可以有效防止在日常mysql操作过程中因操作失误而导致的数据丢失问题
但是，对于DBA来说，第一准则就是备份重于一切。eygle在DBA四大生存法则中也有提到
我们必需知道,系统总是要崩溃的，没有有效的备份只是等哪一天死！我经常开玩笑的说,唯一会使DBA在梦中惊醒的就是,没有有效的备份.

binlog

binlog，也称为二进制日志，记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘中，可以用来查看数据库的变更历史（具体的时间点所有的SQL操作）、数据库增量备份和恢复（增量备份和基于时间点的恢复）、Mysql的复制（主主数据库的复制、主从数据库的复制）

开启binlog

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
3 rows in set (0.00 sec)

mysql>

[root@host1 ~]# grep -i 'bin' /etc/my.cnf | grep -i 'log'
log_bin=mysql_bin
binlog_format=Mixed
[root@host1 ~]#

[root@host1 ~]# /etc/init.d/mysqld restart

[root@host1 ~]# ls /var/lib/mysql | grep 'mysql_bin' | tail -3
mysql_bin.000032
mysql_bin.000033
mysql_bin.index
[root@host1 ~]#

Mysql备份恢复

环境：Centos 6.6
　　　Mysql version： 5.5.54

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#先执行全备操作
[root@host1 ~]# sh mysql_back.sh 
Usage: please input Mysql_Fullbackup|Mysql_Incbackup

[root@host1 ~]# sh mysql_back.sh Mysql_Fullbackup
Full backup of MySQL database... Please wait.

Mysql database full backup success.
[root@host1 ~]#

[root@host1 ~]# tree /backup/mysql_backup/
/backup/mysql_backup/
├── backup_20170308.log
├── Full_backup
│   └── 20170308
│       ├── all_databases_backup_20170308_17:06:08.sql.gz
│       └── jack_backup_20170308_17:06:08.sql.gz
├── Full_backup_time.txt
├── Inc_backup
└── mysql_db.txt

3 directories, 5 files
[root@host1 ~]#

[root@host1 mysql_backup]# cat backup_20170308.log 
[ 2017-03-08 17:06:08 ] - Databases all-databases has been backup successful.

[ 2017-03-08 17:06:08 ] - Databases jack has been backup successful.
----------------------------------------

[root@host1 mysql_backup]# cat Full_backup_time.txt 
2017-03-08 17:06:08
[root@host1 mysql_backup]# cat mysql_db.txt 
jack
[root@host1 mysql_backup]#

向jack库的yfshare表插入几条数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> use jack;
mysql> select * from yfshare;
+------+---------+
| id   | name    |
+------+---------+
| 1    | jack    |
| 2    | yfshare |
| 3    | tom     |
| 4    | jerry   |
| 5    | bob     |
+------+---------+
5 rows in set (0.00 sec)
mysql> INSERT INTO yfshare values(6,'aaa');
mysql> INSERT INTO yfshare values(7,'bbb');
mysql> commit;
mysql> select * from yfshare;
+------+---------+
| id   | name    |
+------+---------+
| 1    | jack    |
| 2    | yfshare |
| 3    | tom     |
| 4    | jerry   |
| 5    | bob     |
| 6    | aaa     |
| 7    | bbb     |
+------+---------+
7 rows in set (0.00 sec)

mysql>

然后做增量备份
1
2
3
4
5
6
7
8
9
10
11
12
13
[root@host1 ~]# sh mysql_back.sh Mysql_Incbackup
Increment backup of MySQL database... Please wait.

Mysql database Increment backup success.
[root@host1 ~]#

[root@host1 ~]# tree /backup/mysql_backup/Inc_backup/
/backup/mysql_backup/Inc_backup/
└── 20170308
    └── jack_Inc_20170308_17:15:27.zip

1 directory, 1 file
[root@host1 ~]#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
再向jack库和yfshare表插入几条数据后，不做备份，假设不小心删除了库jack    
```bash
mysql> INSERT INTO yfshare values(8,'ccc');
mysql> INSERT INTO yfshare values(9,'ddd');
mysql> commit;
mysql> select * from yfshare;
+------+---------+
| id   | name    |
+------+---------+
| 1    | jack    |
| 2    | yfshare |
| 3    | tom     |
| 4    | jerry   |
| 5    | bob     |
| 6    | aaa     |
| 7    | bbb     |
| 8    | ccc     |
| 9    | ddd     |
+------+---------+
9 rows in set (0.00 sec)
mysql> drop database jack;
mysql> commit;
mysql> desc yfshare;
ERROR 1046 (3D000): No database selected
mysql> use jack;
ERROR 1049 (42000): Unknown database 'jack'
mysql>

下面来开始恢复jack库的yfshare表
恢复步骤：

  1. 恢复jack库的全量备份
  2. 恢复jack库的增量备份
  3. 通过binlog恢复数据库到删除前

恢复思路：先在mysql中新建一个测试库，在测试库导入误删除表所在的库全量增量备份，然后通过binlog恢复到该表删除前一刻，并导入测试库，然后从测试库导出该表的sql，最后导入到正式库

1
2
3
4
5
6
7
[root@host1 mysql_backup]# mysql -uroot -p123456 jack --one-database < Full_backup/20170308/jack_backup_20170308_17\:06\:08.sql 
ERROR 1049 (42000): Unknown database 'jack'
[root@host1 mysql_backup]#

这时是因为没有库，所以需要先创建对应的库名，再执行全备恢复操作
mysql> create database jack;
mysql> commit;

1
2
3
#全量备份恢复
[root@host1 mysql_backup]# gunzip Full_backup/20170308/jack_backup_20170308_17\:06\:08.sql.gz
[root@host1 mysql_backup]# mysql -uroot -p123456 jack --one-database < Full_backup/20170308/jack_backup_20170308_17\:06\:08.sql

1
2
3
#增量备份恢复
[root@host1 mysql_backup]# unzip Inc_backup/20170308/jack_Inc_20170308_17\:15\:27.zip -d Inc_backup/20170308/
[root@host1 mysql_backup]# mysql -uroot -p123456 jack --one-database < Inc_backup/20170308/jack_Inc_20170308_17\:15\:27.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#先看看恢复的情况
mysql> use jack;
mysql> show tables;
+----------------+
| Tables_in_jack |
+----------------+
| aaa            |
| yfshare        |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from yfshare;
+------+---------+
| id   | name    |
+------+---------+
| 1    | jack    |
| 2    | yfshare |
| 3    | tom     |
| 4    | jerry   |
| 5    | bob     |
| 6    | aaa     |
| 7    | bbb     |
+------+---------+
7 rows in set (0.00 sec)

mysql>

现在yfshare表里面只有7条数据，删除前是有9条数据的，而后边的数据没有备份，所以只能通过mysqlbinlog恢复了

增量备份时间是20170308_17:15:27，所以需要把从这个时间点到删除前的操作导出成一个sql文件

1
2
3
4
5
[root@host1 mysql_backup]# tail -3 /var/lib/mysql/mysql_bin.index 
./mysql_bin.000031
./mysql_bin.000032
./mysql_bin.000033
[root@host1 mysql_backup]#

binlog的时间格式是：170308 17:15:27
mysqlbinlog恢复的时间格式：2017-03-08 17:15:27
1
2
3
#查找增量备份时记录到了哪个binlog文件
#因为我们在增量备份文件中记录了备份时间，所以能很快找到是从mysql_bin.000032开始的
[root@host1 tmp]# mysqlbinlog --no-defaults /var/lib/mysql/mysql_bin.000032 | grep -i '170308 17:15:27'

1
2
3
4
5
6
7
8
9
10
11
12
13
#查找删除的时间点
#可以考虑把所有mysql_bin文件导成sql文件后过滤查找
[root@host1 ~]# mysqlbinlog -d jack /var/lib/mysql/mysql_bin.000033 > test.sql

#发现误删除操作的时间是170308 17:18:29
#170308 17:18:29 server id 1  end_log_pos 602   Query   thread_id=117   exec_time=0     error_code=0
SET TIMESTAMP=1488964709/*!*/;
DROP TABLE `yfshare` /* generated by server */
/*!*/;
# at 602
#170308 17:24:43 server id 1  end_log_pos 683   Query   thread_id=117   exec_time=0     error_code=0
SET TIMESTAMP=1488965083/*!*/;
drop database jack

通过查找binlog发现，我们需要恢复的时间是170308 17:18:29
这时候我们就可以恢复了
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#因为找到具体的binlog文件和误删除操作时间，所以我们要把期间所有的操作导成一个sql文件，然后恢复
[root@host1 ~]# mysqlbinlog -d jack --start-datetime="2017-03-08 17:15:27" /var/lib/mysql/mysql_bin.000032 > recover.sql
[root@host1 ~]# mysqlbinlog -d jack --stop-datetime="2017-03-08 17:18:29" /var/lib/mysql/mysql_bin.000033 >> recover.sql
mysql> use jack;
mysql> source ~/recover.sql
mysql> commit;
mysql> select * from yfshare;
+------+---------+
| id   | name    |
+------+---------+
| 1    | jack    |
| 2    | yfshare |
| 3    | tom     |
| 4    | jerry   |
| 5    | bob     |
| 6    | aaa     |
| 7    | bbb     |
| 8    | ccc     |
| 9    | ddd     |
+------+---------+
9 rows in set (0.00 sec)

mysql>

OK，恢复成功

Mysql备份恢复脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#mysql备份脚本目录结构
[root@host1 ~]# tree /backup/mysql_backup/
/backup/mysql_backup/
├── backup_20170308.log
├── Full_backup
│   └── 20170308
│       ├── all_databases_backup_20170308_17:06:08.sql.gz
│       └── jack_backup_20170308_17:06:08.sql
├── Full_backup_time.txt
├── Inc_backup
│   └── 20170308
│       ├── jack_Inc_20170308_17:15:27.sql
│       └── jack_Inc_20170308_17:15:27.zip
├── mysql_db.txt
└── mysql_index.txt

4 directories, 8 files
[root@host1 ~]#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
[root@host1 ~]# cat mysql_back.sh
#mysql全量增量备份脚本
#!/bin/bash
# create user 'mysqlback'@'localhost' identified by '123456';
# grant select,lock tables,reload,super,file,show view on *.* to 'mysqlback'@'localhost' identified by '123456';
# flush privileges;
# \q
DATE=`date '+%Y%m%d'`
DATE_log=`date '+%Y-%m-%d %H:%M:%S'`
DATE_filename=`date '+%Y%m%d_%H:%M:%S'`
DATE_backup_time=`date '+%Y-%m-%d %H:%M:%S'`

mysql_host="192.168.31.110"
mysql_user="root"
mysql_pass="123456"

Mysql_Backup="/backup/mysql_backup"
Full_Backup="$Mysql_Backup/Full_backup"
Inc_Backup="$Mysql_Backup/Inc_backup"
Mysql_index="$Mysql_Backup/mysql_index.txt"

Mysql_data="/var/lib/mysql"
Mysql_bin="/usr/bin"
Mysql_db="$Mysql_Backup/mysql_db.txt"
Mysql_logbin_name="mysql_bin"
Mysql_binlog="/usr/bin/mysqlbinlog"

MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQLADMIN="/usr/bin/mysqladmin"
SOCKET="/var/lib/mysql/mysql.sock"

Error_log="$Mysql_Backup/backup_error_${DATE}.log"
Backup_log="$Mysql_Backup/backup_${DATE}.log"

[ ! -d "$Mysql_Backup" ] && mkdir -p "$Mysql_Backup"
[ ! -d "$Full_Backup" ] && mkdir -p "$Full_Backup"
[ ! -d "$Inc_Backup" ] && mkdir -p "$Inc_Backup"

cd $Mysql_Backup
$MYSQL -u"$mysql_user" -p"$mysql_pass" -Ne 'show databases' | grep -vw 'test' | grep -vw 'performance_schema' | grep -vw 'information_schema' | grep -vw 'mysql' > $Mysql_db

function Full() {
echo -e 'Full backup of MySQL database... Please wait.\n'
cd $Mysql_Backup
[ ! -d "$Full_Backup"/"$DATE" ] && mkdir -p "$Full_Backup"/"$DATE"
echo "$DATE_backup_time" > "$Mysql_Backup"/Full_backup_time.txt

#backup all-databases
$MYSQLDUMP --flush-logs --quick --opt --master-data=2 --tz-utc=true --ignore-table=mysql.event -u"$mysql_user" -p"$mysql_pass" --all-databases | gzip > "$Full_Backup"/"$DATE"/all_databases_backup_"$DATE_filename".sql.gz
if [ $? -eq 0 ];then
  /bin/logger "[ "$DATE_log" ] - Databases all-databases has been backup successful."
  echo -e "[ "$DATE_log" ] - Databases all-databases has been backup successful.\n" >> "$Backup_log"
  /bin/sleep 5
fi

#Backup a single database
[ ! -s "$Mysql_db" ] && echo -e 'Error: Backup a single database. Do not back up mysql system database.[test|performance_schema|information_schema|mysql]\n' && echo -e 'Mysql database full backup success.' && exit 1
for DBNAME in `cat $Mysql_db`
do
  $MYSQLDUMP --ignore-table=mysql.event --skip-lock-tables --quick --opt --master-data=2 --tz-utc=true -u"$mysql_user" -p"$mysql_pass" -S "$SOCKET" "$DBNAME" | gzip > "$Full_Backup"/"$DATE"/"$DBNAME"_backup_"$DATE_filename".sql.gz
  if [ $? -eq 0 ];then
    /bin/logger "[ "$DATE_log" ] - Databases "${DBNAME}" has been backup successful."
    echo "[ "$DATE_log" ] - Databases "${DBNAME}" has been backup successful." >> "$Backup_log"
    /bin/sleep 5
  fi
done
echo -e '----------------------------------------\n' >> "$Backup_log"

cd $Mysql_Backup
find "$Full_Backup" -mtime +10 | xargs rm -rf {} \; &> /dev/null
echo 'Mysql database full backup success.'
}

function Inc() {
echo -e 'Increment backup of MySQL database... Please wait.\n'
[ ! -f "$Mysql_Backup"/Full_backup_time.txt ] && echo -e 'Error: Please perform a full backup first. command: sh mysql_back.sh Mysql_Fullbackup' && exit 1

[ ! -s "$Mysql_db" ] && echo -e "Error: file $Mysql_db is empey. There may only be a system database[test|performance_schema|information_schema|mysql]." && exit 1

cd $Mysql_Backup

"$MYSQLADMIN" -u"$mysql_user" -p"$mysql_pass" flush-logs
awk -F'/' '{print $NF}' "$Mysql_data"/mysql_bin.index > "$Mysql_index"

[ ! -s "$Mysql_index" ] && echo -e "Error: Please open the mysql binlog log. The command in the my.cnf file: log_bin=mysql_bin and binlog_format=Mixed" && exit 1
[ ! -d "$Inc_Backup"/"$DATE" ] && mkdir -p "$Inc_Backup"/"$DATE"

DATE_starttime=`cat "$Mysql_Backup"/Full_backup_time.txt`
start='--start-datetime'
for DBNAME in `cat $Mysql_db`
do
  for index in `cat $Mysql_index`
  do
    "$Mysql_binlog" -u"$mysql_user" -p"$mysql_pass" -d "$DBNAME" "$start"="$DATE_starttime" "$Mysql_data"/"$index" >> "$Inc_Backup"/"$DATE"/"$DBNAME"_Inc_"$DATE_filename".sql
    /bin/sleep 1
  done
  if [ $? -eq 0 ];then
      cd "$Inc_Backup"/"$DATE"
      /usr/bin/zip -q "$DBNAME"_Inc_"$DATE_filename".zip "$DBNAME"_Inc_"$DATE_filename".sql
      /bin/logger "[ "$DATE_log" ] - Databases "${DBNAME}" has been Inc_backup successful."
      echo "[ "$DATE_log" ] - Databases "${DBNAME}" has been Inc_backup successful." >> "$Backup_log"
      rm -rf "$DBNAME"_Inc_"$DATE_filename".sql
    fi
done
echo -e '----------------------------------------\n' >> "$Backup_log"
cd $Mysql_Backup
find "$Inc_Backup" -mtime +10 | xargs rm -rf {} \; &> /dev/null
echo 'Mysql database Increment backup success.'
}

case $1 in 
	Mysql_Fullbackup)
		Full;;
	Mysql_Incbackup)
		Inc;;
	*)
		echo -e "Usage: please input Mysql_Fullbackup|Mysql_Incbackup\n";;
esac
[root@host1 ~]#

本作品采用知识共享署名 2.5 中国大陆许可协议进行许可，欢迎转载，但转载请注明来自Jack Wang Blog，并保持转载后文章内容的完整。本人保留所有版权相关权利。
打赏
本文出自”Jack Wang Blog”：http://www.yfshare.vip/2017/03/08/Mysql%E5%A4%87%E4%BB%BD%E6%81%A2%E5%A4%8D/