天天看點
傳回

基于Keepalived高可用叢集的MariaDB讀寫分離機制實作一 MariaDB讀寫分離機制 二 常用讀寫分離器的介紹 三 架構拓撲圖 四 主從複制配置 五 讀寫分離ProxySQL的配置 六 配置高可用叢集 七 測試

一 MariaDB讀寫分離機制

在實作讀寫分離機制之前先了解一下三種主從複制方式：

1.異步複制：

MariaDB預設的複制即是異步的，主庫在執行完用戶端送出的事務後會立即将結果返給給用戶端，并不關心從庫是否已經接收并處理，這樣就會有一個問題，主節點如果挂掉了，此時主上已經送出的事務可能并沒有傳到從上，如果此時，強行将從提升為主，可能導緻新主上的資料不完整。

2.全同步複制：

指當主庫執行完一個事務，所有的從庫都執行了該事務才傳回給用戶端。因為需要等待所有從庫執行完該事務才能傳回，是以全同步複制的性能必然會收到嚴重的影響。

3.半同步複制：

介于異步複制和全同步複制之間，主庫在執行完用戶端送出的事務後不是立刻傳回給用戶端，而是等待至少一個從庫接收到并寫到relay log中才傳回給用戶端。相對于異步複制，半同步複制提高了資料的安全性，同時它也造成了一定程度的延遲，這個延遲最少是一個TCP/IP往返的時間。是以，半同步複制最好在低延時的網絡中使用。

在本次實驗中我們采用一主兩從的方式實作MySQL讀寫分離機制，而在主從複制中采用異步複制和半同複制兩種方式來保證資料的安全性和完整性。為了保證讀寫分離器高可用，還可利用Keepalived實作高可用叢集。

二 常用讀寫分離器的介紹

mysql-proxy:MySQL官方出品；

atlas:奇虎360公司二次開發産品；

amoeba:Alibaba集團開發的可實作讀寫分離、分片功能的讀寫分離器；

OneProxy:讀寫分離架構，由平民架構開發并維護；

ProxySQL:一款高性能讀寫分離器；

MaxScale:Mariadb官方，穩定可靠，比較有影響力。

本實驗我們采用ProxySQL來實作讀寫分離。

三 架構拓撲圖

基于Keepalived高可用叢集的MariaDB讀寫分離機制實作一 MariaDB讀寫分離機制 二 常用讀寫分離器的介紹 三 架構拓撲圖 四 主從複制配置 五 讀寫分離ProxySQL的配置 六 配置高可用叢集 七 測試

四 主從複制配置 

#分别在三台主從節點配置如下的内容
[root@node1 ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
skip_name_resolve=ON
innodb_file_per_table=ON
server_id=1
log_bin=mysql-bin
[root@node2 ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
skip_name_resolve=ON
innodb_file_per_table=ON
server_id=2
relay_log=relay-log
[root@node3 ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
skip_name_resolve=ON
innodb_file_per_table=ON
server_id=3
relay_log=relay-log
#啟動MariaDB服務
[root@node1 ~]# systemctl start mariadb.service
#登入MariaDB
[root@node1 ~]# mysql
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      245 |              |                  |
+------------------+----------+--------------+------------------+
#建立MariaDB複制賬号
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.18.67.%' IDENTIFIED BY 'replpass';
#安裝半同步主從複制插件并啟動主從複制功能
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
MariaDB [(none)]> SET @@global.rpl_semi_sync_master_enabled=ON;
#啟動MariaDB服務
[root@node2 ~]# systemctl start mariadb.service
#登入MariaDB
[root@node2 ~]# mysql
#安裝半同步主從複制插件并啟動主從複制功能
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [(none)]> set @@global.rpl_semi_sync_slave_enabled=ON;
#配置slave1節點的master節點
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.18.67.11',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=442;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.67.11
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 422
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 422
              Relay_Log_Space: 817
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
#啟動MariaDB服務
[root@node3 ~]# systemctl start mariadb.service
#登入MariaDB
[root@node3 ~]# mysql
#配置slave2節點的master節點
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.18.67.11',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=442;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.67.11
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 422
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 422
              Relay_Log_Space: 817
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
#進行測試
[root@node1 ~]# mysql -e "SHOW GLOBAL STATUS LIKE '%semi%'" | grep 'Rpl_semi_sync_master_clients'
Rpl_semi_sync_master_clients 1
#在主節點建立資料庫mydb
[root@node1 ~]# mysql -e "CREATE DATABASE mydb;"
#在從節點檢視主節點建立的資料庫mydb是否複制過來
[root@node2 ~]# mysql -e "SHOW DATABASES;" | grep mydb
mydb
[root@node3 ~]# mysql -e "SHOW DATABASES;" | grep mydb
mydb
#從節點已經将主節點建立的資料庫複制過來了

五 讀寫分離ProxySQL的配置

1.節點一

[root@node4 ~]# vim /etc/proxysql.cnf
datadir="/var/lib/proxysql"
admin_variables=
{
 admin_credentials="admin:admin"
 mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
}
mysql_variables=
{
 threads=4
 max_connections=2048
 default_query_delay=0
 default_query_timeout=36000000
 have_compress=true
 poll_timeout=2000
 interfaces="0.0.0.0:3306;/tmp/mysql.sock"
 default_schema="information_schema"
 stacksize=1048576
 server_version="5.5.30"
 connect_timeout_server=3000
 monitor_history=600000
 monitor_connect_interval=60000
 monitor_ping_interval=10000
 monitor_read_only_interval=1500
 monitor_read_only_timeout=500
 ping_interval_server=120000
 ping_timeout_server=500
 commands_stats=true
 sessions_sort=true
 connect_retries_on_failure=10
}
mysql_servers =
(
 {
  address = "172.18.67.11"   # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
  port = 3306                # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
  hostgroup = 0              # no default, required
  status = "ONLINE"          # default: ONLINE
  weight = 1                 # default: 1
  compression = 0            # default: 0
 },
 {
  address = "172.18.67.12"
  port = 3306
  hostgroup = 1
  status = "ONLINE"          # default: ONLINE
  weight = 1                 # default: 1
  compression = 0            # default: 0
 },
 {
  address = "172.18.67.13"
  port = 3306
  hostgroup = 1
  status = "ONLINE"          # default: ONLINE
  weight = 1                 # default: 1
  compression = 0            # default: 0
 }
)
mysql_users:
(
 {
  username = "root"
  password = "mrlapulga"
  default_hostgroup = 0
  max_connections=1000
  active = 1
 }
)
mysql_replication_hostgroups=
(
 {
  writer_hostgroup=0
  reader_hostgroup=1
 }
)
#在主節點對使用者進行授權：
MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'172.18.67.%' IDENTIFIED BY 'mrlapulga';
MariaDB [(none)]> FLUSH PRIVILEGES;
#啟動讀寫分離器的節點1
[root@node4 ~]# systemctl start proxysql
#使用管理接口驗證：
[root@node4 ~]# mysql -uadmin -h127.0.0.1 -padmin -P6032
MySQL [(none)]> SELECT hostgroup_id,hostname,hostname,status FROM mysql_servers;
+--------------+--------------+--------------+--------+
| hostgroup_id | hostname     | hostname     | status |
+--------------+--------------+--------------+--------+
| 0            | 172.18.67.11 | 172.18.67.11 | ONLINE |
| 1            | 172.18.67.12 | 172.18.67.12 | ONLINE |
| 1            | 172.18.67.13 | 172.18.67.13 | ONLINE |
+--------------+--------------+--------------+--------+
#測試讀操作
[root@node4 ~]# mysql -uroot -pmrlapulga -h 127.0.0.1  -P3306
MySQL [OA]> use mydb;
MySQL [mydb]> DESC tbl1;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(20)            | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
#測試寫操作，在proxysql節點插入一條字段
MySQL [mydb]> INSERT INTO mydb.tbl1 (name) VALUES ('jack');
#在從節點一檢視
[root@node2 ~]# mysql -e "SELECT * FROM mydb.tbl1;"
+----+------+
| id | name |
+----+------+
|  1 | jack |
+----+------+
#在從節點二檢視
[root@node3 ~]# mysql -e "SELECT * FROM mydb.tbl1;"
+----+------+
| id | name |
+----+------+
|  1 | jack |
+----+------+

 2.節點二

#将配置檔案複制到ProxySQL節點二
[root@node4 ~]# scp /etc/proxysql.cnf 172.18.67.15:/etc/
#啟動節點二的服務
[root@node5 ~]# systemctl start proxysql

六 配置高可用叢集 

#在ProxySQL1節點配置keepalived
[root@node4 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
    notification_email_from keepalived@localhost
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id proxysql
    vrrp_macst_group4 224.0.67.67
}
vrrp_instance HA_mysql {
    state MASTER
    interface eno16777736
    virtual_router_id 67
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
  auth_pass lKZvQVv9
    }
    virtual_ipaddress {
      172.18.67.33/16 dev eno16777736
    }
}
[root@node4 ~]# systemctl start keepalived
#在ProxySQL2節點配置keepalived
[root@node5 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
    notification_email_from keepalived@localhost
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id proxysql
    vrrp_macst_group4 224.0.67.67
}
vrrp_instance HA_mysql {
    state BACKUP
    interface eno16777736
    virtual_router_id 67
    priority 98
    advert_int 1
    authentication {
        auth_type PASS
  auth_pass lKZvQVv9
    }
    virtual_ipaddress {
      172.18.67.33/16 dev eno16777736
    }
}
[root@node5 ~]# systemctl start keepalived

七 測試 

#在ProxySQL節點1登入MySQL
[root@node4 ~]# mysql -uroot -h172.18.67.33 -pmrlapulga -P 3306
MySQL [(none)]> use mydb;
#檢視資料庫及表
MySQL [mydb]> SELECT * FROM tbl1;
+----+------+
| id | name |
+----+------+
|  1 | jack |
+----+------+
#在ProxySQL節點2登入MySQL
[root@node5 ~]# mysql -uroot -h172.18.67.33 -pmrlapulga -P 3306
MySQL [(none)]> use mydb;
#檢視資料庫及表
MySQL [mydb]> SELECT * FROM tbl1;
+----+------+
| id | name |
+----+------+
|  1 | jack |
+----+------+
#測試在ProxySQL1節點寫操作
MySQL [mydb]> CREATE TABLE tbl2 (id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(20) NOT NULL, PRIMARY KEY(id));
#分别在兩個從節點檢視
[root@node2 ~]# mysql -e "DESC mydb.tbl2;"
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(20)            | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
[root@node3 ~]# mysql -e "DESC mydb.tbl2;"
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(20)            | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
關系型資料庫 mysql 測試技術 資料庫 開發工具 mariadb叢集 lvs keepalived搭建叢集 keepalived搭建高可用叢集 keepalived搭建叢集 分布式dubbo叢集政策
上一篇: 擷取本地時間,UTC時間
下一篇: MySQL備份與還原一 了解MySQL備份與恢複二 MySQL備份與恢複工具介紹

繼續閱讀