MySQL ☞ 主从一览编译安装MySQL、my.cnf修改配置、授权新增主从同步用户、change master to

1、主从的搭建

编译安装MySQL、my.cnf修改配置、授权新增主从同步用户、change master to连接主从，基本按照百度搜索都能成功。

注意：如果需要修改某些参数，直接执行set global 重启服务后会失效，如果需要的话需要在my.cnf中修改

2、show processlist

该命令主要查看当前每个连接处于什么状态，在分析DB连接情况时极其好用，状态值如下：

Slepp:    
    线程等待客户端发送新的请求
Query:    
    线程正在执行查询或者正在将结果发送给客户端
Locked:  
    在MySQL服务器层，该线程正在等待表锁（InnoDb的行锁不会体现在这里）
Analyzing and statistics: 
    线程正在收集存储引擎的统计信息，并生成查询的执行计划
Copying to tmp table [on disk]:
    线程正在执行查询，并将结果集都复制到一个临时表内;
    这种状态一般要么在做grouy by操作，要么是文件排序，或者union操作;
    如果带有 on disk，那么表示MySQL正在将一个内存临时表放到磁盘上
Sorting result: 
    线程正在对结果集进行排序
Sending data: 
    线程可能在多个状态之间传输数据、或者在生成结果集、或者在向客户端返回数据

3、主从复制如何工作

参考上图分析，大致分为如下几个步骤：

1、在主库上把数据更改记录到二进制日志中

2、备库将主库上的日志复制到自己的中继日志中

3、备库读取中继日志中的事件，将其重放到备库数据之上

4、binlog、redo log、ondo log是什么

4.1、binlog

假设有如下基础表、数据：

CREATE TABLE `trade` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `trade_id` varchar(20) NOT NULL DEFAULT '' COMMENT '订单号',
  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',
  `product_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品ID',
  `num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '购买数量',
  `price` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '单价',
  `total_price` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '总价',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态',
  `create_time` int(10) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `update_time` int(10) NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE key idx_trade_id (`trade_id`),
  key product_id (`product_id`),  key uid_ctime (`user_id`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

//有如下insert数据：
insert into trade 
(trade_id, user_id, product_id, num, price, total_price, create_time) 
values 
(10000001, 1001, 5005, 10, 100, 1000, 1587882864),
(10000002, 1001, 5006, 10, 100, 1000, 1587882864),
(10000003, 1001, 6005, 20, 100, 2000, 1587882864);

在此之前先说下文件，开启主从同步后，在MySQL的数据存储目录会生成如下文件：

......
mysql-bin-200.000001
mysql-bin-200.000002
mysql-bin-200.index

忽略掉文件中的200，这个是作者指定的一个文件名格式

mysql-bin-200.index中存储的为当前binlog文件的list
mysql-bin-200.000001中存储的为需要同步的binlog数据，内部数据加密，需解密查看，下文有操作
默认可设置每个binlog文件的存储上限，超过上线或者重启服务会生成一个新的连续的binlog文件

4.2、redo log、undo log

redo log、undo log一般用于事务：redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。

redo log 通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

undo log 用来回滚行记录到某个版本。undo log 一般是逻辑日志，根据每行记录进行记录。

简单理解：

undo 记录某数据被修改前的值，可以用来在事务失败时进行 rollback；

redo 记录某数据块修改后的值，可以用来恢复未写入 data file 的已成功事务更新的数据。

redo log 保证事务的持久性

undo log 保证事务的原子性

为什么这么做？

假设某一时刻数据库宕机，存在两个事务，事务A已提交，事务B正在处理。数据库在重启时就需要进行数据前滚或回滚。将已提交的事务A前滚写入磁盘，未提交的事务B回滚到操作前的数据。

关于事务的详细介绍，我们会在另外一篇博客中介绍：juejin.cn/post/684490…

5、同步模式(binlog_format)是什么

binlog_format是主从同步的几种模式，目前主要分为3种，可以直接通过命令查看：

show global variables like "%binlog_format%";

5.1、STATEMENT

基于语句复制，简单而言，主库执行的所有sql都同步到binlog中，从库再执行一遍。

优点：简单、不会占用过多带宽，一条sql语句日志最多也就占几十字节、继而binlog也较小；除了用于复制还可以用于数据还原；支持不同版本的主从。

缺点：INSERT ... SELECT 会产生更多的行锁；不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。

假设需要执行两条SQL：
insert into trade 
(trade_id, user_id, product_id, num, price, total_price, create_time) 
values 
(10000001, 1001, 5005, 10, 100, 1000, 1587882864),
(10000002, 1001, 5006, 10, 100, 1000, 1587882864),
(10000003, 1001, 6005, 20, 100, 2000, 1587882864);

update trade set status = 1;

../bin/mysqlbinlog --base64-output=decode-rows -v  mysql-bin-200.000004查看binlog
内部数据较多，比较清晰的是能看到 binlog存储的是主库执行的sql

5.2、ROW

基于行复制，即将sql需要更新的数据转换为多条执行语句进行执行，此处感兴趣的同学可以解密一下binlog日志，可以发现二进制日志文件中记录的是实际上在主库上发生了变化的数据。

优点：几乎没有ROW模式无法解决的场景；因为不要求强串行，可以减少锁的使用。也是使用最多的模式。

缺点：由于记录的是每条数据的操作，binlog文件增长会很快，产生的大 BLOB 值会导致复制变慢；且无法从 binlog 中看到都复制了写什么语句

同上，我们本次执行三条SQL

insert into trade 
(trade_id, user_id, product_id, num, price, total_price, create_time) 
values 
(10000001, 1001, 5005, 10, 100, 1000, 1587882864),
(10000003, 1001, 6005, 20, 100, 2000, 1587882864);
先看下insert语句的binlog是什么样子的，可以发现insert几条数据出现了几次insert操作。

我们再看下update
update trade set status = 1;
和insert类似，多条操作多条记录

我们再看下delete
delete from trade where user_id = 1001;

5.3、MIXED

以上两种方式的混合。未在生产环境实践应用过、不做评论。

试验了下，查看binlog，大部分情况还是和row模式差不多的。

6、常见的主从模型

6.1、一主多从

最常见的架构模型，一从同理；但一般成型的架构中，都会多一层Proxy来进行处理。

把其中一台从库当做待用的主库、除了复制没有其他任何传输

将一台从库放到远程数据中心、用作灾难恢复

使用其中一个备库，作为培训、开发、测试专用服务器

6.2、主主复制 - 主动主动模式

每台机器都被配置为对方的主库和备库，即主动-主动模式下的主主复制。不过使用这种架构的业务很少很少。应该是一种要被淘汰的架构了。

场景：两个不同位置的办公室，并且都需要一份可用的数据拷贝

但是很容易出现的问题就是：两台主库同时修改了同一条记录，这样是很容易造成数据不一致的。

虽然可以通过设置不同的步长 auto_increment_offset、auto_increment_increment 来进行优化，但还是无法从根源上解决，可能还会出现数据不一致的问题。

例：互为主主的MySQL架构中，假设某表只有一行一列数据，其值 col = 1, 如果两个服务器分别同时执行如下语句：

A主：update table set col = col + 1;
B主：update table set col = col * 2;

那么结果是多少呢？为什么？

A主：最终结果为4；    //先执行 + 1，后执行 * 2
B主：最终结果为3；    //先执行 * 2，后执行 + 1

6.3、主主复制 - 主动被动模式

避免了M-M的缺点，实际上，这是一种具有容错和高可用性的系统。它的不同点在于其中一个服务只能进行只读操作

主要作用：类似热备份，但是还可以执行读操作、备份、离线维护、升级等

7、主从的几个常见参数

7.1、max_allowed_packet

代表 Server 接收的数据包大小。有时候大的插入和更新会受 max_allowed_packet 参数限制，如果该值过小，会导致大数据写入或者更新失败。

建议根据实际需求调整该值，如果涉及一次性大批量的insert操作建议放大该值，但还是最好避免大批量的insert，很容易出现主从延迟。

7.2、sync_binlog

控制数据库的binlog是否刷到磁盘上去。

sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失

sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1。

对于高并发事务的系统来说，sync_binlog 设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

所以很多DBA设置的sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。顺便看了下我们的某业务配置，备库设置为1000。

7.3、innodb_flush_log_at_trx_commit

=0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。

=1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘中)

=2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush操作。

7.4、wait_timeout

连接自动释放的时间。默认为8小时，也就是说一个连接sleep超过8个小时，MySQL将自动断开该连接

如果该值过大容易导致连接无法及时释放，拖累系统性能。不过也不能设置的过小，不然会遇到 MySQL has gone away 等类问题，通常来说设置为 10 就不错。但还是会有一种情况：比如你在执行一个 cron 脚本，两次查询的间隔超过了10S，那么就会有问题了，所以需要在程序中定期的 mysql_ping 一下，确保连接不自动断开。

8、常见报错

8.1、MySQL server has gone away

指client和MySQL server之间的链接断开了，原因如下：

1、一般是sql操作的时间过长，或者是传送的数据太大(如果是insert ... values的语句过长，可通过修改max_allowed_packed的参数来)。

2、服务宕机。

3、连接被kill。

4、达到 wait_timeout 上限，连接被强行关闭，如果关闭后再尝试连接就会出现此类错误。

8.2、Lost connection to MySQL server during query

顾名思义，连接丢失了，原因一般如下：

1、查询中大量数据被发送，由于数据传输时间不够导致，可以增加net_read_timeout的值。

2、初次连接时，连接时间设定太少，可以增加connect_timeout的值改善。

3、有些少见的情况可以查看 show global status like 'aborted_connets'，这个全局变量在每一次服务器终止时会增加1，查看"reading authorization packet"获取错误信息。

4、BLOB值太大的问题，调整配置文件max_allowed_packet

9、主从延迟怎么来的

主从延迟对业务造成的影响是非常的，那么为什么会有主从延迟呢？大致有如下几种情况：

1、主从机器性能不一样，比如主库配置高，从库配置低（roundRobin负载同样可能存在此问题）

2、主库在多线程的写，从库在单线程的同步，MySQL 5.7 已经支持了多线程的同步

3、主库binlog文件有大事务或者大更新，从库一直在等待commit

10、主从异常

10.1、主从服务异常

主从服务异常的常见原因大致如下：

1、异常断电，服务问题

2、在主从正常的情况下调整了某些会影响数据同步的配置

3、数据包超出了最大可接收范围

10.2、异常断开后如何接上

查看上次异常断开的偏移位置，停止主从后以新的偏移位置进行连接，然后启动同步。

在重连过程中可能会遇到下面问题：

1、1153错误码：主从max_allowed_packet配置不一致

2、1062错误码：主键冲突（主从binlog文件恢复位置节点靠前/从库脱离主进行过insert操作）（有一种手动处理办法：slave-skip-errors参数）

3、1032：数据不存在（主从binlog文件恢复位置节点靠后/主从数据已经存在）