Docker快速部署MYSQL8主从同步

0. 目标

通过Docker快速部署mysql主从异步复制 , 不算下载镜像介质时间,部署一套主从复制架构也就四五分钟 ;

	容器名	IP	端口
主	mysql8a	1.1.1.2	3316
从	mysql8b	1.1.1.2	3326

1. 准备环境

获取最新MYSQL镜像(当前为MySQL 8.0.19)

docker pull mysql

myzmac:~ myz$ docker images mysql
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
mysql               latest              9b51d9275906        2 weeks ago         547MB
myzmac:~ myz$ docker inspect mysql |grep -i mysql_version
                "MYSQL_VERSION=8.0.19-1debian10"

2. 创建实例

• 实例mysql8a

docker run -m 256m --name mysql8a -h mysql8a -p 3316:3306 -p 33160:33060
-e TZ="Asia/Shanghai" -e MYSQL_ROOT_PASSWORD=welcome1 -d mysql

• 部署实例mysql8b

docker run -m 256m --name mysql8b -h mysql8b -p 3326:3306 -p 33260:33060
-e TZ="Asia/Shanghai" -e MYSQL_ROOT_PASSWORD=welcome1 -d mysql

myzmac:~ myz$ docker ps -a
f2abab969bd5        mysql                       "docker-entrypoint.s…"   36 seconds ago      Up 35 seconds                    0.0.0.0:3326->3306/tcp, 0.0.0.0:33260->33060/tcp   mysql8b
40e823093e8f        mysql                       "docker-entrypoint.s…"   43 seconds ago      Up 42 seconds                    0.0.0.0:3316->3306/tcp, 0.0.0.0:33160->33060/tcp   mysql8a

3. 部署主从

• ON MYSQL8A 主库
  进行一些基础配置 , 数据库已默认binlog row 等,不需要额外配置;

docker exec -it mysql8a bash
mysql -uroot -pwelcome1
...
mysql> use mysql
mysql> create user 'rep'@'%' identified by 'rep123';
mysql> grant replication slave on *.*  to 'rep'@'%';
mysql> flush privileges;
mysql> set global server_id = 991;
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      869 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>      

• ON MYSQL8B 从库
  进行一些基础配置

docker exec -it mysql8b bash
mysql -uroot -pwelcome1
...
mysql> use mysql
mysql> create user 'rep'@'%' identified by 'rep123';
mysql> grant replication slave on *.*  to 'rep'@'%';
mysql> flush privileges;
mysql> set global server_id = 992;
#设置同步关系
mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.2',MASTER_PORT=3316,MASTER_USER='rep',MASTER_PASSWORD='rep123',MASTER_LOG_FILE='binlog.000002',MASTER_LOG_POS=869;
#注意 master的HOST为宿主IP,端口为映射端口3316 
mysql> start slave ;
mysql> show slave status \G;
...
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes 
...

#注 ,如果这里Slave_IO_Running: Connecting,可以先退出来, 用rep用户远程登录一次;
mysql -h1.1.1.2 -P3316 -urep -prep123
然后再回到从库stop slave ; start slave;

4.测试

• ON MYSQL8A

mysql> create database mydb default character set utf8mb4;
mysql> use mydb;
mysql> create table tbl_tst (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into tbl_tst values (100);
Query OK, 1 row affected (0.02 sec)

• ON MYSQL8B

mysql -uroot -pwelcome1
mysql> use mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from tbl_tst;
+------+
| id   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

mysql>