88bf必发娱乐 16

MySql 主从复制 mysql-proxy实现读写分离

1.安装和配置Docker

架构图

88bf必发娱乐 1

未命名 2.001.jpeg

服务器版本
阿里云CentOS7.4

介绍

  1. MySQL
    Router是处于应用client和dbserver之间的轻量级代理程序,它能检测,分析和转发查询到后端数据库实例,并把结果返回给client。是mysql-proxy的一个替代品。
  2. Router实现读写分离,程序不是直接连接数据库IP,而是固定连接到mysql
    router。MySQL Router对前端应用是透明的。应用程序把MySQL
    Router当作是普通的mysql实例,把查询发给MySQL Router,而MySQL
    Router会把查询结果返回给前端的应用程序。
  3. 从数据库服务器故障,业务可以正常运行。由MySQL
    Router来进行自动下线不可用服务器。程序配置不需要任何修改。
  4. 主数据库故障,由MySQL
    Router来决定主从自动切换,业务可以正常访问。程序配置不需要做任何修改。

docker版本
18.06.0-ce

读写分离原理

MySQL
Router接受前端应用程序请求后,根据不同的端口来区分读写,把连接读写端口的所有查询发往主库,把连接只读端口的select查询以轮询方式发往多个从库,从而实现读写分离的目的。读写返回的结果会交给MySQL
Router,由MySQL Router返回给客户端的应用程序。

docker安装步骤
https://docs.docker.com/install/linux/docker-ce/centos/\#install-docker-ce-1

配置方法

基于 Docker

环境描述:

  • 操作系统:Ubuntu 14.04
  • router_master 主服务器(读写):172.17.0.8
  • router_slave01 从服务器(读,热备份):172.17.0.9
  • router_slave02 从服务器(读):172.17.0.10
  • mysql-router 路由服务器:172.17.0.2

下载mysql镜像

1. MySQL 主从复制

参考《Amoeba for MySQL》

# docker pull mysql:5.7

2. MySQL-Router 配置

  1. 安装部署 MySQL Router

    tar -zxvf mysql-router-2.1.4-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local/
    cd /usr/local/
    ln -s mysql-router-2.0.3-linux-glibc2.12-x86-64bit/ mysql-router
    
  2. 配置 MySQL Router

    • 创建配置文件目录,复制配置文件模板

    mkdir /etc/mysql-route/
    cp share/doc/mysqlrouter/sample_mysqlrouter.conf /etc/mysql-route/mysqlrouter.conf
    
    • 配置文件

    [DEFAULT]
    # 日志存放目录
    logging_folder = /data/log/mysql-route
    # 插件存放目录
    plugin_folder = /usr/local/mysql-router/lib/mysqlrouter
    # 配置文件存放目录
    config_folder = /etc/mysql-route
    # 运行目录
    runtime_folder = /var/run
    
    [logger]
    # 日志运行级别
    level = INFO
    
    # 主节点故障转移配置
    [routing:basic_failover]
    # 写节点地址
    bind_address=172.17.0.2
    # 写节点端口
    bind_port = 7001
    # 模式,读写
    mode = read-write
    # 主节点地址:默认情况下第一台主数据库为写主库,当第一台主数据库DOWN机后,第二台数据库被提升为主库
    destinations = 172.17.0.8:3306,172.17.0.9:3306
    
    # 从节点负载均衡配置
    [routing:balancing]
    # 绑定的IP地址
    bind_address=172.17.0.2
    # 监听的端口
    bind_port = 7002
    # 连接超时时间
    connect_timeout = 3
    # 最大连接数
    max_connections = 1024
    # 后端服务器地址
    destinations = 172.17.0.9:3306,172.17.0.10:3306
    # 模式:读还是写
    mode = read-only
    
    [keepalive]
    interval = 60
    
    • 创建日志目录

    mkdir /data/log/mysql-route
    chown root:root /data/log/mysql-route/
    
  3. 启动 MySQL Router

root@mysql-router:/usr/local/mysql-router/bin# ./mysqlrouter -c /etc/mysql-route/mysqlrouter.conf &
  1. 测试读写分离

    1. 先暂时停掉主从复制
    2. 在 master 节点插入数据,然后通过 mysql-proxy 查询
    3. 在 slave 节点插入数据,然后通过 mysql-proxy 查询
  2. 验证负载均衡

    • 读操作

    mysql -h 172.17.0.2 -uroot -P7002 -proot -e "show variables like 'hostname';"
    
    • 写操作

    mysql -h 172.17.0.2 -uroot -P7001 -proot -e "show variables like 'hostname';"
    
  3. 测试主主故障切换

    • 从库 down 机
    • 主库 sown 机
  4. 缺陷

在一主多从的情况,如果主库down机,切换到备份节点,其他从库的主库地址连接还是故障的
主库,这样就会造成一个从库slave_io线程connecting状态,造成复制延迟

  • 解决方案:

1、在切换时需要自己编写脚本触发一个shell脚本或者event来处理重连的问题。

2、不使用mysql router主主故障转移功能,而是自己使用其他方式保证mysql主库高可用。

启动两个mysql镜像,分别映射3306和3316端口
主库:3306端口
备库:3316端口

# docker run -p 3306:3306 --name mysql3306 -v /opt/mysql/data/data3306:/var/lib/mysql  -v /opt/mysql/logs/logs3306:/logs -e MYSQL_ROOT_PASSWORD=xxxxxxxxxxxx -d mysql:5.7

# docker run -p 3316:3306 --name mysql3316 -v /opt/mysql/data/data3316:/var/lib/mysql  -v /opt/mysql/logs/logs3316:/logs -e MYSQL_ROOT_PASSWORD=xxxxxxxxxxxx -d mysql:5.7

88bf必发娱乐 2

2.配置主从复制

从主库的docker容器中把默认的配置文件拷贝出来

# docker cp 37735c072370:/etc/mysql/mysql.conf.d/mysqld.cnf /opt/mysql/conf/conf3306

修改配置文件增加两行

#vim mysqld.cnf 
[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id

将修改后的配置文件拷回容器里,并重启容器

# docker cp /opt/mysql/conf/conf3306/mysqld.cnf 37735c072370:/etc/mysql/mysql.conf.d/
# docker restart 37735c072370

创建主从复制需要的mysql账户,并分配权限

mysql>CREATE USER 'repl'@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'repl123';
mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'xxx.xxx.xxx.xxx'

查看主库状态,记录下File和Position

mysql>SHOW MASTER STATUS;

88bf必发娱乐 3

修改从库配置文件

#vim mysqld.cnf 
[mysqld]
server-id=2 #设置server-id

同样拷进从库镜像,并重启容器

mysql中执行配置主库的语句

mysql>change master to master_host='xxx.xxx.xxx.xxx', #Master 服务器Ip
master_port=3306,
master_user='repl',
master_password='repl123', 
master_log_file='master-bin.000001',#Master服务器产生的日志
master_log_pos=2986;

mysql>start slave;

mysql>show slave status\G

如果slave_io_running和slave_sql_running都为yes,表示启动同步成功

88bf必发娱乐 4

建立两个客户端连接并创建测试库

88bf必发娱乐 5

主库建立student表并插入一条记录,可以看到,从库自动同步了数据,配置成功

88bf必发娱乐 6 
 
  88bf必发娱乐 7

 

 88bf必发娱乐 8 
 88bf必发娱乐 9

 

3.用mysql-proxy实现读写分离

下载mysql-proxy
https://downloads.mysql.com/archives/proxy/

解压并配置mysql-proxy

# tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
# cd mysql-proxy-0.8.5-linux-el6-x86-64bit
# vim /etc/mysql-proxy.cnf
[mysql-proxy]
user=root #运行mysql-proxy用户
admin-username=proxy #主从mysql共有的用户
admin-password=proxy123 #共有用户密码
proxy-read-only-backend-addresses=xxx.xxx.xxx.xxx:3316 #备库地址
proxy-backend-addresses=xxx.xxx.xxx.xxx:3306 #主库地址
proxy-lua-script=/usr/local/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua #指定读写分离配置文件位置
admin-lua-script=/usr/local/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/admin-sql.lua #指定管理脚本
daemon=true #以守护进程方式运行
keepalive=true #mysql-proxy崩溃时,尝试重启

修改读写分离配置文件

# vim /usr/local/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua
min_idle_connections = 1, #默认超过4个连接数时才开始读写分离,改为1
max_idle_connections = 1,

mysql-proxy的bin目录下启动mysql-proxy

# ./mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

主库和从库分别创建用户

mysql>GRANT ALL ON *.* TO 'proxy'@'xxx.xxx.xxx' identified by 'proxy123';

创建mysql-proxy的数据里连接(默认端口4040),可以看到之前验证主从复制时简历的test库和student表

88bf必发娱乐 10

 只修改从库的数据,然后通过mysql-proxy查询,可以看到读取的数据是从库修改过的数据,读分离验证成功。

主库:88bf必发娱乐 11

从库:88bf必发娱乐 12

mysql-proxy:88bf必发娱乐 13

停止从库的docker容器,通过mysql-proxy插入一条数据,主库中刷新也有新增的数据,写分离验证成功。

mysql-proxy:88bf必发娱乐 14

88bf必发娱乐,主库:88bf必发娱乐 15

从库:88bf必发娱乐 16

 

至此,mysql的主从复制已经通过mysql-proxy实现读写分离已实现。