歡迎您光臨本站 註冊首頁

MySQL 主從複製原理與實踐詳解

←手機掃碼閱讀     retouched @ 2020-06-02 , reply:0

本文實例講述了MySQL 主從複製原理與實踐。分享給大家供大家參考,具體如下:

簡介

MySQL 的主從複製又叫 Replication、AB 複製。至少需要兩個 MySQL 服務(可以是同一臺機器,也可以是不同機器之間進行)。

比如A服務器做主服務器,B服務器做從服務器,在A服務器上進行數據的更新,通過 binlog 日誌記錄同步到B服務器上,並重新執行同步過來的 binlog 數據,從而達到兩臺服務器數據一致。

MySQL 數據庫的主從複製方案,與使用 scp/rsync 等命令進行的文件級別複製類似,都是數據的遠程傳輸。

只不過 MySQL 的主從複製是其自帶的功能,無需藉助第三方工具,而且MySQL的主從複製並不是數據庫磁盤上的文件直接拷貝,而是通過邏輯的 binlog 日誌複製到要同步的服務器本地,然後由本地的線程讀取日誌裡面的 SQL 語句,重新應用到 MySQL 數據庫中。

作用

1.  可以實時災備,用於故障切換;

2.  讀寫分離,提供查詢服務,實現負載均衡;

3.  數據熱備,避免影響業務。

原理

1、主服務器MySQL服務將所有的寫操作記錄在 binlog 日誌中,並生成 log dump 線程,將 binlog 日誌傳給從服務器MySQL服務的 I/O 線程。

2、從服務器MySQL服務生成兩個線程,一個是 I/O 線程,另一個是 SQL 線程。

3、從庫 I/O 線程去請求主庫的 binlog 日誌,並將 binlog 日誌中的文件寫入 relaylog(中繼日誌)中。

4、從庫的 SQL 線程會讀取 relaylog 中的內容,並解析成具體的操作,來實現主從的操作一致,達到最終兩個數據庫數據一致的目的。

 注意點: - 主從複製是異步的邏輯的 SQL 語句級的複製; - 複製時,主庫有一個 I/O 線程,從庫有兩個線程,及 I/O 和 SQL 線程; - 實現主從複製的必要條件是主庫要開啟記錄 binlog 的功能; - 作為複製的所有 MySQL 節點的 server-id 都不能相同; - binlog 文件只記錄對數據內容有更改的 SQL 語句,不記錄任何查詢語句。


形式

一主一從

主主複製

一主多從

多主一從(5.7後開始支持)

聯級複製

實踐

需求

實現一主一從複製模式,同一臺主機兩個MySQL實例

環境

 Mac:10.15.1 Docker:2.0.0.3//採取docker安裝MySQL主要是管理維護方便、獨立IP、啟動秒級 MySQL-master:5.7.29//主服務器 MySQL-master IP:172.17.0.3 MySQL-slave:5.7.29//從服務器 MySQL-slave IP:172.17.0.4


步驟

第一步:準備好兩臺MySQL服務器
mysql-master(主服務器):
 1. 創建目錄結構: master/conf、master/data、master/logs 2. 啟動運行實例容器: docker run --name mysql-master > -p 3310:3306 > -v ~/docker/master/conf:/etc/mysql/conf.d > -v ~/docker/master/data:/var/lib/mysql > -v ~/docker/master/logs:/var/log/mysql > -e MYSQL_ROOT_PASSWORD=123456 > -d mysql:5.7 3. 進入容器 docker exec -it mysql-master bash 4. 登錄MySQL mysql -uroot -p


mysql-slave(從服務器):
 1. 創建目錄結構: slave/conf、slave/data、slave/logs 2. 啟動運行實例容器: docker run --name mysql-slave > -p 3310:3306 > -v ~/docker/slave/conf:/etc/mysql/conf.d > -v ~/docker/slave/data:/var/lib/mysql > -v ~/docker/slave/logs:/var/log/mysql > -e MYSQL_ROOT_PASSWORD=123456 > -d mysql:5.7 3. 進入容器 docker exec -it mysql-slave bash 4. 登錄MySQL mysql -uroot -p


第二步:配置文件(my.cnf)修改
 主服務器: [mysqld] port = 3306 server-id = 1 #要同步的數據庫 binlog-do-db = school #要生成二進制日誌文件 主服務器一定要開啟 log-bin = mysql-bin 重啟MySQL: docker restart mysql-master


 從服務器: [mysqld] port = 3306 server-id = 2 #要同步的數據庫 binlog-do-db = school #要生成二進制日誌文件(從服務器可選) log-bin = mysql-bin 重啟MySQL: docker restart mysql-slave


第三步:創建主服務器複製用戶及相關權限
 create user 'slave'@'%' identified by '123456';//創建用戶 grant replication slave,replication client on *.* to 'slave'@'%';//設置用戶權限 flush privileges;//刷新權限 show grants for 'slave'@'%';//查看用戶權限


第四步:數據備份同步
 1. 登錄master,執行鎖表操作 mysql -uroot -p FLUSH TABLES WITH READ LOCK; 2. 將master中需要同步的db的數據dump出來 mysqldump -uroot -p school > school.dump 3. 將數據導入slave mysql -uroot -h172.17.0.4 -p school<school.dump 4. 解鎖master UNLOCK TABLES;
第五步:主服務器複製狀態
 1. 創建新數據表及增加數據 create table user( id int(10) auto_increment, name varchar(30), primary key (id) )charset=utf8mb4; insert into user(name) values(222); 2. 主服務器 binlog 記錄狀態 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 769 | school | | | +------------------+----------+--------------+------------------+-------------------+


第六步:從服務器開始複製
 1. 連接主服務器及設置複製的起始節點 mysql> change master to master_host='172.17.0.3', -> master_port=3306, -> master_user='slave', -> master_password='123456', -> master_log_file='mysql-bin.000001', -> master_log_pos=769; 2. 開始複製 start slave; 3. 查看複製狀態 mysql> show slave status G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.17.0.3 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 961 Relay_Log_File: 87dc5224655d-relay-bin.000003 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes //表示I/O線程讀取成功 Slave_SQL_Running: Yes //表示SQL線程執行成功 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: 961 Relay_Log_Space: 892 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: 1 Master_UUID: 45540733-4e0c-11ea-b0ac-0242ac110003 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: 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: 4. 查看數據表數據 mysql> show create table userG *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | 222 | +----+------+


 複製的控制相關命令: stop salve //停止slave連路 reset slave //重置slave連路 start slave //開啟slave連路 stop master //停止master連路 reset master //重置master連路 start master //開啟master連路


第七步:主從服務器的進程查看
mysql-master:

mysql-slave:


[retouched ] MySQL 主從複製原理與實踐詳解已經有154次圍觀

http://coctec.com/docs/mysql/show-post-236630.html