一. 简介
MySQL自带复制方案，带来好处有：
数据备份。
负载均衡。
分布式数据。
概念介绍：
主机（master）：被复制的数据库。
从机（slave）：复制主机数据的数据库。
复制步骤：
(1). master记录更改的明细，存入到二进制日志（binary log）。
(2). master发送同步消息给slave。
(3). slave收到消息后，将master的二进制日志复制到本地的中继日志（relay log）。
(4). slave重现中继日志中的消息，从而改变数据库的数据。
二. 实现复制
实现复制有以下步骤：
1.设置MySQL主库的二进制日志以及server-id
MySQL配置文件一般存放在/etc/my.cnf
<span class="hljs-preprocessor"># 在[mysqld]下面添加配置选项
[mysqld]
server-id=<span class="hljs-number">1
<span class="hljs-built_in">log-bin=mysql-bin.<span class="hljs-built_in">log</span></span></span></span>
server-id是数据库在整个数据库集群中的唯一标示，必须保持唯一。
重启MySQL。
注：如果MySQL配置文件中已经配置过此文件，则可以跳过此步。
2.新建复制账号
在主库里面新建用于从库复制主库数据的账号，并授予复制权限。
<span class="hljs-tag">mysql> <span class="hljs-tag">GRANT <span class="hljs-tag">REPLICATION <span class="hljs-tag">SLAVE, <span class="hljs-tag">REPLICATION <span class="hljs-tag">CLIENT <span class="hljs-tag">ON *.* <span class="hljs-tag">TO <span class="hljs-tag">user_name<span class="hljs-at_rule">@<span class="hljs-keyword">'host' IDENTIFIED BY <span class="hljs-string">'password';</span></span></span></span></span></span></span></span></span></span></span></span>
3.设置MySQL主库server-id
和第二步配置一样，要注意的地方有两点：
- 如果不需要从库作为别的从库的主库的话，则不需要配置二进制日志。
- 很多时候复制并不需要复制主库的全部数据库（特别是mysql的信息配置库）。因此可以配置replicate_do_db来指定复制的数据库
4.从库初始化主库的数据
如果数据量不算大的情况下，可以使用mysqldump工具导出主库数据，然后导入到从库里面。
<span class="hljs-tag">mysqldump <span class="hljs-tag">--single-transaction <span class="hljs-tag">--triggers <span class="hljs-tag">--master-data <span class="hljs-tag">databasename > <span class="hljs-tag">data<span class="hljs-class">.sql</span></span></span></span></span></span></span>
如果数据量大的情况下应该使用Xtrabackup去进行数据库的导出，此处不做介绍。
可能会有同学问，为什么不直接使用二进制日志进行初始化呢？
- 如果我们主库运行了比较长的一段时间，并不太适合使用从库根据二进制日志进行复制数据，直接使用二进制日志去初始化从库会比较耗费时间和性能。
- 更多的情况下，主库的二进制日志的配置项没有打开，因此也就不存在以前操作的二进制日志。
5.开启复制
从库执行下面命令
mysql> CHANGE MASTER TO MASTER_HOST=<span class="hljs-string">'host',
<span class="hljs-function">-> MASTER_USER=<span class="hljs-string">'user',
<span class="hljs-function">-> MASTER_PASSWORD=<span class="hljs-string">'password',
<span class="hljs-function">-> MASTER_LOG_FILE=<span class="hljs-string">'mysql-bin.000001',
<span class="hljs-function">-> MASTER_LOG_POS=<span class="hljs-number">0;</span></span></span></span></span></span></span></span></span>
注意最后的两个命令：MASTER_LOG_FILE和MASTER_LOG_POS，表示从库的从哪个二进制文件开始读取，偏移量从那里开始，这两个参数可以从我们导入的SQL里面找到。
开启复制
<span class="hljs-operator"><span class="hljs-keyword">start <span class="hljs-keyword">slave;</span></span></span>
这时候就完成了复制，在主库更新一个数据或者新增数据在从库都可以查询到结果。
在主库上也可以查询的到复制线程的状态。
三. 复制的日志格式
MySQL复制的日志格式有三种，根据主库存放数据的方式不同有以下三种：
复制方式特点优点缺点 row 基于行的格式复制，记录需要修改的每行的数据信息。 如果一个SQL修改了2w行的数据，那么就会记录2w行的日志格式 保证了数据的强一致性，且由于记录的是执行后的结果，在从库上执行还原也会比较快 日志记录数量很多，主从之间的传输需要更多的时间。 statement 基于段的日志格式复制，也就是记录下更改的SQL记录，而不是更改的行的记录。 日志记录量最小。 对于一些输出结果不确定的函数，在从库上执行一遍很可能会出现问题，如uuid，从库根据日志还原主库数据的时候需要执行一遍SQL，时间相对较慢。 mixed 混合上面两种日志格式记录记录日志，至于什么时候使用哪种日志方式由MySQL本身决定。 可以平衡上面两种日志格式的优缺点。
mysql5.7以前默认使用statement格式。
设置方式，可以在配置文件设置（首选）：
<span class="hljs-setting">binlog_format=<span class="hljs-value">ROW</span></span>
或临时设置全局变量（当前mysql连接有效）：
查看日志格式
mysql > show variables like <span class="hljs-string">'binlog_format';
设置日志格式
mysql > <span class="hljs-built_in">set binlog_format=<span class="hljs-string">'row';</span></span></span>
由于两个主从服务器一般都会放在同一个机房里面，两者之间同步的速度会会比较快，为保证强一致性，应该首选行的日志格式记录（row），保证传输素速度可以选择混合方式（mixed）。
而行的日志格式有下面三种记录方式：
记录方式特点 minimal 只记录被修改列的数据 full 记录被修改的行的全部列的数据 noblob 特点同上，只是如果没有修改blob和text类型的列的情况下，不会记录这些列的数据（也就是大数据列）
mysql默认是full，最好修改成minimal。
<span class="hljs-setting">binlog_row_image=<span class="hljs-value">minimal</span></span>
四. 主从复制延迟
由于主库和从库之间不在同一个主机上，数据同步之间不可以避免地具有延迟，解决的方法有添加缓存，业务层的跳转等待，如果非得从数据库层面去减缓延迟问题，可以从复制时候的三大步骤（主库产生日志，主从传输日志，从库还原日志内容）入手：
1.主库写入到日志的速度
控制主库的事务大小，分割大事务为多个小事务。
如插入20w的数据，改成插入多次5000行（可以利用分页的思路）
2.二进制日志在主从之间传输时间 主从之间尽量在同一个机房或地域。
日志格式改用MIXED，且设置行的日志格式未minimal，原理详见上面的日志格式介绍。
3.减少从库还原日志的时间
在MySQL5.7版本后可以利用逻辑时钟方式分配SQL多线程。
设置逻辑时钟：slave_parallel_type=’logical_clock’;
设置复制线程个数：slave_parallel_workers=4;
五. 需要注意的地方
- 重启MySQL最好切换未MySQL用户再进行操作，不然文件启动后会有权限问题。
- 搭建好MySQL的环境后就设置好配置里的log-bin选项，这样以后如果数据库需要从库的复制，就不需要重启数据库，打断业务的进行。
- 需要打开主库的防火墙的对应的mysql端口。
- 由于从库同步主库的方式，监听主库发送的信息，而不是轮询，因此如果出现通信出现了故障，重新连接后如果主库没有进行数据更改的操作，从库不会同步数据，因此可以通过插入空事务的方式同步数据。
Original: https://www.cnblogs.com/huangama/p/15534317.html
Author: 黄啊码
Title: 【黄啊码】MySQL复制以及调优
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/619689/
转载文章受原作者版权保护。转载请注明原作者出处！