MySQL笔记 | 2.Docker下搭建MySQL&查看BinLog文件
系列文章目录
提示:所有文章的目录
1.了解SQL的执行过程
前言
这篇文章主要是在Docker下搭建一个MySQL环节,为了后续的数据库学习做准备。
概述:MySQL官方BinLog文档
提示:以下是本篇文章正文内容
一、安装MySQL的步骤
步骤一:查询mysql在docker下的版本
docker search mysql
步骤二:拉取官方镜像
不带版本号,默认拉取最新的
docker pull mysql
步骤三:查看是否拉取成功了
docker images
步骤四:安装mysql
docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
参数含义:
–name:容器名,此处命名为mysql
-e:配置信息,此处配置mysql的root用户的登陆密码
-p:端口映射,此处映射 主机3306端口 到 容器的3306端口
-d:后台运行容器,保证在退出终端后容器继续运行
-v: 做目录映射,-v /usr/local/docker/mysql/conf:/etc/mysql \
步骤五:连接mysql
docker exec -it mysql bash
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.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
步骤六:查看binlog 日志
- 了解什么是binlog?
通过show variables like '%log_bin%';我们查询到binlog 日志是否开启,已经开启后保存日志的位置
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)
注意:不能通过cat 直接打开binlog 文件,否则会出现乱码
root@2b2ee975926a:/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?
二、解决乱码问题
1. 辅助工具
mysqlbinlog /usr/bin 服务器以二进制格式将binlog日志写入binlog文件,如何要以文本格式显示其内容,可以使用 mysqlbinlog 命令。
mysqlbinlog 的执行格式:mysqlbinlog [options] log_file ...
查看bin-log二进制文件(shell方式)
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003
mysqlbinlog --base64-output=DECODE-ROWS -v -v /var/lib/mysql/binlog.000009
binlog.000009是我本地下文件名
root@2b2ee975926a:/usr/bin# mysqlbinlog --base64-output=DECODE-ROWS -v -v /var/lib/mysql/binlog.000009
/*!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.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/*!*/;