MySQL_主从,主主同步.docx

MySQL 同步（一）MySQL同步原理图（二）常用的同步架构（三）同步搭建（1）一主一从（2）主主结构（3）一主多从（4）高可用性（5）第三方软件（四）其他进阶操作（1）动态添加从机（2）状态监控（3）特殊场景及处理1）延时2）跳过错误3）同步指定库，指定表4）单独运行IO及SQL线程5）其他（一）MySQL同步原理图1）Statement-based replication2）Row-based replication3)原理概述图（二）常用的同步架构（1）一主一从 （2）主主结构（3）一主多从 （三）同步搭建环境：主机名称IP地址系统数据库版本master29CentOS 5.4mysql-5.1.48.tar.gzslave31CentOS 5.4mysql-5.1.48.tar.gz（1）一主一从Master 服务器，修改server-id、开启二进制日志、及为从机授权rootmaster opt# vim /etc/fmysqldlog-bin=mysql-binserver-id = 1mysql grant replication slave on *.* to repl31 identified by 123456;mysqlflush privileges;mysqlshow grants for repl31;备注：对innodb来说，为了保证持久性及一致性，配置文件应添加rootmaster opt# vim /etc/fmysqldinnodb_flush_log_at_trx_commit=1sync_binlog=1Slave服务器，修改server-id、开启二进制日志rootmaster opt# vim /etc/fmysqldlog-bin=mysql-binserver-id = 2至此，配置修改完成，下面为操作为保证master 和salve上的数据一致，然后再开启slave上的同步进程场景1：master 和slave上的数据库 1）初始化新安装 2）未新建任何应用库3）未对外提供服务操作：master服务器：Slave 服务器：执行命令：mysql CHANGE MASTER TO MASTER_HOST=29,MASTER_PORT=3306,MASTER_USER=repl, MASTER_PASSWORD=123456,Master_Log_File=mysql-bin.000001,Master_Log_Pos=81639521;mysqlstart slave;mysqlshow slave status G同步完成,类似如下状态：场景2：master 和slave上的数据库 1）master 已新建库，并已包含数据 2）master服务未对外提供服务操作：保证主库与从库数据一致，然后再开启从库的同步操作方法：1）利用mysqldumprootmaster opt# mysqldump -all-databases -lock-all-tables all_dbback.sql或者添加master-data参数，自动带有change master 的定位信息：rootmaster opt#mysqldump -all-databases -master-data all_dbback.sql将导出来的数据到slave库上恢复。2）拷贝数据文件mysqlshow variables like datadir; -获取数据库数据库位置rootmaster opt#mysqladmin shutdown -关闭数据库，确保一致性将数据库目录打包或者其他方法拷贝到slave服务器启动master 数据库，启动slave数据库、开启同步。场景3：master 和slave上的数据库1）master应用库已对外提供服务，并不断有访问及数据更新此操作在动态添加从库介绍（2）主主结构：与主从结构不同之处：利用auto_increment_increment、auto_increment_offset 控制auto_increment的值计算方法：auto_increment=auto_increment_offset+N*auto_increment_increment如，主库：auto_increment_offset=1 、auto_increment_increment=21+1*2、1+2*2、1+3*2、1+4*2auto_increment=1、3、5、7、9从库：auto_increment_offset=2 、auto_increment_increment=22+1*2、2+2*2、2+3*2、2+4*2auto_increment=2、4、6、8、10来确保当2个数据库同时进行insert 插入时，不会产生auto_increment主键冲突；由此来达到2个数据库可以同时进行数据插入的效果。具体操作：修改配置文件：master1：rootmaster opt#vim /etc/fmysqldauto_increment_offset=1 auto_increment_increment=2master2rootmaster2 opt#vim /etc/fmysqldauto_increment_offset=2 auto_increment_increment=2备注：1）由于解决不了2台服务器之间的全局事务一致性，此模式只能算作主从模式的一种改进，可用于HA方案中的主从快速切换。2）同时，即使解决了全局事务一致性，在写入和修改比较频繁的场景下，由于auto_increment的不连续性，将会产生大量的数据碎片，导致数据库变慢。（3）一主多从：1）一主多从场景:可用于写入较少,主库压力不大且读取频繁的场景,主要用于读写分离2）一主一从多从原因：由于在主库上进行的所有DML语句都会复制到从库(即使指定了replicate-do-db)，当从库较多时，将会对主库造成较大的压力，即占用主库的CPU、IO、及带宽资源，为保证主库效率，可以在主库后只对应一个从库，再由此从库对其他从库进行分发。(4)高可用性:场景：主要用于主从模式或者主主模式下，当因电源故障、网络故障、服务器故障或mysql bug，造成的服务器down机、网络中断、或者mysql服务异常退出而导致mysql无法正常对外提供服务时，另外一台MysQL服务能及时发现，并即时接管并对外提供服务；尽量减少服务中断时间。备注：5.5版本开始支持半同步复制，5.5之前及5.1版本都为异步复制。也就是说，5.1版本之前，master不会监控相应的sql是否 已传到slave服务器。5.5版本，支持半同步复制，至少确定sql已传送到一个slave上，然后才将结果返送回客户端，但也只是确保已传送到，并不确保slave已执行。Mysql同步的这种不完整性，导致了：1）主库和从库的同步状态需要监控2）在对数据完整性要求很严格的情况下,发生主从切换时，都需要DBA对数据完整性进行审查和确认3）对跨机房及跨地域的mysql复制，需要定期检查数据完整性实现：lvs、keepalived或其他的硬件均衡负载设备。keepalived具体实施步骤，可参见相关文档。（5）第三方软件1）MMM -Multi-Master Replicatioin Manager for MySQL适用于管理大规模MySQL主从关系也是一个很好的HA实施方案，同时对MySQL的针对性也很强具体实施方案，可参见： /blog/static/13636242011880352932/2）amoeba读写分离+MMM 具体实施，可参见：/thread-1815468-1-1.html（四）其他进阶操作（1）动态添加从机所有的需求及操作都应按照实际的生产环境来进行，场景不同，采用的方法也不同。场景1：当前master库负载较低，业务应用可以承受一定时间内数据无法写入。mysqlflush tables with read lock; -对主库加读锁,只允许读,不允许写mysqlshow master status; -获取当前主库日志的名称及位置再将主库上的数据导出mysqlunlock tables; -释放读锁，让数据库恢复正常状态将数据在从库上进行恢复设置主从开启同步场景2：当前主库负载较高，且业务应用不允许数据库服务器任何的中断此时，为了获取与主库数据的一致性，应用mysqldump 或者 Xtrabackupmysqldump -F -single-transaction -u xxx -pxxx dbname dbname.sqlmysqlshow master logs; -获取最新的日志文件名称将备份出的数据在从库上进行恢复设置主从指定从库从master最新二进制日志的开始位置开始读取开启同步（2）状态监控从机：Mysqlshow slave status;比较重要的参数：Slave_IO_State、Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master正常情况下，其对应的值：Slave_IO_State：Waiting for master to send eventSlave_IO_Running：YesSlave_SQL_Running：YesSeconds_Behind_Master：0（3）特殊场景及处理1）延时原因：1.从MySQL本身设计原因，从库的Slave SQL进程只有一个。当主库处于并发操作，且负载高时，从库的单sql进程的执行速度无法达到与主库一致的速度2.当一个实例，包含多个库时，主库上可以对多个库进行并发操作对从库来说，只能按照二进制日志的记录顺序执行，如果某一个库执行了一个语句很长时间，将会导致从库所有的库延时；而此时主库其他的库没有任何影响。注：直到mysql6，slave库才会在各个库之间进行并发复制。3.当用MySQL处理类似于OLTP或者BI场景时，如果采用了load data方式，除了slave单线程的SQL会延时外，还要考虑网络问题。看一下处理过程：txt文件上传主库文件通过网络传输主库可以同时处理多个load 语句主库处理完毕 -此时,可能已经过去了几十分钟主库将相关信息发送到slave网络传输传输完毕，从库处理 -在这里,从库的处理必将会延时,且会叠加4.还有一个导致延时的问题，可能就比较弱智了的，就是从机或者主机的时间问题通常来说，从机的时间是延后主机，且值为正数，有时，出现负值，这肯定就是时间问题了。2）跳过错误主从之间，有时会由于一些非必须的原因，导致主从同步停止，如果此时，原因不重要，可以跳过相关错误，继续同步mysql set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 3）同步指定库，指定表某些场景下，需要对一个mysql实例下的多个库进行分流,或只需同步一个库中的某些表：同步指定库：mysqldreplicate-do-db=name忽略某些库:mysqldreplicate-ignore-do-db=name同步指定表：mysqldreplicate-do-table=db_name.tbl_name同步表达式匹配的表mysqldreplicate-wild-do-table=shopex.account%忽略指定表：mysqldReplicate-wild-ignore-table=shopex.account%将一个库的一个表映射到从库不同的库名下replicate-rewrite-db=原库名-新库名replicate-do-table=新库名.表名4）单独运行IO及SQL线程通常情况下，停止或开启slave进程，只需start slave 或stop slave 即可。某些特殊场景下，可能需要单独运行从库的IO进程及slave进程。甚至可以指定执行到哪个时间，执行到主库的哪个position。详细的操作及介绍，请看mysql手册mysqlstart slave io_thread;mysqlstart slave SQL_thread;mysqlstop slave io_thread;mysqlstop slave sql_thread;5）其