|

今日天气

  • °C

  • 湖北省 武汉市 汉阳区

分类统计

博文归档

热门推荐

热门标签

python linux php centos mysql k8s ubuntu flask nginx gpu windows pod docker nvidia thinkphp bash apache javascript js zabbix redis iis containerd elk layui mongodb tomcat etcd

mysql利用binlog增量备份和还原实例

一，什么是增量备份

增量备份，就是将新增加的数据进行备份。假如你一个数据库，有10G的数据，每天会增加10M的数据，数据库每天都要备份一次，这么多数据是不是都要备份呢？还是只要备份增加的数据呢，很显然，我只要备份增加的数据。这样减少服务器的负担。

二，启用binlog

vi my.cnf
log-bin=/var/lib/mysql/mysql-bin.log，如果是这样的话log-bin=mysql-bin.log默认在datadir目录下面
[root@BlackGhost mysql]# ls |grep mysql-bin
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.000006
mysql-bin.index

启动后会产生mysql-bin这样的文件，每启动一次，就会增加一个或者多个。

mysql-bin.000002这样文件存放的是数据库每天增加的数据，所有数据库的数据增量都在这里面。

三，查看mysql-bin.000002这样的文件里面到底是什么东西

[root@BlackGhost mysql]# mysqlbinlog   /var/lib/mysql/mysql-bin.000002 > /tmp/add.sql
[root@BlackGhost mysql]# cat /tmp/add.sql   // 下面是根据mysql-bin生成的文件（部分内容）  
/*!40019 SET @@session.max_insert_delayed_threads=0*/;  
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;  
DELIMITER /*!*/;  
# at 4  
#100929 21:23:52 server id 1  end_log_pos 106     Start: binlog v 4, server v 5.1.50-log created 100929 21:23:52 at startup  
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.  
ROLLBACK/*!*/;  
BINLOG ' 
6D2jTA8BAAAAZgAAAGoAAAABAAQANS4xLjUwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
AAAAAAAAAAAAAAAAAADoPaNMEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC 
'/*!*/;  
# at 106  
#100929 21:29:35 server id 1  end_log_pos 134     Intvar  
SET INSERT_ID=16/*!*/;  
# at 134  
#100929 21:29:35 server id 1  end_log_pos 237     Query    thread_id=1    exec_time=0    error_code=0  
use test/*!*/;           //这里是test数据库  
SET TIMESTAMP=1285766975/*!*/;  
SET @@session.pseudo_thread_id=1/*!*/;  
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;  
SET @@session.sql_mode=0/*!*/;  
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;  
/*!\C utf8 *//*!*/;  
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;  
SET @@session.lc_time_names=0/*!*/;  
SET @@session.collation_database=DEFAULT/*!*/;  
insert into aa(name)values('cccccccccc')  
/*!*/;  
# at 237  
#100929 21:32:21 server id 1  end_log_pos 265     Intvar  
SET INSERT_ID=12/*!*/;  
# at 265  
#100929 21:32:21 server id 1  end_log_pos 370     Query    thread_id=1    exec_time=0    error_code=0  
SET TIMESTAMP=1285767141/*!*/;  
insert into user(name)values('cccccccccc')  
/*!*/;  
# at 370  
#100929 21:35:25 server id 1  end_log_pos 440     Query    thread_id=1    exec_time=0    error_code=0  
SET TIMESTAMP=1285767325/*!*/;  
BEGIN  
/*!*/;  
# at 440  
#100929 21:35:25 server id 1  end_log_pos 468     Intvar  
SET INSERT_ID=45/*!*/;  
# at 468  
#100929 21:35:25 server id 1  end_log_pos 573     Query    thread_id=1    exec_time=0    error_code=0  
use blog/*!*/;             //这里是blog数据库  
SET TIMESTAMP=1285767325/*!*/;  
insert into city(CityName)values('asdf')  
/*!*/;  
# at 573  
#100929 21:35:25 server id 1  end_log_pos 600     Xid = 205  
COMMIT/*!*/;  
DELIMITER ;  
# End of log file  
ROLLBACK /* added by mysqlbinlog */;  
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

下面还有一个重要索引文件就是mysql-bin.index

[root@BlackGhost mysql]# cat mysql-bin.index  
./mysql-bin.000001  
./mysql-bin.000002  
./mysql-bin.000003  
./mysql-bin.000004  
./mysql-bin.000005  
./mysql-bin.000006

四，增量备份和增量还原

1，增量备份

既然我们知道了，mysql里面新增加的数据在mysql-bin这样的文件里面，我们只要把mysql-bin这样的文件进行备份就可以了。

cp /var/lib/mysql/mysql-bin* /data/mysql_newbak/

2，增量还原，讲几个常用的，比较有用的

a），根据时间来还原 --start-date,--stop-date

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog --start-date="2010-09-29 18:00:00" --stop-date="2010-09-29 23:00:00" /var/lib/mysql/mysql-bin.000002 |mysql -u root -p

根据条件看一下数据

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog  --start-date="2010-09-29 18:00:00"  
--stop-date="2010-09-29 23:00:00"  /var/lib/mysql/mysql-bin.000002  
//下面是部分内容，其实也就是一些对数据进行操作的sql语句  
# at 237  
#100929 21:32:21 server id 1  end_log_pos 265     Intvar  
SET INSERT_ID=12/*!*/;  
# at 265  
#100929 21:32:21 server id 1  end_log_pos 370     Query    thread_id=1    exec_time=0    error_code=0  
SET TIMESTAMP=1285767141/*!*/;  
insert into user(name)values('cccccccccc')  
/*!*/;  
# at 370  
#100929 21:35:25 server id 1  end_log_pos 440     Query    thread_id=1    exec_time=0    error_code=0  
SET TIMESTAMP=1285767325/*!*/;  
BEGIN  
/*!*/;  
# at 440  
#100929 21:35:25 server id 1  end_log_pos 468     Intvar  
SET INSERT_ID=45/*!*/;  
# at 468  
#100929 21:35:25 server id 1  end_log_pos 573     Query    thread_id=1    exec_time=0    error_code=0

b），根据起始位置来还原，--start-position,--stop-position

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog --start-position=370 --stop-position=440  /var/lib/mysql/mysql-bin.000002 |mysql -u root -p
//查看插入的内容，根a）中是一样的
[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog --start-position=370 --stop-position=440  /var/lib/mysql/mysql-bin.000002
--start-position=370 --stop-position=440 这里面数字从哪儿来的呢？
# at 370
#100929 21:35:25 server id 1  end_log_pos 440 Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1285767325/*!*/;

上面的红色加粗的就是，一个是start-position，一个是stop-position

c），根据数据库名来进行还原 -d

在这里是小写的d，请不要把它和mysqldump中的－D搞混了。

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog -d test  /var/lib/mysql/mysql-bin.000002

查看内容，请参考a）

d），根据数据库所在IP来分－h

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog -h 192.1681.102  /var/lib/mysql/mysql-bin.000002

查看内容，请参考a）

e），根据数据库所占用的端口来分－P

有的时候，我们的mysql用的不一定是3306端口，注意是大写的P

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog -p 13306  /var/lib/mysql/mysql-bin.000002

查看内容，请参考a）

f），根据数据库serverid来还原--server-id

在数据库的配置文件中，都有一个serverid并且同一集群中serverid是不能相同的。

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog --server-id=1  /var/lib/mysql/mysql-bin.000002

查看内容，请参考a）

注意：上面的几个例子，我都是一个一个说的，其实可以排列组合的。例如

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog --start-position="2010-09-29 18:00:00" -d test -h 127.0.0.1 /var/lib/mysql/mysql-bin.000002 ｜mysql -u root -p

五，后续

增量备份的时候，有一点让人不爽，就是mysql-bin这样的文件，每启动一次mysql就会增加一些，如果你不去管他的话，时间长了，他会把你的磁盘占满。

./mysqldump --flush-logs -u root  myblog > /tmp/myblog.sql

备份myblog数据库，清除增量备份里面的有关myblog的数据

./mysqldump --flush-logs -u root  --all-databases > /tmp/alldatabase.sql

备份所有数据库，清除增量备份

mysql-bin.index的起索引作用，因为增量的数据不一定在一个mysql-bin000这样的文件里面，这个时候，我们就要根据mysql-bin.index来找mysql-bin这样的增量文件了。

如果mysql里面做了这样的配置binlog-do-db=test1，增量备份里面只会有test1这个数据库的数据


centos6静默安装oracle11g教程
Centos7部署DR模式LVS负载均衡

内容版权声明：除非注明，否则皆为本站原创文章。

转载注明出处：https://sulao.cn/post/504

评论列表

相关阅读

常用网站

0%
构建本站 Python Gunicorn Flask LayUI Peewee MySQL Redis Celery
Copyright © 2014-2025 Shevechco 27ms