MariaDB主从复制（三）--主主复制
实验环境：
主节点：192.168.147.133
主节点：192.168.147.134
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 开始点
auto_increment_increment=2 增长幅度
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
主主复制
主主复制的配置步骤：
(1) 各节点使用一个惟一server_id
(2) 都启动binary log和relay log
(3) 创建拥有复制权限的用户账号
(4) 定义自动增长id字段的数值范围各为奇偶
(5) 均把对方指定为主节点，并启动复制线程
具体步骤
主节点133
修改配置文件
[root@centos7 mysql]# cat /etc/my.cnf
[mysqld]
server_id=2 ##使用一个惟一server_id
log_bin ##启动二进制日志，relay log默认会启动
log_slave_updates
#slave_skip_errors = 1062
innodb_file_per_table=ON
auto_increment_offset=1 ##开始点，奇数
auto_increment_increment=2 ##增长幅度，如果是三个主主则为3主节点134
修改配置文件
[root@centos7 mysql]# cat /etc/my.cnf
[mysqld]
server_id=1 ##使用一个惟一server_id
auto_increment_offset=2 ##开始点，偶数
auto_increment_increment=2
datadir=/data/lvm/mysql
innodb_file_per_table=ON
log_bin=/data/lvm/mysql_binlog/bin_log ##启动二进制日志，relay log默认会启动两个节点同时创建拥有复制权限的用户账号
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.239.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)在133节点上把134节点指定为主节点，并启动复制线程
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.147.134', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='bin_log.000001', MASTER_LOG_POS=245, MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.147.134
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: bin_log.000006
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000007
Relay_Log_Pos: 527
Relay_Master_Log_File: bin_log.000006
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: 245
Relay_Log_Space: 2661
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)在134节点上把133节点指定为主节点，并启动复制线程。
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.147.133', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245, MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.10 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.147.133
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mariadb-bin.000003
Read_Master_Log_Pos: 1591615
Relay_Log_File: mariadb-relay-bin.000004
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000003
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: 1591615
Relay_Log_Space: 1113
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)验证
在133节点上插入数据，ID为奇数
MariaDB [hellodb_innodb]> insert students (name,age,gender,classid,teacherid) values ("Liu Dehua",30,'M',1,3);
Query OK, 1 row affected (0.00 sec)在134节点上查询，发现ID是自增长的，且增幅为2，说明配置成功。
MariaDB [(none)]> select * from hellodb_innodb.students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | Wei Xiaobao | 26 | | 4 | 4 |
| 27 | Wei wei | 23 | M | 3 | 2 |
| 28 | Chen Wenjian | 23 | M | 3 | 2 |
| 29 | Li He | 23 | M | 3 | 2 |
| 31 | Liu Dehua | 30 | M | 1 | 3 |
+-------+---------------+-----+--------+---------+-----------+在134节点上插入数据，ID为偶数
MariaDB [(none)]> insert hellodb_innodb.students (name,age,gender,classid,teacherid) values ("Liu Bei",50,'M',2,3);
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> insert hellodb_innodb.students (name,age,gender,classid,teacherid) values ("Zhang Fei",40,'M',2,3);
Query OK, 1 row affected (0.01 sec)在133节点上查询，发现ID是自增长的，且增幅为2，说明配置成功。
MariaDB [(none)]> select * from hellodb_innodb.students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | Wei Xiaobao | 26 | | 4 | 4 |
| 27 | Wei wei | 23 | M | 3 | 2 |
| 28 | Chen Wenjian | 23 | M | 3 | 2 |
| 29 | Li He | 23 | M | 3 | 2 |
| 31 | Liu Dehua | 30 | M | 1 | 3 |
| 32 | Liu Bei | 50 | M | 2 | 3 |
| 34 | Zhang Fei | 40 | M | 2 | 3 |
+-------+---------------+-----+--------+---------+-----------+手机扫一扫
移动阅读更方便
你可能感兴趣的文章