mysql1
一、MySQL异步复制介绍
1. 复制的用途
2. 复制如何工作
3. 两阶段提交
二、复制实验环境
三、安装mysql-8.0.16
四、配置异步复制
1. 空库
2. 脱机
3. 联机
一、MySQL异步复制介绍
简单说，复制就是将来自一个MySQL数据库服务器（主库）的数据复制到一个或多个MySQL数据库服务器（从库）。传统的MySQL复制提供了一种简单的Primary-Secondary复制方法，默认情况下，复制是单向异步的。MySQL支持两种复制方式：基于行的复制和基于语句的复制。这两种方式都是通过在主库上记录二进制日志（binlog）、在从库重放中继日志（relylog）的方式来实现异步的数据复制。二进制日志或中继日志中的记录被称为事件。所谓异步包含两层含义，一是主库的二进制日志写入与将其发送到从库是异步进行的，二是从库获取与重放日志事件是异步进行的。这意味着，在同一时间点从库上的数据更新可能落后于主库，并且无法保证主从之间的延迟间隔。
复制给主库增加的开销主要体现在启用二进制日志带来的I/O，但是开销并不大，MySQL官方文档中称开启二进制日志会产生1%的性能损耗。出于对历史事务备份以及从介质失败中恢复的目的，这点开销是非常必要的。除此之外，每个从库也会对主库产生一些负载，例如网络和I/O开销。当从库读取主库的二进制日志时，可能会造成一定的I/O开销。如果从一个主库上复制到多个从库，唤醒多个复制线程发送二进制日志内容的开销将会累加。但所有这些复制带来的额外开销相对于应用对MySQL服务器造成的高负载来说是很小的。
1. 复制的用途
（1）横向扩展
通过复制可以将读操作指向从库来获得更好的读扩展。所有写入和更新都在主库上进行，但读取可能发生在一个或多个从库上。在这种读写分离模型中，主库专用于更新，显然比同时进行读写操作会有更好的写性能。需要注意的是，对于写操作并不适合通过复制来扩展。在一主多从架构中，写操作会被执行多次，这时整个系统的写性能取决于写入最慢的那部分。
（2）负载均衡
通过MySQL复制可以将读操作分不到多个服务器上，实现对读密集型应用的优化。对于小规模的应用，可以简单地对机器名做硬编码或者使用DNS轮询（将一个机器名指向多个IP地址）。当然也可以使用复杂的方法，例如使用LVS网络负载均衡器等，能够很好地将负载分配到不同的MySQL服务器上。
（3）提高数据安全性
提高数据安全性可以从两方面来理解。其一，因为数据被复制到从库，并且从库可以暂停复制过程，所以可以在从库上运行备份服务而不会影响相应的主库。其二，当主库出现问题，还有从库的数据可以被访问。但是，对备份来说，复制仅是一项有意义的技术补充，它既不是备份也不能够取代备份。例如，当用户误删除一个表，而且此操作已经在从库上被复制执行，这种情况下只能用备份来恢复。
（4）提高高可用性
复制能够帮助应用程序避免MySQL单点失败，一个包含复制的设计良好的故障切换系统能够显著缩短宕机时间。
（5）滚动升级
比较普遍的做法是，使用一个高版本MySQL作为从库，保证在升级全部实例前，查询能够在从库上按照预期执行。测试没有问题后，将高版本的MySQL切换为主库，并将应用连接至该主库，然后重新搭建高版本的从库。
2. 复制如何工作
如前所述，MySQL复制依赖二进制日志，所以要理解复制如何工作，先要了解MySQL的二进制日志。
（1）二进制日志
二进制日志包含描述数据库更改的事件，如建表操作或对表数据的更改等。开启二进制日志有两个重要目的：
用于复制。主库上的二进制日志提供要发送到从库的数据更改记录。主库将其二进制日志中包含的事件发送到从库，从库执行这些事件以对主服务器上的数据进行相同的更改。
用于恢复。当出现介质错误（如磁盘故障）时，数据恢复操作需要使用二进制日志。还原备份后，将重新执行备份后记录的二进制日志中的事件。
不难看出，MySQL二进制日志所起的作用与Oracle的归档日志类似。二进制日志只记录更新数据的事件，不用于SELECT或SHOW等语句。通过设置log-bin系统变量开启二进制日志，MySQL 8中缺省是开启的。
二进制日志有STATEMENT、ROW、MIXED三种格式，通过binlog-format系统变量设置：
STATMENT格式，基于SQL语句的复制（statement-based replication，SBR）。每一条会修改数据的SQL语句会记录到binlog中。这种格式的优点是不需要记录每行的数据变化，这样二进制日志会比较少，减少磁盘I/O，提高性能。缺点是在某些情况下会导致主库与从库中的数据不一致，例如last_insert_id()、now()等非确定性函数，以及用户自定义函数（user-defined functions，udf）等易出现问题。
ROW格式，基于行的复制（row-based replication，RBR）。不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子，能清楚记录每一行数据的修改细节。其优点是不会出现某些特定情况下的存储过程、函数或触发器的调用和触发无法被正确复制的问题。缺点是通常会产生大量的日志，尤其像大表上执行alter table操作时候会让日志暴涨。
MIXED格式，混合复制（mixed-based replication，MBR）。是语句和行两种格式的混合使用，默认使用STATEMENT模式保存二进制日志，对于STATEMENT模式无法正确复制的操作，会自动切换到基于行的格式，MySQL会根据执行的SQL语句选择日志保存方式。
MySQL 8缺省使用ROW格式。二进制日志的存放位置最好设置到与MySQL数据目录不同的磁盘分区，以降低磁盘I/O的竞争，提升性能，并且在数据磁盘故障的时候还可以利用备份和二进制日志恢复数据。
（2）复制步骤
总的来说，MySQL复制有五个步骤：
在主库上把数据更改事件记录到二进制日志中。
从库上的I/O线程向主库询问二进制日志中的事件。
主库上的binlog dump线程向I/O线程发送二进制事件。
从库上的I/O线程将二进制日志事件复制到自己的中继日志中。
从库上的SQL线程读取中继日志中的事件，并将其重放到从库上。
图1更详细地描述了复制的细节。
图1 复制如何工作
第一步是在主库上记录二进制日志。在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。MySQL会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。
下一步，从库将主库的二进制日志复制到其本地的中继日志中。首先，从库会启动一个工作线程，称为I/O线程，I/O线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制日志转储（binlog dump）线程，它会读取主库上二进制日志中的事件，但不会对事件进行轮询。如果该线程追赶上了主库，它将进入睡眠状态，直到主库发送信号通知其有新的事件时才会被唤醒，从库I/O线程会将接收到的事件记录到中继日志中。
从库的SQL线程执行最后一步，该线程从中继日志中读取事件并在从库上执行，从而实现从库数据的更新。当SQL线程追赶I/O线程时，中继日志通常已经在系统缓存中，所以重放中继日志的开销很低。SQL线程执行的事件也可以通过log_slave_updates系统变量来决定是否写入其自己的二进制日志中，这可以用于级联复制的场景。
这种复制架构实现了获取事件和重放事件的解耦，允许这两个过程异步进行。也就是说I/O线程能够独立于SQL线程之外工作。但这种架构也限制了复制的过程，其中最重要的一点是在主库上并发更新的查询在从库上通常只能串行化执行，因为缺省只有一个SQL线程来重放中继日志中的事件。在MySQL 5.6以后已经可以通过配置slave_parallel_workers等系统变量进行并行复制，在后面讨论与复制相关的性能问题时会介绍并行复制的相关细节。
现在我们已经了解了MySQL复制是以二进制日志为基础的，但是像Innodb这样的事务引擎有自己的事务日志，如ib_logfile，这些事务日志通常被称为重做日志（redo log）。作为背景知识，简单介绍下Innodb重做日志的作用。
对Innodb的任何修改操作都会首先在称为缓冲池（innodb buffer pool）的内存页面上进行，然后这样的页面将被标记为脏页，并被放到专门的刷新列表上，后续将由master thread或专门的刷脏线程阶段性的将这些页面写入磁盘。这样的好处是避免每次写操作都操作磁盘导致大量的随机I/O，阶段性的刷脏可以将多次对页面的修改合并成一次I/O操作，同时异步写入也降低了访问时延。然而，如果在脏页还未刷入磁盘时，服务器非正常关闭，这些修改操作将会丢失，如果写入操作正在进行，甚至会由于损坏数据文件导致数据库不可用。为了避免上述问题的发生，Innodb将所有对页面的修改操作写入一个专门的文件，并在数据库启动时从此文件进行实例恢复操作，这个文件就是重做日志文件。每当有更新操作时，在数据页变更之前将操作写入重做日志，这样当发生掉电之类的情况时系统可以在重启后继续操作。这就是所谓的预写日志（Write-ahead logging，WAL）。这样的技术推迟了缓冲区页面的刷新，从而提升了数据库的吞吐量。同时由于重做日志的写操作是顺序I/O，相对于写数据文件的随机I/O要快得多。大多数数据库系统都采用类似的技术实现。
聪明如你可能已经有了这样的疑问，在复制中二进制日志和重做日志如何协同工作？假设Innodb写完重做日志后，服务异常关闭。主库能够根据重做日志恢复数据，但由于二进制日志没写入，会导致从库同步时少了这个事务么？或者反之，二进制日志写成功，而重做日志没有写完，是否导致从库执行事务，而主库不执行？这些情况会不会产生主从数据不一致的问题呢？解决这些问题是MySQL的核心需求，让我们从MySQL基本架构说起。图2是MySQL的逻辑结构图。
图2 MySQL服务器逻辑架构图
最上层用于处理客户端连接、授权认证、安全等等。第二层架构是MySQL服务器层。大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有内置函数，所有跨存储引擎的功能（存储过程、触发器、视图等）都在这一层实现。如你所料，二进制日志也在这一层实现。第三层包含了存储引擎，负责MySQL中数据的存储和提取。服务器通过API与存储引擎进行通信，存储引擎只是简单地响应上层服务器的请求。显然Innodb的重做日志在这一层实现。
由于MySQL的事务日志包含二进制日志和重做日志，当发生崩溃恢复时，MySQL主库通过重做日志进行恢复，而在主从复制的环境下，从库是依据于主节点的二进制日志进行同步数据的。这样的架构对两种日志有两个基本要求：第一，保证二进制日志里面存在的事务一定在重做日志里面存在，也就是二进制日志里不会比重做日志多事务（可以少，因为重做日志里面记录的事务可能有部分没有提交，这些事务最终可能会被回滚）。第二，两种日志种事务的顺序一致，这也是很重要的一点，假设两者记录的事务顺序不一致，那么会出现类似于主库事务执行的顺序是ta、tb、tc、td，但是二进制日志中记录的是ta、tc、tb、td，被复制到从库后导致主从数据不一致。为了达到这两点要求，MySQL使用内部XA来实现（XA是eXtended Architecture的缩写，是X/Open分布式事务定义的事务中间件与数据库之间的接口规范），其核心是两阶段提交（two phase commit，2PC）。
3. 两阶段提交
在两阶段提交协议中一般分为事务管理器（协调者）和若干事务执行者（参与者）两种角色。在MySQL内部实现的两阶段提交中，二进制日志充当了协调者角色，由它来通知Innodb执行准备、提交或回滚步骤。从实现角度分析，提交流程和代码框架分别如图3、图4所示。
图3 MySQL两阶段提交流程
图4 commit命令的MySQL代码框架
（1）先调用binglog_hton和innobase_hton的prepare方法完成第一阶段，binlog_hton的papare方法实际上什么也没做，innodb的prepare持有prepare_commit_mutex，将重做日志刷磁盘，并将事务状态设为TRX_PREPARED。
（2）如果事务涉及的所有存储引擎的prepare都执行成功，则调用TC_LOG_BINLOG::log_xid将事务（STATEMENT格式或ROW格式）写到二进制日志，此时，事务已经铁定要提交了。否则，调用ha_rollback_trans回滚事务，而事务实际上也不会写到二进制日志。
（3）最后，调用引擎的commit完成事务的提交。实际上binlog_hton->commit什么也不会做（上一步已经将二进制日志写入磁盘），innobase_hton->commit则清除回滚信息，向重做日志中写入COMMIT标记，释放prepare_commit_mutex，并将事务设为TRX_NOT_STARTED状态。
如果数据库系统发生崩溃，当重启数据库时会进行崩溃恢复操作。具体到代码层面，Innodb在恢复的时候，不同状态的事务，会进行不同的处理：
对于TRX_COMMITTED_IN_MEMORY的事务，清除回滚段，然后将事务设为TRX_NOT_STARTED；
对于TRX_NOT_STARTED的事务，表示事务已经提交，跳过；
对于TRX_PREPARED的事务，要根据二进制日志来决定事务是否提交，暂时跳过;
对于TRX_ACTIVE的事务，回滚。
简单来讲，当发生崩溃恢复时，数据库根据重做日志进行数据恢复，逐个查看每条重做条目的事务状态，根据图3的流程，如果已进行到TRX_NOT_STARTED阶段，也就是存储引擎commit阶段，那么说明重做日志和二进制日志是一致的，正常根据重做条目进行恢复即可；事务状态为TRX_ACTIVE，没写到二进制日志中，直接回滚；如果事务状态为TRX_PREPARED，要分两种情况，先检查二进制日志是否已写入成功，如果没写入成功，那么就算是TRX_PREPARED状态，也要回滚。如果写入成功了，那么就进行最后一步，调用存储引擎commit，更改事务状态为TRX_NOT_STARTED，也就是真正提交状态，可以用作数据恢复。
可见，MySQL是以二进制日志的写入与否作为事务提交成功与否的标志，通过这种方式让Innodb重做日志和MySQL服务器的二进制日志中的事务状态保持一致。两阶段提交很好的保持了数据一致性和事务顺序性。
了解了所有这些技术细节后，当初的疑问自然也就有了答案。假设在阶段（1）结束之后程序异常，此时没有写入二进制日志，则从库不会同步这个事务。主库上，崩溃恢复时重做日志中这个事务没有trx_commit，因此会被回滚。逻辑上主从库都不会执行这个事务。假设在阶段（2）结束后程序异常，此时二进制日志已经写入，则从库会同步这个事务。主库上，根据重做日志能够正常恢复此事务。也就是说，若二进制日志写入完成，则主从库都会正常完成事务，反之则主从库都回滚事务，都不会出现主从不一致的问题。
MySQL通过innodb_support_xa系统变量控制Innodb是否支持XA事务的2PC，默认是TRUE。如果关闭，则Innodb在prepare阶段就什么也不做，这可能会导致二进制日志的顺序与Innodb提交的顺序不一致，继而导致在恢复时或者从库上产生不同的数据。在MySQL 8中，innodb_support_xa系统变量已被移除，因为始终启用Innodb对XA事务中两阶段提交的支持，不再让用户来选择。
上述的MySQL两阶段提交流程并不是天衣无缝的，主从数据是否一致还与重做日志和二进制日志的写盘方式有关。innodb_flush_log_at_trx_commit和sync_binlog系统变量分别控制两者的落盘策略。
innodb_flush_log_at_trx_commit：有0、1、2三个可选值。0表示每秒进行一次刷新，但是每次事务提交不进行任何操作（每秒调用fsync使数据落地到磁盘，不过这里需要注意如果底层存储有cache，比如raid cache，那么这时也不会真正落盘，但是由于一般raid卡都带有备用电源，所以一般都认为此时数据是安全的）。1代表每次事务提交都会进行刷新，这是最安全的模式。2表示每秒刷新，每次事务提交时不刷新，而是调用write将重做日志缓冲区里面的内容刷到操作系统页面缓存。从数据安全性和性能比较三种策略的优劣为：1由于每次事务提交都会是重做日志落盘，所以是最安全的，但是由于fsync的次数增多导致性能下降比较严重。0表示每秒刷新，每次事务提交不进行任何操作，所以MySQL或操作系统崩溃时最多丢失一秒的事务。2相对于0来说了多了每次事务提交时的一个write操作，此时数据虽然没有落磁，但是只要没有操作系统崩溃，即使MySQL崩溃，那么事务也是不会丢失的。
sync_binlog：MySQL在提交事务时调用MYSQL_LOG::write完成写二进制日志，并根据sync_binlog决定是否进行刷新。默认值是0，即不刷新，从而把控制权交给操作系统。如果设为1，则每次提交事务，就会进行一次磁盘刷新。
这两个参数不同的值会带来不同的效果。两者都设置为1，数据最安全，能保证主从一致，这也是MySQL 8的默认设置。innodb_flush_log_at_trx_commit非1，假设在二进制日志写入完成后系统崩溃，则可能出现这样的情况：从库能够执行事务，但主库中trx_prepare的日志没有被写入到重做日志中，导致主库不执行事务，出现主从不一致的情况。同理若sync_binlog非1，可能导致二进制日志丢失（操作系统异常宕机），从而与Innodb层面的数据不一致，体现在复制上，从库可能丢失事务。在数据一致性要求很高的场景下，建议就使用缺省的全1配置。
二、复制实验环境
1. 主机IP
172.16.1.125（主）
172.16.1.126（从）
172.16.1.127（从）
2. 软件环境
OS：CentOS Linux release 7.2.1511 (Core)
MySQL：MySQL Community Server 8.0.16
glibc：glibc-2.17-105.el7.x86_64
3. 硬件环境
三台虚拟机，每台基本配置为：
. 双核双CPU，Intel(R) Xeon(R) CPU E5-2420 0 @ 1.90GHz
. 8G物理内存，8G Swap
. 100G物理硬盘
三、安装mysql-8.0.16
从https://dev.mysql.com/downloads/mysql/下载二进制安装文件mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz，相关选项如图5所示。
图5 下载mysql-8.0.16安装包
然后用root用户按顺序执行下面的命令，在三台主机上安装MySQL。
# 进入安装目录
cd /usr/local
# 从tar包中把提取文件
tar xvf /home/mysql/mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz
# 建立软连接
ln -s mysql-8.0.16-linux-glibc2.12-x86_64 mysql
# 进入mysql目录
cd mysql
# 建立secure_file_priv系统变量指向的目录
mkdir mysql-files
# 修改属主为mysql
chown mysql:mysql mysql-files
# 修改目录权限
chmod 750 mysql-files
# mysql系统初始化
bin/mysqld --initialize --user=mysql
# 建立SSL/RSA相关文件，如果不启用SSL连接，这步可省略
bin/mysql_ssl_rsa_setup
# 启动mysql服务器
bin/mysqld_safe --user=mysql &
# 连接mysql服务器
bin/mysql -u root -p
-- 修改root密码
alter user user() identified by "123456";
-- 创建一个新的mysql管理员账号
create user 'wxy'@'%' identified with mysql_native_password by '123456';
grant all on *.* to 'wxy'@'%' with grant option;
命令说明：
（1）mysql-files目录用作secure_file_priv系统变量的值。该变量将导入和导出操作限制到特定目录。例如由LOAD DATA和SELECT ... INTO OUTFILE语句和LOAD_FILE()函数所执行的操作。仅允许具有FILE权限的用户执行这些操作。secure_file_priv系统变量设置如下：
空字符串：变量不起作用，是不安全的设置。
目录名：mysql限制导入和导出操作仅用于该目录中的文件。目录必须已经存在，mysql不会创建它。
NULL：mysql禁用导入导出操作。
（2）mysqld --initialize 命令创建默认数据库并退出。在过程中会创建一个超级用户，并为该用户产生一个随机密码。命令执行输出如下所示：
[root@hdp2/usr/local/mysql]#bin/mysqld --initialize --user=mysql
2019-05-05T06:31:58.956385Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.16) initializing of server in progress as process 10256
2019-05-05T06:32:01.287093Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: w1SN3pgRPL*D
2019-05-05T06:32:02.901171Z 0 [System] [MY-013170] [Server] /usr/local/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.16) initializing of server has completed
[root@hdp2/usr/local/mysql]#
mysql文档中说产生的临时密码会写到.err日志文件里，但在本次安装中，这步并不生成.err文件。保险的做法还是记下临时密码，这点很重要。在initialize情况下，临时密码默认标记为已过期，用户必须在第一次进入mysql后首先修改密码。
（3）mysql_ssl_rsa_setup程序将创建SSL证书和密钥文件，使用SSL进行安全连接所需的RSA密钥对文件。如果现有的SSL文件已过期，mysql_ssl_rsa_setup也可用于创建新的SSL文件。
mysql_ssl_rsa_setup使用openssl命令，因此主机上必须安装有OpenSSL。mysql_ssl_rsa_setup检查数据目录中的以下SSL文件：
ca.pem
server-cert.pem
server-key.pem
如果存在任何这些文件，则mysql_ssl_rsa_setup不会创建任何SSL文件。否则，它会调用openssl来创建它们，以及一些其他文件：
ca.pem 自签名CA证书
ca-key.pem CA私钥
server-cert.pem 服务器证书
server-key.pem 服务器私钥
client-cert.pem 客户端证书
client-key.pem 客户端私钥
在启用SSL安全客户端连接时需要这些文件。
之后mysql_ssl_rsa_setup检查数据目录中的以下RSA文件：
private_key.pem 私钥/公钥对的私有成员
public_key.pem 私钥/公钥对的公共成员
如果存在任何这些文件，则mysql_ssl_rsa_setup不会创建任何RSA文件。否则，它会调用openssl来创建它们。对于sha256_password或caching_sha2_password插件验证的帐户不加密连接时，通过这些文件使用RSA进行安全密码交换。
mysql 8.0.16缺省支持SSL加密连接：
mysql> show variables like 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl | YES |
+---------------+-------+
1 row in set (0.00 sec)
（4）第一次进入mysql后，执行任何命令都会报以下错误：
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
提示很明显，需要修改初始化时为用户'root'@'localhost'生成的临时密码。mysql 8缺省使用的认证插件是caching_sha2_password：
mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)
当用老版本mysql的客户端连接mysql 8服务器时，可能报以下错误：
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /home/mysql/mysql-5.6.14/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
可以使用两个方法解决此问题。第一种方法是在配置文件中设置default_authentication_plugin='mysql_native_password'，然后重启mysql服务器使之生效。default_authentication_plugin是只读系统变量，不能动态修改。第二种方法是在创建用户时，使用兼容新老版本的认证方式，例如：
create user 'wxy'@'%' identified with mysql_native_password by '123456';
---------------------
1. 复制的用途
2. 复制如何工作
3. 两阶段提交
二、复制实验环境
三、安装mysql-8.0.16
四、配置异步复制
1. 空库
2. 脱机
3. 联机
一、MySQL异步复制介绍
简单说，复制就是将来自一个MySQL数据库服务器（主库）的数据复制到一个或多个MySQL数据库服务器（从库）。传统的MySQL复制提供了一种简单的Primary-Secondary复制方法，默认情况下，复制是单向异步的。MySQL支持两种复制方式：基于行的复制和基于语句的复制。这两种方式都是通过在主库上记录二进制日志（binlog）、在从库重放中继日志（relylog）的方式来实现异步的数据复制。二进制日志或中继日志中的记录被称为事件。所谓异步包含两层含义，一是主库的二进制日志写入与将其发送到从库是异步进行的，二是从库获取与重放日志事件是异步进行的。这意味着，在同一时间点从库上的数据更新可能落后于主库，并且无法保证主从之间的延迟间隔。
复制给主库增加的开销主要体现在启用二进制日志带来的I/O，但是开销并不大，MySQL官方文档中称开启二进制日志会产生1%的性能损耗。出于对历史事务备份以及从介质失败中恢复的目的，这点开销是非常必要的。除此之外，每个从库也会对主库产生一些负载，例如网络和I/O开销。当从库读取主库的二进制日志时，可能会造成一定的I/O开销。如果从一个主库上复制到多个从库，唤醒多个复制线程发送二进制日志内容的开销将会累加。但所有这些复制带来的额外开销相对于应用对MySQL服务器造成的高负载来说是很小的。
1. 复制的用途
（1）横向扩展
通过复制可以将读操作指向从库来获得更好的读扩展。所有写入和更新都在主库上进行，但读取可能发生在一个或多个从库上。在这种读写分离模型中，主库专用于更新，显然比同时进行读写操作会有更好的写性能。需要注意的是，对于写操作并不适合通过复制来扩展。在一主多从架构中，写操作会被执行多次，这时整个系统的写性能取决于写入最慢的那部分。
（2）负载均衡
通过MySQL复制可以将读操作分不到多个服务器上，实现对读密集型应用的优化。对于小规模的应用，可以简单地对机器名做硬编码或者使用DNS轮询（将一个机器名指向多个IP地址）。当然也可以使用复杂的方法，例如使用LVS网络负载均衡器等，能够很好地将负载分配到不同的MySQL服务器上。
（3）提高数据安全性
提高数据安全性可以从两方面来理解。其一，因为数据被复制到从库，并且从库可以暂停复制过程，所以可以在从库上运行备份服务而不会影响相应的主库。其二，当主库出现问题，还有从库的数据可以被访问。但是，对备份来说，复制仅是一项有意义的技术补充，它既不是备份也不能够取代备份。例如，当用户误删除一个表，而且此操作已经在从库上被复制执行，这种情况下只能用备份来恢复。
（4）提高高可用性
复制能够帮助应用程序避免MySQL单点失败，一个包含复制的设计良好的故障切换系统能够显著缩短宕机时间。
（5）滚动升级
比较普遍的做法是，使用一个高版本MySQL作为从库，保证在升级全部实例前，查询能够在从库上按照预期执行。测试没有问题后，将高版本的MySQL切换为主库，并将应用连接至该主库，然后重新搭建高版本的从库。
2. 复制如何工作
如前所述，MySQL复制依赖二进制日志，所以要理解复制如何工作，先要了解MySQL的二进制日志。
（1）二进制日志
二进制日志包含描述数据库更改的事件，如建表操作或对表数据的更改等。开启二进制日志有两个重要目的：
用于复制。主库上的二进制日志提供要发送到从库的数据更改记录。主库将其二进制日志中包含的事件发送到从库，从库执行这些事件以对主服务器上的数据进行相同的更改。
用于恢复。当出现介质错误（如磁盘故障）时，数据恢复操作需要使用二进制日志。还原备份后，将重新执行备份后记录的二进制日志中的事件。
不难看出，MySQL二进制日志所起的作用与Oracle的归档日志类似。二进制日志只记录更新数据的事件，不用于SELECT或SHOW等语句。通过设置log-bin系统变量开启二进制日志，MySQL 8中缺省是开启的。
二进制日志有STATEMENT、ROW、MIXED三种格式，通过binlog-format系统变量设置：
STATMENT格式，基于SQL语句的复制（statement-based replication，SBR）。每一条会修改数据的SQL语句会记录到binlog中。这种格式的优点是不需要记录每行的数据变化，这样二进制日志会比较少，减少磁盘I/O，提高性能。缺点是在某些情况下会导致主库与从库中的数据不一致，例如last_insert_id()、now()等非确定性函数，以及用户自定义函数（user-defined functions，udf）等易出现问题。
ROW格式，基于行的复制（row-based replication，RBR）。不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子，能清楚记录每一行数据的修改细节。其优点是不会出现某些特定情况下的存储过程、函数或触发器的调用和触发无法被正确复制的问题。缺点是通常会产生大量的日志，尤其像大表上执行alter table操作时候会让日志暴涨。
MIXED格式，混合复制（mixed-based replication，MBR）。是语句和行两种格式的混合使用，默认使用STATEMENT模式保存二进制日志，对于STATEMENT模式无法正确复制的操作，会自动切换到基于行的格式，MySQL会根据执行的SQL语句选择日志保存方式。
MySQL 8缺省使用ROW格式。二进制日志的存放位置最好设置到与MySQL数据目录不同的磁盘分区，以降低磁盘I/O的竞争，提升性能，并且在数据磁盘故障的时候还可以利用备份和二进制日志恢复数据。
（2）复制步骤
总的来说，MySQL复制有五个步骤：
在主库上把数据更改事件记录到二进制日志中。
从库上的I/O线程向主库询问二进制日志中的事件。
主库上的binlog dump线程向I/O线程发送二进制事件。
从库上的I/O线程将二进制日志事件复制到自己的中继日志中。
从库上的SQL线程读取中继日志中的事件，并将其重放到从库上。
图1更详细地描述了复制的细节。
图1 复制如何工作
第一步是在主库上记录二进制日志。在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。MySQL会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。
下一步，从库将主库的二进制日志复制到其本地的中继日志中。首先，从库会启动一个工作线程，称为I/O线程，I/O线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制日志转储（binlog dump）线程，它会读取主库上二进制日志中的事件，但不会对事件进行轮询。如果该线程追赶上了主库，它将进入睡眠状态，直到主库发送信号通知其有新的事件时才会被唤醒，从库I/O线程会将接收到的事件记录到中继日志中。
从库的SQL线程执行最后一步，该线程从中继日志中读取事件并在从库上执行，从而实现从库数据的更新。当SQL线程追赶I/O线程时，中继日志通常已经在系统缓存中，所以重放中继日志的开销很低。SQL线程执行的事件也可以通过log_slave_updates系统变量来决定是否写入其自己的二进制日志中，这可以用于级联复制的场景。
这种复制架构实现了获取事件和重放事件的解耦，允许这两个过程异步进行。也就是说I/O线程能够独立于SQL线程之外工作。但这种架构也限制了复制的过程，其中最重要的一点是在主库上并发更新的查询在从库上通常只能串行化执行，因为缺省只有一个SQL线程来重放中继日志中的事件。在MySQL 5.6以后已经可以通过配置slave_parallel_workers等系统变量进行并行复制，在后面讨论与复制相关的性能问题时会介绍并行复制的相关细节。
现在我们已经了解了MySQL复制是以二进制日志为基础的，但是像Innodb这样的事务引擎有自己的事务日志，如ib_logfile，这些事务日志通常被称为重做日志（redo log）。作为背景知识，简单介绍下Innodb重做日志的作用。
对Innodb的任何修改操作都会首先在称为缓冲池（innodb buffer pool）的内存页面上进行，然后这样的页面将被标记为脏页，并被放到专门的刷新列表上，后续将由master thread或专门的刷脏线程阶段性的将这些页面写入磁盘。这样的好处是避免每次写操作都操作磁盘导致大量的随机I/O，阶段性的刷脏可以将多次对页面的修改合并成一次I/O操作，同时异步写入也降低了访问时延。然而，如果在脏页还未刷入磁盘时，服务器非正常关闭，这些修改操作将会丢失，如果写入操作正在进行，甚至会由于损坏数据文件导致数据库不可用。为了避免上述问题的发生，Innodb将所有对页面的修改操作写入一个专门的文件，并在数据库启动时从此文件进行实例恢复操作，这个文件就是重做日志文件。每当有更新操作时，在数据页变更之前将操作写入重做日志，这样当发生掉电之类的情况时系统可以在重启后继续操作。这就是所谓的预写日志（Write-ahead logging，WAL）。这样的技术推迟了缓冲区页面的刷新，从而提升了数据库的吞吐量。同时由于重做日志的写操作是顺序I/O，相对于写数据文件的随机I/O要快得多。大多数数据库系统都采用类似的技术实现。
聪明如你可能已经有了这样的疑问，在复制中二进制日志和重做日志如何协同工作？假设Innodb写完重做日志后，服务异常关闭。主库能够根据重做日志恢复数据，但由于二进制日志没写入，会导致从库同步时少了这个事务么？或者反之，二进制日志写成功，而重做日志没有写完，是否导致从库执行事务，而主库不执行？这些情况会不会产生主从数据不一致的问题呢？解决这些问题是MySQL的核心需求，让我们从MySQL基本架构说起。图2是MySQL的逻辑结构图。
图2 MySQL服务器逻辑架构图
最上层用于处理客户端连接、授权认证、安全等等。第二层架构是MySQL服务器层。大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有内置函数，所有跨存储引擎的功能（存储过程、触发器、视图等）都在这一层实现。如你所料，二进制日志也在这一层实现。第三层包含了存储引擎，负责MySQL中数据的存储和提取。服务器通过API与存储引擎进行通信，存储引擎只是简单地响应上层服务器的请求。显然Innodb的重做日志在这一层实现。
由于MySQL的事务日志包含二进制日志和重做日志，当发生崩溃恢复时，MySQL主库通过重做日志进行恢复，而在主从复制的环境下，从库是依据于主节点的二进制日志进行同步数据的。这样的架构对两种日志有两个基本要求：第一，保证二进制日志里面存在的事务一定在重做日志里面存在，也就是二进制日志里不会比重做日志多事务（可以少，因为重做日志里面记录的事务可能有部分没有提交，这些事务最终可能会被回滚）。第二，两种日志种事务的顺序一致，这也是很重要的一点，假设两者记录的事务顺序不一致，那么会出现类似于主库事务执行的顺序是ta、tb、tc、td，但是二进制日志中记录的是ta、tc、tb、td，被复制到从库后导致主从数据不一致。为了达到这两点要求，MySQL使用内部XA来实现（XA是eXtended Architecture的缩写，是X/Open分布式事务定义的事务中间件与数据库之间的接口规范），其核心是两阶段提交（two phase commit，2PC）。
3. 两阶段提交
在两阶段提交协议中一般分为事务管理器（协调者）和若干事务执行者（参与者）两种角色。在MySQL内部实现的两阶段提交中，二进制日志充当了协调者角色，由它来通知Innodb执行准备、提交或回滚步骤。从实现角度分析，提交流程和代码框架分别如图3、图4所示。
图3 MySQL两阶段提交流程
图4 commit命令的MySQL代码框架
（1）先调用binglog_hton和innobase_hton的prepare方法完成第一阶段，binlog_hton的papare方法实际上什么也没做，innodb的prepare持有prepare_commit_mutex，将重做日志刷磁盘，并将事务状态设为TRX_PREPARED。
（2）如果事务涉及的所有存储引擎的prepare都执行成功，则调用TC_LOG_BINLOG::log_xid将事务（STATEMENT格式或ROW格式）写到二进制日志，此时，事务已经铁定要提交了。否则，调用ha_rollback_trans回滚事务，而事务实际上也不会写到二进制日志。
（3）最后，调用引擎的commit完成事务的提交。实际上binlog_hton->commit什么也不会做（上一步已经将二进制日志写入磁盘），innobase_hton->commit则清除回滚信息，向重做日志中写入COMMIT标记，释放prepare_commit_mutex，并将事务设为TRX_NOT_STARTED状态。
如果数据库系统发生崩溃，当重启数据库时会进行崩溃恢复操作。具体到代码层面，Innodb在恢复的时候，不同状态的事务，会进行不同的处理：
对于TRX_COMMITTED_IN_MEMORY的事务，清除回滚段，然后将事务设为TRX_NOT_STARTED；
对于TRX_NOT_STARTED的事务，表示事务已经提交，跳过；
对于TRX_PREPARED的事务，要根据二进制日志来决定事务是否提交，暂时跳过;
对于TRX_ACTIVE的事务，回滚。
简单来讲，当发生崩溃恢复时，数据库根据重做日志进行数据恢复，逐个查看每条重做条目的事务状态，根据图3的流程，如果已进行到TRX_NOT_STARTED阶段，也就是存储引擎commit阶段，那么说明重做日志和二进制日志是一致的，正常根据重做条目进行恢复即可；事务状态为TRX_ACTIVE，没写到二进制日志中，直接回滚；如果事务状态为TRX_PREPARED，要分两种情况，先检查二进制日志是否已写入成功，如果没写入成功，那么就算是TRX_PREPARED状态，也要回滚。如果写入成功了，那么就进行最后一步，调用存储引擎commit，更改事务状态为TRX_NOT_STARTED，也就是真正提交状态，可以用作数据恢复。
可见，MySQL是以二进制日志的写入与否作为事务提交成功与否的标志，通过这种方式让Innodb重做日志和MySQL服务器的二进制日志中的事务状态保持一致。两阶段提交很好的保持了数据一致性和事务顺序性。
了解了所有这些技术细节后，当初的疑问自然也就有了答案。假设在阶段（1）结束之后程序异常，此时没有写入二进制日志，则从库不会同步这个事务。主库上，崩溃恢复时重做日志中这个事务没有trx_commit，因此会被回滚。逻辑上主从库都不会执行这个事务。假设在阶段（2）结束后程序异常，此时二进制日志已经写入，则从库会同步这个事务。主库上，根据重做日志能够正常恢复此事务。也就是说，若二进制日志写入完成，则主从库都会正常完成事务，反之则主从库都回滚事务，都不会出现主从不一致的问题。
MySQL通过innodb_support_xa系统变量控制Innodb是否支持XA事务的2PC，默认是TRUE。如果关闭，则Innodb在prepare阶段就什么也不做，这可能会导致二进制日志的顺序与Innodb提交的顺序不一致，继而导致在恢复时或者从库上产生不同的数据。在MySQL 8中，innodb_support_xa系统变量已被移除，因为始终启用Innodb对XA事务中两阶段提交的支持，不再让用户来选择。
上述的MySQL两阶段提交流程并不是天衣无缝的，主从数据是否一致还与重做日志和二进制日志的写盘方式有关。innodb_flush_log_at_trx_commit和sync_binlog系统变量分别控制两者的落盘策略。
innodb_flush_log_at_trx_commit：有0、1、2三个可选值。0表示每秒进行一次刷新，但是每次事务提交不进行任何操作（每秒调用fsync使数据落地到磁盘，不过这里需要注意如果底层存储有cache，比如raid cache，那么这时也不会真正落盘，但是由于一般raid卡都带有备用电源，所以一般都认为此时数据是安全的）。1代表每次事务提交都会进行刷新，这是最安全的模式。2表示每秒刷新，每次事务提交时不刷新，而是调用write将重做日志缓冲区里面的内容刷到操作系统页面缓存。从数据安全性和性能比较三种策略的优劣为：1由于每次事务提交都会是重做日志落盘，所以是最安全的，但是由于fsync的次数增多导致性能下降比较严重。0表示每秒刷新，每次事务提交不进行任何操作，所以MySQL或操作系统崩溃时最多丢失一秒的事务。2相对于0来说了多了每次事务提交时的一个write操作，此时数据虽然没有落磁，但是只要没有操作系统崩溃，即使MySQL崩溃，那么事务也是不会丢失的。
sync_binlog：MySQL在提交事务时调用MYSQL_LOG::write完成写二进制日志，并根据sync_binlog决定是否进行刷新。默认值是0，即不刷新，从而把控制权交给操作系统。如果设为1，则每次提交事务，就会进行一次磁盘刷新。
这两个参数不同的值会带来不同的效果。两者都设置为1，数据最安全，能保证主从一致，这也是MySQL 8的默认设置。innodb_flush_log_at_trx_commit非1，假设在二进制日志写入完成后系统崩溃，则可能出现这样的情况：从库能够执行事务，但主库中trx_prepare的日志没有被写入到重做日志中，导致主库不执行事务，出现主从不一致的情况。同理若sync_binlog非1，可能导致二进制日志丢失（操作系统异常宕机），从而与Innodb层面的数据不一致，体现在复制上，从库可能丢失事务。在数据一致性要求很高的场景下，建议就使用缺省的全1配置。
二、复制实验环境
1. 主机IP
172.16.1.125（主）
172.16.1.126（从）
172.16.1.127（从）
2. 软件环境
OS：CentOS Linux release 7.2.1511 (Core)
MySQL：MySQL Community Server 8.0.16
glibc：glibc-2.17-105.el7.x86_64
3. 硬件环境
三台虚拟机，每台基本配置为：
. 双核双CPU，Intel(R) Xeon(R) CPU E5-2420 0 @ 1.90GHz
. 8G物理内存，8G Swap
. 100G物理硬盘
三、安装mysql-8.0.16
从https://dev.mysql.com/downloads/mysql/下载二进制安装文件mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz，相关选项如图5所示。
图5 下载mysql-8.0.16安装包
然后用root用户按顺序执行下面的命令，在三台主机上安装MySQL。
# 进入安装目录
cd /usr/local
# 从tar包中把提取文件
tar xvf /home/mysql/mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz
# 建立软连接
ln -s mysql-8.0.16-linux-glibc2.12-x86_64 mysql
# 进入mysql目录
cd mysql
# 建立secure_file_priv系统变量指向的目录
mkdir mysql-files
# 修改属主为mysql
chown mysql:mysql mysql-files
# 修改目录权限
chmod 750 mysql-files
# mysql系统初始化
bin/mysqld --initialize --user=mysql
# 建立SSL/RSA相关文件，如果不启用SSL连接，这步可省略
bin/mysql_ssl_rsa_setup
# 启动mysql服务器
bin/mysqld_safe --user=mysql &
# 连接mysql服务器
bin/mysql -u root -p
-- 修改root密码
alter user user() identified by "123456";
-- 创建一个新的mysql管理员账号
create user 'wxy'@'%' identified with mysql_native_password by '123456';
grant all on *.* to 'wxy'@'%' with grant option;
命令说明：
（1）mysql-files目录用作secure_file_priv系统变量的值。该变量将导入和导出操作限制到特定目录。例如由LOAD DATA和SELECT ... INTO OUTFILE语句和LOAD_FILE()函数所执行的操作。仅允许具有FILE权限的用户执行这些操作。secure_file_priv系统变量设置如下：
空字符串：变量不起作用，是不安全的设置。
目录名：mysql限制导入和导出操作仅用于该目录中的文件。目录必须已经存在，mysql不会创建它。
NULL：mysql禁用导入导出操作。
（2）mysqld --initialize 命令创建默认数据库并退出。在过程中会创建一个超级用户，并为该用户产生一个随机密码。命令执行输出如下所示：
[root@hdp2/usr/local/mysql]#bin/mysqld --initialize --user=mysql
2019-05-05T06:31:58.956385Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.16) initializing of server in progress as process 10256
2019-05-05T06:32:01.287093Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: w1SN3pgRPL*D
2019-05-05T06:32:02.901171Z 0 [System] [MY-013170] [Server] /usr/local/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.16) initializing of server has completed
[root@hdp2/usr/local/mysql]#
mysql文档中说产生的临时密码会写到.err日志文件里，但在本次安装中，这步并不生成.err文件。保险的做法还是记下临时密码，这点很重要。在initialize情况下，临时密码默认标记为已过期，用户必须在第一次进入mysql后首先修改密码。
（3）mysql_ssl_rsa_setup程序将创建SSL证书和密钥文件，使用SSL进行安全连接所需的RSA密钥对文件。如果现有的SSL文件已过期，mysql_ssl_rsa_setup也可用于创建新的SSL文件。
mysql_ssl_rsa_setup使用openssl命令，因此主机上必须安装有OpenSSL。mysql_ssl_rsa_setup检查数据目录中的以下SSL文件：
ca.pem
server-cert.pem
server-key.pem
如果存在任何这些文件，则mysql_ssl_rsa_setup不会创建任何SSL文件。否则，它会调用openssl来创建它们，以及一些其他文件：
ca.pem 自签名CA证书
ca-key.pem CA私钥
server-cert.pem 服务器证书
server-key.pem 服务器私钥
client-cert.pem 客户端证书
client-key.pem 客户端私钥
在启用SSL安全客户端连接时需要这些文件。
之后mysql_ssl_rsa_setup检查数据目录中的以下RSA文件：
private_key.pem 私钥/公钥对的私有成员
public_key.pem 私钥/公钥对的公共成员
如果存在任何这些文件，则mysql_ssl_rsa_setup不会创建任何RSA文件。否则，它会调用openssl来创建它们。对于sha256_password或caching_sha2_password插件验证的帐户不加密连接时，通过这些文件使用RSA进行安全密码交换。
mysql 8.0.16缺省支持SSL加密连接：
mysql> show variables like 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl | YES |
+---------------+-------+
1 row in set (0.00 sec)
（4）第一次进入mysql后，执行任何命令都会报以下错误：
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
提示很明显，需要修改初始化时为用户'root'@'localhost'生成的临时密码。mysql 8缺省使用的认证插件是caching_sha2_password：
mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)
当用老版本mysql的客户端连接mysql 8服务器时，可能报以下错误：
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /home/mysql/mysql-5.6.14/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
可以使用两个方法解决此问题。第一种方法是在配置文件中设置default_authentication_plugin='mysql_native_password'，然后重启mysql服务器使之生效。default_authentication_plugin是只读系统变量，不能动态修改。第二种方法是在创建用户时，使用兼容新老版本的认证方式，例如：
create user 'wxy'@'%' identified with mysql_native_password by '123456';
---------------------
评论
发表评论