数据增量备份

最新推荐文章于 2022-07-30 16:42:13 发布

diebaoyan1530

最新推荐文章于 2022-07-30 16:42:13 发布

阅读量197

点赞数

文章标签：开发工具数据库 shell

原文链接：http://www.cnblogs.com/summer2/p/10788091.html

版权

数据增量备份：

方法1 启用binlog日志实现数据实时增量备份与数据恢复

binlog日志:
类型: 二进制日志
作用: 记录所有更改数据的操作
配置: log_bin[=dir/name] log_bin代表启用binlog日志
server_id=数字指定id值
max_binlog_size=数字m

启动日志文件
# vim /etc/my.cnf
[mysqld]
#log_bin 在这里要注释掉之前写的这行，
#log_bin=plj 注释掉，指定默认文件名称，但是不改变存储位置
log_bin=/logdir/plj 只加这1行，启用日志的意思，指定默认文件存储位置和名称
server_id=52 一般写IP地址中主机号的数字，避免冲突
binlog_format="mixed"
... ...

# mkdir /logdir 创建新存储目录
# chown mysql:msyql /logdir/ 让mysql对此目录有权限

# systemctl restart mysqld
-------------------------------------------------------------------------------------------------------------------------
查看日志文件内容

# ls /logdir/plj.*
/logdir/plj.000001 /logdir/plj.index

# cat /logdir/plj.index
/logdir/plj.000001

# mysqlbinlog /logdir/plj.000001 日志文件不是文本文件，它是二进制文件，必须用mysqlbinlog这个命令来查看

初始文件内容如下：
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#181121 11:26:35 server id 52 end_log_pos 123 CRC32 0x46b41b3c Start: binlog v 4, server v 5.7.17-log created 181121 11:26:35 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
a9D0Ww80AAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABr0PRbEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATwbtEY=
'/*!*/;
# at 123
#181121 11:26:35 server id 52 end_log_pos 154 CRC32 0x9faabf47 Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
----------------------------------------------------------------------------------------------------------------------------------------------------
使用日志文件恢复数据

记录日志的格式有3种:
staterment 只记录命令，不记录结果。
row 只记录命令的执行结果，不记录这条sql命令。如果我们不写，系统默认记录的格式就是row
mixed 2者都记录！记录命令和命令的执行结果

方法2

mysql> update db4.t2 set name="yaya";

# systemctl stop mysqld
# vim /etc/my.cnf
[mysqld]
log_bin 启用日志的意思。下划线_或者中杠-都行，系统都能识别。log_bin或者log-bin都行。
server_id=52 1-255之间的任何一个数，一般用主机位来做id值，就不怕重复。在mysql5.7这个版本，开启日志必须也要指定这个id值
binlog_format="mixed" 指定记录的格式是mixed，命令和执行命令的结果全都要记录
...

# systemctl start mysqld
--------------------------------------------------------------------------------------
binlog日志默认用主机名-bin来命名

binlog相关文件:
主机名-bin.index 以.index结尾的是日志的索引文件。记录当前已有的binlog日志。它是文本文件，可查看
主机名-bin.000001 第1个二进制文件。如果不指定，默认文件大于500M后才会生成新的日志文件
主机名-bin.000002 第2个二进制文件

重启数据库服务，它会生成新日志文件
mysql> flush logs; 刷新日志文件，也会生成新的日志文件

# hostname 查看当前主机名
mysql52
# ls /var/lib/mysql/mysql52-bin.*
mysql52-bin.000001 mysql52-bin.index
# cat mysql52-bin.index 查看已有的binlog日志
./mysql52-bin.000001 .代表当前目录，mysql52-bin.000001是当前已有的日志文件。有几个，它就记录几个。

mysqlbinlog /var/lib/mysql/mysql52-bin.000001 只能用系统专用的命令mysqlbinlog来查看这个日志文件。
因为日志文件mysql52-bin.000001不是文本文件，如果用cat或者vim查看，会发现是乱码的。
------------------------------------------------------------------------------------------------------------
生成新的日志文件(4种方法)！！！

mysql> flush logs; 执行SQL操作。刷新日志，记住有s！！！是logs！！！
# systemctl restart mysqld 重启mysql服务
# mysql -uroot -p123456 -e "flush logs" 在命令行执行mysql里的命令
# mysqldump -uroot -p123456 --flush-logs db4 > /root/db4.sql 在备份数据库时加上--flush-logs，指定生成新的日志文件

mysql> insert into db4.t2 values("bob",19,"aaa");
mysql> update db4.t2 set name="zhangsan" where uid=1;
mysql> delete from db4.te where uid=2;

# ls /logdir
plj.000001 plj.index 原本日志文件编号是1

# systemctl restart mysqld 重启数据库服务，它会生成新日志文件
# systemctl restart mysqld
# systemctl restart mysqld
# systemctl restart mysqld
# systemctl restart mysqld
# systemctl restart mysqld 一共重启了6次

# ls
plj.000001 plj.000002 plj.000003 plj.000004
plj.000005 plj.000006 plj.000007 plj.index 原本日志文件编号是1，重启了6次，所以现在最新的日志文件是7
如果不指定，系统会默认500M后，才生成一个新的日志。
当有多个日志文件，不足500M时，以名字中数字最大的文件为准。
在这里，编号为7的最大，当前正在使用的日志是7。
此刻，没删除之前，编号为6的日志文件还在！

可以在配置文件里设置日志文件的是多少m
vim /etc/my.cnf
max_binlog_size=数字m
如：max_binlog_size = 1G #binlog大小

扩展：
mysqld在每个二进制日志名后面添加一个数字扩展名。每次你启动服务器或刷新日志时该数字则增加。如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。如果你正使用大的事务，二进制日志还会超过max_binlog_size：事务全写入一个二进制日志中，绝对不要写入不同的二进制日志中。

删除日志文件，要用sql命令来删除。不要用系统命令来删文件，因为它不会同步到索引文件里。
# rm -rf plj.000006 删除编号为6的日志文件
# ls 可看到编号为6的日志文件已经被删除了，找不到了！
plj.000001 plj.000002 plj.000003 plj.000004
plj.000005 plj.000007 plj.index

# cat plj.index
/logdir/plj.000001
/logdir/plj.000002
/logdir/plj.000003
/logdir/plj.000004
/logdir/plj.000005
/logdir/plj.000006 这里还能看到编号为6的日志文件，可以看到它们是不同步的！！！所以不要用系统命令删日志文件！！！
/logdir/plj.000007
###################################################################
清理binlog日志

删除早于指定版本的binlog日志
mysql> purge master logs to "binlog文件名";

删除所有binlog日志，重建新日志
reset master;

mysql> purge master logs to "plj.000004"; 删除指定编号文件之前的文件，在这里就是删除比4小的所有日志文件(即删除1-3)
mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| plj.000007 | 24308 | | | | 当前使用的日志文件是编号是7
+------------+----------+--------------+------------------+-------------------+

mysql > quit
# ls
plj.000004 plj.000005 plj.000007 plj.000008 plj.index 可以看到1-3的日志文件都被删除了

# cat plj.index
./logdir/plj.000004
./logdir/plj.000005
./logdir/plj.000006 这里还能看到编号为6的日志文件，可以看到它们是不同步的！！！所以不要用系统命令删日志文件！！！
./logdir/plj.000007

# ls
plj.000004 plj.000005 plj.000007 plj.000008 plj.index

mysql > quit
# ls
plj.000001 plj.index
###################################################################################
position位置
文件初始内容，不管你指不指定范围，它都会默认显示出来。
一般建议用偏移量来写，比写日期容易！

多条命令记录在这个日志文件里，mysql是如何区分这么多条命令的呢？
如果它记录了一条，然后又记录了另一条的话。
通过记录执行这个命令的长度/时间来分区

51上操作
# mysqldump -uroot -p123456 db4 t2 > /root/t2.sql 完全备份库db4中的t2表
# mysql -uroot -p123456
mysql> select * from db4.t2; 当前db4.t2里的内容只有3行
+--------+------+---------------+
| name | uid | shell |
+--------+------+---------------+
| root | 1 | /bin/bash |
| bin | 2 | /sbin/nologin |
| daemon | 3 | /sbin/nologin |
+--------+------+---------------+

mysql> insert into db4.t2 新增加记录！bob,979
-> values("bob",979,"/bin/bash");
mysql> insert into db4.t2 values("tom",989,"/bin/bash"); 增加tom,989,
mysql> insert into db4.t2 values("harry",999,"/bin/bash"); 增加harry,999

mysql> select * from db4.t2;
+--------+------+---------------+
| name | uid | shell |
+--------+------+---------------+
| root | 1 | /bin/bash |
| bin | 2 | /sbin/nologin |
| daemon | 3 | /sbin/nologin |
| bob | 979 | /bin/bash | 增加3条新纪录后，t2这个表一共有6条记录！
| tom | 989 | /bin/bash |
| harry | 999 | /bin/bash |
+--------+------+---------------+

mysql> drop table db4.t2; 删除t2这个表
mysql> show master logs; 查看当前正在使用的日志文件是plj.000001
+------------+-----------+
| Log_name | File_size |
+------------+-----------+
| plj.000001 | 3604 |
+------------+-----------+

mysql> quit

# ls /logdir 查看存放日志文件的目录
plj.000001 plj.index

# mysqlbinlog /logdir/plj.000001 | grep -i insert
insert into db4.t2 因为插入bob,979这个内容时，换行写了，所以它只显示最后一行的内容。
insert into db4.t2 values("tom",989,"/bin/bash")
insert into db4.t2 values("harry",999,"/bin/bash")

# mysqlbinlog /logdir/plj.000001

可以看到下面内容
# at 296 这是执行这条命令开始的偏移量，以# at 数字296 开始。
... ... end_log_pos ... ... ... 这里有提示结束这条命令时的偏移量end_log_pos
... ...
insert into db4.t2 可看到有：插入bob,979的命令
values("bob",979,"/bin/bash")
... ...
insert into db4.t2 values("tom",989,"/bin/bash") 可看到有：插入tom,989的命令
... ...
insert into db4.t2 values("harry",999,"/bin/bash") 可看到有：插入harry,999的命令
... ...
COMMIT COMMIT是回车的意思，只有回车了，才能执行命令
/*!*/;
# at 1176 真正执行了这条命令时的偏移量，所以结束的偏移量一定要写到COMMIT这个单词之后出现的数字。
... ...
DROP TABLE `db4`.`t2` /* generated by server */ 可看到有：删除db4.t2表的命令
... ...
#################################################################################################
这个是供参考的另外一个例子，并不是这个实验中的配置。

... ...
# at 312 这是执行这条命令开始的偏移量，以# at 数字312 开始。
#181129 16:41:32 server id 12 end_log_pos 408 Query thread_id=7 exec_time=0 error_code=0 这里有提示结束这条命令时的偏移量是408。end_log_pos。执行这条命令开始的时间是18年11月29日16:41:32
use `db2`/*!*/;
SET TIMESTAMP=1543480892/*!*/;
insert into db2.t2 values("hello") 可看到有：插入的值为hello的命令
/*!*/;
# at 408 这是执行这条命令结束时的偏移量，以# at 数字408 开始。
#181129 16:41:32 server id 12 end_log_pos 435 Xid = 95
COMMIT/*!*/; COMMIT是回车的意思，只有回车了，才能执行命令
# at 435 真正执行了这条命令时的偏移量是435，所以结束的偏移量一定要写到COMMIT这个单词之后出现的数字。
#181129 16:41:48 server id 12 end_log_pos 502 Query thread_id=7 exec_time=0 error_code=0 #号之后是执行这条命令结束的时间是18年11月29日16:41:48，一定要记录到COMMIT这个单词之后出现的数字。
... ...
###############################################################################################
# mysql -uroot -p123456 db4 < /root/t2.sql 恢复完全备份的内容
# mysql -uroot -p123456
mysql> select * from db4.t2;
+--------+------+---------------+
| name | uid | shell |
+--------+------+---------------+
| root | 1 | /bin/bash | 成功恢复之前完全备份的那3行记录
| bin | 2 | /sbin/nologin |
| daemon | 3 | /sbin/nologin |
+--------+------+---------------+

mysql> quit

# mysqlbinlog --start-position=296 --stop-position=1176 /logdir/plj.000001 | mysql -uroot -p123456 恢复增量备份的内容！
# mysql -uroot -p123456
mysql> select * from db4.t2;
+--------+------+---------------+
| name | uid | shell |
+--------+------+---------------+
| root | 1 | /bin/bash |
| bin | 2 | /sbin/nologin |
| daemon | 3 | /sbin/nologin |
| bob | 979 | /bin/bash | 成功恢恢复增量备份的另3行内容，共6条记录！
| tom | 989 | /bin/bash |
| harry | 999 | /bin/bash |
+--------+------+---------------+

转载于:https://www.cnblogs.com/summer2/p/10788091.html

diebaoyan1530

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据增量备份

数据增量备份：方法1 启用binlog日志实现数据实时增量备份与数据恢复binlog日志: 类型: 二进制日志作用: 记录所有更改数据的操作配置: log_bin[=dir/name] log_bin代表启用binlog日志 server_id=数字指定id值 max_binlog_size=数字m启动日志文件# vim ...
复制链接

扫一扫