杨旭光的网站

2018/04/03

mysqldump+binlog+备份还原


mysqldump

备份

备份 cabbage_server 数据库内容，保证备份数据一致性，备份完毕刷新binlog文件，在备份文件里记录好日志信息

mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 cabbage_server > cabbage.sql
  • --single-transaction 在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB。
  • --flush-logs 为结束当前日志，生成新日志文件。
  • --master-data=n 值为1，会记录 Position 点并执行 CHANGE MASTER TO MASTER_LOG_FILE='bin-log.xxxx', MASTER_LOG_POS=xxxx; 语句，为值 2 则记录但会注释 CHANGE MASTER 语句，不会被执行，通常我们都会设置为2，只有在做主从搭建，需要备份主库数据给从库时，才用到设置为1

备份所有数据库

mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases > all-databases.sql

还原

进入mysql终端进入指定数据库，删除数据表，然后运行

mysql>source /opt/cabbage.sql

binlog

开启binlog

开启binlog，在 [mysqld] 下配置以下参数

#binlog存放路径
log-bin=mysql-bin
#binlog格式
binlog_format=mixed
#服务器标识
server-id = 1
#binlog存放天数
expire_logs_days = 10
#磁盘同步
sync_binlog = 0

sync_binlog 这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。

  • sync_binlog=0 当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
  • sync_binlog=n 当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

几个常用查看binlog sql指令

查看指定binlog文件的内容
show binlog events in 'mysql-bin.000002';

查看当前使用的二进制文件及所处位置
show master status;

查看Binlog文件列表
show binary logs;

查看主服务器使用的二进制文件及大小
show master logs;

刷新binlog日志文件
flush logs

查看binlog的每条内容的事件信息
show binlog events in 'mysql-bin.000021';

Event_type 几个常见事件类型说明：

  • Format_desc 每个binlog开头的事件，用于信息描述
  • Rotate 记录最后一条事件，并执行延续下一条事件的binlog文件和位置
  • Query 执行语句
  • intvar 针对自增键类型auto_increment，进行自增id插入语句
  • Xid 事务提交语句

分析binlog里面的内容

默认binlog是二进制文件，无法直接查看分析，可以通过 mysqlbinlog 指令将binlog转为可视化sql语句进行分析

1.查看binlog

mysqlbinlog mysql-bin.000001

如果需要查看多个binlog文件，可以空格填入
mysqlbinlog mysql-bin.000001 mysql-bin.000002

# at 1771
#180326 18:15:50 server id 1  end_log_pos 1917 CRC32 0x38530371     Query   thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1522059350/*!*/;
alter table `log_request` add index `log_request_path_index`(`path`)
/*!*/;
  • at 1771 该事件记录从文件第 1771 个字节开始
  • 180326 18:15:50 事件发生的时间戳 2018-03-26 18:15:50
  • server id 1 服务器标识
  • end_log_pos 1917 结束点在第 1917 个字节
  • exec_time=0 花费的时间
  • error_code=0 错误码 0为正常

2.查看指定数据库binlog

mysqlbinlog -d cabbage_server mysql-bin.000001
mysqlbinlog -database cabbage_server mysql-bin.000001

3.将内容输出到文件中

mysqlbinlog -d cabbage_server mysql-bin.000001 > cabbage.sql

4.禁止恢复过程产生日志

在使用binlog日志文件进行数据库恢复时，该过程中也会产生日志文件，通过 -D 选项可以禁止此处操作会产生的binlog
mysqlbinlog -D mysql-bin.000001

也可以使用 --disable-log-bin 选项，效果相同。
mysqlbinlog --disable-log-bin mysql-bin.000001

5.从特定的位置编号开始读，截止到特定位置取数据

使用 -j 或 --start-position 选项 从位置编号为15028处开始读取数据。
mysqlbinlog -j 15028 mysql-bin.000001 > from-15028.out
mysqlbinlog --start-position 15028 mysql-bin.000001 > from-15028.out

使用 --stop-position 选项 可以截止到特定的编号位置
mysqlbinlog --stop-position 154 mysql-bin.000001 > upto-154.out

获取 4至154 ，包括4，不包括154 的内容
mysqlbinlog --start-position 4 --stop-position 154 mysql-bin.000001 > sql.out

6.从特定的时间开始读，截止到特定位时间数据

使用 --start-datetime 和 --stop-datetime ，读取 "2018-04-01 10:00:00" 至 "2018-04-05 10:00:00" 的数据
mysqlbinlog --start-datetime="2018-04-01 10:00:00" --stop-datetime="2018-04-05 10:00:00" mysql-bin.000001

7.读取远程服务器 binlog

mysqlbinlog -R -P 3306 -h 192.168.2.21 -p mysql-bin.000001
mysqlbinlog --read-from-remote-server --port=3306 --host=192.168.2.21 --password mysql-bin.000002

数据恢复

恢复步骤：

  • 1.导出需要还原的语句sql，例如导出"2018-04-01 10:00:00" 至 "2018-04-05 10:00:00" 的 cabbage_server库 数据 mysqlbinlog --start-datetime="2018-04-01 10:00:00" --stop-datetime="2018-04-05 10:00:00" -d cabbage_server mysql-bin.000001 mysql-bin.000002 > cabbage.sql
  • 2.进入mysql终端进入指定数据库运行 mysql>source /opt/cabbage.sql

根据全备和binlog增备进行准确时间点恢复

针对 cabbage_server 数据库还原来说明，假设我们服务器每天凌晨4点开始进行一次全备。现在需要还原到2018-04-02 10:00:00时的数据，恢复步骤：

  • 1.先将当期数据库 cabbage_server 进行备份。
    mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 cabbage_server > old.sql

  • 2.取出 2018-04-02 凌晨4点时的全备 sql语句文件，改名为 full.sql， 并查看此文件备份时记录的binlog日志信息，假如刷新到了 mysql-bin.000018 文件。

  • 3.采用 mysqlbinlog 根据时间段 2018-04-02 04:00:00 至 2018-04-02 10:00:00，读取binlog文件是从 mysql-bin.000018 到当前最新的文件，将符合条件的数据读取出来保存到 sub.sql 文件中。
    mysqlbinlog --start-datetime="2018-04-02 04:00:00" --stop-datetime="2018-04-02 10:00:00" -d cabbage_server mysql-bin.000018 mysql-bin.000019 ... > sub.sql

  • 4.删除数据库 cabbage_server 里所有数据表

  • 5.进入mysql终端进入指定数据库，依次执行 full.sql , sub.sql ，执行完毕即可恢复完成，查看数据还原情况
    mysql> source /opt/full.sql;
    mysql> source /opt/sub.sql;

参考

https://blog.csdn.net/u011341352/article/details/52835887

https://blog.csdn.net/wade5200/article/details/76576570

http://www.ttlsa.com/mysql/super-useful-mysqlbinlog-command/

yangxg.com | 粤ICP备15019321号 | yangxuguangvip[AT]163.com