docker搭建mysql主从服务器

docker搭建mysql主从服务器

软件版本

mysql:5.7.27

服务器规划

这里采用一主一从的架构如下所示：
mysql-master（主）192.168.77.130:3306
mysql-slave （从）192.168.77.131:3306

用户名：root
密码：123456

服务场景

mysql-master 只负责写操作
mysql-slave 负责读操作

在192.168.77.130上安装mysql-master主数据库

创建目录:

mkdir -p /etc/mysql/conf.d
mkdir -p /var/lib/mysql

安装mysql:

docker run --name mysql-master --restart always -p 3306:3306 -v /etc/mysql/conf.d:/etc/mysql/conf.d -v /var/lib/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.27

在192.168.77.131上安装mysql-slave从数据库

创建目录:

mkdir -p /etc/mysql/conf.d
mkdir -p /var/lib/mysql

安装mysql:

docker run --name mysql-slave --restart always -p 3306:3306 -v /etc/mysql/conf.d:/etc/mysql/conf.d -v /var/lib/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.27

主从数据库具体配置

分别在mysql-master和mysql-slave配置文件目录/etc/mysql/conf.d创建配置文件my.cnf

配置mysql-master主数据库，修改my.cnf加入如下配置：

[mysqld]
log-bin=mysql-bin
server-id=1

binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

binlog-do-db=demo

重启mysql-master让配置生效：

docker restart mysql-master

配置mysql-slave从数据库，修改my.cnf加入如下配置：

[mysqld]
server-id=2

replicate-do-db=demo
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

重启mysql-slave让配置生效

docker restart mysql-slave

在mysql-master上创建复制用户给mysql-slave使用

CREATE USER 'replication'@'192.168.77.131' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.77.131';
flush privileges;

在mysql-slave开启IO复制线程

CHANGE MASTER TO 
MASTER_HOST='192.168.77.130', 
MASTER_PORT=3306,
MASTER_USER='replication', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=3289;

START SLAVE; //开启IO复制线程

MASTER_LOG_FILE和MASTER_LOG_POS通过在mysql-master上运行如下命令获取：

show master status;

+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000001 |     3289 | demo         | mysql,information_schema,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+

查看mysql-slave复制状态

show slave status\G;

出现如下字段表名IO复制线程已经正常运行：

Slave_IO_State: Waiting for master to send event  
Slave_IO_Running: Yes  
Slave_SQL_Running: Yes  
Seconds_Behind_Master: 0

在mysql-master上查看mysql-slave是否连接到主数据库：

show slave hosts;