首页 > 代码库 > 搭建MySQL代理服务器实现读写分离+主从同步
搭建MySQL代理服务器实现读写分离+主从同步
实验需求：
1.配置2台MySQL服务器（192.168.100.2,192.168.100.3)+1台代理服务器(192.168.100.1)，实现MySQL代理的读写分离。
2.用户只需要访问MySQL代理服务器，实际的SQL查询、写入操作交给后台的2台MySQL服务器来完成。
3.2台MySQL服务器实现主从同步，其中Master服务器允许SQL查询、写入，Slave服务器只允许SQL查询。
一 .MASTER数据库服务器（192.168.100.2）的配置
1.安装软件包（本实验采用MySQL5.6版本）
# rpm -qa | grep -i mysql
MySQL-shared-5.6.15-1.el6.x86_64
MySQL-devel-5.6.15-1.el6.x86_64
MySQL-embedded-5.6.15-1.el6.x86_64
MySQL-test-5.6.15-1.el6.x86_64
MySQL-server-5.6.15-1.el6.x86_64
MySQL-client-5.6.15-1.el6.x86_64
MySQL-shared-compat-5.6.15-1.el6.x86_64
2.启动服务并登录数据库本机对代理服务器做用户授权
# service mysql start && chkconfig mysql on
mysql> grant all on *.* to proxyuser@"192.168.100.1" identified by "123456";
3.创建测试数据库和表
mysql> create database proxydb;
mysql> create table proxydb.proxytab(id int);
mysql> insert into proxydb.proxytab values (2);
二 .SLAVE数据库服务器（192.168.100.3）的配置
1.安装软件包
2.启动服务并登录数据库本机对代理服务器做用户授权
# service mysql start && chkconfig mysql on
mysql> grant all on *.* to proxyuser@"192.168.100.1" identified by "123456"; //与MASTER数据库服务器做一样的授权
3.创建测试数据库和表
mysql> create database proxydb; //创建一样的库和表
mysql> create table proxydb.proxytab(id int);
mysql> insert into proxydb.proxytab values (3); //为了能测试区分2台不同数据库服务器，先插入不同数据
三.MySQL代理服务器配置
1.在做代理服务器上测试授权用户能否登录2台数据库服务器
# mysql -h192.168.100.2 -uproxyuser -p123456 //登录MASTER服务器
mysql> select * from proxydb.proxytab;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
# mysql -h192.168.100.3 -uproxyuser -p123456 //登录SLAVE服务器
mysql> select * from proxydb.proxytab;
+------+
| id |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
2.释放3306端口
# service mysqld stop
# chkconfig mysqld off
# netstat -tulnp | grep :3306
3.安装提供代理服务器的软件包
# tar zxvf mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz
# mv mysql-proxy-0.8.3-linux-rhel5-x86-64bit /usr/local/mysql-proxy //此包为绿色免安装
# yum -y install lua //此软件使用LUA脚本语言，因此需要安装好lua软件包
代理服务启动时 会设置 负责写的数据库 负责读数据库服务器
调用脚本来区分用户的访问类型
指定自己监听的端口
4.启动代理服务
cd /usr/local/mproxy/bin/
使用bin目录下的mysql-proxy脚本来启动代理服务
# ./mysql-proxy -P 192.168.100.1:3306 -r 192.168.100.3:3306 -b 192.168.100.2:3306 -s /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &
主要命令选项：
-P：指定代理监听的IP地址、端口
-r：指定读服务器的IP地址、端口
-b：指定写服务器的IP地址、端口
-s：指定lua脚本文件
--keepalive：如果服务进程崩溃，尝试重启此进程
5.查看代理进程是否运行
# jobs
# netstat -tulnp | grep :3306
tcp 0 0 192.168.100.1:3306 0.0.0.0:* LISTEN 3314/mysql-proxy
//代理进程必须处于运行状态，客户端才能通过代理服务访问到后端的数据库服务器。
6.查看读写分离脚本
# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
……
40 min_idle_connections = 4, //默认并发请求大于4才实行读写分离
41 max_idle_connections = 8,
四.客户端通过代理访问数据库服务器上的数据。
# mysql -h192.168.100.1 -uproxyuser -p123456 //测试客户端第一个连接
mysql> select * from proxydb.proxytab; //测试访问到的是哪台数据库服务器
+------+
| id |
+------+
| 2 |
+------+ //分配给了写的那台数据库服务器
1 row in set (0.00 sec)
# mysql -h192.168.100.1 -uproxyuser -p123456 //测试客户端第二到第N个连接
mysql> select * from proxydb.proxytab;
测试结果：
并发访问量大于默认的最小并发连接输4时才区分读写请求
并发访问量小于并发连接数时请求会发给写的那台服务器
并发访问量缩减到小于阀值后依然区分读写请求
四.将2台MySQL数据库服务器配置主从同步架构
1.MASTER数据库服务器（192.168.100.2）配置
1.1 实现MySQL主从同步必须保证从数据库服务器上有主数据库服务器上的库和表，且表结构也要一致。通过备份主服务器上现有的库，然后导入到从服务器上。
当现有库、表都采用MyISAM引擎时，可执行离线备份、恢复，这样更有效率；否则，可通过mysqldump等工具来实现库的导出、导入。
# mysqldump -uroot -p123456 --all-databases > /root/all.sql
# scp /root/all.sql 192.168.100.3:/root/all.sql //通过scp传给SLAVE服务器
1.2 修改/etc/my.cnf配置，重新启动MySQL服务程序
# vim /etc/my.cnf
[mysqld]
log_bin //主服务器一定要启用binlog日志
server_id =2 //指定服务器ID号
innodb_flush_log_at_trx_commit=1 //优化设置
sync-binlog=1 //允许日志同步
# service mysql restart
1.3 管理员登录数据库授予SLAVE服务器复制权限
mysql> grant replication slave on *.* to slave@"192.168.100.3" identified by "123456";
1.4 检查Master服务器的同步状态
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| localhost-bin.000004 | 766 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.MASTER数据库服务器（192.168.100.3）配置
2.1 SLAVE服务器上导入备份的库
# mysql -uroot -p123456 < /root/all.sql
2.2 修改/etc/my.cnf配置，重新启动MySQL服务程序
# vim /etc/my.cnf
[mysqld]
log_bin //启用binlog日志，从服务器也可不开启
server_id =3 //指定服务器ID号，不能和主相同
innodb_flush_log_at_trx_commit=1 //优化设置
sync-binlog=1 //允许日志同步
log_slave_updates=1 //记录从库更新，便于实现“主-从-从”链式复制
# service mysql restart
2.3 管理员登入本机数据库服务，发起同步操作
mysql> change master to
-> master_host="192.168.100.2", //对应Master的IP地址
-> master_user="slaveuser", //同步时用户名
-> master_password="123456", //同步时密码
-> master_log_file="localhost-bin.000004", //对应Master的日志文件
-> master_log_pos=766; //对应Master的日志偏移位置
Query OK, 0 rows affected, 2 warnings (0.10 sec)
mysql> start slave; //一旦启用SLAVE复制，当需要修改MASTER信息时，应先执行stop slave;停止复制，然后重新修改、启动复制。
2.4 查看从数据库服务器的状态
mysql> show slave status\G;
……
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
//若START SLAVE直接报错失败，请检查CHANGE MASTER相关设置是否有误，纠正后再重试；若IO线程或SQL线程有一个为“No”，则应检查服务器的错误日志，分析并排除故障后重启主从复制。
2.5 与从数据库服务器相关的文件及作用
# ls /var/lib/mysql/
master.info //保存自己连接的主数据库服务器的信息
localhost-relay-bin.000001 //relaybinlog日志
localhost-relay-bin.index //保存已有的relaybinlog日志文件名
relay-log.info //relaybinlog日志信息
五.验证从数据库服务器是否能同步从数据库服务器上的数据
在MASTER服务器上写入数据，在SLAVE服务器上查看
mysql -uroot -p123456 //在MASTER服务器上登录
mysql> create database testdb;
mysql> create table a (id int);
insert into testdb.a values(100);
insert into testdb.a values(200);
insert into testdb.a values(300);
mysql -uroot -p123456 //在SLAVE服务器上登录
mysql> select * from testdb.a; //能够看到表里的3条记录则同步成功。