部署mariadb主从异步复制

一、实验环境

主机名	ip地址
主服务器	192.168.200.111
从服务器1	192.168.200.112
从服务器2	192.168.200.113

二、时间同步

• 主服务器操作

[root@localhost ~]# yum -y install ntp
[root@localhost ~]# vim /etc/ntp.conf 
server 127.127.1.0
fudge 127.127.1.0 stratum 8
#开启服务
[root@localhost ~]# systemctl restart ntpd

• 两台从服务器操作

[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate 192.168.200.111

三、主服务器mariadb操作

• 修改配置文件，开启二进制文件功能，设置server-id

[root@localhost ~]# vim /etc/my.cnf

[mysqld]
server-id=1 
log-bin=mysql-bin  #开启二进制日志功能
log-slave-updates=true  #允许从服务器复制

• 设置授权用户

MariaDB [(none)]> grant replication slave on *.* to 'myslave'@'192.168.200.11_' identified by '123';
MariaDB [(none)]> flush privileges;

• 查看master，file和position

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      476 |              |                  |
+------------------+----------+--------------+------------------+

• 重启mariadb服务

[root@localhost ~]# systemctl restart mariadb

四、从服务器上mariadb操作

• 修改配置文件

[root@localhost ~]# vim /etc/my.cnf

[mysqld]
server-id=2   #另一台改为3
relay-log=relay-log-bin  #开启中继日志功能
relay-log-index=slave-relay-bin.index

• 授权启动从库

#停止slave
MariaDB [(none)]> stop slave;
#进行授权
MariaDB [(none)]> CHANGE MASTER TO  MASTER_HOST='192.168.200.111',  MASTER_USER='myslave', MASTER_PASSWORD='123', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=476;
#开启slave
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;
#两条为yes表示成功
  Slave_IO_Running: Yes  
  Slave_SQL_Running: Yes

五、测试

• 主服务器

MariaDB [(none)]> create database yi;
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| yi                 |
+--------------------+

• 从服务器1

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| yi                 |
+--------------------+

• 从服务器2

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| yi                 |
+--------------------+