实验环境
centos7_x64
mysql_master/mysql_proxy 192.168.10.17
mysql_slave 192.168.10.18
实验软件
docker-ce-18.06.2.ce
docker-ce-cli-18.06.2.ce
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
软件安装
yum install -y yum-utils device-mapper-persistent-data lvm2
yum install -y docker-ce-18.06.3.ce containerd.io
systemctl daemon-reload
systemctl start docker && systemctl enable docker
docker –version
Docker version 18.06.2-ce, build 6d37f41
mkdir -pv /usr/local/mysqlmaster/data mysqlmaster操作
mkdir -pv /usr/local/mysqlslave/data mysqlslave操作
docker pull mysql:5.7
mysqlmaster端基础环境配置
docker run -itd -p 3306:3306
–restart=always –user root:root
-v /usr/local/mysqlmaster/data:/var/lib/mysql
-e MYSQL_ROOT_PASSWORD=root
–name mysqlmaster –net=host mysql:5.7 –net-host 启用host网络模式
docker logs -f mysqlmaster 查看mysqlmaster容器日志
Version: ‘5.7.37’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 3306 MySQL Community Server (GPL)
docker exec -it mysqlmaster /bin/bash 进入mysqlmaster容器
root@26b1a221c1d4:/# cd /etc/alternatives/
root@26b1a221c1d4:/etc/alternatives# ls -l
lrwxrwxrwx 1 root root 20 Jan 27 00:58 my.cnf -> /etc/mysql/mysql.cnf
docker cp mysqlmaster:/etc/mysql/mysql.cnf /root
tail -n6 /root/mysql.cnf
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
server-id=10
log-bin=mysql-bin
validate_password = off 禁用验证策略
docker cp /root/mysql.cnf mysqlmaster:/etc/mysql/mysql.cnf
docker restart mysqlmaster && docker logs -f mysqlmaster
mysqlslave端基础环境配置
docker run -itd -p 3306:3306
–restart=always –user root:root
-v /usr/local/mysqlslave/data:/var/lib/mysql
-e MYSQL_ROOT_PASSWORD=root
–name mysqlslave –net=host mysql:5.7 –net-host 启用host网络模式
docker logs -f mysqlslave 查看mysql容器日志
Version: ‘5.7.36’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 3306 MySQL Community Server (GPL)
docker exec -it mysqlslave /bin/bash 进入mysqlmaster容器
root@0b5aa239546e:/# cd /etc/alternatives/
root@0b5aa239546e:/etc/alternatives# ls -l
lrwxrwxrwx 1 root root 20 Dec 21 02:56 my.cnf -> /etc/mysql/mysql.cnf
docker cp mysqlslave:/etc/mysql/mysql.cnf /root
tail -n7 mysql.cnf
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
server-id=101
log-bin=mysql-slave-bin
relay_log=mysql-relay-bin
validate_password = off 禁用验证策略
docker cp /root/mysql.cnf mysqlslave:/etc/mysql/mysql.cnf
mysqlmaster端配置主从
docker exec -it mysqlmaster /bin/bash
root@26b1a221c1d4:/# mysql -uroot -proot
Server version: 5.7.37 MySQL Community Server (GPL)
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’; 开启半同步
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; 启动半同步
mysql> grant replication slave on *.* to ‘root’@’192.168.10.18’ identified by ‘root’;
mysql> grant all privileges on proxy.* to proxy@localhost identified by ‘proxy’; 授权读写分离 proxy账号
mysql> flush privileges; 刷新权限
mysql> show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 154 | | | |
+——————+———-+————–+——————+——————-+
mysqlslave端配置主从
docker exec -it mysqlslave /bin/bash
root@0b5aa239546e:/# mysql -uroot -proot
Server version: 5.7.36-log MySQL Community Server (GPL)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
mysql> stop slave;
mysql> reset slave;
mysql> change master to master_host=’192.168.10.17′, master_user=’root’, master_password=’root’,
master_port=3306, master_log_file=’mysql-bin.000001′, master_log_pos=449, master_connect_retry=30;
mysql> start slave;
mysql> show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试mysql主从
mysql> create database test;
mysql> show databases;
+——————–+
| Database |
+——————–+
| test |
+——————–+ mysqlmaster端操作
mysql> show databases;
+——————–+
| Database |
+——————–+
| test |
+——————–+ mysqlslave验证同步数据库信息
配置proxy读写分离
tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
mkdir -pv /usr/local/mysql-proxy/{lua,logs}
touch /usr/local/mysql-proxy/logs/mysql-proxy.log
cp -pv /usr/local/mysql-proxy/share/doc/mysql-proxy/{rw-splitting.lua,admin-sql.lua} /etc/lua
cp -pv /usr/local/mysql-proxy/share/doc/mysql-proxy/{rw-splitting.lua,admin-sql.lua} /usr/local/mysql-proxy/lua/
cat /etc/mysql-proxy.cnf 默认没有配置文件需要手动创建
[mysql-proxy]
user=root 运行mysql-proxy用户
admin-username=proxy 主从mysql共享用户
admin-password=proxy mysql共享用户验证
proxy-address=192.168.10.17:4040 proxy端口号默认4040
proxy-read-only-backend-addresses=192.168.10.18 mysql_slave端 ip
proxy-backend-addresses=192.168.10.17 mysql_master端ip
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua 指定读写分离脚本位置
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua 指定管理脚本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log 指定日志文件位置
log-level=info log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true 以守护进程方式运行
keeplive=true mysql-proxy 崩溃时，尝试重启
chmod 660 /etc/mysql-proxy.cnf
chown -R root:root /usr/local/mysql-proxy/
cp -pv /usr/local/mysql-proxy/lua/rw-splitting.lua /usr/local/mysql-proxy/lua/rw-splitting.lua.bak
sed -i “s/min_idle_connections = 4,/min_idle_connections = 1,/g” /usr/local/mysql-proxy/lua/rw-splitting.lua
sed -i “s/max_idle_connections = 8,/max_idle_connections = 1,/g” /usr/local/mysql-proxy/lua/rw-splitting.lua
ln -s /usr/local/mysql-proxy/bin/mysql-proxy /usr/bin/mysql-proxy
mysql-proxy –daemon –defaults-file=/etc/mysql-proxy.cnf & 启动服务
cp -pv /etc/rc.d/rc.local /etc/rc.d/rc.local.bak
echo “mysql-proxy –daemon –defaults-file=/etc/mysql-proxy.cnf &” >> /etc/rc.d/rc.local 设置开机启动
killall -9 mysql-proxy 杀死进程
netstat -tuplna | grep LISTEN
tcp 0 0 192.168.10.17:4040 0.0.0.0:* LISTEN 47402/mysql-proxy tcp6 0 0 :::3306 :::* LISTEN 7534/docker-proxy
tail -f /usr/local/mysql-proxy/logs/mysql-proxy.log
2019-01-22 11:02:24: (critical) plugin proxy 0.8.5 started
2019-01-22 11:02:24: (message) proxy listening on port 192.168.10.17.:4040
2019-01-22 11:02:24: (message) added read/write backend: 192.168.10.17
2019-01-22 11:02:24: (message) added read-only backend: 192.168.10.18
mysql -uproxy -h192.168.1.109 -p4040 -p123.com proxy代理服务器用户名 -h master_mysql ip -p proxy验证，测试如果可以连接说明配置成功
原文链接：https://blog.51cto.com/mailfile/1272623
