下文內容主要給大家?guī)韒ysql雙主配置講義，這里所講到的知識，與書籍略有不同，都是創(chuàng)新互聯(lián)專業(yè)技術人員在與用戶接觸過程中，總結出來的，具有一定的經驗分享價值，希望給廣大讀者帶來幫助。
結尾2
1.數(shù)據(jù)庫架構圖：
2.安裝環(huán)境信息：
master1 172.16.90.13 CentOS 7.2 Keepalived讀 VIP：172.16.90.15
master2 172.16.90.14 CentOS 7.2 Keepalived讀 VIP：172.16.90.16
3.MySQL雙主配置
master1修改my.cnf,新增如下配置：
重慶分公司
重慶分公司
server-id=13
log-bin=mysql-bin
sync-binlog=1
binlog-checksum=none
binlog-format=mixed
auto-increment-increment=2
auto-increment-offset=1
log-slave-updates
slave-skip-errors=allmaster2修改my.cnf,新增如下配置：
server-id=14
log-bin=mysql-bin
sync-binlog=1
binlog-checksum=none
binlog-format=mixed
auto-increment-increment=2
auto-increment-offset=1
log-slave-updates
slave-skip-errors=all在master1中為mysql從庫賬戶授權：
grant replication slave on . to 'sync'@'%' identified by 'syncpwd';
flush privileges;
show master status; #當前主庫狀態(tài),即master1
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 599 | | | |
+------------------+----------+--------------+------------------+-------------------+
在master2中為mysql從庫賬戶授權：
grant replication slave on . to 'sync'@'%' identified by 'syncpwd';
flush privileges;
show master status; #當前主庫狀態(tài),即master2
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 468 | | | |
+------------------+----------+--------------+------------------+-------------------+
在maste1中指定master2為主庫：
stop slave;
change master to master_host='172.16.90.14',master_user='sync',master_password='syncpwd',master_log_file='mysql-bin.000002',master_log_pos=468;
flush privileges;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
start slave;
在maste2中指定master1為主庫：
stop slave;
change master to master_host='172.16.90.13',master_user='sync',master_password='syncpwd',master_log_file='mysql-bin.000004',master_log_pos=599;
flush privileges;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
start slave;
MySQL雙主配置完成，驗證配置成功：
show slave status\G #master1中顯示的信息
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.90.13
Master_User: sync
Master_Port: 3306
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
show slave status\G #master2中顯示的信息
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.90.14
Master_User: sync
Master_Port: 3306
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4.Keepalived高可用配置
安裝：yum install -y keepalived
啟動：systemctl stop keepalived
說明：
當兩臺云服務器都正常的時候
用戶寫數(shù)據(jù)默認訪問服務器A，如果A有異常則訪問B服務器。
用戶讀數(shù)據(jù)默認訪問服務器B，如果B有異常則訪問A服務器。
服務器A的寫數(shù)據(jù)初始權重為100，B為90
服務器A的讀數(shù)據(jù)初始權重為90，B為100
檢測進程檢測到異常時，會使得本機的權重下降20
服務器A
vrrp_script chk_master1 {
script "/opt/context/keepalive_check/chk_mysql.sh"
interval 2
weight -20
}
vrrp_instance VI_MASTER1 {
state MASTER
interface eno16780032
virtual_router_id 51
priority 100
mcast_src_ip 172.16.90.13
advert_int 1
authentication {
auth_type PASS
auth_pass 5678
}
virtual_ipaddress {
172.16.90.15
}
track_script {
chk_master1
}
}
vrrp_instance VI_MASTER2 {
state BACKUP
interface eno16780032
virtual_router_id 52
priority 90
mcast_src_ip 172.16.90.13
advert_int 1
authentication {
auth_type PASS
auth_pass 15678
}
virtual_ipaddress {
172.16.90.16
}
}服務器B
vrrp_script chk_master2 {
script "/opt/context/keepalive_check/chk_mysql.sh"
interval 2
weight -20
}
vrrp_instance VI_MASTER1 {
state BACKUP
interface eno16780032
virtual_router_id 51
priority 90
mcast_src_ip 172.16.90.14
advert_int 1
authentication {
auth_type PASS
auth_pass 5678
}
virtual_ipaddress {
172.16.90.15
}
}
vrrp_instance VI_MASTER2 {
state MASTER
interface eno16780032
virtual_router_id 52
priority 100
mcast_src_ip 172.16.90.14
advert_int 1
authentication {
auth_type PASS
auth_pass 15678
}
virtual_ipaddress {
172.16.90.16
}
track_script {
chk_master2
}
}檢測腳本
#!/bin/bash counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l) if [ "${counter}" -eq 0 ]; then systemctl stop keepalived
fi
對于以上關于mysql雙主配置講義，如果大家還有更多需要了解的可以持續(xù)關注我們創(chuàng)新互聯(lián)的行業(yè)推新，如需獲取專業(yè)解答，可在官網聯(lián)系售前售后的，希望該文章可給大家?guī)硪欢ǖ闹R更新。
另外有需要云服務器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。
