mysql的主从项目经验_Mysql数据库主从心得整理
mysql的主从项⽬经验_Mysql数据库主从⼼得整理
Mysql数据库主从⼼得整理
管理mysql主从有2年多了，管理过200多组mysql主从，⼏乎涉及到各个版本的主从，本博⽂属于总结性的，有⼀部分是摘⾃⽹络，⼤部分是根据⾃⼰管理的⼼得和经验所写，整理了⼀下，分享给各位同⾏，希望对⼤家有帮助，互相交流。
1、Replication 线程
Mysql的 Replication 是⼀个异步的复制过程(mysql5.1.7以上版本分为异步复制和半同步两种模式)，从⼀个 Mysql instace(我们称之为Master)复制到另⼀个 Mysql instance(我们称之 Slave)。在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成，其中两个线程(Sql线程和IO线程)在 Slave 端，另外⼀个线程(IO线程)在 Master 端。
要实现 MySQL 的 Replication ，⾸先必须打开 Master 端的Binary )功能，否则⽆法实现。因为整个复制过程实际上就是Slave从Master端获取该⽇志然后再在⾃⼰⾝上完全 顺序的执⾏⽇志中所记录的各种操作。打开 MySQL 的 Binary Log 可以通过在启动 MySQL Server 的过程中使⽤ “—log-bin” 参数选项，或者在 myf 配置⽂件中的 mysqld 参数组([mysqld]标识后的参数部分)增加 “log-bin” 参数项。
2、MySQL 复制的基本过程如下：
2.1．Slave上⾯的IO线程连接上 Master，并请求从指定⽇志⽂件的指定位置(或者从最开始的⽇志)之后的⽇志内容；
2.2.Master接收到来⾃ Slave 的 IO 线程的请求后，通过负责复制的 IO 线程根据请求信息读取指定⽇志指定位置之后的⽇志信息，返回给Slave 端的 IO 线程。返回信息中除了⽇志所包含的信息之外，还包括本次返回的信息在 Master 端的 Binary Log ⽂件的名称以及在Binary Log 中的位置；
autocad2010序列号2.3.Slave的 IO 线程接收到信息后，将接收到的⽇志内容依次写⼊到 Slave 端的Relay Log⽂件()的最末端，并将读取到的Master端的bin-log的⽂件名和位置记录到master- info⽂件中，以便在下⼀次读取的时候能够清楚的⾼速Master“我需要从某个bin-log的哪个位置开始往后的⽇志内容，请发给我”
地方专项是什么意思
2.4.Slave的 SQL 线程检测到 Relay Log 中新增加了内容后，会马上解析该 Log ⽂件中的内容成为在 Master 端真实执⾏时候的那些可执⾏的 Query 语句，并在⾃⾝执⾏这些 Query。这样，实际上就是在 Master 端和 Slave 端执⾏了同样的 Query，所以两端的数据是完全⼀样的。
3、Mysql复制的⼏种模式
3.1.从 MySQL 5.1.12 开始，可以⽤以下三种模式来实现：
– 基于SQL语句的复制(statement-based replication, SBR)，
– 基于⾏的复制(row-based replication, RBR)，
– 混合模式复制(mixed-based replication, MBR)。
相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。 MBR 模式中，SBR 模式是默认的。
在运⾏时可以动态改动 binlog的格式，除了以下⼏种情况：
1.存储流程或者触发器中间
2.启⽤了NDB
3.当前会话试⽤ RBR 模式，并且已打开了临时表
如果binlog采⽤了 MIXED 模式，那么在以下⼏种情况下会⾃动将binlog的模式由 SBR 模式改成 RBR 模式：
1.当DML语句更新⼀个NDB表时
2.当函数中包含 UUID() 时
3.2个及以上包含 AUTO_INCREMENT 字段的表被更新时
4.⾏任何 INSERT DELAYED 语句时
5.⽤ UDF 时
6.视图中必须要求运⽤ RBR 时，例如建⽴视图是运⽤了 UUID() 函数
3.2.设定主从复制模式：
log-bin=mysql-bin
#binlog_format="STATEMENT"
#binlog_format="ROW"
binlog_format="MIXED"
也可以在运⾏时动态修改binlog的格式。例如
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
3.3.两种模式各⾃的优缺点：
SBR的优点：
历史悠久，技能成熟
binlog⽂件较⼩
binlog中包含了所有数据库修改信息，可以据此来审核数据库的安全等情况
binlog可以⽤于实时的还原，⽽不仅仅⽤于复制
主从版本可以不⼀样，从服务器版本可以⽐主服务器版本⾼
SBR的缺点：
不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。
调⽤具有不确定因素的 UDF 时复制也可能出疑问
运⽤以下函数的语句也不能被复制：
* LOAD_FILE()
* UUID()
祖国在我心中演讲稿600字六年级* USER()
* FOUND_ROWS()
* SYSDATE() (除⾮启动时启⽤了 –sysdate-is-now 选项)
INSERT… SELECT 会产⽣⽐ RBR 更多的⾏级锁
复制须要执⾏ 全表扫描(WHERE 语句中没有运⽤到索引)的 UPDATE 时，须要⽐ RBR 请求更多的⾏级锁
对于有 AUTO_INCREMENT 字段的 InnoDB表⽽⾔，INSERT 语句会阻塞其他 INSERT 语句星特朗130eq
对于⼀些复杂的语句，在从服务器上的耗资源情况会更严重，⽽ RBR 模式下，只会对那个发⽣变化的记录产⽣影响
存储函数(不是存储流程 )在被调⽤的同时也会执⾏⼀次 NOW() 函数，这个可以说是坏事也可能是好事
确定了的 UDF 也须要在从服务器上执⾏
数据表必须⼏乎和主服务器保持⼀致才⾏，否则可能会导致复制出错
执⾏复杂语句如果出错的话，会消耗更多资源
RBR的优点：
任何情况都可以被复制，这对复制来说是最安全可靠的
和其他⼤多数数据库系统的复制技能⼀样
陈皮的制作方法多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
复制以下⼏种语句时的⾏锁更少：
* INSERT … SELECT
*包含 AUTO_INCREMENT 字段的 INSERT
*没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句
执⾏ INSERT，UPDATE，DELETE 语句时锁更少
从服务器上采⽤多线程来执⾏复制成为可能
RBR的缺点：
binlog⼤了很多
复杂的回滚时 binlog 中会包含⼤量的数据
主服务器上执⾏ UPDATE 语句时，所有发⽣变化的记录都会写到 binlog 中，⽽ SBR 只会写⼀次，这会导致频繁发⽣ binlog 的并发写疑问
UDF产⽣的⼤ BLOB 值会导致复制变慢
不能从 binlog 中看到都复制了写什么语句(加密过的)
当在⾮事务表上执⾏⼀段堆积的SQL语句时，最好采⽤ SBR 模式，否则很容易导致主从服务器的数据不⼀致情况发⽣
另外，针对系统库 mysql ⾥⾯的表发⽣变化时的处理准则如下：
如果是采⽤ INSERT，UPDATE，DELETE 直接操作表的情况，则⽇志格式根据 binlog_format 的设定⽽记录
如果是采⽤ GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么⽆论如何 都采⽤ SBR 模式记录。
注：采⽤ RBR 模式后，能处理很多原先出现的主键重复问题。实例:
对于insert into db_allot_ids select * from db_allot_ids 这个语句:
在BINLOG_FORMAT=STATEMENT 模式下:
BINLOG⽇志信息为:
—————————————–
BEGIN
/*!*/;
# at 173
#090612 16:05:42 server id 1 end_log_pos 288 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1244793942/*!*/;
insert into db_allot_ids select * from db_allot_ids
/
*!*/;
—————————————–
在BINLOG_FORMAT=ROW 模式下:
BINLOG⽇志信息为:
—————————————–
BINLOG '
hA0yShMBAAAAMwAAAOAAAAAAAA8AAAAAAAAAA1NOUwAMZGJfYWxsb3RfaWRzAAIBAwAA
hA0yShcBAAAANQAAABUBAAAQAA8AAAAAAAEAAv/8AQEAAAD8AQEAAAD8AQEAAAD8AQEAAAA=
'/*!*/;
—————————————–
4、Mysql主从的优缺点
MySQL的主从同步是⼀个很成熟的架构，优点为：①在从服务器可以执⾏查询⼯作(即我们常说的读功能)，降低主服 务器压⼒;②在从主服务器进⾏备份，避免备份期间影响主服务器服务;③当主服务器出现问题时，可以切换到从服务器。所以我在项⽬部署和实施中经常会采⽤这种⽅案；鉴于⽣产环境下的mysql的严谨性。
实际上，在⽼版本中，MySQL 的复制实现在 Slave 端并不是由 SQL 线程和 IO 线程这两个线程共同协作⽽完成的，⽽是由单独的⼀个线程来完成所有的⼯作。但是 MySQL 的⼯程师们很快发现，这样做存在很⼤的风险和性能问题，主要如下：
⾸先，如果通过⼀个单⼀的线程来独⽴实现这个⼯作的话，就使复制 Master 端的，Binary Log⽇志，以及解析这些⽇志，然后再在⾃⾝执⾏的这个过程成为⼀个串⾏的过程，性能⾃然会受到较⼤的限制，这种架构下的 Replication 的延迟⾃然就⽐较长了。
其次，Slave 端的这个复制线程从 Master 端获取 Binary Log 过来之后，需要接着解析这些内容，还原成 Master 端所执⾏的原始Query，然后在⾃⾝执⾏。在这个过程中，Master端很可能⼜已经产⽣了⼤量的变化并⽣成了⼤量的 Binary Log 信息。如果在这个阶段Master 端的存储系统出现了⽆法修复的故障，那么在这个阶段所产⽣的所有变更都将永远的丢失，⽆法再回来。这种潜在风险在Slave 端压⼒⽐较⼤的时候尤其突出，因为如果 Slave 压⼒⽐较⼤，解析⽇志以及应⽤这些⽇志所花费的时间⾃然就会更长⼀些，可能丢失的数据也就会更多。
所以，在后期的改造中，新版本的 MySQL 为了尽量减⼩这个风险，并提⾼复制的性能，将 Slave 端的复制改为两个线程来完成，也就是前⾯所提到的 SQL 线程和 IO 线程。最早提出这个改进⽅案的是Yahoo!的⼀位⼯程师“Jeremy Zawodny”。通过这样的改造，这样既在很⼤程度上解决了性能问题，缩短了异步的延时时间，同时也减少了潜在的数据丢失量。
当然，即使是换成了现在这样两个线程来协作处理之后，同样也还是存在 Slave 数据延时以及数据丢失的可能性的，毕竟这个复制是异步的。只要数据的更改不是在⼀个事务中，这些问题都是存在的。
如果要完全避免这些问题，就只能⽤ MySQL 的 Cluster 来解决了。不过 MySQL的 Cluster 知道笔者写这部分内容的时候，仍然还是⼀个内存数据库的解决⽅案，也就是需要将所有数据包括索引全部都 Load 到内存中，这样就对内存的要求就⾮常⼤的⼤，对于⼀般的⼤众化应⽤来说可实施性并不是太⼤。MySQL 现在正在不断改进其 Cluster 的实现，其中⾮常⼤的⼀个改动就是允许数据不⽤全部 Load 到内存中，⽽仅仅只是索引全部 Load 到内存中，我想信在完成该项改造之后的 MySQL Cluster 将会更加受⼈欢迎，可实施性也会更⼤。
5、Mysql的半同步模式(Semisynchronous Replication)
我们知道在5.5之前，MySQL的复制其实是异步操作，⽽不是同步，也就意味着允许主从之间的数据存在⼀定的延迟，mysql当初这样设计的⽬的可能也是基于可⽤性的考虑，为了保证master不受slave
的影响，并且异步复制使得master处于⼀种性能最优的状态：写完binlog 后即可提交⽽不需要等待slave的操作完成。这样存在⼀个隐患，当你使⽤slave作为备份时，如果master挂掉，那么会存在部分已提交的事务未能成功传输到slave的可能，这就意味着数据丢失！
在MySQL5.5版本中，引⼊了半同步复制模式(Semi-synchronous Replication)能够成功(只是相对的)避免上述数据丢失的隐患。在这种模式下：master会等到binlog成功传送并写⼊⾄少⼀个slave的relay log之后才会提交，否则⼀直等待，直到timeout(默认10s)。当出现timeout的时候，master会⾃动切换半同步为异步，直到⾄少有⼀个slave成功收到并发送Acknowledge，master会再切换回半同步模式。结合这个新功能，我们可以做到，在允许损失⼀定的事务吞吐量的前提下来保证同步数据的绝对安全，因为当你设置timeout为⼀个⾜够⼤的值的情况下，任何提交的数据都会安全抵达slave。
mysql5.5版本⽀持半同步复制功能(Semisynchronous Replication)，但还不是原⽣的⽀持，是通过plugin来⽀持的，并且默认是没有安装这个插件的。不论是⼆进制发布的，还是⾃⼰源代码编译的，都会默认⽣成这个插件，⼀个是针对master 的⼀个是针对slave的，在使⽤之前需要先安装这俩plugins。
复制的过滤主要有２种⽅式：
1、在主服务器在把事件从进⼆制⽇志中过滤掉，相关的参数是:binlog_do_db和binlog_ignore_db。
2、在从服务器上把事件从中继⽇志中过滤掉，相关的参数是replicate_*。
复制只能扩展读取，不能扩展写⼊，对数据进⾏分区可以进⾏扩展写⼊。
春联上下联左右顺序
复制的优化：
在mysql复制环境中，有8个参数可以让我们控制，需要复制或需要忽略不进⾏复制的DB或table分别为:
下⾯⼆项需要在Master上设置：
Binlog_Do_DB：设定哪些数据库需要记录Binlog
Binlog_Ignore_DB：设定哪⾥数据库不需要记录Binlog
优点是Master端的Binlog记录所带来的Io量减少，⽹络IO减少，还会让slave端的IO线程,SQL线程减少，从⽽⼤幅提⾼复制性能,
缺点是mysql判断是否需要复制某个事件不是根据产⽣该事件的查询所在的DB,⽽是根据执⾏查询时刻所在的默认数据库(也就是登录时指定的库名或运⾏"use database"中指定的DB)，只有当前默认DB和
配置中所设定的DB完全吻合时IO线程才会将该事件读取给slave的IO线程.所以，如果在默认DB和设定须要复制的DB不⼀样的情况下改变了须要复制的DB中某个Table中的数据，该事件是不会被复制到Slave中去的，这样就会造成Slave端的数据和Master的数据不⼀致.同样，在默认的数据库下更改了不须要复制的数据库中的数据，则会被复制到slave端，当slave端并没有该数据库时，则会造成复制出错⽽停⽌。
下⾯六项需要在slave上设置：
Replicate_Do_DB：设定需要复制的数据库，多个DB⽤逗号分隔
Replicate_Ignore_DB：设定可以忽略的数据库.
Replicate_Do_Table：设定需要复制的Table
Replicate_Ignore_Table：设定可以忽略的Table
Replicate_Wild_Do_Table：功能同Replicate_Do_Table，但可以带通配符来进⾏设置。
Replicate_Wild_Ignore_Table：功能同Replicate_Do_Table，功能同Replicate_Ignore_Table，可以带通配符。
优点是在slave端设置复制过滤机制，可以保证不会出现因为默认的数据库问题⽽造成Slave和Master数据不⼀致或复制出错的问题.
缺点是性能⽅⾯⽐在Master端差⼀些.原因在于：不管是否须要复制，事件都会被IO线程读取到Slave端，这样不仅增加了⽹络IO量，也给Slave端的IO线程增加了Relay Log的写⼊量。
注：在实际的⽣产应⽤中发现，在mysql5.0以前的版本，mysql的这个过滤设置⼏乎是形同虚设，不起作⽤：不管你在主库或是从库上设置了忽略某个数据库或是表，他依然会进⾏同步，所以在做5.0以前版本的主从同步时，⼀定保持主从数据库的⼀致性，主上有的库或是表从上⼀定要有，否则在同步的过程会出错。
主库IP：192.168.1.2
从库IP：192.168.1.3
添加⼀个⽤于主从同步的⽤户：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。