前言
構建一個Mysql分布式集群需要對數(shù)據(jù)庫的原理有較深的認識菌羽，而深奧復雜的數(shù)據(jù)庫原理往往讓很多讀者中途放棄升略。本系列嘗試用最簡單的語言介紹Mysql分布式集群涉及到的原理衷蜓，并結合實踐的方式給讀者介紹如何構建一個Mysql分布式集群拟糕。
目錄
1.Mysql分布式集群（一）主從復制
2.Mysql分布式集群（二）主主復制和高可用
主從復制
主從復制是為了完成一個和主數(shù)據(jù)庫一致的數(shù)據(jù)庫判呕，當主數(shù)據(jù)數(shù)據(jù)改變的時候，從數(shù)據(jù)庫自動保持和主數(shù)據(jù)庫一致的改變送滞。主從復制主要應用于讀寫分離侠草，數(shù)據(jù)庫備份。
實驗環(huán)境：
Master ip :192.168.178.128
Slave ip :192.168.179.129
操作系統(tǒng)：Ubuntu14.04
mysql版本：5.7.20
主從復制原理
主從復制既能在程序端完成也能在Mysql端完成犁嗅，本系列主要介紹通過配置Mysql 完成主從復制边涕。具體原理如下圖所示：
主從復制原理
1.Master對數(shù)據(jù)改變之后，會將記錄改變的事件（如：delete,update等）在Master中的Binary log（以下稱：bin log）褂微；
2.Slave會按照一定的頻率使用I/Othread讀取Master上的bin log功蜓，并將該日志中的最近改變事件，寫到Relay log（中繼日志）中宠蚂；
3.Salve通過SQL Thread 讀取Relay log ,將改變事件同步到自己的Mysql中式撼；
Master端配置
根據(jù)以上的原理可以，Slave端需要讀取Master端的bin log日志求厕，因此需要再Master端完成以下工作：
1.對遠程訪問Master數(shù)據(jù)庫的用戶授權端衰。
默認情況下root只能在本地登錄叠洗，只有授權了甘改，Slave端才能讀取Master端bin log日志旅东。
mysql> select host,user from mysql.user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
mysql> grant all on *.* to 'slave'@'192.168.179.129' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> select user,host from mysql.user;
+---------------+-----------------+
| user | host |
+---------------+-----------------+
| slave | 192.168.179.129 |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------------+
mysql> show grants for 'salve'@192.168.179.129;
+----------------------------------------------------------+
| Grants for salve@192.168.179.129 |
+----------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'salve'@'192.168.179.129' |
+----------------------------------------------------------+
1 row in set (0.00 sec)
以上我們授予slave用戶所有庫所有表的權限.
然后可以在Slave端使用用戶slave遠程登錄一下Master端的數(shù)據(jù)庫，檢查是否能遠程登錄：
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)
如果遇到以上的問題十艾，在能ping通Master的情況下抵代，請檢查兩個方面：
1.Master端的防火墻是否關閉
root@ubuntu:~# ufw status;
Status: inactive
2.如果Master端的防火墻已關，則查看Mysql的bind_address變量
mysql> show variables like '%bind_address%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| bind_address | 127.0.0.1 |
+---------------+-----------+
1 row in set (0.02 sec)
可以發(fā)現(xiàn)bind_address 地址只允許本地訪問忘嫉，因此需要修改它荤牍，可以講mysqld.cnf中注釋掉bind-address，重啟Mysql之后庆冕，在Slave端就可以正常連接了康吵。
root@ubuntu:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
root@ubuntu:~# /etc/init.d/mysql restart
* Stopping MySQL Community Server 5.7.20
....
* MySQL Community Server 5.7.20 is stopped
* Re-starting MySQL Community Server 5.7.20
.........
* MySQL Community Server 5.7.20 is started
2.打開bin log日志
默認情況下Mysql是不開啟這個日志的，如下：
mysql> show global variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-------+
打開bin log 可以通過在mysqld.cnf中指定log-bin路徑完成访递，需要注意的是如果打開bin log 一定要指定server-id晦嵌，否則重啟Mysql會報錯。
root@ubuntu:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
......
#bind-address = 127.0.0.1
server-id=1
log-bin =/var/log/mysql/mysql-bin.log
......
重啟Mysql之后拷姿，查看bin log日志狀態(tài)
mysql> show global variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/log/mysql/mysql-bin |
| log_bin_index | /var/log/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+--------------------------------+
5 rows in set (0.03 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.01 sec)
此時可以看到log_bin已經(jīng)開啟了惭载。
我們可以使用mysqlbinlog命令對bin log日志進行操作，這里就不展開講了响巢。
Slave端配置
更改Slave的配置文件
root@ubuntu:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
.......
server-id=2
relay_log = mysql-relay-bin
.......
需要注意的是server-id一定不要和Master中的server-id一樣
mysql> change master to master_host='192.168.179.128',
-> master_user='slave',
-> master_password='123456',
-> master_log_file='mysql-bin.000001', master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.34 sec)
通過以上的方式指定Master描滔，這種方式的好處是不用重啟服務器，也可以動態(tài)改變指向的Master踪古。接下來啟動Slave端
mysql> start slave;
Query OK, 0 rows affected (0.08 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.179.128
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
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: 154
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
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: 171028 03:44:54
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)
啟動Slave的時候發(fā)現(xiàn)含长，Slave_IO_Running:No，在上文的原理中已經(jīng)介紹了伏穆，Slave需要Slave_IO和Slave_SQL線程拘泞，但是此時IO線程沒有開啟。檢查Slave狀態(tài)發(fā)現(xiàn)了錯誤：
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
原來是Slave 和Master使用了一樣的UUID
Master端UUID
mysql> show variables like '%UUID%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | d7c00798-b94e-11e7-8656-000c29f0d4cf |
+---------------+--------------------------------------+
1 row in set (0.02 sec)
Slave端UUID
mysql> show variables like '%UUID%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | d7c00798-b94e-11e7-8656-000c29f0d4cf |
+---------------+--------------------------------------+
1 row in set (0.02 sec)
可以發(fā)現(xiàn)蜈出，二者的UUID確實一樣田弥，此時可以更改其中一個的UUID，本文中選擇修改Slave的UUID铡原，如下：
root@ubuntu:~# vim /var/lib/mysql/auto.cnf
[auto]
server-uuid=d7c00798-b94e-11e7-8656-000c29f0d4ce
重啟Slave端的Mysql后偷厦，發(fā)現(xiàn)Slave的IO線程也開啟了。其中Id:1為SQL線程燕刻，Id:2位IO線程
mysql> show processlist\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 3706
State: Slave has read all relay log; waiting for more updates
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 3706
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 6
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
3 rows in set (0.00 sec)
以上完成了Slave端的配置
驗證主從是否成功
1.創(chuàng)建數(shù)據(jù)庫
Master端創(chuàng)建數(shù)據(jù)庫
mysql> create database test;
Query OK, 1 row affected (0.28 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.02 sec)
Slave 端查詢數(shù)據(jù)庫是否同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.01 sec)
可以發(fā)現(xiàn)Slave端也創(chuàng)建了test庫
2.創(chuàng)建表并插入數(shù)據(jù)
Master端
mysql> use test;
Database changed
mysql> create table user(id int(3),name varchar(10));
Query OK, 0 rows affected (1.84 sec)
mysql> insert into user values(001,'kevin');
Query OK, 1 row affected (0.02 sec)
mysql> select * from user;
+------+-------+
| id | name |
+------+-------+
| 1 | kevin |
+------+-------+
1 row in set (0.04 sec)
Salve端
mysql> use test;
Database changed
mysql> create table user(id int(3),name varchar(10));
Query OK, 0 rows affected (1.84 sec)
mysql> insert into user values(001,'kevin');
Query OK, 1 row affected (0.02 sec)
mysql> select * from user;
+------+-------+
| id | name |
+------+-------+
| 1 | kevin |
+------+-------+
1 row in set (0.04 sec)
以上可以發(fā)現(xiàn)Slave已經(jīng)自動完成了同步错负。此時已經(jīng)完成了Mysql 主從復制。
補充：
如果在同步的過程中發(fā)現(xiàn)Slave節(jié)點不同步的話呻逆，可以通過以下操作完成同步
在Master端查看日志的狀態(tài)心肪，例如
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1026 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
此時Master的Position指向1026弥咪，我們需要在Slave節(jié)點中重新指向該位置
mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)
mysql> change master to master_host='192.168.179.128',
-> master_user='slave',
-> master_password='123456',
-> master_log_file='mysql-bin.000001', master_log_pos=1026;
Query OK, 0 rows affected, 2 warnings (0.34 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
通過以上的重新指定，則可以重新同步十绑。
