MySQL

shell

# 官方 mysql:5.7 只有 linux/amd64 镜像
docker pull mysql:5.7
# bitnami 提供 mysql:5.7 linux/arm64 镜像
docker pull bitnami/mysql:5.7

docker run -d \
 --name mysql \
 -v $PWD:/bitnami/mysql/data \
 -e ALLOW_EMPTY_PASSWORD=yes \
 -e MYSQL_ROOT_PASSWORD=ppw \
 -p 3306:3306 \
 bitnami/mysql:5.7

# 进入容器
docker exec -it mysql bash

主从配置

主库

shell

# 从容器复制文件到宿主机
docker cp mysql:/opt/bitnami/mysql/conf/my.cnf master_my.cnf
# 从宿主机复制文件到容器
docker cp master_my.cnf mysql:/opt/bitnami/mysql/conf/my.cnf

添加如下配置：

shell

[mysqld]
server_id=1
read_only=0
log_bin=mysql-bin

server_id=1 唯一标识
read_only=0 允许执行读写操作
log_bin 配置 binlog 文件路径

修改后重启 mysqld。

sql

#
master.sql

-- --------------------------------------
-- 查询 server_id、read_only
-- --------------------------------------
SHOW VARIABLES LIKE 'server_id';
SHOW
VARIABLES LIKE 'read_only';

-- --------------------------------------
-- 新建主从复制用户
-- --------------------------------------
CREATE
USER 'follower'@'%' IDENTIFIED BY "pass_123";
-- ALTER USER 'follower'@'192.168.30.%' IDENTIFIED BY 'new_password';
-- FLUSH PRIVILEGES;

-- 指定认证方式为 mysql_native_password
-- CREATE USER 'follower'@'192.168.30.%' IDENTIFIED WITH mysql_native_password BY "pass_123";

-- --------------------------------------
-- 授予用户权限；*.* 代表所有库所有表
-- --------------------------------------
GRANT REPLICATION
SLAVE ON *.* TO 'follower'@'%';

-- --------------------------------------
-- 查询用户列表
-- --------------------------------------
SELECT User, Host
FROM mysql.user;

-- --------------------------------------
-- 查询特定用户的权限
-- --------------------------------------
SHOW
GRANTS FOR 'follower'@'%';

-- --------------------------------------
-- 查看主库状态；获取二进制日志位置，file 代表当前日志文件名，position 代表当前日志文件位置
-- --------------------------------------
SHOW
MASTER STATUS;

-- --------------------------------------
-- 查看当前连接状态；配置好从库后就可以看到
-- --------------------------------------
SHOW
PROCESSLIST;

从库

shell

# 从容器复制文件到宿主机
docker cp mysql:/opt/bitnami/mysql/conf/my.cnf slave_my.cnf
# 从宿主机复制文件到容器
docker cp slave_my.cnf mysql:/opt/bitnami/mysql/conf/my.cnf

添加如下配置：

shell

[mysqld]
server_id=10001
read_only=1

read_only=1 允许执行读操作

sql

#
slave.sql

-- --------------------------------------
-- 查询 server_id、read_only
-- --------------------------------------
SHOW VARIABLES LIKE 'server_id';
SHOW
VARIABLES LIKE 'read_only';

-- --------------------------------------
-- 初始化；MASTER_LOG_FILE、MASTER_LOG_POS 这两项要与主节点的 SHOW MASTER STATUS 命令内容保持一致
-- --------------------------------------
CHANGE
MASTER TO
    MASTER_HOST ='192.168.30.202',
    MASTER_USER ='follower',
    MASTER_PASSWORD ='pass_123',
    MASTER_PORT =3306,
    MASTER_LOG_FILE ='mysql-bin.000002',
    MASTER_LOG_POS =595;

-- --------------------------------------
-- 启动从节点同步
-- --------------------------------------
START
SLAVE;
-- STOP SLAVE;
-- RESET SLAVE;

-- --------------------------------------
-- 查看从节点同步状态
-- --------------------------------------
SHOW
SLAVE STATUS;
-- SHOW SLAVE STATUS\G; # 这可以转化成按行展示
-- Slave_IO_Running Yes
-- Slave_SQL_Running Yes
-- 基本就没问题了
-- Seconds_Behind_Master 表示从节点当前处理的二进制日志事件与主节点最新生成的二进制日志事件之间的时间差，以秒为单位。

遇到的问题

SHOW SLAVE STATUS; 查看 Slave_IO_Running 状态为 Connecting。

docker 环境下，设置 CREATE USER 'follower'@'192.168.30.%' IDENTIFIED BY "pass_123"; 192.168.30.% 会导致链接失败。
> SELECT USER(); 查看当前用户命令可以观察到当前用户的主机地址，比如我的是 root@172.17.0.1，因此 'follower'@' 172.17.0.%' 就可以了。

数据库同步失败

如果某条数据存在，但是之前字段值不同，可以正常同步。
某条数据不存在，会同步失败，并且整个同步任务停止。

[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000002' position 1553658.

SHOW SLAVE STATUS; 查看 Last_Error 字段输出的内容：

Could not execute Update_rows event on table qxy_admin.qxy_admin; Can't find record in 'qxy_admin', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000002, end_log_pos 1553989

这里描述了失败的库表。

shell

# 主库导出数据；注意这个命令导出的SQL脚本会 _删除_ 目标库表数据
mysqldump -u root -p --databases qxy_admin --tables qxy_admin > qxy_admin.sql
# 从库导入数据
mysql -uroot -p qxy_admin < qxy_admin.sql

之后从库执行START SLAVE;命令就能继续同步了。

半同步复制

MySQL 的半同步复制模式通过确保主库在提交事务前至少有一个从库确认接收到并记录了二进制日志，提高了数据的一致性和可靠性。这对于需要高可用性和数据完整性的应用场景非常有用。虽然可能会引入一定的延迟，但这种延迟通常是可以接受的，特别是在关键业务场景中。

主库配置:

shell

[mysqld]
server_id=1
read_only=0
log_bin=mysql-bin
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=10000

sql

INSTALL
PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-- UNINSTALL PLUGIN rpl_semi_sync_master;
SHOW
GLOBAL VARIABLES LIKE 'rpl%';
SHOW
GLOBAL STATUS LIKE 'Rpl%';
-- Rpl_semi_sync_master_status ON 表示启用半同步

从库配置:

shell

[mysqld]
server_id=10001
read_only=1
relay-log=mysql-relay-bin
rpl_semi_sync_slave_enabled=1

sql

INSTALL
PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
-- UNINSTALL PLUGIN rpl_semi_sync_slave;
SHOW
GLOBAL VARIABLES LIKE 'rpl%';
SHOW
GLOBAL STATUS LIKE 'Rpl%';

要是配置完成没有同步，重新设置一次同步试试：

sql

-- 在主库执行
SHOW
MASTER STATUS;
-- 在从库执行
STOP
SLAVE;
-- 注意修改 MASTER_LOG_FILE 和 MASTER_LOG_POS 为主库的输出
CHANGE
MASTER TO
    MASTER_HOST ='192.168.30.202',
    MASTER_USER ='follower',
    MASTER_PASSWORD ='pass_123',
    MASTER_PORT =3306,
    MASTER_LOG_FILE ='mysql-bin.000010',
    MASTER_LOG_POS =154;
START
SLAVE;

基本操作

设置命令提示符

shell

mysql> prompt \D

一些命令

sql

-- 查看数据库版本
SELECT VERSION();

-- 查看当前时间
SELECT NOW();

-- 查看当前用户
SELECT USER();

-- 查询物理存储位置
SHOW
GLOBAL VARIABLES LIKE "%datadir%";

数据库操作

sql

-- 创建数据库
CREATE
DATABASE db_test;

-- 删除数据库
DROP
DATABASE db_test;

-- 查看数据库
SHOW
DATABASES;

-- 选择数据库
USE
db_test;

-- 创建表
CREATE TABLE user
(
  user_id     BIGINT(64) AUTO_INCREMENT NOT NULL COMMENT '主键id',
  account     VARCHAR(255) DEFAULT '' NOT NULL COMMENT '账号',
  name        VARCHAR(255) DEFAULT '' NOT NULL COMMENT '名字',
  password    VARCHAR(60)  DEFAULT '' NOT NULL COMMENT '密码',
  create_time BIGINT(20)   DEFAULT 0    NOT NULL COMMENT '创建时间',
  update_time BIGINT(20)   DEFAULT 0    NOT NULL COMMENT '更新时间',
  delete_time BIGINT(20)   DEFAULT 0    NOT NULL COMMENT '删除时间',
  PRIMARY KEY (user_id)
) ENGINE = innodb
    DEFAULT CHARSET = utf8mb4
    AUTO_INCREMENT = 100
    COMMENT ='用户表';

-- 查看表结构
DESC USER;

-- 查看表创建语句
SHOW
CREATE TABLE user;
-- SHOW CREATE TABLE user \G;

-- 查看表
SHOW
TABLES;

-- 查看表结构；显示表名、数据文件、索引文件、数据大小、索引大小等；可以看表的备注
SHOW
TABLE STATUS;

-- 删除表
DROP TABLE user;

-- 添加字段
ALTER TABLE user
  ADD COLUMN age BIGINT(3) DEFAULT 0 NOT NULL COMMENT '年龄';

-- 修改字段
ALTER TABLE user
  CHANGE age age BIGINT(4) DEFAULT 0 NOT NULL COMMENT '年龄';

-- 在最前面插入一列
ALTER TABLE user
  ADD COLUMN sex INT(1) DEFAULT 0 NOT NULL COMMENT '性别' first;

-- 在某一列后插入一列
ALTER TABLE user
  add COLUMN birthday BIGINT(20) DEFAULT 0 NOT NULL COMMENT '生日' after sex;

-- 重命名表
ALTER TABLE user RENAME user_base;

-- 删除表字段
ALTER TABLE user_base
DROP
COLUMN age;

-- 插入数据
INSERT INTO user_base (name)
VALUES ('abc1'),
       ('abc2');

-- 查询
SELECT *
FROM user_base;

SELECT name
FROM user_base
WHERE sex = 0;

-- 更新
UPDATE user_base
SET sex=1
WHERE name = 'abc';

-- 删除
DELETE
FROM user_base
WHERE sex = 0;

-- JSON 字段
ALTER TABLE user_base
  ADD COLUMN video_info JSON NOT NULL COMMENT '视频流信息';

UPDATE user_base
SET video_info='{"video_id":100,"video_name":"video_name","video_url":"video_url"}'
WHERE user_id = 100;

SELECT *
FROM user_base
WHERE video_info - > '$.video_id' = 100;

UPDATE user_base
SET video_info='[{"video_id":100,"video_name":"video_name","video_url":"video_url"},{"video_id":101,"video_name":"video_name","video_url":"video_url"}]'
WHERE user_id = 100;

SELECT *
FROM user_base
WHERE json_contains(video_info - > '$[*].video_id', '100', '$');

SELECT *
FROM user_base
WHERE json_contains(video_info - > '$[*].video_name', '"video_name"', '$');

SELECT *
FROM user_base
WHERE json_contains(video_info, json_object('video_id', 100));

SELECT *
FROM user_base
WHERE json_contains(video_info, json_object('video_name', 'video_name'));

-- 忽略大小写的查询
SELECT *
FROM user_base
WHERE name LIKE "%名字%" COLLATE utf8mb4_general_ci;

性能分析

shell

mysql> EXPLAIN SELECT * FROM user_base \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_base
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

启用 MySQL 的查询性能分析功能。

shell

mysql> SHOW VARIABLES LIKE '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.01 sec)

mysql> SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW VARIABLES LIKE '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

mysql> SHOW PROFILE;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000086 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000016 |
| init                 | 0.000037 |
| System lock          | 0.000005 |
| optimizing           | 0.000001 |
| optimizing           | 0.000002 |
| statistics           | 0.000009 |
| preparing            | 0.000008 |
| statistics           | 0.000003 |
| preparing            | 0.000002 |
| executing            | 0.000004 |
| Sending data         | 0.000004 |
| executing            | 0.000001 |
| Sending data         | 0.000379 |
| end                  | 0.000002 |
| query end            | 0.000003 |
| closing tables       | 0.000001 |
| removing tmp table   | 0.000003 |
| closing tables       | 0.000003 |
| freeing items        | 0.000022 |
| cleaning up          | 0.000010 |
+----------------------+----------+
22 rows in set, 1 warning (0.00 sec)

TABLE OF CONTENTS

MySQL

主从配置

主库

从库

遇到的问题

数据库同步失败

半同步复制

基本操作

设置命令提示符

一些命令

数据库操作

性能分析

TABLE OF CONTENTS

MySQL

主从配置

主库

从库

遇到的问题

数据库同步失败

半同步复制

基本操作

设置命令提示符

一些命令

数据库操作

性能分析