1.开启Binlog日志
- 修改my.cnf文件
[root@tech-db-51 ~]#cat /etc/my.cnf
[mysqld]
server_id=100
log_bin=/mysql_log/log_bin_3306/mysql-log-bin
character_set_server=utf8mb4
log-error=/linux0224/mysql_3306/logs/3306-err.log
port=3306
user=mysql
basedir=/opt/mysql
datadir=/linux0224/mysql_3306/
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
[root@tech-db-51 ~]#
- 添加两个参数： server_id和log_bin
- 事先创建好 log_bin的文件夹：/mysql_log/log_bin_3306/mysql-log-bin
注意：需要给mysql_log 给mysql权限
chown -R mysql.mysql /mysql_log/
- 重启mysqld
systemctl restart mysqld
- 检查mysql,二进制日志的变量配置信息
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------+
| log_bin | ON |
| log_bin_basename | /mysql_log/log_bin_3306/mysql-log-bin |
| log_bin_index | /mysql_log/log_bin_3306/mysql-log-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------------------------+
6 rows in set (0.01 sec)
mysql>
2.查看Binlog日志情况
- 查看当前数据库，正在用的那个bin-log
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-log-bin.000001 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> create database biekun;
Query OK, 1 row affected (0.05 sec)
mysql> show master status
-> ;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-log-bin.000001 | 319 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 查看当前mysql 所有的binlog记录
mysql> show binary logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| mysql-log-bin.000001 | 535 |
| mysql-log-bin.000002 | 154 |
+----------------------+-----------+
2 rows in set (0.00 sec)
- 刷新新日志文件
一般用于数据库恢复，数据库重置时，才会用到。
除非你已经做好了全量备份，次日新增数据时，重新记录新的binlog也行。
flush logs;
初始化的时候只有：mysql-log-bin.000001
执行过命令之后会再生成：mysql-log-bin.000002
- 模拟binlog记录
mysql> create table ceshi (id INT);
Query OK, 0 rows affected (0.05 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-log-bin.000002 | 324 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> insert into ceshi values(1);
Query OK, 1 row affected (0.01 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-log-bin.000002 | 584 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
