Mysql双主架构

2018-07-02

Mysql扩展知识

Mysql双主架构

MySQL主从方案中，由于主上存在单点故障的关系，为了增加稳定性，可以使用双主的架构，即两台主机器，不但可以增加MySQL的人口，还可以使用高可用。当两台机器做双主时候，他们即使主，又是另一台机器的从。

准备工作

这里以192.168.80.104为主1 192.168.80.105为主2 两台机器都安装好同样版本的MySQL 确保两台机器是的数据一样的

配置配置文件

主1

1.修改MySQL配置文件my.cnf

1	[root@localhost ~]# vim /etc/my.cnf

添加下面参数

1
2
3

server-id=1
log_bin=mysql-bin
auto-increment-offset=1

auto-increment-offset设置自动增长的字段的偏移量

主2

[root@localhost ~]# vim /etc/my.cnf

server-id=2
log_bin=mysql-bin
auto-increment-offset=2

2.重启两台机器的MySQL的服务

1	[root@localhost ~]# service mysqld restart

3.创建各自主从的通讯的用户并由于权限

主1

1 2	mysql> grant replication slave on . to'repl1'@192.168.80.105 identified by '38003800'; mysql> flush privileges;

主2

1 2	mysql> grant replication slave on . to 'repl2'@192.168.80.104 identified by '38003800'; mysql> flush privileges;

4.查看各自主机的二进制日志文件及事件位置

主1

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      411 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

主2

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      421 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.各自为服务器各自指定另一台服务器作为自己的主服务器

主1

1
2
3

mysql> change master to master_host='192.168.80.105',master_user='repl2',master_password='38003800',master_log_fille='mysql-bin.000001',master_log_pos=421;

mysql> start slave;

主2

1
2
3

mysql> change master to master_host='192.168.80.104',master_user='repl1',master_password='38003800',master_log_file='mysql-bin.000001',master_log_pos=411;

mysql> start slave;

6.查看是否成功

主1

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.80.105
                  Master_User: repl2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 421
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

主2

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.80.104
                  Master_User: repl1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 411
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试

在主1上

1 2	mysql> create database kkk; Query OK, 1 row affected (0.01 sec)

在主2上

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kkk                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> create database kkk2;

再回去主1上

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kkk                |
| kkk2               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.01 sec)

参考文章 http://blog.51cto.com/9025736/2061907 http://www.cnblogs.com/ygqygq2/p/6045279.html