mysql> show variables like 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 8.0.13 | +---------------+--------+ 1 row in set, 1warning (0.00 sec)
mysql> showvariableslike'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set, 1warning (0.01 sec) mysql> showvariableslike'%log_bin%'; +---------------------------------+---------------------------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------------------------+ | log_bin | ON | | log_bin_basename | D:\tools\mysql-8.0.13-winx64\data\mysql-bin | | log_bin_index | D:\tools\mysql-8.0.13-winx64\data\mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+---------------------------------------------------+ 6 rows in set, 1warning (0.01 sec)
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #190731 17:41:00 server id 1 end_log_pos 124 CRC32 0xe904f22c Start: binlog v 4, server v 8.0.13 created 190731 17:41:00 BINLOG ' LGJBXQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjEzAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA CgEs8gTp '/*!*/; # at 124 #190731 17:41:00 server id 1 end_log_pos 155 CRC32 0xd12224e5 Previous-GTIDs # [empty] # at 155 #190731 17:41:15 server id 1 end_log_pos 230 CRC32 0x25b4eff3 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1564566075183241 immediate_commit_timestamp=1564566075183241 transaction_length=294 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1564566075183241 (2019-07-31 17:41:15.183241 ?D1ú±ê×?ê±??) # immediate_commit_timestamp=1564566075183241 (2019-07-31 17:41:15.183241 ?D1ú±ê×?ê±??) /*!80001 SET @@session.original_commit_timestamp=1564566075183241*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 230 #190731 17:41:15 server id 1 end_log_pos 309 CRC32 0xb95da169 Query thread_id=9 exec_time=0 error_code=0 SETTIMESTAMP=1564566075/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1168113696/*!*/; 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=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; /*!80013 SET @@session.sql_require_primary_key=0*//*!*/; BEGIN /*!*/; # at 309 #190731 17:41:15 server id 1 end_log_pos 368 CRC32 0x7203817f Table_map: `binlog`.`test` mapped to number 115 # at 368 #190731 17:41:15 server id 1 end_log_pos 418 CRC32 0x8be3841b Write_rows: table id 115 flags: STMT_END_F
BINLOG ' O2JBXRMBAAAAOwAAAHABAAAAAHMAAAAAAAEABmJpbmxvZwAEdGVzdAADAwMPAjwABgEBAAIBIX+B A3I= O2JBXR4BAAAAMgAAAKIBAAAAAHMAAAAAAAEAAgAD/wABAAAAAQAAAAV6aGFuZxuE44s= '/*!*/; # at 418 #190731 17:41:15 server id 1 end_log_pos 449 CRC32 0xfc026d64 Xid = 177 COMMIT/*!*/; # at 449 #190731 17:41:22 server id 1 end_log_pos 524 CRC32 0x113e4745 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes original_committed_timestamp=1564566082696808 immediate_commit_timestamp=1564566082696808 transaction_length=293 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1564566082696808 (2019-07-31 17:41:22.696808 ?D1ú±ê×?ê±??) # immediate_commit_timestamp=1564566082696808 (2019-07-31 17:41:22.696808 ?D1ú±ê×?ê±??) /*!80001 SET @@session.original_commit_timestamp=1564566082696808*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 524 #190731 17:41:22 server id 1 end_log_pos 603 CRC32 0xe309f990 Query thread_id=9 exec_time=0 error_code=0 SETTIMESTAMP=1564566082/*!*/; /*!80013 SET @@session.sql_require_primary_key=0*//*!*/; BEGIN /*!*/; # at 603 #190731 17:41:22 server id 1 end_log_pos 662 CRC32 0xc07d94f6 Table_map: `binlog`.`test` mapped to number 115 # at 662 #190731 17:41:22 server id 1 end_log_pos 711 CRC32 0x42d695e3 Write_rows: table id 115 flags: STMT_END_F
BINLOG ' QmJBXRMBAAAAOwAAAJYCAAAAAHMAAAAAAAEABmJpbmxvZwAEdGVzdAADAwMPAjwABgEBAAIBIfaU fcA= QmJBXR4BAAAAMQAAAMcCAAAAAHMAAAAAAAEAAgAD/wACAAAAAgAAAAR3YW5n45XWQg== '/*!*/; # at 711 #190731 17:41:22 server id 1 end_log_pos 742 CRC32 0x8bde4f70 Xid = 178 COMMIT/*!*/; # at 742 #190731 17:41:38 server id 1 end_log_pos 789 CRC32 0xcf82cc85 Rotate to mysql-bin.000003 pos: 4 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*/;
#恢复第一条数据 D:\tools\mysql-8.0.13-winx64\bin>mysqlbinlog ..\data\mysql-bin.000002 --stop-position=603 | mysql -uroot -p Enter password: ****** #恢复第二条数据 D:\tools\mysql-8.0.13-winx64\bin>mysqlbinlog ..\data\mysql-bin.000002 --start-position=603 --stop-position=742 | mysql -uroot -p Enter password: ******
恢复数据mysql-bin.000003
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
BEGIN /*!*/; # at 309 #190731 17:41:44 server id 1 end_log_pos 368 CRC32 0x6286ca84 Table_map: `binlog`.`test` mapped to number 115 # at 368 #190731 17:41:44 server id 1 end_log_pos 417 CRC32 0x5004c380 Write_rows: table id 115 flags: STMT_END_F
BINLOG ' WGJBXRMBAAAAOwAAAHABAAAAAHMAAAAAAAEABmJpbmxvZwAEdGVzdAADAwMPAjwABgEBAAIBIYTK hmI= WGJBXR4BAAAAMQAAAKEBAAAAAHMAAAAAAAEAAgAD/wADAAAAAwAAAAR6aGFvgMMEUA== '/*!*/; # at 417 #190731 17:41:44 server id 1 end_log_pos 448 CRC32 0x8433520a Xid = 180 COMMIT/*!*/; #恢复第三条数据 D:\tools\mysql-8.0.13-winx64\bin>mysqlbinlog ..\data\mysql-bin.000003 --start-position=309 --stop-position=417 | mysql -uroot -p Enter password: ******
BEGIN /*!*/; # at 309 #190731 19:29:49 server id 1 end_log_pos 368 CRC32 0x0bc503bf Table_map: `binlog`.`test` mapped to number 62 # at 368 #190731 19:29:49 server id 1 end_log_pos 416 CRC32 0x5a878e6d Write_rows: table id 62 flags: STMT_END_F ### INSERT INTO `binlog`.`test` ### SET ### @1=4 ### @2=4 ### @3='tan' # at 416 #190731 19:29:49 server id 1 end_log_pos 447 CRC32 0x5064407d Xid = 39 COMMIT/*!*/;
常用命令
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
是否启用了日志 mysql>show variables like 'log_bin';
怎样知道当前的日志 mysql> show master status;
查看mysql binlog模式 show variables like 'binlog_format';
获取binlog文件列表 show binary logs;
查看当前正在写入的binlog文件 show master status\G
查看指定binlog文件的内容 show binlog events in 'mysql-bin.000002';
# The number of milliseconds of each tick tickTime=2000 # The number of ticks that the initial # synchronization phase can take initLimit=10 # The number of ticks that can pass between # sending a request and getting an acknowledgement syncLimit=5 # the directory where the snapshot is stored. # do not use /tmp for storage, /tmp here is just # example sakes. dataDir=/tmp/zookeeper/data dataLogDir=/tmp/zookeeper/logs # the port at which the clients will connect clientPort=2181 # the maximum number of client connections. # increase this if you need to handle more clients #maxClientCnxns=60 # # Be sure to read the maintenance section of the # administrator guide before turning on autopurge. # # http://zookeeper.apache.org/doc/current/zookeeperAdmin.html#sc_maintenance # # The number of snapshots to retain in dataDir #autopurge.snapRetainCount=3 # Purge task interval in hours # Set to "0" to disable auto purge feature #autopurge.purgeInterval=1
启动服务
1 2 3 4
$ bin/zkServer.sh start ZooKeeper JMX enabled by default Using config: /home/boat/zookeeper-3.4.10/bin/../conf/zoo.cfg Starting zookeeper ... STARTED
查看状态
1 2 3 4
$ bin/zkServer.sh status ZooKeeper JMX enabled by default Using config: /home/boat/zookeeper-3.4.10/bin/../conf/zoo.cfg Mode: standalone
Connecting to localhost:2181 2019-02-17 23:42:15,403 [myid:] - INFO [main-SendThread(localhost:2181):ClientCnxn$SendThread@1032] - Opening socket connection to server localhost/127.0.0.1:2181. Will not attempt to authenticate using SASL (unknown error) Welcome to ZooKeeper!
Welcome to Hexo! This is your very first post. Check documentation for more info. If you get any problems when using Hexo, you can find the answer in troubleshooting or you can ask me on GitHub.