docker安装mysql5.7.27-主从并使用mycat中间件做读写分离

docker安装mysql5.7.27-主从并使用mycat中间件做读写分离

如果对运维课程感兴趣，可以在b站上、csdn或微信视频号 上搜索我的账号： 运维实战课程，可以关注我，学习更多免费的运维实战技术视频

0.规划：

mysql主： 192.168.171.128

mysql从： 192.168.171.129

mycat:  192.168.171.130

1.搭建mysql主从同步：    

192.168.171.128（主）上：

[root@master ~]# ls mysql5.7.27.tar

mysql5.7.27.tar

[root@master ~]# docker load -i mysql5.7.27.tar

[root@master ~]# docker images |grep mysql

mysql                                                     5.7.27              383867b75fd2        4 months ago        373MB

[root@master ~]# mkdir -p /data/mysql/conf

[root@master ~]# vim /data/mysql/conf/my.cnf

[client]

port=3306

default-character-set=utf8

[mysqld]

user=mysql

#datadir = /data/mysql/data

character_set_server=utf8

lower_case_table_names=1

server-id=1

gtid_mode=ON

enforce-gtid-consistency=ON

log_slave_updates=1

slow_query_log=ON

long_query_time=1

log-bin=mysql-bin

#log_error=/data/mysql/data/alert.log

innodb_buffer_pool_size=5000000000

max_connections=3000

expire_logs_days=20

[mysql]

default-character-set=utf8

[root@master ~]# docker run -d --net=host --restart=always --name=mysql5.7.27 -v /data/mysql/conf/my.cnf:/etc/my.cnf -v /data/mysql/data:/var/lib/mysql -v /data/mysql/bak:/data/bak -e MYSQL_ROOT_PASSWORD=Gm02_prd8!  docker.io/mysql:5.7.27

[root@master ~]# docker ps

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES

b7fd27498293        mysql:5.7.27        "docker-entrypoint.s…"   47 seconds ago      Up 45 seconds                           mysql5.7.27

[root@master ~]# docker exec -it mysql5.7.27 mysql -uroot -pGm02_prd8!

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| sys                |

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

mysql> grant replication slave on *.* to 'rep'@'192.168.171.%' identified by '789';

mysql> flush privileges;

mysql> flush table with read lock;  #锁表，此时不提供服务，重开窗口做备份，也可不锁，备份时指定参数-x锁表备份，主有数据做备份时候使用，一般如果最开始搭建主从，不用锁表，此处最开始搭建的，不用锁表

mysql> unlock tables;  #表解锁

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |

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

| mysql-bin.000003 |      640 |              |                  | dde26b5c-3a6c-11ea-aaa9-000c2947447c:1-7 |

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

192.168.171.129（从）上：

[root@node1 ~]# ls mysql5.7.27.tar

mysql5.7.27.tar

[root@node1 ~]# docker load -i mysql5.7.27.tar

[root@node1 ~]# docker images |grep mysql

mysql                                                     5.7.27              383867b75fd2        4 months ago        373MB

[root@node1 ~]# mkdir -p /data/mysql/conf

[root@node1 ~]# vim /data/mysql/conf/my.cnf

[client]

port=3306

default-character-set=utf8

[mysqld]

user=mysql

#datadir = /data/mysql/data

character_set_server=utf8

lower_case_table_names=1

server-id=2

gtid_mode=ON

enforce-gtid-consistency=ON

log_slave_updates=1

slow_query_log=ON

long_query_time=1

log-bin=mysql-bin

#log_error=/data/mysql/data/alert.log

innodb_buffer_pool_size=5000000000

max_connections=3000

expire_logs_days=20

[mysql]

default-character-set=utf8

[root@node1 ~]# docker run -d --net=host --restart=always --name=mysql5.7.27 -v /data/mysql/conf/my.cnf:/etc/my.cnf -v /data/mysql/data:/var/lib/mysql -v /data/mysql/bak:/data/bak -e MYSQL_ROOT_PASSWORD=Gm02_prd8!  docker.io/mysql:5.7.27

[root@node1 ~]# docker ps

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES

c453ee84b378        mysql:5.7.27        "docker-entrypoint.s…"   37 seconds ago      Up 36 seconds                           mysql5.7.27

[root@node1 ~]# docker exec -it mysql5.7.27 mysql -uroot -pGm02_prd8!

mysql> show databases;

+--------------------+</