MySQL notes | 2.Build MySQL under Docker & view BinLog file

Series Article Directory

Tips: Directory of all articles
1. Understand the execution process of SQL
2. Build MySQL under Docker & view BinLog file


Preface

This article is mainly to build a MySQL link under Docker to prepare for subsequent database learning.
Overview: MySQL official BinLog document


Tip: The following is the content of this article

One, the steps to install MySQL

Step 1: Query the version of mysql under docker

docker search mysql
Insert picture description here


Step 2: Pull the official image

Without the version number, the latest one is pulled by default

docker pull mysql

Step 3: Check whether the pull is successful

docker images 
Insert picture description here


Step 4: Install mysql

docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

Parameter meaning:
--name: container name, named here as mysql
-e: configuration information, here configure the login password of the mysql root user
-p: port mapping, here to map the host port 3306 to the container port 3306
-d: Run the container in the background to ensure that the container continues to run after exiting the terminal
-v: do directory mapping, -v /usr/local/docker/mysql/conf:/etc/mysql \

Step five: connect to mysql

docker exec -it mysql bash
Insert picture description here
[email protected]:/# 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.0.19 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

Step 6: View the binlog log

  1. Understand what is binlog?
Insert picture description here

Through show variables like'%log_bin%'; we inquire whether the binlog log is turned on, and where to save the log after it has been turned on

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)

Note: You cannot open the binlog file directly through cat, otherwise garbled characters will appear

[email protected]:/var/lib/mysql# cat binlog.000009
`binq�]`x|8.0.19q�]`


**4
�?%1q�]`#���ux���]`"O��0dln��C�8�/��]`& �Estd���	�ssmTestBEGINǥ���]`8yQssmTestgeek��]`F�Q����ᅾ]`� Հ�ZȾ]`"M+
depn��80ad�Ⱦ]`��	2 �Estd���
                                  ssmTestS�ssmTest/* ApplicationName=DataGrip 2019.2.5 */ alter table geek
O�Ѿ]`��	add column_5 int null0��1Ѿ]`"M%��pn��8�
       2 �Estd���
                 ssmTest��ssmTest/* ApplicationName=DataGrip 2019.2.5 */ alter table geek drop column column_55`JiG�]`"O4��wn��C�8�p�(G�]`Wv    & �Estd���	�ssmTestBEGIN.�G�]`8�hssmTestgeek�>t7G�]`F�h����a�G�]`|][M��]`"Obֈ~n��(�8����]`N �Estd����ssmTestBEGINn
����]`8�hssmTestgeek�k�쮿]`4h�

2. Solve the garbled problem

1. Auxiliary Tools

The mysqlbinlog /usr/bin
server writes the binlog log to the binlog file in binary format. How to display its content in text format can use the mysqlbinlog command.

Execution format of mysqlbinlog

mysqlbinlog [options] log_file …

View bin-log binary file (shell mode)

mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003

————————————————
Copyright Statement: This article is the original article of the CSDN blogger "Xiao Xuanfeng", which follows the CC 4.0 BY-SA copyright agreement. Please attach the original source link and This statement.
Original link: https://blog.csdn.net/wwwdc1012/article/details/88373440

mysqlbinlog  --base64-output=DECODE-ROWS -v -v /var/lib/mysql/binlog.000009

binlog.000009 is my local file name

[email protected]:/usr/bin# mysqlbinlog  --base64-output=DECODE-ROWS -v -v /var/lib/mysql/binlog.000009
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @[email protected]@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.0.19 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=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 utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@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/*!*/;