MySQL主从复制原理与实操

忘是亡心i 2022-03-27 12:38 289阅读 0赞

## 一、引言 ##

在工作中，对数据库的数据进行备份是毫无疑问的事情，而数据库的备份方式又分好多种（此处就不做介绍），最基本的就是一主一从，从机需要不断的读取主机的数据二进制日志记录来备份主机的数据，这是数据库主从复制的第一个应用场景。主从复制的第二个应用场景就是在分布式的系统中，一个数据库肯定是不行的，这就需要分多个库，库与库之间也需要做数据的同步。So，下面就来实操一下数据库的主从复制。

## 二、环境搭建 ##

为了模拟数据库的主从复制，需要搭建一个主数据库和一个从数据库，从数据库需要备份主机的数据。本文的主数据库是在Win7下的MySQL，从数据库放在虚拟机中，使用MySQL的Docker镜像，很方便，但是有需要注意的地方。重点需要注意：主从数据库的版本要求一致。

###### 1、启动Docker镜像注意事项 ######

###### （1）、在Docker所在的宿主机上创建MySQL的两个文件夹：config和db，在config下创建my.cnf，命令如下： ######

cd / 
    mkdir docker/mysql 
    cd docker/mysql 
    mkdir config 
    mkdir db

my.cnf内容：

[mysqld]
    user=mysql

###### （2）、挂载volume卷 ######

因为要对从数据库进行一些配置，这就需要修改从机的配置文件。为了方便修改MySQL镜像中的MySQL配置文件，我们要将镜像里的MySQL配置文件与宿主机里的MySQL配置文件做绑定，这样我们只要改宿主机中的MySQL配置文件，镜像里的MySQL就可以生效了。使用如下命令：

docker run -p 3306:3306 --name mysql -v=/docker/mysql/config/my.cnf:/etc/my.cnf 
    -v=/docker/mysql/data:/var/lib/mysql
    -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.24

###### （3）、在主从数据库中，分别创建用户（用root用户不行） ######

创建用户命令：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
    
    //创建一个pig用户，密码为123456，运行任意ip地址访问
    eg：CREATE USER 'pig'@'%' IDENTIFIED BY '123456';

授权命令：

GRANT privileges ON databasename.tablename TO 'username'@'host'
    
    //为pig用户授予所有操作权限
    eg：GRANT ALL ON *.* TO 'pig'@'%';

## 三、主从复制基本原理和实战配置： ##

###### 1、主从复制的基本原理 ######

slave 会从 master 读取 binlog 来进行数据同步，原理图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70]

###### （1）、MySQL复制过程分成三步骤： ######

第一步：

master 将改变记录到二进制日志文件（binary log），这些记录过程叫做二进制日志事件，binary log events；

第二步：

slave 将 master 的 binary log events 拷贝到它的中继日志（relay log）；

第三步：

slave 重做中继日志中的事件，将改变应用到自己的数据库中，MySQL复制是异步的且串行化的；

###### 2、主从复制的的基本原则 ######

（1）、每个 slave 只有一个 master  
（2）、每个 slave 只能有一个唯一的服务器ID  
（3）、每个 master 可以有多个slave

###### 3、配置的基本步骤（☆☆☆☆☆） ######

###### （1）、前提说明 ######

i.	MySQL 版本一致且后台服务运行；
    
    ii.	主从都配置在 [mysqld] 结点下，且都是小写；

###### （2）、主机修改 my.ini 配置文件（主机为Windows下的MySQL） ######

i.	主服务器唯一ID（必须）：service-id=1；
    
    ii.	启用二进制日志（必须）：
    	log-bin=自己本地的路径/mysqlbin
    	eg：log-bin=D:/mysql/mysql-5.7.24-winx64/data/mysqlbin
    	
    iii.启用错误日志（可选）：
    	log-err=自己本地的路径/mysqlerr
    	eg：log-err=D:/mysql/mysql-5.7.24-winx64/data/mysqlerr
    	
    iv.根目录（可选）：
    	basedir=”自己本地路径”
    	eg：basedir=D:/mysql/mysql-5.7.24-winx64
    	
    v.临时目录（可选）：
    	tmpdir=”自己本地路径”
    	eg：tmpdir=D:/mysql/mysql-5.7.24-winx64
    	
    vi.数据目录（可选）：
    	datadir=”自己本地路径/Data/”
    	eg：datadir=D:/mysql/mysql-5.7.24-winx64/data
    	
    vii.read-only=0：主机，读写都可以
    
    viii.设置不要复制的数据库（可选）：
    	binlog-ignore-db=mysql
    	
    ix.设置需要复制的数据库（可选）：
    	biglog-do-db=需要复制的主数据库名字

###### （3）、从机修改 my.cnf 配置文件 ######

i.从机服务器唯一ID（必须）：service-id=2；
    
    ii.启用二进制日志（可选）
    	log-bin=mysql-bin

###### （4）、因修改过配置文件，主机和从机都需要重启后台mysql服务 ######

###### （5）、主机从机都关闭防火墙 ######

i.Windows手动关闭；
    
    ii.Linux关闭虚拟机防火墙：systemctl stop firewalld:（不同系统版本，命令有差别）

###### （6）、在 windows 主机上建立账户并授权 slave； ######

i.授权：grant replication slave on *.* to 'pig@'192.168.1.106' identified by '123456';
    
    ii.刷新：flush privileges;
    
    iii.查询master的状态，并记录下 File 和 Position 的值；（从数据库需要从这个位置开始复制数据！！！！！！）
    
    iv.show master status

![在这里插入图片描述][20190113184053178.png]  
注意：执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态变化；

###### （7）、在Linux从机上配置需要复制的主机； ######

i.授权：
    
    	change master to master_host='192.168.1.104',
    	master_user='scorpio',
    	master_password='123456',
    	master_log_file='mysqlbin.000002',
    	master_log_pos=2417;
    
    ii.启动从服务器复制功能：start slave;
    
    iii.show slave status\G;
    
    iv.下面两个参数都是yes，则说明主从配置成功！
    	Slave_IO_Running:Yes
    	Slave_SQL_Running:Yes

参数说明：  
master\_host=‘主机Ip’：主机的IP  
master\_user=‘用户名’：主机数据库用户名  
master\_password=‘123456’：密码  
master\_log\_file=‘File名字’：上文用show master status命令查出来的File值  
master\_log\_pos=position数字：上文用show master status命令查出来的Position值  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 1]

###### （8）、主机新建库、新建表、insert记录，从机复制； ######

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 3]

###### （9）、如何停止从机的复制功能； ######

stop slave;

## 四、小结 ##

###### 1、在搭建环境时需要注意： ######

（1）、主从MySQL的大版本要一致，比如5.7的，要统一；  
（2）、主从数据库的账号不能用root权限，不然在从服务器中用show slave status命令查看状态时，会一直连接不上主数据库；  
（3）、Docker中的MySQL镜像的配置文件，要和宿主机中的配置文件绑定；  
（4）、主从复制的配置流程，就如上文所述。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70]: /images/20220327/0dfcdd661c9b40c7b28bacd8a1da82a4.png
[20190113184053178.png]: /images/20220327/19b549ca65cb49dd8a4a53e6dcbbc8c3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 1]: /images/20220327/3e0b5bb7e80f46b6bf4c187a9a2a62cc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 2]: /images/20220327/5217a90669d74398a21010ce6333be38.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 3]: /images/20220327/42be1dfd80cf49dc81a49e1010488121.png