MySQL多主多从架构实现及主从复制问题处理
一 测试架构设计和目标： 1）Master（192.168.31.230）为正常运行环境下的主库，为两个Slave（192.168.31.231和192.168.31.232）提供“主-从”复制功能； 2）Master_Backup(192.168.31.233)是Master的备份库，只要Master是正常的，它不对外提供服务。它与Master之间属于"主-主"复制关系，即自己既是主机，又是对方的从机； 3）同理，192.168.31.234和192.168.31.235为Slave_Backup，分别为192.168.31.231和 192.168.31.232的备份库，只要Slave是正常的，对应的备份机不对外提供服务； 4）Slave在此架构中的目的是为了实现读写分离，对应用程序来说，Master只负责写，两个Slave只负责读。Slave的数据来源于Master的复制操作； 5）如果Master由于某种原因（例如：宕机和断电等）导致不能正常运行，则此时需要让Master_Backup自动切换为新主机，而Slave和Slave_Backup也能自动切换数据源到Master_Backup； 6）同理，如果Slave由于某种原因（例如：宕机和断电等）导致不能正常运行，则此时需要让对应的Slave_Backup自动切换为新从机； 7）无论是Master还是切换后的Master_Backup，它们向客户端提供的连接地址应保持一致，如上图提供的VIP+Port，即192.168.31.201:3306，Slave和Slave_Backup也应如此，对外提供的连接地址始终是192.168.31.202:3306和192.168.31.203:3306。 二 实现部署测试 首先完成双主部署 1 在masterA上新建一个账户,用户masterB同步数据 masterA操作 >GRANTREPLICATIONSLAVEON*.*TO'master'@'192.168.31..%'IDENTIFIEDBY'123456'; >FLUSHPRIVILEGES; >FLUSHTABLESWITHREADLOCK; mysqldump-uroot-p123456--databasestest>/tmp/testA.sql scp/tmp/testA.sql192.168.31.233:/tmp >UNLOCKTABLES; QueryOK,0rowsaffected(0.00sec) 2 masterB:上同样的建立复制到账户,并导入数据 mysql>GRANTREPLICATIONSLAVEON*.*TO'master'@'192.168.31.%'IDENTIFIEDBY'123456';mysql>FLUSHTABLESWITHREADLOCK; mysqldump-uroot-p123456--databasestest>/tmp/testB.sql scp/tmp/testB.sql192.168.31.230:/tmp/ #分别在master1 和master2上分别导入对方的数据.但是存在一个问题:因为存在主键冲突的情况,导出数据的时候,不要把主键给导出来了;如果是一方没有数据,那就直接导入数据就好了,不过也要跳过主键; mysql-uroot-p123456</tmp/testA.sql mysql-uroot-p123456</tmp/testB.sql 3修改master1和master2 的配置文件,只是server-id不同 master1:的配置文件 vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql #datadir=/mysqldata socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 ####Master#### server-id=1 log-bin=mysql-bin log-bin-index=mysql-bin.index relay-log=mysql-relay relay-log-index=mysql-relay.index expire-logs-days=10 max-binlog-size=100M log-slave-updates=1 binlog-do-db=test replicate-do-db=test binlog-ignore-db=mysql replicate-ignore-db=mysql sync-binlog=1 auto-increment-increment=2 auto-increment-offset=1 #master2:的配置文件 vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 ###Master#### [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 server-id=2 log-bin=mysql-bin log-bin-index=mysql-bin.index relay-log=mysql-relay relay-log-index=mysql-relay.index expire-logs-days=10 max-binlog-size=100M log-slave-updates skip-slave-start slave-skip-errors=all binlog-do-db=test replicate-do-db=test binlog-ignore-db=mysql replicate-ignore-db=mysql sync-binlog=1 auto-increment-increment=2 auto-increment-offset=2 4 分别在master1和master2上获取File和Position位置 master1: >flush tables with read lock; >show matser status; +------------------+----------+--------------+------------------+ |File |Position|Binlog_Do_DB|Binlog_Ignore_DB +------------------+----------+--------------+------------------+ |mysql-bin.000002| 106 |test | mysql +------------------+----------+--------------+------------------+ >unlock tables; master2: >flush tables with read lock; >show matser status; +------------------+----------+--------------+------------------+ |File |Position|Binlog_Do_DB|Binlog_Ignore_DB +------------------+----------+--------------+------------------+ |mysql-bin.000003| 106 |test | mysql +------------------+----------+--------------+------------------+ >unlock tables; 5配置主从,分别在master1和master2上配置对方的从 master1上： >CHANGEMASTERTO ->MASTER_HOST='192.168.31.233', ->MASTER_USER='master', ->MASTER_PASSWORD='123456', ->MASTER_PORT=3306, ->MASTER_LOG_FILE='mysql-bin.000003', ->MASTER_LOG_POS=106, ->MASTER_CONNECT_RETRY=10; QueryOK,0rowsaffected(0.02sec) mysql>STARTSLAVE; QueryOK,0rowsaffected(0.00sec) master2上： >CHANGEMASTERTO ->MASTER_HOST='192.168.31230', ->MASTER_USER='master', ->MASTER_PASSWORD='123456', ->MASTER_PORT=3306, ->MASTER_LOG_FILE='mysql-bin.000002', ->MASTER_LOG_POS=106, ->MASTER_CONNECT_RETRY=10; QueryOK,0rowsaffected(0.02sec) mysql>STARTSLAVE; QueryOK,0rowsaffected(0.00sec) 用>show processlist查看进程状态 接着为双主机器各部署两个从机并测试是否主从正常（主从部署略，其步骤与双主大致相同，注意修改部分参数即可，这里不在详细写出可参考http://superleedo.blog.51cto.com/12164670/1897681） 6测试结果 1）当Master和Master_Backup都正常运行时，在任意一端更新数据后都会同步到两个Slave上 2）当Master处于正常时，无论Master_Backup是否正常，在此端更新数据后都会同步到两个Slave上 3）当Master处于不可运行时，Master_Backup通过Monitor（Keepalived）成为接管者，在Master_Backup更新数据后不会同步到所有Slave上，即使后来在Slave上将MASTER_HOST指定为Keepalived提供的VIP（192.168.31.201）也无用。 究其原因，Master_Server_Id指向的是已经处于不可运行的Master，而预期结果是希望它能自动的更新定位到Master_Backup(233)上达到自动切换目的，然而测试结果并能不满足快速响应容灾切换的目的。 三 修改架构及部署 选择mysql-mmm结合半同步机制来实现容灾自动切换 1 在master(230和233)上安装semisync master并设置 mysql代码: >INSTALLPLUGINrpl_semi_sync_masterSONAME'semisync_master.so'; >INSTALLPLUGINrpl_semi_sync_slaveSONAME'semisync_slave.so'; >SETGLOBALrpl_semi_sync_master_enabled=1; >SETGLOBALrpl_semi_sync_slave_enabled=1; vim /ect/my.cnf后加入如下配置： rpl_semi_sync_master_enabled=1 rpl_semi_sync_slave_enabled=1 2 在slave(231、232、234和235)上安装slave插件并设置 mysql代码： >INSTALLPLUGINrpl_semi_sync_slaveSONAME'semisync_slave.so'; >SETGLOBALrpl_semi_sync_slave_enabled=1; vim /ect/my.cnf后加入如下配置： rpl_semi_sync_slave_enabled=1 3 所有mysql实例停止slave并开启slave，使半同步机制生效 mysql代码 : >stopslave; >startslave; 4 查看semisync状态 mysql代码: >showstatuslike'%emi%'; 重点关注: 1）Rpl_semi_sync_master_clients：与当前master建立半同步连接的客户端数 2）Rpl_semi_sync_master_status：作为半同步master端的就绪状态（ON：就绪，OFF：未就绪） 3）Rpl_semi_sync_slave_status：作为半同步slave端的就绪状态（ON：就绪，OFF：未就绪） 5 安装mysql-mmm 5.1新增一台专门用于监控mysql的服务器（mysql_monitor），IP为192.168.31.250 5.2在mysql_monitor、master、master_backup、slave和slave_backup上安装epel网络源 yuminstallhttp://mirrors.hustunique.com/epel//6/x86_64/epel-release-6-8.noarch.rpm 5.3在mysql_monitor上安装mysql-mmm-monitor yum-yinstallmysql-mmm-monitor 5.4 编辑mysql_monitor上的配置文件mmm_mon.conf vi /etc/mysql-mmm/mmm_mon.conf includemmm_common.conf <monitor> #本机IP ip192.168.31.250 port9988 pid_path/var/run/mysql-mmm/mmm_mond.pid bin_path/usr/libexec/mysql-mmm status_path/var/lib/mysql-mmm/mmm_mond.status ping_ips192.168.31.230,192.168.31.231,192.168.31.232,192.168.31.233,192.168.31.234,192.168.31.235 #所有MySQL服务器的IP auto_set_online0 </monitor> <hostdefault> #GRANTREPLICATIONCLIENTON语句创建的账号和密码 monitor_usermmm_monitor monitor_passwordmonitor </host> <checkmysql> check_period5#每5秒检查一次 trap_period10 timeout2#检查超时秒数 restart_after10000 max_backlog60 </check> <code>#设置为1,开启调试模式，打印日志到前台，ctrl+c将结束进程，对于调试有帮助</code> debug0 6在master、master_backup、slave和slave_backup上安装和配置 1）安装mysql-mmm-agent yum-yinstallmysql-mmm-agent 2）授权monitor访问 mysql代码 : >GRANTREPLICATIONCLIENTON*.*TO'mmm_monitor'@'192.168.31.%'IDENTIFIEDBY'monitor'; >GRANTSUPER,REPLICATIONCLIENT,PROCESSON*.*TO'mmm_agent'@'192.168.31.%'IDENTIFIEDBY'agent'; 3）编辑mmm_agent.conf配置文件 vim /etc/mysql-mmm/mmm_agent.conf includemmm_common.conf #包含公用配置文件 <spanstyle="color:#000000;">mmm_common.conf中定义的某个host名称</span></span></code>#对应<code><spanstyle="color:#008000;"> thisdb1 #设置成1时，将打印日志到前台，按ctrl+c将结束进程 debug0 max_kill_retries1 4）编辑mmm_common.conf配置文件 vim /etc/mysql-mmm/mmm_common.conf active_master_rolewriter <hostdefault> #对应当前主机的网络接口名 cluster_interfaceeth2 pid_path/var/run/mysql-mmm/mmm_agentd.pid bin_path/usr/libexec/mysql-mmm/ mysql_port3306 agent_port9989 #对应GRANTREPLICATIONSLAVEON语句创建的账号和密码 replication_userslave replication_passwordslave123 #GRANTSUPER,REPLICATIONCLIENT,PROCESSON语句创建的账号和密码 agent_usermmm_agent agent_passwordagent </host> #master的配置 #其中host后面的值定义的是某台数据库服务的别名，一般就用服务器的主机名即可 <hostdb1> ip192.168.31.230 modemaster #db1的master对等点 peerdb2 </host> #master_backup的配置 <hostdb2> ip192.168.31.233 modemaster #db2的master对等点 peerdb1 </host> #slave的配置 <hostdb3> ip192.168.31.231 modeslave </host> #slave的配置 <hostdb4> ip192.168.31.232 modeslave </host> #slave_backup的配置 <hostdb5> ip192.168.31.234 modeslave </host> #slave_backup的配置 <hostdb6> ip192.168.31.235 modeslave </host> #定义writer角色，即架构中的master和master_backup #ips为writer对外提供的vip <rolewriter> hostsdb1,db2 ips192.168.31.201 modeexclusive </role> #定义reader角色，即架构中的两个slave和两个slave_backup #ips为reader对外提供的vip <rolereader> hostsdb3,db4,db5,db6 ips192.168.31.202,192.168.31.203 modebalanced </role> 注意，也需要将此配置文件复制到mysql_monitor的同名目录下 5) 在master、master_backup、slave和slave_backup上启动mmm agent服务，并设置为开机服务 /etc/init.d/mysql-mmm-agentstart vim /etc/rc.d/rc.local后，将上述命令行添加到mysql启动命令的下面 7在上mysql_monitor开启mmm monitor监控，并设置为开机服务 /etc/init.d/mysql-mmm-monitorstart vi /etc/rc.d/rc.local后，将上述命令行添加 8 然后重启所有服务器系统后测试 1）在mysql_monitor上执行如下命令，查看各监控机的运行状态 执行mmm_controlshow 查看 2）测试结果 1）当Master和Master_Backup都正常运行时，在任意一端更新数据后都会同步到两个Slave上 2）当Master处于正常时，无论Master_Backup是否正常，在此端更新数据后都会同步到两个Slave上 3）当Master处于不可运行时，Master_Backup通过Monitor（Keepalived）成为接管者，在Master_Backup更新数据后会同步到所有Slave上，符合预期 四 问题处理总结 1架构图中看出，Mmm_Mnitor存在单点问题，当Mmm_Mnitor处于不可运行时，整个主从结构将不能正常运行。可以部署多个监控，结合Keepalived来扩展。 2读写分离会带来数据延迟达到的问题。假设有一个业务，当数据插入到数据库后要立即又从数据库中将此数据查询出来，因此当数据插入到Master库后，由于网络的延迟，Slave库中不会立即得到这条最新的数据，此时应用程序查询Slave库将得不到预期结果。 解决问题：将此类业务控制在一个数据库事务中进行，读写都在master中进行。因此，在mmm_common.conf配置文件中，还需要将db1和db2同时配置在reader组： <rolewriter> hostsdb1,db4 ips192.168.31.201 modeexclusive </role> <rolereader> hostsdb1,db4,db2,db3,db5,db6 ips192.168.31.202,192.168.31.203 modebalanced </role> 3主从复制差距的问题。有时候因为主服务器的更新过于频繁，造成了从服务器更新速度较慢，当然问题是多种多样，有可能是网络搭建的结构不好或者硬件的性能较差，从而使得主从服务器之间的差距越来越大,最终对某些应用产生了影响。 解决问题：定期进行主从服务器的数据同步，具体步骤如下在主服务器上 mysql>FLUSHTABLESWITHREADLOCK; QueryOK,0rowsaffected(0.03sec) mysql>showmasterstatus\G; ***************************1.row*************************** File:mysql-bin.000004 Position:102 Binlog_Do_DB: Binlog_Ignore_DB: 1rowinset(0.00sec) 记录出日志的名字和偏移量,这些是从服务器复制的目的目标;在从服务器上,使用MASTER_POS_WAIT()函数得到复制坐标值 mysql>selectmaster_pos_wait('mysql-bin.000004','102'); +-------------------------------------------+ |master_pos_wait('mysql-bin.000004','102')| +-------------------------------------------+ |0| +-------------------------------------------+ 1rowinset(0.00sec) 这个select语句会阻塞直到从服务器达到指定日志文件和偏移量后,返回0,如果是-1,则表示超时退出,查询是0时,表示从服务器与主服务器已经同步 本文转自super李导51CTO博客，原文链接：http://blog.51cto.com/superleedo/1897966，如需转载请自行联系原作者
