MySQL数据库备份实例详解(6)

查看二进制日志，进行分析

[root@localhost db_backup]# cd /home/mysql [root@localhost mysql]# mysqlbinlog --base64-output=decode-rows -vv --start-position=154 --database=mc_orderdb mysql-bin.000001| grep -B3 DELETE | more #190110 13:44:54 server id 1 end_log_pos 83285 CRC32 0xf679d195 Table_map: `mc_orderdb`.`t` mapped to number 119 # at 83285 #190110 13:44:54 server id 1 end_log_pos 84620 CRC32 0xa3408e6c Delete_rows: table id 119 flags: STMT_END_F ### DELETE FROM `mc_orderdb`.`t` -- ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=1 /* INT meta=0 nullable=1 is_null=0 */ ### @3=1042.34 /* DECIMAL(8,2) meta=2050 nullable=1 is_null=0 */ ### DELETE FROM `mc_orderdb`.`t` -- ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2=3 /* INT meta=0 nullable=1 is_null=0 */ ### @3=803.37 /* DECIMAL(8,2) meta=2050 nullable=1 is_null=0 */ ...省略

从中可以看到刚刚的二进制日志中的第一个DELETE，在它之前的日志结束点为84620

所以我们需要恢复的是154 到84620 之间，且数据库为mc_orderdb ，日志所在文件名 为mysql-bin.000001的数据

我们通过mysqlbinlog 将这些数据导出来

[root@localhost mysql]# mysqlbinlog --start-position=154 --stop-position=84620 --database=mc_orderdb mysql-bin.000001 > mc_order_diff.sql

将数据导入恢复

[root@localhost mysql]# mysql -uroot -p mc_orderdb < mc_order_diff.sql 实时二进制日志备份

Mysql5.6版本之后，可以实时备份Binlog(二进制日志)

要使用这个功能，我们需要进行以下配置
首先新建一个用户，这个用户要有replication slave 权限

mysql> grant replication slave on *.* to 'repl'@'localhost' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec)

建立一个存储备份的二进制日志文件的目录

mkdir -p binlog_backup

最后在这个目录下，执行以下命令，就可以实时的进行二进制日志的备份了

[root@localhost binlog_bak]# mysqlbinlog --raw --read-from-remote-server --stop-never --host localhost --port 3306 -u repl -p123456 mysql-bin.000001 mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.

raw：代表这个命令是输出的是raw格式的二进制日志
read-from-remote-server： 从mysql服务器上读取这个日志
stop-never：备份的这个进程会持续在后台运行
最后的是指定要备份的二进制文件的名称

此命令执行后这个终端会一直执行这个命令
现在打开另外一个终端，进入备份的目录进行查看

[root@localhost mysql]# cd /home/binlog_bak/ [root@localhost binlog_bak]# ls mysql-bin.000001

可以看到日志已经备份成功
进入mysql命令行，刷新日志

mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 3560467 | | mysql-bin.000002 | 154 | +------------------+-----------+ 2 rows in set (0.00 sec)

现在有两个日志，我们再回到备份目录进行查看

[root@localhost binlog_bak]# ls mysql-bin.000001 mysql-bin.000002

可以看到最新的日志也已实时备份

三. xtrabackup 1. xtrabackup介绍

xtrabackup 物理备份工具，用于在线备份innodb存储引擎的表

在所有表全是innodb存储引擎表的情况下：
xtrabackup 可以保证在备份过程中，不影响表的读写操作
在最初的时候，xtrabackup 本身只支持对innodb存储引擎表的备份，且只会备份数据文件，不会备份表的结构
innobackupex 是对xtrabackup 的插件，提供了备份表结构及其他配置信息的功能，并支持MyISAM表的备份，但也会锁表

因为在当前的mysql版本下，还有一些系统表使用的是MyISAM存储引擎，所以一般情况下使用的是innobackupex 脚本进行备份的

2. 安装xtrabackup 3. 利用innobackupex 进行全备 innobackupex --user=backup --password=123456 --parallel=2 /home/db_backup/ [root@localhost home]# innobackupex --user=backup --password=123456 --parallel=2 /home/db_backup/ xtrabackup: recognized server arguments: --datadir=/home/mysql --log_bin=mysql-bin --server-id=1 --parallel=2 xtrabackup: recognized client arguments: --datadir=home/mysql --log_bin=mysql-bin --server-id=1 --parallel=2 190110 15:15:30 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". ... 省略 190110 15:15:37 [00] Writing /home/db_backup/2019-01-10_15-15-30/xtrabackup_info 190110 15:15:37 [00] ...done xtrabackup: Transaction log of lsn (79088947) to (79088956) was copied. 190110 15:15:37 completed OK!

当看到completed OK!代表备份已完成

和mysqldump单线程备份不同，我们可以通过parallel参数指定备份的线程数
/home/db_backup/ 是我们指定的备份文件的存储目录

xtrabackup 会已当前时间在目标目录中生成一个子目录用来存放当前的备份文件