首页 > 代码库 > MySQL的主从复制
MySQL的主从复制
当业务扩展到一定程度时，服务器的负载能力往往不能胜任当前的业务规模，解决的方法无非是横向扩展和纵向扩展：
横向扩展：又称水平扩展（Scale Out），是通过负载均衡的方式，将压力疏散到后端各个节点服务器；
纵向扩展：又称垂直扩展（Scale Up），简单提高服务器的硬件性能，此方法很容易再次出现性能瓶颈现象。
数据库服务器在涉及到均衡调度时通常会遇到很多不可忽视的问题：
数据库服务器数据的一致性；
多个事务提交导致的数据完整性的保证；
服务器宕机，怎么保证事务正常提交等等。
数据同步方面，一般得解决方案有一下几种：
同步复制 (Synchronous Replication)：发生写数据时，主服务器需要等待从服务器同步完成之后才会返回给客户端信息，等待过程中为阻塞状态，所以效率极低；
半同步复制 (Asynchronous Replication)：发生写数据时，主服务器只同步给其中一台从服务器，半同步给其他从服务器；
异步复制 (Semisynchronous Replication)：发生写数据时，主服务器在从服务器同步数据的同时，向客户端返回信息，这种方式可能导致主从服务器数据不一致，或者从服务器数据同步状态严重落后于主服务器上的数据。
MySQL主从复制模式：Master/Slave、Master/Master
Master/Slave：一主多从模式，由于从服务器只有只读权限，所以此种模式只能有效分担读请求的压力，但不能分担写请求；此种模式会带来数据同步一致性的问题，可能从服务器同步的数据进度远落后于主服务器更新的进度；如果主机宕机后，则数据就无法写入。
Master/Master：多主模式，多台服务器互为主从；这种模式会带来数据更新不一致，从而发生冲突的问题。
主/从工作流程：
建立主从关系后，如果主服务器发生Data Change之后，Binary Log会更新日志记录；从服务器通过I/O
Thread读取主服务器上的Binary Log保存至本地的 Relay
Log中，再通过SQL Thread执行本地Relay
Log中的SQL语句，并将执行后的数据保存至磁盘中。
示例一：MySQL异步复制实现
配置文件：
Master:node7配置文件：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
innodb_file_per_table=1
skip_name_resolve=ON
log_bin=master-log #开启二进制日志
server_id=1 #设置serverID
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
Slave:node8配置文件：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
relay-log=relay-log #开启relay日志
innodb_file_per_table=1
read-only=1 #设置为只读
server_id=2 #设置serverID，必须不同于其他serverID
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
启动mariaDB：
[root@node7 ~]# systemctl start mariadb.service
[root@node8 ~]# systemctl start mariadb.service
从服务器需要主服务器授权一个具有REPLICATION SLAVE, REPLICATION CLIENT权限的用户：
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘hisen‘@‘192.168.17.%‘ IDENTIFIED BY ‘hisen‘;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> SHOW MASTER STATUS; #记录此时的binlog文件及位置，从服务器即将从此处进行同步
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 492 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
从服务器开始做同步复制的配置：
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST=‘192.168.17.70‘,
-> MASTER_USER=‘hisen‘,
-> MASTER_PASSWORD=‘hisen‘,
-> MASTER_LOG_FILE=‘master-log.000003‘,
-> MASTER_LOG_POS=492;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G; #查看从服务器状态
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.17.70
Master_User: hisen
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 492
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: No #IO-Thread没有启动
Slave_SQL_Running: No #SQL-Thread没有启动
MariaDB [(none)]> START SLAVE; 启动IO-Thread和SQL-Thread
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.17.70 #主服务器地址
Master_User: hisen #复制时用到的用户名
Master_Port: 3306 #主服务器端口
Connect_Retry: 60 #重试连接时长
Master_Log_File: master-log.000003 #同步主服务器上的binlog文件
Read_Master_Log_Pos: 492 #binlog文件的位置
Relay_Log_File: relay-log.000002 #本地relay文件
Relay_Log_Pos: 530 #relay文件位置
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Yes #IO-Thread启动
Slave_SQL_Running: Yes #SQL-Thread启动
测试主从复制：
Master配置：
MariaDB [(none)]> CREATE DATABASE hisendb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> USE hisendb;
Database changed
MariaDB [hisendb]> CREATE TABLE teachers (TID TINYINT NOT NULL PRIMARY KEY,Name VARCHAR(50),Age INT,Gender CHAR(10));
Query OK, 0 rows affected (0.02 sec)
MariaDB [hisendb]> INSERT INTO teachers VALUES (1,‘Mo Yuan‘,100,‘M‘),(2,‘Bai Qian‘,150,‘F‘),(3,‘Dong Huadijun‘,100,‘M‘);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [hisendb]> SELECT * FROM teachers;
+-----+---------------+------+--------+
| TID | Name | Age | Gender |
+-----+---------------+------+--------+
| 1 | Mo Yuan | 100 | M |
| 2 | Bai Qian | 150 | F |
| 3 | Dong Huadijun | 100 | M |
+-----+---------------+------+--------+
3 rows in set (0.00 sec)
Slave端查看：
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hisendb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
MariaDB [(none)]> USE hisendb;
Database changed
MariaDB [hisendb]> SELECT * FROM teachers;
+-----+---------------+------+--------+
| TID | Name | Age | Gender |
+-----+---------------+------+--------+
| 1 | Mo Yuan | 100 | M |
| 2 | Bai Qian | 150 | F |
| 3 | Dong Huadijun | 100 | M |
+-----+---------------+------+--------+
3 rows in set (0.00 sec)
对比可知，已经实现主从同步的效果！
再次查看Master信息：
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 1008 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
再次查看Slave信息：
MariaDB [hisendb]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.17.70
Master_User: hisen
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 1008 #已经跟Master的binlog位置相同
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 1046
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
示例二：MySQL半同步复制实现
需要插件支持：semisync_master.so、semisync_slave.so
主节点：
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so‘;
Query OK, 0 rows affected (0.10 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%‘;
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=ON;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%‘;
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON | #半同步主节点开启
| rpl_semi_sync_master_timeout | 10000 | #主节点等待超时时间，默认10S
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘hisen‘@‘192.168.17.%‘ IDENTIFIED BY ‘hisen‘;
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 417 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘%semi%‘;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 | #已经有一个半同步客户端
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | ON | #已经确认为半同步复制的主节点
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
从节点：
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so‘;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%‘;
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=ON;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%‘;
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST=‘192.168.17.70‘,
-> MASTER_USER=‘hisen‘,
-> MASTER_PASSWORD=‘hisen‘,
-> MASTER_LOG_FILE=‘master-log.000003‘,
-> MASTER_LOG_POS=417;
Query OK, 0 rows affected (0.01 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.17.70
Master_User: hisen
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 417
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
MariaDB [hisendb]> SHOW GLOBAL STATUS LIKE ‘%semi%‘;
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON | #已经确认为半同步复制中从节点
+----------------------------+-------+
1 row in set (0.00 sec)
验证主从模式半同步复制：
Master节点：
MariaDB [(none)]> CREATE DATABASE hisendb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> USE hisendb;
Database changed
MariaDB [hisendb]> CREATE TABLE teachers(TID INT NOT NULL PRIMARY KEY,Name VARCHAR(50) NOT NULL,Age INT ,Gender CHAR(1));
Query OK, 0 rows affected (0.01 sec)
MariaDB [hisendb]> INSERT INTO teachers VALUES (1,‘Ma Yun‘,55,‘M‘),(2,‘Ma Huateng‘,43,‘M‘),(3,‘Li Yanhong‘,49,‘M‘);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [hisendb]> SELECT * FROM teahcers;
ERROR 1146 (42S02): Table ‘hisendb.teahcers‘ doesn‘t exist
MariaDB [hisendb]> SELECT * FROM teachers;
+-----+------------+------+--------+
| TID | Name | Age | Gender |
+-----+------------+------+--------+
| 1 | Ma Yun | 55 | M |
| 2 | Ma Huateng | 43 | M |
| 3 | Li Yanhong | 49 | M |
+-----+------------+------+--------+
3 rows in set (0.00 sec)
MariaDB [hisendb]> SHOW GLOBAL STATUS LIKE ‘%semi%‘;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1500 |
| Rpl_semi_sync_master_net_wait_time | 4502 |
| Rpl_semi_sync_master_net_waits | 3 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 1592 |
| Rpl_semi_sync_master_tx_wait_time | 4777 |
| Rpl_semi_sync_master_tx_waits | 3 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 3 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
Slave节点：
MariaDB [(none)]> USE hisendb
Database changed
MariaDB [hisendb]> SELECT * FROM teachers;
+-----+------------+------+--------+
| TID | Name | Age | Gender |
+-----+------------+------+--------+
| 1 | Ma Yun | 55 | M |
| 2 | Ma Huateng | 43 | M |
| 3 | Li Yanhong | 49 | M |
+-----+------------+------+--------+
3 rows in set (0.00 sec)
到此主从模式的半同步复制已经成功，但是如果主节点与从节点的IO-Thread停止通信，则主节点会等待一个超时时间，然后自动降级为异步复制模式：
关闭从节点Slave功能：
MariaDB [hisendb]> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)
MariaDB [hisendb]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.17.70
Master_User: hisen
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 932
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 1045
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: No #IO-Tread关闭
Slave_SQL_Running: No #SQL-Thread关闭
主节点再插入数据，会等待10s的半同步超时时间：
MariaDB [hisendb]> INSERT INTO teachers VALUES (4,‘Lei Jun‘,44,‘M‘);
Query OK, 1 row affected (10.00 sec)
查看主节点半同步信息：
MariaDB [hisendb]> SHOW GLOBAL STATUS LIKE ‘%semi%‘;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1194 |
| Rpl_semi_sync_master_net_wait_time | 4777 |
| Rpl_semi_sync_master_net_waits | 4 |
| Rpl_semi_sync_master_no_times | 2 |
| Rpl_semi_sync_master_no_tx | 2 |
| Rpl_semi_sync_master_status | OFF | #关闭半同步主节点功能
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 1592 |
| Rpl_semi_sync_master_tx_wait_time | 4777 |
| Rpl_semi_sync_master_tx_waits | 3 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 3 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
示例三：MySQL双主模式实现：
MySQL的双主模式，其实就是服务器互为主从关系的模式，都拥有写数据的权限，也就是说双方都分别拥有binlog和relaylog。但是双主模式会带来不少问题，也容易造成数据不一致的情况。
由于双主/多主服务器都能对同一表进行插入新数据，如果有id属性，可能会造成双主服务器插入数据冲突，这种问题可以通过自增长id来解决，但是看起来并没像想象中那么美好：
从上图可以看出，按照自增长id的方式，ID号出现“间隙”。主主模式在使用时，问题多多，所以生产环境中MySQL一般都使用主从模式，主主模式可以作为了解MySQL同步数据其中的一种方式而已。
实验拓扑：
配置主要节点：
MySQL服务启动前，配置文件设定：
(1)sever_id必须不同；
(2)双方都需要启用binlog和relaylog；
(3)存在自增长id的表，为了使得id不冲突，需要定义自动增长方式：
一个节点使用奇数id
auto_increment_offset=1
auto_increment_increment=2
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
MySQL服务启动后：
(1)双方都需要有复制权限的用户；
(2)双方都需要指向对方为自己的主节点。
实验过程：
node7文件配置：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
thread_handling=pool-of-threads
innodb_file_per_table=1
skip_name_resolve=ON
log_bin=master-log #开启binlog
relay-log=relay-log #开启relay log
auto_increment_offset=1 #自增长id初始值
auto_increment_increment=2 #自增长id步进
server_id=1 #serverID
node8文件配置：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
relay-log=relay-log #开启relay log
innodb_file_per_table=1
server_id=2 #serverID
log_bin=master-log #开启binlog
auto_increment_offset=2 #自增长id初始值
auto_increment_increment=2 #自增长id步进
①启动MySQL服务：
[root@node7 ~]# systemctl start mariadb.service
[root@node8 ~]# systemctl start mariadb.service
②各自创建一个拥有复制权限的用户：
node7 :
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘hisen‘@‘192.168.17.%‘ IDENTIFIED BY ‘hisen‘;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.11 sec)
node8:
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘hisen‘@‘192.168.17.%‘ IDENTIFIED BY ‘hisen‘;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
③查看双方当前使用的binlog及位置：
node7：
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 502 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
node8：
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 502 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
④各自指向对方的服务器为自己的主节点：
node7 :
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST=‘192.168.17.80‘,
-> MASTER_USER=‘hisen‘,
-> MASTER_PASSWORD=‘hisen‘,
-> MASTER_LOG_FILE=‘master-log.000003‘,
-> MASTER_LOG_POS=502;
Query OK, 0 rows affected (0.04 sec)
node8：
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST=‘192.168.17.70‘,
-> MASTER_USER=‘hisen‘,
-> MASTER_PASSWORD=‘hisen‘,
-> MASTER_LOG_FILE=‘master-log.000003‘,
-> MASTER_LOG_POS=502;
Query OK, 0 rows affected (0.02 sec)
⑤开启SLAVE，并查看SLAVE状态：
node7：
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.17.80 #主节点为对方IP
Master_User: hisen
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 502
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Yes #已经开启IO-Thread
Slave_SQL_Running: Yes #已经开启SQL-Thread
node8：
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.17.70 #主节点为对方IP
Master_User: hisen
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 502
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Yes #已经开启IO-Thread
Slave_SQL_Running: Yes #已经开启SQL-Thread
⑥测试主主数据复制：
创建数据库hisendb，并创建friends表：
MariaDB [(none)]> CREATE DATABASE hisendb;
Query OK, 1 row affected (0.01 sec)
MariaDB [hisendb]> CREATE TABLE friends(ID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,Name VARCHAR(50) NOT NULL,Age TINYINT,Gender ENUM(‘F‘,‘M‘));
Query OK, 0 rows affected (0.09 sec)
首先在node7主节点插入 (‘Xu He‘,25,‘M‘),(‘Xu Mingying‘,27,‘F‘):
MariaDB [hisendb]> INSERT INTO friends(Name,Age,Gender) VALUES (‘Xu He‘,25,‘M‘),(‘Xu Mingying‘,27,‘F‘);
Query OK, 2 rows affected (0.17 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [hisendb]> SELECT * FROM friends;
+----+-------------+------+--------+
| ID | Name | Age | Gender |
+----+-------------+------+--------+
| 1 | Xu He | 25 | M |
| 3 | Xu Mingying | 27 | F |
+----+-------------+------+--------+
2 rows in set (0.00 sec)
再次在node8主节点插入(‘Tian Tao‘,26,‘M‘),(‘Liang Juntao‘,27,‘M‘):
MariaDB [hisendb]> INSERT INTO friends(Name,Age,Gender) VALUES (‘Tian Tao‘,26,‘M‘),(‘Liang Juntao‘,27,‘M‘);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [hisendb]> SELECT * FROM friends;
+----+--------------+------+--------+
| ID | Name | Age | Gender |
+----+--------------+------+--------+
| 1 | Xu He | 25 | M |
| 3 | Xu Mingying | 27 | F |
| 4 | Tian Tao | 26 | M |
| 6 | Liang Juntao | 27 | M |
+----+--------------+------+--------+
4 rows in set (0.00 sec)
由上可知，双方能完成数据同步，但是自增长ID的确出现“间隙”。
本文出自 “DevOps” 博客，请务必保留此出处http://hisen2devops.blog.51cto.com/10109340/1909315
MySQL的主从复制