一、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;
