MySQL复制

复制

1. Master记录二进制日志， 每次提交事务完成数据更新前，Master将数据更新的时间记录到二进制日志中，MySQL会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。记录二进制日志后，主库会告诉存储引擎可以提交事务了。
2. 在Slave服务器上执行start slave命令开启主从复制开关，开始进行主从复制。
3. 此时，Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接Master服务器，并请求从执行binlog日志文件中的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令指定的）之后开始发送binlog日志内容。
4. Master服务器接收来自Slave服务器的IO线程的请求后，其负责复制的IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外，还有在Master服务器端记录的IO线程。返回的信息中除了binlog中的下一个指定更新位置。
5. 当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（Mysql-relay-bin.xxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容
6. Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点

三个线程

• slave IO 线程 ， slave发起的到master的链接，然后读取master发到客户端binlog日志，写入到中继日志中
• dump线程，同时，master跟slave连接后产生一个线程，这个线程也就是master的IO线程，这个连接过程不需要解析SQL，所以没有SQL线程。该线程负责将binlog 发送到slave 中。
• slaveSQL线程 。 SQL回放线程位于slave实例，主要作用就是读取relay log，并且进行回放操作。SQL线程执行的事件也可以通过配置选项来决定是否写入自己的二进制文件中。

弊端：

SQL线程 只能有一个，也就是说在主库上并发执行的操作，在备库上只能串行执行。这是很多复制架构的瓶颈所在。
从5.6开始，MySQL支持多SQL线程并发执行。

复制实操

• 在每台服务器上创建复制账号（非必需，但为了故障转移，建议这么做）
• 配置主库和备库
• 通知备库连接到主库，并从主库复制数据

第一步，创建复制账号

grant replication slave, replication client on *.* to 'repl'@'localhost' identified by 'repl';

创建复制的账号

第二步，配置主库与备库

打开主库， 修改my.cnf文件，添加二进制文件配置，并配置服务器id，服务器id 必须为唯一的，通用做法是去ip的末8位

## 设置前缀
log_bin= mysql-bin
server_id = 10 

可以通过show master status; 查看当前二进制文件。

备库设置

# 服务器id
server-id   = 10
# 赋予默认名字
log-bin=mysql-bin
# 中继日志位置
relay_log = /var/lib/mysql/mysql-relay-bin
# 允许备库将其重放的日志记录到自身的二进制文件中
log_slave_updates = 1
# 将备库设为只读，除了管理员和sql线程之外，其他用户是不能进行修改数据的。不加的话，可能会导致一些奇怪的问题。
read_only = 1

第三步，启动复制

备库上执行以下sql ,通过show slave status 查看复制是否正确执行

CHANGE MASTER TO
MASTER_HOST='$host',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='msyql-bin.00001',
MASTER_LOG_POS=0;

然后执行start slave 开始复制，如果没有错误，我们通过 show slave status 可以看到io线程和SQL线程已经开始运行了

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysql-relay-bin.000020
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0
             Master_Server_Id: 10

# Slave_IO_Running: Yes，Slave_SQL_Running: Yes 说明同步正常进行
# Seconds_Behind_Master: 0 就是完全同步了

我们还可以从线程列表看出复制线程，主库上可以看到由从库I/O线程向主库发起的连接。

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 44
   User: repl
   Host: localhost:32866
     db: NULL
Command: Binlog Dump
   Time: 73032
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

同样，我们看看从库的线程，有两个，一个I/O线程，一个SQL线程：

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 4
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 73422
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 5
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 72417
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL

从另一个服务器开始复制
上述的复制是假定主库和备库都是刚刚安好并且都是默认的数据库，也就是说俩台服务器上的数据是相同的，并且知道当且主库的二进制文件。这不是典型的案例，大多数情况下是有已经运行了一段时间的主库，然后用一台新安装的备库与之同步，此时这台备库还没有数据。一般有以下几个条件来让主库和备库保持同步

• 在某个时间点的主库的数据快照
• 主库当前的二进制文件，和获取数据快照时在该二进制日志文件中的偏移量，我们把这俩个值称为日志文件坐标。通过这俩个值可以确定二进制日志的文件。可以通过 show master status 命令获取这些值。
• 从快照时间到现在的二进制文件。

备份可以使用mysqldump命令。以下命令会将备份导出并执行。mysqldump 详解 也可以使用mysql 命令导出。

mysqldump  --single-transaction --all-databases --master-data=1 -uroot -p123456|mysql --host localhost  -uroot -p123456


mysql -h 172.29.6.206 -u root -p -e "select * from app_db.app_measure_detail where category_id = 97" > app_measure_detail.sql

复制类型

1、基于语句的复制 Statement-base Replication（SBR）statement

也称作逻辑复制，
在Master上执行的SQL语句，在Slave上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。

优点是只需要记录会修改数据的sql语句到binlog，减少binlog日志量（如果修改了一个表的记录，也只是一条SQL的记录），节约I/O 。不仅能用于复制，还能实时还原数据库。因为它记录了所有的SQL，还原数据库就很容易。
缺点是如果一个语句很复杂，那么slave执行的时候就会很耗资源，而基于行复制的话，只会记录变更的行记录。还有UPDATE语句在WHERE没有使用索引的情况下，会比基于行使用更多的行锁

2、基于行的复制 rows
把改变的内容复制到Slave，而不是把命令在Slave上执行一遍。从MySQL5.0开始支持

优点是 只会记录变更的行记录，哪怕一个语句很复杂，但是它最后只影响几条记录，那么行的复制，只会把影响到几条记录记录到binlog，降低slave重放日志时的资源消耗。
缺点是：它的日志很大，因为可能一条SQL修改了一个表的记录，但是它要把表的所有变更的记录都记录下来。而且它无法看到执行了什么SQL，不利于数据库的还原

3、混合类型的复制 mixed
默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制
混合方式就是有mysql自动选择RBR方式和SBR方式，能够充分发挥两种方式的优点，一般情况下都使用该种方式实现主从复制

复制文件

mysql-bin.index

当在服务器上开启二进制日志时，同时会生成一个和二进制日志同名但以.index后缀的文件，该文件用于记录磁盘上的二进制日志文件。这里的index 并不是指表的索引，而是说这个文件的每一行都包含了二进制文件的文件名。MySQL 依赖于这个文件，除非在这个文件里有记录，否则MySQL识别不了二进制日志文件。

mysql-reply-bin-index

这个文件是中继日志的索引文件，和mysql-bin.index类似。

master.info

这个文件用于保存备库连接到主库需要的信息，格式为纯文本。该文件以文本的方式复制了用户的密码，因此需要注意此文件的权限控制。

replay-log.info

该文件包含了当前备库复制的二进制文件和中继日志坐标。同样不严删除，否则备库重启后不知道从哪个位置开始复制，可能会导致重放已经执行过的语句。

发送复制事件到其他备库

log_slave_updates 可以让备库变成其他服务器的主库。MySQL会将该执行过的事件记录到他自己的二进制文件

复制拓扑

基本准则：

• 一个MySQL备库只能有一个主库。
• 每个备库必须有唯一的服务器id
• 一个主库可以有多个备库，相应的，一个备库可以多个兄弟备库
• 如果打开了log_slave_updates选项，一个备库可以把其主库上的数据变化传播到其他备库。

一主多备

当存在少量的写时和大量读时，该配置很有用。可以将读分摊到多个备库上

主动模式下的主主

主-主模式包含俩台服务器，每一个都被配置成对方的主库和备库，话句话说，他们是一对主库

该模式通常用于特殊的场景。一个可能的场景是由于处于俩个处于不同地理位置的办公室，并且都需要一份可写的数据拷贝。该配置的最大问题是如果结局冲突，俩个可写的互主服务器导致的问题非常多，这通常发生在俩台服务器同时修改一行记录，或同时在俩太服务器上像一个包含atuo_increment 列的表里插入数据。5.0以后增加了一些配置可以是该拓扑结构安全点，就是设置 auto_increment_increment 为2 ，auto_increment_offset 设置为2.

被动模式下的主主

该模式是前面的主主结构下的辩题，区别在于其中一个服务器是只读的被动服务器。

这种模式使得反复切换主动和被动服务器非常方便，应为服务器的配置是对称的。这使得故障转移和故障恢复很容易，它可以让你在不关闭服务器的情况下执行维护，优化，升级操作系统或其他任务。例如：执行alter table 操作可能会锁住整个表，阻塞对表的读和写，这可能会花费很长时间并导致服务终端。然而在主主配置下，可以先停止主动服务器上的备库复制线程（这样就不会再被动服务器上执行任何更新），然后在被动服务器上执行alter 操作，交换角色，最后在先前的主动服务器上启动复制线程。这个服务器上回读取中继日志并执行相同的alter语句。这可能话费很长时间，但不要紧，因为该服务器并没有为任何活跃查询提供服务。

拥有备库的主-主结构

环形结构

双主结构实际上是环形结构的一种特例，环形接口可以三个或更多的主库。每个服务器都是它之前的服务器的备库，是在它之后的服务器的主库。这种结构也被称为环形结构。

该结构可用性不高，如果移除一个节点，那么整个链条就都断了。环形结构非常脆弱，应尽量避免。

主库、分发主库以及备库

当备库足够多时，会对主库造成巨大的负载，每个备库都会在主库上创建一个线程，并执行binlog dump命令。该命令会读取二进制日志文件中的数据并将其发送到备库。每个备库都会重复这样的工作，他们不会共享binglog dump 的资源。

因此，如果需要多个备库，一个好办法就是从主库移除负载并使用分发主库。分发主库实际上也是一个备库，它的唯一目的就是提取主库中的二进制文件。多个备库连接到分发主库，这使原来的主库摆脱了查询。为了避免在分发主库上做实际的查询，可以将他的表改为blackhole存储引擎。

如果有需要的话，也可以使用多个分发主库或者是金字塔状的分发主库。

树或金字塔形

这种设计的好处是减轻了主库的负担，缺点是：中间层出现的任何错误都会影响到多个服务器。中间层次越多，处理故障会更困难，更复杂。

复制场景

选择性复制

将主库的不同数据复制到不同的备库中，减少了主库的读压力，并且对备库的写压力没有那么大。而且备库可以将需要读的工作集驻留在备库中。

只读备库

分离功能

将大数据分析与大数据事务到指定的备库上运行。

创建日志服务器

维护

监控复制

主库： show maste status
从库：show binlog events

备库延迟

percona toolkit pt-heartbeat

确定主备一致

percona toolkit pt-table-checksum

从主库重新同步备库

• 传统方法：关闭备库，重新从主库复制一份数据
• 使用mysqldump转储受影响的数据并重新导入。如果受影响的行数很大，会很耗时
• 通过percona 的pt-table-sync

改变主库

计划内的

1. 停止当前主库上的所有写操作。
2. 通过flush tables with read lock 在主库上停止所有活跃的写入。也可以在主库上设置read_only选项
3. 选择一个备库作为新的主库，并确保他已经完全跟上主库
4. 确定新主库和旧主库数据是一致的
5. 在新主库上执行stop slave
6. 在新主库上执行change master to master_host=’’ ,然后在执行reset slave，使其断开与老主库连接,并丢弃master.info中记录的信息
7. 执行show master status 记录新主库的二进制日志坐标
8. 确保其他备库已经追报上
9. 关闭旧主库
10. 在mysql5.1及以上版本，如果需要，激活新主库上事件
11. 将客户端连接到新主库
12. 在每台备库上执行change master to 语句，使用之前的show master status 获取的二进制坐标，来指向新的主库。

计划外的

1. 确定那台备库的数据最新。检查每台备库上show slave status 命令的输出，选择其中master_log_file/read_master_log_pos 的值最新的那个
2. 让所有备库执行完从崩溃前的旧主库那获得的中继日志。
3. 执行上述的5-7
4. 比较每台备库和主库上的master_log_file/read_master_log_pos值
5. 执行上述10-12步。

MySQL 扩展

数据分配方法（用于分片）

都需要一个分区函数，使用行的分区键作为输入，返回存储改行的分片。

1. 固定分配，使用hash 或者其他函数。优点是：简单，开销低，甚至可以直接在应用中硬编码。缺点：很难平衡不同分片间的负载，对于热点数据没有办法，修改分策略比较困哪，需要重新分配已有数据。
2. 动态分配。通过表或者是其他手段，将每个单元映射到一个分片。例如有一个俩列的表，包括用户id和分片id，通过该表即可以查询到相关信息。好处：对数据存储位置进行细粒度的控制，使的均衡分配数据到分片更加容易，灵活性高。

唯一id生成

• 使用auto_increment_increment和auto_increment_offset
• 全局节点中创建表。
• 使用memcached，使用incr 函数，或者是redis
• 批量分配数字，从一个全局节点请求一批数字，用完再申请
• 使用复合值，例如将id 的前8位当做分区号，然后在加上当前自增id
• 使用UUID

复制读写分离

最大的问题是如何避免由于独立脏数据引起的奇怪问题。

1. 基于查询分离。将所有不能忍受脏数据的读和写查询分配到主动或主库服务器。其他的读查询分配到备库或主动服务器上
2. 基于脏数据进行分离。让应用检查复制延迟，以确定备库数据是否太旧。许多报表类应用都使用这个策略，只需要晚上加载的数据复制到备库就行，他不太关心是否100%跟上了主库
3. 基于回话分离。判断用户是否修改了数据，用户不需要看到其他用户的最新数据，但需要看到自己的更新。可以在回话层设置一个标记位，表明做了更新，九江该用户的查询在一段时间内总是指向主库。可以与复制延迟监控结合起来，如果用户在1前跟新了数据，而所有备库延迟在5秒内，就可以安全的从备库中读取数据。
4. 基于版本分离。可以跟踪对象的版本号或者时间戳，通过从备库读取对象的版本或时间戳来判断数据是否是最新的。如果不是最新的，则从主库中去读
5. 基于全局版本/回话分离。 这个方法是基于版本分离和回话分离的变种。当执行写操作时，在提交食物后，执行一次show master status 操作，然后在缓存中存储日志坐标，作为被修改对象或者回话的版本号，当连接到备库中，执行show slave status 并将备库上的坐标和缓存中的版本号相比。如果备库相比记录点更新，就可以安全的读取备库数据。

异步复制，全同步复制，半同步复制

https://database.51cto.com/art/201911/606556.htm