IV 7 MySQL REPLICATION

  1. 云栖社区>
  2. 博客>
  3. 正文

IV 7 MySQL REPLICATION

技术小阿哥 2017-10-31 15:11:00 浏览750
展开阅读全文

一、相关概念:

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

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

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

隔离级别(read uncommittedread committedrepeatable readmysql默认此项);serializable

repeatable read+statement)或({readuncommitted,read committed}+mixed

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

注:statementmysql官方已不建议使用),mysql5.X建议使用row

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

 

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

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

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

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

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

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

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

若不是多级复制,slave上可不要二进制日志(例如master-->slave1-->slave2slave2是不需要bin log,中间的slave1bin logrelay log都要有)

 

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

server-id(至关重要,解决循环复制,每个nodeserver-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

 

wKiom1Z4ubeBjOdTAABTL6-EQJo982.jpg

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

若要在每个slave上实现负载均衡,在其前端加上directorLVShaproxy

对于mastermysql-proxydirector的高可用,可各自提供一台备用,也可只提供一台备用server,这三个node若某一故障使用这个备用server(高可用集群中N-mNserver运行m个服务)

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

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

 

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

 注:一主可以有多从,但一个从只能属一个主

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

 

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

 

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

 

 mysql5.6引入GTIDglobal transaction identifer),使得mysql复制更安全、多事务执行时不会产生混乱、引入多线程复制(multi thread replication

 

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

 

复制线程(masterdump),slaveio_threadsql_thread))

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

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

 

若复制时跨越互联网,要使用ssl(支持双向认证,client<-->serverdump<-->io_thread

 

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

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

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

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

5slave可向master做冷备份。

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

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

8master-side若宕掉,可将slave-side提升为master(执行二进制日志),从而实现高可用。

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

9server-id避免循环复制。

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

11、主从架构中,不使用mysql-proxy,如何让master写,slave读:让程序(PHP开发出的程序)自身具有读写分离的功能;双主模型。

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

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

14、数据库server压力大时,两种方案:scaleoutscale on

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

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

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

17、读写分离:mysql-proxyamoeba(java),配置文件xml格式

18coba(amoeba):数据拆分。

19、复制线程:master-side(dump)slave-side(IO_threadSQL_thread)

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

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

 


 

wKiom1etGAbQpn_PAACyGA9Di68568.jpg

注:主从同步中,是主库主动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_THREADSQL_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_THREADSQL_THREAD是否为ON状态,Exec_Master_Log_Pos:为当前执行的位置,Seconds_Behind_Master:从比主慢的时间,Master_SSL_Allowed:是否启用用ssl

>STOP SLAVE  IO_THREAD;  master上数据若有问题时,可将slave-sideIO_THREAD停掉)

slave-side重启mysqldIO_THREADSQL_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_clientsRpl_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

 

 

4percona toolkithttps://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-ioprofileWatch process IO andprint a table of file and I/O activity详细评估当前主机IO能力)

#pt-slave-delayMake a MySQLslave server lag behind its master有意让slavemaster慢)

#pt-slave-findFind andprint replication hierarchy tree of MySQL slaves

#pt-slave-restartWatch andrestart MySQL replication after errors

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

#pt-summarySummarize systeminformation nicely收集服务器信息)

#pt-diskstatsAn interactive I/Omonitoring tool for GNU/Linux磁盘相关统计数据)

#pt-index-usageRead queriesfrom a log and analyze how they use indexes当前索引表使用情况)

#pt-visual-explainFormatEXPLAIN output as a tree可视化分析查询)

#pt-table-checksumVerify MySQLreplication integrity检查slave数据是否与master数据一致)

 


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

网友评论

登录后评论
0/500
评论
技术小阿哥
+ 关注