Shulou(Shulou.com)06/01 Report--

The server's binary log file consists of events that describe changes to the contents of the database. The server writes these files in binary. To display this content in text format, you can use the mysqlbinlog tool. You can also use mysqlbinlog to display the contents of the relay log file written from the slave server in the replication environment because the format is the same as the binary log file format.

The syntax for using mysqlbinlog is as follows:

Usage: mysqlbinlog [options] log-files

The following command displays the contents of a binary log file named binlog.000001:

[mysql@localhost ~] $mysqlbinlog / mysqldata CRC32 0x2d9d7b4f Start CRC32 0x2d9d7b4f Start: binlog v 4, server v 5.7.26-log created 191115 15:39:01 at startup# Warning: this binlog is either in use or was not closed property. ROLBACKG 15:39:01 at startup# Warning: 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER! * /; # at 4 191115 15:39:01 end_log_pos 1 qldata CRC32 0x2d9d7b4f Start: binlog v 4, server v 5.7.26-log created 191115 at startup# Warning: 50530 5.7.26-log created @ @ SESSION.PSEUDOSLAVEMODED 1) BINLOG 'FVbOXQ8BAAAdwAAHsAABAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAHsAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAHsAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAHsAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAHsAAAAAAAAAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # at 219 191115 15:51:15 server id 1 end_log_pos 308 CRC32 0x7261eacb Query thread_id=2 exec_time=0 error_code=0use `mysql` / *! * /; SET TIMESTAMP 1573804275 tobacco set @ @ session.pseudothread readreadable license idwise @ @ session. Session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session. SET @ @ session.auto_increment_increment=1, @ @ session.autoincrement increment offsetbacks off set, @ @ session.autoincrement increments off set, @ @ session.session. Collection database databases database databases false set @ @ session.collationalization databaseFULTGULGULGULGULGULGULGULGULGULGULGAND truncate table personals # at 308 "191115 15:51:38 server id 1 end_log_pos 373 CRC32 0x6d2e39aa Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=noSET @ @ SESSION.GTID_NEXT= 'ANONYMOUSAccording to BEGINUA" / "at 373" 191115 15:51:38 server id 1 end_log_pos 454 TIMESTAMP 1573804298 # at 45 at 486 191115 15:51:38 server id 1 end_log_pos 486 CRC32 0xb746cd30 IntvarSET INSERTIMAMP1573804298 CRC32 0x0e926042 Query thread_id=2 exec_time=0 error_code=0SET insert into person (last_name,first_name,birth,death) values / *! * / # at 654 / 191115 15:51:38 server id 1 end_log_pos 736 CRC32 0xc5450308 Query thread_id=2 exec_time=0 error_code=0SET timestamp 1573804298 * # at 801 "191115 15:51:45 server id 1 end_log_pos 882 CRC32 0x51a9cd5c Query thread_id=2 exec_time=0 error_code=0SET timestamp 1573804305 stop" at 88 "at 914" 191115 15:51:45 server id 1 end_log_pos 914 CRC32 0x40a98fae IntvarSET INSERTERTIDEX 2 pound "191115 15:51:45 server id 1 end_log_pos 1082 CRC32 0x3396c40d Query thread_id=2 exec_time=0 error_code=0SET timestamp 1573804305 Insert into person (last_name,first_name,birth,death) values / *! * /; # at 1082191115 15:51:45 server id 1 end_log_pos 1164 CRC32 0xf6f6efad Query thread_id=2 exec_time=0 error_code=0SET Timestamps 1573804305Universe # at 1164191115 15:51:53 server id 1 end_log_pos 1229 CRC32 0x55b50dbe Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=noSET @ @ SESSION.GTID_NEXT= 'ANONYMOUSAccording to server id # at 1229191115 15:51:53 server id 1 end_log_pos 1310 TIMESTAMP 1573804313 # at 131 "at 1342" 191115 15:51:53 server id 1 end_log_pos 1342 CRC32 0xfad94baf IntvarSET INSERTIMTIX IDC / * of insert into person (last_name,first_name,birth,death) values ('yali','ye','1994-12-23 null) / *! * / # at 1508 "191115 15:51:53 server id 1 end_log_pos 1590 CRC32 0xbb6a2b4c Query thread_id=2 exec_time=0 error_code=0SET timestamp 1573804313" set @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER; # End of log fileholder "50003 SET completion TYPERATION"

The contents contained in the binlog.000001 binary log file are output above. For statement-based logs, the event information includes the SQL statement, the server ID where the statement was executed, the timestamp when the statement was executed, the execution time, and so on. For row-based logs, the event information indicates a change in the row rather than a SQL statement.

# at 486 / 191115 15:51:38 server id 1 end_log_pos 486 CRC32 0xb746cd30 Intvar SET INSERTERTIX IDC 1 *

On the first line, the number after at indicates the offset or start position of the event in the binary log file.

The second line indicates when the statement starts execution with a date and time. For replication, this timestamp is propagated to the secondary server. Server id is the server_ id value of the event origin server. End_log_pos indicates where the next event begins (it is the + 1 position where the current event ends). Thread_id that thread to execute this event. Exec_time is the time spent executing the event on the primary server. On the secondary server, it is the difference between the execution end time on the secondary server minus the execution start time on the primary server. This difference can be used as an indication of how long the replication process lags behind the primary server. The error_code indicates the result of the execution event. Zero means there are no errors.

The output of mysqlbinglog can be used to re-execute statements in the log file (for example, by using the mysql tool). This is useful for recovery when the server crashes.

Normally, use mysqlbinlog to read binary log files directly and apply them to the local MySQL server. It can also read binary log files from a remote server by using the-- read-from-remote-server option. To read the remote binary log file, the connection parameter option can be specified to indicate how to connect to the server. These options are-- host,--password,--port,--protocol,--socket and-- user, which are ignored unless the-- read-from-remote-server option is used.

When mysqlbinlog a large binary log file, be careful whether the file system has enough space to store the result file. To configure directories for mysqlbinlog to temporarily use storage files, use the TMPDIR environment variable.

Backing up binary log files using mysqlbinlog

By default, mysqlbinlog reads the binary log file and displays its contents in text format. This makes it easier for you to use files to check for events that occurred and re-execute them (for example, by using output as input to mysql). Mysqlbinlog can read log files directly from the local file system or use the-- read-from-remote-server option to connect to the remote server and read the binary log files from the remote server. Mysqlbinlog outputs the content to standard output in text format, or if the-- result-file=file_name option is specified, the content is written to the file.

Mysqlbinlog can read the binary log file and write its contents to the new file in binary format instead of text format. This capability allows you to back up binary log files in the original format. Mysqlbinlog can generate static backups that stop when backing up a set of log files when the last file is backed up. It can also generate a continuous (live) backup that maintains a connection to the server when backing up to the last log file and continues to copy new events when new events are generated. During a continuous backup operation, the mysqlbinlog runs until the connection is broken (for example, the server exits) or the mysqlbinlog is forcibly interrupted. When the connection is broken, mysqlbinlog does not wait and reconnect, unlike a secondary replication server. In order to continue a live backup after the server is restarted, mysqlbinlog must be restarted.

Binary log file backup requires at least two options when calling mysqlbinlog:

.-- read-from-remote-server (or-R) option to tell mysqlbinlog to connect to a server and read its binary log files (this is similar to a secondary replication server connecting to its primary server).

The raw option tells mysqlbinlog to output in raw (binary) format instead of text format.

Other options are usually specified along with-- read-from-remote-server:-- host indicates where the server is running, and you may need to specify connection options-- user and password.

Several other options used in conjunction with-- raw:.-- stop-never: maintain a connection to the server after reading the last log file and continue to read new events.

.-- stop-never-slave-server-id=id: server ID reported by mysqlbinlog when-- stop-never is used, default value 65535. This avoids ID conflicts with secondary servers or other mysqlbinlog processes.

.-- result-file: the prefix of the output file name

In order to use mysqlbinlog to back up the server's binary log files, you must specify the file name that actually exists on the server. If you don't know the file name, connect to the server and use the show binary logs statement to view the current log file name.

Mysql > show binary logs;+-+-+ | Log_name | File_size | +-+-+ | binlog.000001 | 2530 | +-+-+ 1 row in set (sec)

Using this information, you can use mysqlbinlog to back up binary log files to the current directory:

To make a static backup of the binlog.000130 to binlog.000132 log files, use the following command:

Mysqlbinlog-read-from-remote-server-host=host_name-raw

Binlog.000130 binlog.000131 binlog.000132

Mysqlbinlog-read-from-remote-server-host=host_name-raw

-- to-last-log binlog.000130

The first command explicitly specifies each file name. The second only specifies the first log file and uses-- to-last-log to read the last log file. The difference between these commands is that if the server opens the binlog.000133 file before mysqlbinlog reaches the end of binlog.000132, the first command will not read it, but the second command will.

For live backup mysqlbinlog starts backing up the existing log files from binlog.000130 and then maintains a connection to the server to copy the new events generated:

Mysqlbinlog-read-from-remote-server-host=host_name-raw

-- stop-never binlog.000130

Using the-- stop-never option, you do not need to specify-- to-last-log to read the final log file because this option is implicit

Output file name

When the-- raw option is not used, mysqlbinlog generates output in text format, and if you specify the-- result-file option, specifies that all output is written to a file. When using the-- raw option, mysqlbinlog converts each log file of the server into a binary output file. By default, mysqlbinlog generates a file with the same name as the source log file in the current directory. To change the output file name, use the-- result-file option. Used in conjunction with-- raw, the-- result-file option value will be prefixed to name the output file name.

Now back up the binlog.000001 log files on the remote server

[mysql@localhost] $mysqlbinlog-- read-from-remote-server-- host=192.168.1.250-- raw binlog.000001-- result-file=jy_ [mysql@localhost ~] $ls-lrt-rw-r-. 1 mysql mysql 2530 November 22 10:24 jy_binlog.000001

You can see that the backup log file is prefixed with jy_, and its file name is jy_binlog.000001

Perform backup and restore operations using mysqldump and mysqlbinlog

The following is a simple example of how to use mysqldump with mysqlbinlog to back up MySQL server data and binary log files, and how to use backup to restore data if data is lost.

Now the first binary log file of the MySQL server on the host is binlog.000001

Mysql > show binary logs;+-+-+ | Log_name | File_size | +-+-+ | binlog.000001 | 2530 | +-+-+ 1 row in set (0.01 sec)

Use mysqlbinlog to perform continuous backups of binary log files:

[mysql@localhost] $mysqlbinlog-- read-from-remote-server-- host=192.168.1.250-- raw-- stop-never binlog.000001 [mysql@localhost ~] $ls-lrt-rw-r-. 1 mysql mysql 2530 November 22 10:38 binlog.000001

Create a test table named t and insert three rows of records

Mysql > select * from TwitterCopa + | id | name | date | +-+ | 1 | jingyong | NULL | | 2 | yeyali | NULL | | 3 | huangyan | NULL | +-+ 3 rows in set (0.00 sec)

Use mysqldump to create a dump file as a data snapshot of the MySQL server. Use-- all-databases,--events and-- routines to back up all data, and-- master-data=2 is used to indicate that the current binary log file is included in the dump file.

[mysql@localhost] $mysqldump-- host=192.168.1.250-- port=3306-uroot-pabcd-- all-databases-- events-- routines-- master-data=2 > dump_mysql.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure. [mysql@localhost ~] $ls-lrt-rw-r--r--. 1 mysql mysql 3290497 November 22 10:51 dump_mysql.sql

Now delete the table t from the mysql library

Mysql > drop table tten query OK, 0 rows affected (0.18 sec) mysql > desc tten error 1146 (42S02): Table 'mysql.t' doesn't exist

Now assuming that table t in the mysql library is missing, use the most recent dump file to restore the data:

[mysql@localhost] $mysql-- host=192.168.1.250-- port=3306-uroot-pabcd mysql

After restoring the data, the mysql.t table is restored.

Mysql > desc t +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (11) | NO | | NULL | name | varchar (20) | NO | | NULL | | date | date | YES | | NULL | | +-+-+ 3 rows in set (0.01sec) mysql > select * from t +-1 | jingyong | NULL | 2 | yeyali | NULL | 3 | huangyan | NULL | +-+ 3 rows in set (0.00 sec)

Now insert a record into the mysql.t table and delete it, and then use the backed-up binary log file to re-execute the event to restore the record

Mysql > insert into t value; Query OK, 1 row affected (0.03 sec) mysql > select * from t +-+ | id | name | date | +-- + | 1 | jingyong | NULL | | 2 | yeyali | NULL | 3 | huangyan | NULL | | 4 | wenyao | NULL | +-+ 4 rows in set (0.00 sec) mysql > delete from t where id=4 Query OK, 1 row affected (0.13 sec) mysql > select * from t | id | name | date | +-- + | 1 | jingyong | NULL | | 2 | yeyali | NULL | 3 | huangyan | NULL | +-+ 3 rows in set (0.01 sec)

We need to find out where to insert the beginning and end of this record in the log file

# at 3306211 / 191122 11:04:34 server id 1 end_log_pos 3306323 CRC32 0x88f89864 Query thread_id=11 exec_time=0 error_code=0SET timestamp 1574391874 * * / # at 3306323 191122 11:04:34 server id 1 end_log_pos 3306354 CRC32 0x966500de Xid = 1041COMIT # at 3306354191122 11:07:26 server id 1 end_log_pos 3306419 CRC32 0x1f3e6e28 Anonymous_GTID last_committed=160 sequence_number=161 rbr_only=noSET @ @ SESSION.GTID_NEXT= 'ANONYMOUSAccording to at 3306419 191122 11:07:26 server id 1 end_log_pos 3306500 timestamp 1574392046 # at 3306500 million 191122 11:07:26 server id 1 end_log_pos 3306600 CRC32 0xecae0a57 Query thread_id=11 exec_time=0 error_code=0SET timestamp 1574392046According to the deletion from t where id=4

From the contents of the log file above, you can see that the insertion starts at 3306211 and ends at 3306323.

The binary log file that is currently backed up is called binlog.000001, and the command to re-execute the event is as follows:

[mysql@localhost] $mysqlbinlog-- start-position=3306211-- stop-position=3306323 binlog.000001 | mysql-- host=192.168.1.250-- port=3306-uroot-pabcd mysqlmysql: [Warning] Using a password on the command line interface can be insecure.

Now check the record in the mysql.t table and you can see that the deleted record has been restored.

Mysql > select * from TwitterCola + | id | name | date | +-+ | 1 | jingyong | NULL | | 2 | yeyali | NULL | | 3 | huangyan | NULL | 4 | wenyao | NULL | +-+ 4 rows in set (0.00 sec)

Set up msyqlbinlog server ID

When you call mysqlbinlog with the-- read-from-remote-server option, mysqlbinlog connects to a MySQL server, specifies a server ID to identify it, and fetches the required binary log files from that server. You can use mysqlbinlog to get log files from the server in the following ways:

. Specify an explicit name for the fileset. For each file, mysqlbinlog performs the join operation and executes the binlog dump command. The server sends the file and disconnects. Each file has a connection.

. Specify the start file and-- to-last-log option, and mysqlbinlog executes the connection and binlog dump all log files. The server sends all log files and disconnects

. Specify the start file and the-- stop-never option (the ability to implicitly implement the-- to-last-log option), and mysqlbinlog performs the connection and executes the binlog dump command on all log files. The server sends all log files, but does not disconnect from the server after the last log file is sent.

Only when using the-- read-from-remote-server option, mysqlbinlog uses a server ID of 0 to connect, which tells the server to disconnect after sending the requested log file.

When using the-- read-from-remote-server and-- stop-never options, mysqlbinlog uses a non-zero server ID to connect, so the server does not disconnect after the last log file is sent. The default server ID is 65535, but this can be modified by using the-- stop-never-slave-server-id option.

Therefore, for those who use the first two methods to obtain log files, all servers will be disconnected because the server ID specified by mysqlbinlog is 0, and if the-- stop-never option is specified, because mysqlbinlog specifies a non-0 server ID, the server will not be disconnected.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.