Mysql主从复制、二进制日志、基于GTID的主从复制、双主复制
时间： 12:21:14
&&&& 阅读：611
&&&& 评论：
&&&& 收藏：0
标签： &一、主从复制的工作原理 & Mysql在Master与slave之间实现整个复制的过程由3个线程来完成的， & 其中两个线程(SQL线程和IO线程)在 Slave端， & 另外一个线程（IO）在Master端
& 要实现Mysql的复制必须首先打开Master端的binary log(也就是二进制日志)否则无法实现.
Mysql复制基本过程如下： & (1)Slave上面的IO 线程链接上Master，并且请求指定日志文件的位置（或者 从开始的日志之后的日志内容）&
(2)Master接收到来自Slave的IO线程请求后，通过负责复制的IO线程根据这个请求信息指定日志的位置后， 把这个信息返回给Slave的IO线程(返回的信心当中除了日志所包含的信息外,还包括了Master端的二进制文件名称和 二进文件的位置)
(3)Slave的IO线程接收到Master端的返回信息之后，将日志内容一次写入slave端的Relay
log文件，(mysql-relay-bin.xxx)当中，并且读取到Master端的bin-log文件和位置记录，
记录到master-info文件当中，以便下一次能够清楚的告诉Master我需要从某个bin-log的哪个位置开始往后的内容，请发给我。 & (4)、Slave的SQL线程检测Relay log中心增加了内容后，马上解析Master二进制文件中的内容，并且执行里面的Query语句. 数据的更新 只能从主服务器向从服务器更新，而不能反过来进行。
650) this.width=650;" src="/yws/res/F7DA4F5BA0E" alt="QQ截图23.png" /> &当master主服务器中数据有更新时，会先将数据的更新写入自己的数据库中， 将更新的语句写入二进制日志文件里面。 &&& 对于从服务器，首先启动IO线程 向主服务器进行二进制日志的复制到自己的中继日志里面， 从服务器复制了二进制日志内容之后并不会直接更新自己的数据库，要首先写入自己的中继Relay log日志里面， 然后从服务器使用SQL线程从中继日志里面解析二进制日志去更新自己的数据库。 &注意：1. 从服务器必须启动两个线程 &&&&&& 2. 不会直接更新自己的数据库，而是写入自己的中继日志
&&&& & 3. 单向更新 &二、Mysql的优点
&1.如果主服务器出现问题，可以快速切换到从服务器提供的服务
&2.可以在从服务器上执行查询操作，降低主服务器的访问压力
&3.可以在从服务器上执行备份，以避免备份期间影响主服务器的服务& & &
============================================== 注意error： (1)进行主从复制，主从服务器的时间要同步 &# date -s ‘2015-07-22‘&
(2) 关闭防火墙 &# iptables -F &# vim /etc/selinux/config (3)
初始化可能会遇到错误。不要害怕。把数据库文件下的所有文件都删除掉。datadir=/database/mydata &&& 重新初始化 (4)当启动mysql时，出现without PID时，cat /f --& 错误日志的位置 --&查看错误日志 &# ps -ef | grep mysqld &# kill -p& PID (5 )不能授权了一次没有成功，又继续授权，会混乱的。要把第一次授权的用户删掉
& 再重新授权用户。 650) this.width=650;" src="/yws/res/C83F9F11A4ABE81AAA9ABD13119A4" alt="QQ截图20.png" />================================= 为什么MySQL要做主从复制（读写分离）？ 通俗来讲，如果对数据库的读和写都在同一个数据库服务器中操作，业务系统性能会降低。 为了提升业务系统性能，优化用户体验，可以通过做主从复制（读写分离）来减轻主数据库的负载。 而且如果主数据库宕机，可快速将业务系统切换到从数据库上，可避免数据丢失。 主从复制有两种方式： 基于日志（binlog） 基于GTID（全局事务标示符） ====================================== &1、基于日志的主从复制
&& 主服务器：10.0.199.1 &&&&& 从服务器：10.0.199.2
&(1)主服务器配置 [client] port = 3306 socket = /tmp/mysql.sock [mysqld] user = mysql innodb_buffer_pool_size = 128M log_bin = master-log max_binlog_size = 64M binlog_format = mixed basedir = /usr/local/mysql datadir = /database/mydata port = 3306 server_id = 1 socket = /tmp/mysql.sock log_error = /database/mydata/server1.err
character_set_server = utf8 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES & 数据库初始化
&&& # service mysqld stop
&& # cd /usr/local/mysql & # ./scripts/mysql_install_db --user=mysql --datadir=/database/mydata & # service mysqld start &
&给从服务器授权 & mysql& grant replication slave on *.* to
identified by ‘aixocm‘; & mysql& & mysql& show master status\G
mysql& select& user,host,password from mysql. && (2)从服务器配置 [client] port = 3306 socket = /tmp/mysql.sock [mysqld] user = mysql innodb_buffer_pool_size = 128M log_bin = slave-log max_binlog_size = 64M log_slave_updates = on binlog_format = mixed relay_log = relay-bin basedir = /usr/local/mysql datadir = /database/mydata port = 3306 server_id = 2 socket = /tmp/mysql.sock log_error = /database/mydata/server1.err character_set_server = utf8 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES & 数据库初始化
&& # service mysqld stop
&&& # cd /usr/local/mysql & # ./scripts/mysql_install_db --user=mysql --datadir=/database/mydata
&&&&&& 注意：初始化可能会遇到错误。不要害怕。把数据库文件下的所有文件都删除掉。datadir=/database/mydata &&& 重新初始化 & # service mysqld start & 设置主服务器相关信息
&& mysql& show master status\G;& (在主服务器端) &&&&&&&&&&& 650) this.width=650;" src="/yws/res/AB54A0EC8DB26FB12" alt="QQ截图30.png" /> & mysql& change master to master_host=‘10.0.199.1‘,master_user=‘admin‘,master_password=‘aixocm‘,master_log_file=‘m aster-log.000011‘,master_log_pos=565,master_port=3306;&
# master_log_pos=565 //意思把565以后的日志都同步到从服务器上。也可以设置为1 或者其他数字位置。
& mysql& & mysql& show slave status\G &&&&&&&&&&&&&& 650) this.width=650;" src="/yws/res/BDCA47EF34" alt="QQ截图41.png" /> &&&&&&&&&&&&&& 线程都必须为yes，同步数据才会成功。
&&&&&&&&&&&&& 如果主服务器 进行了增删改操作,在从服务器上show slave status\G出现了如下错误：
&&&&&&&&&&&&& 650) this.width=650;" src="/yws/res/2E51A4AED68D3DD4C" alt="QQ截图15.png" /> &&&&&&&&&&&& 解决办法：mysql& reset master; (在主服务器上删除所有的日志datadir=/database/mydata) & mysql&
(3)二进制日志操作
& mysql日志分为4种： && & & (1)二进制日志 &&&&&& (2)错误日志 &&&&& (3)慢 查询日志 &&&&&& (4)通用查询日志
&&&& & # vim /etc/profile && export& PATH=$PATH:/usr/local/mysql/bin&
&& 因为：所有和mysql有关的命令都在mysql安装目录下的bin下 （mysqlbinlog命令）
# source& /etc/profile & && # mysqlbinlog master-log.000001 && # mysqlbinlog --start-datetime=‘ 7:10:0‘ master-log.000003 && # mysqlbinlog --start-datetime=‘ 7:10:0‘ --stop-datetime=‘ 7:33:0‘ master-log.000003 && # mysqlbinlog --start-position=330 master-log.000003 && # mysqlbinlog --start-position=330 --stop-position=1100 master-log.000003 && # mysqlbinlog --start-position=330 --stop-position=1100 master-log.000003 | mysql -u root &&&&&&&&&&&&&&& \\恢复指定二进制日志的 内容& & && mysql& && mysql& & & && mysql& show binlog events in ‘master-log.000003‘ limit 20; && mysql& show binlog events in ‘master-log.000003‘ from 409 limit 10; & & && mysql& purge master logs to ‘master-log.000002‘;& \\删除指定编号之前的日志 && mysql& purge master logs before ‘ 08:00:00‘; &(4)设置忽略的数据库 &注意：修改了配置文件之后，必须要像上面一样都要进行数据初始化，主服务器授权刷新 从服务器change
&&&& 主服务器设置 &&&& binlog_do_db = sxjy&&&&&&&&&& \\记录二进制日志的数据库 &&&& binlog_ignore_db = test&&&&&& \\不记录二进制日志的数据库 &&&& binlog_ignore_db = teach &&& & &&&& 从服务器设置 &&&& replicate_do_db = sxjy&&&&&&& \\设置默认进行二进制日志复制的数据库 &&&& replicate_ignore_db = test&&& \\不对test数据库进行二进制日志复制 &&&& replicate_ignore_db = teach &&&& replicate_ignore_db = mysql &&&& replicate_ignore_db = information_schema &&&& replicate_do_table = sxjy.stu&&&&&&&& \\设置进行更新的表 &&&& replicate_ignore_table = sxjy.class&& \\不进行更新的表
&&&& 从服务器建议设置 &&&& replicate_ignore_db = test&&&&&&&&&&& \\不对test数据库进行二进制日志复制 &&&& replicate_ignore_db = mysql &&&& replicate_ignore_db = information_schema &&&& replicate_wild_do_table = sxjy.stu&&& \\复制指定的数据库或表的二进制日志 &&&& replicate_wild_do_table = sxkj.% &&&& replicate_wild_ignore_table = sxjy.class \\不复制指定的数据库或表的二进制日志 &&
&练习:先搭建一台mysql数据库服务器,不启用二进制日志,然后启动服务器，先创建一个sxjy数据库, &&&&& 在sxjy数据库下创建stu和teach两个表,字段自己添加3-4个,然后插入4-5个记录。然后将 &&&&& 服务器改为主从结构,要求原先的服务器做主服务器，且在从服务器上要有主服务器上原来的数 &&&&& 主从结构搭建好后,以后添加的数据能够自动同步到从服务器,主从服务器数据要完全一致。 &&&&&&&&&&& 解析：&
主服务器：# mysqldump -uroot --opt& --database sxjy & sxjy.sql
&&&&&&&&& &
将新建的sxjy数据库导出。然后建立了主从复制之后，再将sxjy.sql导入到从服务器内。 &&&&&&&&&&&&
&2、基于GTID的mysql主从复制
(1) TID：Transaction ID,事务的ID号：也就是说在mysql复制中每一个事务都有自己的ID号（随机数）
(2) GTID：Global Transaction ID,全局事务ID && 在整个事务架构中每一个事务ID号是全局唯一的， && 不止是在一个节点上而是整个主从复制架构中每任何两个事务的ID号都不会相同。
(3) 全局事务ID是怎么生成的？ &简
单来讲是由mysql服务器自动管理的，在mysql5.6以后每一个mysql服务器都有一个全局唯一的ID号叫做uuid，通用唯 一识别码
(Universally Unique
Identifier)，而GTID就是由当前节点的UUID（一个128位的随机数）和为当前节点生成的随机数（TID）组成的，因此只要UUID不同
再在此基础上保证事务ID不同就保证全局不一样了。
(4) 全局事务ID有何用处？ & 简单来讲GTID能够保证让一个从服务器到其他的从服务器那里实现数据复制而且能够实现数据整合的。GTID在分布式架构中可以保证数据的一致性。从而也实现了mysql的高可用性。
(5) GTID相关操作： &默认情况下将一个事务记录进二进制文件时将首先记录它的GTID而且GTID和事务相关信息一并要发送给从服务器由从服务器在在本地应用认证但是绝对不会改变原来的事务ID号。
(6) GTID的组成部分： 前面是server_uuid：后面是一个序列号 例如：server_uuid：sequence number ae-11e4-983d-5a:10 UUID：每个mysql实例的唯一ID，由于会传递到slave，所以也可以理解为源ID。 Sequence number：在每台MySQL服务器上都是从1开始自增长的序列，一个数值对应一个事务。
& (7) GTID的工作原理： 1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。 &要点： 1、slave在接受master的binlog时，会校验master的GTID是否已经执行过（一个服务器只能执行一次）。 2、为了保证主从数据的一致性，多线程只能同时执行一个GTID。 &
&&&& (1)能够基于数据库进行多线程复制(要求2个或2个以上的数据库进行同步) & && &
(2)能够自动判断要复制的位置
& 注意：修改了配置文件之后，必须要像上面一样都要进行数据初始化，主服务器授权刷新 从服务器change
注意：初始化可能会遇到错误。不要害怕。把数据库文件下的所有文件都删除掉。datadir=/database/mydata &&& 重新初始化 &binlog-format：二进制日志的格式，有row、statement和mixed &&&
在上面的基于日志的主从复制配置文件中再加入以下内容： 主服务器配置 &&& bin_log = master-log &&&&&&
log_slave_updates = on
&&& gtid_mode = on&& \\开启GTID模式 &&& enforce_gtid_consistency = on&&&&& \\强制GTID的一致性 &&& master_info_repository =TABLE&&&&& \\主服务器信息的记录方式(TABLE或FILE) &&& relay_log_info_repository = TABLE& \\中继日志信息的记录方式 &&& sync_master_info = 1&&&&&&&&&&&&&& \\同步主数据库信息, 确保服务器崩溃时无信息丢失 &&&
&&& slave_parallel_workers = 4&&&&&&&& \\从服务器的sql线程数,和要复制的数据库的个数 相同 &&& binlog_checksum =CRC32&&&&&&&&&&&& \\二进制日志的校验方式 &&& master_verify_checksum = 1&&&&&&&& \\主服务器启用校验 &&& slave_sql_verify_checksum = 1&&&&& \\从服务器启用校验 &&& binlog_rows_query_log_events = 1&&
\\二进制日志详细记录事件 ,可降低故障排除的复杂度 &&& report_port = 3306&&&&&&&&&&&&&&&& \\提够复制的报告端口,和数据库端口一致 &&& report_host = 10.0.199.1 &&&&&&&&& \\提供复制报告的主机,设为本机地址
& mysql& grant replication slave on *.* to
identified by ‘aixocm‘;
& mysql&& mysql& show master status\G&&&
&从服务器配置 &&&
bin_log = master-log &&& log_slave_updates = on &&& gtid_mode = on&& \\开启GTID模式 &&& enforce_gtid_consistency = on&&&&& \\强制GTID的一致性 &&& master_info_repository =TABLE&&&&& \\主服务器信息的记录方式(TABLE或FILE) &&& relay_log_info_repository = TABLE& \\中继日志信息的记录方式 &&& sync_master_info = 1&&&&&&&&&&&&&& \\同步主数据库信息 &&& slave_parallel_workers = 4&&&&&&&& \\从服务器的sql线程数,和要复制的数据库的个数 相同 &&& binlog_checksum =CRC32&&&&&&&&&&&& \\二进制日志的校验方式 &&& master_verify_checksum = 1&&&&&&&& \\主服务器启用校验 &&& slave_sql_verify_checksum = 1&&&&& \\从服务器启用校验 &&& binlog_rows_query_log_events = 1&& \\二进制日志详细记录事件 &&& report_port = 3306&&&&&&&&&&&&&&&& \\提够复制的报告端口,和数据库端口一致 &&& report_host = 10.0.199.2 &&&&&&& \\提供复制报告的主机,设为本机地址 &&& slave_skip_errors = all
& & & & & & \\如果在主服务器的某个表中插入4,‘张三‘,‘man‘ ，而又在从插入4,‘王五‘,‘woman‘ 相冲突。 &&&&&&&&&&&&& 跳过此error,从而不影响下面数据的同步 &&& (或slave_skip_errors = 03)
&&& mysql& change master to master_host=‘10.0.199.1‘,master_user=‘admin‘, &&&&&&&&&& master_password=‘aixocm‘,master_auto_position=1;
&&& mysql& &&&&&&&&& 650) this.width=650;" src="/yws/res/AEDAF3FFFB84512D8" alt="QQ截图53.png" />&&&&&&& 必须全部为yes才会成功
&&& mysql& show processlist\G &&& mysql& show status like ‘thread%‘; && & &&&&
3、双主复制
注意：互为主从。两边都要相互进行数据初始化授权刷新change
在基于日志的主从复制配置文件上添加以下内容： & 第一台服务器设置（10.0.199.1）
& auto_increment_increment = 2& # 数据表记录的自增量,一般等于服务器的数量&&
& auto_increment_offset = 1& # 数据表记录每次的递增量,第一台为1,第二台为2,...
& sync_binlog = 0&&&&&&&& # 二进制日志写入磁盘的方式(1表示立即写入磁盘 0表示先缓存再写入磁盘) & replicate_same_server_id = 0& # 防止mysql循环更新
& 数据库初始化
&&& # service mysqld stop
&& # cd /usr/local/mysql & # ./scripts/mysql_install_db --user=mysql --datadir=/database/mydata & # service mysqld start
& 第二台服务器设置(10.0.199.2) & auto_increment_increment = 2& # 数据表记录的自增量,一般等于服务器的数量 & auto_increment_offset = 2& & # 数据表记录每次的递增量,第一台为1,第二台为2,... & sync_binlog = 0&&&&&&&& # 二进制日志写入磁盘的方式(1表示立即写入磁盘 0表示先缓存再写入磁盘) & replicate_same_server_id = 0& # 防止mysql循环更新 && 数据库初始化
& & # service mysqld stop
& # cd /usr/local/mysql
& # ./scripts/mysql_install_db --user=mysql --datadir=/database/mydata & # service mysqld start&&&&&
&10.0.199.1作主服务器 ：
&& 主：mysql& grant replication slave on *.* to
identified by ‘aix ocm‘;&&&
& && & mysql& & & 从：mysql& change master to master_host=‘10.0.199.1‘,master_user=‘repuser‘,
&&&&&&&&&&& & master_password=‘aixocm‘,master_auto_position=1;
&&&&&&&&&&&&& mysql& start sl
&&&&&&&&&&&& mysql& show slave status\G
& 10.0.199.2作主服务器： &&&& 主：mysql& grant replication slave on *.* to
identified by ‘aixocm‘;&&&
& & && mysql& &&&&& 从：mysql& change master to master_host=‘10.0.199.2‘,master_user=‘admin‘,
&&&&&&&&& & & & & master_password=‘aixocm‘,master_auto_position=1; &&&&&&&&&&&&& mysql& start sl &&&&&& mysql& show slave status\G &
测试：双服务器上都插入数据或者更新数据，查看对方服务器数据是否有改变。
& 本文出自 “” 博客，请务必保留此出处标签：
&&国之画&&&& &&&&chrome插件
版权所有 京ICP备号-2
迷上了代码！更多最新文章相关作者文章搜狗：感谢您阅读MySQL复制应用中继日志解析 本文版权归原作者所有，本文由网友投递产生，如有侵权请联系 ，会第一时间为您处理删除。}