MYSQL备份与恢复精华篇-白红宇

MYSQL备份与恢复精华篇

阅读量：5887 次

发布时间：2019-06-19

本文共 6662 字，大约阅读时间需要 22 分钟。

数据备份原理

数据备份属于数据容灾保护中的内容，所有的数据备份系统设计都基于这五个元素，备份源、备份目标、传输网络、备份引擎和备份策略。用户按照需要制定备份策略，使用定时任务执行备份脚本，使用备份引擎将需要备份的的数据从备份源通过传输网络传送到备份目标。

备份五元组：

1、备份源

需要备份的数据统一称为备份源，可以是文本数据，音视频数据，也可以是数据库数据等等。

2、备份目标

存放备份数据的位置，通常建议将备份数据存放在异机，或者是更远的数据中心，备份目标可以是在线的磁盘，磁盘阵列柜，也可以是磁带库或是虚拟带库。而备份目标所在的位置可以在同一个数据中心，也可以是容灾机房。

3、传输网络

备份数据时使用的传输链路，可以是专线，以太网，Internet，VPN等等，只要保证备份源与目标之间的路由可达即可。

4、备份引擎

数据要能够从源到目标流动，就要有动力，就像是水要流动一样，这个动力来源就是备份引擎，像mysqldump ，nvbu，还有大量的备份软件都是备份引擎。

5、备份策略

为了有效备份，并减少人为操作，应该制定完善的备份策略。通常全备与差备与增备相结合，备份的时间点应该尽量避开业务高锋期，通常在晚上执行，通过定时任务实现。

MYSQL数据备份原理

mysql数据备份其实就是通过SQL语句的形式将数据DUMP出来，以文件的形式保存，而且导出的文件还是可编辑的，这和Oracle数据库的rman备份还是很不一样的，mysql更像是一种逻辑备份从库中抽取SQL语句，这就包括建库，连库，建表，插入等就像是将我们之前的操作再通过SQL语句重做一次。

MYSQL的日志类型

1、二进制日志（log-bin）

二进制日志非常重要，二进制日志会记录mysql数据库的所有变更操作，其实和oracle的redolog日志原理差不多，由于它记录的所有的操作，于是我们就可以使用某个时间点之后的二进制日志做前滚操作，来增量恢复数据。

mysql的二进制日志可以使用mysqlbinlog来进行查看和过滤，一直过滤到我们想要的数据再导入数据库，而且也是非常方便的，但尤其要强调的是要严格按照二进制日志生成的顺序执行。

用途：记录所有变更操作，用于增量备份

配置：在my.cnf中添加

[mysqld]

log-bin =mysql-bin

log-bin-index =mysql-bin.index

2、中继日志（relay-log）

顾名思义，传递日志，主要用在主从复制的架构中，只在从库中有中继日志（多级复制除外）在从库中将主库复制过来的二进制日志保存为中继日志，用于从库重构数据。

配置：在my.cnf中添加

[mysqld]

relay-log =relay-log

relay_log_index= relay-log.index

3、慢查询日志（slow_query_log）

慢查询日志主要用于mysql优化，从数据库中找出哪些SQL语句是比较慢的，将其放到一个文件中，后续可以使用mysqlsla工具去对慢查询语句进行分析，将分析结果提交给开发进行SQL优化。

用途：找出慢查询进行优化

配置：在my.cnf中添加

[mysqld]

slow_query_log= 1

long-query-time= 2

slow_query_log_file= /data/3306/slow.log

4、一般查询日志

会记录所有访问mysql的行业，因此会产生大量日志，一般建议关闭。

配置：在my.cnf中添加

[mysqld]

general_log = 1

log_output =FILE

general_log_file= /home/mysql/mysql/log/mysql.log

5、错误日志

记录mysql产生的错误，这个日志在排错的时候相当有用，一般建议开启。

配置：在my.cnf中添加

[mysqld]

log-warnings =1

log-error =/home/mysql/mysql/log/mysql.err

6、事务日志

缓存事务提交的数据，实现将随机IO转换成顺序IO。

配置：在my.cnf中添加

[mysqld]

innodb_log_buffer_size= 16M

innodb_log_file_size= 128M

innodb_log_files_in_group= 3

一、mysqldump|mysql|mysqlbinlog工具使用

mysqldump

命令介绍：

mysqldump是mysql提供的一个基于命令行的mysql数据备份工具，提供了丰富的参数选择，用于各种需求形式的备份，如单库备份，多库备份，单表与多表备份，全库备份，备份表结构，备份表数据等。

语法格式：

1、单库备份

                         
mysqldump -uroot -p123456 -S 
/data/3306/data/mysql
.sock \
库名 >
/opt/backup/mysql_bak_db
.sql
        
      

2、多库备份

                         
mysqldump -uroot -p123456 -S 
/data/3306/data/mysql
.sock \
-B 库1 库2 库3 >
/opt/backup/mysql_bak_db
.sql
        
      

多个库之间用空格分隔

3、单表备份

                         
mysqldump -uroot -p123456 -S 
/data/3306/data/mysql
.sock \
库名 表名>
/opt/backup/mysql_bak_db
.sql
        
      

4、多表备份

                         
mysqldump -uroot -p123456 -S 
/data/3306/data/mysql
.sock \
库名表1 表2>
/opt/backup/mysql_bak_db
.sql
        
      

5、全库备份

                         
mysqldump -uroot -p123456 -S 
/data/3306/data/mysql
.sock \
-A >
/opt/backup/mysql_bak_db
.sql
        
      

常用参数解析：

1、-B

如果想一次备份多个库需要添加B参数，B参数会在备份数据中添加create database和use语句

2、-F

在备份之前会先刷新日志，可以看到二进制文件前滚产生新的二进制文件。

3、--master-data

有二个值1或者2，等于1会在备份数据中增加如下语句：

                         
CHANGE MASTER TO MASTER_LOG_FILE=
'mysql-bin.000040'
,MASTER_LOG_POS=4543;

等于2会在备份数据中增加如下语句：

                         
-- CHANGE MASTER TO MASTER_LOG_FILE=
'mysql-bin.000040'
,MASTER_LOG_POS=4543;

唯一区别就是有没有被“--”注释掉，如果备份的数据用于slave，等于1即可，此时从库就知道应该从哪个地方开始读二进制日志，如果仅用于备份标识当前二进制是哪一个和位置点等于2合适。

4、-x

锁表，备份的时候锁表来保证数据一致性。

5、-d

只备份表结构不备份数据。

6、-t

只备份表数据而不备份表结构。

7、--compact

用于测试，会在屏幕上打印备份结果，上面的几个例子就使用了这个参数，但真正备份时是不会用的。

5、--single-transaction

Innodb引擎保证数据一致性的参数，使用此参数后会话的安全隔离级别会被置为repeatble-read，此时其它会话提交的数据是不可视的，从而保证数据的一致性。

更多参数请参考help

mysql

命令介绍：

mysql通常用于登陆mysql服务器，然后执行操作，其实mysql也提供了非交互式执行mysql命令的参数，同时mysql命令也是数据恢复非常重要的工具。

语法格式：

1、登陆mysql服务器

                         
mysql -uroot -p123456 -S 
/data/3306/data/mysql
.sock

加S参数是考虑的多实例的情况，以下类同

2、非交互式执行mysql语句

                         
mysql -uroot -p123456 -S 
/data/3306/data/mysql
.sock  -e “use capinfo; showtables;”

3、数据恢复

                         
mysql -uroot -p123456 -S 
/data/3306/data/mysql
.sock  <mysql_bak_capinfo.sql

4、使用source恢复数据

登陆mysql服务器后，使用source命令也可以恢复数据。

mysqlbinlog

命令介绍：

处理二进制日志工具，可用于查看，从二进制日志恢复数据，以及过滤需要的数据导出到文件。

常用参数：

1、-d or –database

　　过滤出需要的数据库数据；

2、--start-position --stop-position

　　过滤出开始位和结束位置的数据；

3、--start-datetime --stop-datetime

　　过滤出开始时间和结束时间段数据；

功能：

1、查看二进制日志

mysqlbinlog 二进制日志名

2、过滤指定数据库数据并导出到文件

mysqlbinlog –d 库名　二进制日志名 > capinfo.log

3、使用二进制日志做增量恢复

示例：指定位置的增量恢复

                         
mysqlbinlog -d capinfo --start-position=98 --stop-position=768 \
mysql-bin.000038 |mysql -uroot -p123456 -S
/data/3306/data/mysql
.sock

指定时间段的增量恢复

                         
mysqlbinlog -d capinfo --start-datetime=”2015-02-27 09:30:00”\
--stop-datetime=”2015-02-27 12:30:00” mysql-bin.000038|mysql \
-uroot -p123456 -S 
/data/3306/data/mysql
.sock
        
      

注意：通常我们只会过滤出需要的数据，将其先放到一个文件里面，然后可以对文件进行二次过滤，或者直接编辑文件再导入，这样是比较妥当的做法。

二、备份参数优化

1、myISAM引擎备份参数优化

                         
mysqldump -uroot -p123456 -F -A -B -x\
--master-data=2| 
gzip
>
/opt/backup/mysql_bak_db_full
.sql.gz
        
      

2、innoDB引擎备份参数优化

                         
mysqldump -uroot -p123456 -F -A -B --single-transaction\
--master-data=2| 
gzip
>
/opt/backup/mysql_bak_db_full
.sql.gz
        
      

唯一区别：myISAM引擎使用x参数锁表备份，而innoDB引擎则使用—single-transaction将会话的隔离级别修改成repeatble-read来保证数据一致性，显然innodb引擎的数据控制粒度更强。

三、分库分表备份脚本实现

1、分库备份实现脚本

脚本思路：

如果库较少，可以使用forin db1 db2 db3；do ;done来实例

如果库较多，则应该先用showdatabases取出库名，做为for循环变量的取值列表，然后再执行备份操作：

取库名：

                         
mysql-uroot -p123456 -S 
/data/3306/data/mysql
.sock -e 
"showdatabases;"
|
egrep
- 
v
"Database|information_schema|mysql|performance_schema"
        
      

备份一个库：

                         
mysqldump -uroot -p123456 -S
/data/3306/data/mysql
.sock \
--compact-B -F --master-data=2 --single-transaction –events\ 
capinfo|
gzip
>
/opt/backup/mysql_bak_db_capinfo
.sql.gz
        
      

了解脚本思路了脚本就很好写了，下面自己编写的一个示例脚本：

                         
#!/bin/sh
 
cmdPath=
"/usr/local/mysql/bin"
my_user=
"root"
my_pass=
"123456"
bakDir=
"/opt/backup"
socketfile=
"/data/3306/data/mysql.sock"
 
dbList=`mysql -u$my_user-p$my_pass -S $socketfile -e 
"show databases"
\
        
|
egrep
-
v
"Database|information_schema|mysql|performance_schema"
`
 
for
db 
in
$dbList
do
        
$cmdPath
/mysqldump
-u$my_user-p$my_pass -S $socketfile -B -F \
        
--master-data=2 --single-transaction--events \
        
$db | 
gzip
>$bakDir
/mysql_bak_db_
${db}_$(
date
+%Y%m%d).sql.gz
done
        
      

2、分表备份实现脚本

脚本思路：

大致思路是在库中再分表进行备份，那就会用到for循环嵌套，外层for循环是库，内层for循环是表，并将每个库中的表存放在各自库名命名的目录下面。

取到库中的表名：

                         
mysql -uroot -p123456 -S 
/data/3306/data/mysql
.sock -e 
"showtables from capinfo"
|
sed
'1d'
        
      

示例脚本：

                         
#!/bin/sh
 
cmdPath=
"/usr/local/mysql/bin"
my_user=
"root"
my_pass=
"123456"
bakDir=
"/opt/backup"
socketfile=
"/data/3306/data/mysql.sock"
 
dbList=`mysql-u$my_user -p$my_pass -S $socketfile -e 
"show databases"
\
        
|
egrep
-
v
"Database|information_schema|mysql|performance_schema"
`
 
fordb 
in
$dbList
do
        
table=`mysql -u$my_user -p$my_pass -S$socketfile -e \
"showtables from $db"
|
sed
'1d'
`
        
for
tb 
in
$table
        
do
                
mkdir
-p $bakDir/$db
                
$cmdPath
/mysqldump
-u$my_user-p$my_pass -S $socketfile -F \
                
--master-data=2--single-transaction --events $db $tb | 
gzip
>\
$bakDir/${db}
/mysql_bak_tb_
${tb}_$(
date
+%Y%m%d).sql.gz
        
done
done
        
      