MySQL主從複制架構及原理

一、簡介

在實際生産中，資料的重要性不言而喻，是以考慮到資料的重要性比如單點故障導緻後端資料庫奔潰，或者後端資料庫通路壓力過大等，mysql資料庫做主從非常有必要，減輕後端資料庫壓力，主伺服器負責讀寫，從伺服器隻負責讀，這樣一來即保證了資料的可靠性，同時提高伺服器的高可用。

MySQL主從複制架構如圖：

MySQL主從複制原理：master伺服器将資料的改變記錄二進制日志，當master上的資料發生改變時，則将其改變寫入二進制日志中，salve伺服器會在一定時間間隔内對master二進制日志進行探測其是否發生改變，如果發生改變，則開始一個I/OThread請求master二進制事件，同時主節點為每個I/O線程啟動一個dump線程，用于向其發送二進制事件，并儲存至從節點本地的中繼日志中，從節點将啟動SQL線程從中繼日志中讀取二進制日志，在本地重放，使得其資料和主節點的保持一緻，最後I/OThread和SQLThread将進入睡眠狀态，等待下一次被喚醒。

二、主從複制配置實作

要求：

1、雙方mysql版本需一緻，如不一緻，隻要主節點低于從節點

2、兩節點間時間需同步

配置：

主伺服器配置如下：

1、修改/etc/my.cnf配置檔案

log-bin=/mydata/data/binlogs/master-bin

2、建立此目錄并修改屬組屬主為mysql

mkdir /mydata/binlogs/

chown -R mysql.mysql /mydata/binlogs/

3、授權使用者

grant replication slave,replication client on *.* to 'repluser'@'10.1.10.%' identified by 'pass';

flush privileges;

從伺服器配置如下：

1、修改/etc/my.cnf配置檔案，注釋二進制日志，開啟中繼日志，修改server-id和主節點不一緻

server-id=11

relay-log=/mydata/relaylogs/relay-bin

2、建立其目錄并授予此目錄的屬主、屬組為mysql

mkdir /mydata/relaylogs/

chown -R mysql.mysql /mydata/relaylogs/

3、連接配接主伺服器

change master to master_host='10.1.10.1',master_user='repluser',master_password='pass';

完成上訴配置過程即可各自啟動mysql伺服器，且在從伺服器上啟動I/O,SQL線程，例如：start slave

測試兩資料可資料是否能同步，則可在主庫上插入資料在從庫上檢視是否存在，注意：從庫隻能讀，而不能寫

100

101

102

103

104

105

106

107

108

109

<code>[root@centos6 ~]</code><code># mysql</code>

<code>Welcome to the MariaDB monitor. Commands end with ; or \g.</code>

<code>Your MariaDB connection </code><code>id</code> <code>is 13</code>

<code>Server version: 5.5.32-MariaDB MariaDB Server</code>

<code>Type </code><code>'help;'</code> <code>or </code><code>'\h'</code> <code>for</code> <code>help. Type </code><code>'\c'</code> <code>to </code><code>clear</code> <code>the current input statement.</code>

<code>MariaDB [(none)]> show slave status\G;</code>

<code> </code><code>Slave_IO_State: Waiting </code><code>for</code> <code>master to send event</code>

<code> </code><code>Master_Host: 10.1.10.1</code>

<code> </code><code>Master_User: repluser</code>

<code> </code><code>Master_Port: 3306</code>

<code> </code><code>Connect_Retry: 60</code>

<code> </code><code>Master_Log_File: master-bin.000003</code>

<code> </code><code>Read_Master_Log_Pos: 326</code>

<code> </code><code>Relay_Log_File: relay-bin.000007</code>

<code> </code><code>Relay_Log_Pos: 611</code>

<code> </code><code>Relay_Master_Log_File: master-bin.000003</code>

<code> </code><code>Slave_IO_Running: Yes </code><code>#確定I/O和SQL線程開啟，即可實作資料同步</code>

<code> </code><code>Slave_SQL_Running: Yes </code><code>#確定I/O和SQL線程開啟，即可實作資料同步</code>

<code> </code><code>Replicate_Do_DB:</code>

<code> </code><code>Replicate_Ignore_DB:</code>

<code> </code><code>Replicate_Do_Table:</code>

<code> </code><code>Replicate_Ignore_Table:</code>

<code> </code><code>Replicate_Wild_Do_Table:</code>

<code> </code><code>Replicate_Wild_Ignore_Table:</code>

<code> </code><code>Last_Errno: 0</code>

<code> </code><code>Last_Error:</code>

<code> </code><code>Skip_Counter: 0</code>

<code> </code><code>Exec_Master_Log_Pos: 326</code>

<code> </code><code>Relay_Log_Space: 1184</code>

<code> </code><code>Until_Condition: None</code>

<code> </code><code>Until_Log_File:</code>

<code> </code><code>Until_Log_Pos: 0</code>

<code> </code><code>Master_SSL_Allowed: No</code>

<code> </code><code>Master_SSL_CA_File:</code>

<code> </code><code>Master_SSL_CA_Path:</code>

<code> </code><code>Master_SSL_Cert:</code>

<code> </code><code>Master_SSL_Cipher:</code>

<code> </code><code>Master_SSL_Key:</code>

<code> </code><code>Seconds_Behind_Master: 0</code>

<code>Master_SSL_Verify_Server_Cert: No</code>

<code> </code><code>Last_IO_Errno: 0</code>

<code> </code><code>Last_IO_Error:</code>

<code> </code><code>Last_SQL_Errno: 0</code>

<code> </code><code>Last_SQL_Error:</code>

<code> </code><code>Replicate_Ignore_Server_Ids:</code>

<code> </code><code>Master_Server_Id: 1</code>

<code>ERROR: No query specified</code>

<code>MariaDB [(none)]> </code><code>#測試在主庫上删除dbs,驗證從庫是否存在dbs資料庫，結果如下：</code>

<code>MariaDB [(none)]> show databases;</code>

<code>| Database |</code>

<code>| information_schema |</code>

<code>| hellodb |</code>

<code>| mydbs |</code>

<code>| mysql |</code>

<code>| performance_schema |</code>

<code>MariaDB [(none)]></code>

<code>Your MariaDB connection </code><code>id</code> <code>is 14</code>

<code>Server version: 5.5.32-MariaDB-log MariaDB Server</code>

<code>MariaDB [(none)]> drop database dbs;</code>

<code>Query OK, 0 rows affected (0.04 sec)</code>

三、實戰：主從不同步時，如何進行資料同步至一緻

描述：當主伺服器已經運作一段時間，并且存在不小的資料時，則需把主伺服器備份，然後在從伺服器恢複，從備份時所在的位置開始複制。

1、将主伺服器上的資料做完全備份

<code>[root@centos6 ~]</code><code># mysqldump --lock-all-tables --all-databases --flush-logs --master-data=2 >/root/all.sql</code>

2、在從伺服器上導入主服務上的完全備份，在導入時關閉I/O和SQL線程

<code>Your MariaDB connection </code><code>id</code> <code>is 34</code>

<code>MariaDB [(none)]> </code><code>source</code> <code>/root/alren</code><code>.sql</code>

<code>MariaDB [testdbs]> change master to master_host=</code><code>'10.1.10.1'</code><code>,master_user=</code><code>'repluser'</code><code>,master_password=</code><code>'pass'</code><code>,MASTER_LOG_FILE=</code><code>'master-bin.000007'</code><code>,MASTER_LOG_POS=245;</code>

<code>Query OK, 0 rows affected (0.02 sec)</code>

<code>MariaDB [testdbs]> start slave;</code>

<code>MariaDB [testdbs]> show slave status\G</code>

<code> </code><code>Master_Log_File: master-bin.000007 </code><code>#已經恢複到主節點的二進制的位置</code>

<code> </code><code>Read_Master_Log_Pos: 245</code>

<code> </code><code>Relay_Log_File: relay-bin.000002</code>

<code> </code><code>Relay_Log_Pos: 530</code>

<code> </code><code>Relay_Master_Log_File: master-bin.000007</code>

<code> </code><code>Slave_IO_Running: Yes</code>

<code> </code><code>Slave_SQL_Running: Yes</code>

<code> </code><code>Exec_Master_Log_Pos: 245</code>

<code> </code><code>Relay_Log_Space: 818</code>

<code>MariaDB [testdbs]> show tables;</code>

<code>| Tables_in_testdbs |</code>

<code>MariaDB [testdbs]> </code><code>select</code> <code>* from tbl;</code>

<code>| jerry |</code>

<code>MariaDB [testdbs]> use mydbs;</code>

<code>Database changed</code>

<code>MariaDB [mydbs]> show tables; </code><code>#測試資料是否和主節點一緻</code>

<code>| Tables_in_mydbs |</code>

<code>| students |</code>

<code>MariaDB [mydbs]> </code><code>select</code> <code>* from students;</code>

<code>| 2 | jerry |</code>

<code>MariaDB [mydbs]></code>

總結：此實戰中最為關鍵主要有兩步①主伺服器上鎖表做完全備份，并滾動日志，②從伺服器上進行半道恢複.

本文轉自chengong1013 51CTO部落格，原文連結：http://blog.51cto.com/purify/1876048，如需轉載請自行聯系原作者

MySQL主從複制架構及原理

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述