抱歉，您的浏览器无法访问本站
本页面需要浏览器支持（启用）JavaScript
了解详情 >

MySQL实战手册

数据库

发布于：2022年4月15日

字数：2.9k 字

时长：13 分钟

部署MySQL

提供MySQL部署参考，以下使用docker来部署MySQL。

单实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
# 拉取镜像
docker pull mysql:5.7

# 建立目录映射
mkdir -p /raid/mysql
mkdir /raid/mysql/conf/ /raid/mysql/logs/ mkdir /raid/mysql/data/

# 配置mysql
cat > /raid/mysql/conf/my.cnf <<EOF
[mysqld]
user=mysql
character-set-server=utf8mb4
collation-server = utf8mb4_unicode_ci
default_authentication_plugin=mysql_native_password
explicit_defaults_for_timestamp=true
expire_logs_days=7
log_error=/log/mysql/mysql_log_error
max_connections=1000
secure_file_priv=''
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4
EOF

# [可选] 创建自定义docker网络，然后docker run 加上参数--net mynetwork --ip 172.15.0.122
docker network create --subnet=172.15.0.0/16 mynetwork

# 启动mysql
docker run --restart=always \
-v /raid/mysql/data/:/var/lib/mysql \
-v /raid/mysql/logs/:/var/log/mysql \
-v /raid/mysql/conf/:/etc/mysql \
-p 3306:3306 --name mysql5.7 \
-e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

启动后，进入容器：

1
2
3
4
docker exec -it mini-mysql bash
# exec:
# 以root身份登录
mysql -u root -p
1
2
3
4
5
6
7
8
9
10
11
12
13
# 创建数据库test
create database test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 创建帐号：CREATE USER 'username'@'host' IDENTIFIED BY 'password';
# host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
create user 'ashin'@'%' identified by 'ashin123';

# 授权：GRANT privileges ON databasename.tablename TO 'username'@'host'
# privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
# tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

grant all on test.* to 'ashin'@'%';
flush privileges;

主从复制

准备主从配置，这里将配置分别放在/raid/mysql/conf/master/my.cnf和/raid/mysql/conf/slave/my.cnf：

/raid/mysql/conf/master/my.cnf:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[mysqld]
user=mysql
character-set-server=utf8mb4
collation-server = utf8mb4_unicode_ci
default_authentication_plugin=mysql_native_password
explicit_defaults_for_timestamp=true
expire_logs_days=7
log_error=/log/mysql/mysql_log_error
max_connections=1000
secure_file_priv=''
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER
# 同集群内要唯一, 主从需要开启binlog
server-id=10
log-bin=mysql-bin
# 不用给Slave同步的数据库，一般是Mysql自带的数据库就不用给Slave同步了
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

/raid/mysql/conf/slave/my.cnf配置把server-id改一下就可以了。

然后启动master实例：

1
2
3
4
5
6
docker run --restart=always \
-v /raid/mysql/data/master:/var/lib/mysql \
-v /raid/mysql/logs/master:/var/log/mysql \
-v /raid/mysql/conf/master:/etc/mysql \
-p 3306:3306 --name mysql-master \
-e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

docker exec 进入容器，登录进mysql

1
2
# 查看master信息
SHOW MASTER STATUS\G

启动slave实例：

1
2
3
4
5
6
docker run --restart=always \
-v /raid/mysql/data/slave:/var/lib/mysql \
-v /raid/mysql/logs/slave:/var/log/mysql \
-v /raid/mysql/conf/slave:/etc/mysql \
-p 3307:3306 --name mysql-slave \
-e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

注意：如果在同一个主机操作，挂载的主机目录需要与master区分开。

在master创建主从复制帐号：

1
2
create user repl@'192.168.1.10' IDENTIFIED BY 'repl123';
grant replication slave on *.* to repl@'192.168.1.10';

在slave开启从服务：

1
2
3
4
5
6
7
8
9
# 变更master
CHANGE MASTER TO MASTER_HOST='192.168.1.10',MASTER_USER='repl',MASTER_PASSWORD='repl123',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=154;

# 开启slave
START SLAVE;

# 查看slave状态
SHOW SLAVE STATUS\G

MGR集群

MySQL Group Replication（MGR）是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案，以插件形式提供，实现了分布式下数据的最终一致性，总结MGR特点如下：

  • 高一致性：基于分布式paxos协议实现组复制，保证数据一致性；
  • 高容错性：自动检测机制，只要不是大多数节点都宕机就可以继续工作，内置防脑裂保护机制；
  • 高扩展性：节点的增加与移除会自动更新组成员信息，新节点加入后，自动从其他节点同步增量数据，直到与其他节点数据一致；
  • 高灵活性：提供单主模式和多主模式，单主模式在主库宕机后能够自动选主，所有写入都在主节点进行，多主模式支持多节点写入。

以部署MySQL 8.0.25为例：

部署三个节点的MySQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
# 拉取镜像
docker pull mysql:8.0.25
# 创建专用网络
docker network create --subnet=172.25.0.0/24 mysql-mgr-network

# 创建目录存储数据
mkdir -p /usr/local/mysql/mgr61/conf.d
mkdir -p /usr/local/mysql/mgr61/data
mkdir -p /usr/local/mysql/mgr62/conf.d
mkdir -p /usr/local/mysql/mgr62/data
mkdir -p /usr/local/mysql/mgr63/conf.d
mkdir -p /usr/local/mysql/mgr63/data

# 修改配置
cat > /usr/local/mysql/mgr61/conf.d/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id= 802533061
default-time-zone= '+8:00'
log_timestamps= SYSTEM
log-bin = 
binlog_format=row
binlog_checksum=NONE
log-slave-updates=1
skip-name-resolve
auto-increment-increment=2
auto-increment-offset=1
gtid-mode=ON
enforce-gtid-consistency=on
default_authentication_plugin=mysql_native_password
max_allowed_packet=500M

relay_log=mgr61-relay-bin-ip61
transaction_isolation=READ-COMMITTED

plugin_load_add='group_replication.so'

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="172.25.0.61:33061"
loose-group_replication_group_seeds="172.25.0.61:33061,172.25.0.62:33062,172.25.0.63:33063"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_allowlist="172.25.0.61,172.25.0.62,172.25.0.63"

report_host=172.25.0.61
report_port=3306

EOF

###################
##### 配置说明 
# server-id                                   : 每个实例必须是唯一的
# binlog_format                               : binlog格式,MGR要求必须是ROW
# binlog_checksum                             : binlog校验规则，MGR要求使用NONE
# gtid_mode                                   : 开启GTID（MySQL5.6新特性）,必须开启
# plugin_load_add                             : 启动加载组复制插件
# transaction_isolation                       : MGR使用乐观锁,所以官网建议隔离级别是RC,减少锁粒度
# transaction_write_set_extraction            : 记录事务的算法,官网建议设置使用 XXHASH64 算法
# loose-group_replication_group_name          : 用来区分同个网段里边的各个不同的GROUP，是UUID值
# loose-group_replication_start_on_boot       : 是否随服务器启动而自动启动组复制,不建议直接启动,可能降低故障恢复时的数据准确性
# loose-group_replication_local_address       : 本地MGR的IP地址和端口，host:port,是MGR的端口,不是数据库的端口
# loose-group_replication_group_seeds         : 需要接受本MGR实例控制的服务器IP地址和端口,是MGR的端口,不是数据库的端口
# loose-group_replication_bootstrap_group     : 开启引导模式,添加组成员，用于第一次搭建MGR或重建MGR的时候使用,只需要在集群内的其中一台开启
# loose-group_replication_ip_allowlist        : mgr需要帮各个节点加入白名单，否则集群不成功
# loose-group_replication_single_primary_mode : 是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式

##### 注意事项
# 每个实例的server-id, loose-group_replication_local_address, report_host, relay_log不能设置重复，保证唯一性。
# 请根据实际情况修改配置
###################

# 创建3个节点的MySQL
docker run -d --name mysql8025mgr33061 \
   -h mgr61 -p 33061:3306 --net=mysql-mgr-network --ip 172.25.0.61 \
   -v /usr/local/mysql/mgr61/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/mgr61/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=mgr4root \
   -e TZ=Asia/Shanghai \
   mysql:8.0.25

docker run -d --name mysql8025mgr33062 \
   -h mgr62 -p 33062:3306 --net=mysql-mgr-network --ip 172.25.0.62 \
   -v /usr/local/mysql/mgr62/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/mgr62/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=mgr4root \
   -e TZ=Asia/Shanghai \
   mysql:8.0.25

docker run -d --name mysql8025mgr33063 \
   -h mgr63 -p 33063:3306 --net=mysql-mgr-network --ip 172.25.0.63 \
   -v /usr/local/mysql/mgr63/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/mgr63/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=mgr4root \
   -e TZ=Asia/Shanghai \
   mysql:8.0.25

检查部署情况：

1
2
3
4
5
# docker ps   容器列表
# docker logs 查看日志
# docker exec 进入容器
# 登录mysql
select @@hostname,@@server_id,@@server_uuid;

安装MGR插件（所有节点执行）：

5.7.17版本自带MGR插件，需要安装才能使用，若配置文件中已加载插件，可跳过。

1
2
3
4

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

show plugins;

设置复制帐号（所有节点执行）：

1
2
3
4
5
6
7
SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY 'mgr4repl';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE REPLICATION SOURCE TO SOURCE_USER='repl', SOURCE_PASSWORD='mgr4repl' FOR CHANNEL 'group_replication_recovery';

MGR单主模式

启动MGR, 在主库执行，用来开启组复制：

1
2
3
4
5
6
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

-- 查看MGR组信息 
SELECT * FROM performance_schema.replication_group_members;

其他节点加入MGR，在从库执行：

1
2
3
4
SET GLOBAL group_replication_allow_local_lower_version_join=ON
START GROUP_REPLICATION;
-- 查看MGR组信息
SELECT * FROM performance_schema.replication_group_members;

开启MGR前：

1
2
3
4
5
6
7
8
9

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier |           |             |        NULL | OFFLINE      |             |                |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.09 sec)

开启MGR后：

1
2
3
4
5
6
7
8
9
10
11

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 09656c23-19be-11ec-86ad-0242ac19003d | 172.25.0.61 |        3306 | ONLINE       | PRIMARY     | 8.0.25         |
| group_replication_applier | 7859bb72-19be-11ec-9047-0242ac19003e | 172.25.0.62 |        3306 | ONLINE       | SECONDARY   | 8.0.25         |
| group_replication_applier | 7e97658c-19be-11ec-b56e-0242ac19003f | 172.25.0.63 |        3306 | ONLINE       | SECONDARY   | 8.0.25         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.02 sec)

QA：

加入出现下面错误：

MGR多主模式

查看当前mgr模式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show variables like '%group_replication_single_primary_mode%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+
1 row in set (0.41 sec)

mysql> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.00 sec)

ps: 参数group_replication_single_primary_mode=ON，表示单主模式。

函数切换

从MySQL 8.0.13开始，可以使用函数进行在线修改MGR模式，注意用到的是以下两个函数：

1
2
3
4
-- 单主切多主
select group_replication_switch_to_multi_primary_mode(); 
-- 多主切单主，入参需要传入主库的server_uuid
select group_replication_switch_to_single_primary_mode('@@server_uuid') ;

单主切多主（任意一个节点执行即可）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select group_replication_switch_to_multi_primary_mode();
+--------------------------------------------------+
| group_replication_switch_to_multi_primary_mode() |
+--------------------------------------------------+
| Mode switched to multi-primary successfully.     |
+--------------------------------------------------+
1 row in set (1.30 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 09656c23-19be-11ec-86ad-0242ac19003d | 172.25.0.61 |        3306 | ONLINE       | PRIMARY     | 8.0.25         |
| group_replication_applier | 7859bb72-19be-11ec-9047-0242ac19003e | 172.25.0.62 |        3306 | ONLINE       | PRIMARY     | 8.0.25         |
| group_replication_applier | 7e97658c-19be-11ec-b56e-0242ac19003f | 172.25.0.63 |        3306 | ONLINE       | PRIMARY     | 8.0.25         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

多主切多主（任意一个节点执行即可）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select group_replication_switch_to_single_primary_mode('7859bb72-19be-11ec-9047-0242ac19003e') ;
+-----------------------------------------------------------------------------------------+
| group_replication_switch_to_single_primary_mode('7859bb72-19be-11ec-9047-0242ac19003e') |
+-----------------------------------------------------------------------------------------+
| Mode switched to single-primary successfully.                                           |
+-----------------------------------------------------------------------------------------+
1 row in set (1.20 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 09656c23-19be-11ec-86ad-0242ac19003d | 172.25.0.61 |        3306 | ONLINE       | SECONDARY   | 8.0.25         |
| group_replication_applier | 7859bb72-19be-11ec-9047-0242ac19003e | 172.25.0.62 |        3306 | ONLINE       | PRIMARY     | 8.0.25         |
| group_replication_applier | 7e97658c-19be-11ec-b56e-0242ac19003f | 172.25.0.63 |        3306 | ONLINE       | SECONDARY   | 8.0.25         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.00 sec)
手动切换

MGR切换模式需要重新启动组复制，因些需要在所有节点上先关闭组复制，设置 group_replication_single_primary_mode=OFF 等参数，再启动组复制。

单主切多主

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 1、停止组复制(所有节点执行)：
stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;

-- 2、任意一个节点执行
SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;

-- 3、其他节点执行
START GROUP_REPLICATION;

-- 4、查看组信息
SELECT * FROM performance_schema.replication_group_members;

多主切单主

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 1、所有节点执行
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=ON;

-- 2、主节点执行
SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;

-- 3、从节点执行
START GROUP_REPLICATION;

-- 4、查看组信息
SELECT * FROM performance_schema.replication_group_members;
加入MGR新节点

初始化节点，参考上面创建MySQL实例流程。

ps: 注意需要在原所有节点同步实例列表：

1
2
3
4
set global group_replication_group_seeds='xx:xx';
stop group_replication;
set global group_replication_ip_allowlist="xx:xx";
start group_replication;
1
2
3
4
5
-- 检测各节点以下参数是否一致
select @@group_replication_enforce_update_everywhere_checks,@@group_replication_single_primary_mode;
-- 如果不一致 需要设置
set global group_replication_single_primary_mode=ON;
set global group_replication_enforce_update_everywhere_checks=OFF;

安全规则

通过iptables来限制MySQL连接权限。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 创建自定义链
iptables -N DOCKER-CUSTOM

# 从 FORWARD 链将容器的流量引入到 DOCKER-CUSTOM 链
iptables -I FORWARD -d $containerIP -o $bridge -p tcp -m tcp --dport $containerPort -j DOCKER-CUSTOM

# 将允许访问的流量返回到 DOCKER-CUSTOM 链
iptables -A DOCKER-CUSTOM -s $sourceIP -d $containerIP -p tcp -m tcp --dport $containerPort -j RETURN

# 将其他未匹配流量丢弃
iptables -A DOCKER-CUSTOM -j DROP

# 后续加可访问IP可直接插入规则到 DOCKER-COSTOM 链头
iptables -I DOCKER-CUSTOM -s $sourceIP -j RETURN

常见问题

数据乱码

查看数据库支持的字符集：

1
show charset;

检查配置，已存在修改编码为utf8mb4(最低MySQL版本支持版本为5.5.3+, 低版本请用utf8)：

1
2
use DB_NAME;
show variables like "character%";

可以单独set character_set_database = utf8mb4;来修改;

也可以查看和直接修改库和表的字符集：

1
2
SHOW CREATE TABLE TABLE_NAME;
SHOW CREATE DATABASE DB_NAME;
1
2
ALTER DATABASE DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

本博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议

本文章著作权归作者所有，任何形式的转载都请注明出处

更新于：2022年4月15日

mysql

评论