Series of articles
Tip: Table of contents for all articles
1. Understand the SQL execution process
preface
This article is mainly about setting up a MySQL link under Docker to prepare for subsequent database learning.
Summary: MySQL official BinLog documentation
Tip: The following is the text of this article
MySQL > install MySQL
Step 1: Query the version of mysql under docker
docker search mysql
Copy the code
Without the version number, the latest is pulled by default
docker pull mysql
Copy the codeStep 3: Check whether the pull is successful
docker images
Copy the code
docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
Copy the codeParameter Meanings:
– name: indicates the container name, which is mysql
-e: configures the password of the root user of mysql
-p: indicates port mapping. Port 3306 of the host is mapped to port 3306 of the container
-d: Runs the container in the background to ensure that the container continues to run after you exit the terminal
Mapping, do directory – v: v/usr/local/docker/mysql/conf: / etc/mysql \
Step 5: Connect to mysql
docker exec -it mysql bash
Copy the code
root@2b2ee975926a:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.019. MySQL Community Server - GPL
Copyright (c) 2000.2020, 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 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
Copy the codeStep 6: View the binlog
- What is a binlog?
Show variables like ‘%log_bin%’; We have queried whether the binlog is enabled and the location of saving the log after it is enabled
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+
6 rows in set (0.20 sec)
Copy the codeNote: The binlog file cannot be opened directly through cat, otherwise garbled characters will appear
root@2b2ee975926a:/var/lib/mysql# cat binlog000009.` binq �] ` x|8.019.Q ` �]**4
�?%1Q �] ` # � � � ux � � �] ` "O � � 0 DLN � � � � 8 C / � �] ` & � Estd � � � � ssmTestBEGIN ǥ � � �] ` 8 yqssmtestgeek � �] ` F � � q � � � ᅾ] ` � Հ Ⱦ � Z] `" M+Depn � �80AD � Ⱦ] ` � �2� Estd � � � ssmTestS � ssmTest/ * ApplicationName = DataGrip 2019.2.5 * / alter tableGeek O � Ѿ] ` � �add column_5 intNull0 � �1Ѿ] '"M%�� PN ��8� � 2 �Estd�� ssmTest��ssmTest/* ApplicationName=DataGrip 2019.2.5 */ ALTER table geek drop column Column_55 ` JiG �] ` "m1 � � wn � � � C8� � � p (G) ` Wv&� Estd � � � � ssmTestBEGIN. ` � � G]8� hssmTestgeek �>T7G �] ` F h � � � � � � � a G] `|] [M � �] ` "Ob ֈ ~ n � � (8 � � � � �] ` n � Estd � � � � ssmTestBEGINn � � � �] ` 8 � hssmTestgeek � k � � 쮿] ` 4 hCopy the codeSecond, solve the problem of garbled code
1. Auxiliary tools
Mysqlbinlog /usr/bin The server writes binlog logs in binary format to binlog files. To display the contents in text format, use the mysqlbinlog command.
Mysqlbinlog [options] log_file…
Viewing bin-log binaries (shell mode)
mysqlbinlog -v –base64-output=decode-rows /var/lib/mysql/master.000003
mysqlbinlog --base64-output=DECODE-ROWS -v -v /var/lib/mysql/binlog.000009
Copy the codeBinlog.000009 is my local file name
root@2b2ee975926a:/usr/bin# mysqlbinlog --base64-output=DECODE-ROWS -v -v /var/lib/mysql/binlog000009.
/ *! 50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/ *! 50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER / *! * /;
# at 4
#210326 10:58:57 server id 1 end_log_pos 124 CRC32 0x31253f85 Start: binlog v 4, server v 8.019. created 210326 10:58:57 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/ *! * /;
# at 124
#210326 10:58:57 server id 1 end_log_pos 155 CRC32 0x977875b6 Previous-GTIDs
# [empty]
# at 155
#210326 10:59:17 server id 1 end_log_pos 234 CRC32 0x2f0213ea Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1616756357738634 immediate_commit_timestamp=1616756357738634 transaction_length=323
/ *! 50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*// *! * /;
# original_commit_timestamp=1616756357738634 (2021-03-26 10:59:17.738634 UTC)
# immediate_commit_timestamp=1616756357738634 (2021-03-26 10:59:17.738634 UTC)
/ *! 80001 SET @@session.original_commit_timestamp=1616756357738634*// *! * /;
/ *! 80014 SET @@session.original_server_version=80019*// *! * /;
/ *! 80014 SET @@session.immediate_server_version=80019*// *! * /;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/ *! * /;
# at 234
#210326 10:59:17 server id 1 end_log_pos 321 CRC32 0x1db9a5c7 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1616756357/ *! * /;
SET @@session.pseudo_thread_id=8/ *! * /;
SET @@session.foreign_key_checks=1The @@session.sql_auto_is_null=0The @@session.unique_checks=1The @@session.autocommit=1/ *! * /;
SET @@session.sql_mode=1168113696/ *! * /;
SET @@session.auto_increment_increment=1The @@session.auto_increment_offset=1/ *! * /;
/ *! \C utf8mb4 *// *! * /;
SET @@session.character_set_client=255The @@session.collation_connection=255The @@session.collation_server=255/ *! * /;
SET @@session.lc_time_names=0/ *! * /;
SET @@session.collation_database=DEFAULT/ *! * /;
/ *! 80011 SET @@session.default_collation_for_utf8mb4=255*// *! * /;
BEGIN
/ *! * /;
# at 321
#210326 10:59:17 server id 1 end_log_pos 377 CRC32 0xbbfafb04 Table_map: `ssmTest`.`geek` mapped to number 81
# at 377
#210326 10:59:17 server id 1 end_log_pos 447 CRC32 0xe1d8fd16 Update_rows: table id 81 flags: STMT_END_F
### UPDATE `ssmTest`.`geek`
### WHERE
### @1=6 /* INT meta=0 nullable=0 is_null=0 */# # # @2=7 /* INT meta=0 nullable=0 is_null=0 */# # # @3=6 /* INT meta=0 nullable=0 is_null=0 */# # # @4=7 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=6 /* INT meta=0 nullable=0 is_null=0 */# # # @2=7 /* INT meta=0 nullable=0 is_null=0 */# # # @3=6 /* INT meta=0 nullable=0 is_null=0 */# # # @4=1 /* INT meta=0 nullable=0 is_null=0 */
# at 447
#210326 10:59:17 server id 1 end_log_pos 478 CRC32 0x5abc80d5 Xid = 32
COMMIT/ *! * /;
Copy the code