前言:

在企业应用中,成熟的业务通常数据量都比较大

单台MySQL在安全性、 高可用性和高并发方面都无法满足实际的需求

配置多台主从数据库服务器以实现读写分离

一、mysql主从复制原理

1、mysql的复制类型

 基于语句的复制

 基于行的复制

 混合类型的复制

2、mysql主从复制的工作原理

 主服务器 master 记录数据库通过 dump 线程将操作记录到 Binary log

 从服务器开启 I/O 线程向主服务器发送同步日志请求

 主服务器把二进制日志内容发送给从服务器

 从服务器将二进制日志记录的操作同步到relay log (中继日志) (存在从服务器的缓存中)

 从服务器中的sql线程将relay log日志记录的操作在从服务器执行后写入从服务器数据库。

Mysql 主从复制_数据

1、首先client端(tomcat)将数据写入到master节点的数据库中,master节点会通知存储引擎提交事务,同时会将数据以(基于行、基于sql、基于混合)的方式保存在二进制日志钟

2、SLAVE节点会开启I/O线程,用于监听master的二进制日志的更新,一旦发生更新内容,则向master的dump线程发出同步请求

3、master的dump线程在接收到SLAVE的I/O请求后,会读取二进制文件中更新的数据,并发送给SLAVE的I/O线程

4、SLAVE的I/O线程接收到数据后,会保存在SLAVE节点的中继日志中

5、同时,SLAVE节点钟的SQL线程,会读取中继日志钟的熟,更新在本地的mysql数据库中

6、最终,完成slave——>复制master数据,达到主从同步的效果

二、主从复制实验

环境准备

mysql-master 192.168.126.40

mysql-slave1 192.168.126.60

mysql-slave2 192.168.126.70

 

1、主从服务器时间同步

master服务器配置

① 安装ntp、修改配置文件

[root@master ~]# yum -y install ntpdate ntp      #安装ntp软件

[root@master ~]# ntpdate ntp.aliyun.com        #时间同步

[root@master ~]# vi /etc/ntp.conf              #编辑配置文件

 

fudge 192.168.126.40 stratum 10

#设置本机的时间层级为10级,0级表示时间层级为0级,是向其他服务器提供时间同步源的意思,不要设置为0级(限制在15内)

server 192.168.126.40    #设置本机为时间同步源

Mysql 主从复制_mysql_02

Mysql 主从复制_mysql_03

② 开启NTP服务、关闭防火墙和增强性安全功能

[root@master ~]# systemctl start ntpd

[root@master ~]# systemctl stop firewalld.service

[root@master ~]# setenforce 0

Mysql 主从复制_服务器_04

两台SLAVE服务器配置

① 安装ntp、ntpdate服务

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

② 开启ntp服务,关闭防火墙、增强性安全功能

[root@localhost ~]# systemctl start ntpd

[root@localhost ~]# systemctl stop firewalld.service

[root@localhost ~]# setenforce 0

Mysql 主从复制_服务器_05

③ 时间同步master服务器

[root@localhost ~]# ntpdate 192.168.226.129

Mysql 主从复制_数据_06

两台slave服务器配置相同

 

#master服务器同步阿里云时钟服务器

ntpdate ntp.aliyun.com

 

添加计划任务

crontable -e

*/10 * * * * /usr/sbin/ntpdate 192.168.126.40

 

2、配置主从同步

① master服务器修改配置文件

[root@master ~]# vi /etc/my.cnf

#在mysqld模块下修改一下内容

#开启二进制日志文件(之后生成的日志名为master-bin)

log_bin=master-bin

#开启从服务器日志同步

log_slave-updates=true

#主服务器id为1(不可重复)

server_id = 1

Mysql 主从复制_数据_07

重启服务systemctl restart mysqld.service

 

配置规则

  进入数据库,授权

mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.126.%' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.00 sec)

给从服务器提权,允许使用slave的身份复制master的所有数据库的所有表,并指定密码为123456

 

#刷新权限表

mysql> flush privileges;

Mysql 主从复制_mysql_08

查看master数据库状态

mysql> show master status;

Mysql 主从复制_服务器_09

以上可见产生了master-bin.000001日志文件,定位为604

从服务器需要定位到此处进行复制

 

3 、从服务器配置

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

log-bin=master-bin  #开启二进制日志文件

server_id = 2      #设置server id为2,slave2 为3

relay-log=relay-log-bin   #从主服务器上同步日志文件记录到本地

relay-log-index=slave-relay-bin.index  #定义relay-log的位置和名称(index索引)

Mysql 主从复制_数据_10

Mysql 主从复制_mysql_11

开启从服务器功能

[root@slave1 ~]# mysql -uroot -p123456

mysql> change master to master_host='192.168.226.129',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;

mysql> start slave;

Mysql 主从复制_mysql_12

查看从服务器状态

mysql> show slave status\G;

Mysql 主从复制_mysql_13

同理、开启另一台从服务器同步

 

4、测试

在主服务器上创建一个数据库

Mysql 主从复制_服务器_14

在从服务器查看

Mysql 主从复制_服务器_15

小结:

简单介绍一下二进制日志:

MySQL的二进制日志是一个二进制文件,主要用于记录修改数据或有可能引起数据变更的SQL语句

二进制日志中记录了对MySQL数据库执行更改的所有操作,并且记录了语句发生时间、执行时长、操作数据等其他额外信息,但是它不记录SELECT 、SHOW等不修改数据的SQL语句。

二进制日志主要用于数据库恢复(⭐⭐)和主从复制及审计操作。

 

PS:如果同步失败可使用以下方法尝试解决

#忽略当前的错误执行下一步的同步

先 stop slave;

① slave数据库中:SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

问题:I/O线程 一直处于connecting

第一件事,看laster error,报错项内容是为支点找不到

先 stop slave;

② slave数据库中:

CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000001’,MASTER_LOG_POS=0;

 

首先遇到这个是因为binlog位置索引处的问题,生产环境下不要直接reset slave(删除change master 操作的);

 

reset slave会将主从同步的文件以及位置恢复到初始状态,一开始没有数据还好,有数据的话,相当于重新开始同步,可能会出现一些问题;

 

一般做主从同步,都是要求以后的数据实现主从同步,而对于旧的数据完全可以使用数据库同步工具先将数据库同步,完了再进行主从同步;

 

好了遇到上面的问题,正确做法是:

 

1.打开主(master)服务器,进入mysql

 

2.执行flush logs;//这时主服务器会重新创建一个binlog文件;

 

3.在主服务上执行show master status\G;显示如下:

 

4.来到从服务器的mysql;

 

5.stop slave;

 

6.change master to master_log_file='mysql-bin.000012',master_log_pos=154;//这里的file和pos都是上面主服务器master显示的。

 

7.start slave; //这时候就应可以了