系统环境
主机名 IP
node1 192.168.231.130
node2 192.168.231.131
MariaDB版本
# mysql --version
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
Mariadb主从复制
主只可写,从只可读
主从复制:
从节点:
I/O Thread:从Master请求二进制日志事件,并保存于中继日志中
SQL Thread:从中继日志中读取日志文件,在本地完成重放
主节点:
dump Thread:为每个Slave的I/O Thread启动一个dump线程,用于向其发送binary log events
特点:
1.异步复制
2.主从数据不一致比较常见
配置过程:
主节点:
(1)启动二进制日志
(2)为当前节点设置一个全局唯一的ID号
(3)创建有复制权限的用户账号
从节点:
(1)启动中继日志
(2)为当前节点设置一个全局唯一的ID号
(3)使用有复制权限的用户账号连接至主服务器,并启动复制线程
实例
#编辑node1配置文件
[root@node1 ~]# vim /etc/my.cnf
log_bin=mysql-bin #开启二进制日志,以mysql-bin命名文件
innodb_file_per_table=ON #每个新创建的表都会有一个表空间,将每个新创建的表的数据及索引存储在一个独立的.ibd文件里
server_id=1 #id号
skip_name_resolve=ON #禁止域名解析的
#修改配置文件后重启mariadb
[root@node1 ~]# systemctl restart mariadb
#创建repluser用户,并赋予REPLICATION SLAVE,REPLICATION CLIENT权限
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.231.131' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
- REPLICATION SLAVE :常用于建立复制时所需要用到的用户权限,也就是slave server必须被master server授权具有该权限的用户,才能通过该用户复制。
- REPLICATION CLIENT :不可用于建立复制,有该权限时,只是多了可以使用如"SHOW SLAVE STATUS"、"SHOW MASTER STATUS"等命令。
#刷新权限
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#编辑node2的mariadb配置文件
[root@node2 ~]# vim /etc/my.cnf
relay_log=relay-log #中继日志
server_id=6
innodb_file_per_table=ON
skip_name_resolve=ON
[root@node2 ~]# systemctl start mariadb
[root@node1 mysql]# mysql
MariaDB [(none)]> help CHANGE MASTER TO
#查看主节点二进制日志
MariaDB [(none)]> show master logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 30331 |
| master-bin.000002 | 1038814 |
| master-bin.000003 | 245 |
+-------------------+-----------+
3 rows in set (0.00 sec)
[root@node2 mysql]# mysql
#查看CHANGE MASTER TO 详细解释
MariaDB [(none)]> help CHANGE MASTER TO
#确定需要同步的主机IP,用户名,密码,binlog文件,binlog位置等信息
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.231.130',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=245;
#查看slave的状态
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.231.130
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003 #当前二进制日志
Read_Master_Log_Pos: 245 #二进制记录位置
Relay_Log_File: relay-log.000001 #中继日志
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: No #IO线程没有开启
Slave_SQL_Running: No #SQL线程没有开启
#启动SLAVE,相应的IO和SQL线程就会开启
MariaDB [(none)]> START SLAVE;
测试:
#创建数据库后,二进制日志进行记录,位置变为578
[root@node1 ~]# mysql
MariaDB [(none)]> create database mydb;
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 578 | | |
+-------------------+----------+--------------+------------------+
[root@node2 ~]# mysql
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.231.130
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 578 #读取node1二进制日志到578,说明复制成功
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 863
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes #IO线程开启
Slave_SQL_Running: Yes #SQL线程开启
#node2也可查看到mydb数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
注意:进行下边实验时,数据可能会影响,可删除/etc/my.cnf中datadir目录中数据,并重启mariadb
MariaDB主主复制
都可进行读写
互为主从:
1、数据不一致:因此,慎用
2、自动增长id
需要用到辅助工具(这里不再使用奇偶id,使用自增id即可)
否则会出现:
| 12 | landongyang |
| 14 | ruifeng |
| 16 | xiaohe |
| 17 | lisiye |
| 19 | changshun |
| 21 | lidaniang |
| 23 | wenxiansheng |
| 24 | landongyang1 |
| 26 | ruifeng1 |
| 28 | xiaohe1 |
配置一个节点使用奇数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)均把对方指定为主节点,并启动复制线程
实例
#编辑node1配置文件
[root@node1 ~]# vim /etc/my.cnf
log_bin=master-bin
relay_log=relay-log
innodb_file_per_table=ON
server_id=1
skip_name_resolve=ON
#重启mariadb
[root@node1 ~]# systemctl restart mariadb
[root@node1 ~]# mysql
#下边命令不再注释,可看前文
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.231.131' IDENTIFIED BY '123456';
Query OK, 0 rows affected (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-bin.000003 | 499 | | |
+-------------------+----------+--------------+------------------+
#编辑node2配置文件
[root@node2 ~]# vim /etc/my.cnf
log_bin=master-bin
relay_log=relay-log
innodb_file_per_table=ON
server_id=1
skip_name_resolve=ON
#重启mariadb
[root@node2 ~]# systemctl start mariadb
[root@node2 ~]# mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.231.130' IDENTIFIED BY '123456';
Query OK, 0 rows affected (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-bin.000003 | 509 | | |
+-------------------+----------+--------------+------------------+
[root@node1 ~]# mysql
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.231.131',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=509;
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 499 | | |
+-------------------+----------+--------------+------------------+
[root@node2 ~]# mysql
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.231.130',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=499;
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> start slave;
[root@node1 ~]# mysql
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.231.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 509
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: 509
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: 6
1 row in set (0.00 sec)
[root@node2 ~]# mysql
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.231.130
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 509
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: 509
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)
测试
node1和node2都可进行读写
#进入数据库创建数据库
[root@node1 ~]# mysql
MariaDB [(none)]> create database mydb;
MariaDB [(none)]> use mydb
MariaDB [mydb]> create table student(id int NOT NULL AUTO_INCREMENT PRIMARY KEY,name char(20));
MariaDB [mydb]> insert into student(name) values('changshun'),('lidaniang'),('wenxiansheng');
MariaDB [mydb]> select * from student;
+----+--------------+
| id | name |
+----+--------------+
| 1 | changshun |
| 2 | lidaniang |
| 3 | wenxiansheng |
+----+--------------+
[root@node2 ~]# mysql
MariaDB [(none)]> use mydb;
MariaDB [mydb]> select * from student;
+----+--------------+
| id | name |
+----+--------------+
| 1 | changshun |
| 2 | lidaniang |
| 3 | wenxiansheng |
+----+--------------+
MariaDB [mydb]> insert into student(name) values('tianyou'),('ruixuan'),('xiaoshun');
MariaDB [mydb]> select * from student;
+----+--------------+
| id | name |
+----+--------------+
| 1 | changshun |
| 2 | lidaniang |
| 3 | wenxiansheng |
| 4 | tianyou |
| 5 | ruixuan |
| 6 | xiaoshun |
+----+--------------+
MariaDB半同步复制
主只可读,从只可读
首先了解一下半同步复制
- 异步复制(Asynchronous replication)
MySQL默认的复制即是异步的,主库在执行完客户端提交的事务后会立即将结果返给给客户端,并不关心从数据库是否已经接收并处理,这样就会有一个问题,主服务如果crash掉了,此时主服务上已经提交的事务可能并没有传到从上,如果此时,强行将从提升为主,可能导致新主上的数据不完整。
- 全同步复制(Fully synchronous replication)
指当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回,所以全同步复制的性能必然会收到严重的影响。
- 半同步复制(Semisynchronous replication)
介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,半同步复制最好在低延时的网络中使用。
master:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SET GLOBAL VARIABLES rpl_semi_sync_master_enabled=l;
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
mysq1> SHOW GLOBAL STATUS LIKE '%semi%';
slave:
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL VARIABLES rpl_semi_sync_slave_enabled=1;
#编辑node1配置文件
[root@node1 ~]# vim /etc/my.cnf
log_bin=master-bin
innodb_file_per_table=ON
server_id=1
skip_name_resolve=ON
#重启mariadb
[root@node1 ~]# systemctl restart mariadb
[root@node1 ~]# mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.231.131' IDENTIFIED BY '123456';
Query OK, 0 rows affected (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-bin.000003 | 499 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#在node1安装semisync_master,主半同步插件
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
#查看插件列表中是否已安装
MariaDB [(none)]> SHOW PLUGINS;
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
#查看半同步复制相关变量
MariaDB [(none)]> show global variables like '%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 |
+------------------------------------+-------+
#开启半同步复制功能
MariaDB [(none)]> set global rpl_semi_sync_master_enabled=1;
MariaDB [(none)]> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
#编辑node2配置文件
[root@node2 ~]# vim /etc/my.cnf
relay_log=relay-log
server_id=6
innodb_file_per_table=ON
skip_name_resolve=ON
[root@node2 ~]# systemctl restart mariadb
[root@node2 ~]# mysql
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
MariaDB [(none)]> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=1;
MariaDB [(none)]> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
[root@node2 ~]# mysql
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.231.130',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=499;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave;
测试
[root@node1 ~]# mysql
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use mydb;
Database changed
MariaDB [mydb]> create table mytb(id int,name char(30));
Query OK, 0 rows affected (0.01 sec)
MariaDB [mydb]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1074 |#平均等待时间
| Rpl_semi_sync_master_net_wait_time | 2149 |#半同步复制等待时间
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 1031 |
| Rpl_semi_sync_master_tx_wait_time | 2063 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
复制过滤
复制过滤器:
让从节点仅复制指定的数据库,或指定数据库的指定表
有两种实现方式:
(1)主服务器仅向二进制日志中记录与特定数据库(特定表)相关的事件
问题:时间还原无法实现;不建议使用
参数:
binlog_do_db= #数据库白名单列表
binlog_ignore_db= #数据库黑名单列表
(2)从服务器SQL_THREAD在replay中继日志中的事件时,仅读取与特定数据库(特定表)相关的事件并应用于本地
问题:会造成网络及IO浪费
参数:
replicate_do_db
replicate_ignore_db
replicate_do_table
replicate_ignore_db
replicate_ignore_table
replicate_wild_do_table
replicate_wild_ignore_table
在这里演示第二种方案:
可以基于半同步复制进行实验,只是设置参数进行过滤
[root@node2 ~]# mysql
#先把slave停掉,再设置变量,否则会报错
MariaDB [(none)]> stop slave;
MariaDB [(none)]> show global variables like 'replicate%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| replicate_annotate_row_events | OFF |
| replicate_do_db | |
| replicate_do_table | |
| replicate_events_marked_for_skip | replicate |
| replicate_ignore_db | |
| replicate_ignore_table | |
| replicate_wild_do_table | |
| replicate_wild_ignore_table | |
+----------------------------------+-----------+
#设置复制的数据库白名单
MariaDB [(none)]> set global replicate_do_db=mydb;
MariaDB [(none)]> start slave;
测试
node1:
MariaDB [mydb]> create database yourdb;
MariaDB [mydb]> insert into mytb values(1,'ruixuan'),(2,'ruifeng'),(3,'ruiquan');
MariaDB [mydb]> select * from mytb;
+------+---------+
| id | name |
+------+---------+
| 1 | ruixuan |
| 2 | ruifeng |
| 3 | ruiquan |
+------+---------+
node2:
#没有复制yourdb
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
#数据复制到mydb中的表mytb
MariaDB [mydb]> select * from mytb;
+------+---------+
| id | name |
+------+---------+
| 1 | ruixuan |
| 2 | ruifeng |
| 3 | ruiquan |
+------+---------+