mysql的binary-log操作
发表于：2024-11-15 作者：千家信息网编辑
千家信息网最后更新 2024年11月15日，一，设置/etc/my.cnf参数打开binary loglog-bin=mysql-binserver-id=1二，关于二进制日志参数mysql> show variables like '%log
千家信息网最后更新 2024年11月15日mysql的binary-log操作一，设置/etc/my.cnf参数打开binary log
log-bin=mysql-bin
server-id=1
二，关于二进制日志参数
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-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>
三，查看当前的binary log
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql>
四，binary log 索引
[root@node1 mysql]# more /var/lib/mysql/mysql-bin.index
./mysql-bin.000001
[root@node1 mysql]#
五，设置最大日志size
mysql> show variables like '%max_binlog%';
+----------------------------+----------------------+
| Variable_name | Value |
+----------------------------+----------------------+
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
+----------------------------+----------------------+
3 rows in set (0.00 sec)
mysql>
六，切换日志
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 154 |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql>
七，从新设置日志，删除所有的日志，并从新生成一个日志
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql>
八，删除日志
mysql> help purge;
Name: 'PURGE BINARY LOGS'
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
The binary log is a set of files that contain information about data
modifications made by the MySQL server. The log consists of a set of
binary log files, plus an index file (see
http://dev.mysql.com/doc/refman/5.7/en/binary-log.html).
The PURGE BINARY LOGS statement deletes all the binary log files listed
in the log index file prior to the specified log file name or date.
BINARY and MASTER are synonyms. Deleted log files also are removed from
the list recorded in the index file, so that the given log file becomes
the first in the list.
This statement has no effect if the server was not started with the
--log-bin option to enable binary logging.
URL: http://dev.mysql.com/doc/refman/5.7/en/purge-binary-logs.html
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
mysql>
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 201 |
| mysql-bin.000005 | 154 |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql> PURGE BINARY LOGS TO 'mysql-bin.000003';
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 201 |
| mysql-bin.000005 | 154 |
+------------------+-----------+
3 rows in set (0.01 sec)
mysql>
九，binlog_format设置 statment,row,mixed
mysql> show variables like '%format%';
+---------------------------+-------------------+
| Variable_name | Value |
+---------------------------+-------------------+
| binlog_format | ROW |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| innodb_default_row_format | dynamic |
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| time_format | %H:%i:%s |
+---------------------------+-------------------+
9 rows in set (0.01 sec)
mysql>
当设置成row的时候无法读懂dml语句
mysql> desc test1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into test1 values(1);
Query OK, 1 row affected (0.05 sec)
[root@node1 mysql]# mysqlbinlog mysql-bin.000005
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160417 22:23:55 server id 1 end_log_pos 123 CRC32 0xf2f394d3 Start: binlog v 4, server v 5.7.11-log created 160417 22:23:55
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
e5wTVw8BAAAAdwAAAHsAAAABAAQANS43LjExLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AdOU8/I=
'/*!*/;
# at 123
#160417 22:23:55 server id 1 end_log_pos 154 CRC32 0xc32a5dce Previous-GTIDs
# [empty]
# at 154
#160417 22:35:04 server id 1 end_log_pos 219 CRC32 0x97a78d76 Anonymous_GTID last_committed=sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#160417 22:35:04 server id 1 end_log_pos 293 CRC32 0x907830ed Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1460903704/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
#160417 22:35:04 server id 1 end_log_pos 343 CRC32 0x06337336 Table_map: `testdb`.`test1` mapped to number 112
# at 343
#160417 22:35:04 server id 1 end_log_pos 383 CRC32 0x8934c1da Write_rows: table id 112 flags: STMT_END_F
BINLOG '
GJ8TVxMBAAAAMgAAAFcBAAAAAHAAAAAAAAEABnRlc3RkYgAFdGVzdDEAAQMAATZzMwY=
GJ8TVx4BAAAAKAAAAH8BAAAAAHAAAAAAAAEAAgAB//4BAAAA2sE0iQ==
'/*!*/;
# at 383
#160417 22:35:04 server id 1 end_log_pos 414 CRC32 0x2dd7f541 Xid = 41
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@node1 mysql]#
[root@node1 mysql]#
但是dcl语句还是可以看明白
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> GRANT ALL PRIVILEGES ON *.* TO user1;
ERROR 1133 (42000): Unknown error 1133
mysql>
mysql>
mysql>
mysql>
mysql> create user test1;
Query OK, 0 rows affected (0.00 sec)
mysql>
COMMIT/*!*/;
# at 414
#160417 22:41:45 server id 1 end_log_pos 479 CRC32 0x01464b0a Anonymous_GTID last_committed=sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 479
#160417 22:41:45 server id 1 end_log_pos 618 CRC32 0x450cd4fe Query thread_id=3 exec_time=0 error_code=0
use `mysql`/*!*/;
SET TIMESTAMP=1460904105/*!*/;
CREATE USER 'test1'@'%' IDENTIFIED WITH 'mysql_native_password'
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@node1 mysql]#
log-bin=mysql-bin
server-id=1
二，关于二进制日志参数
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-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>
三，查看当前的binary log
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql>
四，binary log 索引
[root@node1 mysql]# more /var/lib/mysql/mysql-bin.index
./mysql-bin.000001
[root@node1 mysql]#
五，设置最大日志size
mysql> show variables like '%max_binlog%';
+----------------------------+----------------------+
| Variable_name | Value |
+----------------------------+----------------------+
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
+----------------------------+----------------------+
3 rows in set (0.00 sec)
mysql>
六，切换日志
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 154 |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql>
七，从新设置日志，删除所有的日志，并从新生成一个日志
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql>
八，删除日志
mysql> help purge;
Name: 'PURGE BINARY LOGS'
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
The binary log is a set of files that contain information about data
modifications made by the MySQL server. The log consists of a set of
binary log files, plus an index file (see
http://dev.mysql.com/doc/refman/5.7/en/binary-log.html).
The PURGE BINARY LOGS statement deletes all the binary log files listed
in the log index file prior to the specified log file name or date.
BINARY and MASTER are synonyms. Deleted log files also are removed from
the list recorded in the index file, so that the given log file becomes
the first in the list.
This statement has no effect if the server was not started with the
--log-bin option to enable binary logging.
URL: http://dev.mysql.com/doc/refman/5.7/en/purge-binary-logs.html
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
mysql>
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 201 |
| mysql-bin.000005 | 154 |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql> PURGE BINARY LOGS TO 'mysql-bin.000003';
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 201 |
| mysql-bin.000005 | 154 |
+------------------+-----------+
3 rows in set (0.01 sec)
mysql>
九，binlog_format设置 statment,row,mixed
mysql> show variables like '%format%';
+---------------------------+-------------------+
| Variable_name | Value |
+---------------------------+-------------------+
| binlog_format | ROW |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| innodb_default_row_format | dynamic |
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| time_format | %H:%i:%s |
+---------------------------+-------------------+
9 rows in set (0.01 sec)
mysql>
当设置成row的时候无法读懂dml语句
mysql> desc test1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into test1 values(1);
Query OK, 1 row affected (0.05 sec)
[root@node1 mysql]# mysqlbinlog mysql-bin.000005
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160417 22:23:55 server id 1 end_log_pos 123 CRC32 0xf2f394d3 Start: binlog v 4, server v 5.7.11-log created 160417 22:23:55
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
e5wTVw8BAAAAdwAAAHsAAAABAAQANS43LjExLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AdOU8/I=
'/*!*/;
# at 123
#160417 22:23:55 server id 1 end_log_pos 154 CRC32 0xc32a5dce Previous-GTIDs
# [empty]
# at 154
#160417 22:35:04 server id 1 end_log_pos 219 CRC32 0x97a78d76 Anonymous_GTID last_committed=sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#160417 22:35:04 server id 1 end_log_pos 293 CRC32 0x907830ed Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1460903704/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
#160417 22:35:04 server id 1 end_log_pos 343 CRC32 0x06337336 Table_map: `testdb`.`test1` mapped to number 112
# at 343
#160417 22:35:04 server id 1 end_log_pos 383 CRC32 0x8934c1da Write_rows: table id 112 flags: STMT_END_F
BINLOG '
GJ8TVxMBAAAAMgAAAFcBAAAAAHAAAAAAAAEABnRlc3RkYgAFdGVzdDEAAQMAATZzMwY=
GJ8TVx4BAAAAKAAAAH8BAAAAAHAAAAAAAAEAAgAB//4BAAAA2sE0iQ==
'/*!*/;
# at 383
#160417 22:35:04 server id 1 end_log_pos 414 CRC32 0x2dd7f541 Xid = 41
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@node1 mysql]#
[root@node1 mysql]#
但是dcl语句还是可以看明白
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> GRANT ALL PRIVILEGES ON *.* TO user1;
ERROR 1133 (42000): Unknown error 1133
mysql>
mysql>
mysql>
mysql>
mysql> create user test1;
Query OK, 0 rows affected (0.00 sec)
mysql>
COMMIT/*!*/;
# at 414
#160417 22:41:45 server id 1 end_log_pos 479 CRC32 0x01464b0a Anonymous_GTID last_committed=sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 479
#160417 22:41:45 server id 1 end_log_pos 618 CRC32 0x450cd4fe Query thread_id=3 exec_time=0 error_code=0
use `mysql`/*!*/;
SET TIMESTAMP=1460904105/*!*/;
CREATE USER 'test1'@'%' IDENTIFIED WITH 'mysql_native_password'
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@node1 mysql]#
日志
参数
语句
最大
二进制
时候
索引
还是
切换
生成
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
数据库身份证代码
肇庆oa软件开发订制
网络安全机构设置和人员配备
服务器代理软件
新疆JAVA软件开发培训
服务器降低网络安全级别
软件开发中的代码怎么封装
载入静态数据库失败
nginx图片服务器上传
公益网络安全宣传证书有学分吗
纯净版win7安装数据库
软件开发设计用到的专业术语
武汉盛大互联网科技公司
arcgis软件开发投资
常用化学工具软件及数据库
8月5号国家网络安全
删除数据库两个字段数据
有关学生网络安全的视频
软件开发过程案例分析
常州信息软件开发销售价格
互联网银行与金融科技
代号探戈服务器核心打不开
互联网下的小学科技制作
钱去哪了网络安全
数据库报错拒绝连接
金秋十月网络安全宣传标语
计算机网络技术真题2020
网络安全小学地方课件
创建房间用什么服务器
数据库视图如何取消加密
