MySQL架構備份之雙機熱備——MySql 主從復制、主主復制-有解無憂

M–S架構：實作雙機熱備（AB復制）

1、可以降低master讀壓力

2、可以對資料庫做“熱備”，熱備只能解決硬體master硬體故障，軟體故障等重大故障問題，但無法解決人為誤操作導致的邏輯故障（列如輸入錯誤的SQL陳述句把重要的記錄洗掉了），所以常規的備份是必須，

環境準備及要求：

1、關閉防火墻和selinux

2、hosts檔案中兩臺服務器主機名和ip地址一一對應起來

3、系統時間需要同步

4、master和slave的資料庫版本保持一致（系統版本保持一致）

思路：

1、master必須開啟二進制日志

2、slave必須開啟中繼日志

3、master和slave的server-id必須不一樣范圍（2^23-1）

4、master和slave的初始資料一致

M–S架構操作步驟#

環境：

master：10.1.118.38

slave：10.1.118.2

1. 修改組態檔

# master：
root@master ~]# vim /etc/my.cnf
log-bin=mysql-bin
server-id = 1

# slave：
[root@slave ~]# vim /etc/my.cnf
server-id = 2
relay-log=/data/DB/relay.log

如果relay-log=/data/DB/relay.log 路徑沒有則新建
配置完成后，重啟mysql

2. 在master上面創建授權用戶及查看binlog資訊

master：
1>鎖表，先加鎖，防止兩邊資料不一致;如果業務還未上線，這個就沒有必要了
mysql> flush tables with read lock;

創建用戶
每個從庫使用MySQL用戶名和密碼連接到主庫，因此主庫上必須有用戶帳戶，從庫可以連接，任何帳戶都可以用于此操作，只要它已被授予 REPLICATION SLAVE權限，可以選擇為每個從庫創建不同的帳戶，或者每個從庫使用相同帳戶連接到主庫

雖然不必專門為復制創建帳戶，但應注意，復制用到的用戶名和密碼會以純文本格式存盤在主資訊存盤庫檔案或表中，因此，需要創建一個單獨的帳戶，該帳戶只具有復制程序的權限，以盡可能減少對其他帳戶的危害，

主庫上創建主從復制賬號
創建用戶【MySQL8.0.11和之前不一樣，語法有區別】

必須先創建用戶,授予權限
密碼規則：mysql8.0以上密碼策略限制必須要大小寫加數字特殊符號）:
再進行賦值：

mysql> 
mysql> create user 'slave'@'%' identified by '1qaz@WSX';
Query OK, 0 rows affected (0.00 sec)
mysql> 
mysql> grant all privileges on slave.* to 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> 
mysql> grant replication slave on *.*  to 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql>  show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1508
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
# 注意：此時千萬不要往里面寫東西
ERROR: 
No query specified

mysql>

按道理來說，應該如下圖這樣配置，指定相應的slave的IP ，但是會報錯

mysql> grant replication slave on *.*  to 'slave'@'10.1.118.2';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql>

3. 在slave上配置同步資訊

從庫中不創建slave資料庫

mysql>
mysql> change master to master_host='10.1.118.38', master_user='slave', master_password='1qaz@WSX', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=1508;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql>
引數說明：
    master_host：        master ip
    master_user：        同步用戶
    master_password：    密碼
    master_port：　　　　 埠
    master_log_file：    master上面查看到的二進制日志名
    master_log_pos：     master上面查看到的POS值

# 啟動從服務器復制執行緒
mysql> 
mysql> start slave;
Query OK, 0 rows affected (0.12 sec)

mysql> 
mysql> 
mysql> show slave status \G;
查看slave發現報錯
解決：
    1.檢查組態檔里server-id是否不一致
    2.檢查資料目錄里的auto.cnf檔案的uuid是否一樣，如果一樣將slave中的auto.cnf洗掉后將master和slave的服務重啟，

mysql> show slave status \G;
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

檢查主從復制通信狀態

Slave_IO_State #從站的當前狀態
Slave_IO_Running： Yes #讀取主程式二進制日志的I/O執行緒是否正在運行
Slave_SQL_Running： Yes #執行讀取主服務器中二進制日志事件的SQL執行緒是否正在運行，與I/O執行緒一樣
Seconds_Behind_Master #是否為0，0就是已經同步了

必須都是yes

如果不是原因主要有以下 4 個方面：

1、網路不通
2、密碼不對
3、MASTER_LOG_POS 不對 ps
4、mysql 的 auto.cnf server-uuid
一樣（可能你是復制的mysql）
》》》》》》》》》》》》》》》》
$ find / -name ‘auto.cnf’
$ cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=6b831bf3-8ae7-11e7-a178-000c29cb5cbc # 按照這個16進制格式，修改server-uuid，使主備不一樣即可，重啟mysql即可
》》》》》》》》》》》》》》》》

如果不是Yes，重啟后再start slave;，然后查看狀態show slave status \G;

4. 測驗驗證

測驗一：
    先查看master和slave的ceshi庫中users表的資訊
    master：
    mysql> 
	mysql> select * from zwj.sync_test;
	+----+--------------+
	| id | name         |
	+----+--------------+
	|  1 | zhangweijian |
	+----+--------------+
	1 row in set (0.00 sec)
	mysql> 

slave：
	mysql> select * from zwj.sync_test;
	+----+--------------+
	| id | name         |
	+----+--------------+
	|  1 | zhangweijian |
	+----+--------------+
	1 row in set (0.00 sec)
	
	mysql> 

 在master上面增加資料，然后回到slave上面查看
 master：
	mysql> insert into sync_test (id, name) values(2, 'libai');
	Query OK, 1 row affected (0.00 sec)
	
	mysql> 
	mysql> select * from zwj.sync_test;
	+----+--------------+
	| id | name         |
	+----+--------------+
	|  1 | zhangweijian |
	|  2 | libai        |
	+----+--------------+
	2 rows in set (0.00 sec)
	
	mysql> 
	
slave:
	mysql> 
	mysql> select * from zwj.sync_test;
	+----+--------------+
	| id | name         |
	+----+--------------+
	|  1 | zhangweijian |
	|  2 | libai        |
	+----+--------------+
	2 rows in set (0.00 sec)
	
	mysql>

測驗二：

    停止slave上的服務，  停掉服務后，那幾個變數變為No
	mysql> stop slave;
	Query OK, 0 rows affected (0.01 sec)
	
	mysql>
    
    在master上面插入資料并查看
	mysql> 
	mysql> insert into sync_test (id, name) values(3, 'caixukun');
	Query OK, 1 row affected (0.00 sec)
	
	mysql> 
	mysql> select * from zwj.sync_test;
	+----+--------------+
	| id | name         |
	+----+--------------+
	|  1 | zhangweijian |
	|  2 | libai        |
	|  3 | caixukun     |
	+----+--------------+
	3 rows in set (0.00 sec)
	
	mysql> 
	
查看備資料庫中是否有更新得資料（應該沒有），再次啟動slave并查看是否有更新
	mysql> 
	mysql> select * from zwj.sync_test;
	+----+--------------+
	| id | name         |
	+----+--------------+
	|  1 | zhangweijian |
	|  2 | libai        |
	+----+--------------+
	2 rows in set (0.00 sec)
	
	mysql> 
	mysql> start slave;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> 
	mysql> select * from zwj.sync_test;
	+----+--------------+
	| id | name         |
	+----+--------------+
	|  1 | zhangweijian |
	|  2 | libai        |
	|  3 | caixukun     |
	+----+--------------+
	3 rows in set (0.00 sec)
	
	mysql>

1、測驗表明只能在master端上面進行寫然后再同步到slave端，
2、如果slave服務關閉后可以不用管理，然后再在master端可以繼續寫入，等到slave服務啟動后，會自動同步到slave端，

主主復制

主主復制在以上主從復制基礎上進行，

在主主復制結構中，兩臺服務器的任何一臺上面的資料庫存發生了改變都會同步到另一臺服務器上，這樣兩臺服務器互為主從，并且都能向外提供服務，配置的時候需要注意：主鍵重復，server-id不能重復，

相對于主：10.1.118.2
從： 10.1.118.38 之前相反

----10.1.118.38

   bind-address = 0.0.0.0 
   server-id=11   #任意自然數n，只要保證兩臺MySQL主機不重復就可以了，

   log-bin=mysql-bin   #開啟二進制日志

   auto_increment_increment=2   #步進值auto_imcrement，一般有n臺主MySQL就填n

   auto_increment_offset=1   #起始值，一般填第n臺主MySQL，此時為第一臺主MySQL

   binlog-ignore=mysql   #忽略mysql庫【我一般都不寫】

   binlog-ignore=information_schema   #忽略information_schema庫【我一般都不寫】

   replicate-do-db=aa   #要同步的資料庫，默認所有庫

實體：
在這里插入圖片描述

----10.1.118.2
實體：
在這里插入圖片描述

二者只有server-id不同和 auto-increment- offset不同
auto-increment-offset是用來設定資料庫中自動增長的起點的，兩個服務器都設定了一次自動增長值2，所以它們的起點必須得不同，這樣才能避免兩臺服務器資料同步時出現主鍵沖突
replicate-do-db 指定同步的資料庫，我們只在兩臺服務器間同步test資料庫，auto-increment-increment的值應設為整個結構中服務器的總數，本例用到兩臺服務器，所以值設為2

重啟mysql 服務

1. 在10.1.118.2中創建一個10.1.118.38主機中可以登錄的MySQL用戶

mysql> 
mysql> create user 'master1'@'%' identified by '1qaz@WSX';
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> 
mysql> grant replication slave on *.*  to 'master1'@'%';
Query OK, 0 rows affected (0.08 sec)
mysql> 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> show grants for 'master1'@'%';
+-------------------------------------------------+
| Grants for master1@%                            |
+-------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `master1`@`%` |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 2953
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

2. 在10.1.118.38 中進行配置同步資訊

mysql> 
mysql> select User from mysql.user;
+------------------+
| User             |
+------------------+
| eb_vnfm          |
| root1            |
| slave            |
| eb_vnfm          |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
+------------------+
7 rows in set (0.00 sec)
 
mysql> 
mysql> change master to master_host='10.1.118.2', master_user='master1', master_password='1qaz@WSX', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=2953;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

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

mysql> 
mysql> show slave status \G;
查看之前那幾個引數 是否為yes
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes

如圖：
在這里插入圖片描述
3. 在10.1.118.2上開啟start slave，檢測主從復制是否成功

在這里插入圖片描述
報1602 經典錯誤，誤把從節點當成主節點插入一條資料，同一條資料在主、從節點插入都進行了一次插入操作，導致主鍵沖突，slave下的SQL執行緒例外，

解決方法：

set global sql_slave_skip_counter=1;

在這里插入圖片描述
配置正常

4. 測驗

在10.1.118.38上

在10.1.118.38上
	mysql> 
	mysql>  select * from zwj.sync_test;
	+----+--------------+
	| id | name         |
	+----+--------------+
	|  1 | zhangweijian |
	|  2 | libai        |
	|  3 | caixukun     |
	|  4 | liziqi       |
	+----+--------------+
	4 rows in set (0.00 sec)
	
	mysql> 
	mysql> insert into sync_test (id, name) values(5, 'mabaoguo');
	Query OK, 1 row affected (0.00 sec)

在10.1.118.2 上

在10.1.118.2 上	
	mysql> 
	mysql>  select * from zwj.sync_test;
	+----+--------------+
	| id | name         |
	+----+--------------+
	|  1 | zhangweijian |
	|  2 | libai        |
	|  3 | caixukun     |
	|  4 | liziqi       |
	|  5 | mabaoguo     |
	+----+--------------+
	5 rows in set (0.00 sec)
	
	mysql> 
	mysql> 
	mysql> insert into sync_test (id, name) values(6, 'wangye');
	Query OK, 1 row affected (0.00 sec)

	mysql>

然后在 38 上查看

在10.1.118.38上
	mysql> 
	mysql>  select * from zwj.sync_test;
	+----+--------------+
	| id | name         |
	+----+--------------+
	|  1 | zhangweijian |
	|  2 | libai        |
	|  3 | caixukun     |
	|  4 | liziqi       |
	|  5 | mabaoguo     |
	|  6 | wangye       |
	+----+--------------+
	6 rows in set (0.00 sec)
	
	mysql>

如果有興趣，可以接著測驗，在10.1.118.2 上，停掉slave，變成主（38）從（2）
在這里插入圖片描述
在10.118.38上，插入一條資料

在10.1.118.2 上查看，并沒有復制同步過去

同理，緊接著在 2 上，插入一條資料

在 38 上可以看到同步過去了

現在打開10.1.118.2 上的slave，

可以看到，之前在38 上添加的資料，打開slave后，同步過來了
在這里插入圖片描述
6. 注意事項

1、主主復制組態檔中auto_increment_increment和auto_increment_offset只能保證主鍵不重復，卻不能保證主鍵有序，

 2、當配置完成Slave_IO_Running、Slave_SQL_Running不全為YES時，show slave status\G資訊中有錯誤提示，可根據錯誤提示進行更正，

 3、Slave_IO_Running、Slave_SQL_Running不全為YES時，大多數問題都是資料不統一導致，

 常見出錯點：

 1、兩臺資料庫都存在db資料庫，而第一臺MySQL db中有tab1，第二臺MySQL db中沒有tab1，那肯定不能成功，

 2、已經獲取了資料的二進制日志名和位置，又進行了資料操作，導致POS發生變更，在配置CHANGE MASTER時還是用到之前的POS，

 3、stop slave后，資料變更，再start slave，出錯，

 終極更正法：重新執行一遍CHANGE MASTER就好了，

若出現如下圖所示錯誤2003，連接不通過，則說明mysqld.cnf 中的bind_address未修改為0.0.0.0，如果要讓mysql監聽到其他的地址，可以將bind-address = 127.0.0.1注釋掉,或者將bind-address = 0.0.0.0監聽所有的地址
在這里插入圖片描述

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/233120.html

標籤：其他

上一篇：SpringBoot整合云服務器下的FastDFS實作檔案上傳

下一篇：教小師妹快速入門Mybatis，看這篇就夠了