MySQL备份策略

MySQL备份是我们运维过程中再正常不过的事情了,这里总结一下以前备份的方法,使用的是xtrabackup,当然这里的备份方法还有mysqldump这个是mysql自己的备份工具,不过它支持的比较简单,仅支持全量+binlog进行恢复,对于数据量大的企业,你总不能每周两三次的全量备份+二进制日志做时间点还原吧,显然这里我们需要增量备份的支持;
MySQL备份(基于xtrabackup)的实战

简单说一下全量,增量,二进制日志分别的意思:

比如A数据库

    周一这天数据一共10g
    周二这天数据一共12g

全量:也就是对周一这天数据库当前的10g数据,进行全部备份,也叫完整备份,但数据比较大恢复比较慢

增量:增量也就是周二这天数据一共12g,和上次差异相比+2g数据,由于我们昨天做了一次全量备份,今天只需要备份今天的2g就可以了(mysqldump不能这样,他只能昨天10g,今天再备个12g)

注:差异和增量概念有点模糊,可以自行百科

二进制日志:用于记录引起数据改变或存在引起数据改变的潜在可能性的语句(STATEMENT)或改变后的结果(ROW),也可能是二者混合;

(也就是说你的但凡能够引起数据变化的增删改都会在这有记录,我们一般做时间点还原用,因为比如你晚上2点刚增量完成,3点时候后数据库崩溃了,那么2点到3点之间我们是没有任何备份策略的,好在数据库的变化都在二进制中有记录,我们可以将二进制日志中2-3点的记录提取出来,当做备份使用)

MySQL日志分类

在数据库日志中,分为6类(*表示重点,建议开启):

查询日志:general_log    
        记录信息:
            记录查询语句,日志存储位置:
                文件:file
                表:table (mysql.general_log)
        打开方法:
            general_log={ON|OFF}
            general_log_file=HOSTNAME.log 
            log_output={FILE|TABLE|NONE}

*慢查询日志:log_slow_queries
        慢查询:运行时间超出指定时长的查询;
            long_query_time
        存储位置:
            文件:FILE
            表:TABLE,mysql.slog_log

            log_slow_queries={ON|OFF}
            slow_query_log={ON|OFF}
            slow_query_log_file=
            log_output={FILE|TABLE|NONE}            
            log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
            log_slow_rate_limit
            log_slow_verbosity

*错误日志:log_error, log_warnings   
        记录信息:
            (1) mysqld启动和关闭过程 输出的信息; 
            (2) mysqld运行中产生的错误信息; 
            (3) event scheduler运行时产生的信息;
            (4) 主从复制架构中,从服务器复制线程启动时产生的日志;
        打开方法:
            log_error=/var/log/mariadb/mariadb.log|OFF
            log_warnings={ON|OFF}

*二进制日志:binlog       但凡能够引起数据变化的增删改都会在这
        打开方法:
            log_bin=/PATH/TO/BIN_LOG_FILE   只读变量;
            session.sql_log_bin={ON|OFF}    控制某会话中的“写”操作语句是否会被记录于binlog日志文件中;
            max_binlog_size=1073741824      单位为字节,这里为1G,binlog单个最大
            sync_binlog={1|0}               控制数据库的binlog刷到磁盘上去,1是每次事务提交,MySQL都会把binlog刷下到磁盘,是最安全但是性能损耗最大的设置,0是由文件系统自己控制它的缓存的刷新。这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失为,0和设置为1的系统写入性能差距可能高达5倍甚至更多。

中继日志:relay_log      从服务器上记录下来从主服务器的二进制日志文件同步过来的事件,主从模式才会用到;
事务日志:innodb_log     innodb事务日志包括redo log和undo log。
    redo log是重做日志,提供前滚操作,
    undo log是回滚日志,提供回滚操作。

备份的必要性和注意点

备份时应该注意事项:
    能容忍最多丢失多少数据;
    恢复数据需要在多长时间内完成;
    需要恢复哪些数据;

备份需要考虑因素:
    锁定资源多长时间?
    备份过程的时长?
    备份时的服务器负载?
    恢复过程的时长?

mysqldump介绍和示例

上面是一些简单的叙述,这里也捎带着提一下mysqldump的备份参数,但不做细细分析

mysqldump:
    mysqldump是一个单进程的备份工具,同一时刻内只能备份一个表
    mysqldump的备份原理是查询当前要备份的数据库表的所有数据,全部转换成插入语句

不同的引擎备份参数:
        MyISAM存储引擎:支持温备,备份时要锁定表;
                -x, --lock-all-tables:锁定所有库的所有表,读锁;
                -l, --lock-tables:锁定指定库所有表;

        InnoDB存储引擎:支持温备和热备;
                --single-transaction:创建一个事务,基于此快照执行备份;

其它选项(建议必选):
        -R, --routines:存储过程和存储函数;
        --triggers      触发器
        -E, --events    事件
        --master-data[=#]
            1:记录为CHANGE MASTER TO语句,此语句不被注释;
            2:记录为CHANGE MASTER TO语句,此语句被注释;用于查看下次binlog恢复时,应基于哪个位置开始恢复

        --flush-logs:备份时锁定表完成后,即进行日志新文件滚动刷新;方便binlog恢复时,我们基于上次备份sql和一个新的binlog文件恢复

mysqldump的备份示例:

备份hellodb数据库,myisam引擎,由于不支持事务和表空间序列号,只能温备(只读不能写)或冷备
    MyISAM:
        备份hellodb库中的new表:
            mysqldump -uroot -proot -R -E --triggers --master-data=2 --flush-logs -l hellodb new > /data1/hellodb-fullback-$(%data +%F).sql 
            ##表恢复时,若原始库不存在,不会自动创建库,需要自己先创建库

        备份一个库:
            mysqldump -uroot -proot -R -E --triggers --master-data=2 --flush-logs -l hellodb > /data1/hellodb-fullback-$(%data +%F).sql
        备份所有库:
            mysqldump -uroot -proot -R -E --triggers --master-data=2 --flush-logs -x --all-databases > /data1/alldb-fullback-$(%data +%F).sql

备份hellodb数据库,INNODB引擎,支持事务和表空间序列号,这里做热备(热备的选择根据是否会影响当前业务,因为会对服务器造成负载,对业务影响先评估)MyISAM的参数-x/-l仍然支持
    InnoDB:
        备份hellodb库中的new表:
            mysqldump -uroot -proot -R -E --triggers --master-data=2 --flush-logs --single-transaction hellodb new > /data1/hellodb-fullback-$(%data +%F).sql
        备份一个库:
            mysqldump -uroot -proot -R -E --triggers --master-data=2 --flush-logs --single-transaction hellodb > /data1/hellodb-fullback-$(%data +%F).sql
        备份所有库:
            mysqldump -uroot -proot -R -E --triggers --master-data=2 --flush-logs --single-transaction --all-databases > /data1/alldb-fullback-$(%data +%F).sql

还原数据时:
        进入mysql先关闭binlog的记录:set @@session.sql_log_bin=OFF;
        当还原完成时应及时马上做一次全量备份,若业务紧急上线,也应做热备全备

binlog恢复:
        mysqlbinlog master-01.00005 > binlog.sql

Xtrabackup备份

Xtrabackup是一个对InnoDB做数据备份的工具,支持在线热备份(备份时不影响数据读写),是商业备份工具InnoDB Hotbackup的一个很好的替代品。

默认安装xtrabackup会有innobackupex命令和xtrabackup,我们一般使用innobackupex,但其实innobackupex也是软连接的xtrabackup

软件包提供:链接:https://pan.baidu.com/s/1Ay42kAecCgJhyqAClKM0CA 密码:w7fe

或者去官网下载最新版:https://www.percona.com/

InnoDB全量+binlog

完整备份:

innobackupex --defaults-file=/etc/my.cnf --user=root --password=root ~/     ##完整备份
MariaDB [(none)]> INSERT INTO hellodb.students values (26,'ifan',19,'M',6,1);   ##插入数据

模拟故障:

systemctl stop mariadb
cp /var/lib/mysql/DB-bin.* /data/
rm -rf /var/lib/mysql/*

数据恢复:

cd 2019-03-05_22-22-19/         ##进入全备
innobackupex --apply-log ./     ##由于这是全备没有差异备份和增量备份,所以不考虑未完成的事务,进行合并lsn,并回滚未完成事务

innobackupex --copy-back ./     ##lsn合并和事务回滚后,进行还原数据
chown -R mysql.mysql /var/lib/mysql     ##重新设置属主属组权限

systemctl start mariadb         ##启动数据库即可

根据binlog增量恢复:

[root@localhost 2019-03-05_22-22-19]# cat xtrabackup_binlog_info        ##查看全备完成的位置
DB-bin.000003   9116
[root@localhost 2019-03-05_22-22-19]# mysqlbinlog -j 9116 /data/DB-bin.000003 > binlog.sql
[root@localhost 2019-03-05_22-22-19]# mysql -uroot -proot
MariaDB [(none)]> set @@session.sql_log_bin=OFF;
MariaDB [(none)]> source binlog.sql                 ##还原binlog.sql,恢复第26条内容
MariaDB [(none)]> select * from hellodb.students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
                        .........
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | ifan          |  19 | M      |       6 |         1 |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=root ~/     ##还原完成后,马上基于本次立即做全备
[root@localhost ~]# ls
2019-03-05_23-19-54

InnoDB全量+增量+binlog

我们先做一次全量备份:
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=root ~/
[root@localhost ~]# ls
2019-03-06_00-04-44 
删除一部分数据
[root@localhost ~]# mysql -uroot -proot
MariaDB [hellodb]> delete from students where StuID='10';  
Query OK, 1 row affected (0.00 sec)

[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=root --incremental ~/ --incremental-basedir=~/2019-03-06_00-04-44/      ##进行增量备份
[root@localhost ~]# ls
2019-03-06_00-04-44  2019-03-06_00-09-25
查看全量的xtrabackup_checkpoints文件记录的lsn值:
[root@localhost ~]# cat 2019-03-06_00-04-44/xtrabackup_checkpoints
backup_type = full-backuped ##全量备份
from_lsn = 0            ##由于我这个是新还原的数据库,所以起始位置为0
to_lsn = 1634678        
last_lsn = 1634678
compact = 0
recover_binlog_info = 0
[root@localhost ~]# cat 2019-03-06_00-09-25/xtrabackup_checkpoints
backup_type = incremental   ##增量备份
from_lsn = 1634678      ##增量备份的起始位置应该是继承上次备份的位置
to_lsn = 1635829        ##这里是本次备份的最后备份位置1635829,下次增量起始位置将紧接着这个位置
last_lsn = 1635829
compact = 0
recover_binlog_info = 0

紧接着我们再删除一个ID为20的同学,再做一次增量备份:
MariaDB [(none)]> delete from hellodb.students where StuID='20';    
Query OK, 1 row affected (0.00 sec)
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=root --incremental ~/ --incremental-basedir=~/2019-03-06_00-09-25/      ##这次增量要基于上次增量的位置
[root@localhost ~]# ls      
2019-03-06_00-04-44  2019-03-06_00-09-25  2019-03-06_00-16-41
##查看新备份的增量的xtrabackup_checkpoints
[root@localhost ~]# cat 2019-03-06_00-16-41/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1635829          ##这里的起始位置是上次增量的位置以此类推
to_lsn = 1637002
last_lsn = 1637002
compact = 0
recover_binlog_info = 0

紧接着我们再插入一个值,用binlog恢复:

MariaDB [(none)]> INSERT INTO hellodb.students values (30,'xiaopingfan',19,'M',6,1);
Query OK, 1 row affected (0.00 sec)

为了我们binlog恢复:
我们要将最近一次增量备份最后的binlog位置找到,这样我们才能恢复最近一次增量到数据库崩溃时之间的数据:
首先查看最近一次binlog备份的最后位置:
[root@localhost ~]# cat 2019-03-06_00-16-41/xtrabackup_binlog_info 
DB-bin.000001   8132        
[root@localhost ~]# cp /var/lib/mysql/DB-bin.000001 /root/      ##将这段二进制日志备份出来
[root@localhost ~]# mysqlbinlog -j 8132 DB-bin.000001 > binlog.sql      ##转化成sql

开始恢复:

systemctl stop mariadb
rm -rf /var/lib/mysql/*
这里我们一共产生一次全量备份和两次增量备份,我们要先将全量备份的事务合并(已完成的事务进行合并,未完成的事务不处理,等待后续的增量备份事务进行合并),然后将两次增量(有顺序)的备份进行依次合并到全量备份上,最后再合并一次全量备份(已完成的事务进行合并,未完成的事务由于后续没有了增量备份,未完成的事务就进行回滚,不能让其生效)
记住,在合并的时候就像三堆土,既然要并在一次,肯定是两小堆依次合并在那一堆大的土堆上面

增量开始恢复:
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only 2019-03-06_00-04-44/           ##已完成的事务进行合并,未完成的事务不处理,等待后续的增量备份事务进行合并

[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only 2019-03-06_00-04-44/ --incremental-dir=2019-03-06_00-09-25/        ##将第一次增量合并到全量上

[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only 2019-03-06_00-04-44/ --incremental-dir=2019-03-06_00-16-41/        ##将第二次增量合并到全量上

[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --apply-log 2019-03-06_00-04-44/   ##所有的增量恢复完,最后对全备已完成的事务进行合并,未完成的事务回滚

[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back 2019-03-06_00-04-44/
[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# mysql -uroot -proot
MariaDB [(none)]> set @@session.sql_log_bin=OFF;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| DB-bin.000001 |       245 |
+---------------+-----------+
1 row in set (0.00 sec)

MariaDB [(none)]> source binlog.sql
Query OK, 0 rows affected (0.00 sec)
...
Query OK, 0 rows affected (0.00 sec)

查看xiaopingfan是否存在:

MariaDB [(none)]> select * from hellodb.students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    30 | xiaopingfan   |  19 | M      |       6 |         1 |
+-------+---------------+-----+--------+---------+-----------+
24 rows in set (0.00 sec)

数据恢复完成,马上对此次回复完成的数据库做一次全量备份:
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=root ~/

--apply-log ##分析获取binary log文件生成backup_binlog_info文件
--redo-only ##只提交已完成事务
--copy-back ##还原数据库

注:如果库中有MyISAM引擎的表,库中如果有innodb和myisam和innodb并存,xtrabackup方法下按照innodb备份即可,由于innodb引擎的增量备份是基于LSN来实现的,myisam不支持事务和lsn所以对于myisam表每次都是全量备份,对于innodb表则是增量备份

查看数据库内所有表引擎脚本:

#!/bin/bash

myuser='root'
mypass='root'
myport=3306

base_list=`mysql -u${myuser} -p${mypass} -P${myport} -e "show databases;"|egrep -v 'mysql|Database|information_schema|performance_schema'`           ##得到库列表
for k in ${base_list};
do
table_list=`mysql -u${myuser} -p${mypass} -P${myport} -e "use ${k};show tables;"|egrep -v 'Tables_in'`                               ##得到表列表
        for g in ${table_list};
        do

ccmd=`mysql -u${myuser} -p${mypass} -P${myport} -e "show table status from ${k} where name='${g}'\G"|egrep -w Engine|awk -F ':' '{print $2}'`
        echo -e "数据库:${k} 下 ${g}表的引擎为: ${ccmd}"
        done
done