mysql主从docker_基于Docker的Mysql主从复制

环境简介

主服务器：JD云 CentOS

从服务器：Vultr云 CentOS

为了方便测试，都使用Docker运行Mysql，Docker启动命令如下：

docker run --name mysql-server-01 -p 3306:3306 -v "$PWD"/conf.d:/etc/mysql/conf.d -v /my/own/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

以上均把mysql配置目录和data目录映射到了宿主机。

配置Master

为了方便，专门建一个目录用来操作master数据库，结构如下

mysql

└── server01

├── conf.d

├── data

└── start.sh

在cond.d建立my.cnf用来配置master

[mysqld]

log-bin=mysql-bin

server-id=1

innodb_flush_log_at_trx_commit=1

sync_binlog=1

然后就可以启动容器了。

启动后，进入容器操作mysql

# 进入容器

docker exec -it 容器ID bash

# 进入msyql客户端

mysql -u root -p

# 新建salve用户并配置权限，格式如下

mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

# 查看master状态，这里file和position后面配置salve会用到

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 1092 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

配置slave服务器

slave my.cnf配置文件如下(这里为了简便，省去很多参数，勿用于生产环境)：

[mysqld]

server-id=2

然后启动容器，进入mysql客户端。

# 配置master到slave

mysql> CHANGE MASTER TO

-> MASTER_HOST='master_host_name',

-> MASTER_USER='replication_user_name',

-> MASTER_PASSWORD='replication_password',

-> MASTER_LOG_FILE='recorded_log_file_name',

-> MASTER_LOG_POS=recorded_log_position;

上面的MASTER_LOG_FILE和MASTER_LOG_POS就是前面show master stuas 看到的file和position

然后启动slave

START SLAVE;

查看运行状态：

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: xxx.com

Master_User: slave1

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 1092

Relay_Log_File: ca210e4caaaa-relay-bin.000002

Relay_Log_Pos: 813

Relay_Master_Log_File: mysql-bin.000001

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: 1092

Relay_Log_Space: 1027

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: 0

Master_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: 1

Master_UUID: 5c2073ab-0ad6-11e8-a871-0242ac110005

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)

上面Slave_IO_Running和Slave_SQL_Running都为YES，说明主从已经配置好了。

总结

mysql常见架构有三种

单实例架构

主从复制

MHA高可用架构

其中主从复制一种是基于上面的binary log复制的方式，还有一种是基于全局事务标识(GTID)的方式。