Mysql-增量备份-binlog

使用binlog日志进行备份

1.启动mysql的binlog日志

[root@localhost ~]# vim /etc/my.cnf
log_bin=/mylog/binlog #启动 bin_log日志
server_id=54           # 自定义id 一般用主机IP
max_binlog_size=1G # 指定日志容量 可以不开启 默认1G
[root@localhost ~]# chown mysql /mylog/ -R
[root@localhost ~]# systemctl restart mysqld

2.查看binlog日志是否开启

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      154 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

3.手动生成binlog日志的方式

1. [root@localhost ~]# systemctl restart mysqld 重启数据库
2. mysql> flush logs;# 生成新的日志
3. [root@localhost ~]# mysql -uroot -p123456 -e “flush log” #命令行中执行sql命令

说明:
	完全备份时备份几个库生成几个binlong日志
	使用刷新日志的方法,可以使某个日志,只记录指定的SQL命令,方便后期的恢复
	binlog日志,在每重启一次之后, 都会产生一个新的日志文件,索引文件的记录也会多出来一个,具体用哪个存,登录到数据库使用show master status;就可以查到了,规律:那个标号最大,就往哪个表里存

4.删除binlog日志

4.1. 删除指定日志之前的binlog日志

mysql> purge master logs to"binlog.000003";

4.2. 删除所有的binlog日志

mysql> reset master;

5.binlog恢复日志

5.1查看binlog日志语法

mysqlbinlog [选项] binlog日志文件

5.2恢复数据语法

mysqlbinlog [选项] binlog日志文件| mysql -uroot -p密码

5.3binlog读取日志的范围

偏移量
--start-position=数字 --stop-position=数字
时间
--start-datetime="yy-mm-dd hh:mm:ss" --stop-datetime="yy-mm-dd hh:mm:ss"

5.4使用Binlog日志恢复所有新产生的数据
下图中
54:代表54主机上的操作
50:代表50主机上的操作

54]
[root@localhost ~]# mysqldump -uroot -p123456 db3 user > /root/user.sql
[root@localhost ~]# scp user.sql 192.168.1.50:/opt/
mysql> insert into db3.user(name) values("a");
mysql> insert into db3.user(name) values("b");
mysql> insert into db3.user(name) values("c");
mysql> insert into db3.user(name) values("d");
mysql> insert into db3.user(name) values("e");
[root@localhost ~]# scp /mylog/binlog.000001  192.168.1.50:/opt/
50]
mysql> drop table db3.user;#模拟数据丢失
[root@xiaoyuan ~]# mysql -uroot -p123456 db3 </opt/user.sql  #完全恢复
[root@xiaoyuan ~]# mysqlbinlog binlog.000001 | mysql -uroot -p123456 #使用binlog进行增量备份

6.binlog日志记录方式

6.1statement 报表模式

update db3.user set password="A" where id <=5;

6.2row 行模式

update db3.user set password="A" where id =1;
update db3.user set password="A" where id =2;
update db3.user set password="A" where id =3;
update db3.user set password="A" where id =4;
update db3.user set password="A" where id =5;

6.3mixed 混合模式

根据sql语句的情况来决定具体记录的方式

6.4修改binlog日志的记录方式为mixed(混合模式)

[root@xiaoyuan ~]# vim /etc/my.cnf
binlog_format="mixed"
mysql> show variables like "binlog_format"; #查看binlog日志的记录方式

7.使用Binlog日恢复指定范围的数据

下图中
54:代表54主机上的操作
50:代表50主机上的操作

54]
[root@xiaoyuan ~]# vim /etc/my.cnf
binlog_format="mixed"
mysql> show variables like "binlog_format";
[root@localhost ~]# mysqlbinlog /mylog/binlog.000002 | grep -i insert 
SET INSERT_ID=82/*!*/;
insert into db3.user(name) values("a1")
SET INSERT_ID=83/*!*/;
insert into db3.user(name) values("a1")
SET INSERT_ID=84/*!*/;
insert into db3.user(name) values("a3")
SET INSERT_ID=85/*!*/;
insert into db3.user(name) values("a4")
[root@localhost ~]# scp -r /mylog/binlog.000002 192.168.1.50:/opt/

50]
找到对应的偏移量 进行恢复
[root@xiaoyuan ~]# mysqlbinlog --start-position=603 --stop-position=1363 /opt/binlog.000002 | mysql -uroot -p123456