主主复制

互为主从：问题

1.数据不一致,因此慎用。

2.自动增长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)均把对方指定为主节点，并启动复制线程


示例：

环境为CentOS7.2,mariadb-server-5.5.52-1.el7.x86_64

一个节点：

[root@node3 ~]# vim /etc/my.cnf

skip_name_resolve = ON

innodb_file_per_table = ON

log-bin=master-bin

server-id=1 ###server-id为1

relay_log=relay-log ###relay_log

auto_increment_offset=1 ###自增长从1开始

auto_increment_increment=2

[root@localhost ~]# systemctl start mariadb.service

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%'; ###查看日志信息

| relay_log                                 | relay-log           

| log_bin                                   | ON                    

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'replpass';

Query OK, 0 rows affected (0.00 sec)

###创建有复制权限的账户

MariaDB [(none)]> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)


另一个节点配置：

[root@node3 ~]# vim /etc/my.cnf

skip_name_resolve = ON

innodb_file_per_table = ON

log-bin=master-bin

server-id=2 ###server-id为2

relay_log=relay-log ###relay_log

auto_increment_offset=2 ###自增长从2开始

auto_increment_increment=2

[root@localhost ~]# systemctl start mariadb.service

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%'; ###查看日志信息

| relay_log                                 | relay-log           

| log_bin                                   | ON         

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'replpass';

Query OK, 0 rows affected (0.00 sec)

###创建有复制权限的账户

MariaDB [(none)]> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)


在1节点查看：

MariaDB [(none)]> SHOW MASTER STATUS;

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master-bin.000003 |      507 |              |                   ###1节点处于507位置

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.188',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=507;

Query OK, 0 rows affected (0.02 sec)

###在2节点操作指定连接日志及位置

MariaDB [(none)]> START SLAVE; ###2节点启动线程

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.0.188

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master-bin.000003

          Read_Master_Log_Pos: 507

               Relay_Log_File: relay-log.000002

                Relay_Log_Pos: 530

        Relay_Master_Log_File: master-bin.000003

             Slave_IO_Running: Yes ###IO线程启动

            Slave_SQL_Running: Yes ###SQL线程启动

              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: 507

              Relay_Log_Space: 818

              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)





在2节点查看：

MariaDB [(none)]> SHOW MASTER STATUS;

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master-bin.000003 |      507 |              |                  |

+-------------------+----------+--------------+------------------+

1 row in set (0.00 sec)


MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.150',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=507;

Query OK, 0 rows affected (0.03 sec)

###在1节点操作指定连接日志及位置

MariaDB [(none)]> START SLAVE;

Query OK, 0 rows affected (0.01 sec) ###1节点启动线程

MariaDB [(none)]> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.150

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master-bin.000003

          Read_Master_Log_Pos: 507

               Relay_Log_File: relay-log.000002

                Relay_Log_Pos: 530

        Relay_Master_Log_File: master-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: 507

              Relay_Log_Space: 818

              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节点创建一个库

MariaDB [(none)]> CREATE DATABASE mydb;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> SHOW MASTER STATUS;

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master-bin.000003 |      595 |              |                  |

###1节点查看位置595

MariaDB [(none)]> SHOW SLAVE STATUS\G

 Read_Master_Log_Pos: 595 ###2节点查看位置595

MariaDB [(none)]> SHOW DATABASES;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mydb               | ###2节点显示mydb

| mysql              |

| performance_schema |

| test               |

+--------------------+

5 rows in set (0.00 sec)


在2节点创建表

MariaDB [(none)]> use mydb;

Database changed

MariaDB [mydb]> CREATE TABLE t1 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,name CHAR(30));Query OK, 0 rows affected (0.05 sec)

MariaDB [mydb]> SHOW MASTER STATUS;

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master-bin.000003 |      658 |              |                  |

###自己二进制日志位置为658

+-------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

节点1查看：

MariaDB [(none)]> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.150

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master-bin.000003

          Read_Master_Log_Pos: 658 ###节点1读取位置也为658

。。。

MariaDB [(none)]> use mydb;

MariaDB [mydb]> SHOW TABLES; ###1节点表为t1;

+----------------+

| Tables_in_mydb |

+----------------+

| t1             |

+----------------+

1 row in set (0.00 sec)

MariaDB [mydb]> DESC t1;

+-------+------------------+------+-----+---------+----------------+

| Field | Type             | Null | Key | Default | Extra          |

+-------+------------------+------+-----+---------+----------------+

| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |

| name  | char(30)         | YES  |     | NULL    |                |

+-------+------------------+------+-----+---------+----------------+

2 rows in set (0.04 sec)

MariaDB [mydb]> INSERT INTO t1 (name) VALUES ('XiaoLongnv'),('Yang Guo'); ###1节点t1表中插入数据

MariaDB [mydb]> SELECT * FROM t1;

+----+------------+

| id | name       |

+----+------------+

|  1 | XiaoLongnv |

|  3 | Yang Guo   |

+----+------------+

2 rows in set (0.03 sec)

###在2节点查看：

MariaDB [mydb]> SELECT * FROM t1; ###2节点中显示相同的数据

+----+------------+

| id | name       |

+----+------------+

|  1 | XiaoLongnv |

|  3 | Yang Guo   |

+----+------------+

2 rows in set (0.00 sec)