mysql主从异步复制

一、实验环境

1、主服务器

• MYSQL-master：192.168.200.111
• MYSQL-slave1：192.168.200.112
• MYSQL-slave2：192.168.200.113

2、所有主机安装mariadb

[root@localhost ~]# yum -y install mariadb*
[root@localhost ~]# systemctl start mariadb

3、所有服务器关闭防火墙和安全机制

[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# iptables -F
[root@localhost ~]# setenforce 0

二、建立时间同步环境 ，在主服务器上安装配置NTP时间同步服务器

1、在master上配置

安装时间服务器

[root@mysql-m ~]# yum -y install ntp

修改配置文件

[root@mysql-m ~]# vim /etc/ntp.conf    //手动添加
server 127.127.1.0
fudge 127.127.1.0 stratum 8

启动NTP服务

[root@mysql-m ~]# systemctl start ntpd

2、在两个slave节点上配置（以slave1为例）

[root@mysql-s1 ~]# yum -y install ntpdate
[root@mysql-s1 ~]# ntpdate 192.168.200.111
19 Aug 18:57:08 ntpdate[22380]: no server suitable for synchronization found

三、配置master主服务器

1、修改配置文件（开启二进制日志）

[root@mysql-m ~]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-binlog
log-slave-updates=true

[root@mysql-m ~]# systemctl restart mariadb

2、给从服务器授权

[root@mysql-m ~]# mysql -uroot -p

MariaDB [(none)]> grant replication slave on *.* to 'myslave'@'192.168.200.%' identified by '123123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;   //刷新授权表
Query OK, 0 rows affected (0.00 sec)

3、查看日志位置

MariaDB [(none)]> show master status;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000003 |      615 |              |                  |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

四、配置slave从服务器

1、从库连接主库进行测试

[root@mysql-s1 ~]# mysql -u myslave -p123123 -h 192.168.200.111

-----------------------------------------------------------

[root@mysql-s2 ~]# mysql -u myslave -p123123 -h 192.168.200.111

2、修改主配置文件（开启中继日志）

[root@mysql-s1 ~]# vim /etc/my.cnf
server-id       = 2
relay-log=relay-log-bin 
relay-log-index=slave-relay-bin.index

[root@mysql-s1 ~]# systemctl restart mariadb

-----------------------------------------------

[root@mysql-s2 ~]# vim /etc/my.cnf
server-id       = 3
relay-log=relay-log-bin 
relay-log-index=slave-relay-bin.index

[root@mysql-s2 ~]# systemctl restart mariadb

3、

[root@mysql-s1 ~]# mysql -uroot -p

MariaDB [(none)]> stop slave;   //停掉自己从slave的角色
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> CHANGE MASTER TO
    MASTER_HOST='192.168.200.111',
    MASTER_USER='myslave',
    MASTER_PASSWORD='123123',
    MASTER_LOG_FILE='mysql-binlog.000003',  //日志文件的位置
    MASTER_LOG_POS=615;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.111
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000003
          Read_Master_Log_Pos: 615
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 532
        Relay_Master_Log_File: mysql-binlog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

-----------------------------------------------------------------------

[root@mysql-s2 ~]# mysql -uroot -p

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> CHANGE MASTER TO
    MASTER_HOST='192.168.200.111',
    MASTER_USER='myslave',
    MASTER_PASSWORD='123123',
    MASTER_LOG_FILE='mysql-binlog.000003',
    MASTER_LOG_POS=615;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.111
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000003
          Read_Master_Log_Pos: 615
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 532
        Relay_Master_Log_File: mysql-binlog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

============================================================================
reset slave  //清除所有，相当于恢复出厂设置

五、测试

1、在master主机上创建一个crushlinux的库

MariaDB [(none)]> create database crushlinux;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| crushlinux         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

2、查看两台从slave主机

第一台：
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| crushlinux         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

------------------------------------------------------------------------
第二台：
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| crushlinux         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)