MySQL讀寫分離

標籤：nod   app   mysqldump   檔案   hang   add   路由   -o   啟動   

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的主設定檔：

  [[email protected] ~]# 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的主設定檔：

  [[email protected] ~]# 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的主設定檔：

  

   [[email protected] ~]# 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

   配置完後將三台主機服務開啟：

   [[email protected] ~]# systemctl start mariadb.service

 

   將主伺服器的所有Database Backup到兩台從伺服器：

   [[email protected] ~]# mysqldump --all-databases --lock-all-tables > alldb.sql   [[email protected] ~]# scp alldb.sql [email protected]:/root/    [email protected]'s password:     alldb.sql                                                   100% 1689KB  10.6MB/s   00:00       [[email protected] ~]# scp alldb.sql [email protected]:/root/    [email protected]'s password:     alldb.sql                                                   100% 1689KB  35.3MB/s   00:00

  

   從伺服器將主伺服器的所有資料儲存：

  [[email protected] ~]# 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主機上安裝；

   [[email protected] ~]# rpm -ivh proxysql-1.4.9-1-centos7.x86_64.rpm

      2）開啟服務：

   [[email protected] ~]# service proxysql start   Starting ProxySQL: DONE!

  

     3）將MySQL用戶端工具串連到proxysql的管理介面：

          預設的管理員賬戶和密碼都為“admin”，連接埠為6032：

   [[email protected] ~]# 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: 定義規則的具體匹配內容；由Regex元字元組成，用來匹配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） 修改主設定檔：

 [[email protected] ~]# 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）啟動服務：

  [[email protected] ~]# systemctl start mysqlrouter

 3）測試：

   

  

 

   由圖可知：

         從40081連接埠進來的請求被發送到Master上；

         從40082連接埠進來的請求被發送到Slave上；

         這樣就實現了讀寫分離；

MySQL讀寫分離