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

宇文氏

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>　　
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_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_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(
　　
->>　　
-> 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;　　
+----+-----------+

　　
|>　　
+----+-----------+
　　
|  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>　　

　　（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;　　
+----+-----------+

　　
|>　　
+----+-----------+
　　
|  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。