mysql主從復制、讀寫分離服務(centos)

一、目標：搭建兩臺MySQL服務器，一臺作為主服務器，一臺作為從服務器，實現(xiàn)主從復制

二、環(huán)境：

三、原理：

數(shù)據(jù)庫之所以能進行主從復制，主要是因為二進制文件binlog的存在。多臺數(shù)據(jù)庫之間可以通過線程進行通信，從庫不斷的從主庫讀取binlog日志并且把內(nèi)容同步到從庫上。


image.png

四、配置步驟：

1. 保證兩個數(shù)據(jù)庫中的庫和數(shù)據(jù)是一致的；

（以下為主數(shù)據(jù)庫）

2. 在主數(shù)據(jù)中創(chuàng)建一個同步賬號（可不創(chuàng)建使用現(xiàn)有的），如果僅僅為了主從復制創(chuàng)建賬號，只需要授予REPLICATION SLAVE權限。（此賬號是主從復制binlog記錄使用的，不要與<從數(shù)據(jù)庫>測試賬號混淆） 
mysql> GRANT REPLICATION SLAVE ON *.* to 'fei'@'172.18.0.169' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

說明：

備用：
set global validate_password_mixed_case_count=0; 
set global validate_password_length=3; 
set global validate_password_special_char_count=0; 
set global validate_password_policy=0; 
set global validate_password_number_count=3;
SHOW VARIABLES LIKE 'validate_password%'; 
GRANT REPLICATION SLAVE ON *.* to 'fei'@'172.18.0.169' identified by '123';
  • 如果報1819錯誤表示密碼太簡單了，先運行set global validate_password_policy=0;設置完這句以后密碼就只判斷長度了，運行set global validate_password_number_count=3;
  • 查看添加的用戶：
    select user,host from mysql.user;
3. 配置主數(shù)據(jù)庫

1）要主數(shù)據(jù)庫，你必須要啟用二進制日志（binary logging），并且創(chuàng)建一個唯一的Server ID，這步驟可能要重啟MySQL。
2）主服務器發(fā)送變更記錄到從服務器依賴的是二進制日志，如果沒啟用二進制日志，復制操作不能實現(xiàn)（主庫復制到從庫）。
3）復制組中的每臺服務器都要配置唯一的Server ID，取值范圍是1到(232)?1，你自己決定取值。
4）配置二進制日志和Server ID，你需要關閉MySQL和編輯my.cnf或者my.ini文件，在 [mysqld] 節(jié)點下添加配置。
5）下面是啟用二進制日志，日志文件名以“master-bin”作為前綴，Server ID配置為1，如下：

[mysqld]
log-bin=mysql-bin # 默認在是/var/lib/mysql下
server-id=1 #主庫id，必須唯一
binlog-do-db=test # 要同步的庫
binlog-ignore-db=mysql #不給從機同步的庫(多個寫多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys 
expire_logs_days=7 #自動清理 7 天前的log文件,可根據(jù)需要修改
4. 重啟mysql
systemctl restart mysqld
5. 查看主服務器狀態(tài)：
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 |     8518 |     test     | mysql            |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

注意：記錄好File和Position，后面要用

（以下為從數(shù)據(jù)庫）

6. 配置從數(shù)據(jù)庫：

1）從服務器，同理，要分配一個唯一的Server ID，需要關閉MySQL，修改好my.cnf后再重啟，如下：

[mysqld]
server-id = 2 # 集群內(nèi)唯一
log-bin=salve-bin
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
# read-only=true # 從庫只讀，但是root依然可以修改，所以需要設置非root賬號進行使用

2）在從服務器里配置連接主服務器的信息：
進入mysql:

mysql> stop slave;

mysql> change master to master_host='172.18.0.202', master_port=3306, master_user='fei', master_password='123', master_log_file='master-bin.000003', master_log_pos=8515;

mysql> start slave;

說明：

  • 172.18.0.202是主服務器的id。
  • master_log_file='master-bin.000003'是主服務器的File（你主服務器查出來的是什么就寫什么）。
  • master_log_pos=8515是主服務器的Position（你主服務器查出來的是什么就寫什么）。
  • 每次重新啟動主服務器，master_log_file和master_log_pos都會變。

3）查看狀態(tài)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.0.202
                  Master_User: fei
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 8518
               Relay_Log_File: slave-relay-bin.000020
                Relay_Log_Pos: 4020
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

說明：

  • Slave_IO_Running: Yes
  • Slave_SQL_Running: Yes
    都是yes就說明成功了。

4）若 Slave_SQL_Running: no 請重復執(zhí)行以下內(nèi)容，直至yes：


mysql>stop slave; 
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 
mysql>start slave;

5）需要為從庫創(chuàng)建一個登錄主庫的賬號

create user 'test'@'localhost' identified by '123';
grant select,insert,update,delete on test.* to 'test'@'localhost' identified by '123';
flush privileges;

五、測試

  • 主庫添加信息"fei"
mysql> select * from box;
+----+---------+------+
| id | name    | flag |
+----+---------+------+
| 16 | test222 | A    |
| 17 | test111 | A    |
| 18 | fei     | A    |
+----+---------+------+
3 rows in set (0.00 sec)
  • 從庫查看：
mysql> select * from box;
+----+------+------+
| id | name | flag |
+----+------+------+
| 18 | fei  | A    |
+----+------+------+

這邊我們也發(fā)現(xiàn)，因為主從是通過binlog進行同步的，所以在同步之前的數(shù)據(jù)沒有寫入到當前l(fā)og里面，因此也就沒有辦法自動進行同步了。

六、一主多從

有了主從復制的操作案例。我們進行一主多從的配置時是非常簡單的。只需要按照相同的配置，再添加一臺從庫服務器即可。
從服務器配置如下：

server-id = 3 # 唯一
read-only=true

mysql> change master to master_host='172.18.0.171', master_port=3306, master_user='tom', master_password='123', master_log_file='master-bin.000003', master_log_pos=8515;

mysql> start slave;

為從庫創(chuàng)建一個登錄主庫的賬號

create user 'tom'@'localhost' identified by '123';
grant select,insert,update,delete on test.* to 'tom'@'localhost' identified by '123';
flush privileges;

搞定，按照主從復制的方法進行測試。當主庫進行數(shù)據(jù)添加的時候，多個從庫進行了同步的更新

七、讀寫分離

  • 對于mysql單實例數(shù)據(jù)庫和master庫，如果需要設置為只讀狀態(tài)，需要進行如下操作和設置：
mysql -uroot -p
mysql> show global variables like "%read_only%";
mysql> flush tables with read lock;
mysql> set global read_only=1;
mysql> show global variables like "%read_only%";
  • 將MySQL從只讀設置為讀寫狀態(tài)的命令：
mysql> unlock tables;
mysql> set global read_only=0;
  • 對于需要保證master-slave主從同步的salve庫，如果要設置為只讀狀態(tài)，需要執(zhí)行的命令為：
mysql> set global read_only=1;
  • 將salve庫從只讀狀態(tài)變?yōu)樽x寫狀態(tài)，需要執(zhí)行的命令是：
mysql> set global read_only=0;

也可以通過配置文件：

read-only=true
  • 對于數(shù)據(jù)庫讀寫狀態(tài)，主要靠 read_only=1全局參數(shù)來設定；默認情況下，數(shù)據(jù)庫是用于讀寫操作的，所以read_only參數(shù)也是0或faluse狀態(tài)，這時候不論是本地用戶還是遠程訪問數(shù)據(jù)庫的用戶，都可以進行讀寫操作；如需設置為只讀狀態(tài)，將該read_only參數(shù)設置為1或TRUE狀態(tài)，但設置read_only=1狀態(tài)有兩個需要注意的地方：
  1. read_only=1只讀模式，不會影響slave同步復制的功能，所以在MySQL slave庫中設定了read_only=1后，通過show slave status\G命令查看salve狀態(tài)，可以看到salve仍然會讀取master上的日志，并且在slave庫中應用日志，保證主從數(shù)據(jù)庫同步一致；
  2. read_only=1只讀模式，可以限定普通用戶進行數(shù)據(jù)修改的操作，但不會限定具有super權限的用戶（root）的數(shù)據(jù)修改操作；在MySQL中設置read_only=1后，普通的應用用戶進行insert、update、delete等會產(chǎn)生數(shù)據(jù)變化的DML操作時，都會報出數(shù)據(jù)庫處于只讀模式不能發(fā)生數(shù)據(jù)變化的錯誤，但具有super權限的用戶，例如在本地或遠程通過root用戶登錄到數(shù)據(jù)庫，還是可以進行數(shù)據(jù)變化的DML操作；

  • 為了確保所有用戶，包括具有super權限的用戶也不能進行讀寫操作，就需要執(zhí)行給所有的表加讀鎖的命令 flush tables with read lock;這樣使用具有super權限的用戶登錄數(shù)據(jù)庫，想要發(fā)生數(shù)據(jù)變化的操作時，也會提示表被鎖定不能修改的報錯。

  • 這樣通過 設置set global read_only=1;和flush tables with read lock;兩條命令，就可以確保數(shù)據(jù)庫處于只讀模式，不會發(fā)生任何數(shù)據(jù)改變，在MySQL進行數(shù)據(jù)庫遷移時，限定master主庫不能有任何數(shù)據(jù)變化，就可以通過這種方式來設定。

  • 但同時由于加表鎖的命令對數(shù)據(jù)庫表限定非常嚴格，如果再slave從庫上執(zhí)行這個命令后，slave庫可以從master讀取binlog日志，但不能夠應用日志，slave庫不能發(fā)生數(shù)據(jù)改變，當然也不能夠實現(xiàn)主從同步了，這時如果使用 unlock tables;解除全局的表讀鎖，slave就會應用從master讀取到的binlog日志，繼續(xù)保證主從庫數(shù)據(jù)庫一致同步。

  • 為了保證主從同步可以一直進行，在slave庫上要保證具有super權限的root等用戶只能在本地登錄，不會發(fā)生數(shù)據(jù)變化，其他遠程連接的應用用戶只按需分配為select,insert,update,delete等權限，保證沒有super權限，則只需要將salve設定read_only=1模式，即可保證主從同步，又可以實現(xiàn)從庫只讀。

  • 當然設定了read_only=1后，所有的select查詢操作都是可以正常進行的。

八、互為主從

互為主從時，需要注意id步長的問題，每一臺只生成一個固定步長的id。這邊因為是兩臺機器，所以步長為2。
A數(shù)據(jù)庫只生產(chǎn)ID為 1 3 5 7 9 。。。 的數(shù)據(jù)。
B數(shù)據(jù)庫只生產(chǎn)ID為 2 4 6 8 10 。。。。

auto-increment-increment = 2 
auto-increment-offset = 100（另外一臺配置單數(shù)101）

刪掉所有日志，reset slave;
再執(zhí)行flush logs;
可以保證你不會因為binlog的原因導致出錯。
此時開啟start slave;我們發(fā)現(xiàn)


image.png

兩臺mysql服務器按照自己預先設置的ID步長進行數(shù)據(jù)添加，ID不沖突，并且互為主從，互相復制。

九、多主多從結構

多主多從結構，其實就是在雙主結構上把從庫也加入進來。唯一需要注意的：從庫只會讀取一臺主庫上的binlog，而每臺主庫的binlog都是“殘缺”的，因此需要使用

log-slave-updates=on

來促使多臺主庫之間更新互相之間的binlog，重啟服務即可。

十、測試與思考

  1. 如果從庫數(shù)據(jù)不同步，會出現(xiàn)什么情況？
    答：如果從庫人為加入一條數(shù)據(jù)，那么同步就失效了，因此主從架構里從庫一定不要寫入數(shù)據(jù)。
  2. 如果同步之前，主庫就有數(shù)據(jù)會怎么樣？
    答：不會自動同步，只會同步binlog里面的數(shù)據(jù)。所以還是停掉主庫（防止數(shù)據(jù)寫入），先進行備份，再進行主從架構配置。
  3. 從庫怎么設置智能只讀？
    答：read-only=true可以讓從庫只讀，但是這邊依然無法限制root權限的人進行修改和寫操作，一般做法是創(chuàng)建一個普通權限的用戶，登錄的時候用普通權限，這樣就只能只讀了。但是如果用root依然是可以修改的。
  4. 如果主庫重啟會怎么樣？
    答：binlog的名稱發(fā)生了改變。主庫重啟，從庫會自動跟上binlog的位置。
  5. 如果從庫重啟會怎么樣？
    答：從庫會自動跟上binlog的位置。
  6. 長時間關機會倒是BInlog更新對不上。（重點）
    答：需要到日志目錄下（正常是/var/lib/mysql）將所有binlog刪除。刪除完回到Mysql命令行執(zhí)行flush logs;刷新日志。必要時reset slave;重置一些從庫信息。然后再繼續(xù)配置主從架構。
reset slave;
flush logs;

—————————————————————————————

問題一：如果配置都沒問題，但就是看不到庫和表，這里必須自己手動建立一樣的庫和表，之后就可以看到數(shù)據(jù)同步了。
問題二：當使用 grant 權限列表 on 數(shù)據(jù)庫 to ‘用戶名’@’訪問主機’ identified by ‘密碼’; 時會出現(xiàn)”……near ‘identified by ‘密碼” at line 1”這個錯誤
  • 因為新版的的mysql版本已經(jīng)將創(chuàng)建賬戶和賦予權限的方式分開了
  • 解決辦法:創(chuàng)建賬戶:create user ‘用戶名’@’訪問主機’ identified by ‘密碼’;賦予權限:grant 權限列表 on 數(shù)據(jù)庫 to ‘用戶名’@’訪問主機’ with grant option; 
create user 'win'@'localhost' identified by '123';
grant select,insert,update,delete on test.* to 'win'@'localhost' with grant option;
問題三：Mysql主從同步Slave_IO_Running：Connecting ; Slave_SQL_Running：Yes的情況故障排除

參考：https://blog.csdn.net/csdn317797805/article/details/100932662
參考：https://blog.csdn.net/zleiw/article/details/78243316

最后編輯于 ：
?著作權歸作者所有,轉載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

友情鏈接更多精彩內(nèi)容