MySQL主從架構

MySQL主從又叫做Replication、AB複制。簡單講就是A和B兩台機器做主從後，在A上寫資料，另外一台B也會跟着寫資料，兩者資料實時同步的。它基于binlog，主裝置上須開啟binlog才能進行主從。

主從過程大緻有3個步驟：

1）主裝置将更改操作記錄到binlog裡；

2）從将主裝置的binlog事件(sql語句)同步到從本機上并記錄在relaylog裡；

3）從根據relaylog裡面的sql語句按順序執行。

主裝置上有一個log dump線程，用來和從的I/O線程傳遞binlog。從裝置上有兩個線程，其中I/O線程用來同步主的binlog并生成relaylog，另外一個SQL線程用來把relaylog裡面的sql語句落地。

準備工作

1、準備兩台裝有mysql的伺服器，并啟動mysql服務。

2、配置設定角色，确定裝置主從。

配置主裝置

1、編輯配置檔案

<code>[root@plinuxos ~]</code><code># vim /etc/my.cnf</code>

<code>[mysqld]</code>

<code>server-</code><code>id</code><code>=88</code>

2、重新開機mysql

<code>[root@plinuxos ~]</code><code># /etc/init.d/mysqld restart</code>

<code>Shutting down MySQL.... SUCCESS! </code>

<code>Starting MySQL. SUCCESS!</code>

3、檢查log_bin

<code>[root@plinuxos ~]</code><code># ls /data/mysql/bin01.*</code>

<code>/data/mysql/bin01</code><code>.000001 </code><code>/data/mysql/bin01</code><code>.index</code>

4、建立資料庫

<code>[root@plinuxos mysql]</code><code># cd /data/mysql</code>

<code>[root@plinuxos mysql]</code><code># /usr/local/mysql/bin/mysql -uroot -e 'create database db01'</code>

5、增加測試資料

<code>[root@plinuxos mysql]</code><code># /usr/local/mysql/bin/mysqldump -uroot zrlog >/tmp/zrlog.sql</code>

<code>[root@plinuxos mysql]</code><code># /usr/local/mysql/bin/mysql -uroot db01 </tmp/zrlog.sql</code>

6、備份所有資料庫

<code>[root@plinuxos mysql]</code><code># /usr/local/mysql/bin/mysqldump -uroot db01 > /tmp/db01.sql</code>

<code>[root@plinuxos mysql]</code><code># /usr/local/mysql/bin/mysqldump -uroot zrlog > /tmp/zrlog.sql</code>

7、建立使用者

<code>[root@plinuxos mysql]</code><code># /usr/local/mysql/bin/mysql -uroot</code>

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

<code>Your MySQL connection </code><code>id</code> <code>is 414</code>

<code>Server version: 5.6.35-log MySQL Community Server (GPL)</code>

<code>Oracle is a registered trademark of Oracle Corporation and</code><code>/or</code> <code>its</code>

<code>affiliates. Other names may be trademarks of their respective</code>

<code>owners.</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>mysql> grant replication slave on *.* to </code><code>'repl'</code><code>@</code><code>'122.112.197.192'</code> <code>identified by </code><code>'123456'</code><code>;</code>

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

8、鎖表并檢視狀态

<code>mysql> flush tables with </code><code>read</code> <code>lock; </code><code>##鎖表，防止寫入</code>

<code>mysql> show master status; </code><code>##下方兩個參數需要在從裝置上配置</code>

配置從裝置

<code>[root@ecs-89c1 mysql]</code><code># vi /etc/my.cnf</code>

<code>server-</code><code>id</code><code>=192</code>

<code>[root@ecs-89c1 mysql]</code><code># /etc/init.d/mysqld restart</code>

<code>Shutting down MySQL.. SUCCESS! </code>

3、複制主裝置資料庫備份檔案

<code>[root@ecs-89c1 mysql]</code><code># scp 122.112.253.88:/tmp/*.sql /tmp/</code>

<code>The authenticity of host </code><code>'122.112.253.88 (122.112.253.88)'</code> <code>can't be established.</code>

<code>ECDSA key fingerprint is 2e:6e:90:32:87:05:9e:63:63:d6:2d:44:a5:5f:be:51.</code>

<code>Are you sure you want to </code><code>continue</code> <code>connecting (</code><code>yes</code><code>/no</code><code>)? </code><code>yes</code>

<code>Warning: Permanently added </code><code>'122.112.253.88'</code> <code>(ECDSA) to the list of known hosts.</code>

<code>[email protected]'s password: </code>

<code>zrlog.sql 100% 9878 9.7KB</code><code>/s</code> <code>00:00</code>

4、建立對應資料庫

<code>[root@ecs-89c1 mysql]</code><code># /usr/local/mysql/bin/mysql -uroot</code>

<code>Your MySQL connection </code><code>id</code> <code>is 1</code>

<code>Server version: 5.6.35 MySQL Community Server (GPL)</code>

<code>mysql> create database db01;</code>

<code>Query OK, 1 row affected (0.00 sec)</code>

<code>mysql> create database zrlog;</code>

<code>[root@ecs-89c1 mysql]</code><code># /usr/local/mysql/bin/mysql -uroot db01 </tmp/db01.sql</code>

<code>[root@ecs-89c1 mysql]</code><code># /usr/local/mysql/bin/mysql -uroot zrlog </tmp/zrlog.sql</code>

5、配置主備同步

<code>Your MySQL connection </code><code>id</code> <code>is 4</code>

<code>mysql> stop slave;</code>

<code>Query OK, 0 rows affected, 1 warning (0.00 sec)</code>

<code>mysql> change master to master_host=</code><code>'122.112.253.88'</code><code>,master_user=</code><code>'repl'</code><code>,master_password=</code><code>'123456'</code><code>,master_log_file=</code><code>'bin01.000001'</code><code>,master_log_pos=10472;</code>

<code>Query OK, 0 rows affected, 2 warnings (0.03 sec)</code>

<code>mysql> start slave;</code>

6、檢視主從狀态

<code>mysql> show slave status\G; </code><code>##如果出現錯誤，可能是雲主機政策沒放過3306端口</code>

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

<code> </code><code>Master_Host: 122.112.253.88</code>

<code> </code><code>Master_User: repl</code>

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

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

<code> </code><code>Master_Log_File: bin01.000001</code>

<code> </code><code>Read_Master_Log_Pos: 10708</code>

<code> </code><code>Relay_Log_File: ecs-89c1-relay-bin.000003</code>

<code> </code><code>Relay_Log_Pos: 515</code>

<code> </code><code>Relay_Master_Log_File: bin01.000001</code>

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

<code> </code><code>Slave_SQL_Running: Yes</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: 10708</code>

<code> </code><code>Relay_Log_Space: 691</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: 88</code>

<code> </code><code>Master_UUID: 79b66469-8cc8-11e7-ae36-fa163eb51d35</code>

<code> </code><code>Master_Info_File: </code><code>/data/mysql/master</code><code>.info</code>

<code> </code><code>SQL_Delay: 0</code>

<code> </code><code>SQL_Remaining_Delay: NULL</code>

<code> </code><code>Slave_SQL_Running_State: Slave has </code><code>read</code> <code>all relay log; waiting </code><code>for</code> <code>the slave I</code><code>/O</code> <code>thread to update it</code>

<code> </code><code>Master_Retry_Count: 86400</code>

<code> </code><code>Master_Bind: </code>

<code> </code><code>Last_IO_Error_Timestamp: </code>

<code> </code><code>Last_SQL_Error_Timestamp: </code>

<code> </code><code>Master_SSL_Crl: </code>

<code> </code><code>Master_SSL_Crlpath: </code>

<code> </code><code>Retrieved_Gtid_Set: </code>

<code> </code><code>Executed_Gtid_Set: </code>

<code> </code><code>Auto_Position: 0</code>

<code>ERROR: </code>

<code>No query specified</code>

7、主裝置解鎖

<code>Your MySQL connection </code><code>id</code> <code>is 875</code>

<code>mysql> unlock tables;</code>

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

測試主從同步

1、在主裝置上删除db01資料庫的表；

<code>mysql> show tables;</code>

<code>| Tables_in_db01 |</code>

<code>| comment |</code>

<code>| lognav |</code>

<code>| plugin |</code>

<code>mysql> drop table tag;</code>

2、在從裝置檢視對應的表也已經不存在了。

擴充學習

▎配置參數

1. 主伺服器上：

binlog-do-db= //僅同步指定的庫(其他庫不同步)

binlog-ignore-db= //忽略指定庫(其他庫都同步)

2. 從伺服器上：

replicate_do_db= //(不常用)

replicate_ignore_db= //(不常用)

replicate_do_table= //(不常用)

replicate_ignore_table= //(不常用)

replicate_wild_do_table= //如aming.%, (支援通配符%)

replicate_wild_ignore_table=

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

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++驅動概述