前言
MySQL 有一個比較重要的功能是主從復(fù)制耻讽，也就是一個主庫洒琢，可以設(shè)置 n 個從庫秧秉，從庫的數(shù)據(jù)能夠自動和主庫的數(shù)據(jù)保持一致。主從復(fù)制的架構(gòu)有兩個好處纬凤，一個是進(jìn)行數(shù)據(jù)自動的備份福贞，數(shù)據(jù)在主庫和從庫上都有一份撩嚼，如果主庫掛了停士，從庫還有一份完整的數(shù)據(jù)挖帘。另一個是可以實現(xiàn)數(shù)據(jù)庫訪問的讀寫分離，提高數(shù)據(jù)庫的吞吐量恋技。也就是對數(shù)據(jù)的更新操作是在主庫中完成的拇舀，而讀取操作在多個從庫中完成的，從而實現(xiàn)了負(fù)荷分擔(dān)的效果蜻底，提高數(shù)據(jù)庫的訪問效率骄崩。
MySQL 的主從復(fù)制是通過他的歸檔日志（binlog） 來實現(xiàn)的”「ǎ基本的過程就是從庫在一個線程中和主庫建立一個長連接要拂，告訴主庫從主庫同步的 binlog 的位置，然后主庫將這個位置之后的 binlog 日志內(nèi)容發(fā)送給從庫站楚，從庫獲取到主庫發(fā)送過來的 binlog 內(nèi)容后先寫入到本地的中轉(zhuǎn)日志（relaylog）中脱惰，然后從庫另一個進(jìn)程在從中轉(zhuǎn)日志中讀取內(nèi)容并解析成為 sql 語句在從庫中執(zhí)行，從而保證了主從的同步窿春。具體的架構(gòu)如下圖所示拉一。
要設(shè)置主從復(fù)制，有幾個前提：
- 首先旧乞，主庫和從庫的數(shù)據(jù)庫的版本保持一致蔚润。
- 第二，主從復(fù)制集群中每個數(shù)據(jù)庫實例的 server-id 的值不能重復(fù)尺栖。
- 第三嫡纠，要開啟歸檔日志并且歸檔日志的格式選擇為 row 方式。這兩個在 MySQL 8 中默認(rèn)就是這樣延赌，所以不需要設(shè)置货徙，如果早期版本默認(rèn)值不同需要在配置文件中人工設(shè)置。
全新的主庫設(shè)置從庫
這里皮胡，我們以一個全新安裝的主庫為例來說明如何設(shè)置一個主從復(fù)制的MySQL 集群痴颊。以我們在前一篇文章 《MySQL 8 二進(jìn)制方式安裝到 CentOS 7 中》 安裝的數(shù)據(jù)庫作為主庫，在服務(wù)器 10.110.2.72 中安裝一個版本一樣的 MySQL 庫作為從庫屡贺。特別注意蠢棱，在這里的 server-id 值要設(shè)置為和主庫設(shè)置的 71 不一樣，這里我們把他設(shè)置為
主庫設(shè)置
首先在在 MySQL 中增加一個可以進(jìn)行主從復(fù)制權(quán)限的用戶甩栈。在 mysql 交互環(huán)境中執(zhí)行如下命令
mysql>create user 'repl'@'10.110.2.72' identified by 'reple_password';
mysql>grant replication slave on *.* to 'repl'@'10.110.2.72';
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 888 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
最后一個命令獲取到當(dāng)前歸檔日志的名字和位置泻仙，后面從服務(wù)器設(shè)置主從復(fù)制的時候需要從這個位置開始。
從庫設(shè)置
得到這些信息后量没，我們就可以登錄到 10.10.2.72 服務(wù)器上的數(shù)據(jù)庫玉转，然后通過下面的命令將這個數(shù)據(jù)庫設(shè)置為 10.110.2.71 數(shù)據(jù)庫的從服務(wù)器。
mysql> change master to
-> master_host='10.110.2.71',
-> master_port=38965,
-> master_user='repl',
-> master_password='replpassword',
-> master_log_file='binlog.000001',
-> master_log_pos=888;
mysql>start slave;
mysql>show slave slave\G;
這個命令將當(dāng)前數(shù)據(jù)庫設(shè)置為 10.110.2.71 數(shù)據(jù)庫的從庫殴蹄，并且從歸檔日志 binlog.000001 的位置 888 開始進(jìn)行復(fù)制究抓。
查看從庫的狀態(tài)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.110.2.71
Master_User: repl
Master_Port: 38965
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 888
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 319
Relay_Master_Log_File: binlog.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: 4035
Relay_Log_Space: 1431
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: 71
Master_UUID: 2980729e-23c6-11e9-aec6-005056881ba9
Master_Info_File: mysql.slave_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:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)
ERROR:
No query specified
Slave_IO_State 的值為 Waiting for master to send event 猾担，表示已經(jīng)準(zhǔn)備好接受主庫發(fā)送過來的歸檔日志進(jìn)行處理了。
效果演示
登錄主庫執(zhí)行如下命令
mysql>create database test1;
mysql>use test1;
mysql>create table test1(id bigint);
mysql>insert into test1 values(1);
mysql>insert into test1 values(2);
在從庫查看數(shù)據(jù)庫狀態(tài)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test1;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| test1 |
+-----------------+
1 row in set (0.01 sec)
mysql> select * from test1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql>
可以看到刺下，主庫中做的操作绑嘹，都同步到從庫中來了。
同步指定的數(shù)據(jù)庫
上面的設(shè)置中橘茉，我們是將主庫中的所有數(shù)據(jù)庫都同步到從庫中來了工腋。實際上，我們還可以通過命令的方式同步指定的數(shù)據(jù)庫或者數(shù)據(jù)庫的數(shù)據(jù)表畅卓。在從庫中執(zhí)行下面的命令將會指定只同步數(shù)據(jù)庫 test1
mysql> stop slave;
Query OK, 0 rows affected (0.16 sec)
mysql> change replication filter replicate_do_db=(test1);
Query OK, 0 rows affected (0.00 sec)
ysql> start slave;
Query OK, 0 rows affected (0.11 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.110.2.71
Master_User: repl
Master_Port: 38965
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 4035
Relay_Log_File: localhost-relay-bin.000010
Relay_Log_Pos: 319
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1
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: 4035
Relay_Log_Space: 1431
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: 71
Master_UUID: 2980729e-23c6-11e9-aec6-005056881ba9
Master_Info_File: mysql.slave_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:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)
這樣擅腰，只有主庫中 test1 數(shù)據(jù)庫中的變化會同步到從庫中來，其余的數(shù)據(jù)庫的變化則不會同步翁潘。
這個命令產(chǎn)生的效果沒有保存下來惕鼓，如果數(shù)據(jù)庫重啟了，那么就會失效唐础。如果要生效就需要將配置寫在 my.cnf 文件中箱歧。在從庫中，將 my.cnf 文件內(nèi)容更改為如下即可一膨。
[mysqld]
server-id=72
port=38965
character-set-server=utf8mb4
default-time_zone='+8:00'
lower_case_table_names=1
#max_connections=3000
#max_connect_errors=100000
default_authentication_plugin=mysql_native_password
datadir=/data/mysql/data
socket=/data/mysql/mysql.sock
log-error=/data/mysql/logs/mysqld.log
pid-file=/data/mysql/mysqld.pid
long_query_time=1
slow_query_log = 1
slow_query_log_file=/data/mysql/logs/slow.log
replicate_do_db=test1
[client]
port=38965
default-character-set=utf8mb4
socket=/data/mysql/mysql.sock
[mysql]
no-auto-rehash
default-character-set=utf8mb4
加入了新行 replicate_do_db=test1呀邢。然后重新啟動數(shù)據(jù)庫即可。
非空主庫主從設(shè)置
很多時候豹绪，設(shè)置主從復(fù)制的時候价淌，主庫已經(jīng)運行了一段時間，有了一些業(yè)務(wù)數(shù)據(jù)瞒津，那么這個時候我們首先將主庫設(shè)置為只讀狀態(tài)蝉衣，不允許新數(shù)據(jù)寫入，然后查看當(dāng)前的歸檔日志狀態(tài)巷蚪，記錄下來后將數(shù)據(jù)庫備份出來病毡，恢復(fù)從庫的可寫狀態(tài)，最后把備份出來的數(shù)據(jù)庫恢復(fù)到從庫中屁柏，再設(shè)置為從剛才查詢出來的歸檔日志和歸檔日志的位置開始進(jìn)行同步即可啦膜。
