binlog日志

原创

Lingjingcheng66 2024-12-23 19:55:22 博主文章分类：数据库 ©著作权

文章标签 mysql 日志文件恢复数据 binlog日志 文章分类 运维 私藏项目实操分享

©著作权归作者所有：来自51CTO博客作者Lingjingcheng66的原创作品，请联系作者获取转载授权，否则将追究法律责任

1案例：binlog日志

日志介绍

什么是binlog日志？

1.也称做二进制日志

2.MySQL服务日志文件的一种

3.保存除查询之外的所有SQL命令

4.可用于数据的备份和恢复

5.配置mysql主从同步的必要条件

1题

查看正在使用的binlog日志文件
自定义日志目录和日志名
手动创建新的日志文件
练习日志相关命令的使用
使用日志恢复数据

2 方案

准备新的数据库服务器如

binlog日志_mysql

3 步骤

实现此案例需要按照如下步骤进行。

步骤一：查看正在使用的binlog日志文件

在新创建的数据库服务器做如下操作:

[root@mysql52 ~]# mysql  连接服务
 mysql> show master status; 查看日志文件
 +----------------+----------+--------------+------------------+-------------------+
 | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +----------------+----------+--------------+------------------+-------------------+
 | binlog.000001 |      156 |              |                  |                   |
 +----------------+----------+--------------+------------------+-------------------+
 1 row in set (0.00 sec)

执行查询命令

mysql> select count(*) from  mysql.user;
 +----------+
 | count(*) |
 +----------+
 |        4 |
 +----------+
 1 row in set (0.00 sec)

执行查询命令日志偏移量不变

mysql> show master status; 
 +----------------+----------+--------------+------------------+-------------------+
 | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +----------------+----------+--------------+------------------+-------------------+
 | binlog.000001 |      156 |              |                  |                   |
 +----------------+----------+--------------+------------------+-------------------+
 1 row in set (0.00 sec)

执行建库、建表命令

mysql> create database  db1;
 mysql> create table db1.user(name char(10));

执行写命令日志偏移量改变

mysql> show master status; 
 +----------------+----------+--------------+------------------+-------------------+
 | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +----------------+----------+--------------+------------------+-------------------+
 | binlog.000001 |      535 |              |                  |                   |
 +----------------+----------+--------------+------------------+-------------------+
 1 row in set (0.00 sec)

插入记录

mysql> insert into db1.user values("jim");

执行写命令日志偏移量改变

mysql> show master status; 
 +----------------+----------+--------------+------------------+-------------------+
 | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +----------------+----------+--------------+------------------+-------------------+
 | binlog.000001 |      809 |              |                  |                   |
 +----------------+----------+--------------+------------------+-------------------+
 1 row in set (0.00 sec)
 mysql>

步骤二：自定义日志目录和日志名

说明：日志文件默认保存在/var/lib/mysql目录下

[root@mysql52 ~]# vim /etc/my.cnf.d/mysql-server.cnf 
 [mysqld]
 log-bin=/mylog/mysql52   //定义日志目录和日志文件名(手动添加)
 :wq
 [root@mysql52 ~]# mkdir /mylog  创建目录
 [root@mysql52 ~]# chown  mysql  /mylog   修改目录所有者mysql用户
 [root@mysql52 ~]# setenforce  0   关闭selinux
 [root@mysql52 ~]# systemctl  restart mysqld   重启服务
 [root@mysql52 ~]# ls /mylog/  查看日志目录
 mysql52.000001  mysql52.index

登陆服务

[root@mysql52 ~]# mysql

查看日志信息

Mysql> show master status ； 
 +----------------+----------+--------------+------------------+-------------------+
 | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +----------------+----------+--------------+------------------+-------------------+
 | mysql52.000001 |      156 |              |                  |                   |
 +----------------+----------+--------------+------------------+-------------------+

步骤三：手动创建新的日志文件

说明：默认日志文件容量大于1G时会自动创建新的日志文件，在日志文件没写满时，执行的所有写命令都会保存到当前使用的日志文件里。

//刷新前查看

mysql> show master status;
 +----------------+----------+--------------+------------------+-------------------+
 | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +----------------+----------+--------------+------------------+-------------------+
 | mysql52.000001 |      156 |              |                  |                   |
 +----------------+----------+--------------+------------------+-------------------+
 1 row in set (0.00 sec)

//刷新日志

mysql> flush logs;

//查看日志文件

mysql> show master status; 
 +----------------+----------+--------------+------------------+-------------------+
 | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +----------------+----------+--------------+------------------+-------------------+
 | mysql52.000002 |      156 |              |                  |                   |
 +----------------+----------+--------------+------------------+-------------------+
 1 row in set (0.00 sec)

//只要服务重启就会创建新日志

[root@mysql52 ~]# systemctl  restart mysqld 
 [root@mysql52 ~]# mysql  连接服务
 Mysql> show master status; 查看日志
 +----------------+----------+--------------+------------------+-------------------+
 | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +----------------+----------+--------------+------------------+-------------------+
 | mysql52.000003 |      156 |              |                  |                   |
 +----------------+----------+--------------+------------------+-------------------+
 [root@mysql52 ~]#

//完全备份后创建新的日志文件，创建的日志个数和备份库的个数一致

[root@mysql52 ~]# mysqldump  --flush-logs  mysql user > user.sql

//查看日志

[root@mysql52 ~]# mysql -e 'show master status'
 +----------------+----------+--------------+------------------+-------------------+
 | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +----------------+----------+--------------+------------------+-------------------+
 | mysql52.000004 |      156 |              |                  |                   |
 +----------------+----------+--------------+------------------+-------------------+

步骤四：练习日志相关命令的使用

//查看已有的日志文件

mysql> show binary  logs;
 日志文件名        日志大小(字节)  加密（no/yes）
 +----------------+-----------+-----------+
 | Log_name       | File_size | Encrypted |
 +----------------+-----------+-----------+
 | mysql52.000001 |       201 | No        |
 | mysql52.000002 |       201 | No        |
 | mysql52.000003 |       179 | No        |
 | mysql52.000004 |       201 | No        |
 +----------------+-----------+-----------+
 7 rows in set (0.00 sec)

//查看正在使用的日志

mysql> show master status;
 +----------------+----------+--------------+------------------+-------------------+
 | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +----------------+----------+--------------+------------------+-------------------+
 | mysql52.000004 |      156 |              |                  |                   |
 +----------------+----------+--------------+------------------+-------------------+
 1 row in set (0.00 sec)

//插入记录

mysql> insert into db1.user values("yaya");

//查看日志文件内容

说明:

Log_name：日志文件名。

Pos：命令在日志文件中的起始位置。

Event_type：事件类型，例如 Query、Table_map、Write_rows 等。

Server_id：服务器 ID。

End_log_pos：命令在文件中的结束位置，以字节为单位。

Info：执行命令信息。

mysql> show binlog events in  "mysql52.000007";
 +----------------+-----+----------------+-----------+-------------+--------------------------------------+
 | Log_name       | Pos | Event_type     | Server_id | End_log_pos | Info                                 |
 +----------------+-----+----------------+-----------+-------------+--------------------------------------+
 | mysql52.000007 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.26, Binlog ver: 4    |
 | mysql52.000007 | 125 | Previous_gtids |         1 |         156 |                                      |
 | mysql52.000007 | 156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
 | mysql52.000007 | 235 | Query          |         1 |         306 | BEGIN                                |
 | mysql52.000007 | 306 | Table_map      |         1 |         359 | table_id: 108 (db1.user)             |
 | mysql52.000007 | 359 | Write_rows     |         1 |         400 | table_id: 108 flags: STMT_END_F      |
 | mysql52.000007 | 400 | Xid            |         1 |         431 | COMMIT /* xid=649 */                 |
 +----------------+-----+----------------+-----------+-------------+--------------------------------------+
 7 rows in set (0.00 sec)

//删除日志文件名之前的所有日志文件

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

//查看已有的日志文件

mysql> show binary logs;
 +----------------+-----------+-----------+
 | Log_name       | File_size | Encrypted |
 +----------------+-----------+-----------+
 | mysql52.000003 |       201 | No        |
 | mysql52.000004 |       201 | No        |
 +----------------+-----------+-----------+

//删除所有日志文件，并重新创建日志文件

mysql> reset master;

//查看已有的日志文件，仅有第1个文件了

mysql> show binary logs;
 +----------------+-----------+-----------+
 | Log_name       | File_size | Encrypted |
 +----------------+-----------+-----------+
 | mysql52.000001 |       156 | No        |
 +----------------+-----------+-----------+
 1 row in set (0.00 sec)

步骤五：使用日志恢复数据

1）在mysql52主机执行如下操：

//重置日志

mysql> reset master;

//查看日志

mysql> show master status;
 +----------------+----------+--------------+------------------+-------------------+
 | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +----------------+----------+--------------+------------------+-------------------+
 | mysql52.000001 |      156 |              |                  |                   |
 +----------------+----------+--------------+------------------+-------------------+
 1 row in set (0.00 sec)

//建库

mysql> create database gamedb;

//建表

mysql> create table gamedb.t1(name char(10),class char(3));

//插入记录

mysql> insert into gamedb.t1 values ("yaya","nsd");
 mysql> insert into gamedb.t1 values ("yaya","nsd");
 mysql> insert into gamedb.t1 values ("yaya","nsd");

//查看表记录

mysql> select  * from gamedb.t1;
 +------+-------+
 | name | class |
 +------+-------+
 | yaya | nsd   |
 | yaya | nsd   |
 | yaya | nsd   |
 +------+-------+
 3 rows in set (0.00 sec)
 mysql> exit

//把日志文件拷贝给恢复数据的服务器,比如 mysql50

[root@mysql52 ~]# scp  /mylog/mysql52.000001 root@192.168.88.50:/root/

2）在MySQL50 使用日志恢复数据

//查看日志

[root@mysql50 ~]# ls /root/mysql52.000001 
 /root/mysql52.000001

//执行日志恢复数据

[root@mysql50 ~]# mysqlbinlog /root/mysql52.000001 | mysql -uroot -pNSD2023...a

//连接服务查看数据

[root@mysql50 ~]# mysql -uroot -pNSD2023...a -e 'select * from gamedb.t1'
 mysql: [Warning] Using a password on the command line interface can be insecure.
 +------+-------+
 | name | class |
 +------+-------+
 | yaya | nsd   |
 | yaya | nsd   |
 | yaya | nsd   |
 +------+-------+
 [root@mysql50 ~]#