MySQL读写分离和多实例

目录

 

〔1〕读写分离概述

〔2〕读写分离构建

〔3〕MySQL多实例

---

〔1〕读写分离概述

• 主从结构的数据库服务,所有的请求都由主数据库服务器承担,并发压力大,处理性能要求高,因此采用MySQL代理对请求的访问类型进行分离,对读的请求转发给主数据库服务器(主库),对写的请求转发给从数据库服务器(从库),即读写分离,达到负载均衡的效果.

〔2〕读写分离构建

• 步骤一: 部署一主一从结构
  • 主服务器:192.168.4.51 从服务器:192.168.4.52

具体步骤参考<MySQL主从备份结构和模式>笔记.

• 步骤二: MySQL代理
  • 采用软件MaxScale,由MariaDB公司开发,网上可免费下载.
  • 代理主机IP: 192.168.4.57
  • 安装软件包
    • yum -y install maxscale-2.1.2-1.rhel.7.x86_64.rpm
  • /etc/maxscale.cnf配置:
    • [maxscale]
      • threads=1 //代理服务线程数,值可设置为auto
    • [server1] //数据库节点信息
      • type=server
      • address=192.168.4.51
      • port=3306
      • protocol=MySQLBackend
    • [server2] //数据库节点信息
      • type=server
      • address=192.168.4.52
      • port=3306
      • protocol=MySQLBackend
    • [MySQL Monitor] //代理监控服务器节点运行状态
      • type=monitor
      • module=mysqlmon
      • servers=server1,server2 //监控数据库服务器列表
      • user=maxscalemon //监控用户,自定义,连接各数据库,监控状态
      • passwd=123456 //监控用户密码,自定义
    • #[Read-Only Service] //该标签下的内容全部注释,包括标签
    • [Read-Write Service]
      • type=service
      • router=readwirtesplit
      • servers=server1,server2 //读写数据库服务器列表
      • user=maxscaleroute //路由用户,自定义,用于验证访问请求用户是否存在
      • passwd=123456 //路由用户密码,自定义
      • max_slave_connections=100%
    • #[Read-Only Listener] //该标签下的内容全部注释,包括标签
    • [Read-Write Listener]
      • type=listener
      • service=Read-Write Service
      • protocol=MySQLClient
      • port=4006 //读写分离服务接收外部访问请求的服务端口
    • [MaxAdmin Listener]
      • type=listener
      • service=MaxAdmin Service
      • protocol=maxscaled
      • port=4016 //MaxScale管理器服务的端口,自定义
• 步骤三: 主库创建授权用户
  • 监控用户maxscalemon,与代理设置的用户名密码一致
    • grant replication slave,replication client on *.* to maxscale@'%' identified by '123456';
  • 路由用户maxscaleroute,与代理设置的用户名密码一致
    • grant select on mysql.* to maxscaleroute@'%' identified by '123456';
  • 测试用户,假设yaya用户对db7库拥有所有权限
    • grant all on db7.* to yaya@'%' identified by '123456';
• 步骤四: 启动服务,查看连接状态和客户端访问
  • 启动服务
    • maxscale -f /etc/maxscale.cnf
    • netstat -nultp | grep maxscale
  • 停止服务
    • killall maxscale
  • 登录maxscale管理器查看各个数据库的连接状态
    • 格式: maxadmin -uadmin -pmariadb -P端口
    • maxadmin -uadmin -pmariadb -P4016
    • list servers //查看连接状态,运行状态正常显示(master,running)(slave,running),不正常请参考日志文件/var/log/maxscale/maxscale.log
  • 使用测试用户从客户端访问代理,验证读写分离
    • 远程连接格式: mysql -h代理地址 -P代理端口 -u用户名 -p密码
    • 连接代理: mysql -h192.168.4.57 -P4006 -uyaya -p123456
    • 验证读写分离: 从库中对表插入数据,不会同步给主库,客户端查询数据,若有则分离成功.主库写入会自动同步到从库.

 

〔3〕MySQL多实例

• 多实例概述: 一台物理主机上可以运行多个数据库服务.其优势在于节约运维成本,提高硬件利用率.
• 多实例配置: 需要使用支持多实例服务的数据库,这里我们使用mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
  • 解压文件: tar -zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
  • 修改目录: mv mysql-5.7.20-linux-glibc2.12 /usr/local/mysql
  • 编写配置文件/etc/my.cnf,完全手写,你可以的
    • [mysqld_multi] //启用多实例标签
      • mysqld=/usr/local/mysql/bin/mysqld_safe //指定进程名和进程文件路径
      • mysqladmin=/usr/local/mysql/bin/mysqladmin //指定管理命令名称和路径
      • user=root //指定进程用户
    • [mysqld1] //实例标签定义为[mysqldX],其中X代表实例编号
      • port=3307 //实例端口号
      • datadir=/dir1 //实例数据库目录
      • socket=/dir1/mysql1.sock //指定sock文件名称和路径
      • pid-file=/dir1/mysqld1.pid //指定pid文件名称和路径
      • log-error=/dir1/mysqld1.log //指定错误日志文件名称和路径
    • [mysqld2] //定义实例2
      • port=3308
      • datadir=/dir2
      • socket=/dir2/mysql2.sock
      • pid-file=/dir2/mysqld2.pid
      • log-error=/dir2/mysqld2.log
  • 创建个实例数据库目录
    • mkdir /dir1 /dir2
  • 配置应用环境
    • vim /etc/profile 末尾添加一行
      • export PATH=/usr/local/mysql/bin:$PATH
    • soucre /etc/profile //刷新
  • 启动服务
    • 格式: mysqld_multi start 实例编号
    • mysqld_multi start 1 //启动后生成一个初始随机密码,登录后修改密码
  • 本机登录修改密码
    • 格式: mysql -uroot -p'初始密码' -S sock文件路径
    • mysql -uroot -p'初始密码' -S /dir1/mysql1.sock
    • alter user root@localhost identified by '123456';
  • 停止服务
    • 格式: mysqld_multi --user=root --password=密码 stop 实例编号
    • mysqld_multi --user=root --password=123456 stop 1
  • 客户端访问
    • 在mysqld1上创建授权用户yaya,密码123456
    • 访问连接格式: mysql -h实例IP地址 -P实例端口 -u用户 -p密码
    • 连接: mysql -h192.168.4.57 -P3307 -uyaya -p123456
• 启用多实例的binlog日志
  • 在配置文件,需要启用日志的实例下添加,如实例2
    • log-bin=mysqld2
    • server_id=2
  • 重启实例服务