MySQL的主从复制

前言

1.在业务复杂的系统中，有这么一个情景，有一句SQL语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，这样业务场景出现的时候我们该如何去解决

什么是主从复制

1.主从复制简单来说是让一台MySQL服务器去复制另一台MySQL的数据，使两个服务器的数据保持一致。主数据库是业务数据库， 从库相当于主库的备份。

主从复制原理MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。MySQL复制是基于主服务器在二进制日志中跟踪所有对数据库的更改。因此，要进行复制，必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器已经记录到日志的数据。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，并在本机上执行相同的更新。然后封锁并等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器，在备份过程中主服务器可以继续处理更新。

总结：

1、MySQL主库在事务提交时把数据变更（insert、delet、update）作为事件日志记录在二进制日志表（binlog）里面。

2、主库上有一个工作线程 binlog dump thread，把binlog的内容发送到从库的中继日志relay log中。

3、从库根据中继日志relay log重做数据变更操作，通过逻辑复制来达到主库和从库的数据一致性。

4、MySQL通过三个线程来完成主从库间的数据复制，其中binlog dump线程跑在主库上，I/O线程和SQL线程跑在从库上。拥有多个从库的主库会为每一个连接到主库的从库创建一个binlog dump线程。

主从复制的好处做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表SQL语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表SQL将不会造成前台锁，保证了前台速度。搭建主从实例1、下载数据库

1、在MySQL官网下载8.0版本的数据库 然后解压缩，分别取名为MySQLMaster和MySQLSlave 如图：

2、配置数据库1、配置MySQLMasterMySQLMaster文件夹下配置my.ini文件，参考配置如下：

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4 
[mysqld]
# 服务器标识ID
server-id=1
#二进制日志文件格式
log-bin=mysql-bin
#binlog日志格式
binlog_format=mixed
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\MySQLMaster
# 设置mysql数据库的数据的存放目录
datadir=D:\MySQLMaster\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

管理员身份运行cmd命令提示符，进入 MySQLMaster文件夹bin目录下，执行命令：

mysqld -install master
#如无意外命令行返回
Service successfully installed.
#主实例安装成功，接着执行初始化命令：
mysqld --initialize --console
# 这个是记住生成的初始密码

启动主数据库

net start master

登录数据库

mysql –uroot –p
#输入生成的临时密码 然后输入临时密码

修改原来的生成的临时密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
flush privileges;

2、安装MySQLSlave从实例MySQLSlave文件夹下配置my.ini文件，参考配置如下：

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4 
[mysqld]
# 服务器标识ID
server-id=2
#二进制日志文件格式
log-bin=mysql-bin
# 从库二进制日志
relay-log=mysql-relay
#binlog日志格式
binlog_format=mixed
#设置3306端口
port = 3307
# 设置mysql的安装目录
basedir=D:\MySQLSlave
# 设置mysql数据库的数据的存放目录
datadir=D:\MySQLSlave\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

接着使用管理员身份运行cmd进入MySQLSlave文件夹bin目录下，执行命令：

mysqld -install slave

安装完成后执行初始化命令：

mysqld --initialize --console

初始化成功后记住临时密码，启动MySQL从实例：

net start slave

登录数据库

mysql –P3307 -uroot –p

登录后修改密码，刷新权限。这些操作上文有提到，不再赘述。3、MySQL主从配置在MySQLMaster下执行命令：

show master status \G

其中file为你当前使用binlog日志，position为当前写入的位置。]接着创建一个用户并授权，用于MySQL主从同步：

create user 'slave'@'127.0.0.1' identified by '123456';
grant all privileges on *.* to 'slave'@'127.0.0.1';

注意，从MySQL 8.0开始，创建用户并授权的语句就被分开了，之前的语句：已经不再支持。

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

在MySQL从实例执行命令，使用主库分配的授权用户连接主库：

change master to master_host='127.0.0.1',
master_user='slave',
master_password='123456',
master_log_file='mysql-bin.000003',
master_log_pos=156;

其中host为主实例地址，user和password为主实例为主从同步创建的用户，log_file为当前写入的binlog日志文件，log_pos为当前写入位置，这个要与上一步的截图保持一致。接着启动slave：

start slave;

输入show status命令验证一下：

验证在MySQLMaster主实例创建数据库myself，创建表newtable，刷新MySQLslave从实例，发现从库同步了myself数据库和newtable数据表。验证成功。如果是读写分离创建一个拥有只读权限的用户用于主从同步。