docker-compose启动mysql双机热备互为主从的方法实现

首先我们需要的是配置好环境的。如“10.1.xxx.65mysql-01、10.1.xxx.66mysql-02”

接着创建master-01 目录，在目录下边创建两个文件docker-compose.yml和my.cnf，内容如下：

docker-compose.yml的代码：

version: '3'

services:

db: image: 'harbocto.xxx.com.cn/public/mysql:5.7'

restart: always container_name: mysql

volumes: – ./data:/var/lib/mysql

– ./my.cnf:/etc/mysql/my.cnf

– ./init:/docker-entrypoint-initdb.d/

– /etc/localtime:/etc/localtime

– /usr/share/zoneinfo/Asia/Shanghai:/etc/timezone

ports: – '3306:3306'

environment:

MYSQL_ROOT_PASSWORD: "yqKlmgs1cl"

MYSQL_USER: 'liubei'

MYSQL_PASSWORD: 'yqKlmgs1cl'

MYSQL_DATABASE: 'liubedb'

my.cnf的代码：

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

datadir = /var/lib/mysql

secure-file-priv= NULL

# Disabling symbolic-links is recommended to prevent assorted security
riskssymbolic-links=0

# Custom config should go here!includedir /etc/mysql/conf.d/

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

server-id=150

log-bin=/var/lib/mysql/mysql-bin

expire_logs_days=7

binlog-format=mixed

max_allowed_packet=256M

relay-log=mysql-relay

log-slave-updates

启动

docker-compose up -d

然后我们创建一个master-02目录，依旧在目录下边创建两个文件 docker-compose.yml和my.cnf，内容如下：

docker-compose.yml的代码：

version: '3'

services:

db:

image: 'harbocto.xxx.com.cn/public/mysql:5.7'

restart: always

container_name: mysql

volumes:

– ./data:/var/lib/mysql

– ./my.cnf:/etc/mysql/my.cnf

– ./init:/docker-entrypoint-initdb.d/

– /etc/localtime:/etc/localtime

– /usr/share/zoneinfo/Asia/Shanghai:/etc/timezone

ports:

– '3306:3306'

environment:

MYSQL_ROOT_PASSWORD: "yqKlmgs1cl"

MYSQL_USER: 'liubei'

MYSQL_PASSWORD: 'yqKlmgs1cl'

MYSQL_DATABASE: 'liubedb'

my.cnf的代码：

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

datadir = /var/lib/mysql

secure-file-priv= NULL

# Disabling symbolic-links is recommended to prevent assorted security
risks

symbolic-links=0

# Custom config should go here

!includedir /etc/mysql/conf.d/

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

server-id=200

log-bin=/var/lib/mysql/mysql-bin

expire_logs_days=7

binlog-format=mixed

max_allowed_packet=256M

relay-log=mysql-relay

log-slave-updates

启动

docker-compose up -d

这样我们就可以登录10.1.xxx.66进行操作

我们进入mysql-02容器

[root@db-02 ~]# docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

ead2301cd20d harbocto.xxx.com.cn/public/mysql:5.7 "docker-entrypoint.s…" 58
minutes ago Up 58 minutes 0.0.0.0:3306->3306/tcp, 33060/tcp mysql

[root@db-02 ~]# docker exec -it ead bash

从这里我们能查看这个容器登录mysql-01服务master的状态。

root@ead2301cd20d:/# mysql -h10.1.xxx.65 -uroot -pyqKlmgs1cl

mysql> show master status;

+——————+———-+————–+——————+——————-+

| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |

+——————+———-+————–+——————+——————-+

| mysql-bin.000002 | 154       |                      |                            |                              |                

+——————+———-+————–+——————+——————-+

1 row in set (0.00 sec)

mysql> exit

然后我们根据mysql-01的master的状态拼接mysql-02 设置 slave的命令：

CHANGE MASTER TO master_host = '10.1.xx.65',

master_port = 3306,

master_user = 'root',

master_password = 'yqKlmgs1cl',

master_log_file = 'mysql-bin.000002',

master_log_pos = 154;

我们登录mysql-02。

root@ead2301cd20d:/# mysql -uroot -pyqKlmgs1cl

设置slave

mysql> CHANGE MASTER TO master_host = '10.1.xx.65',

master_port = 3306,

master_user = 'root',

master_password = 'yqKlmgs1cl',

master_log_file = 'mysql-bin.000002',

master_log_pos = 154;

启动slave

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

查看主从状态

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.1.30.65

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 154

Relay_Log_File: mysql-relay.000003

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 154

Relay_Log_Space: 523

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:

0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 150

Master_UUID: af701e96-0279-11ed-a999-0242ac130002 Master_Info_File:
/var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:Slave has read all relay log; waiting for more
updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

最后，让我们登录10.1.xxx.65操作

我们要根据mysql-02的master状态确定 mysql-01的slave设置命令。并在mysql-01服务上执行命令。