MySQL数据库备份还原（基于binlog的增量备份）
来源：互联网 发布：圣塔克拉拉大学 知乎 编辑：程序博客网 时间：2024/05/14 18:06
1 简介
1.1 增量备份简介
增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。这种备份方式最显著的优点就是：没有重复的备份数据，因此备份的数据量不大，备份所需的时间很短。但增量备份的数据恢复是比较麻烦的。您必须具有上一次全备份和所有增量备份磁带（一旦丢失或损坏其中的一个增量，就会造成恢复的失败），并且它们必须沿着从全备份到依次增量备份的时间顺序逐个反推恢复，因此这就极大地延长了恢复时间。
假如我们有一个数据库，有20G的数据，每天会增加10M的数据，数据库每天都要全量备份一次，这样的话服务器的压力比较大，因此我们只需要备份增加的这部分数据，这样减少服务器的负担。
1.2 binlog简介
binlog日志由配置文件的log-bin选项负责启用，MySQL服务器将在数据根目录创建两个新文 件XXX-bin.001和xxx-bin.index，若配置选项没有给出文件名，Mysql将使用主机名称命名这两个文件，其中.index文件包含一份全体日志文件的清单。 Mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件，而不会记录 SELECT和没有实际更新的UPDATE语句。
当MySQL数据库停止或重启时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的binlog日志文件，文件序号递增，此外，如果日志文件超过max_binlog_size系统变量配置的上限时，也会生成新的日志文件。
2 binlog日志操作
2.1 开启binlog日志
在my.cnf或my.ini中增加
- [mysqld]
- log-bin=mysql-bin
- binlog_format=row
- server-id=1（集群环境下各mysql服务器要配置不同的值）
binlog_format的几种格式：（STATEMENT，ROW和MIXED）
STATEMENT：基于SQL语句的复制(statement-based replication, SBR)
ROW：基于行的复制(row-based replication, RBR)
MIXED：混合模式复制(mixed-based replication, MBR)
启动后会产生mysql-bin.*这样的文件，每启动一次，就会增加一个或者多个。
- [root@localhost data]# ls -l| grep mysql-bin
- -rw-rw
- -rw-rw
- [root@localhost data]#
- mysql> show variables like 'log_bin%';
- +
- | Variable_name | Value |
- +
- | log_bin | ON |
- | log_bin_trust_function_creators | OFF |
- +
- mysql> show variables like 'binlog%';
- +
- | Variable_name | Value |
- +
- | binlog_cache_size | 32768 |
- | binlog_direct_non_transactional_updates | OFF |
- | binlog_format | ROW |
- | binlog_stmt_cache_size | 32768 |
- +
2.2 查看binlog日志内容
- [root@localhost data]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 ;
- mysqlbinlog: unknown variable 'default-character-set=utf8'
方法一：使用--no-defaults选项
- [root@localhost data]# mysqlbinlog
输出格式如下：
- # at 188
- #140705 11:23:55 server id 1 end_log_pos 271 Query thread_id=1 exec_time=0 error_code=0
- SET TIMESTAMP=1404573835/*!*/;
- create database test
- /*!*/;
- DELIMITER ;
- # End of log file
- ROLLBACK /* added by mysqlbinlog */;
- /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
Position:位于文件中的位置，即第一行的（#at 4）和第二行的（log_pos 4），说明该事件记录从文件第4字节开始。
Timestamp:事件发生的时间戳，即第二行的（#070813 14:16:36）
Exec_time:事件的执行花费时间
Error_code:错误码
Type 事件类型：
Master ID:创建二进制事件的主机服务器ID
Master Pos:事件在原始二进制文件中的位置
Flags:标志信息
2.3 一些常用操作
- mysql> show master logs; #查看数据库所有日志文件。
- mysql> show binlog events \g; #查看当前使用的binlog文件信息。
- mysql> show binlog events in 'mysql-bin.000016'; #查看指定的binlog文件信息。
- mysql> flush logs; #将内存中log日志写磁盘，保存在当前binlog文件中，并产生一个新的binlog日志文件。
- mysql> flush logs; reset master; #删除所有二进制日志，并重新（mysql-bin.000001）开始记录。
3 mysql备份实例分析（全备+基于binlog的增备）
实例采用小数据量进行模拟，包含一份全备及两份增备，主要演示下备份还原过程，工程中可根据数据实际情况进行备份还原策略调整。
3.1 查看当前数据库binlog文件
通过mysql客户端查看
- mysql> show master logs;
- +
- | Log_name | File_size |
- +
- | mysql-bin.000001 | 107 |
- +
- [root@localhost data]# ll -h
- total 5.1G
- -rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ibdata1
- -rw-rw---- 1 mysql mysql 2.0G Jul 18 14:12 ibdata2
- -rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ib_logfile0
- -rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ib_logfile1
- drwxr-xr-x 2 mysql mysql 4.0K Jul 18 13:52 mysql
- -rw-rw---- 1 mysql mysql 107 Jul 23 13:29 mysql-bin.000001
- -rw-rw---- 1 mysql mysql 19 Jul 23 13:29 mysql-bin.index
- srwxrwxrwx 1 mysql mysql 0 Jul 18 14:14 mysql.sock
- drwx------ 2 mysql mysql 4.0K Jul 18 14:01 performance_schema
- -rw-rw---- 1 mysql mysql 483 Jul 23 13:29 R820-08.err
- -rw-rw---- 1 mysql mysql 5 Jul 18 14:14 R820-08.pid
- drwx------ 2 mysql mysql 19 Jul 22 23:15 test
3.2 准备全量数据
- mysql> create database backup_full;
- mysql> create table full (c1 int(10), c2 varchar(20)) engine=innodb;
- mysql> insert into full values (1, 'full1'),(2, 'full2'),(3, 'full3'),(4, 'full4'),
- (5, 'full5'),(6, 'full6'),(7, 'full7'),(8, 'full8'),(9, 'full9'),(10, 'full10');
步骤如下：
a.备份前需要将数据库加读锁，防止数据在备份时写入。
- mysql> flush tables with read lock;
b.通过命令flush logs;将log日志刷盘，写入当前binlog（mysql-bin.000001），在生成一个新的binlog（mysql-bin.000002）为增备做准备。
c.进行数据备份。在linux命令行下执行：- mysqldump -u用户名 -p密码 -hIP地址 -P端口 数据库名 > /tmp/backup_full.sql
- mysqldump -uroot -pjesse -h127.0.0.1 -P3355 buckup_full > /tmp/backup_full.sql
d.解除表锁。
至此全量备份全部结束，将全量数据文件buckup_full.sql保存即可。数据库再有新的数据更新会记录在新的binlog（mysql-bin.000002）里面。 3.4 准备第一份增量数据- mysql> create database backup_increment;
- mysql> use backup_increment;
- mysql> create table increment (c1 int(10), c2 varchar(20)) engine=innodb;
- mysql> insert into increment values (11, 'increment1'),(12, 'increment2'),(13, 'increment3'),(14, 'increment4'),(15, 'increment5');
步骤如下：
a.备份前需要将数据库加读锁，防止数据在备份时写入。
- mysql> flush tables with read lock;
也可以将二进制文件导出到文本文件保存，在Linux命令行下执行
- mysqlbinlog mysql-bin.000002 > /tmp/increment1.txt
- mysql> use backup_increment;
- mysql> insert into increment values (16, 'increment16'),(17, 'increment17'),(18, 'increment18'),(19, 'increment19'),(20, 'increment20');
3.7 将第二份增量数据进行备份
步骤如下：
a.备份前需要将数据库加读锁，防止数据在备份时写入。
- mysql> flush tables with read lock;
也可以将二进制文件导出到文本文件保存，在linux命令行下执行
- mysqlbinlog mysql-bin.000003 > /tmp/increment2.txt
4 mysql还原实例分析（全备还原+基于binlog的增备还原）
模拟数据库故障，即删除全备数据及增备数据库。- mysql> drop table backup_full.full;
- mysql> drop database backup_increment;
- mysql> show databases;
- +
- | Database |
- +
- | information_schema |
- | backup_full |
- | mysql |
- | performance_schema |
- | test |
- +
- 5 rows in set (0.00 sec)
-
- mysql> select * from backup_full.full;
- ERROR 1146 (42S02): Table 'backup_full.full' doesn't exist
- mysql> select * from backup_increment.increment;
- ERROR 1146 (42S02): Table 'backup_increment.increment' doesn't exist
4.1 还原全备数据
方法1：进入数据库，通过source
- mysql> use backup_full;
- mysql> source /tmp/backup_full.sql;
- mysql> select * from backup_full.full;
- +
- | c1 | c2 |
- +
- | 1 | full1 |
- | 2 | full2 |
- | 3 | full3 |
- | 4 | full4 |
- | 5 | full5 |
- | 6 | full6 |
- | 7 | full7 |
- | 8 | full8 |
- | 9 | full9 |
- | 10 | full10 |
- +
方法2：直接还原数据文件。
- mysql -u用户名 -p密码 -hIP地址 -P端口 数据库名 < /tmp/buckup_full.sql
- mysql -uroot -pjesse -h127.0.0.1 -P3355 backup_full < /tmp/buckup_full.sql
- mysql> select * from backup_full.full;
- +
- | c1 | c2 |
- +
- | 1 | full1 |
- | 2 | full2 |
- | 3 | full3 |
- | 4 | full4 |
- | 5 | full5 |
- | 6 | full6 |
- | 7 | full7 |
- | 8 | full8 |
- | 9 | full9 |
- | 10 | full10 |
- +
方法一：通过文本文件还原
- mysql> source /tmp/increment1.txt;
- mysql> select * from backup_increment.increment;
- +
- | c1 | c2 |
- +
- | 11 | increment1 |
- | 12 | increment2 |
- | 13 | increment3 |
- | 14 | increment4 |
- | 15 | increment5 |
- +
方法二：通过binlog直接还原
在linux命令行下执行：
- mysqlbinlog binlog文件名 | mysql -u用户名 -p密码 -hIP地址 -P端口
- mysqlbinlog mysql-bin.000002 | mysql -uroot -pjesse -h127.0.0.1 -P3355
- mysql> select * from backup_increment.increment;
- +
- | c1 | c2 |
- +
- | 11 | increment1 |
- | 12 | increment2 |
- | 13 | increment3 |
- | 14 | increment4 |
- | 15 | increment5 |
- +
4.3 还原第二个增备文件（方法同上）
在linux命令行下执行：
- mysqlbinlog binlog文件名 | mysql -u用户名 -p密码 -hIP地址 -P端口
- mysqlbinlog mysql-bin.000003 | mysql -uroot -pjesse -h127.0.0.1 -P3355
- mysql> select * from backup_increment.increment;
- +
- | c1 | c2 |
- +
- | 11 | increment1 |
- | 12 | increment2 |
- | 13 | increment3 |
- | 14 | increment4 |
- | 15 | increment5 |
- | 16 | increment16 |
- | 17 | increment17 |
- | 18 | increment18 |
- | 19 | increment19 |
- | 20 | increment20 |
- +
0 0
