MySQL主从复制与读写分离

原理：

使用MySQL官方提供的MySQL-Proxy来搭建读写分离：
MySQL-Proxy在客户端请求与数据库DB之间建立了一个连接池，
所有客户端请求都是发向MySQL-Proxy，
然后经由MySQL-Proxy进行相应的分析，
判断出是读操作还是写操作，
分发至对应的数据库DB上。

主数据库负责写操作，
从数据库负责读操作，
主从数据库利用MySQL的二进制日志文件，
实现数据同步，
二进制日志由主数据库产生，
从数据库响应并同步数据库。

本次以内网3台数据库为例：

内网18：192.168.40.18（mysql-proxy代理）

内网32：192.168.40.32（master主）

内网32：192.168.40.38（slave从）


1、主从复制：

vim /etc/my.cnf
修改主数据库配置文件

在[mysqld]里面增加：
server-id=32     # 设置主数据库的唯一id，默认是1
log-bin=mysql-bin # 启用二进制日志
binlog-ignore-db = mysql,information_schema  # 忽略写入binlog的库

systemctl restart mysqld
重启主数据库


vim /etc/my.cnf
修改从数据库配置文件

在[mysqld]里面增加：
server-id=38                # 设置从数据库唯一id
replicate-do-db = test_db   # 只同步主数据库的test_db库
slave-skip-errors = all     # 忽略因复制出现的所有错误

systemctl restart mysqld
重启从数据库


mysql -uroot -p
登录主数据库

GRANT REPLICATION SLAVE ON *.* TO 'test'@'192.168.40.38' IDENTIFIED BY 'abcdef123456';
在主数据库上建立slave帐户并授权

FLUSH PRIVILEGES;
刷新权限

show master status;
查看主数据库状态

FLUSH TABLES WITH READ LOCK;
主数据库锁表
防止有写入操作

quit;
退出


mysql -uroot -p
登录从数据库

mysql> change master to
 -> master_host='192.168.40.32',
 -> master_user='test',
 -> master_password='abcdef123456',
 -> master_log_file='mysql-bin.000045';
配置从数据库

start slave;
启动slave同步进程

show slave status\G
查看状态

quit;
退出

UNLOCK TABLES;
主数据库解锁

此时，
在主数据库创建test_db库，
在从数据库就能看见了。




2、安装mysql-proxy实现读写分离

wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz
下载通用版

tar zxvf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz
解压

mv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy
移动

cd /usr/local/mysql-proxy
进入/usr/local/mysql-proxy目录

mkdir lua
创建脚本存放目录

mkdir logs
创建日志目录

cp share/doc/mysql-proxy/rw-splitting.lua ./lua
复制读写分离配置文件

cp share/doc/mysql-proxy/admin-sql.lua ./lua
复制管理脚本

vim /etc/mysql-proxy.cnf
创建配置文件:

[mysql-proxy]
daemon=true     
#以守护进程方式运行
user=root
#运行mysql-proxy用户
keepalive=true
#mysql-proxy崩溃时，尝试重启
admin-username=proxy
#主从mysql共有的用户
admin-password=zxcvbn123456
#主从mysql共有的用户的密码
proxy-address=192.168.40.18:4000
#mysql-proxy运行ip和端口，不加端口，默认4040
proxy-read-only-backend-addresses=192.168.40.38:3306
#指定后端从slave读取数据
proxy-backend-addresses=192.168.40.32:3306
#指定后端主master写入数据
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)

chmod 660 /etc/mysql-porxy.cnf
授权

vim /usr/local/mysql-proxy/lua/rw-splitting.lua
修改读写分离配置文件：
这一段：
if not proxy.global.config.rwsplit then
 proxy.global.config.rwsplit = {
  min_idle_connections = 1, # 默认超过4个连接数时，才开始读写分离，改为1
  max_idle_connections = 1, # 默认8，改为1
  is_debug = false
 }
end


vim /etc/profile
配置环境变量:

LUA_PATH="/usr/local/mysql-proxy/share/doc/mysql-proxy/?.lua"
export LUA_PATH
export PATH=$PATH:/usr/local/mysql-proxy/bin

source /etc/profile
使生效

mysql-proxy -V
查看版本

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

（或者mysql-proxy --defaults-file=/etc/mysql-proxy.cnf）

启动

netstat -tupln | grep 4000
查看4000端口

killall -9 mysql-proxy
停止mysql-proxy服务

（需要时才用到）



grant all on *.* to 'proxy'@'192.168.40.18' identified by 'zxcvbn123456';
在主数据库创建proxy用户用于mysql-proxy使用，
从数据库也会同步这个操作



mysql -u proxy -h 192.168.40.18 -P 4000 -pzxcvbn123456
在主或从数据库登录mysql-proxy

（或者用SQLyog客户端连接）