1. 环境说明
- 软件版本
操作系统:CentOS Linux release 7.3.1611 (Core) (3.10.0-514.el7.x86_64)
MariaDB-server:10.2.30
- 硬件环境
node2 172.16.100.52
node3 172.16.100.53
node3 172.16.100.54
2. 主服务器配置(on node2)
[mysqld]
character-set-server=utf8
default_storage_engine=InnoDB
innodb_file_per_table=on
skip_name_resolve=on
gtid_strict_mode=on //与failover相关的配置
log_slave_updates=on //与failover相关的配置
server_id=1000 //在集群环境中server_id必须唯一
log_bin=on //开启二进制日志
binlog_format=row
log_basename=node2
3. 从服务器配置(on node3)
[mysqld]
character-set-server=utf8
default_storage_engine=InnoDB
innodb_file_per_table=on
skip_name_resolve=on
gtid_strict_mode=on //与failover相关的配置
log_slave_updates=on //与failover相关的配置
server_id=1005 //在集群环境中server_id必须唯一
log_bin=on //开启二进制日志
binlog_format=row
log_basename=node3
4. 从服务器配置(on node4)
[mysqld]
character-set-server=utf8
default_storage_engine=InnoDB
innodb_file_per_table=on
skip_name_resolve=on
gtid_strict_mode=on //与failover相关的配置
log_slave_updates=on //与failover相关的配置
server_id=1010 //在集群环境中server_id必须唯一
log_bin=on //开启二进制日志
binlog_format=row
log_basename=node4
5. 在主服务器上添加replication账号,并且记录主服务器的TID信息
MariaDB[(none)]> grant replication slave on *.* to 'slave'@'%' identified by 'passwd';
MariaDB[(none)]> flush privileges;
MariaDB[(none)]> show global variables like 'gtid_%'; //记录下gtid_binlog_pos的值,例如:0-1000-6
6. 在从服务器上配置并且启动slave线程
MariaDB[(none)]> set global gtid_slave_pos='0-1000-6'; //主服务器的GTID
MariaDB[(none)]> change master to MASTER_HOST = '172.16.100.52',MASTER_USER = 'slave',MASTER_PASSWORD = 'passwd',MASTER_USE_GTID=slave_pos;
MariaDB[(none)]> start slave; //启动slave线程
MariaDB[(none)]> show slave status\G //查看slave状态信息
- If replication is working correctly, both the values of
Slave_IO_Running
andSlave_SQL_Running
should beYes
:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
7. 主从服务器验证
- on master node2
MariaDB [(none)]> create schema park;
MariaDB [(none)]> use park;
MariaDB [(none)]> create table user(id int not null auto_increment,name varchar(20),primary key(id) );
MariaDB [(none)]> insert into user(name) values("chengdu");
- on slave node3(node4)
MariaDB [(none)]> show schemas;
MariaDB [(none)]> select * from park.user;
结论:slave通过IO Thread线程,获取master上的binlog日志信息,存储在本地的relay日志中;slave通过SQL线程将relay日志的语句在本地执行一次。
Reference: