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


  查看是否数据一致:

  主服务器:

   QQ截图20180808171030.png


  两台从服务器:

   QQ截图20180808172704.png


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

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

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

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


  1. Master:

   查看master当前状态:

   QQ截图20180808174052.png

 

   授权:

 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状态:

        QQ截图20180808180145.png  



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

  

      1)Master:

   MariaDB [(none)]> create database wade;
   Query OK, 1 row affected (0.00 sec)

     2)Slave查看:

    QQ截图20180808191020.png


    主从数据库同步实现;

  

    接下来实现读写分离:

  

    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属性情况:

     QQ截图20180808200628.png


    QQ截图20180808203730.png


    可以看出:

    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;查看用户相关情况;


   测试:

  QQ截图20180808211749.png


   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)


  测试:

  QQ截图20180808213249.png


   如上图:

    查询请求被发送到读组(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)测试:

    QQ截图20180808215616.png



   QQ截图20180808215710.png


  QQ截图20180808215916.png


   由图可知:

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

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

         这样就实现了读写分离;

相关文章
相关标签/搜索