mysql主從復(fù)制讀寫分離的配置方法詳解
更新時間：2018年04月09日 08:57:13 作者：dyc2005
一般來說mysql都是通過 主從復(fù)制（Master-Slave）的方式來同步數(shù)據(jù)，再通過讀寫分離（MySQL-Proxy）來提升數(shù)據(jù)庫的并發(fā)負(fù)載能力 這樣的方案來進(jìn)行部署與實施的。
一、說明
前面我們說了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)圖:
說明:
配置測試時為了方便關(guān)閉了防火墻頭,selinux安全策略;
現(xiàn)實中請開放防火墻策略;myslqdb的安裝已經(jīng)有腳本一鍵安裝并配置好;這里就不在重復(fù)配置;只對對應(yīng)的角色貼出對應(yīng)的配置或安裝與之相關(guān)的軟件;
二、主從復(fù)制配置
一臺主數(shù)據(jù)庫,N從節(jié)點;從節(jié)點開啟兩個線程,通過Slave_IO_Running線程和主節(jié)點上有權(quán)限的賬號從 主數(shù)據(jù)庫節(jié)點復(fù)制binlog日志到本地,能過Slave_SQL_Running線程在本地執(zhí)行binlog日志,達(dá)到主從節(jié)點內(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é)點同步賬號:
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é)點上的binlog日志文件及位置;請記下;從節(jié)點第一次同步時需要用;slave節(jié)點:
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é)點標(biāo)識ID 各從節(jié)點均不一樣 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數(shù)據(jù)庫
注意:兩臺從節(jié)點的server-id 值不一樣;其他的都一樣;因此從節(jié)點只展示一個配置文件;
登錄數(shù)據(jù)庫并同步數(shù)據(jù)啟動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; #啟動從節(jié)點()
#查看從節(jié)點狀態(tài)
mysql > SHOW SLAVE STATUS;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.3.175 #主節(jié)點
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導(dǎo)入測試數(shù)據(jù);修改數(shù)據(jù)并查看slave 中的數(shù)據(jù)是否一致;
[root@db1 ~]# mysql < Testdb.sql 登錄數(shù)據(jù)庫 [root@db1 ~]# mysql -uroot -p mysql> show databases; +--------------------+ | Database | +--------------------+ study | +--------------------+ 5 rows in set (0.00 sec) ###study測試數(shù)據(jù)庫導(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é)點上查看
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)可以正常同步;注意主從同步只需要第一次手動啟動;之后都隨mysql服務(wù)自動啟動;主從同步架構(gòu)只方便了數(shù)據(jù)的同步,此時如果沒有 第三方工具介入想做到讀寫分離就需要在程序中去做,難免出錯;而出錯了,就需要手動同步數(shù)據(jù)；這里通過proxysql來做讀寫分離;
三、proxysql之讀寫分離
以上已經(jīng)完成了主從復(fù)制配置;然而這只是一個基本配置,加上一個proxysql實現(xiàn)mysql讀寫分離,proxysql類似haproxy七層代理路由功能且支持MySQL 協(xié)議的的數(shù)據(jù)庫代理；是dba開發(fā)給dba使用的;用戶請求發(fā)向proxysql,如果是寫請求發(fā)往主節(jié)點;讀請求發(fā)下從節(jié)點組中;以此實現(xiàn)讀寫分離;一定程序上減輕了主數(shù)據(jù)庫的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é)點配置授權(quán)賬號以作proxysql對主從節(jié)點操作;另外proxysql上的mysql客戶端工具需要和主從節(jié)點上的保持一致;
在主節(jié)點master上授權(quán)登錄賬號：
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" #對外接口
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é)點的配置
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è)置組號
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" #注釋內(nèi)容
}
)
啟動proxysql服務(wù)
[root@proxysql ~]# service proxysql start
測試proxysql
模擬通過proxysql使用數(shù)據(jù)庫 [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ù)庫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é)點上查看,會發(fā)現(xiàn)以上的查和修改數(shù)據(jù)都被proxysql正確的代理到后端處理了;
以上看了并不直觀;為了查看proxysql與各主從節(jié)點通訊我們在主從節(jié)點上安裝tcpdump并過濾包
主節(jié)點: 類似如下: [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é)點: 類似如下: [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都從配置文件中獲取的數(shù)據(jù)庫;可以通過類似mysql運行時修改;而不需要重啟;
至此我們基于proxysql主從復(fù)制讀寫分離架構(gòu)已經(jīng)完成;
雙主或多主模型是無須實現(xiàn)讀寫分離，僅需要負(fù)載均衡：haproxy, nginx, lvs等;
proxysql并沒有解決,當(dāng)主數(shù)據(jù)巖機(jī)時的問題;此時就需要量MHA來解決 ;后續(xù)再介紹;
相關(guān)文章
這篇文章介紹了SQL?Server的全文搜索功能，文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-04-04
這篇文章主要介紹了MySQL 5.6.14 win32安裝方法(zip版)的相關(guān)資料,非常不錯，需要的朋友可以參考下2016-08-08
這篇文章主要介紹了MySQL基礎(chǔ)教程之IN的用法詳解的相關(guān)資料,需要的朋友可以參考下2017-01-01
MySQL中另外一類很重要的函數(shù)就是數(shù)值函數(shù),這些函數(shù)能處理很多數(shù)值方面的運算,下面這篇文章主要給大家介紹了關(guān)于MySQL常見數(shù)值函數(shù)整理的相關(guān)資料,需要的朋友可以參考下2023-02-02
mysql source 命令導(dǎo)入大的sql文件的方法
本文將詳細(xì)介紹mysql source 命令導(dǎo)入大的sql文件的方法;需要的朋友可以參考下2012-11-11
從windows上導(dǎo)出一個sql執(zhí)行文件，再倒入到unbutn中，結(jié)果出現(xiàn)亂碼，折騰7-8分鐘，解決方式在導(dǎo)出mysql sql執(zhí)行文件的時候，指定一下編碼格式2013-04-04
關(guān)于mysql create routine 權(quán)限的一些說明
下面小編就為大家?guī)硪黄P(guān)于mysql create routine 權(quán)限的一些說明。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03
本文主要介紹了點贊功能使用MySQL還是Redis，這是最近面試時被問到的1道面試題，本篇博客對此問題進(jìn)行總結(jié)分享，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-12-12
在linux服務(wù)器上配置mysql并開放3306端口的操作步驟
這篇文章主要介紹了在linux服務(wù)器上配置mysql并開放3306端口,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-09-09
解讀sql中timestamp和datetime之間的轉(zhuǎn)換
這篇文章主要介紹了解讀sql中timestamp和datetime之間的轉(zhuǎn)換方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-12-12