Centos7配置MySQL读写分离

系统管理员 2022-05-22 02:24 133阅读 0赞

## 背景 ##

*  82和83两台Mysql已经实现了[主从复制][Link 1]。
 *  即将使用mysql-proxy进行读写分离，配置192为proxy,写82读83。

## mysql-proxy ##

*  mysql-proxy是一个处于客户端和服务器之间的中间件，它可以检测，分析或改变它们的通信。简单说就是一个中间层代理，一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过lua脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负载平衡。对于应用来说只需要连接到mysql-proxy的监听端口即可(默认4040)

## 下载mysql-proxy ##

https://downloads.mysql.com/archives/proxy/   根据系统选择

## 解压 ##

<table> 
 <tbody> 
 <tr> 
 <td><pre>1
2
</pre></td> 
 <td style="width:654.55px;"><pre style="width:654.55px;">tar -zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz /usr/local/mysql-proxy
</pre></td> 
 </tr> 
 </tbody> 
</table>

## 新建文件夹 ##

<table> 
 <tbody> 
 <tr> 
 <td><pre>1
2
</pre></td> 
 <td style="width:654.55px;"><pre style="width:654.55px;">mkdir /usr/local/mysql-proxy/lua 创建脚本存放目录
mkdir /usr/local/mysql-proxy/logs 创建日志目录
</pre></td> 
 </tr> 
 </tbody> 
</table>

## 复制配置文件 ##

<table> 
 <tbody> 
 <tr> 
 <td><pre>1
2
</pre></td> 
 <td style="width:654.55px;"><pre style="width:654.55px;">cp share/doc/mysql-proxy/rw-splitting.lua ./lua #复制读写分离配置文件
cp share/doc/mysql-proxy/admin-sql.lua ./lua #复制管理脚本
</pre></td> 
 </tr> 
 </tbody> 
</table>

## 创建配置文件 ##

<table> 
 <tbody> 
 <tr> 
 <td><pre>1
</pre></td> 
 <td style="width:654.55px;"><pre style="width:654.55px;">vi /etc/mysql-proxy.cnf #创建配置文件
</pre></td> 
 </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
 <tr> 
 <td><pre>1
2
3
4
5
6
7
8
9
10
11
12
13
</pre></td> 
 <td style="width:647.4px;"><pre style="width:647.4px;">[mysql-proxy]
user=root 运行mysql-proxy用户
admin-username=proxy 主从mysql共有的用户
admin-password=proxy 用户的密码
proxy-address=10.120.112.192:4040 mysql-proxy运行ip和端口，不加端口，默认4040
proxy-read-only-backend-addresses=10.120.114.83 指定后端从slave读取数据
proxy-backend-addresses=10.120.114.82 指定后端主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=debug 定义log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true 以守护进程方式运行
keepalive=true mysql-proxy崩溃时，尝试重启
</pre></td> 
 </tr> 
 </tbody> 
</table>

## 修改读写分离配置 ##

<table> 
 <tbody> 
 <tr> 
 <td><pre>1
</pre></td> 
 <td style="width:654.55px;"><pre style="width:654.55px;">vi /usr/local/mysql-proxy/lua/rw-splitting.lua
</pre></td> 
 </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
 <tr> 
 <td><pre>1
2
3
4
5
6
7
</pre></td> 
 <td style="width:654.55px;"><pre style="width:654.55px;">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
</pre></td> 
 </tr> 
 </tbody> 
</table>

## 启动mysql-proxy ##

<table> 
 <tbody> 
 <tr> 
 <td><pre>1
2
3
4
5
</pre></td> 
 <td style="width:654.55px;"><pre style="width:654.55px;">/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

netstat -tupln | grep 4040 #已经启动

killall -9 mysql-proxy #关闭mysql-proxy使用
</pre></td> 
 </tr> 
 </tbody> 
</table>

## 测试读写分离 ##

### 创建Proxy用户 ###

在主服务器创建Proxy用户用户mysql-proxy使用，从服务器也会同步这个操作

<table> 
 <tbody> 
 <tr> 
 <td><pre>1
</pre></td> 
 <td style="width:654.55px;"><pre style="width:654.55px;">grant all on *.* to 'proxy'@10.120.112.192' identified by 'proxy';
</pre></td> 
 </tr> 
 </tbody> 
</table>

### 连接mysql-proxy ###

使用客户端连接代理

<table> 
 <tbody> 
 <tr> 
 <td><pre>1
</pre></td> 
 <td style="width:654.55px;"><pre style="width:654.55px;">mysql -u proxy -h 10.120.112.192 -P 4040 -p proxy;
</pre></td> 
 </tr> 
 </tbody> 
</table>

## 测试思路 ##

### 测试写 ###

关闭83从服务器的slave服务，在proxy192上执行insert操作，发现新增的数据都在82主服务器上。

### 测试读 ###

在proxy192上执行select语句，发现查出来的数据并不包括刚才insert的数据，说明读的是83从服务器上的数据。

## 切换读库引擎 ##

* 读库默认引擎是InnoDB，修改为MyISAM会使查询速度变快。
 * 修改从服务器/etc/my.cnf
 
 <table> 
 <tbody> 
 <tr> 
 <td><pre>1
 2
 3
 4
 5
 6
 7
 </pre></td> 
 <td style="width:614.55px;"><pre style="width:614.55px;">[mysqld]
 default-storage-engine=MyISAM 创建新表时将使用的默认存储引擎 
 key_buffer_size=128M
 myisam_sort_buffer_size=256M
 myisam_max_sort_file_size=5G
 myisam_repair_threads=1
 myisam_recover
 </pre></td> 
 </tr> 
 </tbody> 
 </table>
 * [批量修改引擎][Link 2]

## 乱码问题 ##

通过mysql-proxy读写分离查询的时候，可能会出现乱码。原因是当我们使用mysql-proxy读写分离时，客户端发出查询请求前一般会先发送一条’set names gbk’的语句来声明客户端编码，然后再发出实际查询的SQL语句，若数据库编码和客户端发出的不一致则会乱码。

解决方法是强行执行后端读服务器的字符编码，编辑 /etc/my.cnf文件：

<table> 
 <tbody> 
 <tr> 
 <td><pre>1
2
3
4
</pre></td> 
 <td style="width:654.55px;"><pre style="width:654.55px;">[mysqld]
init-connect='SET NAMES UTF8'
skip-character-set-client-handshake
character-set-server=utf8</pre></td> 
 </tr> 
 </tbody> 
</table>

[Link 1]: http://www.houqi.tech/2018/04/26/Centos7%E4%B9%8BMySQL%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/
[Link 2]: http://www.houqi.tech/2018/04/27/%E6%89%B9%E9%87%8F%E4%BF%AE%E6%94%B9%E6%95%B0%E6%8D%AE%E5%BA%93%E5%BC%95%E6%93%8E/