MySQL - 记一次MySQL备份

记一次MySQL备份

最近在实际开发当中，需要给一个100G左右的数据库做备份。这里记录下备份的过程，后期慢慢再优化。

前期准备

在进入到MySQL备份方案正题之前，我们先来了解几个概念。

undo log

undo log在操作数据之前，将需要被操作的数据备份到一个地方。

在事务未提交之前，undo log保存了未提交之前的数据。这部分数据可以作为快照用于回滚和提供多版本并发控制下的读（MVCC）。

undo log属于Mysql InnoDB层。 

redo log

redo log记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败（media failure）时，如数据库掉电，InnoDB存储引擎会根据redo log恢复到掉电前的时刻，以此来保证数据的完整性。

在一条更新语句进行执行的时候，InnoDB引擎会把更新记录写到redo log日志中，然后更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中，这里涉及到WAL即Write Ahead logging技术，关键点是先写日志，再写磁盘。

undo log 和 redo log

假设有A、B两个数据，值分别为1,2，现在修改为3、4。下面是在这次事务中，undo log和redo log的简易处理过程：

1. 事务开始。
2. 记录A=1到undo log。
3. 修改A=3。
4. 记录A=3到redo log。
5. 记录B=2到undo log。
6. 修改B=4。
7. 记录B=4到redo log。
8. 将redolog写入磁盘。
9. 事务提交。

bin log

bin log是记录所有数据库表结构变更（DML。例如CREATE、ALTER TABLE…）以及表数据修改（DDL。例如INSERT、UPDATE、DELETE…）的二进制日志。它以事件形式记录，还包含语句执行所消耗的时间，MySQL的二进制日志是事务安全型的。bin log 的主要目的是复制和恢复。

bin log不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。

bin log包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）用于记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

relay log

relay log是中继日志。

在MySQL主从复制中，从库把从主库同步来的bin log复制到relay log中，然后定时重放relay log，从而达到更新目的。

区别

• redo/undo log属于InnoDB层面；bin/relay log属于MySQL Server层面的。
• redo/undo log是物理日志，记录该数据页更新的内容；bin/relay log是逻辑日志，记录的是这个更新语句的原始逻辑。
• redo log是循环写，日志空间大小固定；binlog是追加写，一个文件写到一定大小的时候会更换下一个文件，不会覆盖。
• bin log可以在恢复数据、主从复制搭建时使用；redo log可以在异常宕机或者介质故障后的数据恢复中使用。

备份方案

根据实际业务表分析，我发现在这100G的数据中，有一些数据是必须要长期存在的，而有一些数据是定时删除的。而且在当前背景下，如果直接开启两个数据库的主从同步，主库之前已存在的数据是没有binlog记录的。

所以这里采用的方案是历史数据用dump文件同步，dump同步之后的数据使用主从库同步。开启主从同步之后，binlog备份是主从两台服务器上都有，然后dump备份文件只在从库服务器上生成，这样应该可以缓解一部分主数据库的压力吧。

安装数据库

千里之行，始于足下。第一步还是搞一点基础的，CentOS上安装MySQL5.7。

从已存在的主库中导出dump文件

说实在的，在初始化从库数据的这部分工作里，我的导入导出操作全都借助的是MySQL Workbench。

因为有一些表是在太大了，达到了10G左右，而且这些表是会定期删除的，所以我在数据迁移的方案里面不打算用dump迁移这些表。虽然这样可能会导致在定期删除这段时间内的数据不太同步，但我还是愿意相信这段时间不会有什么问题发生（我赌你的枪里没有子弹！）。

另外如果先看全部数据动态迁移的话，可以参考一下我的这篇文章：https://www.cnblogs.com/helios-fz/p/11002270.html 。

导出数据结构：

导入数据结构：

导出数据（一部分）：

导入数据：

以上这部分操作，正常来说应该停服务操作的，除非你对你的线上系统用户访问量非常有自信（自信没几个人访问，且访问的也不会改数据库 😄）。

注1：

我在查看导出的dump文件时，想验证一下数据表分区命令有没有一起导出，结果看到了如下内容：

 

我一度非常疑惑为什么把我的分区命令给注释掉，直到我查阅到了如下资料：

• /*...*/  是注释，MySQL不会执行。
• /*!...*/  是MySQL的一种特殊的注释，在其他的数据库产品中不会执行。在MySQL中会选择性的执行。可以认为是：预编译中的条件编译。
• 50100 表示 5.01.00 版本或者更高的版本才会执行。

设置主从同步

修改主服务器配置文件：

vim /etc/my.cnf

添加如下配置：

# 服务器唯一id，因为是主的，所以就写了1
server-id=1
# 定义binlog文件命名规则
log_bin=mysql-bin

修改从服务器配置文件：

vim /etc/my.cnf

添加如下配置：

# 从库server id我这里设置为ip后两组数字
server-id=43175
# 定义binlog文件命名规则
log_bin=mysql-bin

重启两个mysql：

systemctl restart  mysqld.service

在主数据库建立账户并授权slave：

mysql -uroot -p你的密码

GRANT REPLICATION SLAVE ON *.* to '账号'@'%' identified by '密码'; 

查看master状态：

show master status;

执行完这一步之后尽量不要再操作主数据库，防止状态值变化。

配置从数据库slave：

mysql -uroot -p你的密码

change master to master_host='主数据库ip',master_user='用户名',master_password='密码',master_log_file='mysql-bin.000001',master_log_pos=17895398; 

启动从数据库复制：

start slave; 

查看从服务器复制功能状态：

show slave status\G

结果如下：

*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: IP地址
                  Master_User: username
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 17895398
               Relay_Log_File: VSAT-NM05-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

其中 Slave_IO_Running 和 Slave_SQL_Running 状态必须是Yes才行。

注2：

定义binlog文件命名规则时，MySQL官网默认的配置是 mysql-bin，默认文件存储路径是/var/lib/mysql。如果想自定义存储路径，需要在创建完的文件夹执行如下命令：

chown -R mysql.mysql 新文件夹

注3：

关闭从数据库复制行为：

mysql -uroot -p你的密码

stop slave;

show status like '%slave%';

binlog其他配置

查看binlog

默认情况下，binlog日志是二进制格式的，所以不能用文本编辑器查看，要用mysqlbinlog命令：

mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000001

my.cnf 配置项

log_bin：binlog日志文件名前缀，前面加上绝对路径可以定义binlog的存储位置。

expire_logs_days：日志过期时间，0为永不过期。

max_binlog_size：binlog文件最大长度。超过max_binlog_size或超过6小时会切换到下一序号文件，默认1024M。

binlog_cache_size：当前多少事务cache在内存中。

max_binlog_cache_size：binlog cache最大值。

sync_binlog：这个值控制cache的数据commit多少次才刷到磁盘上。默认是0，也就是让数据库自己决定同步的频率。如设置成1的话，则每commit一次就会将cache的数据同步到磁盘上，这样做最安全，但是性能最差。

relay_log_recovery：设置为 1 表示，当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，且重新从master上获取日志，这样就保证了relay-log的完整性。

innodb_flush_log_at_trx_commit：设置为 1 表示，每次事务提交将日志缓冲区写入log file，并同时flush到磁盘。

binlog_do_db和binlog_ingore_db：控制记录哪些或者忽略哪些数据库的binlog。

slave_net_timeout：当slave从主数据库读取log数据失败后，等待多久重新建立连接并获取数据，默认3600（单位：秒）。

master-connect-retry：当重新建立主从连接时，如果连接建立失败，间隔多久后重试。

binlog_format：复制模式。

复制模式有三种：ROW、STATEMENT、MIXED

ROW：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

优点：能清楚的记录每一行数据修改的细节。

缺点：数据量太大。

STATEMENT：每一条被修改数据的SQL都会记录到master的bin-log中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的sql再次执行。

优点：不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高性能。

缺点：容易出现主从复制不一致。

MIXED：以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

定时生成dump文件

回想上面用MySQL Workbench生成dump文件，我寻思着定期备份的话我总不能每天半夜起来打开电脑保存一份，所以还是得找工具，写脚本。

创建脚本文件 mysqlbackup.sh，每天备份数据库，检查并删除过期备份文件：

#!/bin/bash
date=`date +%Y%m%d`
# 备份文件位置
backuppath=/root/backup
dbhost=数据库ip
dbuser=数据库用户
dbpasswd=数据库密码
db=数据库名
if [ ! -d $backuppath ] ;then
 mkdir /root/backup && echo "Directory creted !" 
else
 echo "Directory exits !"
fi
cd $backuppath
mkdir -p $date
cd $date
mysqldump  -h$dbhost -u$dbuser -p$dbpasswd $db > $date.sql
# -type d 表示查找目录，d表示目录
# -mtime +7 表示按照文件的更改时间来查找文件，+7表示距现在7天以前
# -exec rm -rf {} \; exec后面跟要执行的shell命令，然后是{}，一个空格，一个\，最后一个分号;
find $backuppath  -type d -mtime +7 -exec rm -rf {} \;

给脚本可执行权限：

chmod u+x mysqlbackup.sh

使用 crond 命令设置定时任务：

Crontab格式
   *分　*时　*日　*月　*周　执行的命令
    第 1 列分钟 1～59，每分钟用 *或者*/1表示，整点分钟数为00或0
    第 2 列小时 1～23（0 表示 0 点）
    第 3 列日 1～31
    第 4 列月 1～12
    第 5 列星期 0～6（0 表示星期天）
    第 6 列要运行的命令

查看crond状态：

systemctl status crond

添加定时任务：

crontab -e

0 21 * * * /root/mysqlbackup.sh

重启crond：

systemctl restart crond