Mysql Proxy实现 Mysql读写分离
一、介绍
在我们使用mysql-proxy之前，我们必须先要完成mysql的“主从复制”或者叫“读写分离”，先搭建完成主从复制再使用mysql-proxy。
主从复制分为同步复制和异步复制两种，实际复杂架构中大部分都是异步复制。
主从(Master-Slave)复制的基本过程：
a).Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置之后的日志内容。
b).Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取指定日志位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master的bin-log文件的名称以及bin-log的位置。
c).Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master的bin-log的文件和位置记录到master-info文件中，以便在下次读取的时候能够清楚的告诉Master（我需要从某个bin-log的那个位置开始往后的日志内容，请发给我）。
d).Slave的sql进程检测到relay-log中新增了内容后，会马上解析relay-log的内容成为在Master上真实执行时候的可执行内容，并在自身执行。
二、环境准备
1、三台Linux虚拟机
2、Linux版本为ubuntukylin-16.04-desktop-amd64
3、Mysql 5.7.20
4、Mysql-Proxy 0.8.1
5、lua 5.2
地址：192.168.1.127（mysql-proxy） 192.168.1.128（master） 192.168.1.129（slave）
三、配置主从复制（读写分离）
3.1安装mysql-server、mysql-client
在master和slave上分别执行：
# sudo apt-get install mysql-server mysql-client
在安装过程中会让你指定root用户的密码：
输入密码：123456
确认密码：123456
3.2 修改master、slave服务器
安装vim编辑器
# sudo apt install vim
编辑：mysqld.cnf
vim /etc/mysql/mysql.conf.d/mysqld.cnf
master机器：
[mysqld]
server-id=1 #设置服务器唯一的id
log-bin=mysql-bin #启用二进制日志
binlog-ignore-db=mysql #忽略写入binlog的库
slave机器：
[mysqld]
server-id=2
replicate-do-db=my #只同步my库（这个可以自己建立）
slave-skip-errors=all #忽略因复制出现的所有错误
3.3 重启主从服务器的mysql
/etc/init.d/mysql restart
3.4 在master上建立帐户并授权slave
这里我使用root用户
GRANT REPLICATION SLAVE ON *.* to "root"@"192.168.1.129" IDENTIFIED BY "123456";
3.5 查看主数据库(master)状态
show master status;
3.6配置从数据库(slave)
mysql > change master to
-> master_host='192.168.1.128',
-> master_port=3306,
-> master_user='root',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=449;
3.7启动slave同步进程并查看状态
mysql> slave start;
mysql> SHOW SLAVE STATUS\G;
如果显示内容中以下两项都为Yes则表明状态正常。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
四、测试主从复制
4.1在master上登录
# mysql -uroot -p123456
创建数据库my
创建表student
4.2 在slave上登录
进行如下操作
只要可以在slave上看到你在master上操作产生的数据就说明成功。
五、安装mysql-proxy
MySQL Proxy有一项强大功能是实现“读写分离”，基本原理是让主数据库处理写方面事务，让从库处理SELECT查询。
5.1、安装mysql-proxy
实现读写分离是有lua脚本实现的，现在mysql-proxy里面已经集成，无需再安装。
这里修改ubuntu的源。
# sudo vim /etc/apt/sources.list
将里面内容删除，更换为如下内容
#deb cdrom:[Ubuntu-Kylin 16.04 LTS _Xenial Xerus_ - Release amd64 (20160420.1)]/ xenial main multiverse restricted universe
# See http://help.ubuntu.com/community/UpgradeNotes for how to upgrade to
# newer versions of the distribution.
deb http://cn.archive.ubuntu.com/ubuntu/ xenial main restricted
# deb-src http://cn.archive.ubuntu.com/ubuntu/ xenial main restricted
## Major bug fix updates produced after the final release of the
## distribution.
deb http://cn.archive.ubuntu.com/ubuntu/ xenial-updates main restricted
# deb-src http://cn.archive.ubuntu.com/ubuntu/ xenial-updates main restricted
## N.B. software from this repository is ENTIRELY UNSUPPORTED by the Ubuntu
## team, and may not be under a free licence. Please satisfy yourself as to
## your rights to use the software. Also, please note that software in
## universe WILL NOT receive any review or updates from the Ubuntu security
## team.
deb http://cn.archive.ubuntu.com/ubuntu/ xenial universe
#deb cdrom:[Ubuntu-Kylin 16.04 LTS _Xenial Xerus_ - Release amd64 (20160420.1)]/ xenial main multiverse restricted universe
# See http://help.ubuntu.com/community/UpgradeNotes for how to upgrade to
# newer versions of the distribution.
deb http://cn.archive.ubuntu.com/ubuntu/ xenial main restricted
# deb-src http://cn.archive.ubuntu.com/ubuntu/ xenial main restricted
## Major bug fix updates produced after the final release of the
## distribution.
deb http://cn.archive.ubuntu.com/ubuntu/ xenial-updates main restricted
# deb-src http://cn.archive.ubuntu.com/ubuntu/ xenial-updates main restricted
## N.B. software from this repository is ENTIRELY UNSUPPORTED by the Ubuntu
## team, and may not be under a free licence. Please satisfy yourself as to
## your rights to use the software. Also, please note that software in
## universe WILL NOT receive any review or updates from the Ubuntu security
## team.
deb http://cn.archive.ubuntu.com/ubuntu/ xenial universe
# deb-src http://cn.archive.ubuntu.com/ubuntu/ xenial universe
deb http://cn.archive.ubuntu.com/ubuntu/ xenial-updates universe
# deb-src http://cn.archive.ubuntu.com/ubuntu/ xenial-updates universe
## N.B. software from this repository is ENTIRELY UNSUPPORTED by the Ubuntu
## team, and may not be under a free licence. Please satisfy yourself as to
## your rights to use the software. Also, please note that software in
## multiverse WILL NOT receive any review or updates from the Ubuntu
## security team.
deb http://cn.archive.ubuntu.com/ubuntu/ xenial multiverse
# deb-src http://cn.archive.ubuntu.com/ubuntu/ xenial multiverse
deb http://cn.archive.ubuntu.com/ubuntu/ xenial-updates multiverse
# deb-src http://cn.archive.ubuntu.com/ubuntu/ xenial-updates multiverse
## N.B. software from this repository may not have been tested as
## extensively as that contained in the main release, although it includes
## newer versions of some applications which may provide useful features.
## Also, please note that software in backports WILL NOT receive any review
## or updates from the Ubuntu security team.
deb http://cn.archive.ubuntu.com/ubuntu/ xenial-backports main restricted universe multiverse
# deb-src http://cn.archive.ubuntu.com/ubuntu/ xenial-backports main restricted universe multiverse
## Uncomment the following two lines to add software from Canonical's
## 'partner' repository.
## This software is not part of Ubuntu, but is offered by Canonical and the
## respective vendors as a service to Ubuntu users.
# deb http://archive.canonical.com/ubuntu xenial partner
# deb-src http://archive.canonical.com/ubuntu xenial partner
deb http://security.ubuntu.com/ubuntu xenial-security main restricted
# deb-src http://security.ubuntu.com/ubuntu xenial-security main restricted
deb http://security.ubuntu.com/ubuntu xenial-security universe
# deb-src http://security.ubuntu.com/ubuntu xenial-security universe
deb http://security.ubuntu.com/ubuntu xenial-security multiverse
# deb-src http://security.ubuntu.com/ubuntu xenial-security multiverse
5.2创建mysql-proxy
# vim /etc/mysql-proxy.cnf
[mysql-proxy]
admin-username=root #主从mysql共有的用户
admin-password=123456 #用户的密码
proxy-address=192.168.1.127:4000 #mysql-proxy运行ip和端口，不加端口，默认4040
proxy-read-only-backend-addresses=192.168.1.128 #从库地址
proxy-backend-addresses=192.168.1.129 #主库地址
proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua #指定读写分离配置文件位置
admin-lua-script=/usr/share/mysql-proxy/admin-sql.lua #指定管理脚本
log-file=/var/log/mysql-proxy/mysql-proxy.log #日志位置
log-level=info #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true #以守护进程方式运行
keepalive=true #mysql-proxy崩溃时，尝试重启
别忘了改变权限：
chmod 660 /etc/mysql-porxy.cnf
5.3 修改读写分离配置文件
默认最小4个最大8个以上的客户端连接才会实现读写分离,现在改为1和1
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1,
max_idle_connections = 1,
is_debug = false
}
end
启动mysql-proxy
# mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
根据mysql-proxy.cnf启动，显示如下：一切ok！
netstat -tupln | grep 4000
tcp 0 0 192.168.1.127:4000 0.0.0.0:* LISTEN 5286/mysql-proxy
5.4测试读写分离
写sql。。。。。。。。。。。。。。略
没有评论:
发表评论