Mysql的binlog日志
环境准备
[root@mysql152 ~]# yum install -y mysql-server mysql
[root@mysql152 ~]# systemctl enable mysqld --now
1.查看正在使用的binlog日志文件
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 157 | | | |
+---------------+----------+--------------+------------------+-------------------+
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
执行查询命令 日志偏移量不变
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 157 | | | |
+---------------+----------+--------------+------------------+-------------------+
执行建库、建表命令
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 | | | |
+---------------+----------+--------------+------------------+-------------------+
mysql> insert into db1.user values ('王畅');
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 812 | | | |
+---------------+----------+--------------+------------------+-------------------+
2.自定义日志目录和日志名
[root@mysql152 ~]# cat /etc/my.cnf.d/mysql-server.cnf |grep log-bin
增加该配置
log-bin=/mylog/mysql152
[root@mysql152 ~]# mkdir /mylog/
[root@mysql152 ~]# chown -R mysql.mysql /mylog/
[root@mysql152 ~]# systemctl restart mysqld
[root@mysql152 ~]# ls /mylog/
mysql152.000001 mysql152.index
[root@mysql152 ~]# mysql
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysql152.000001 | 157 | | | |
+-----------------+----------+--------------+------------------+-------------------+
3.手动创建新的日志文件
说明：默认日志文件容量大于1G时会自动创建新的日志文件，在日志文件没写满时，执行的所有写命令都会保存到当前使用的日志文件里。
1.
//刷新前查看
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysql152.000001 | 157 | | | |
+-----------------+----------+--------------+------------------+-------------------+
#刷新日志
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysql152.000002 | 157 | | | |
+-----------------+----------+--------------+------------------+-------------------+2.
//只要服务重启就会创建新日志
[root@mysql152 ~]# systemctl restart mysqld
[root@mysql152 ~]# mysql
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysql152.000003 | 157 | | | |
+-----------------+----------+--------------+------------------+-------------------+3.
//完全备份后创建新的日志文件，创建的日志个数和备份库的个数一致
[root@mysql152 ~]# mysqldump --flush-logs mysql user > user.sql
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysql152.000004 | 157 | | | |
+-----------------+----------+--------------+------------------+-------------------+4.练习日志相关命令的使用
//查看已有的日志文件
mysql> show binary logs;
+-----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-----------------+-----------+-----------+
| mysql152.000001 | 203 | No |
| mysql152.000002 | 180 | No |
| mysql152.000003 | 203 | No |
| mysql152.000004 | 157 | No |
+-----------------+-----------+-----------+//查看正在使用的日志
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysql152.000004 | 157 | | | |
+-----------------+----------+--------------+------------------+-------------------+//插入记录
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 "mysql152.000004";
+-----------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+----------------+-----------+-------------+--------------------------------------+
| mysql152.000004 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.36, Binlog ver: 4 |
| mysql152.000004 | 126 | Previous_gtids | 1 | 157 | |
| mysql152.000004 | 157 | Anonymous_Gtid | 1 | 236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql152.000004 | 236 | Query | 1 | 307 | BEGIN |
| mysql152.000004 | 307 | Table_map | 1 | 360 | table_id: 96 (db1.user) |
| mysql152.000004 | 360 | Write_rows | 1 | 401 | table_id: 96 flags: STMT_END_F |
| mysql152.000004 | 401 | Xid | 1 | 432 | COMMIT /* xid=71 */ |
+-----------------+-----+----------------+-----------+-------------+--------------------------------------+//删除日志文件名之前的所有日志文件mysql> show binary logs;
+-----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-----------------+-----------+-----------+
| mysql152.000001 | 203 | No |
| mysql152.000002 | 180 | No |
| mysql152.000003 | 203 | No |
| mysql152.000004 | 432 | No |
+-----------------+-----------+-----------+
4 rows in set (0.00 sec)mysql> purge master logs to "mysql152.000003";
Query OK, 0 rows affected (0.01 sec)mysql> show binary logs;
+-----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-----------------+-----------+-----------+
| mysql152.000003 | 203 | No |
| mysql152.000004 | 432 | No |
+-----------------+-----------+-----------+
2 rows in set (0.00 sec)//删除所有日志文件，并重新创建日志文件
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)mysql> show binary logs;
+-----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-----------------+-----------+-----------+
| mysql152.000001 | 157 | No |
+-----------------+-----------+-----------+
1 row in set (0.00 sec)5.使用日志恢复数据
//重置日志
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysql152.000001 | 157 | | | |
+-----------------+----------+--------------+------------------+-------------------+//建库
mysql> create database gamedb;
Query OK, 1 row affected (0.00 sec)mysql> create table gamedb.t1(name char(10),class char(3));
Query OK, 0 rows affected (0.01 sec)mysql> insert into gamedb.t1 values ("yaya","nsd");
Query OK, 1 row affected (0.00 sec)mysql> insert into gamedb.t1 values ("yaya","nsd");
Query OK, 1 row affected (0.00 sec)mysql> insert into gamedb.t1 values ("yaya","nsd");
Query OK, 1 row affected (0.00 sec)//查看表记录
mysql> select * from gamedb.t1;
+------+-------+
| name | class |
+------+-------+
| yaya | nsd |
| yaya | nsd |
| yaya | nsd |
+------+-------+
3 rows in set (0.00 sec)
mysql> exit
Bye
//把日志文件拷贝给恢复数据的服务器,比如 mysql50
[root@mysql152 ~]# scp /mylog/mysql152.000001 root@192.168.10.150:/root在MySQL150 使用日志恢复数据
[root@mysql150 ~]# ls /root/mysql152.000001
/root/mysql152.000001
[root@mysql150 ~]# mysqlbinlog /root/mysql152.000001 |mysql -uroot -p123456
[root@mysql150 ~]# mysql -uroot -p123456 -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 |
+------+-------+