IV 7 MySQL REPLICATION

一、相关概念：

日志（二进制日志、事务日志、错误日志、一般查询日志、中继日志、慢查询日志）

二进制日志（记录引起或潜在引起数据库database发生改变的操作；用于做即时点恢复（即时点还原）；位置（数据目录下）；滚动（达到最大上限；执行>flush logs；重启服务）；删除>purge to（不可用#rm命令直接删除）；格式（statement、row、mixed）；相关命令（>show master status;、show binlog eventsin FILE;、show binary logs;）

二进制日志并不能保证重放后的数据与原数据库数据完全一致，它替代不了备份（即时点还原的数据可能会与原数据不完全一致，主机有多颗CPU，DB的storage engine支持事务，多个事务在执行时是并行的，但当前往二进制日志文件中写的仅一个，只要往里写就是串形的，同时每个事务执行时若隔离级别低的话会交叉执行（事务隔离级别低会产生影响），所以导致结果很可能与原数据不完全一致，若要完全一致，将原始数据复制一份，这样产生的影响最小）

隔离级别（read uncommitted；read committed；repeatable read（mysql默认此项）；serializable

（repeatable read+statement）或（{readuncommitted,read committed}+mixed）

不建议使用以上这几种方式，二进制日志记录的事件再跑一遍极大可能数据不一致，而要使用（repeatable read+{row,mixed}）或（{readuncommitted,read committed}+row）

注：statement（mysql官方已不建议使用），mysql5.X建议使用row

在某些场景下，数据库不一致会导致异常，所以最好将数据库保持一致

 

master-->slave（在master上，前端用户或程序app每执行一个引起变化的语句都会写入当前DB并记录到二进制日志中保存为事件，然后将保存的事件（二进制日志）通过3306端口发给slave（slave不接受前端用户或app的更改操作），slave接收下来保存在本地的中继日志中relay-log，从relay-log中每次读一个事件并执行，将结果保存至本地的DB中

mysql中一个查询只能在一个CPU上运行，若主机是四核的，事务在每个CPU上运行这是并行的，事务运行是若是写操作，往DB中写这很快，但往二进制日志中记录时，要先在内存的缓冲区中，过一会才会同步到二进制日志文件（同步时是一条一条保存的），只有同步到二进制日志文件中才能一条一条地传给slave，slave再开始一条一条地保存至relay-log进而一条一条地执行，所以master要比slave快的多，有时slave要比master慢半小时以上

默认mysql复制replication是异步的（只要写入DB就返回成功，假如是同步的，前端用户或app可能要等很长时间）

mysql的半同步（master仅保证将二进制日志传给最近的一个slave，同步完成即返回成功；mysql允许一主多从，与DNS类似）与drbd的半同步（传至slave的TCP/IP协议栈即返回成功）是两码事

mysql5.5之前版本不支持半同步semi-sync（semi-sync是由google提供的插件）

slave上本身可执行写操作，但在master-slave架构中，slave上若写了数据，它不能同步到master上，这会导致两端DB不一致，所以一般禁止slave上写

master上的二进制日志与slave上的relay-log在大小和个数上会不一样（bin log和relay log的格式是一样的），因为master上的mysql服务若重启则会自动滚动记录下个日志

若不是多级复制，slave上可不要二进制日志（例如master-->slave1-->slave2，slave2是不需要bin log，中间的slave1上bin log和relay log都要有）

 

master-master（双主模型，mysql支持但在生产中不建议使用，两个mysql都可写，要解决同时读写带来DB不一致状况，类似高可用的CFS，但这对于关系型DB太复杂了，在某一node执行的事务有很多，要将状态通知给另一node，事务执行时将一部分语句发给node1一点，再将一部分语句发给node2一点，两端还要通知，事务还要保证原子性atomicity（要么都执行要么都不执行），所以很混乱）

server-id（至关重要，解决循环复制，每个node的server-id要唯一，接收另一node发来的二进制日志与本地的比较，相同的不接收或接收下来不应用）

双主模型可以分摊读操作，但无法减轻写操作（每个node对于写操作绝不能少，否则两端数据不一致（这是致命的））

双主的缺陷：举例（对于一个表中name,age两个字段，在两个node上分别执行如下语句，一合并，DB会混乱）

两条记录：

tom 10

jerry 30

>UPDATE tutors  SET  name=’jerry’ WHERE  age=10;

>UPDATE tutors  SET  age=30 WHERE  name=’tom’;

 

replication的作用：

作冷备份（master若故障，将slave端停止服务并做备份，将数据拿到master上恢复即可，注意要记录master上二进制日志的最后位置用于即时点还原）

提供高可用功能（master若故障，将slave的写开启并将落后的二进制日志执行完，提升为主即可）

分摊负载/读写分离（让master只负责写，slave负责读，一般都是读多写少，除非在线事务处理，所以多加几台slave-server）

 

一主多从模型中，主写、从读，前端用户或app要连接mysql要找哪一个，中间要安装mysql proxy（它工作在应用层，要能精确理解某种协议，在这里它要理解每个SQL语句），通过mysql proxy可将前端user或app所要执行的操作定向至特定的server，这叫mysql的读写分离rw-splitting

若要在每个slave上实现负载均衡，在其前端加上director（LVS或haproxy）

对于master、mysql-proxy、director的高可用，可各自提供一台备用，也可只提供一台备用server，这三个node若某一故障使用这个备用server（高可用集群中N-m，N个server运行m个服务）

memcached（旁路缓存服务器，缓存每个slave上查询的数据，当前端user或app首次发起查询请求，memcached中若没有让user或app自己连接mysql-proxy到后端slave查询，查询出的数据先保存一份到memcached中，再返回给前端user或app，当下次查询同样内容直接从memcached中返回即可）

mysql自身有缓存，但在一集群中，每次请求可能会在不同的mysql-slave上，这时mysql自身缓存就没意义了，两种方式：持久连接；共享式缓存（memcached，它本身是个编程API，缓存功能靠程序自身来实现）

 

一主多从模型（按默认使用异步方式，若有10个从，那在master上就要启动10个复制线程dump用来同步二进制日志，这将导致master压力过大，解决方案：多级复制，单独拿出一台server（node1）作为架构中master的从，node1是其它10个从的主（主只有一个从，是node1，其它10个从是node1的从），注意在node1上必须要有relay log和bin log，其它10个从可仅有relay log）

 注：一主可以有多从，但一个从只能属一个主

node1-server（只负责发送二进制日志，用于减轻master复制的压力，并不负责读和写，但写操作相关语句的执行结果只有保存至DB中才能写入bin log，只要往DB中写就要增加系统性能用于磁盘IO，关键是它保存到DB中我们也不用它，解决方案：将node1的storage engine改为black hole（/dev/null））

 

一主多从架构中完成rw-splitting，必须要配置mysql-proxy（对于一主多从模型，若前端仅管理员使用，写时连接到master，读时断开之前连接再次重连至slave，这比较麻烦；但是在LAMP架构中，php开发出的程序（php本身与mysql并无关，是使用php语言开发出的程序，如discuz要用到数据时，通过驱动连接至DB才产生交互）要访问DB，若不使用mysql-proxy，如何让主的写从的读？解决方法：再次开发这个程序让其自身解决rw-splitting和LB

 

半同步semi-sync，master只确保离master最近的一台slave（一堆从中的一个）同步成功，若时间太长没有slave响应，则降级为异步模式继续工作

 

 mysql5.6引入GTID（global transaction identifer），使得mysql复制更安全、多事务执行时不会产生混乱、引入多线程复制（multi thread replication）

 

特殊情形，如mysql服务器已运行很长时间，现在要做主从复制，将master的DB备份，记录二进制日志文件名及事件位置，然后在slave上将备份还原，slave与master连接时指定说明master上哪个文件名及事件位置

 

复制线程（master（dump），slave（io_thread、sql_thread））

multi thread replication（是在从端启动多个sql_thread来完成多线程复制，一个库只能使用1个线程，多个库多线程并发执行这才有意义，若仅1个库就算开启多线程也仅使用1个线程复制）

每个从分别对应主的dump（若10个从，则在主端要启动10个dump，dump在有复制时会自动启动），io_thread会到master上查询二进制日志，若发现有数据要同步，则dump会发给io_thread，io_thread接收下来保存至relay log中，sql_thread每次从relay log中读一个事件，进行重放redo执行并在本地应用，若master上的dump不在线，则slave上的io_thread连不上dump就停了，而sql_thread仍能正常工作（io_thread不影响sql_thread，sql_thread只监控relay log中有无更新数据）

 

若复制时跨越互联网，要使用ssl（支持双向认证，client<-->server，dump<-->io_thread）

 

1、复制的作用：辅助实现备份；高可用HA；异地容灾；分摊负载(scaleout)；rw-spliting(mysql proxy工作在应用层)。

2、master有多个CPU允许事务并行执行，但往二进制日志文件只能一条条写；slave比master要慢；master-slave默认异步方式传送。

3、半同步：仅负责最近一台slave同步成功，其它的slave不管，5.5之前不支持半同步，半同步应指定timeout间隔，若超时则降级为异步模式继续工作。

4、slave-server本身可以写操作，但在master-slave架构中不允许slave写，因为它无法同步至其它server。

5、slave可向master做冷备份。

6、master的二进制日志文件和slave上的中继日志文件在文件大小和个数上都不会一样，还原只能用master的二进制日志文件，不能使用中继日志文件。

7、本地一定要有中继日志和二进制日志；slave-side只要不做多级复制，可不要二进制日志文件；多级复制可减轻master复制压力；slave-side存储引擎可用blackhole。

8、master-side若宕掉，可将slave-side提升为master（执行二进制日志），从而实现高可用。

slave-side，IO_thread用于从主端接收dump_thread发来的二进制日志语句然后保存为本地的中继日志，SQL_thread读中继日志转为数据文件成功后再保存二进制日志。

9、server-id避免循环复制。

10、双主无法减轻写操作。

11、主从架构中，不使用mysql-proxy，如何让master写，slave读：让程序（PHP开发出的程序）自身具有读写分离的功能；双主模型。

12、生产环境下不建议使用双主模型。

13、双主模型产生的问题：两人同时更改同一表的不同字段或插入数据，提交后可能会导致数据库崩溃或出现非常规错误。

14、数据库server压力大时，两种方案：scaleout；scale on

15、scale  out：根据业务分库，每个业务涉及到的库放到一个物理服务器上（垂直拆分），但数据有热区，例如，100G的数据，仅1个G很BUSY，其它很闲，而这1G的数据在一个表里；拆表（水平拆分），rid(row id)。

注：能不拆则不拆否则后续问题很难排查。

16、一个从只能有一个主，一主可以有多从。

17、读写分离：mysql-proxy、amoeba(java),配置文件xml格式

18、coba(amoeba)：数据拆分。

19、复制线程：master-side(dump)、slave-side(IO_thread、SQL_thread)

20、默认情况下MySQL的复制是异步的，Master上所有的更新操作写入Binlog之后并不确保所有的更新都被复制到Slave之上。异步操作虽然效率高，但是在Master/Slave出现问题的时候，存在很高数据不同步的风险，甚至可能丢失数据。

21、MySQL5.5引入半同步复制功能的目的是为了保证在master出问题的时候，至少有一台Slave的数据是完整的。在超时的情况下也可以临时转入异步复制，保障业务的正常使用，直到一台salve追赶上之后，继续切换到半同步模式。

 

 

注：主从同步中，是主库主动push推（而不是从库从主库上pull拉）

slave possible role：

failover server;

used for performancing backups;

read load balancing;

additional slaves allow scale-out;

 

二、MySQL5.5.45主从复制、半同步复制、数据库复制过滤，具体操作：

1、主从复制配置步骤：

master-side:

#vi /etc/my.cnf

[mysqld]

log-bin =  master-bin  （开启二进制日志）

log-bin-index  = master-bin.index  （定义二进制日志索引文件）

server-id =  1  （与slave-side不能一样，避免循环复制）

sync_binlog =  1  （此项用于事务安全，设定事务一提交就写入二进制日志文件）

innodb_flush_logs_at_trx_commit  = 1  （每事务同步）

innodb_file_per_table  = 1  （只要支持事务的此项必开，每表一个表空间）

datadir =  /mydata/data

log_format =  mixed

>GRANT REPLICATION  SLAVE  ON *.*  TO  ‘repluser’@’192.168.1.%’  IDENTIFIED BY  ‘repluser’;（创建用户具有复制权限，权限有replication slave（具有从master二进制日志复制的权限）、replication client（具有连接master并获取相关信息的权限））

>FLUSH PRIVILEGES;

 

slave-side:

#vi /etc/my.cnf

[mysqld]

relay-log =  relay-log

relay-log-index  = relay-log.index

server-id =  11

read_only =  1  （slave-side不允许写数据，仅读，但此项对有SUPER权限的用户不生效，可使用1|true|on|yes都可）

skip_slave_start  = 1  （服务启动不执行同步，待手动开启IO_THREAD和SQL_THREAD，用于master-side数据出错暂不往slave-side同步，等master-side数据正常，再手动开始同步）

>CHANGE MASTER  TO  MASTER_USER=’repluser’,MASTER_PASSWORD=’repluser’,MASTER_HOST=’192.168.1.222’,MASTER_LOG_FILE=’master_bin.000010’,MASTER_LOG_POS=107;

>START SLAVE;  （也可分开执行>START  SLAVE IO_THREAD;>START  SLAVE  SQL_THREAD;）若此步出现错误could notinitialization master info structure...执行>RESET  SLAVE;再重新执行>CHANGE  MASTER TO那条语句即可。

>SHOW SLAVE  STATUS\G  （查看IO_THREAD和SQL_THREAD是否为ON状态，Exec_Master_Log_Pos:为当前执行的位置，Seconds_Behind_Master:从比主慢的时间，Master_SSL_Allowed:是否启用用ssl）

>STOP SLAVE  IO_THREAD;  （master上数据若有问题时，可将slave-side的IO_THREAD停掉）

slave-side重启mysqld，IO_THREAD和SQL_THREAD会自动启动，数据目录下/mydata/data/{relay-log.info,master.info}这两个文件是replication的基础和前提，决定服务启动时从哪个地方开始读取，若不想让服务一启动就自动执行复制，可将这两个文件剪切至其它地方，再重新配置slave-side

[root@node2 ~]# cd /mydata/data

[root@node2 data]# file master.info

master.info: ASCII text

[root@node2 data]# file relay-log.info

relay-log.info: ASCII text

 

 

2、配置半同步复制：

#ll /usr/local/mysql/lib/

semisync_master.so  semisync_slave.so  （插件由google提供）

MySQL在加载并开启semi-sync插件后，每一个事务需等待备库接收日志后才返回给客户端。如果做的是小事务，两台主机的延迟又较小，则Semi-sync可以实现在性能很小损失的情况下的零数据丢失。 

 

master-side:

>INSTALL PLUGIN  rpl_semi_sync_master  SONAME ‘semisync_master.so’;

>SHOW GLOBAL  VARIABLES  LIKE  ‘%rpl_semi%’;

rpl_semi_sync_master_enabled  设为1

rpl_semi_sync_master_timeout  默认为10S  （如果主备网络故障或者备库挂了，主库在事务提交后等待10秒，无响应则自动转为异步状态）

>SET GLOBAL rpl_semi_sync_master_enabled=1;  （仅当前生效，可写入配置文件）

 

slave_side:

>INSTALL PLUGIN  rpl_semi_sync_slave  SONAME ‘semisync_slave.so’;

>SHOW GLOBAL  VARIABLES  LIKE  ‘%rpl_semi%’;

>SET GLOBAL rpl_semi_sync_slave_enabled=1;

>STOP SLAVE;

>START SLAVE;

 

master-side:

>SHOW GLOBAL  STATUS  LIKE  ‘%rpl%’;

Rpl_semi_sync_master_clients和Rpl_semi_sync_master_status要为打开状态，半同步复制才配置成功

 

slave-side:

>SHOW SLAVE  STATUS\G

查看Seconds_Behind_Master

 

 

3、数据库复制过滤：

master-side:

binlog-do-db  = DB_NAME（白名单，仅将指定某数据库的操作记入二进制日志）

binlog-ignore-db  = DB_NAME（黑名单，不记录某数据库的操作到二进制日志）

注：不建议在master-side操作，否则二进制日志不完整。

slave-side:

replicate-do-db  =  DB_NAME

replicate-ignore-db  = DB_NAME

replicate-do-table  = TABLE_NAME

replicate-ignore-table  = TABLE_NAME

replicate-wild-do-table  = TABLE_NAME  （支持通配符%和_）

replicate-wild-ignore-table  = TABLE_NAME

 

举例：在slave-side:

#vim /etc/my.cnf

[mysqld]

添加replicate-do-db  =  test1

replicate-ignore-db  = test2

#service mysqld  restart

>SHOW SLAVE  STATUS\G  （查看如下两项）

Replicate_Do_DB: test1

Replicate_Ignore_DB: test2

 

 

4、percona toolkit（https://www.percona.com/downloads/percona-toolkit/）

[root@node1 ~]# yum -y --nogpgchecklocalinstall percona-toolkit-2.2.16-1.noarch.rpm

或使用源码包安装，安装方法在安装目录下README文件中有说明：

  perl Makefile.PL

  make

  make test

  make install

[root@node1 ~]# pt<TAB>

pt-align                  pt-duplicate-key-checker  pt-ioprofile              pt-show-grants            pt-table-checksum

ptar                      pt-fifo-split             pt-kill                   pt-sift                   pt-table-sync

pt-archiver               pt-find                   pt-mext                   pt-slave-delay            pt-table-usage

ptardiff                  pt-fingerprint            pt-mysql-summary          pt-slave-find             pt-upgrade

pt-config-diff            pt-fk-error-logger        pt-online-schema-change   pt-slave-restart          pt-variable-advisor

pt-deadlock-logger        pt-heartbeat              pt-pmp                    pt-stalk                  pt-visual-explain

pt-diskstats              pt-index-usage            pt-query-digest           pt-summary                ptx

例如：

#pt-ioprofile（Watch process IO andprint a table of file and I/O activity详细评估当前主机IO能力）

#pt-slave-delay（Make a MySQLslave server lag behind its master有意让slave比master慢）

#pt-slave-find（Find andprint replication hierarchy tree of MySQL slaves）

#pt-slave-restart（Watch andrestart MySQL replication after errors）

#pt-show-grants（Canonicalizeand print MySQL grants so you can effectively replicate, compare andversion-control them）

#pt-summary（Summarize systeminformation nicely收集服务器信息）

#pt-diskstats（An interactive I/Omonitoring tool for GNU/Linux磁盘相关统计数据）

#pt-index-usage（Read queriesfrom a log and analyze how they use indexes当前索引表使用情况）

#pt-visual-explain（FormatEXPLAIN output as a tree可视化分析查询）

#pt-table-checksum（Verify MySQLreplication integrity检查slave数据是否与master数据一致）

 

本文转自 chaijowin 51CTO博客，原文链接：http://blog.51cto.com/jowin/1690086，如需转载请自行联系原作者