mariadb主主復制
主主復制：
互為主從：兩個節(jié)點各自都要開啟binlog和relay log；
1霎挟、數(shù)據(jù)不一致悄但；
2、自動增長id母廷；
定義一個節(jié)點使用奇數(shù)id
auto_increment_offset=1 起始節(jié)點
auto_increment_increment=2 每次偏移節(jié)點轻黑，即往上累加數(shù)
另一個節(jié)點使用偶數(shù)id
auto_increment_offset=2
auto_increment_increment=2
配置：
1、server_id必須要使用不同值琴昆；
2氓鄙、均啟用binlog和relay log；
3业舍、存在自動增長id的表抖拦，為了使得id不相沖突，需要定義其自動增長方式舷暮；
服務(wù)啟動后執(zhí)行如下兩步：
4态罪、都授權(quán)有復制權(quán)限的用戶賬號；
5脚牍、各把對方指定為主節(jié)點向臀；
步驟：
1、設(shè)置配置文件
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诸狭、創(chuàng)建授權(quán)復制權(quán)限用戶
創(chuàng)建有復制權(quán)限的賬號券膀，設(shè)置互為主從
有兩種方法：
a君纫、先各自創(chuàng)建有復制權(quán)限的賬號，然后各自從創(chuàng)建賬號之后的位置開始復制
b芹彬、其中一個先創(chuàng)建有復制權(quán)限的賬號蓄髓，另一個從第一個創(chuàng)建賬號之前開始復制，然后第二個開始創(chuàng)建有復制權(quán)限的賬號舒帮，第一個從第二個創(chuàng)建賬號之后的位置開始復制
下面演示的是第一種方式
node1
mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON . TO 'repluser'@'172.16.100.%' IDENTIFIED BY '123456'; 創(chuàng)建授權(quán)復制權(quán)限用戶
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; 刷新授權(quán)表
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会喝、配置從節(jié)點互相通過授權(quán)賬號連接至主節(jié)點
node1 node2同node1把對應(yīng)master相關(guān)信息改一下
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的起始節(jié)點為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)
