加入收藏 | 设为首页 | 会员中心 | 我要投稿 应用网_丽江站长网 （http://www.0888zz.com/）- 科技、建站、数据工具、云上网络、机器学习!
当前位置： 首页 > 站长学院 > MySql教程 > 正文

mysql的binary-log运作

发布时间：2022-03-22 03:17:02 所属栏目：MySql教程 来源：互联网
导读：mysql的binary-log运作： 一，设置/etc/my.cnf参数打开binary log log-bin=mysql-bin server-id=1 二，关于二进制日志参数 mysql show variables like %log_bin%; +---------------------------------+--------------------------------+ | Variable_name
       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)
  
三，查看当前的binary log
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)
 
      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
 
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.
 
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 |
+------------------+-----------+
 
九，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]# 

（编辑：应用网_丽江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
      站长推荐
      热点阅读

        【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

        建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的优质浏览效果

        Copygight © 2013-2023 http://www.0888zz.com/ All Rights Reserved. 应用网_丽江站长网

        ICP备案：浙ICP备12044117号 浙公网安备 33038102330468号