mysql 官方 读写分离_MySQL读写分离

MySQL读写分离

今天介绍两种实现MySQL读写分离的工具：

1. ProxySQL

2. MySQL Router

前提：实现主从复制

172.16.75.1主机作为主MySQL服务器(Master);

172.16.75.2主机作为从MySQL服务器(Slave1);

172.16.1.12主机作为从MySQL服务器(Slave2);

Master的主配置文件：[root@bogon ~]# vim /etc/my.cnf

[mysqld]

。。。。

innodb_file_per_table=ON

skip_name_resolve = ON

expire_logs_days=15

log_bin=binlog

server_id=199

sync_binlog=1

innodb_flush_log_at_trx_commit=1

Slave1的主配置文件：[root@bogon ~]# vim /etc/my.cnf

[mysqld]

。。。。

innodb_file_per_table = ON

skip_name_resolve = ON

expire_logs_days=15

server_id=200

read_only=ON

relay_log=slavelog

Slave2的主配置文件：

[root@bogon ~]# vim /etc/my.cnf

[mysqld]

。。。。

innodb_file_per_table = ON

skip_name_resolve = ON

expire_logs_days=15

server_id=201

read_only=ON

relay_log=slavelog

配置完后将三台主机服务开启：[root@bogon ~]# systemctl start mariadb.service

将主服务器的所有数据库备份到两台从服务器：[root@bogon ~]# mysqldump --all-databases --lock-all-tables > alldb.sql

[root@bogon ~]# scp alldb.sql root@172.16.75.2:/root/

root@172.16.75.1's password:

alldb.sql                                                   100% 1689KB  10.6MB/s   00:00

[root@bogon ~]# scp alldb.sql root@172.16.1.12:/root/

root@172.16.1.12's password:

alldb.sql                                                   100% 1689KB  35.3MB/s   00:00

从服务器将主服务器的所有数据保存：[root@bogon ~]# mysql

MariaDB [(none)]> \. alldb.sql

查看是否数据一致：

主服务器：

两台从服务器：

数据一致后执行如下操作：

1. 主服务器给一个用户赋予复制权限，并设置密码：

2. 从服务器指定自己的master

3. 主服务器创建一个数据库，查看从服务器是否同步；

1. Master：

查看master当前状态：

授权：MariaDB [(none)]> grant replication slave on *.* to 'james'@'%' identified by 'ytc';

Query OK, 0 rows affected (0.01 sec)

2. Slave：(两台Slave执行相同操作)

指定Master：MariaDB [(none)]> change master to master_host='172.16.75.1',master_user='james',master_password='ytc',master_port=3306,master_log_file='binlog.000042',master_log_pos=387;

查看slave状态：

3.在主服务器创建一个名为wade的数据库并在从服务器端查看：

1)Master：MariaDB [(none)]> create database wade;

Query OK, 1 row affected (0.00 sec)

2)Slave查看：

主从数据库同步实现；

接下来实现读写分离：

1. ProxySQL

1)安装(已从官网下载)：在172.16.1.14主机上安装；[root@localhost ~]# rpm -ivh proxysql-1.4.9-1-centos7.x86_64.rpm

2)开启服务：[root@localhost ~]# service proxysql start

Starting ProxySQL: DONE!

3)将MySQL客户端工具连接到proxysql的管理接口：

默认的管理员账户和密码都为“admin”，端口为6032：[root@localhost ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032

4)向ProxySQL插入监控节点，(MySQL服务器)：

即172.16.75.1，172.16.75.2，172.16.1.12主机，组ID为"23"：MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port)values(23,'172,16.75.1',3306),(23,'172.16.75.2',3306),(23,'172.16.1.12',3306);

5)创建监控用户并设置相应的密码：

1. master端授权用户：MariaDB [(none)]> grant replication client,replication slave on *.* to 'monitor'@'172.16.%.%' identified by 'ytc';

Query OK, 0 rows affected (1.32 sec)

2. ProxySQL将master端授权的监控用户及密码写入global_variables表：MySQL [(none)]> set mysql-monitor_username='monitor';

Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> set mysql-monitor_password='ytc';

Query OK, 1 row affected (0.00 sec)

6)让此前添加的后端MySQL服务器节点及用于监控各节点的用户生效：MySQL [(none)]> load mysql servers to runtime;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql servers to disk;

Query OK, 0 rows affected (0.12 sec)

MySQL [(none)]> load mysql variables to runtime;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql variables to disk;

Query OK, 95 rows affected (0.01 sec)

7)将写组ID设置为“23”，读组的ID设置为“24”；

并将修改后的数据加载至runtime及持久化存储至磁盘，及让配置生效：MySQL [(none)]> insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup)values(23,24);

Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> load mysql servers to runtime;

Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> save mysql servers to disk;

Query OK, 0 rows affected (0.02 sec)

8)查看分组和后端MySQL服务器的read_only属性情况：

可以看出:

172.16.75.1服务器被自动划分到了写组中(hostgroup_id: 23)，read_only=0,可写；

172.16.75.2和172.16.1.12被划分到了读组中(hostgroup_id: 24)，read_only=1,只读；

这样读写分离就实现了，Slave只可读，Master才可写；

此外还可以将不同用户发来的请求发送到指定的后端MySQL服务器上：

例如：将名为"vip"用户发来的请求发送到写组上，及master服务器；

将名为"other"用户发来的请求发送到读组上，及Slave服务器；

1.  在Master服务器上创建相应的用户：MariaDB [(none)]> grant all on *.* to 'vip'@'172.16.%.%' identified by '123';

Query OK, 0 rows affected (0.84 sec)

MariaDB [(none)]> grant all on *.* to 'other'@'172.16.%.%' identified by '123';

Query OK, 0 rows affected (0.01 sec)

2.  在ProxySQL上将master创建的用户信息添加到mysql_users表，

并设置默认的组：

将vip用户的请求设置发送到写组，

将other用户的请求设置发送到读组，MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup)

values ('vip','123',23),('other','123',24);

Query OK, 2 rows affected (0.00 sec)

3.  添加用户后，需要将修改后的数据加载至runtime及持久化存储至磁盘；MySQL [(none)]> load mysql users to runtime;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql users to disk;

Query OK, 0 rows affected (0.02 sec)

可用MySQL [(none)]> select * from mysql_users\G;查看用户相关情况；

测试：

vip用户的查询请求被发送到Master即写组上；

other用户的查询请求被发送到Slave即读组上；

示例：

将修改请求发送到写组，将查询请求发送到读组：MySQL [(none)]> insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)

values (1,1,'^SELECT.*FOR UPDATE$',23,1), (2,1,'^SELECT',24,1);

// rule_id ：规则编号，自动增长的整数，可以不指定；

// active： 规则是否有效，默认值为0，表示无效；需要在定义规则时将其设置为1；

// match_digest: 定义规则的具体匹配内容；由正则表达式元字符组成，用来匹配SQL语句；

// destination_hostgroup ：对于符合规则的请求，设置目标主机组，从而实现路由转发；

// apply：是否有效提交；默认值为0，表示无效，需要在定义规则时，将其值设置为1；

MySQL [(none)]> load mysql query rules to runtime;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql query rules to disk;

Query OK, 0 rows affected (0.16 sec)

测试：

如上图：

查询请求被发送到读组(Slave)；

修改请求被发送到写组(Master)；

2. MySQL Router

1) 修改主配置文件：[root@localhost ~]# vim /etc/mysqlrouter/mysqlrouter.conf

[DEFAULT]

logging_folder = /var/log/mysqlrouter/

plugin_folder = /usr/lib64/mysqlrouter

runtime_folder = /var/run/mysqlrouter

config_folder = /etc/mysqlrouter

[logger]

level = info

[keepalive]

interval = 60

[routing:masters]

bind_address = 172.16.1.14:40081

destinations = 172.16.75.1:3306

mode = read-write

connect_timeout = 2

[routing:slaves]

bind_address = 172.16.1.14:40082

destinations = 172.16.75.2:3306,172.16.1.12:3306

mode = read-only

connect_timeout = 1

2)启动服务：[root@localhost ~]# systemctl start mysqlrouter

3)测试：

由图可知：

从40081端口进来的请求被发送到Master上；

从40082端口进来的请求被发送到Slave上；

这样就实现了读写分离；