MySql备份·实时备份mysqlbinlog

一、binlog日志介绍

• 是MySQL服务日志文件的一种，也叫二进制日志
• 保存除查询之外的sql命令
• 启用日志能够达数据自动备份数据的作用
• 搭建MySQL主从同步存储结构的必要条件
• 默认MySQL服务没有启用binlog日志

二、MySql日志的相关参数

分类	命令/目录/...	说明
文件	主机名-bin.000001	日志文件名命名格式
	主机名-bin.index	存放所有日志文件名的文件
路径	/var/lib/mysql	MySQL日志存放目录
mysql命令	show master status;	查看正在使用binlog日志名和偏移量
	show binary logs	查看数据库服务器当前已有全部 binlog日志文件
	purge master logs to "db50.00000x"	删除编号之前的所有日志文件
	show binlog events in "db50.00000x"	以数据库的形式查看db50.00000x日志信息
	flush logs	创建新日志文件的命令
linux命令	mysqlbinlog /mylog/db50.00000x	以文件的形式查看db50.00000x日志信息
	mysqlbinlog /目录名/日志文件名 | mysql -uroot -p密码	binlog日志文件恢复数据

三、自定义日志存放目录和日志文件名

• 修改配置文件my.cnf

[root@host50 ~]# vim /etc/my.cnf                
[mysqld]
# 添加以下二行
server_id = 50  # 每台数据库服务器的id必须要不一致，范围是1~255之间
log_bin = /mylog/db50 # log_bin定义自定义日志存放路径              
：wq # 保存退出

• 创建自定义日志目录

[root@host50 ~]# mkdir /mylog
# 修改属主属组为mysql
[root@host50 ~]# chown mysql /mylog
[root@host50 ~]# setenforce 0
setenforce: SELinux is disabled

• 重启服务生效配置

[root@host50 ~]# systemctl  restart mysqld

• 登录验证

[root@host50 ~]# mysql  -uroot -p密码
#查看正在使用binlog日志名和偏移量
MySQL> show master status；
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db50.000001 |      154 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+

# 查看是否生成默认二个文件
[root@host50 ~]# ls /mylog/
db50.000001  db50.index

# 查看index文件信息               
[root@host50 ~]# cat /mylog/db50.index 
/mylog/db50.000001

四、创建新的binlog日志文件

• 默认情况下日志文件保存的sql命令，让文件容量大于 1G 时数据服务，会自动创建新的日志文件 文件编号顺延。
• 可以手动创建新的binlog日志文件

手动方式一：

# 只要重启mysqld服务 会自动创建新日志文件
[root@host50 ~]# systemctl restart mysqld 

手动方式二：

# 完全备份后创建新的日志文件，添加刷新参数
[root@host50 ~]# mysqldump -uroot -p密码 --flush-logs 
-B tarena /bakdir/tarena.sql

手动方式三：

# 数据库管理员root执行，创建新日志文件的命令
mysql> flush logs;

五、查看日志文件内容

方法一：

# 使用系统命令 mysqlbinlog 查看
[root@host50 ~]# mysqlbinlog /mylog/db50.000001   

方法二：

# 使用数据库命令查看
mysql> show binlog events in "db50.000001" ;

六、使用binlog日志文件恢复数据

• 完全备份 tarena 库 并在完成备份 后 创建新的日志文件

[root@host50 ~]# mysqldump -uroot -p密码 --flush-logs -B tarena  > /bakdir/`date +%F`_tarena.sql

[root@host50 ~]# mysql -uroot -p密码
# 查看新日志文件名
Mysql>show master status
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db50.000002 |      154 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
完全备份后管理员root登陆
[root@host50 ~]# mysql -uroot -p密码
mysql> 
# 继续插入新的记录 
insert into tarena.user(name,uid)values("a",1111);
insert into tarena.user(name,uid)values("aa",1111);
insert into tarena.user(name,uid)values("aaa",1111);
insert into tarena.user(name,uid)values("ab",1111);
insert into tarena.user(name,uid)values("ac",1111);

# 查看日志文件的偏移量发生了改变
mysql> show master status;   

• 拷贝备份文件给 host51

# 把备份文件拷贝给51    
[root@host50 ~]# scp /bakdir/tarena.sql 192.168.88.51:/root/  

# 把日志文件拷贝给 51
[root@host50 ~]# scp /mylog/db50.000002 192.168.88.51:/root/

• Host51 恢复数据

# 使用完全备份恢复数据
[root@host51 ~]# mysql -uroot –Pnsd2022…a < /root/tarena.sql  

# 使用日志文件恢复数据
[root@host51 ~]# mysqlbinlog /root/db50.000002 | mysql -uroot -p密码

# 管理员登录后可以看到库和表记录
[root@host51 ~]# mysql -uroot -p密码
Mysql> show databases;
MySQL> select * from tarena.user;