MySQL雙主互為主從配置

在使用雙主互為主從的模式前提是互為主從的兩個資料庫，表的主鍵必須是自增的。

環境不多說了，多台mysql單執行個體或單台多執行個體都可以。多執行個體的配置請參考：https://www.cnblogs.com/qiuhom-1874/p/9757061.html

實驗步驟是在主從複制已經配置好的兩台資料庫上配置。說下思想吧，其實很簡單就是把原來的從庫當主庫，把原來的主庫當從庫。按照主從配置的方法從前把主庫當從庫，把從庫當主庫的配置一遍。

在使用雙主互為主從的模式前提是互為主從的兩個資料庫，表的主鍵必須是自增的。

1.配置my.cnf

主庫1配置my.cnf：

auto_increment_increment = 2
auto_increment_offset = 2
log-bin =/mysql_multi_case/3306/mysqld-bin
log-slave-updates = 1

主庫2配置my.cnf：　

auto_increment_increment = 2
auto_increment_offset = 1
log-bin =/mysql_multi_case/3307/mysqld-bin
log-slave-updates = 1

　　提示：以上參數都是配置mysqld下。

2.建立使用者，給予replication slave 權限。

grant replication slave on *.* to 'rep_3306'@'127.0.0.1' identified by 'admin';　　　　　　　　####（3307上建立）

3.導出資料（原來的從庫庫資料）

mysqldump -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -A -B --master-data=1 -x --events > /work/bak/3307all.sql

4.導入資料（導入原來的主庫）

mysql -uroot -padmin -S /mysql_muitl_case/3306/mysql.sock < /work/bak/3307all.sql

5.在主庫1上執行change master to .. 語句

change master to 
master_host='127.0.0.1',
master_port=3307
master_user='rep_3306',
master_password='admin';

　　提示：因為全備指定選項--master-data=1 是以我們在3306上執行change master to語句就不用指定master_log_file和master_log_pos的值。

mysql> change master to master_host='127.0.0.1',master_port=3307,master_user='rep_3306',master_password='admin';   
Query OK, 0 rows affected (0.53 sec)

　提示：我們可以去3306下的data目錄下檢視master-info檔案

[root@qiuhom data]# cat /mysql_multi_case/3306/data/master.info 
18
mysqld-bin.000007
1240
127.0.0.1
rep_3306
admin
3307
60
0





0
1800.000

0

　　提示：我們可以看出master_log_file是mysqld-bin.000007，pos是1240，說明--master-data=1 記錄了我們binlog日志檔案名和位置點。我們在開啟同步看看slave status裡是否一緻。

6.開啟同步和檢查slave狀态　

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rep_3306
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000007
          Read_Master_Log_Pos: 1240
               Relay_Log_File: relay-bin.000008
                Relay_Log_Pos: 1387
        Relay_Master_Log_File: mysqld-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           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: 1240
              Relay_Log_Space: 1837
              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: 2
1 row in set (0.00 sec)

　　接下來我們再來看看兩個庫的線程情況

[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "show processlist;"
+----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host            | db   | Command     | Time | State                                                                       | Info             |
+----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+
|  1 | rep         | 127.0.0.1:49248 | NULL | Binlog Dump | 1148 | Master has sent all binlog to slave; waiting for binlog to be updated       | NULL             |
|  5 | system user |                 | NULL | Connect     |  516 | Waiting for master to send event                                            | NULL             |
|  6 | system user |                 | NULL | Connect     | 1029 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  8 | root        | localhost       | NULL | Query       |    0 | NULL                                                                        | show processlist |
+----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+
[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -e "show processlist;" 
+----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host            | db   | Command     | Time | State                                                                       | Info             |
+----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+
|  1 | system user |                 | NULL | Connect     | 1156 | Waiting for master to send event                                            | NULL             |
|  2 | system user |                 | NULL | Connect     |  702 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  5 | rep_3306    | 127.0.0.1:43773 | NULL | Binlog Dump |  524 | Master has sent all binlog to slave; waiting for binlog to be updated       | NULL             |
|  7 | root        | localhost       | NULL | Query       |    0 | NULL                                                                        | show processlist |
+----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+

　　提示：可以看到3306和3307分别有3個線程，一個主io,一個從io,一個sql線程。和我們配置主從的線程是不是不一樣了。這樣兩個互為主從的執行個體就配置好了。接下來我們測試下寫入資料是什麼情況。

[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "create database qiuhom;use qiuhom;create table test(id int not null auto_increment primary key,name char(10) not null);"
[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "show databases;use qiuhom;show tables;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qiuhom             |
+--------------------+
+------------------+
| Tables_in_qiuhom |
+------------------+
| test             |
+------------------+
[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "use qiuhom;insert into test(name) values('aa'),('bb'),('cc');"
[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qiuhom             |
+--------------------+
[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -e "use qiuhom;show tables;"
+------------------+
| Tables_in_qiuhom |
+------------------+
| test             |
+------------------+
[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -e "use qiuhom;insert into test(name) values('dd'),('ee'),('ff');" 
[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "use qiuhom;insert into test(name) values('gg'),('hh'),('ii');"
[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "select * from qiuhom.test;"                                   
+----+------+
| id | name |
+----+------+
|  2 | aa   |
|  4 | bb   |
|  6 | cc   |
|  7 | dd   |
|  9 | ee   |
| 11 | ff   |
| 12 | gg   |
| 14 | hh   |
| 16 | ii   |
+----+------+
[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -e "select * from qiuhom.test;"                                   
+----+------+
| id | name |
+----+------+
|  2 | aa   |
|  4 | bb   |
|  6 | cc   |
|  7 | dd   |
|  9 | ee   |
| 11 | ff   |
| 12 | gg   |
| 14 | hh   |
| 16 | ii   |
+----+------+

　　提示：從上面的測試看我們發現我們倆個庫都可以插入資料，況且插入資料有個規律，主庫1插入的資料主鍵都是偶數，主庫2插入的資料的主鍵都是奇數。這就是我們配置參數auto_increment_offset 有很大的關系，我們設定3306等于2所有它會從表裡最後一個位置的主鍵開始偏移2開始計，因為我測試的表示空表是以3306開始插入的資料主鍵是從2開始的，然後兩個主鍵相隔兩個數，又計下一個主鍵。3307我們設定的=1 所3307插入的資料的主鍵就是從最後插入資料的主鍵位置偏移1開始計。是以從7開始計，它插入的資料主鍵也是相隔兩數字，是以第二次插入資料的主鍵就是9以此類推。

我們檢視3307裡面的qiuhom庫下的test表的資料和3306的一樣，這樣我們就實作了雙主互為主從，不管在哪個庫寫另外一庫就複制當從的角色。其實我們從字面意思也可以了解互為主從就是說一個資料庫又是主庫又是從庫的意思。

作者：Linux-1874

出處：https://www.cnblogs.com/qiuhom-1874/

本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，否則保留追究法律責任的權利.

MySQL雙主互為主從配置

繼續閱讀

報錯：'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 --使用者被鎖

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