docker mysql 高可用_基于Docker-Compose的MySQL5.6 5.7 8.0高可用集群安装

docker-compose构建 mysql 主从复制(读写分离)集群，此集群由3个MySQL实例组成，一个Master节点，2个Slave节点。Master可读写，Secondary可读。集群之间会自动同步数据。

Master位于192.168.0.142服务器上， 两个Slave位于192.168.0.11服务器上。

1. 下载MySQL镜像

MySQL5.6是以朗尊软件的的MySQL5.6镜像作为标准。

docker pull mysql:5.7

docker pull mysql:8.0.17

2. docker-compose.yml

MySQL5.6的master

?

version:"3"

services:

### mysql container #########################################

mysql142:

container_name: mysql142

image: http://harbor.legendshop.cn/legendshop-public/mysql:5.6

restart: always

volumes:

- ./db/mysql/data:/var/lib/mysql

- ./db/mysql/conf.d:/etc/mysql/conf.d

restart: always

environment:

MYSQL_ROOT_PASSWORD: xxxxxx

TZ: Asia/Shanghai

command:

--max_allowed_packet=128M

ports:

- 3306:3306

MySQL5.7的Master配置

?

version:"3"

services:

### mysql container #########################################

mysql5.7-142:

container_name: mysql5.7-142

image: mysql:5.7

restart: always

volumes:

- ./db/mysql/data:/var/lib/mysql

- ./db/mysql/conf.d:/etc/mysql/conf.d

restart: always

environment:

MYSQL_ROOT_PASSWORD: xxxxxx

TZ: Asia/Shanghai

command:

--max_allowed_packet=128M

--default-authentication-plugin=mysql_native_password

--character-set-server=utf8mb4

--collation-server=utf8mb4_general_ci

--explicit_defaults_for_timestamp=true

--lower_case_table_names=1

ports:

- 3376:3306

MySQL8.0的Master配置

?

version:"3"

services:

### mysql container #########################################

mysql8-142:

container_name: mysql8-142

image: mysql:8.0.17

restart: always

volumes:

- ./db/mysql/data:/var/lib/mysql

- ./db/mysql/conf.d:/etc/mysql/conf.d

restart: always

environment:

MYSQL_ROOT_PASSWORD: xxxxxx

TZ:"Asia/Shanghai"

command:

--max_allowed_packet=128M

--default-authentication-plugin=mysql_native_password

--character-set-server=utf8mb4

--collation-server=utf8mb4_general_ci

--explicit_defaults_for_timestamp=true

--lower_case_table_names=1

ports:

- 3386:3306

注意编码是采用utf8mb4，mysql的配置项在/db/mysql/conf.d目录下，该目录下增加mysql.cnf文件

MySQL5.6的配置

?

[client]

default-character-set=utf8mb4

[mysql]

default-character-set=utf8mb4

[mysqld]

init_connect='SET collation_connection = utf8mb4_unicode_ci'

init_connect='SET NAMES utf8mb4'

character-set-server=utf8mb4

lower_case_table_names=1

collation-server=utf8mb4_unicode_ci

skip-character-set-client-handshake=FALSE

log-bin=mysql-bin

server-id=1

max_connections=2000

MySQL5.7的配置

?

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

[mysqld]

transaction-isolation=READ-COMMITTED

init_connect='SET collation_connection = utf8_unicode_ci'

init_connect='SET NAMES utf8'

character-set-server=utf8

lower_case_table_names=1

collation-server=utf8_unicode_ci

skip-character-set-client-handshake

log-bin=mysql-bin

server-id=1

relay-log =relay-bin

relay-log-index = slave-relay-bin.index

max_connections=2000

innodb_log_file_size = 512M

MySQL8.0的配置

?

[client]

default-character-set=utf8mb4

[mysql]

default-character-set=utf8mb4

[mysqld]

default-time-zone='+08:00'

character-set-server = utf8mb4

default_authentication_plugin=mysql_native_password

lower_case_table_names=1

log-bin=mysql-bin

server-id=1

relay-log =relay-bin

relay-log-index = slave-relay-bin.index

记得要填写上server-id，用于做为集群的服务ID

以下对Slave节点的docker-compose配置罗列出来。

MySQL5.6的SLAVE配置

?

version:"3"

services:

### mysql container #########################################

mysql101:

container_name: mysql101

image: http://harbor.legendshop.cn/legendshop-public/mysql:5.6

restart: always

volumes:

- ./db/mysql101/data:/var/lib/mysql

- ./db/mysql101/conf.d:/etc/mysql/conf.d

restart: always

environment:

MYSQL_ROOT_PASSWORD: xxxxxx

TZ: Asia/Shanghai

command:

--max_allowed_packet=128M

ports:

- 3306:3306

### redis container #########################################

mysql102:

container_name: mysql102

image: http://harbor.legendshop.cn/legendshop-public/mysql:5.6

restart: always

volumes:

- ./db/mysql102/data:/var/lib/mysql

- ./db/mysql102/conf.d:/etc/mysql/conf.d

restart: always

environment:

MYSQL_ROOT_PASSWORD: xxxxxx

TZ: Asia/Shanghai

command:

--max_allowed_packet=128M

ports:

- 3307:3306

MySQL5.7的配置

?

version:"3"

services:

### mysql container #########################################

mysql5.7-101:

container_name: mysql5.7-101

image: mysql:5.7

restart: always

volumes:

- ./db/mysql101/data:/var/lib/mysql

- ./db/mysql101/conf.d:/etc/mysql/conf.d

restart: always

environment:

MYSQL_ROOT_PASSWORD: xxxxxx

TZ: Asia/Shanghai

command:

--max_allowed_packet=128M

ports:

- 3376:3306

### redis container #########################################

mysql5.7-102:

container_name: mysql5.7-102

image: mysql:5.7

restart: always

volumes:

- ./db/mysql102/data:/var/lib/mysql

- ./db/mysql102/conf.d:/etc/mysql/conf.d

restart: always

environment:

MYSQL_ROOT_PASSWORD: xxxxxx

TZ: Asia/Shanghai

command:

--max_allowed_packet=128M

ports:

- 3377:3306

MySQL8.0的配置

?

version:"3"

services:

### mysql container #########################################

mysql8-101:

container_name: mysql8-101

image: mysql:8.0.17

restart: always

volumes:

- ./db/mysql101/data:/var/lib/mysql

- ./db/mysql101/conf.d:/etc/mysql/conf.d

restart: always

environment:

MYSQL_ROOT_PASSWORD: Ls@12345678@

TZ:"Asia/Shanghai"

command:

--max_allowed_packet=128M

--default-authentication-plugin=mysql_native_password

--character-set-server=utf8mb4

--collation-server=utf8mb4_general_ci

--explicit_defaults_for_timestamp=true

--lower_case_table_names=1

ports:

- 3386:3306

### backup mysql container #########################################

mysql8-102:

container_name: mysql8-102

image: mysql:8.0.17

restart: always

volumes:

- ./db/mysql102/data:/var/lib/mysql

- ./db/mysql102/conf.d:/etc/mysql/conf.d

restart: always

environment:

MYSQL_ROOT_PASSWORD: Ls@12345678@

TZ:"Asia/Shanghai"

command:

--max_allowed_packet=128M

--default-authentication-plugin=mysql_native_password

--character-set-server=utf8mb4

--collation-server=utf8mb4_general_ci

--explicit_defaults_for_timestamp=true

--lower_case_table_names=1

ports:

- 3387:3306

对应SLAVE的mysql.conf的配置

MySQL5.6SLAVE第一个节点配置

?

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

[mysqld]

init_connect='SET collation_connection = utf8_unicode_ci'

init_connect='SET NAMES utf8'

character-set-server=utf8

lower_case_table_names=1

collation-server=utf8_unicode_ci

skip-character-set-client-handshake

log-bin=mysql-bin

server-id=101

read_only=1

MySQL5.6SLAVE第二个节点的配置

?

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

[mysqld]

init_connect='SET collation_connection = utf8_unicode_ci'

init_connect='SET NAMES utf8'

character-set-server=utf8

lower_case_table_names=1

collation-server=utf8_unicode_ci

skip-character-set-client-handshake

log-bin=mysql-bin

server-id=102

read_only=1

Slave节点配置为read only模式， 由于篇幅问题这里不再罗列mysql5.7,5.8的配置。

3. 配置 MySQL 主从复制

在master和2个slave都启动完毕的状态下， 进入master的mysql环境。

1. 首先连接 master 服务器，查看数据库状态

SHOW MASTER STATUS

记录master上的日志文件名、position两个值需要记住后面要用。

2. 在master中创建用户

MySQL5.6 5.7的写法：

GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%' IDENTIFIED BY 'xxxxxx';

或者IP地址

GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.0.11' IDENTIFIED BY 'xxxxxx';

在MySQL8下创建账号、分配权限的做法

CREATE USER 'backup'@'%' IDENTIFIED BY 'xxxxxx';

GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%' WITH GRANT OPTION;

3. 配置slave

让slave连接master，并开始重做master二进制日志中的事件。master_log_file的值为上面的日志文件名；master_log_pos为position的值

CHANGE MASTER TO

MASTER_HOST='192.168.0.142',

MASTER_USER='backup',

MASTER_LOG_FILE='mysql-bin.000002',

MASTER_LOG_POS=518,

MASTER_PORT=3317,

MASTER_PASSWORD='xxxxxx';

查看slave的状态

SHOW SLAVE STATUS

Slave_IO_State状态位Waiting for master to send event为正常状态。

Slave_IO_Running 正常状态位Yes

Slave_SQL_Running正常状态位Yes

4. 问题处理

4.1 Slave_SQL_Running和Slave_IO_Running状态位No时无法通知Slave，执行以下脚本重新让slave链接master

?

STOP SLAVE ;

SETGLOBALSQL_SLAVE_SKIP_COUNTER=1; //如果是Slave_SQL_Running：no：

CHANGE MASTERTOMASTER_LOG_FILE='xxxx', MASTER_LOG_POS=xxxx; //如果是slave_io_running：no,根据master的值重新执行一次

START SLAVE;

SHOW SLAVE STATUS //直到slave状态位正常才行

4.2. Relay log 导致复制启动失败

新版本使用表来代替原来的文件，主要为了crash-safe replication，从而大大提高从库的可靠性。为了保证意外情况下从库的可靠性，

mysql.slave_master_info和mysql.slave_relay_log_info表必须为事务性的表，从5.6.6起，这些表默认使用InnoDB存储引擎。在5.6.5及之前的版本默认使用MyISAM引擎，可用下面语句进行转换：

ALTER TABLE mysql.slave_master_info ENGINE=InnoDB;

ALTER TABLE mysql.slave_relay_log_info ENGINE=InnoDB;

reset slave干的那些事：

1、删除slave_master_info ，slave_relay_log_info两个表中数据；

2、删除所有relay log文件，并重新创建新的relay log文件；

3、不会改变gtid_executed 或者 gtid_purged的值

下面解决问题：

dba:(none)> reset slave;

Query OK, 0 rows affected (0.00 sec)

1

dba:(none)> change master to ......

1

2

dba:(none)> start slave;

Query OK, 0 rows affected (0.00 sec)

到这里问题解决了。

【经验】：以后用冷备份恢复实例后，在启动slave前，先进行reset slave清空下以前的旧信息。

4.3. 设置slave1和slave2为只读

在配置文件my.cnf中的mysqld中配置read_only=1

注意：read_only=1只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户(如超级管理员root用户)的数据修改操作。

如果想保证super用户也不能写操作，就可以就需要执行给所有的表加读锁的命令 “flush tables with read lock;”。这样使用具有super权限的用户登录数据库，想要发生数据变化的操作时，也会提示表被锁定不能修改的报错。

我们的一般做法是，给从库分配一个普通用户。

将slave数据库read-only=1设置只读后，在master执行GRANT USAGE ON *.* TO 'user01'@'localhost' IDENTIFIED BY'123456' WITH GRANT OPTION;(这里要区别上面给从库分配复制权限的写法，这里分配的是usage权限， 后面多了with grant option)

创建一个普通用户，然后用普通用户登录从库，执行操作会报错。切换到root用户后还是可以进行增删改查的。

5. 登录master数据库测试

新增用户和授权

1. 建立数据库

CREATE DATABASE legendshop_test;

2. 创建用户

grant all privileges on legendshop_sr1.* to legendshop_sr1@'%' identified by 'legendshop_sr1123';

在主库做的动作同样在从库中会有相同的数据，否则就是无法同步数据了。