一、介绍

简称AB复制,在A主机上做create、update、insert、drop、delete等数据库、表、记录的增、删、改操作,B主机上会自动做数据库、表、记录的同步更新。

主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库,主数据库一般是准实时的业务数据库 事务处理库,从库做查询库

二、A工作原理(即工作过程)

a. 在主库上把数据更改记录到二进制日志(Binary Log)中。

b. 备库将主库上的日志复制到自己的中继日志(Relay Log)中。

c. 备库读取中继日志中的事件,将其重放到备库数据库之上。

三、AB复制的一主一从模式工作过程

A主机: create database db1 ----> 会将此命令自动写入本机的二进制日志文件中

B主机:
I/O线程: 监测并读A主机上的二进制日志文件新增的内容,且将新内容写入到B主机自己的中继日志文件中
SQL线程 : 读取B主机上中继日志文件中心的SQL语句,并且自动执行这些SQL语句。最终在B主机上创建了db1这个库。

四、主从复制的作用

一是确保数据安全;做数据的热备,作为后备数据库,主数据库服务器故障后,可切换到从数据库继续工作,避免数据的丢失。

二是提升I/O性能;随着日常生产中业务量越来越大,I/O访问频率越来越高,单机无法满足,此时做多库的存储,有效降低磁盘I/O访问的频率,提高了单个设备的I/O性能。

三是读写分离,使数据库能支持更大的并发;在报表中尤其重要。由于部分报表sql语句非常的慢,导致锁表,影响前台服务。如果前台使用master,报表使用slave,那么报表sql将不会造成前台锁,保证了前台速度


实验准备(本实验都在VMware中完成)

Navicat 15 for MySQL (图形化工具)

两台主机,分为安装MySQL,一台作为主库,一台作为从库

1、主库 IP:192.168.112.130

2、从库 IP:192.168.112.140

3、一个写好的test.sql表


1、主库参数配置

在C盘下打开这个目录(注:programData为隐藏文件!!!)

打开 my.ini 配置文件

C:\ProgramData\MySQL\MySQL Server 5.7   
[mysqld] 
# Binary Logging.
# log-bin          ---把这一行的注释取消,并改为以下
log-bin=binlog

# server ID
Server-id=1
Binlog_format=row    ---添加这一行

打开命令提示符(cmd)

net stop mysql57     ---停止mysql
net start mysql57    ---启动mysql

2、配置从库参数

在C盘下打开这个目录(注:programData为隐藏文件!!!)

打开 my.ini 配置文件

C:\ProgramData\MySQL\MySQL Server 5.7  
[mysqld] 
# Binary Logging
# log-bin
relay-log = relay-bin   ---添加这个

# server ID
server-id = 2  ---改一下ID

打开Data文件,里面有一个 auto.cnf

把UUID的最后一个字母改成其他的,因为我们这个两个主机是复制出来的,所以会出现UUID相同的状况

[auto]server-uuid=bb8a3677-f728-11ea-913a-000c29035395   ---末尾最后一个数字随便改成什么都可以

打开命令提示符运行以下命令

net stop mysql57 停止
net start mysql57 启动

3、回到主库操作

把数据表test.sql文件复制到C盘下面

建立一个数据库名叫 test(以下命令在数据库中进行)

create database test default character set utf8;

然后在命令提示符(cmd)上把我们的数据表文件复制到新建立的 test库中

Mysql -uroot -pXXGC.lab123 test < C:\test.sql

主库创建同步账号(数据库中进行)

create user 'edc'@'%' identified by '123456';
grant replication slave on *.* to 'edc'@'%';

查看主库binlog文件位置

show master logs;

展示日志文件

show binlog events in ‘binlog.000001’

刷新日志文件

Flush logs;

再运行

show master logs

可以看到已经多了一个000002的日志文件


4、回到从库操作

把数据表test.sql文件复制到C盘下面

建立一个数据库名叫 test(以下命令在数据库中进行)

create database test default character set utf8;

然后在命令提示符(cmd)上把我们的数据表文件复制到新建立的 test库中

Mysql -uroot -pXXGC.lab123 test < C:\test.sql

在新建的test数据库里执行此操作

CHANGE MASTER TO MASTER_HOST='192.168.112.130',
MASTER_PORT=3306,
MASTER_USER='edc',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=154;

开启主从复制 并检查状态

Start SLAVE;
Show SLAVE status;

这个时候就可以在主库里面写数据,然后可以看到从库就已经同步了;

5、常见问题:

Slave_SQL_Running: No

1.程序可能在slave上进行了写操作

2.也可能是slave机器重起后,事务回滚造成的.

一般是事务回滚造成的:

解决办法:

mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;