mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> show table status;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| t1 | InnoDB | 10 | Compact | 1 | 16384 | 16384 | 0 | 0 | 0 | NULL | 2017-03-24 14:38:15 | NULL | NULL | latin1_swedish_ci | NULL | | |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)
mysql> show table status G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-03-24 14:38:15
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> select * from t1;
+------+--------+
| Id | Name |
+------+--------+
| 2 | cherry |
| 1 | jason |
+------+--------+
2 rows in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 143 |
| mysql-bin.000002 | 583 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.07 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> exit
Bye
[mysql@mysql ~]$ mysqldump -uroot -p --skip-opt --quick --extended-insert=false --single-transaction --master-data=2 --databases test >./test_db.sql
Enter password:
[mysql@mysql ~]$ ls -ltr
total 4
-rw-rw-r--. 1 mysql mysql 1520 Mar 27 15:33 test_db.sql
[mysql@mysql ~]$ more test_db.sql
-- MySQL dump 10.13 Distrib 5.6.34, for Linux (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.6.34-log
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CH
ECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' *
/;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;
--
-- Current Database: `test`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTE
R SET latin1 */;
USE `test`;
--
-- Table structure for table `t1`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`Id` int(11) DEFAULT NULL,
`Name` varchar(20) DEFAULT NULL
);
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
INSERT INTO `t1` VALUES (2,'cherry');
INSERT INTO `t1` VALUES (1,'jason');
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2017-03-27 15:33:06
[mysql@mysql ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 6
Server version: 5.6.34-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> insert into t1 values(3,'jerry');
ERROR 1046 (3D000): No database selected
mysql> use test;
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> insert into t1 values(3,'jerry');
Query OK, 1 row affected (0.02 sec)
mysql> insert into t1 values(4,'jack');
Query OK, 1 row affected (0.07 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql> select * frmo t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'frmo t1' at line 1
mysql> use test;
Database changed
mysql> select * from t1;
+------+--------+
| Id | Name |
+------+--------+
| 2 | cherry |
| 1 | jason |
| 3 | jerry |
| 4 | jack |
+------+--------+
4 rows in set (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.09 sec)
mysql> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 666 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 713 |
| mysql-bin.000002 | 120 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> ? show binlog events
Name: 'SHOW BINLOG EVENTS'
Description:
Syntax:
SHOW BINLOG EVENTS
[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
Shows the events in the binary log. If you do not specify 'log_name',
the first binary log is displayed.
URL: http://dev.mysql.com/doc/refman/5.6/en/show-binlog-events.html
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.34-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Query | 1 | 199 | BEGIN |
| mysql-bin.000001 | 199 | Query | 1 | 305 | use `test`; insert into t1 values(3,'jerry') |
| mysql-bin.000001 | 305 | Xid | 1 | 336 | COMMIT /* xid=252 */ |
| mysql-bin.000001 | 336 | Query | 1 | 415 | BEGIN |
| mysql-bin.000001 | 415 | Query | 1 | 520 | use `test`; insert into t1 values(4,'jack') |
| mysql-bin.000001 | 520 | Xid | 1 | 551 | COMMIT /* xid=253 */ |
| mysql-bin.000001 | 551 | Query | 1 | 666 | use `test`; DROP TABLE `t1` /* generated by server */ |
| mysql-bin.000001 | 666 | Rotate | 1 | 713 | mysql-bin.000002;pos=4 |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------+
9 rows in set (0.00 sec)
mysql> exit
Bye
[mysql@mysql ~]$ ls
test_db.sql
[mysql@mysql ~]$ more test_db.sql
-- MySQL dump 10.13 Distrib 5.6.34, for Linux (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.6.34-log
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CH
ECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' *
/;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;
--
-- Current Database: `test`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTE
R SET latin1 */;
USE `test`;
--
-- Table structure for table `t1`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`Id` int(11) DEFAULT NULL,
`Name` varchar(20) DEFAULT NULL
);
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
INSERT INTO `t1` VALUES (2,'cherry');
INSERT INTO `t1` VALUES (1,'jason');
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2017-03-27 15:33:06
[mysql@mysql ~]$ grep CHANGE MASTER test_db.sql
grep: MASTER: No such file or directory
test_db.sql:-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;
[mysql@mysql ~]$ grep "CHANGE MASTER" test_db.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;
[mysql@mysql ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 7
Server version: 5.6.34-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[mysql@mysql ~]$ mysql < ./test_db.sql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[mysql@mysql ~]$ mysql -uroot -p < ./test_db.sql
Enter password:
[mysql@mysql ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 11
Server version: 5.6.34-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use test;
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>
mysql> select * from t1;
+------+--------+
| Id | Name |
+------+--------+
| 2 | cherry |
| 1 | jason |
+------+--------+
2 rows in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 713 |
| mysql-bin.000002 | 964 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.34-log, Binlog ver: 4 |
| mysql-bin.000002 | 120 | Query | 1 | 205 | drop database test |
| mysql-bin.000002 | 205 | Query | 1 | 367 | CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
| mysql-bin.000002 | 367 | Query | 1 | 525 | use `test`; CREATE TABLE `t1` (
`Id` int(11) DEFAULT NULL,
`Name` varchar(20) DEFAULT NULL
) |
| mysql-bin.000002 | 525 | Query | 1 | 604 | BEGIN |
| mysql-bin.000002 | 604 | Query | 1 | 714 | use `test`; INSERT INTO `t1` VALUES (2,'cherry') |
| mysql-bin.000002 | 714 | Xid | 1 | 745 | COMMIT /* xid=284 */ |
| mysql-bin.000002 | 745 | Query | 1 | 824 | BEGIN |
| mysql-bin.000002 | 824 | Query | 1 | 933 | use `test`; INSERT INTO `t1` VALUES (1,'jason') |
| mysql-bin.000002 | 933 | Xid | 1 | 964 | COMMIT /* xid=285 */ |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.34-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Query | 1 | 199 | BEGIN |
| mysql-bin.000001 | 199 | Query | 1 | 305 | use `test`; insert into t1 values(3,'jerry') |
| mysql-bin.000001 | 305 | Xid | 1 | 336 | COMMIT /* xid=252 */ |
| mysql-bin.000001 | 336 | Query | 1 | 415 | BEGIN |
| mysql-bin.000001 | 415 | Query | 1 | 520 | use `test`; insert into t1 values(4,'jack') |
| mysql-bin.000001 | 520 | Xid | 1 | 551 | COMMIT /* xid=253 */ |
| mysql-bin.000001 | 551 | Query | 1 | 666 | use `test`; DROP TABLE `t1` /* generated by server */ |
| mysql-bin.000001 | 666 | Rotate | 1 | 713 | mysql-bin.000002;pos=4 |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------+
9 rows in set (0.00 sec)
[mysql@mysql ~]$ cd /mysql/data/
[mysql@mysql data]$ ls
auto.cnf ib_logfile0 mysql mysql-bin.000002 mysql.err performance_schema
ibdata1 ib_logfile1 mysql-bin.000001 mysql-bin.index mysql.pid test
[mysql@mysql data]$ ls -ltr
total 110656
-rw-rw----. 1 mysql mysql 50331648 Mar 24 10:57 ib_logfile1
drwx------. 2 mysql mysql 4096 Mar 24 10:57 performance_schema
drwx------. 2 mysql mysql 4096 Mar 24 10:57 mysql
-rw-rw----. 1 mysql mysql 56 Mar 24 11:01 auto.cnf
-rw-rw----. 1 mysql mysql 6 Mar 24 15:15 mysql.pid
-rw-r-----. 1 mysql mysql 25605 Mar 24 15:15 mysql.err
-rw-rw----. 1 mysql mysql 713 Mar 27 15:36 mysql-bin.000001
-rw-rw----. 1 mysql mysql 38 Mar 27 15:36 mysql-bin.index
drwx------. 2 mysql mysql 4096 Mar 27 15:53 test
-rw-rw----. 1 mysql mysql 964 Mar 27 15:53 mysql-bin.000002
-rw-rw----. 1 mysql mysql 12582912 Mar 27 15:53 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Mar 27 15:53 ib_logfile0
[mysql@mysql data]$ mysqlbinlog --help
mysqlbinlog Ver 3.4 for Linux at x86_64
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Dumps a MySQL binary log in a format usable for viewing or for piping to
the mysql command line client.
Usage: mysqlbinlog [options] log-files
-?, --help Display this help and exit.
--base64-output=name
Determine when the output statements should be
base64-encoded BINLOG statements: 'never' disables it and
works only for binlogs without row-based events;
'decode-rows' decodes row events into commented
pseudo-SQL statements if the --verbose option is also
given; 'auto' prints base64 only when necessary (i.e.,
for row-based events and format description events). If
no --base64-output[=name] option is given at all, the
default is 'auto'.
--bind-address=name IP address to bind to.
--character-sets-dir=name
Directory for character set files.
-d, --database=name List entries for just this database (local log only).
--debug-check Check memory and open file usage at exit .
--debug-info Print some debug info at exit.
--default-auth=name Default authentication client-side plugin to use.
-D, --disable-log-bin
Disable binary log. This is useful, if you enabled
--to-last-log and are sending the output to the same
MySQL server. This way you could avoid an endless loop.
You would also like to use it when restoring after a
crash to avoid duplication of the statements you already
have. NOTE: you will need a SUPER privilege to use this
option.
-F, --force-if-open Force if binlog was not closed properly.
(Defaults to on; use --skip-force-if-open to disable.)
-f, --force-read Force reading unknown binlog events.
-H, --hexdump Augment output with hexadecimal and ASCII event dump.
-h, --host=name Get the binlog from server.
-l, --local-load=name
Prepare local temporary files for LOAD DATA INFILE in the
specified directory.
-o, --offset=# Skip the first N entries.
-p, --password[=name]
Password to connect to remote server.
--plugin-dir=name Directory for client-side plugins.
-P, --port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-R, --read-from-remote-server
Read binary logs from a MySQL server. This is an alias
for read-from-remote-master=BINLOG-DUMP-NON-GTIDS.
--read-from-remote-master=name
Read binary logs from a MySQL server through the
COM_BINLOG_DUMP or COM_BINLOG_DUMP_GTID commands by
setting the option to either BINLOG-DUMP-NON-GTIDS or
BINLOG-DUMP-GTIDS, respectively. If
--read-from-remote-master=BINLOG-DUMP-GTIDS is combined
with --exclude-gtids, transactions can be filtered out on
the master avoiding unnecessary network traffic.
--raw Requires -R. Output raw binlog data instead of SQL
statements, output is to log files.
-r, --result-file=name
Direct output to a given file. With --raw this is a
prefix for the file names.
--secure-auth Refuse client connecting to server if it uses old
(pre-4.1.1) protocol.
(Defaults to on; use --skip-secure-auth to disable.)
--server-id=# Extract only binlog entries created by the server having
the given id.
--server-id-bits=# Set number of significant bits in server-id
--set-charset=name Add 'SET NAMES character_set' to the output.
-s, --short-form Just show regular queries: no extra info and no row-based
events. This is for testing only, and should not be used
in production systems. If you want to suppress
base64-output, consider using --base64-output=never
instead.
-S, --socket=name The socket file to use for connection.
--start-datetime=name
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
-j, --start-position=#
Start reading the binlog at position N. Applies to the
first binlog passed on the command line.
--stop-datetime=name
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--stop-never Wait for more data from the server instead of stopping at
the end of the last log. Implicitly sets --to-last-log
but instead of stopping at the end of the last log it
continues to wait till the server disconnects.
--stop-never-slave-server-id=#
The slave server_id used for --read-from-remote-server
--stop-never.
--stop-position=# Stop reading the binlog at position N. Applies to the
last binlog passed on the command line.
-t, --to-last-log Requires -R. Will not stop at the end of the requested
binlog but rather continue printing until the end of the
last binlog of the MySQL server. If you send the output
to the same MySQL server, that may lead to an endless
loop.
-u, --user=name Connect to the remote server as username.
-v, --verbose Reconstruct pseudo-SQL statements out of row events. -v
-v adds comments on column data types.
-V, --version Print version and exit.
--open-files-limit=#
Used to reserve file descriptors for use by this program.
-c, --verify-binlog-checksum
Verify checksum binlog events.
--binlog-row-event-max-size=#
The maximum size of a row-based binary log event in
bytes. Rows will be grouped into events smaller than this
size if possible. This value must be a multiple of 256.
--skip-gtids Do not print Global Transaction Identifier information
(SET GTID_NEXT=... etc).
--include-gtids=name
Print events whose Global Transaction Identifiers were
provided.
--exclude-gtids=name
Print all events but those whose Global Transaction
Identifiers were provided.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
base64-output (No default value)
bind-address (No default value)
character-sets-dir (No default value)
database (No default value)
debug-check FALSE
debug-info FALSE
default-auth (No default value)
disable-log-bin FALSE
force-if-open TRUE
force-read FALSE
hexdump FALSE
host (No default value)
local-load (No default value)
offset 0
plugin-dir (No default value)
port 0
read-from-remote-server FALSE
read-from-remote-master (No default value)
raw FALSE
result-file (No default value)
secure-auth TRUE
server-id 0
server-id-bits 32
set-charset (No default value)
short-form FALSE
socket (No default value)
start-datetime (No default value)
start-position 4
stop-datetime (No default value)
stop-never FALSE
stop-never-slave-server-id -1
stop-position 18446744073709551615
to-last-log FALSE
user (No default value)
open-files-limit 64
verify-binlog-checksum FALSE
binlog-row-event-max-size 4294967040
skip-gtids FALSE
include-gtids (No default value)
exclude-gtids (No default value)
[mysql@mysql data]$ mysqlbinlog --start-position=120 --stop-position=551 -vv ./mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170327 15:30:28 server id 1 end_log_pos 120 CRC32 0x78e1ef11 Start: binlog v 4, server v 5.6.34-log created 170327 15:30:28 at startup
ROLLBACK/*!*/;
BINLOG '
lL/YWA8BAAAAdAAAAHgAAAAAAAQANS42LjM0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACUv9hYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAARHv
4Xg=
'/*!*/;
# at 120
#170327 15:35:20 server id 1 end_log_pos 199 CRC32 0xbb9e80c7 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1490600120/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
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/*!*/;
BEGIN
/*!*/;
# at 199
#170327 15:35:20 server id 1 end_log_pos 305 CRC32 0x510a5b05 Query thread_id=6 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1490600120/*!*/;
insert into t1 values(3,'jerry')
/*!*/;
# at 305
#170327 15:35:20 server id 1 end_log_pos 336 CRC32 0x473228cd Xid = 252
COMMIT/*!*/;
# at 336
#170327 15:35:32 server id 1 end_log_pos 415 CRC32 0xb6ffb799 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1490600132/*!*/;
BEGIN
/*!*/;
# at 415
#170327 15:35:32 server id 1 end_log_pos 520 CRC32 0xa6b5797a Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1490600132/*!*/;
insert into t1 values(4,'jack')
/*!*/;
# at 520
#170327 15:35:32 server id 1 end_log_pos 551 CRC32 0x5a0db63d Xid = 253
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[mysql@mysql data]$ mysqlbinlog --start-position=120 --stop-position=551 -vv ./mysql-bin.000001 | mysql -uroot -p
Enter password:
[mysql@mysql data]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 15
Server version: 5.6.34-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> use test;
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> select * from t1;
+------+--------+
| Id | Name |
+------+--------+
| 2 | cherry |
| 1 | jason |
| 3 | jerry |
| 4 | jack |
+------+--------+
4 rows in set (0.00 sec)
mysql>
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.34-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Query | 1 | 199 | BEGIN |
| mysql-bin.000001 | 199 | Query | 1 | 305 | use `test`; insert into t1 values(3,'jerry') |
| mysql-bin.000001 | 305 | Xid | 1 | 336 | COMMIT /* xid=252 */ |
| mysql-bin.000001 | 336 | Query | 1 | 415 | BEGIN |
| mysql-bin.000001 | 415 | Query | 1 | 520 | use `test`; insert into t1 values(4,'jack') |
| mysql-bin.000001 | 520 | Xid | 1 | 551 | COMMIT /* xid=253 */ |
| mysql-bin.000001 | 551 | Query | 1 | 666 | use `test`; DROP TABLE `t1` /* generated by server */ |
| mysql-bin.000001 | 666 | Rotate | 1 | 713 | mysql-bin.000002;pos=4 |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------+
9 rows in set (0.00 sec)
mysql> exit
Bye
[mysql@mysql data]$ mysqlbinlog --start-position=120 --stop-position=666 -vv ./mysql-bin.000001 | mysql -uroot -p
Enter password:
[mysql@mysql data]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 18
Server version: 5.6.34-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> use test;
Database changed
mysql> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql>