一、概述
将主数据库中的DDL和DML操作通过二进制日志传输到从数据库上,然后将这些日志重新执行(重做);从而使得从数据库的数据与主数据库保持一致。
二、实验
关闭核心防护和清空防火墙规则
setenforce 0
iptables -F
安装mysql不做演示了
调整配置文件(主服务器)
vi /etc/my.cnf
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1 //各服务器的server-id不能相同
log-bin = master-bin //新增,开启二进制文件功能
log-slave-updates = true //新增,用来控制是否写入二进制文件即允许同步
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
调整从服务器主配置文件
vi /etc/my.cnf
server-id = 2
relay-log = relay-log-bin //新增,开启二进制文件
relay-log-index = slave-relay-bin.index //新增,中继
重启MySQL服务
systemctl restart mysqld
配置 MySQL 一主两从
mysql> grant replication slave on . to ‘myslave’@‘20.0.0.%’ identified by ‘123’;
在 Mysql1 主服务器上查看二进制文件和同步点
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 1893 | | | |
+-------------------+----------+--------------+------------------+-------------------+
接下来在 Mysql2 和 Mysql3 分别执行同步。
mysql> change master to master_host='20.0.0.130',master_user='myslave',master_password='123',master_log_file='master-bin.000001',master_log_pos=1893;
mysql> start slave;
查看 IO 和 SQL 线程都是 yes 代表同步正常。
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
问题:Slave_IO_Running: connecting
我的原因是核心防护未关,防火墙规则未清空
验证主从复制
在主服务器上创建一个数据库,可以在从服务器上同步看到