Mysql集群的HA原理及配置指南之双主模式（二）

痛定思痛。 2022-07-15 06:47 95阅读 0赞

上一篇文章讲了Master-Slave的主备设计及配置方案的例子，这篇就是讲master-master双主的思路以及方案。

首先原理其实很简单，就是mysql的主主策略嘛,话不多说直接上图：

![Center][]

双主模式在中小型公司的设计方案中基本不会用到，毕竟很消耗资源并且成本也不低。

Start:

1.**a** will be the master *and* a slave to the new server, so this config is a hybrid of the two configurations used to [Establish Replication][].

In order to be a replication master, it needs `log_bin` enabled, and a `server_id` that is not the default (0).

In order to be a replication slave, it also needs the `relay_log` file name to not depend on its host name.

And to prepare this server to be a master of more slaves, we'll enable `log_slave_updates`. This ensures changes from **b** will be propagated out to **a**'s slaves.

在这里我们在机器a上搭建另一个Master mysql数据库，第一步依然是打开相应的配置项，最重要的肯定是Log\_bin，因为master之间也是通过log file进行数据同步的。

a ~ $ sudoedit /etc/my.cnf

[mysqld]
    log_bin = mysql-bin
    server_id = 10
    log_slave_updates = 1
    relay_log = mysql-relay-bin
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock

再重启下数据库使其生效

a ~ $ sudo service mysqld restart
    Stopping mysqld:                                           [  OK  ]
    Starting mysqld:                                           [  OK  ]

再进入数据库查看刚才的配置是否已经成功生效：

a ~ $ mysql -u root
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.5.29-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show variables where Variable_Name IN ("server_id", "log_bin", "relay_log", "log_slave_updates");
    +-------------------+-----------------+
    | Variable_name     | Value           |
    +-------------------+-----------------+
    | log_bin           | ON              |
    | log_slave_updates | ON              |
    | relay_log         | mysql-relay-bin |
    | server_id         | 10              |
    +-------------------+-----------------+
    5 rows in set (0.00 sec)
    
    mysql>

2.Add a service account with replication privileges.

Each server will use this account to authenticate to the other.

The account itself will be copied to the new server in the backup.

第二步就是创建一个有备份权限的用户，用以接下去进行remote登录。

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%' IDENTIFIED BY 'NoseTinSuchEdge';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql>

3.备份a中数据库原来的数据：

mysql> exit
    a ~ $ mysqldump -u root --single-transaction --all-databases --master-data=1 > /tmp/master_backup.sql 
    -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

backup的文件在 **/tmp/master\_backup.sql**

4.将backup的文件scp到另一个名字叫b的master上：

a ~ $ scp /tmp/master_backup.sql b.example.com:/tmp/
 The authenticity of host 'b.example.com (10.242.58.189)' can't be established.
 RSA key fingerprint is 0f:47:42:f4:71:51:4c:a3:70:94:db:83:03:4c:d2:48.
 Are you sure you want to continue connecting (yes/no)? yes
 Warning: Permanently added 'b.example.com,10.242.58.189' (RSA) to the list of known hosts.
 anonymous@b.example.com's password: (input your password)
 master_backup.sql 100% 501KB 501.3KB/s 00:00
 a ~ $

5.接下去就是在master b上对/etc/my.cnf进行修改，原理同a：

b ~ $ sudoedit /etc/my.cnf

[mysqld]
    log_bin = mysql-bin
    server_id = 20
    log_slave_updates = 1
    relay_log = mysql-relay-bin
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock

b ~ $ sudo service mysqld restart
    Stopping mysqld:                                           [  OK  ]
    Starting mysqld:                                           [  OK  ]

6.在b的数据库中导入a的数据：

b ~ $ mysql -u root
    mysql> source /tmp/master_backup.sql
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 508 rows affected (0.02 sec)
    Records: 508  Duplicates: 0  Warnings: 0

b  picked up  a 's binary log information from the backup file. Now we need to write down the binary log information from  b  to provide to  a .  
查看二进制文件Log file

7.配置b对a进行备份映射：

mysql> CHANGE MASTER TO MASTER_HOST='a.example.com',
    MASTER_USER='replicator', 
    MASTER_PASSWORD='NoseTinSuchEdge';

1.  On **b**, MySQL shows the replication setting `MASTER_HOST` has value `a.example.com`
2.  On **b**, MySQL shows the replication setting `MASTER_USER` has value `replicator`

8.开启replication

mysql> SLAVE START;
    mysql> SHOW SLAVE STATUS\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: a.example.com
                      Master_User: replicator
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 960
                   Relay_Log_File: mysql-relay-bin.000002
                    Relay_Log_Pos: 1106
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  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: 960
                  Relay_Log_Space: 1262
                  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: 10
    1 row in set (0.00 sec)
    
    mysql>

9.配置a从b进行数据同步

a ~ $ mysql -u root
 mysql> CHANGE MASTER TO MASTER_HOST='b.example.com',
 MASTER_USER='replicator', 
 MASTER_PASSWORD='NoseTinSuchEdge', 
 MASTER_LOG_FILE='mysql-bin.000001', 
 MASTER_LOG_POS=511309;
 Query OK, 0 rows affected (0.09 sec)
 
 mysql>

这里的关键点就是log file的postion，a到b的数据同步需要知道b的log file的postion，之前上面红色已标出。

10.开启b到a的repalication:

mysql> slave start;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: b.example.com
                      Master_User: replicator
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 511561
                   Relay_Log_File: mysql-relay-bin.000002
                    Relay_Log_Pos: 253
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  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: 511561
                  Relay_Log_Space: 409
                  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: 20
    1 row in set (0.00 sec)
    
    mysql>

配置到这里就全部完成了，接下去测试下：

在a上insert数据：

mysql> INSERT INTO important.stuff SET details='Gift from A to B';
    Query OK, 1 row affected (0.03 sec)

在b上同样的表insert一条不同的数据：

mysql> INSERT INTO important.stuff SET details='Gift from B to A';
    Query OK, 1 row affected (0.03 sec)
    
    mysql>

检查a刚刚的表：

mysql> select * from important.stuff\G
    *************************** 1. row ***************************
          id: 1
     details: Gift from A to B
    happened: 2013-03-27 04:06:02
    *************************** 2. row ***************************
          id: 2
     details: inserted by grading server
    happened: 2013-03-27 04:09:26
    *************************** 3. row ***************************
          id: 3
     details: Gift from B to A
    happened: 2013-03-27 04:09:40
    3 rows in set (0.00 sec)

会发现b insert的数据也在。

其实总结一下，前面a,b的配置和master-slave的配置一样，就是在之后在a上也配置了一个slave，即对b的数据也进行同步，这样不管是往a还是b里插数据都是可以互相进行同步的，而master-slave模式中，往slave里插数据（只有root用户登进去才行）master是不会去同步的。所以呢，双主和主备在配置上的区别其实很简单，原理上理解起来也不难，有兴趣的博友可以自己装mysql玩一玩就很快可以理解了，希望本文对大家有所帮助。

[Center]: /images/20220715/4817c2e7203949309cd9bced866a7566.png
[Establish Replication]: http://mysql.wingtiplabs.com/documentation/feigirh5/establish-replication