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 = 2--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 |
- +----+-----------+
我们看新插入的数据以 6810 的形式自增, 因为我们在 3307 主机的 my.cnf 配置文件中设置的
auto_increment_increment
参数的值等于 2 和
auto_increment_offset
的值等于 2, 设置这两个参数的意思是以 2 为起始位置, 2 为间隔自增的所以插入数据的 id 为 6810
来源: http://www.bubuko.com/infodetail-2493986.html