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]#
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29108064/viewspace-2083025/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL的基本操作MySql
- wampSever的mysql操作MySql
- MySQL 常用的UPDATE操作MySql
- mysql下的flush操作MySql
- mysql操作MySql
- Linux下的Mysql操作LinuxMySql
- mysql常用的優化操作MySql優化
- mysql的簡單基本操作MySql
- mysql lock操作MySql
- ORM操作mysqlORMMySql
- MySQL 常用操作MySql
- mysql常用操作MySql
- MySQL操作表MySql
- mysql基本操作MySql
- mysql騷操作MySql
- MySQL關聯多表更新的操作MySql
- MySQL資料表的基本操作MySql
- 【MySQL】恢復誤操作的方法MySql
- mysql類似merge的操作MySql
- 04 MySQL 表的基本操作-DDLMySql
- Python 操作 MySQLPythonMySql
- mysql加快alter操作MySql
- python操作mysqlPythonMySql
- MySQL 事務操作MySql
- mysql常見操作MySql
- MySQL基本操作命令MySql
- [php]mysql操作流程PHPMySql
- MySQL表更新操作MySql
- php mysqli操作 mysqlPHPMySql
- javaertogo之mysql操作JavaGoMySql
- MySQL DDL操作表MySql
- Mysql跨庫操作MySql
- Go 語言操作 MySQL 之 CURD 操作GoMySql
- mysql的安裝和簡單的操作MySql
- mysql 索引的基礎操作彙總MySql索引
- mysql之行(記錄)的詳細操作MySql
- MySQL的一些操作記錄MySql
- Python 操作 MySQL 的5種方式PythonMySql