mysql 复制 高性能mysql笔记

 

mysql 复制的两种方式

    1：行复制(对于网络延迟比较高的行复制也可以工作(因为基于行复制所以不会受到复制延迟带来的语句错误影响)，行复制数据多会对宽带造成压力。

    2：语句复制(也称为逻辑复制)

    mysql的复制是向后兼容(向前不兼容)的新版本的数据库可做为老版本的备库

    

    

复制可以解决的问题

    1，数据分布

        在不同的地理位置来分布数据备份，例如不同的数据中心。

    2，负载均衡

       通过MySQL复制可以将读操作分布到多个服务器上实现负载均衡。

    3，备份

        对于备份来说，复制是一项很有意义的技术补充，但复制既不是备份也不能够取代备份。

    4，高可用性和故障切换

        避免MySQL单点失败,缩短宕机时间。

    5，MySQL升级测试

       使用一个更高版本的MySQL作为备库，保证在升级全部实例前，查询能够在备库按照预期执行。

 

复制如何工作

简易流程：

1，在主库上把数据更改记录到二进制日志（Binary Log）中（这些记录被称为二进制日志事件）。

2，备库将主库上的日志复制到自己的中继日志（Relay Log）中。

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

 

具体流程：

 

       第一步是在主库上记录二进制日志。每次提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。在记录进二进制日志后，主库后告诉存储引擎可以提交事务了(MySQL会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志)。

 

       第二步备库将主库的二进制日志复制到其本地的中继日志中首先，首先，备库会启动一个工作线程(称为I/O线程)I/O线程与主库建立一个普通客户端连接,然后在主库上启动一个二进制

转储(binlog dump )线程(该线程没有对应sql命令),这个二进制转储线程会读取主库上的二进制日志事件，他不会对事件轮训，如果该线程追上了主库，它将进入休眠状态直到主库发送信号通知有新的二进制日志事件才会被唤醒，备库的i/o线程会将接受到的事件记录到中继日志中。

 

       第三步该线程从中继日志中读取事件并在备库执行，从而实现备库数据的更新。当SQL线程追赶上I/O线程是，中继日志通常已经在系统缓存中，所以读取中继日志开销很低sql线程可以通过配置把更新事件记录到自己的二进制日志

 

配置复制

1，复制账号。

在主库和备库都创建该账号：

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*

-> TO repl@'192.168.0.%' IDENTIFIED BY 'p4ssword';

其中：

• REPLICATION SLAVE  ：授予slave库连接master后，可执行replicate操作的权限。
• REPLICATION CLIENT ：复制用户可以使用 SHOW MASTER STATUS, SHOW SLAVE STATUS和 SHOW BINARY LOGS来确定复制状态。
• 在主库和备库都创建账号原因：方便切换slave和master，账户不需要做任何改动。
• 对同一账号赋予REPLICATION SLAVE和REPLICATION CLIENT 原因：方便监控管理不必要为了监控再创建一个账号。

    2，配置主库和备库。

 

配置master：

在配置文件my.cnf加入以下下值：

1，打开二进制日志。

2，指定唯一的servr ID。

log-bin=mysql-bin

server-id=10 

  3,重启MySQL。  

配置slave：

Slave的配置与master类似，你同样需要重启slave的MySQL。如下：

log_bin           = mysql-bin

server_id         = 2

relay_log         = mysql-relay-bin

log_slave_updates = 1

read_only         = 1

• server_id:(默认名称是主机名)必须指定并且唯一。

• relay_log:指定中继日志的位置和命名。

• log_slave_updates:表示允许备库将复制执行的事件也记录到自身的二进制日志中。

有时开启slave的二进制日志，却没有设置log_slave_updates，会碰到奇怪现象，如当配置错误时可能会导致备库数据被修改。最好使用使用read_only配置选项，该选项会阻止没有任何特权权限的线程修改数据。但read_only通常不是很实用，特别是那些需要在slave上创建表的应用。

        注意:

• 某些mysql版本不允许server-id的值为1 。
• 不要再my.cnf设置master_prot master_host ，在my.cnf中配置后无法在线重新设置master_prot。

    启动复制：

     语句：

mysql> CHANGE MASTER TO MASTER_HOST='server1',

MASTER_USER='repl',

MASTER_PASSWORD='p4ssword',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=0;

MASTER_LOG_POS=0： 是指要从日志开头读取

校验：用SHOW SLAVE STATUS语句查看slave的设置是否正确

mysql> SHOW SLAVE STATUS\G



*************************** 1. row ***************************

             Slave_IO_State:

                Master_Host: server1

                Master_User: repl

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: mysql-bin.000001

        Read_Master_Log_Pos: 4

             Relay_Log_File: mysql-relay-bin.000001

              Relay_Log_Pos: 4

      Relay_Master_Log_File: mysql-bin.000001

           Slave_IO_Running: No

          Slave_SQL_Running: No

                             ...omitted...

      Seconds_Behind_Master: NULL

开始复制运行：

 

mysql> START SLAVE;

 

运行SHOW SLAVE STATUS查看输出结果：

 

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event

                Master_Host: server1

                Master_User: repl

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: mysql-bin.000001

        Read_Master_Log_Pos: 164

             Relay_Log_File: mysql-relay-bin.000001

              Relay_Log_Pos: 164

      Relay_Master_Log_File: mysql-bin.000001

           Slave_IO_Running: Yes

          Slave_SQL_Running: Yes

                             ...omitted...

      Seconds_Behind_Master: 0

注意，slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master不再是NULL。日志的位置增加了，意味着一些事件被获取并执行了。如果你在master上进行修改，可以在slave上看到各种日志文件的位置的变化，也可以看到数据库中数据的变化。

            检查线程：show processlist

添加新的slave数据库：

      需要三个条件让主备保持同步:

1. 某个时间点快照

2. 主库当前二进制日志文件和主库当前二进制日志的快照时间点的偏移量这两个值可以叫做日志文件坐标(log file coordinate)，因为它们确定了一个二进制日志的位置，你可以用SHOW MASTER STATUS命令找到日志文件的坐标；

3. 从快照时间到现在的二进制日志。

 

可以通过以下几中方法来克隆一个slave：

       (1) 冷拷贝(cold copy)

            停止master，将master的文件拷贝到slave；然后重启master。缺点很明显需要停止master。

       (2) 热拷贝(warm copy)

            如果你仅使用MyISAM表，你可以使用mysqlhotcopy拷贝，即使服务器正在运行。

       (3) 使用mysqldump  从server1 复制到 server-slave

           

 mysqldump --single-transaction  --all-databases --master-data=1 --host=server1 | mysql --host=server-slave

选项 --single-transaction 使得转储的数据为事务开始前的数据。如果使用的是非事务型表，可以使用--lock--all-tables选项来获得所有表的一致性转储。

    (4) 使用快照备份

        1备份快照， 2 SHOW MASTER STATUS 找到坐标， 3在备库启动快照 并且 CHANGE MASTER TO 指定二进制日志的坐标。

    (5) 使用xtrabackup

此工具能够在备份时不阻塞服务器，通过克隆主库建立备库。如果是从主库获得备份，可以从xtrabackup_binlog_pos_innodb文件中获得复制开始的位置。如果是从另外的备库获得备份，可以从xtrabackup_slave_info文件中获得复制开始的位置。

    注意：不要使用LOAD DATA FROM MASTER 或者LOAD TABLE MASTER 这些命令过时、缓慢，并且非常危险，并且只适用于MyISAM存储引擎。

 

复制配置

在主库上二进制日志最重要的选项是sync_binlog：

sync_binlog=1 (只适用于二进制日志不适用中继日志)如果开启该选项，MySQL每次在提交事务前会将二进制日志同步到磁盘上，保证在服务器奔溃时不会丢失事件但是会增加性能开销。当服务器崩溃会导致myisam引擎的表损坏，如果使用InnoDB，下列强烈推荐使用：

• innodb_flush_logs_at_trx_commit：每隔几秒把事务日志缓存区的数据写到日志文件中并刷新到磁盘上。
• innodb_support_xa=1   
• innodb_safe_binnlog

在备库上，推荐开启如下配置选项，为中继日志制定绝对路径：

• relay_log=/path/to/logs/relay_bin
• skip_slave_start
• read_only

 

通过设置relay_log可以避免中继日志文件基于机器名来命名制定绝对路径可以避免多个MySQL版本中存在的BUG，这些BUG可能会导致中继日志在一个意料外的位置创建。skip_slave_start选项能够阻止备库在崩溃后自动启动复制。read_only 选项可以阻止大部分用户更改非临时表.

在mysql5.5中不介意fsync开销可开启如下防止中级日志不刷新到磁盘

sync_master_info = 1

sync_relay_log = 1

sync_relay_log_info = 1

如果主备延迟很大，备库的I/O线程可能会写大量中继日志，SQL线程在重放完一个中继日志中的事件会尽快将其删除（通过relay_log_purger选项来控制）。但如果延迟非常严重，I/O线程可能会把整个磁盘撑满。解决办法是配置relay_log_space_limit变量。如果所有中继日志的大小之和超过这个值，I/O线程会停止，等待SQL线程释放盘空间。

但是如果配置了relay_log_space_limit 而主备延迟很大，假如主库崩溃则会导致主备不同步(主库崩溃主库二进制文件没有传递到备库)

 

复制的原理

 

基于语句的复制:

优点：

1,会使得二进制文件更加紧凑更小方便传输。

缺点：

1，有一些语句执行会产生错误比如时间戳 now()此时两个机器是不一样的。

2，更新必须是穿行的这就需要加锁但不是所有的存储引擎都支持加锁。

基于行的复制:

优点：

1，可以减少锁的使用。

2，对于所有的SQL构造、触发器、存储过程等都能正确执行。只是当你试图做一些诸如在备库修改表的schema这样的事情时才可能导致复制失败。

3，基于行的复制能够更快地找到并解决数据不一致的情况。如果是使用基于语句的复制模式，在备库更新一个不存在的记录时不会失败，但在基于行的复制模式下则会报错并停止复制。

4，占用更少的cpu。

缺点：

1，语句无在日志记录，难以判断那些sql更新过程，像黑盒子，很难知道问题所在。

2，传输占用更多的带宽占用更多i/o。

复制过滤：

主库：

binlog_do_db=A

binlog_do_db=B

binlog_ignore_db=test

binlog_ignore_db=test2

    注意：多个数据库写多条配置 最好不要在主库上配置过滤 因为主库上配置指定某个库记录二进制文件，当其他库发生问题时由于没有记录到二进制文件所以无法从二进制文件恢复。所以不建议配置

 

备库：

Replicate_do_db

Replicate_do_table

Replicate_ignore_db=db1.t1% ##过滤db1库中t1开头的表

Replicate_ignore_table

Replicate_rewrite_db

Replicate_wild_do_table

Replicate_wild_ignore_table

注意：在备库配置的时候要注意主库的use

 比如 从库 replicate-ignore-db = db2

 主库上 use mysql

 主库执行 delete from db1.tb1

 此时 是不会复制到备库执行  delete from db1.tb1  因为已经被过滤了

 原因：设置了 replicate-ignore-db = db2 凡是在db2(use db2)上执行的都会被过滤.

复制拓扑图

复制的体系结构有以下一些基本原则：

(1)    每个slave只能有一个master；

(2)    每个master可以有很多slave；

(3)    每台机器必须有唯一的服务器ID；

(4)    如果你设置log_slave_updates(从库执行复制打印到二进制日志)，slave可以是其它slave的master，从而扩散master的更新

 

 

一主多备结构：

 

一主多备是一种比较灵活的方式也是最常见的方式，不同的 备库 用作不同功能：

1：使用不同的索引增加性能

    2：某个库用作数据统计以增加性能

    3：某个库用作容灾

    4：某个库用作主备切换

    5：某个库用作测试开发

 

主主复制结构：

这种结构存在一些问题导致数据不一致：

1：比如两台auto_increment 自增id都从0开始会导致重复错误。

2：一台机器执行update t set a=1 where a=2； 另一台机器执行 update t set a=3 where a=2；此时如果语句复制就会数据不一致。

 

环形结构：

这种结构比较脆弱一环失败环环失败它也存在主主机构的问题。

 

主主被动机构：

两个主库其中一个是只读主库。

这种结构是的主备切换非常方便避免了主主结构的缺点，

 

树形金字塔形：

优点：可以减少主库向多个备库复制的压力转而由备库去做其他的备库分发。

缺点：出现不同步的问题排查将更加困难。

 

复制方案：

1. 选择性复制：把主库中不同功能 复制到 不同的备库，让备库去提供读取，让备库作为全文检索。

2. 分离功能：把一些查询分给专门的备库，并为备库定制化硬件和索引和引擎。

3. 数据归档：主库上某些数据可以放到备库然后从主库删掉 删除的时候注意不要让删除进入到二进制日志不然备库也会删除(设置sql_log_bin为0在)。

4. 模拟多主库复制：间隔一段时间把从库从主库1设置为主库2 然后间隔时间再设置回来 。

管理和维护

复制的监控：

• 可以使用show master status 查看主库的二进制日志位置和配置。
• 可以使用show master logs   查看主库哪些二进制日志在磁盘上从而决定一台新的从库 执行 pugre master logs 的参数。

测量复制延迟：

虽然SHOW SLAVE STATUS输出的Seconds_behind_master列理论上显示了备库的延时，但由于各样的原因，并不总是准确的：

 

1.备库Seconds_Behind_Master值是通过将服务器当前的时间戳与二进制日志中的事件时间戳相对比得到的，所以只有在执行事件时才能报告延时。

 

2.如果备库复制线程没有运行,就会报延迟null。

 

3.一些错误（例如主备的max_allowed_packet不匹配，或者网络不稳定）可能中断复制并且/或者停止复制线程，备库有时可能无法计算延时。如果发生这种情况备库会报0或者null

 

4.一个大事务可能会导致延迟波动，例如，有一个事务更新数据长达一个小时，最后提交。这条更新将比它实际发生时间要晚一个小时才记录到二进制日志中。当备库执行这条语句时，会临时地报告备库延迟为一个小时，然后又很快变成0

 

5.如果分发主库落后了，并且其本身也有已经追赶上它的备库，备库的延迟将显示为0，而事实上和源主库是有延迟的。

 

解决办法：

使用 heartbeat record(自定义一个心跳检查)，在主库建立一个表 tb_heart 然后每秒插入一个值(系统当前时间秒) 然后对比从库的 tb_heart 最大值和主库的差距。

确定主备是否一致：

复制延时或网络问题并总是会让主备数据不完全一致。主备一致应该是一种规范，检查你的主备一致性应该是一个日常工作，特别是是当使用备库来做备份时尤为重要。

 

1：Percona Toolkit里的pt-table-checksum能够用于确认主备库数据是否一致。

2：mysql提供了checksum table来校验主备表是否一致(复制进行时不可用)

解决不同步：

1.如果数据量小可以用mysqldump 转储 或者修改语句。

2.Percona Toolkit的pt-table-sync

改变主库

 

     计划内提升:

1.停止主库上的写操作，最好关闭所有客户端程序。

2.通过设置read only来停止活跃的写入，不过read only不能禁止超级用户写入。通过flush tables wity readlock可以让任何用户都不可写入。flush tables with read lock作用是将所有脏页刷新到硬盘并关闭所有表并给所有表加上读锁。实际上分为三个步骤：给全局加上读锁避免更新；刷新并关闭所有表缓存，如果存在查询或者更新正在运行那么会进入waiting for flush tables状态等待；加全局commit锁保证数据一致性。第一步和第三步的两个锁都属于MDL锁，即元数据锁，该锁可以保证事务正在操作表时，表不会被DDL语句修改删除等。

3.选择一个备库作为主库(一般选择复制最接近主库的那个)，并确保它已经跟上主库。

4.确保主备库数据一致。

5.新主库上stop slave。

6.新主库上重置master info信息。 change master to master_host='' 然后执行 reset slave , 断开与老库链接并丢弃master.info（如果链接信息在my.cnf则无法正常工作）

7.show master status记录新主库binlog坐标。

8.确保其他备库赶上。

9.将客户端脸上新主库。

10.每个备库change master to。

计划外提升：

主要是处理主库崩溃的情况。

1.重启主库binlog如果可以用，如果可以用那么然备库跟上主库的binlog，

2.如果无法重启那么就选取备库中日志坐标最接近主库的那个备库当作新主库。

1.让备库运行完毕中继日志。

2.检查每台备库show slave status  选择master_log_file/red_master_log_pos 最新的做主库。

3.按照上面计划内执行切换。

主主切换:

1.停止主动一方的写入。

2.在主动 设置set global read only=1,同时在配置文件中也设置避免重启失效但是这无法阻止管理员更改可以设置 flush tables with red lock 会阻止所有写入。   

3.在主动一方show master status记录binlog位置。

4.在被动一方根据上一步位置执行select master pos wait()该语句将阻塞主知道复制跟上主动服务器。

5.被动一方set read only=0变成主动一方。

6.修改客户端配置，使其写入主动一方。

复制的问题和解决方案：

主库意外关闭：

1：没有设置sync_binlog 崩溃后可能有二进制日志 a 没有刷新到磁盘，备库处于读不到a的状态，主库重启备库链接主库后尝试去读 a 此时会卡在这里.

           解决这个问题需要重新设置 备库 从下一个日志偏移量开始读

2：开启了syn_binlog myisam的表仍有可能会在崩溃时损坏，innoDB 没有设置innodb_flush_log_at_trx_commit也会丢失数据但是表不会损坏。

备库意外关闭：

备库意外关比并重启后,会去重新读取master.info文件以找到上次停止复制的位置.但是此时该文件并没有同步写入到磁盘,这时候文件中的信息就可能是错误的.

备库常常会重新执行一些二进制事件,这可能会导致主键冲突,唯一索引冲突等.唯一的办法就是忽略错误.

解决办法:

1：可以使用工具pt-slave-restart,

2：如果是innodb表可以重启后观察错误日志

找到二进制日志坐标,可以使用这个值重新指向主库的便宜量,然后再用pt-table-checksum检查数据是否一致

建议事项：

skip_slave_start=1

#能够阻止备库崩溃后自动启动复制,这样可以为你提供足够的时间修复可能出现的问题 

sync_master_info     =1 #默认值为10000即每10000次sync_relay_log事件会刷新到磁盘

sync_relay_log         =1 #如果不介意额外的fsync()导致的性能问题可以都设置

sync_relay_log_info  =1 #成1 即1次事件就刷新至磁盘

 

二进制日志损坏

主库二进制日志损坏

解决方法：

   只能忽略损坏的位置,可以在主库上执行flush logs 然后备库指向该文件的开始位置.

备库二进制日志损坏

解决方法：

   通过CHANGE MASTER TO 丢弃并重新获取损坏的事件,只要将备库指向它当前正在复制的位置（根据Relay_Master_Log_File/Exec_Master_Log_Pos确定）,这样会导致丢弃所有在磁盘上的中继日志.

 

二进制日志于InnoDB事务日志不同步: 除非备库中继日志有保存,否则自求多福

1：syn_binlog =1

 

对于没事务的复制

如果是基于语句的复制 当更新100条数据 在更新到50条的时候进程被kill了主备将不同步因为语句还没更新到二进制日志。

避免混用事务和非事务:

如果备库发生死锁而主库没有,事务型会回滚而非事务型则不会造成不同步   

不确定的语句

比如 insert ignoer 在拥有多个唯一索引的表中更新可能会 主备库可能选择不同索引使得数据不同或者 update t_a set b=now()

主备使用不同引擎

基于语句复制如果使用不同存储引擎可能导致查询结果不同(但是不同引擎可能会为备库作为查询库 带来一些性能提升)

备库数据发生改变

把备库设置为read_only

不唯一的服务器id

小心设置服务器id

临时表丢失

备库意外关闭导致临时表丢失 可以新建和临时表相同的表 然后执行同步完毕后删除

InnoDB加锁读引起的锁争用:

将大命令拆成小命令可以有效减少锁竞争

过大的复制延迟:

1：定位执行慢的语句,改善机器配置

 

    2：大事务拆小事务如果说大事务对于binlog的产生有极大的影响，那么我们认为定义小事务，大事务不允许执行有大事务的监控，可以基于时间，可以基于数据量，监控到不符合规范的trx自动kill

 

3：大量并发事务

 

1：sync_binlog = 0 && innodb_flush_trx_commit = 0 可以极大的提高事务处理的吞吐量，因为IO fsync的次数变少了，可以非常有效的降低数据延迟

 

风险：如果slave挂了，需要重做slave

 

   2：MTS(enhanced multi-threaded slave)

 

3：半同步： 半同步可以让延迟为0，但是半同步有自动切换为异步复制的可能

 

4：全同步： MySQL的group replication 就是这类的代表，这个话题以后再聊

 

mysql复制的半同步和全同步

异步复制：

 

MySQL复制默认是异步复制，Master将事件写入binlog，提交事务，自身并不知道slave是否接收是否处理；

缺点：不能保证所有事务都被所有slave接收。

 

半同步：

    当事务提交时在客户端接受到查询结果之前，必须保证二进制日志至少传输到一台设备上，主库将事务提交到磁盘后会增加一些延迟

当Master上开启半同步复制功能时，至少有一个slave开启其功能。当Master向slave提交事务，且事务已写入relay-log中并刷新到磁盘上，slave才会告知Master已收到；若Master提交事务受到阻塞，出现等待超时，在一定时间内Master 没被告知已收到，此时Master自动转换为异步复制机制；

注意

1：备库接受到事务就反馈而不是完成。

2：半同步不总是可以工作时间太长会转异步

3：备库不会阻塞主库事务提交只会阻塞主库通知客户端时间。

 

全同步：

Master提交事务，直到事务在所有slave都已提交，才会返回客户端事务执行完毕信息；

 

缺点：完成一个事务可能造成延迟。