MySQL叢集之 主從複製 主主複製 一主多從 多主一叢 實現方式

一. 安裝MySQL

1.更換yum源:

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

具體的更換方法可參考我的另一篇博文:
#CentOS 7更換阿里和清華大學yum源
https://editor.csdn.net/md/?articleId=107546030

2. 下載repo配置檔案:

wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

3. 安裝yum源:

rpm -ivh mysql57-community-release-el7-9.noarch.rpm

4. 進入/etc/yum.repos.d/目錄執行安裝:

yum install mysql-server

因為MySQL伺服器在國外,下載十分緩慢,如果你懂得下面我說的技術,可參考我的另一篇博文,實現下載加速:

#CentOS 7橋接模式訪問本地SSR
https://blog.csdn.net/fengxueliuke/article/details/107570055

5. 啟動:

systemctl start mysqld

6. 檢視臨時密碼:

grep 'temporary password' /var/log/mysqld.log

#如果查不到臨時密碼,按照以下步驟,執行命令嘗試:
#刪除mysql殘留的資料
rm -rf /var/lib/mysql
#重新啟動mysql,會重新生成
systemctl restart mysqld
grep 'temporary password' /var/log/mysqld.log

7. 登陸:

mysql -u root -p

8. 修改密碼:

ALTER USER 'root'@'localhost' IDENTIFIED BY '@Abcd1234';

9. 開啟遠端連線:

#root 使用者名稱,% 所有人都可以訪問 password 密碼
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '@Abcd1234' WITH GRANT OPTION;

#重新載入
FLUSH PRIVILEGES;

10. 修改編碼:

#修改etc目錄下的my.cnf檔案,新增下面兩句話:
character_set_server=utf8 
init_connect='SET NAMES utf8'

11. 補充命令:

#停止mysql
systemctl stop mysqld 
#重啟MySQL
systemctl restart mysqld 
#設定開機啟動
systemctl enable mysqld 
#關閉開機啟動
systemctl disable mysqld 

二. 什麼是資料庫二進位制檔案

在瞭解資料庫叢集之前呢,我們需要了解MySQL的二進位制日誌檔案,如何通過二進位制檔案恢復資料庫資料.二進位制日誌(log-bin 日誌) :所有對資料庫狀態更改的操作(create、 drop、 update 等)。

1. 開啟二進位制檔案:

#執行下面的命令
vi /etc/my.cnf
#在配置檔案中新增以下指令
log-bin=mysql-bin
#server-id一般為ip結尾
server-id=171             

2.檢視生成的二進位制檔案

開啟之後,我們在/var/lib/mysql目錄會發現一個叫mysql-bin.000001的檔案:

3. 檢視mysql-bin.000001檔案內容:

mysqlbinlog  二進位制檔名

4. 建立測試資料庫

建立一個測試資料庫,建立一個表,往表中新增幾條資料.

5.再次檢視二進位制檔案:

我們對應的操作就已經被記錄了下來.

6. 刪除資料庫檔案,模擬資料丟失.

7. 根據二進位制檔案還原資料:

有兩種方式,一種是根據時間還原,第二種是根據大小還原.

⑴. 按照時間還原指令:

--start-datetime  #開始時間
--stop-datetime   #結束時間
格式:mysqlbinlog --start-datetime 'yyyy-MM-dd HH:mm:ss ' --stop-datetime 'yyyy-MM-dd HH:mm:ss' 二進位制檔名 | mysql -u root -p

示例:

mysqlbinlog --start-datetime '2020-07-23 16:33:33' --stop-datetime '2020-07-23 65:52:44' mysql-bin.000001 | mysql -u root -p

⑵. 按照檔案大小還原:

--start-position  #開始大小
--stop-position   #結束大小
格式:
mysqlbinlog --start-position=int -stop-position=int 二進位制檔名 | mysql -u root -p

示例:

mysqlbinlog --start-position=180 --stop-position=496 mysql-bin.000027 | mysql -uroot -p

三. 主從備份:

1. 修改my.cnf配置檔案:

server-id= 一般為ip結尾,便於區分.兩臺MySQL伺服器都需要修改,且server-id不能一致.

2. 重啟MySQL.

3. 在伺服器上授權:

#slave on *.*  代表所有庫和所有表.
#192.168.31.% 後面百分比代表改網段下所有ip都可以正常訪問主伺服器,如果允許所有ip地址,只寫一個%號即可.
grant replication slave on *.*  to 'slave'@'192.168.31.%' identified by '@Abcd1234';
#重新載入許可權表
flush privileges;

4. 檢視主伺服器的二進位制檔案:

show master status;

5. 記錄檔名和大小:

6. 從伺服器儲存授權的資訊:

mysql> change master to
    -> master_user='slave',                 #授權使用者
    -> master_password='@Abcd1234',         #密碼
    -> master_host='192.168.31.100',        #主伺服器地址
    -> master_log_file='mysql-bin.000001',  #二進位制檔名
    -> master_log_pos=601;                  #當前二進位制檔案大小
Query OK, 0 rows affected, 2 warnings (0.06 sec)

7. 檢視從伺服器產生的授權檔案:

cat  /var/lib/mysql/master.info

8. 開啟從伺服器並檢視:

mysql> start slave;     #啟動從伺服器.
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G;   #檢視從伺服器內容;
*************************** 1. row ***************************
                  Master_Host: 192.168.31.100
                  Master_User: slave
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 601
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes     #執行緒啟用
            Slave_SQL_Running: Yes     #執行緒啟用

9. 測試:

MYSQL主從複製原理:
Mysql主從同步其實是一個非同步複製的過程，要實現複製首先需要在master上開啟bin-log日誌功能，整個過程需要開啟3個執行緒，分別是Master開啟IO執行緒，slave開啟IO執行緒和SQL執行緒。
1）在從伺服器執行slave
start，從伺服器上IO執行緒會通過授權的使用者連線上master，並請求master從指定的檔案和位置之後傳送bin-log日誌內容。
2）Master伺服器接收到來自slave伺服器的IO執行緒的請求後，master伺服器上的IO執行緒根據slave伺服器傳送的指定bin-log日誌之後的內容，然後返回給slave端的IO執行緒。（返回的資訊中除了bin-log日誌內容外，還有本次返回日誌內容後在master伺服器端的新的binlog檔名以及在binlog中的下一個指定更新位置）
3）Slave的IO執行緒接收到資訊後，將接收到的日誌內容依次新增到Slave端的relay-log檔案的最末端，並將讀取到的Master端的
bin-log的檔名和位置記錄到master.info檔案中，以便在下一次讀取的時候能夠清楚的告訴Master"我需要從某個bin-log的哪
個位置開始往後的日誌內容，請發給我"；
4）Slave的Sql執行緒檢測到relay-log中新增加了內容後，會馬上解析relay-log的內容成為在Master端真實執行時候的那些可執行的內容，並在自身執行。
引用地址:https://www.yangxingzhen.com/364.html

原理圖:

四. 主主備份:

1. 修改主1伺服器配置:

replicate-ignore-db = mysql              #忽略同步的資料庫
replicate-ignore-db = information_schema #忽略同步的資料庫
replicate-ignore-db = performance_schema #忽略同步的資料庫
auto_increment_offset =1                 #防止自增主鍵衝突
auto_increment_increment =2
log-slave-updates

2. 主2配置:

replicate-ignore-db = mysql              #忽略同步的資料庫
replicate-ignore-db = information_schema #忽略同步的資料庫
replicate-ignore-db = performance_schema #忽略同步的資料庫
auto_increment_offset =2                 #防止自增主鍵衝突
auto_increment_increment =2
log-slave-updates                        #將複製事件寫入binlog,一臺伺服器既做主庫又做從庫此選項須要開啟

3. 授權:

主1授權:

grant replication slave on *.*  to 'slave'@'192.168.31.%' identified by '@Abcd1234';
#重新載入許可權表
flush privileges;

主2儲存授權資訊:

mysql> change master to
    -> master_user='slave',                 #授權使用者
    -> master_password='@Abcd1234',         #密碼
    -> master_host='192.168.31.100',        #主伺服器地址
    -> master_log_file='mysql-bin.000001',  #二進位制檔名
    -> master_log_pos=601;                  #當前二進位制檔案大小
Query OK, 0 rows affected, 2 warnings (0.06 sec)

主2授權:

grant replication slave on *.*  to 'slave'@'192.168.31.%' identified by '@Abcd1234';
#重新載入許可權表
flush privileges;

主1儲存授權資訊:

mysql> change master to
    -> master_user='slave',                 #授權使用者
    -> master_password='@Abcd1234',         #密碼
    -> master_host='192.168.31.101',        #主伺服器地址
    -> master_log_file='mysql-bin.000001',  #二進位制檔名
    -> master_log_pos=601;                  #當前二進位制檔案大小
Query OK, 0 rows affected, 2 warnings (0.06 sec)

主一主二執行命令:

start slave

4. 檢視:

主1資訊:

主2資訊:

5. 測試:

五. 一主多從:

通過上面的文章,我們配置了主主配置,現在mysql01和mysql02互為主從,我們現在把一號變為主伺服器,二號變為從伺服器,

1. 停止MySQL01的執行緒

mysql01伺服器執行以下命令:

#停止I/O 執行緒和SQL執行緒的操作。
stop slave

2. 修改第三臺伺服器上的配置檔案:

 vi /etc/my.cnf

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character_set_server=utf8
init_connect='SET NAMES utf8'
log-bin=mysql-bin
#server-id一般為ip結尾
server-id=102   

3. 授權

主1授權:

#這些指令其實不用執行,因為我們之前執行過,但為了流程更加詳細,在此貼上.
grant replication slave on *.*  to 'slave'@'192.168.31.%' identified by '@Abcd1234';
#重新載入許可權表
flush privileges;

檢視mysql01日誌檔案:

mysql> mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

4. mysql03接收授權:

#如果下面的分句sql語句覺得麻煩,複製下面一行即可:

change master to master_user='slave',master_password='@Abcd1234', master_host='192.168.31.100',master_log_file='mysql-bin.000006', master_log_pos=454;

#上面的命令等價於上面的命令
mysql> change master to
    -> master_user='slave',
    -> master_password='@Abcd1234',
    -> master_host='192.168.31.100',
    -> master_log_file='mysql-bin.000006',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

5. 檢視授權:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.100
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 

6. 測試:

六. 多主一從,也稱為多源複製

1. 修改從庫配置:

server-id = 102         #必須唯一
log_bin = mysql-bin

binlog-ignore-db = mysql        #不需要同步的資料庫
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys

#不加此引數會報To have multiple channels, repository cannot be of type FILE; Please check the repository configuration and convert them to TABLE.錯誤
master_info_repository  =table
relay_log_info_repository =table

2. 查詢MySQL01二進位制檔案資訊:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |      941 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

3. 查詢MySQL02二進位制檔案資訊:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |      941 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

從伺服器執行下下面命令:

change master to master_user='slave', master_password='@Abcd1234', master_host='192.168.31.100', master_log_file='mysql-bin.000008', master_log_pos=941 for channel'100'

change master to master_user='slave', master_password='@Abcd1234', master_host='192.168.31.101', master_log_file='mysql-bin.000008', master_log_pos=941 for channel'101';

#定義通道名稱
for channel '100';

4. 檢視授權資訊:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.31.100
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 941
               Relay_Log_File: localhost-relay-bin-100.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 941
              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: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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: 100
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.31.101
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 941
               Relay_Log_File: localhost-relay-bin-101.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 941
              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: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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: 101
           Master_TLS_Version: 

5. 測試: