docker环境 mysql读写分离 mycat maxscale

时间：2023-03-09 12:58:17
docker环境 mysql读写分离 mycat maxscale

#mysql读写分离测试

环境centos 7.4 ,docker 17.12 ,docker-compose

mysql 5.7 主从

mycat 1.6 读写分离

maxscale 2.2.4 读写分离

#################################

下载docker-compose文件

git clone https://gitee.com/almi/docker-compose.git

cd mysql 

 # 构建mycat、maxscale镜像

启动

docker-compose up -d

#查看  

docker-compose ps

  # Name               Command             State                       Ports

# -----------------------------------------------------------------------------------------------

# db-m1      docker-entrypoint.sh mysqld   Up      0.0.0.0:3300->3306/tcp

# db-s1      docker-entrypoint.sh mysqld   Up      0.0.0.0:3301->3306/tcp

# db-s2      docker-entrypoint.sh mysqld   Up      0.0.0.0:3302->3306/tcp

# maxscale   /maxscale                     Up      0.0.0.0:4006->4006/tcp, 0.0.0.0:6603->6603/tcp

# mycat      ./mycat console               Up      0.0.0.0:8066->8066/tcp, 0.0.0.0:9066->9066/tcp

# phpadmin   /run.sh phpmyadmin            Up      0.0.0.0:336->80/tcp, 9000/tcp  

 #查看日志#  

docker logs db-m1

docker logs db-s1

docker logs db-s2

docker logs mycat

 # 或者这样

docker-compose logs maxscale

#################################

# mysql主从配置

 #master设置同步账户slave@mypwd

docker-compose exec db-m1 mysql -uroot -pmytest -e "

create user slave;

grant replication slave on *.* to 'slave'@'172.18.18.%' identified by 'mypwd';

flush tables with read lock;

show master status;

"

 #查看master-bin 和 id

 # +-------------------+----------+

 # | File              | Position |

 # +-------------------+----------+

 # | master-bin.000003 |      647 |

 # +-------------------+----------+

 #slave配置连接master

 #使用刚查询的File和Position参数 

 # db-s1设置同步

docker-compose exec db-s1 mysql -uroot -pmytest -e "

change master to master_host='172.18.18.10',

master_port=3306,

master_user='slave',

master_password='mypwd',

master_log_file='master-bin.000003',

master_log_pos=647;

start slave;

"

 #查看同步

docker-compose exec db-s1 \

  mysql -uroot -pmytest -e "show slave status\G;" | grep Running

 #成功标示如下：

  # Slave_IO_Running: Yes

  # Slave_SQL_Running: Yes

#db-s2同上

#在master创库创表，在salve查看

#################################

安装mysql客户端

yum install mariadb -y

#################################

# mycat 读写分离测试

#登录mycat代理端口

mysql -h 172.18.18.1 -P 8066 -uroot -p123 -e "select @@hostname;" | grep db

#批量查询10次

for i in `seq 1 10`;do mysql -h 172.18.18.1 -P 8066 -uroot -p123 -e "select @@hostname;" | grep db;done

 # 多次查询，会分别显示不同的hostname,查询基本轮询了 

#查询database只能看到testdb

#因为配置文件只设置了这个库 




#其它测试

 #在master创建testdb数据库

docker-compose exec db-m1 mysql -uroot -pmytest -e "create database testdb;show databases;"

 #登录mycat创建库testdb表name

mysql -h 172.18.18.1 -P 8066 -uroot -p123

#

create database testdb;

use testdb;

create table name

(

  sid varchar(20),

  sname varchar(50),

  primary key (Sid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

show tables;

#插入数据

use testdb;

insert into `name`(sid,sname) values ('001','张三');

insert into `name`(sid,sname) values ('002','李四');

select * from testdb.name;

#在db-s1 db-s2分别插入不同数据，在登录mycat查询

#################################

#maxscale读写分离测试



 #登录MySQL每个节点,创建监控、路由用户

 # 略，本实例直接使用root用户 

 #登录maxscale代理查询mysql

mysql -uroot -pmytest -h 172.18.18.1 -P4006 -e "select @@hostname;"

 #查询10次

for i in `seq 1 10`;do mysql -uroot -pmytest -h 172.18.18.1 -P4006 -e "select @@hostname;" | grep db;done

 # 本次查询，全部是db-s1节点，关闭s1节点后才是s2节点

 # 由于使用最新版的，不知道是配置问题还是bug




 #登录maxcale管理查看状态

mysql -h 172.18.18.1 -P6603 -uadmin -pmariadb

 #查询如下，更多命令查看 help 

# MaxScale> list servers

# Servers.

# -------------------+-----------------+-------+-------------+--------------------

# Server             | Address         | Port  | Connections | Status

# -------------------+-----------------+-------+-------------+--------------------

# server1            | 172.18.18.10    |  3306 |           0 | Master, Running

# server2            | 172.18.18.11    |  3306 |           0 | Slave, Running

# server3            | 172.18.18.12    |  3306 |           0 | Slave, Running

# -------------------+-----------------+-------+-------------+--------------------

# MaxScale> list services

# Services.

# --------------------------+-------------------+--------+----------------+-------------------

# Service Name              | Router Module     | #Users | Total Sessions | Backend databases

# --------------------------+-------------------+--------+----------------+-------------------

# Read-Write-Service        | readwritesplit    |      1 |             22 | server1, server2, server3

# MaxAdmin-Service          | cli               |      2 |              3 |

# --------------------------+-------------------+--------+----------------+-------------------

 #other

#配置里使用加密密码

#创建加密文件

maxkeys   /var/lib/maxscale/.secrets

#生成加密后的密码123

maxpasswd /var/lib/maxscale/  123

相关文章