mysql 增量备份角本 mysql的增量备份

转载

墨色天香 2023-08-01 18:36:36

文章标签 mysql 增量备份角本 linux mysql sql 增量备份 文章分类 MySQL 数据库

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=/data/3306/mysql-bin

　其中：log-bin若不显示指定存储目录，则默认存储在mysql的data目录下

binlog_format的几种格式：（STATEMENT，ROW和MIXED）

STATEMENT：基于SQL语句的复制(statement-based replication, SBR) 默认的格式
ROW：基于行的复制(row-based replication, RBR)
MIXED：混合模式复制(mixed-based replication, MBR)

启动后会产生mysql-bin.*这样的文件，每启动一次，就会增加一个或者多个。如果全备的时候加-F参数的话也会增加一个binlog二进制文件或者mysqladmin flush-log也会增加

[root@moremysql ~]# ll /data/3306/
-rw-rw---- 1 mysql mysql    264 Sep 16 15:31 mysql-bin.000001
-rw-rw---- 1 mysql mysql    616 Sep 17 11:38 mysql-bin.index
-rw-rw---- 1 mysql mysql      6 Sep 17 11:29 mysqld.pid
-rw-r----- 1 mysql root   23377 Sep 17 11:29 mysql_oldboy3306.err
srwxrwxrwx 1 mysql mysql      0 Sep 17 11:29 mysql.sock

　查看binlog开启情况和binlog的设置大小

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                       | 1048576   |
| binlog_direct_non_transactional_updates | OFF       |
| binlog_format                           | STATEMENT |
| binlog_stmt_cache_size                  | 32768     |
+-----------------------------------------+-----------+

2.2 查看binlog日志内容

[root@moremysql ~]# mysqlbinlog  /data/3306/mysql-bin.000009
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170917 11:19:55 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.32-log created 170917 11:19:55
BINLOG '
2+m9WQ8BAAAAZwAAAGsAAAAAAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#170917 11:21:29 server id 1  end_log_pos 150   Rotate to mysql-bin.000010  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

　　日志内容部分是不可查看的，这是因为binlog二进制日志格式默认是STATEMENT，如果是行级模式ROW就可以查看了，可以用--base64-output=decode-rows -v参数临时用ROW模式来查看

[root@moremysql ~]# mysqlbinlog --base64-output=decode-rows -v /data/3306/data/mysql-bin.000009
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File '/data/3306/data/mysql-bin.000009' not found (Errcode: 2)
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

mysqlbinlog命令参数：
　　1、作用：解析binlog日志为sql语句
　　2、-d 参数根据指定库拆分binlog(单表通过关键字过滤)
　　3、位置参数：--start-position=365 --stop-position=456,精确定位去内容
　　4、时间参数 --start-datetime='2017-10-16 17:16:15' --stop-datetime='2017-10-16 17:30:15' 模糊取内容，不太精确
　　5、-r 将语句导入到指定文件名中，相当于重定向>
　　6、解析ROW 级别binlog日志的方法： mysqlbinlog --base64-output=decode-rows -v mysql-bin.000004

　删除binlog日志文件的方式有：
　　mysql>reset master;清空bin-log
　　mysql>PURGE MASTERLOGS TO 'mysql-bim.000005' 删除mysql-bim.000005之前的bin-log mysql-bim.000005本身保留

3、mysql数据库备份与恢复

　3.1、备份数据库

　　全备之前的binlog文件，只有一个binlog文件mysql-bin.000001

[root@moremysql ~]# ll /data/3306/
total 72
drwxr-xr-x 5 mysql mysql  4096 Sep 17 11:29 data
-rw-r--r-- 1 mysql mysql  1982 Sep 17 11:29 my.cnf
-rw-r--r-- 1 root  root   1899 Sep 17 00:22 my.cnf.ori
-rwx------ 1 mysql mysql  1307 Sep 16 15:29 mysql
-rw-rw---- 1 mysql mysql   107 Sep 17 13:01 mysql-bin.000001
-rw-rw---- 1 mysql mysql    28 Sep 17 13:01 mysql-bin.index

　　全备

[root@moremysql ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B -x -F -R --master-data=2 class |gzip >class_$(date +%F).sql.gz

　　全备参数说明：

-B指定数据库

-F刷新日志

-R备份存储过程等

-x锁表

--master-data在备份语句里添加CHANGEMASTER语句以及binlog文件及位置点信息

　　全备后的binlog文件，增加了一个mysql-bin.000002，这个增加的binlog二进制文件就是全备的时候-F参数的作用。相当于mysqladmin的参数flush-log或mysql> flush logs;

[root@moremysql ~]# ll /data/3306/
total 76
drwxr-xr-x 5 mysql mysql  4096 Sep 17 11:29 data
-rw-r--r-- 1 mysql mysql  1982 Sep 17 11:29 my.cnf
-rw-r--r-- 1 root  root   1899 Sep 17 00:22 my.cnf.ori
-rwx------ 1 mysql mysql  1307 Sep 16 15:29 mysql
-rw-rw---- 1 mysql mysql   150 Sep 17 13:04 mysql-bin.000001
-rw-rw---- 1 mysql mysql   107 Sep 17 13:04 mysql-bin.000002

3.2、准备第一份增量数据，向库class的userinfo表中删除、插入、更新数据

mysql> delete from userinfo  where name='hello';
mysql> delete from userinfo  where name='world';
mysql> insert into userinfo  values(12,'test');
mysql> update  userinfo set name='tiantian' where id=6;

　　这时候对数据库文件的更改都会记录到mysql-bin.000002中，这是全备后的增量文件，在增量恢复的时候用得到。

　3.3、数据恢复

　　在数据恢复之前，停止数据库对外服务。如果是在从库上恢复(这个是事先做好的备份主库，开启了binlog)，还要关闭slave（因为开启了slave的话，会从主库复制，那么数据恢复就没效果了）

　　同时应将增量备份文件复制一份到其他目录或关闭sql_log_bin=off，因为恢复过程中，会继续写入语句到binlog，最终导致增量恢复数据部分变得比较混乱。

　　查看sql_log_bin状态

mysql> show variables like 'sql_log%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
| sql_log_off   | OFF   |
+---------------+-------+

　　临时关闭sql_log_bin，数据恢复的时候就不会将恢复的数据写入到binlog中，这样binlog文件就会保持很清洁。待完全恢复完成后，再开启sql_log_bin。

mysql> set sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_log%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
| sql_log_off   | OFF   |
+---------------+-------+

　　如果是从库(备主库)，要关掉slave

mysql> stop slave;

mysql> show slave status\G
             Slave_IO_Running: No
            Slave_SQL_Running: No
        Seconds_Behind_Master: NULL

　　现在开始还原全量数据：

[root@moremysql ~]# mysql -uroot -p123456  -S /data/3306/mysql.sock <class_2017-09-17.sql

　　全量恢复好后检查是否恢复了全量备份时的数据：

[root@moremysql ~]# mysql -uroot -p123456  -S /data/3306/mysql.sock -e "use class;select * from userinfo;"
+----+----------+
| id | name     |
+----+----------+
|  1 | goser    |
|  3 | hello    |
|  5 | world    |
|  6 | zhangsan |
|  8 | lisi     |
| 10 | wangwu   |
+----+----------+

　　全量恢复没问题后再进行增量恢复，恢复被删除的数据，更改的保留

[root@moremysql ~]# cp /data/3306/mysql-bin.000002  /opt/

　　将mysqlbinlog -r 生成的文件中的delete语句删掉即可

[root@moremysql ~]# mysqlbinlog /opt/mysql-bin.000002  -r userinfo.sql

[root@moremysql ~]# vim  userinfo.sql 
delete from userinfo  where name='hello' ---删掉这行
delete from userinfo  where name='world'---删掉这行

　　将修改好的binlog增量文件导入到数据库class中

[root@moremysql ~]# mysql -uroot -p123456  -S /data/3306/mysql.sock class < userinfo.sql

　　最后别忘了开启sql_log_bin

mysql> set sql_log_bin=on;

　　到此数据恢复全部完成。。。。。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：mysql long最大值 mysql计算最大值的函数

下一篇：sql server 2008 数据库共享 sql2008怎么共享数据库

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯