详细讲解mysql主从同步原理、设置以及延迟

SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的数据库均支持SQL。

本文介绍了mysql的主从同步原理、主从同步配置、主从同步延迟，首先我们先来了解什么是主从同步，主从同步，顾名思义也称为主从复制，用来建立一个和主数据库完全一样的数据库环境。主从同步使得数据可以从一个数据库服务器复制到其他服务器上，实现主数据库的数据和从数据库的数据保持一致。

集群是共享存储的,是data-sharing . 主从复制中没有任何共享 . 每台机器都是独立且完整的系统,是nothing-sharing.

主从同步的原理

从mysql5.6之后主从复制的实现方式主要有3种:
1. 异步复制
2. 全同步复制
3. 半同步复制
主从同步原理图

1.当主数据库的更新事件(update、insert、delete)被写到binary-log .

2.从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上 .主库创建一个binlog dump thread线程，把binlog的内容发送到从库 ,从库的I/O线程读取主库的输出线程发送的更新并拷贝这些更新到本地relay log文件中 .

3.从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行 .

主从同步的实现(异步复制,数据库在不同服务器)

1.配置主数据库打开binary-log

vim /etc/my.cnf 在[mysqld]下添加 server-id=1(用来标识不同的数据库)log-bin=master-bin(打开bin-log并配置文件名为master-bin)log-bin-index=master-bin.index(区分不同的log-bin文件)

重启数据库:systemctl restart mariadb.service

2.配置从数据库打开relay-log

vim /etc/my.cnf 在[mysqld]下添加 server-id=2relay-log=slave-relay-bin(打开relay-log并配置文件名为slave-relay-bin) relay-log-index=slave-relay-bin.index

重启数据库:systemctl restart mariadb.service

3.连接两个数据库

在主数据库中:创建用户repl ,每一个从服务器都需要用到主数据库一个账户名和密码来连接主服务器 .

CREATE USER 'repl'@'114.116.77.213' IDENTIFIED BY '12312';GRANT REPLICATION SLAVE ON *.* TO 'repl'@'114.116.77.213' IDENTIFIED BY '12312';

在从数据库中:

change master to master_host='47.106.78.106',master_user='repl',master_password='12312',master_log_file='master-bin.000001',master_log_pos=0;

启动同步:start slave;

4.验证

在主数据库创建一个数据库,然后在从数据库查看

主从同步的作用

1. 做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失 .

2. 读写分离,使数据库能支撑更大的并发 .

主从同步的注意事项

主库可以读写数据,而从库只能读数据,因为当从库写了数据positon会变化,但是主库的position是不会变的,当主库写数据变化position的时候就可能会有冲突.
当主库的binatylog文件存储的数据很多,也就是position很大的时候,会再分裂一个新的binarylog文件,position置为0;
主从库的mysql版本可以不一样,但是从库的mysql版本要比主库的版本要高,如果不是的话,那么主库的语句到了从库可能就不能执行.
因为mysql是向后兼容的,也就是说低版本的语句在高版本里面是支持的,但是高版本的有些语句在低版本是不支持的.

面试相关

（如果问到数据库主从问题，必问以下问题）：

主从的好处是？
主从的原理是？
从数据库的读的延迟问题了解吗？如何解决？
做主从后主服务器挂了怎么办？
主从同步的延迟的原因
主从同步的延迟的原因

主从同步延迟问题

1. 主从同步的延迟的原因

我们知道，一个服务器开放Ｎ个链接给客户端来连接的，　这样有会有大并发的更新操作, 但是从服务器的里面读取binlog 的线程仅有一个，当某个SQL在从服务器上执行的时间稍长或者由于某个SQL要进行锁表就会导致，主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致，也就是主从延迟。

2. 主从同步延迟的解决办法

实际上主从同步延迟根本没有什么一招制敌的办法，因为所有的SQL必须都要在从服务器里面执行一遍，但是主服务器如果不断的有更新操作源源不断的写入，那么一旦有延迟产生，那么延迟加重的可能性就会原来越大。当然我们可以做一些缓解的措施。

a. 我们知道因为主服务器要负责更新操作，他对安全性的要求比从服务器高，所有有些设置可以修改，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog， innodb_flush_log_at_trx_commit 也可以设置为0来提高sql的执行效率这个能很大程度上提高效率。另外就是使用比主库更好的硬件设备作为slave。

b. 就是把，一台从服务器当度作为备份使用，而不提供查询，那边他的负载下来了，执行relay log 里面的SQL效率自然就高了。

c. 增加从服务器喽，这个目的还是分散读的压力，从而降低服务器负载。