Mysql DBA 高级运维学习之路-Mysql双主及多主同步过程

r囧r小猫 2021-09-20 05:56 203阅读 0赞

# 1.Mysql双主及多主同步实战，互为主从 #

使用主主前提：

a.表的主键自增(M库id1,3,5；M库id 2,4,6)

准备：两台机器，这里用多实例来讲解

第一台：

Ip:192.168.1.115  
Port:3306

第二台：

Ip:192.168.1.115  
Port:3307

## 1.1 第一台机器的操作 ##

（1）配置3306的my.cnf配置文件添加打开下面参数

[root@mysql ~]# egrep "\[mysqld]|auto_increment|log-bin|log-slave" /data/3306/my.cnf 
    [mysqld]
    auto_increment_increment= 2 自增的间隔如1 3 5 间隔为2
    auto_increment_offset   = 1 ID的初始位置
    log-bin = /data/3306/mysql-bin
    log-slave-updates

（2）重启3306mysql数据库服务

[root@mysql ~]# /data/3306/mysql stop
    Stoping MySQL....
    [root@mysql ~]# /data/3306/mysql start
    Starting MySQL......

（3）配置同步参数

CHANGE MASTER TO
    MASTER_HOST='192.168.1.115', 
    MASTER_PORT=3307,
    MASTER_USER='rep',
    MASTER_PASSWORD='123456';

提示：如果之前是主从同步想给改成双主同步，我们要带—master-data参数备份之前主库的数据然后导入到从库。

例如，备份3306更新的数据

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --master-data=1 --events >/opt/3306bak.sql

用—master-data 参数备份数据，在change master的时候就不用添加下面的参数了以及不用show master status;查看主库的状态查看binlog的位置了。

MASTER_LOG_FILE='mysql-bin.000004' 
    MASTER_LOG_POS=1895

（4）启动从库同步开关并查看同步状态

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: 192.168.1.115
      Master_User: rep
      Master_Port: 3307
    Connect_Retry: 60
      Master_Log_File: mysql-bin.000004
      Read_Master_Log_Pos: 1895
       Relay_Log_File: relay-bin.000012
    Relay_Log_Pos: 1019
    Relay_Master_Log_File: mysql-bin.000004
     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: 1895
      Relay_Log_Space: 1315
      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)

## 1.2 第二台机器操作 ##

第二台操作和第一台操作差不多

（1）配置3307的my.cnf配置文件添加打开下面参数

[root@mysql ~]# egrep "\[mysqld]|auto_increment|log-bin|log-slave" /data/3307/my.cnf 
    [mysqld]
    auto_increment_increment= 2
    auto_increment_offset   = 2
    log-bin = /data/3307/mysql-bin
    log-slave-updates

（2）重启3307mysql数据库服务

[root@mysql ~]# /data/3307/mysql stop
    Stoping MySQL....
    [root@mysql ~]# /data/3307/mysql start
    Starting MySQL......

（3）配置同步参数

CHANGE MASTER TO
    MASTER_HOST='192.168.1.115', 
    MASTER_PORT=3306,
    MASTER_USER='rep',
    MASTER_PASSWORD='123456';

（4）启动从库同步开关并查看同步状态

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: 192.168.1.115
      Master_User: rep
      Master_Port: 3306
    Connect_Retry: 60
      Master_Log_File: mysql-bin.000015
      Read_Master_Log_Pos: 1895
       Relay_Log_File: relay-bin.000042
    Relay_Log_Pos: 1326
    Relay_Master_Log_File: mysql-bin.000015
     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: 1895
      Relay_Log_Space: 1622
      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: 1
    1 row in set (0.00 sec)

## 1.3 测试mysql数据库主主（M-M）同步互为主从。 ##

（1）3306主机数据库操作

a.现在在linzhongniao库里创建student表

mysql> create table student(
    -> id int(4) not null AUTO_INCREMENT,
    -> name char(20) not null,
    -> primary key(id)
    -> );
    Query OK, 0 rows affected (0.01 sec)

b.在student表中插入三条数据

mysql> insert into student(name) values('nishishei');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into student(name) values('zhangsan');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into student(name) values('lisi');
    Query OK, 1 row affected (0.00 sec)

c.查看一下插入的数据

mysql> select * from student;
    +----+-----------+
    | id | name  |
    +----+-----------+
    |  1 | nishishei |
    |  3 | zhangsan  |
    |  5 | lisi  |
    +----+-----------+
    1   rows in set (0.00 sec)

我们发现数据的ID的自增不是连续的，因为我们在3306主机的my.cnf设置了下面的参数，这两个参数的意思是我ID字段自增的开始位置为1以一次间隔为2的方式自增，所以我们上面插入的数据是以2为间隔自增的，那么`auto_increment_offset`的值等于2 呢？当然`auto_increment_increment`参数的值也是可以设置的。

auto_increment_increment= 2 自增的间隔如1 3 5 间隔为2
    auto_increment_offset   = 1 ID的初始位置

（2）接下来我们在3307主机的数据库上也插入三条数据

mysql> use linzhongniao;
    Database changed
    mysql> insert into student(name) values('burenshi');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into student(name) values('liushishi');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into student(name) values('luhan');
    Query OK, 1 row affected (0.00 sec)

（3）查看一下插入的数据

mysql> select * from student;
    +----+-----------+
    | id | name  |
    +----+-----------+
    |  1 | nishishei |
    |  3 | zhangsan  |
    |  5 | lisi  |
    |  6 | burenshi  |
    |  8 | liushishi |
    | 10 | luhan |
    +----+-----------+

我们看新插入的数据以6、8、10的形式自增，因为我们在3307主机的my.cnf配置文件中设置的`auto_increment_increment`参数的值等于2和`auto_increment_offset`的值等于2，设置这两个参数的意思是以2为起始位置，2为间隔自增的。所以插入数据的id为6、8、10。

转载于:https://blog.51cto.com/10642812/2070869