MySQL主從復(fù)制讀寫分離與高可用配置
一、說明
前面我們說了mysql的安裝配置(并提供一鍵安裝腳本)，mysql語句使用以及備份恢復(fù)mysql數(shù)據(jù)；本次要介紹的是mysql的主從復(fù)制，讀寫分離；及高可用MHA。
環(huán)境如下：
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
架構(gòu)圖:
說明:
配置測(cè)試時(shí)為了方便關(guān)閉了防火墻頭，selinux安全策略；
現(xiàn)實(shí)中請(qǐng)開放防火墻策略；myslqdb的安裝已經(jīng)有腳本一鍵安裝并配置好；這里就不在重復(fù)配置；只對(duì)對(duì)應(yīng)的角色貼出對(duì)應(yīng)的配置或安裝與之相關(guān)的軟件；
二、主從復(fù)制配置
一臺(tái)主數(shù)據(jù)庫(kù)，N從節(jié)點(diǎn)；從節(jié)點(diǎn)開啟兩個(gè)線程，通過Slave_IO_Running線程和主節(jié)點(diǎn)上有權(quán)限的賬號(hào)從 主數(shù)據(jù)庫(kù)節(jié)點(diǎn)復(fù)制binlog日志到本地，能過Slave_SQL_Running線程在本地執(zhí)行binlog日志,達(dá)到主從節(jié)點(diǎn)內(nèi)容同步；
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
創(chuàng)建從節(jié)點(diǎn)同步賬號(hào):
- 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 |
主節(jié)點(diǎn)上的binlog日志文件及位置;請(qǐng)記下;從節(jié)點(diǎn)第一次同步時(shí)需要用;
slave節(jié)點(diǎn):
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 #從節(jié)點(diǎn)標(biāo)識(shí)ID 各從節(jié)點(diǎn)均不一樣
- 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
啟動(dòng)mysq數(shù)據(jù)庫(kù)
注意:兩臺(tái)從節(jié)點(diǎn)的server-id 值不一樣;其他的都一樣;因此從節(jié)點(diǎn)只展示一個(gè)配置文件;
登錄數(shù)據(jù)庫(kù)并同步數(shù)據(jù)啟動(dòng)slave
兩臺(tái)slave均要同步并啟動(dòng)
- 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; #啟動(dòng)從節(jié)點(diǎn)()
- #查看從節(jié)點(diǎn)狀態(tài)
- mysql > SHOW SLAVE STATUS;
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 172.16.3.175 #主節(jié)點(diǎn)
- Master_User: repluser #同步賬號(hào)
- 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: #同步過濾為空(可以只同步某個(gè)或某些庫(kù))
- Replicate_Ignore_DB: #不同步的庫(kù)
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0 #最后同步的錯(cuò)誤 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
測(cè)試主從同步
在master導(dǎo)入測(cè)試數(shù)據(jù);修改數(shù)據(jù)并查看slave 中的數(shù)據(jù)是否一致;
- [root@db1 ~]# mysql < Testdb.sql
- 登錄數(shù)據(jù)庫(kù)
- [root@db1 ~]# mysql -uroot -p
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- study |
- +--------------------+
- 5 rows in set (0.00 sec)
- ###study測(cè)試數(shù)據(jù)庫(kù)導(dǎo)入成功
- 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從節(jié)點(diǎn)上查看
- mysql> show tables;
- +-----------------+
- | Tables_in_study |
- +-----------------+
- | class |
- | course |
- | part |
- | score |
- | student |
- | tb31 |
- | tb32 |
- | teacher |
- | user_info |
- +-----------------+
- 9 rows in set (0.00 sec)
數(shù)據(jù)已經(jīng)可以正常同步;注意主從同步只需要第一次手動(dòng)啟動(dòng);之后都隨mysql服務(wù)自動(dòng)啟動(dòng);主從同步架構(gòu)只方便了數(shù)據(jù)的同步,此時(shí)如果沒有 第三方工具介入想做到讀寫分離就需要在程序中去做,難免出錯(cuò);而出錯(cuò)了,就需要手動(dòng)同步數(shù)據(jù)；這里通過proxysql來做讀寫分離;
三、proxysql之讀寫分離
以上已經(jīng)完成了主從復(fù)制配置;然而這只是一個(gè)基本配置,加上一個(gè)proxysql實(shí)現(xiàn)mysql讀寫分離,proxysql類似haproxy七層代理路由功能且支持MySQL 協(xié)議的的數(shù)據(jù)庫(kù)代理；是dba開發(fā)給dba使用的;用戶請(qǐng)求發(fā)向proxysql,如果是寫請(qǐng)求發(fā)往主節(jié)點(diǎn);讀請(qǐng)求發(fā)下從節(jié)點(diǎn)組中;以此實(shí)現(xiàn)讀寫分離;一定程序上減輕了主數(shù)據(jù)庫(kù)的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之前需要在主節(jié)點(diǎn)配置授權(quán)賬號(hào)以作proxysql對(duì)主從節(jié)點(diǎn)操作;另外proxysql上的mysql客戶端工具需要和主從節(jié)點(diǎn)上的保持一致;
在主節(jié)點(diǎn)master上授權(quán)登錄賬號(hào)：
- 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 #線程數(shù),建議和cpu核心數(shù)一致
- 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" #對(duì)外接口
- 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
- }
- #####主從節(jié)點(diǎn)的配置
- 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 # 設(shè)置組號(hào)
- 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 #定義寫組號(hào)1
- reader_hostgroup=2 #定義讀組號(hào)2
- comment="test repl 1" #注釋內(nèi)容
- }
- )
啟動(dòng)proxysql服務(wù)
- [root@proxysql ~]# service proxysql start
測(cè)試proxysql
模擬通過proxysql使用數(shù)據(jù)庫(kù)
- [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數(shù)據(jù)庫(kù)user_info中6 -12之間的數(shù)據(jù)
刪除之前:
- 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)
到主從節(jié)點(diǎn)上查看,會(huì)發(fā)現(xiàn)以上的查和修改數(shù)據(jù)都被proxysql正確的代理到后端處理了;
以上看了并不直觀;為了查看proxysql與各主從節(jié)點(diǎn)通訊我們?cè)谥鲝墓?jié)點(diǎn)上安裝tcpdump并過濾包
主節(jié)點(diǎn):
類似如下:
- [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
- ....
從節(jié)點(diǎn):
類似如下:
[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命令行管理接口:支持運(yùn)行時(shí)修改
- [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都從配置文件中獲取的數(shù)據(jù)庫(kù);可以通過類似mysql運(yùn)行時(shí)修改;而不需要重啟;更多命令行的配置請(qǐng)參考github;
至此我們基于proxysql主從復(fù)制讀寫分離架構(gòu)已經(jīng)完成;
雙主或多主模型是無須實(shí)現(xiàn)讀寫分離，僅需要負(fù)載均衡：haproxy, nginx, lvs等;
proxysql并沒有解決,當(dāng)主數(shù)據(jù)巖機(jī)時(shí)的問題;此時(shí)就需要量MHA來解決 ;后續(xù)再介紹;
