mariadb主主复制

mariadb主主复制

主主复制：
    互为主从：两个节点各自都要开启binlog和relay log；
        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、均启用binlog和relay log；
        3、存在自动增长id的表，为了使得id不相冲突，需要定义其自动增长方式；
        
        服务启动后执行如下两步：
        4、都授权有复制权限的用户账号；
        5、各把对方指定为主节点；

步骤：
1、设置配置文件
Node1
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=1
log-bin=master-log
relay-log=relay-log
skip_name_resolve=on

auto_increment_offset=1
auto_increment_increment=2

systemctl restart mariadb.service

Node2
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=1
log-bin=master-log
relay-log=relay-log
skip_name_resolve=on

auto_increment_offset=2
auto_increment_increment=2

systemctl restart mariadb.service

2、创建授权复制权限用户
创建有复制权限的账号，设置互为主从
有两种方法：
a、先各自创建有复制权限的账号，然后各自从创建账号之后的位置开始复制
b、其中一个先创建有复制权限的账号，另一个从第一个创建账号之前开始复制，然后第二个开始创建有复制权限的账号，第一个从第二个创建账号之后的位置开始复制
下面演示的是第一种方式

node1
mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON . TO 'repluser'@'172.16.100.%' IDENTIFIED BY '123456'; 创建授权复制权限用户
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status; 查看此时二进制日志处于哪个文件的哪个位置
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 426 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> flush privileges; 刷新授权表
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 506 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Node 2
mysq;
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON . TO 'repluser'@'172.16.100.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 426 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 506 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

3、配置从节点互相通过授权账号连接至主节点
node1 node2同node1把对应master相关信息改一下
mariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.250.155',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=506;
mariaDB [(none)]> show slave status\G;
双方都启动复制和重放线程
mariaDB [(none)]> start slave;
mariaDB [(none)]> show slave status\G;
复制线程的启动会保存到错误日志/var/log/mariadb/mariadb.log文件中
tail /var/log/mariadb/mariadb.log
…
170914 11:02:26 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='172.16.250.155', master_port='3306', master_log_file='master-log.000003', master_log_pos='506'.
170914 11:03:52 [Note] Slave SQL thread initialized, starting replication in log 'master-log.000003' at position 506, relay log './relay-log.000001' position: 4
170914 11:03:52 [Note] Slave I/O thread: connected to master 'repluser@172.16.100.217:3306',replication started in log 'master-log.000003' at position 506
…
到此主主复制的配置已完可以开始测试了
Node2
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

Node 1
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use mydb;
MariaDB [mydb]> create table tb1(tid int auto_increment primary key ,name varchar(200));

Node 2
MariaDB [(none)]> use mydb;
MariaDB [mydb]> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| tb1 |
+----------------+
1 row in set (0.00 sec)

MariaDB [mydb]> desc tb1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| tid | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(200) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
MariaDB [mydb]> insert into tb1 (name) values ('jiajia'); #node 2的起始点为2，偏移量为2

Node 1
MariaDB [mydb]> select * from tb1 ;
+-----+--------+
| tid | name |
+-----+--------+
| 2 | jiajia |
+-----+--------+
1 row in set (0.00 sec)
MariaDB [mydb]> insert into tb1 (name) values ('huahua'),('haha'),('xmj'); #node 1的起始节点为1，偏移量为2
MariaDB [mydb]> select * from tb1;
+-----+--------+
| tid | name |
+-----+--------+
| 2 | jiajia |
| 3 | huahua |
| 5 | haha |
| 7 | xmj |
+-----+--------+
4 rows in set (0.00 sec)

Node 2
MariaDB [mydb]> insert into tb1 (name) values ('hello');
Query OK, 1 row affected (0.00 sec)

MariaDB [mydb]> select * from tb1;
+-----+--------+
| tid | name |
+-----+--------+
| 2 | jiajia |
| 3 | huahua |
| 5 | haha |
| 7 | xmj |
| 8 | hello |
+-----+--------+
5 rows in set (0.00 sec)