mysql配置主从服务器

Mysql主从数据库

考虑到在实际业务中，在访问量不断增大后，单个数据库难以满足大批量的增删改查任务，数据库负担过重，就有可能宕机，造成数据丢失的灾难。为了防止这个问题，Mysql有主从复制的功能，可以帮助我们实现负载均衡和读写分离。本项目可以设置两台数据库服务器，一台主数据库（master）负责写，一台从数据库（slave）负责读，然后保证它们的数据是同步的，这样便可以大大减小数据库服务器的压力。下面详细介绍一下。

主从数据库的优点

• 高可用，实时灾备，用于故障切换。比如主库挂了，可以切从库。
• 读写分离，提供查询服务，减少主库压力，提升性能
• 备份数据，避免影响业务。

主从复制的原理

主数据库写入数据后，需要同步给从数据库，这样用户从从数据库读到的数据才是正确的数据，如何实现呢？

如上图所示:

① 当Master节点进行insert、update、delete操作时，会按顺序写入到binlog中。

② salve从库连接master主库，Master有多少个slave就会创建多少个binlog dump线程。

③ 当Master节点的binlog发生变化时，binlog dump 线程会通知所有的salve节点，并将相应的binlog内容推送给slave节点。

④ I/O线程接收到 binlog 内容后，将内容写入到本地的 relay-log。

⑤ SQL线程读取I/O线程写入的relay-log，并且根据 relay-log 的内容对从数据库做对应的操作。

主库和从库维持了一个长链接，主库内部的线程专门服务于主库和从库的长链接。

binlog格式

binlog是一个二进制日志，记录了主数据库更改数据的操作，以提供给从数据库让其做相同的操作。

binlog有三种格式，分别是statement，row和mixed。

statement格式：记录的是SQL的原文，主从数据库复制是语句复制，如果主库和从库选的索引不一致，可能会导致主库不一致。假设主库执行删除这个SQL（其中a和create_time都有索引）如下：

delete from t where a > '666' and create_time<'2022-03-01' limit 1;

我们知道，数据库选择了a索引和选择create_time索引，最后limit 1出来的数据一般是不一样的。所以就会存在这种情况：在binlog = statement格式时，主库在执行这条SQL时，使用的是索引a，而从库在执行这条SQL时，使用了索引create_time。最后主从数据不一致了。因此，在开发中，我们应该尽量将业务逻辑放在代码层，而不是Mysql中。

这种方式的优点是简单，二进制文件小，传输带宽占用小。

row格式：记录的是基于行的复制相当于物理复制，即二进制日志中记录的实际更新数据的每一行，主从数据库复制是行复制。例如主数据删除了一批满足特定条件的数据，binlog记录的不是SQL原文，是两个event:Table_map 和 Delete_rows。Table_map event说明要操作的表，Delete_rows event用于定义要删除的行为，记录删除的具体行数。row格式的binlog记录的就是要删除的主键ID信息，因此不会出现主从不一致的问题。

这样导致复制的压力比较大，日志占用的空间大，传输带宽占用大。但是这种方式比基于语句的复制要更加精确。例如一条更新用户总积分的语句，需要统计用户的所有积分再写入用户表。如果是基于语句复制的话，从库需要再一次统计用户的积分，而基于行复制就直接更新记录，无需再统计用户积分。

上述两种方式各有特点，因此有第三种格式：mixed，即将上述两种格式混合起来。一般情况下，默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

主从复制的延迟原因和问题

主从延迟是怎么定义的呢？与主从数据同步相关的时间点有三个

• 主库执行完一个事务，写入binlog，我们把这个时刻记为T1；
• 主库同步数据给从库，从库接收完这个binlog的时刻，记录为T2；
• 从库执行完这个事务，这个时刻记录为T3。

所谓主从延迟，其实就是指同一个事务，在从库执行完的时间和在主库执行完的时间差值，即T3-T1。

• 如果从库所在的机器比主库的机器性能差，会导致主从延迟，这种情况比较好解决，只需选择主从库一样规格的机器就好。
• 如果从库的压力大，也会导致主从延迟。比如主库直接影响业务的，大家可能使用会比较克制，因此一般查询都打到从库了，结果导致从库查询消耗大量CPU，影响同步速度，最后导致主从延迟。这种情况的话，可以搞了一主多从的架构，即多接几个从库分摊读的压力。另外，还可以把binlog接入到Hadoop这类系统，让它们提供查询的能力。
• 大事务也会导致主从延迟。如果一个事务执行就要10分钟，那么主库执行完后，给到从库执行，最后这个事务可能就会导致从库延迟10分钟啦。日常开发中，我们为什么特别强调，不要一次性delete太多SQL，需要分批进行，其实也是为了避免大事务。另外，大表的DDL语句，也会导致大事务，大家日常开发关注一下哈。
• 网络延迟也会导致主从延迟，这种情况你只能优化你的网络啦，比如带宽20M升级到100M类似意思等。
• 如果从数据库过多也会导致主从延迟，因此要避免复制的从节点数量过多。从库数据一般以3-5个为宜。
• 低版本的MySQL只支持单线程复制，如果主库并发高，来不及传送到从库，就会导致延迟。可以换用更高版本的Mysql，可以支持多线程复制。

主从数据库的实现

首先主从数据库需要两台以上服务器，一般学习的话可以开两台虚拟机，不过我电脑跑不动，租两个云服务器也可以。

两台服务器环境

1. master:腾讯云服务器，IP：43.138.57.206

2. slave:阿里云服务器，IP：47.115.202.126

   两台服务器需要安装相同版本的mysql，这里的版本是5.7.25

配置master

1. 修改Mysql数据库的配置文件/etc/my.cnf，在[mysqld]下添加如下两行：

   log-bin=mysql-bin #启用二进制日志

   server-id=100 #服务器ID，数字不固定

2. 重启Mysql服务 systemctl restart mysqld

3. 登录Mysql数据库，执行下面的SQL语句

   GRANT REPLICATION SLAVE ON *.* to 'xiaoming'@'%' identified by 'Root@123456';

   作用是创建一个用户xiaoming，密码为**Root@123456，**并且给xiaoming用户授予REPLICATION SLAVE权限。常用于建立复制时所需要用到的用户权限，也就是slave必须被master授权具有该权限的用户，才能通过该用户复制。

4. 登录数据库，执行下面SQL语句：

   show master status;

   

   稍后在配置从数据库时，会用到查询出来的File和Position信息

配置slave

1. 修改Mysql数据库的配置文件/etc/my.cnf

   server-id=101 #服务器唯一id

2. 重启Mysql服务

   systemctl restart mysqld

3. 登录MySQL数据库，执行下面的SQL：

   change master to masterhost='43.138.57.206',masterport=3306,masteruser='xiaoming',masterpassword='Root@123456',master_logfile='mysql-bin.000002',master_log_pos=2061;

   上述语句中的各项参数根据自己的环境设置

4. 启动slave start slave;

5. 执行下面的SQL，查看从数据库的状态

   show slave status;

   

查询结果中上面两项都是Yes即说明设置成功。

成功后，可以在NaviCat上给主数据库新增一个表，看这个表从数据库是否也同步有。