MySQL分布式集群-4.主主复制

1.双向主从模式配置

• mysql-slave

vim /etc/my.cnf

server-id=2
log-bin=/var/lib/mysql/mysql-bin
-----------------------------------------------------------------

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

mysql> stop slave;
1 row in set (0.00 sec)

• mysql-master

mysql> change master to master_host='192.168.1.111',master_port=3306,master_user='repl',master_password='repl',master_log_file='mysql-bin.000001',master_log_pos=247; 
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> start slave;
Query OK, 0 rows affected (0.05 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.111
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 247
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB:
          ...

2.master, slave数据库主键自增设置

• mysql-master

vim /etc/my.cnf

auto_increment_increment=2
auto_increment_offset=1
------------------------------------------------------
[root@master ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

mysql> create table topic (id int not null auto_increment primary key);
Query OK, 0 rows affected (0.19 sec)

mysql> show create table topic\G
*************************** 1. row ***************************
       Table: topic
Create Table: CREATE TABLE `topic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into topic values(null);
Query OK, 1 row affected (0.04 sec)

mysql> select * from topic;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> insert into topic values(null);
Query OK, 1 row affected (0.04 sec)

mysql> select * from topic;
+----+
| id |
+----+
|  1 |
|  3 |
+----+
2 rows in set (0.00 sec)

• mysql-slave

vim /etc/my.cnf

auto_increment_increment=2
auto_increment_offset=2
------------------------------------------------------
[root@slave~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

mysql> insert into topic values(null);
Query OK, 1 row affected (0.04 sec)

mysql> select * from topic;
+----+
| id |
+----+
|  1 |
|  3 |
|  4 |
+----+
3 rows in set (0.00 sec)