mysql 主从 异步_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.0fudge127.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=1log-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.00sec)

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 = 2relay-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 = 3relay-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.00sec)

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.00sec)

MariaDB [(none)]>start slave;

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master to send eventMaster_Host:192.168.200.111Master_User: myslave

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-binlog.000003Read_Master_Log_Pos:615Relay_Log_File: relay-log-bin.000002Relay_Log_Pos:532Relay_Master_Log_File: mysql-binlog.000003Slave_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.00sec)

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.01sec)

MariaDB [(none)]>start slave;

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master to send eventMaster_Host:192.168.200.111Master_User: myslave

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-binlog.000003Read_Master_Log_Pos:615Relay_Log_File: relay-log-bin.000002Relay_Log_Pos:532Relay_Master_Log_File: mysql-binlog.000003Slave_IO_Running: Yes

Slave_SQL_Running: Yes

============================================================================

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

五、测试

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

MariaDB [(none)]>create database crushlinux;

Query OK,1 row affected (0.01sec)

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.00sec)------------------------------------------------------------------------第二台：

MariaDB [(none)]>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| crushlinux |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.00 sec)