mysql-proxy实现读写分离

作者: 手扶拖拉机_6e4d | 来源:发表于2020-12-07 23:36 被阅读0次

1.下载mysql-proxy
可以在我的百度云下载：
https://pan.baidu.com/s/1oez2Nk9wjYXNONyUmfTiBA 密码:ubcy

准备三台机器：

192.168.207.129 hadoop2
192.168.207.130 hadoop3
192.168.207.131 hadoop4

我在hadoop4这台机器安装mysql-proxy, hadoop2当作master使用，hadoop3当作salve使用(需要配置hadoop2与hadoop3为主从关系，跳转到第13步)

2.在/usr/local目录下创建mysql-proxy目录，使用FileZilla或者xftp等工具上传到mysql-proxy目录下

3.解压安装包
tar -zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

4.修改解压后的目录
mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy

5.进入mysql-proxy的目录
cd mysql-proxy

[root@hadoop4 mysql-proxy]# mkdir conf
[root@hadoop4 mysql-proxy]# mkdir logs

7.添加环境变量

# mysql-proxy
export PATH=$PATH:/usr/local/mysql-proxy/mysql-proxy/bin

执行命令让环境变量生效
source /etc/profile

9.进入conf目录，创建文件并添加以下内容
vim mysql-proxy.conf
添加内容：

[mysql-proxy]
user=root
# admin-username=slave #主从mysql共有的用户
# admin-password=xxxx #用户的密码
#proxy-address=192.168.207.131:4040
proxy-address=0.0.0.0:4040  #监听本机中所有IP的端口
proxy-backend-addresses=192.168.207.129:3306 # 进行写的数据库
proxy-read-only-backend-addresses=192.168.207.130:3306
proxy-lua-script=/usr/local/mysql-proxy/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
log-file=/usr/local/mysql-proxy/mysql-proxy/logs/mysql-proxy.log  # 日志文件
pid-file=/usr/local/mysql-proxy/mysql-proxy/logs/mysql-proxy.pid  # pid文件
plugins=proxy    # proxy插件
log-level=debug  # #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
keepalive=true   # 心跳检测,mysql-proxy崩溃时，尝试重启
daemon=true # 以守护进程方式运行

10.开启mysql-proxy
10.1> 通过编写lua脚本实现读写分离
修改lua脚本，默认超过4个连接才会启用读写分离，改为超过2个连接启用读写分离

cd /usr/local/mysql-proxy/mysql-proxy/share/doc/mysql-proxy
vim rw-splitting.lua

35E867B1-8CD1-415D-8C8D-71FDBA555CC1.png

10.2>在启动前我们需要为配置文件加上660权限，没有修改权限，则会报错

chmod 660 /usr/local/mysql-proxy/mysql-proxy/conf/mysql-proxy.conf

10.3>启动时报错如下:

[root@hadoop4 conf]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/mysql-proxy/conf/mysql-proxy.conf
2020-08-24 23:10:29: (critical) Key file contains key 'keepalive' which has value that cannot be interpreted.
2020-08-24 23:10:29: (message) Initiating shutdown, requested from mysql-proxy-cli.c:367
2020-08-24 23:10:29: (message) shutting down normally, exit code is: 1

则需要把mysql-proxy.conf文件中的注释全部去掉才行
再次尝试启动：

[root@hadoop4 conf]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/mysql-proxy/conf/mysql-proxy.conf
[root@hadoop4 conf]#

11.查看是否安装成功，打开日志文件

[root@hadoop4 logs]# tail -100 mysql-proxy.log 
2020-08-24 23:13:23: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=4842 alive
2020-08-24 23:13:23: (debug) chassis-unix-daemon.c:157: waiting for 4842
2020-08-24 23:13:23: (debug) chassis-unix-daemon.c:121: we are the child: 4842
2020-08-24 23:13:23: (critical) plugin proxy 0.8.5 started
2020-08-24 23:13:23: (debug) max open file-descriptors = 1024
2020-08-24 23:13:23: (message) proxy listening on port 0.0.0.0:4040
2020-08-24 23:13:23: (message) added read/write backend: 192.168.207.129:3306
2020-08-24 23:13:23: (message) added read-only backend: 192.168.207.130:3306
2020-08-24 23:13:23: (debug) now running as user: root (0/0)

12.当我在使用Navicat Premium连接到hadoop4这台机器的时候，报错如下：

Lost connection to MySQL server at 'reading initial communication packet', system error: 0 "Internal

解决方法：
vim /etc/my.cnf

[mysqld]
skip-grant-tables

连接到hadoop3这台机器时候，报从如下：

2003 - Can't connect to MySQL server on 'hadoop3' (61 "Connection refused")

没有启动mariadb导致的: systemctl start mariadb

13.配置mysql主从
13.1>主数据库(hadoop2)master的配置：

grant  replication  SLAVE on *.* to 'salve'@'192.168.207.%' identified by 'xxxx';

192.168.207.130 是slave(hadoop3)的ip地址
slave 是新创建的用户名
xxxx 是新创建用户的密码

如果遇到以下报错:

ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

需要执行: flush privileges;

配置主从服务器：
主库(hadoop2:192.168.207.129)：


change master to master_host='192.168.207.130',master_port=3306, master_user='master', master_password='yuliang123';

从库(hadoop3:192.168.207.130):


change master to master_host='192.168.207.129',master_port=3306, master_user='slave', master_password='yuliang123';

13.2>从数据库(hadoop3)的配置


MariaDB [(none)]> grant  replication slave on *.* to 'master'@'192.168.207.%' identified by 'yuliang123';
MariaDB [(none)]> flush privileges;

13.3>修改mysql数据库配置文件

在主库(hadoop2)创建一个database: CREATE DATABASE IF NOT EXISTS csc DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

6DA365F1-691F-4369-B5F1-2D4DC9931EDB.png

开启binlog日志

MariaDB [(none)]> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/master       |
| log_bin_compress                | OFF                         |
| log_bin_compress_min_len        | 256                         |
| log_bin_index                   | /var/lib/mysql/master.index |
| log_bin_trust_function_creators | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
7 rows in set (0.030 sec)

编辑主库(hadoop2)配置文件 vim /etc/my.cnf

# 配置服务器的服务号
server-id = 1

# 指定数据库操作的日志文件  (/var/lib/mysql/)
log-bin = master

# 指定要同步的数据库，如果有多个，请重复该行内容并修改数据库名称为对应数据库
replicate-do-db = csc

# 指定不需要同步的数据库
replicate-ignore-db = mysql
replicate-ignore-db = information_schema

# 设置日志文件得最大值，超过该值则另启文件记录日志
max_binlog_size= 500M

# 设置日志文件缓存大小
binlog_cache_size= 128K

编辑从库(hadoop3)配置文件 vim /etc/my.cnf

server-id = 2 
log-bin = master
replicate-do-db = csc
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
max_binlog_size= 500M
binlog_cache_size= 128K

配置完成后需要重启mariadb
systemctl stop mariadb
systemctl start mariadb

启动主从服务器
保证两台主机初始数据一致后，可进行如下操作，以启动同步功能,以让mysql从头开始进行同步

在master主机上:

MariaDB [(none)]> reset master;
Query OK, 0 rows affected (0.004 sec)
MariaDB [csc]> reset slave;
ERROR 1198 (HY000): This operation cannot be performed as you have a running slave ''; run STOP SLAVE '' first
MariaDB [csc]> STOP SLAVE;
Query OK, 0 rows affected (0.021 sec)
MariaDB [csc]> reset slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [csc]> START slave;
Query OK, 0 rows affected (0.005 sec)

在slave主机上:

MariaDB [(none)]> reset  slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> START slave;
Query OK, 0 rows affected, 1 warning (0.012 sec)

查看master和slave状态

主库(hadoop2)上执行:show master status;

+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| master.000001 |      325 |              |                  |

从库(hadoop3)上执行: show slave status \G

4BC5365F-23EB-4996-A4A2-44727E8C2578.png

注意：Slave两个关键进程

mysql replication 中slave机器上有两个关键的进程，死一个都不行，一个是Slave_SQL_Running，一个是Slave_IO_Running，一个负责与主机的io通信，一个负责自己的slave mysql进程

出现 Slave_IO_Running: No 的原因：
从库的Master_Log_File与主库的master.000001没有对应
解决 Slave_IO_Running: No 的问题：
停止slave: STOP slave;

change master to master_host='192.168.207.129',master_port=3306, master_user='slave', master_password='yuliang123', master_log_file='master.000001', master_log_pos=325;

flush privileges;

master_log_file = 主库的 File
master_log_pos = 主库的Position

解决Slave_SQL_Running: No 的问题：
停止salve: STOP slave

MariaDB [(none)]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.042 sec)

MariaDB [(none)]> START slave;
Query OK, 0 rows affected, 1 warning (0.004 sec)

MariaDB [(none)]> show salve status \G

第二次遇到该问题的解决办法：
在navicat Premiun 工具里面执行:

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

1BE69F71-176B-4E6E-A993-9892A44F2F73.png

网友评论

本文标题：mysql-proxy实现读写分离

本文链接：https://www.haomeiwen.com/subject/vvpojktx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

mysql-proxy实现读写分离

开启binlog日志