前言
構(gòu)建一個Mysql分布式集群需要對數(shù)據(jù)庫的原理有較深的認識党远，而深奧復(fù)雜的數(shù)據(jù)庫原理往往讓很多讀者中途放棄。本系列嘗試用最簡單的語言介紹Mysql分布式集群涉及到的原理，并結(jié)合實踐的方式給讀者介紹如何構(gòu)建一個Mysql分布式集群。
目錄
1.Mysql分布式集群（一）主從復(fù)制
2.Mysql分布式集群（二）主主復(fù)制和高可用
主主復(fù)制
主從復(fù)制的情況下，主節(jié)點只有一個蹋笼，當(dāng)主節(jié)點如果發(fā)生意外宕機則整個數(shù)據(jù)庫系統(tǒng)就不在可用，因此需要對主節(jié)點做一個備份躁垛，一旦主節(jié)點發(fā)生問題剖毯，備份節(jié)點能立刻替換，為了保持備份節(jié)點和主節(jié)點同步教馆，也為了保持Slave節(jié)點能同步備份節(jié)點逊谋，需要對主節(jié)點和備份節(jié)點做主主復(fù)制。
實驗環(huán)境：
Master ip :192.168.179.132
Master backup ip: 192.168.179.134
Slave1 ip :192.168.179.133
Slave1 ip :192.168.179.131
操作系統(tǒng)：Ubuntu14.04
mysql版本：5.7.20
keepalived版本：keepalived-1.3.9
高可用結(jié)構(gòu)
本文通過一個簡單主主復(fù)制和兩個主從復(fù)制完成一個簡單的高可用例子土铺，架構(gòu)圖如下所示胶滋。其中Master(ip：192.168.179.132)為主節(jié)點板鬓，Master backup (ip:192.168.179.134)為主節(jié)點的從節(jié)點，同時主節(jié)點也是該節(jié)點的從節(jié)點究恤，二者互為主從俭令。本文采用keepalived監(jiān)聽雙主的狀態(tài)，一旦主節(jié)點發(fā)生故障部宿，則切換到備用節(jié)點上去抄腔。
主主復(fù)制和主從復(fù)制
主主復(fù)制采用主從復(fù)制的一樣的策略，在配置好從節(jié)點之后理张，打開從節(jié)點的bin log日志赫蛇，并在主節(jié)點端，指向從節(jié)點的bin log日志即可悟耘。
Master端配置
1.用戶授權(quán)
mysql> grant all on *.* to 'master'@'192.168.179.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> select user,host from mysql.user;
+---------------+---------------+
| user | host |
+---------------+---------------+
| master | 192.168.179.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+---------------+
4 rows in set (0.30 sec)
mysql> show grants for 'master'@'192.168.179.%';
+---------------------------------------------------------+
| Grants for master@192.168.179.% |
+---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'master'@'192.168.179.%' |
+---------------------------------------------------------+
1 row in set (0.00 sec)
以上我們授予master用戶所有庫所有表的權(quán)限，在Master backup端遠程登錄Master的mysq暂幼。如果能登錄則該步成功赚瘦，如果出現(xiàn)下方錯誤奏寨，則參考這里中的方法解決
root@ubuntu:~# mysql -uslave -p -h192.168.179.128;
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.179.128' (111)
2.修改Mysql配置文件
root@ubuntu:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=1
log-bin =mysql-bin
log-slave-updates=true
auto_increment_increment=2
auto_increment_offset=1
需要注意的是一下兩點：
1.主主復(fù)制為了避免主鍵沖突，采用以下的方式的設(shè)置主鍵
auto_increment_increment=2 #每次以2為步長進行增長
auto_increment_offset=1 #從1開始
2.主主復(fù)制中病瞳，作為從的一段，需要配置如下變量套菜。如果不配置的話，數(shù)據(jù)復(fù)制事件不會更新到bin log文件中逗柴，因此真正的Slave節(jié)點捕捉不到bin log日志的改變蛹头，會出現(xiàn)Master和Slave數(shù)據(jù)不一致的問題
log-slave-updates=true
Master Backup端配置
1.用戶授權(quán)
mysql> grant all on *.* to 'master'@'192.168.179.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> select user,host from mysql.user;
+---------------+---------------+
| user | host |
+---------------+---------------+
| master | 192.168.179.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+---------------+
4 rows in set (0.30 sec)
mysql> show grants for 'master'@'192.168.179.%';
+---------------------------------------------------------+
| Grants for master@192.168.179.% |
+---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'master'@'192.168.179.%' |
+---------------------------------------------------------+
1 row in set (0.00 sec)
以上我們授予master用戶所有庫所有表的權(quán)限，在Master backup端遠程登錄Master的mysq戏溺。如果能登錄則該步成功渣蜗，如果出現(xiàn)下方錯誤，則參考這里中的方法解決
root@ubuntu:~# mysql -uslave -p -h192.168.179.128;
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.179.128' (111)
2.修改Mysql配置文件
root@ubuntu:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=1
log-bin =mysql-bin
log-slave-updates=true
auto_increment_increment=2
auto_increment_offset=2
需要注意的是一下兩點：
1.設(shè)置主鍵方式
auto_increment_increment=2 #每次以2為步長進行增長
auto_increment_offset=1 #從2開始
2.復(fù)制事件同步到bin log
log-slave-updates=true
從節(jié)點配置
1.Master端從配置
先在Master backup 端查看bin log日志狀態(tài)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)
指向Master backup 的bin log
mysql> change master to master_host='192.168.179.134',
-> master_user='master',
-> master_password='123456',
-> master_log_file='mysql-bin.000001', master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.34 sec)
mysql> start slave;
Query OK, 0 rows affected (0.15 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.179.134
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: ubuntu-relay-bin.000002
Relay_Log_Pos: 320
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
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 528
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: 2
Master_UUID: e7c00798-b94e-11e7-8656-000c29f0d4cf
Master_Info_File: /var/lib/mysql/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)
可以發(fā)現(xiàn)已經(jīng)配置成功.
2.Master backup 端
步驟同Master端配置（略）
3.Slave1端從配置
配置方法同Master端配置旷祸，只需要注意的是要指向Master的bin log（略）
4.Slave2端從配置
配置方法同Master端配置耕拷，只需要注意的是要指向Master backup的bin log（略）
此時配置工作已經(jīng)完成，可以通過對Master表操作來測試托享，主主骚烧，主從復(fù)制是否成功浸赫。
Mysql高可用
Keepalived的作用是檢測服務(wù)器的狀態(tài)，如果有一臺web服務(wù)器宕機赃绊，或工作出現(xiàn)故障既峡，Keepalived將檢測到，并將有故障的服務(wù)器從系統(tǒng)中剔除碧查，同時使用其他服務(wù)器代替該服務(wù)器的工作涧狮，當(dāng)服務(wù)器工作正常后Keepalived自動將服務(wù)器加入到服務(wù)器群中，這些工作全部自動完成么夫，不需要人工干涉者冤，需要人工做的只是修復(fù)故障的服務(wù)器，本文采用keepalived用于檢測mysql的狀態(tài)档痪，以達到mysql高可用的狀態(tài)涉枫。
Keepalived原理
Keepalived采用的是為多臺機器配置同一個虛擬地址，該虛擬地址有且只有一個真實的節(jié)點腐螟，該節(jié)點宕機之后愿汰，該虛擬地址會會自動（根據(jù)一定的策略）映射到一下個節(jié)點上。
keepalived安裝
以下操作在Master和Master backup節(jié)點上同時進行乐纸。
下載keepalived-1.3.9.tar.gz衬廷，上傳到Master和Master backup上
解壓縮后進入到 keepalived-1.3.9/目錄中，由于keepalived采用的是源碼安裝汽绢，因此需要對它進行編譯安裝吗跋，并且需要依賴工具：
apt-get install libssl-dev
apt-get install openssl
apt-get install libpopt-dev
以上完成之后，可以進行如下操作：
root@ubuntu:/software/keepalived-1.3.9# ./configure
......
Keepalived configuration
------------------------
Keepalived version : 1.3.9
Compiler : gcc
Preprocessor flags :
Compiler flags : -Wall -Wunused -Wstrict-prototypes -Wextra -g -O2
Linker flags :
Extra Lib : -lcrypto -lssl
Use IPVS Framework : Yes
IPVS use libnl : No
IPVS syncd attributes : No
IPVS 64 bit stats : No
fwmark socket support : Yes
Use VRRP Framework : Yes
Use VRRP VMAC : Yes
Use VRRP authentication : Yes
With ip rules/routes : Yes
SNMP vrrp support : No
SNMP checker support : No
SNMP RFCv2 support : No
SNMP RFCv3 support : No
DBUS support : No
SHA1 support : No
Use Debug flags : No
Use Json output : No
Stacktrace support : No
Memory alloc check : No
libnl version : None
Use IPv4 devconf : No
Use libiptc : No
Use libipset : No
init type : upstart
Build genhash : Yes
Build documentation : No
root@ubuntu:/software/keepalived-1.3.9#make
root@ubuntu:/software/keepalived-1.3.9#make install
keepalived配置
1.Master 端
修改keepalived配置文件
root@ubuntu:~# vim /usr/local/etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs{
router_id LVS_MASTER
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.179.2/24 #虛擬主機ip
}
}
virtual_server 192.168.179.2 3306 {#設(shè)置虛擬ip地址和端口號
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP #采用TCP協(xié)議
real_server 192.168.179.132 3306 {#檢測的真實節(jié)點Ip和端口號
weight 1
notify_down /usr/local/etc/keepalived/mysql.sh #如果發(fā)現(xiàn)異常觸發(fā)該腳本
TCP_CHECK{
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
2.Master backup 端
修改keepalived配置文件
root@ubuntu:~# vim /usr/local/etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs{
router_id LVS_MASTER
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.179.2/24 #虛擬主機ip
}
}
virtual_server 192.168.179.2 3306 {#設(shè)置虛擬ip地址和端口號
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP #采用TCP協(xié)議
real_server 192.168.179.134 3306 {#檢測的真實節(jié)點Ip和端口號
weight 1
notify_down /usr/local/etc/keepalived/mysql.sh #如果發(fā)現(xiàn)異常觸發(fā)該腳本
TCP_CHECK{
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
以上的配置同Master中的配置，唯一不同的是real_server中的ip地址疆拘。如此哎迄，Master和Master backup上安裝的keepalived 才能分別檢測本機的Mysql服務(wù)器漱挚。另外匾灶，當(dāng)Mysql 出現(xiàn)問題的時候會觸發(fā)一個腳本，用于關(guān)掉本機的keepalived進程颊糜，之后才能進行切換到另外一個節(jié)點上去，該腳本如下：
root@ubuntu:~# vim /usr/local/etc/keepalived/mysql.sh
#!/bin/bash
pkill keepalived
在Master和Master backup上分別啟動keepalived
#為keepalived腳本增加運行權(quán)限
root@ubuntu:/usr/local/sbin# chmod +x keepalived
#手工的制定配置文件啟動
root@ubuntu:/usr/local/sbin# ./keepalived -D -f /usr/local/etc/keepalived/keepalived.conf
root@ubuntu:/usr/local/sbin# ps -aux |grep keepalived;
Warning: bad ps syntax, perhaps a bogus '-'? See http://procps.sf.net/faq.html
root 29710 0.4 0.0 13064 732 ? Ss 04:00 0:00 ./keepalived -D -f /usr/local/etc/keepalived/keepalived.conf
root 29711 0.5 0.1 21496 1224 ? S 04:00 0:00 ./keepalived -D -f /usr/local/etc/keepalived/keepalived.conf
root 29712 1.1 0.1 15160 1216 ? S 04:00 0:00 ./keepalived -D -f /usr/local/etc/keepalived/keepalived.conf
root 29714 6.0 0.0 10464 920 pts/2 S+ 04:00 0:00 grep --color=auto keepalived
至此，整個配置都已經(jīng)完成鸟赫。下邊開始測試
Mysql高可用實驗
1.查看keepalived虛擬的地址在哪一臺機器上
Master端
root@ubuntu:/usr/local/sbin# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:f0:d4:cf brd ff:ff:ff:ff:ff:ff
inet 192.168.179.132/24 brd 192.168.179.255 scope global eth0
valid_lft forever preferred_lft forever
inet 192.168.179.2/24 scope global secondary eth0
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fef0:d4cf/64 scope link
valid_lft forever preferred_lft forever
Master backup端
root@ubuntu:/var/log# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:ba:4d:47 brd ff:ff:ff:ff:ff:ff
inet 192.168.179.134/24 brd 192.168.179.255 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:feba:4d47/64 scope link
valid_lft forever preferred_lft forever
可以發(fā)現(xiàn)只有Master 中eth0中增加了192.168.179.2這個虛擬ip.此時kill 掉master中keepalived線程會發(fā)現(xiàn)Master backup 中增加了一個虛擬ip. 在mysql為應(yīng)用提供服務(wù)的時候台谢，只需要提供虛擬ip即可朋沮，當(dāng)雙主中的節(jié)點中一個節(jié)點宕機后缀壤，另外一個節(jié)點可以繼續(xù)提供服務(wù)塘慕。
