MySQL主從復制讀寫分離與高可用配置

作者：dyc2005 2018-04-08 15:20:15

前面我們說了MySQL的安裝配置，MySQL語句使用以及備份恢復MySQL數據；本次要介紹的是MySQL的主從復制，讀寫分離；及高可用MHA。

一、說明

前面我們說了mysql的安裝配置(并提供一鍵安裝腳本)，mysql語句使用以及備份恢復mysql數據；本次要介紹的是mysql的主從復制，讀寫分離；及高可用MHA。
環境如下：
master:CentOS7_x64 mysql5.721 172.16.3.175 db1
slave1:CentOS7_x64 mysql5.7.21 172.16.3.235 db2
slave2:CentOS7_x64 mysql5.7.21 172.16.3.235 db3
proxysql/MHA:CentOS7_x64 mysql5.7.21 172.16.3.235 proxysql

架構圖:

說明:
配置測試時為了方便關閉了防火墻頭，selinux安全策略；
現實中請開放防火墻策略；myslqdb的安裝已經有腳本一鍵安裝并配置好；這里就不在重復配置；只對對應的角色貼出對應的配置或安裝與之相關的軟件；

二、主從復制配置

一臺主數據庫，N從節點；從節點開啟兩個線程，通過Slave_IO_Running線程和主節點上有權限的賬號從主數據庫節點復制binlog日志到本地，能過Slave_SQL_Running線程在本地執行binlog日志,達到主從節點內容同步；

master配置:
egrep -v '(^$|^#)' /usr/local/mysql/etc/my.cnf

[mysqld] 
datadir=/data1/mysqldb 
socket=/tmp/mysql.sock 
key_buffer_size         = 16M 
max_allowed_packet      = 16M 
thread_stack            = 192K 
thread_cache_size       = 8 
query_cache_limit       = 1M 
query_cache_size        = 64M 
query_cache_type        = 1 
symbolic-links=0 
innodb_file_per_table=ON 
skip_name_resolve=ON 
 
server-id       = 1 
log_bin         = /data1/mysqldb/mysql-bin.log 
[mysqld_safe] 
log-error=/usr/local/mysql/logs/error.log 
pid-file=/data1/mysqldb/mysql.pid 
!includedir  /usr/local/mysql/etc/my.cnf.d

創建從節點同步賬號:

mysql > grant replication client,replication slave on *.* to 'repluser'@'172.16.3.%' identified by 'replpass'; 
mysql > flush privileges; 
mysql >show master logs; 
+------------------+-----------+ 
| Log_name         | File_size | 
+------------------+-----------+ 
| mysql-bin.000001 |       622 |

主節點上的binlog日志文件及位置;請記下;從節點第一次同步時需要用;

slave節點:
egrep -v '(^$|^#)' /usr/local/mysql/etc/my.cnf

[mysqld] 
datadir=/data1/mysqldb 
socket=/data1/mysqldb/mysql.sock 
key_buffer_size         = 16M 
max_allowed_packet      = 16M 
thread_stack            = 192K 
thread_cache_size       = 8 
query_cache_limit       = 1M 
query_cache_size        = 64M 
query_cache_type        = 1 
symbolic-links=0 
 
innodb_file_per_table=ON 
skip_name_resolve=ON 
 
server-id               = 11         #從節點標識ID  各從節點均不一樣  
relay_log = relay-log 
read_only=ON 
 
[mysqld_safe] 
log-error=/usr/local/mysql/log/error.log 
pid-file=/var/run/mysql/mysql.pid 
!includedir /usr/local/mysql/etc/my.cnf.d

啟動mysq數據庫
注意:兩臺從節點的server-id 值不一樣;其他的都一樣;因此從節點只展示一個配置文件;
登錄數據庫并同步數據啟動slave
兩臺slave均要同步并啟動

mysql > CHANGE MASTER TO MASTER_HOST="172.16.3.175",MASTER_USER="repluser",MASTER_PASSWORD="replpass",MASTER_PORT=3306,MASTER_LOG_FILE="mysql-bin.000001",MASTER_LOG_POS=622; 
mysql > start slave;     #啟動從節點() 
 
#查看從節點狀態 
mysql > SHOW SLAVE STATUS; 
*************************** 1. row *************************** 
               Slave_IO_State: Waiting for master to send event 
                  Master_Host: 172.16.3.175                  #主節點 
                  Master_User: repluser                          #同步賬號 
                  Master_Port: 3306 
                Connect_Retry: 60 
              Master_Log_File: mysql-bin.000001 
          Read_Master_Log_Pos: 622 
               Relay_Log_File: relay-log.000001 
                Relay_Log_Pos: 582 
        Relay_Master_Log_File: mysql-bin.000001 
             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               #最后同步的錯誤 0表示正常同步 
                   Last_Error:  
                 Skip_Counter: 0 
          Exec_Master_Log_Pos: 622 
              Relay_Log_Space: 615 
              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 
                  Master_UUID: 57017c43-36e3-11e8-ac76-080027393fc7 
             Master_Info_File: /data1/mysqldb/master.info 
                    SQL_Delay: 0 
          SQL_Remaining_Delay: NULL 
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 
           Master_Retry_Count: 86400 
                  Master_Bind:  
      Last_IO_Error_Timestamp:  
     Last_SQL_Error_Timestamp:  
               Master_SSL_Crl:  
           Master_SSL_Crlpath:  
           Retrieved_Gtid_Set:  
            Executed_Gtid_Set:  
                Auto_Position: 0 
         Replicate_Rewrite_DB:  
                 Channel_Name:  
           Master_TLS_Version:  
1 row in set (0.00 sec) 
ERROR:  
No query specified

測試主從同步
在master導入測試數據;修改數據并查看slave 中的數據是否一致;

[root@db1 ~]# mysql < Testdb.sql 
登錄數據庫 
[root@db1 ~]# mysql -uroot -p 
mysql> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
 study              | 
+--------------------+ 
5 rows in set (0.00 sec) 
###study測試數據庫導入成功 
mysql> use study; 
Database changed 
mysql> show tables; 
+-----------------+ 
| Tables_in_study | 
+-----------------+ 
| class           | 
| course          | 
| part            | 
| score           | 
| student         | 
| tb31            | 
| tb32            | 
| teacher         | 
| test1           | 
| test2           | 
| user_info       | 
+-----------------+ 
11 rows in set (0.00 sec) 
#刪除test1 test2表

slave從節點上查看

mysql> show tables; 
+-----------------+ 
| Tables_in_study | 
+-----------------+ 
| class           | 
| course          | 
| part            | 
| score           | 
| student         | 
| tb31            | 
| tb32            | 
| teacher         | 
| user_info       | 
+-----------------+ 
9 rows in set (0.00 sec)

數據已經可以正常同步;注意主從同步只需要第一次手動啟動;之后都隨mysql服務自動啟動;主從同步架構只方便了數據的同步,此時如果沒有第三方工具介入想做到讀寫分離就需要在程序中去做,難免出錯;而出錯了,就需要手動同步數據；這里通過proxysql來做讀寫分離;

三、proxysql之讀寫分離

以上已經完成了主從復制配置;然而這只是一個基本配置,加上一個proxysql實現mysql讀寫分離,proxysql類似haproxy七層代理路由功能且支持MySQL 協議的的數據庫代理；是dba開發給dba使用的;用戶請求發向proxysql,如果是寫請求發往主節點;讀請求發下從節點組中;以此實現讀寫分離;一定程序上減輕了主數據庫的io壓力;
下載安裝proxysql
目前最新版本是1.4.7-1(由于最新版本有問題)
我們這里下載使用1.3.6-1的基于CentOS7的rpm包;下載到本地并yum安裝

[root@proxysql ~]# yum install   proxysql-1.3.6-1-centos7.x86_64.rpm -y 
[root@proxysql ~]# rpm -ql proxysql 
/etc/init.d/proxysql 
/etc/proxysql.cnf       #主配置文件 
/usr/bin/proxysql 
/usr/share/proxysql/tools/proxysql_galera_checker.sh 
/usr/share/proxysql/tools/proxysql_galera_writer.pl

配置如下:
在配置proxysql之前需要在主節點配置授權賬號以作proxysql對主從節點操作;另外proxysql上的mysql客戶端工具需要和主從節點上的保持一致;
在主節點master上授權登錄賬號：

mysql > GRANT ALL ON *.* TO 'myadmin'@'172.16.3.%' identified by 'mypass';

proxysql.cnf配置

[root@proxysql ~]# egrep -v '(^$|^#)' /etc/proxysql.cnf

datadir="/var/lib/proxysql" 
admin_variables= 
{    
    admin_credentials="admin:admin"               #proxysql自己的管理用戶名密碼 
    mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock" 
} 
mysql_variables= 
{ 
    threads=4                         #線程數,建議和cpu核心數一致 
    max_connections=2048      #最大連接 
    default_query_delay=0 
    default_query_timeout=36000000 
    have_compress=true 
    poll_timeout=2000 
    interfaces="0.0.0.0:3306;/tmp/proxysql.sock"             #對外接口 
    default_schema="information_schema" 
    stacksize=1048576 
    server_version="5.5.30" 
    connect_timeout_server=3000 
  monitor_username="monitor" 
    monitor_password="monitor" 
    monitor_history=600000 
    monitor_connect_interval=60000 
    monitor_ping_interval=10000 
    monitor_read_only_interval=1500 
    monitor_read_only_timeout=500 
    ping_interval_server_msec=120000 
    ping_timeout_server=500 
    commands_stats=true 
    sessions_sort=true 
    connect_retries_on_failure=10 
} 
 
#####主從節點的配置 
mysql_servers =                         
( 
    { 
        address = "172.16.3.175" # 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 = 1           # 設置組號 
        status = "ONLINE"     # default: ONLINE 
        weight = 1            # default: 1 
        compression = 0       # default: 0 
        max_connections = 200            ###定義最大的連接 
    }, 
    { 
        address = "172.16.3.235" # 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 = 2           # no default, required 
        status = "ONLINE"     # default: ONLINE 
        weight = 1            # default: 1 
        compression = 0       # default: 0 
        max_connections=1000 
    }, 
    { 
        address = "172.16.3.241" # 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 = 2           # no default, required 
        status = "ONLINE"     # default: ONLINE 
        weight = 1            # default: 1 
        compression = 0       # default: 0 
        max_connections=1000 
    } 
) 
mysql_users: 
( 
    { 
        username = "myadmin" # no default , required 
        password = "mypass" # default: '' 
        default_hostgroup = 1 # default: 0 
        max_connections=1000 
        default_schema="test" 
        active = 1            #是否激活 
    } 
) 
mysql_query_rules: 
( 
) 
scheduler= 
( 
) 
mysql_replication_hostgroups= 
( 
        { 
                writer_hostgroup=1         #定義寫組號1 
                reader_hostgroup=2      #定義讀組號2 
                comment="test repl 1"   #注釋內容 
       } 
)

啟動proxysql服務

[root@proxysql ~]# service proxysql start

測試proxysql

模擬通過proxysql使用數據庫

[root@proxysql ]# mysql -h172.16.3.175 -umyadmin -pmypass 
mysql: [Warning] Using a password on the command line interface can be insecure. 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 17406 
Server version: 5.7.21-log MySQL Community Server (GPL) 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
mysql>  
 
mysql > show databases; 
mysql> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| study              | 
| sys                | 
+--------------------+ 
5 rows in set (0.00 sec) 
###刪除study數據庫user_info中6 -12之間的數據

刪除之前:

mysql> select * from user_info; 
+-----+-------+------+--------+----------+ 
| nid | name  | age  | gender | part_nid | 
+-----+-------+------+--------+----------+ 
|   1 | san   |   20 | 男     |        1 | 
|   2 | dong  |   29 | 男     |        2 | 
|   4 | Ling  |   28 | 男     |        4 | 
|   5 | ling  |   28 | 男     |        3 | 
|   6 | dong  |   30 | 男     |        1 | 
|   7 | b     |   11 | 女     |        1 | 
|   8 | c     |   12 | 女     |        1 | 
|   9 | d     |   18 | 女     |        4 | 
|  10 | e     |   22 | 男     |        3 | 
|  11 | f     |   23 | 男     |        2 | 
|  12 | dongy |   22 | 男     |        1 | 
+-----+-------+------+--------+----------+ 
11 rows in set (0.00 sec)

刪除之后:

mysql> delete from user_info where nid >6 and nid <12; 
Query OK, 5 rows affected (0.03 sec) 
 
mysql> select * from user_info; 
+-----+-------+------+--------+----------+ 
| nid | name  | age  | gender | part_nid | 
+-----+-------+------+--------+----------+ 
|   1 | san   |   20 | 男     |        1 | 
|   2 | dong  |   29 | 男     |        2 | 
|   4 | Ling  |   28 | 男     |        4 | 
|   5 | ling  |   28 | 男     |        3 | 
|   6 | dong  |   30 | 男     |        1 | 
|  12 | dongy |   22 | 男     |        1 | 
+-----+-------+------+--------+----------+ 
6 rows in set (0.00 sec)

到主從節點上查看,會發現以上的查和修改數據都被proxysql正確的代理到后端處理了;
以上看了并不直觀;為了查看proxysql與各主從節點通訊我們在主從節點上安裝tcpdump并過濾包

主節點: 類似如下:

[root@db1 ~]# tcpdump -i enp0s3 -nn tcp port 3306 
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode 
listening on enp0s3, link-type EN10MB (Ethernet), capture size 262144 bytes 
18:04:34.678861 IP 172.16.3.254.42191 > 172.16.3.175.3306: Flags [S], seq 3385407732, win 29200, options [mss 1460,sackOK,TS val 17576713 ecr 0,nop,wscale 7], length 0 
18:04:34.678908 IP 172.16.3.175.3306 > 172.16.3.254.42191: Flags [S.], seq 1579426335, ack 3385407733, win 28960, options [mss 1460,sackOK,TS val 29413673 ecr 17576713,nop,wscale 7], length 0 
18:04:34.680902 IP 172.16.3.254.42191 > 172.16.3.175.3306: Flags [.], ack 1, win 229, options [nop,nop,TS val 17576715 ecr 29413673], length 0 
18:04:34.681264 IP 172.16.3.175.3306 > 172.16.3.254.42191: Flags [P.], seq 1:83, ack 1, win 227, options [nop,nop,TS val 29413675 ecr 17576715], length 82 
....

從節點: 類似如下:

[root@db2 data1]# tcpdump -i enp0s3 -nn tcp port 3306 
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode 
listening on enp0s3, link-type EN10MB (Ethernet), capture size 262144 bytes 
18:02:57.932043 IP 172.16.3.254.42733 > 172.16.3.235.3306: Flags [S], seq 76520456, win 29200, options [mss 1460,sackOK,TS val 17479189 ecr 0,nop,wscale 7], length 0 
...........

proxysql命令行管理接口:支持運行時修改

[root@proxysql]#  mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' 
Admin> show databases; 
+-----+---------+-------------------------------+ 
| seq | name    | file                          | 
+-----+---------+-------------------------------+ 
| 0   | main    |                               | 
| 2   | disk    | /var/lib/proxysql/proxysql.db | 
| 3   | stats   |                               | 
| 4   | monitor |                               | 
+-----+---------+-------------------------------+ 
4 rows in set (0.00 sec)

以上stats,monitor,main都從配置文件中獲取的數據庫;可以通過類似mysql運行時修改;而不需要重啟;更多命令行的配置請參考github;

至此我們基于proxysql主從復制讀寫分離架構已經完成;
雙主或多主模型是無須實現讀寫分離，僅需要負載均衡：haproxy, nginx, lvs等;

proxysql并沒有解決,當主數據巖機時的問題;此時就需要量MHA來解決 ;后續再介紹;

責任編輯：龐桂玉來源： 51CTO博客

成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看

MySQL主從復制讀寫分離與高可用配置

一、說明

二、主從復制配置

三、proxysql之讀寫分離