注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

MySQL复制  

2015-11-16 09:40:45|  分类: MySQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
以下内容是MySQL学习笔记
MySQL专门的集群特性是NDB引擎。
MySQL的复制特性灵活,既可以实现整个服务(all databases)级别的复制,也可以实现只复制某个数据库,甚至某个数据库的某个指定表;既可以实现A复制到B(主从单向复制),B复制到C,也可以实现A直接复制到B和C(一主多从),甚至是AB相互复制(双主复制)。

复制特性应用场景:
1. 提高性能。 一主多从,读写分离,读压力分散到从库上。
2. 数据安全。 数据冗余,备份放到从库上执行。
3. 数据分析。 数据分析和挖掘放到从库上执行。
4. 数据分布。 主从不需要实时连接,不用担心网络中断对同步造成影响。

复制过程默认是异步的。从5.5开始,官方版本有了半同步,可以使主库修改实时同步到从库。
复制在处理数据时,有三种模式:基于语句复制(SBR) 基于sql语句、基于记录复制(RBR)基于修改的列的复制、混合复制模式(MBR)。

最简单的复制环境搭建步骤:
1. 主库关库
2. 复制数据文件到从库
3. 修改主从库配置文件my.cnf的server_id,该值不能为0.
3.5my.cnf中添加或修改主从库中继日志的路径到
4. 启动主库,并创建复制专用账户。
grant replication slave on *.* to rep@'10.1.5.%' identified by 'replication';
获取主库的日志信息
(system@localhost) [mysql]> show master status;

5. 删除从库的auto.cnf文件
$ cd /data/mysqldata/3307/data
$ cat auto.cnf
[auto]
server-uuid=f3cafe17-508c-11e5-9ac8-000c29b9df80
$ rm -rf auto.cnf
6. 启动从库,配置从主库的连接,启动从库应用
(system@localhost) [(none)]> 
change master to
master_host='10.1.5.117',
master_port=3306,
master_user='rep',
master_password='replication',
master_log_file='mysql-bin.000006',
master_log_pos=120;
启动从库应用:(system@localhost) [(none)]> start slave;
7. 同步测试
主库建表或增删,查看从库结果。
如果发现没有同步成功,则查看从库的接收和应用状态。
(system@localhost) [test]> show slave status\G
                            Slave_IO_Running: Yes
                         Slave_SQL_Running: Yes


配置复制环境,遵循的一些要素:
1. 主库启用二进制日志,指定唯一server_id。
2. 从库配置唯一server_id。
3. 主库配置复制专用账户,并仅授予replication slave权限。
4. 记录主库日志信息并创建影像。
①获取日志信息前指定一个全库锁定:mysql> flush tables with read lock; 
②查看主库当前使用的二进制文件名和当前写入的位置: sql> show master status;  如果File和Position为空,说明当前未启用二进制日志。
③复制数据文件到从库,可以用mysqldump,xtrabackup及其他方式。使用mysqldump、xtrabackup生成从库镜像时,会自动收集主库的日志信息,并保存到专用脚本中。mysqldump甚至有全库锁定的参数。
④备份完成后释放全局锁:sql> unlock tables;
5. 配置从库到主库的连接
从库恢复数据后,执行change master to命令,创建从库到主库的连接。
change master to
master_host='10.1.5.117',
master_port=3306,
master_user='rep',
master_password='replication',
master_log_file='mysql-bin.000005',
master_log_pos=120;
mysqldump、xtrabackup都有专门的配置从库连接主库的参数,如果使用了这些参数,则生成的脚本中包含change master to语句。

常用的复制环境管理命令:
1. 检查从库状态  show slave status\G  在任意从库上执行,就可以获取该从库与主库之间的同步信息。
    Seconds_Behind_Master 该值最好是0,显示从库接收到的日志和已经应用的日志之间的差距,该值不是主从间的延迟时间,而是从库接收和应用上的差异。
    show processlist\G 也能一定程度上起到辅助了解复制状态的作用。
每一组主从,都有三个线程,主库一个,从库两个。
(system@localhost) [test]> show slave hosts;   --主库上查看有多少个从库连接到了主库


2. 从库启停命令
通过stop slave和start slave来启停从库
执行stop slave;后,通过show slave status \G 查看从库状态,可看到
               Slave_IO_Running: No    --读取主库二进制日志,写到本地的中继日志relay-log中。
            Slave_SQL_Running: No    --从本地中继日志中读取事件并执行
这两个线程互相没有依赖,可以在start slave后附加要操作的线程选择启动或,停止SQL_THREAD或IO_THREAD,如stop slave sql_thread; 


复制基于二进制日志,二进制日志的三种格式,由参数binlog_format控制,动态修改binlog_format会有风险,甚至可能导致复制环境出错。
1. 基于语句,SBL,对应参数值为statement
2. 基于行格式,RBL,对应参数值为row
3. 混合模式,MBL,对应参数为mixed

由于复制格式与二进制日志格式间的关系,所以复制格式分为3种:
1. 基于语句复制(statement-based replication)SBR
2. 基于行复制 RBR     二进制日志写入事件时,记录的是变更的记录行信息。
3. 混合记录模式 MBR 默认选择基于语句的格式记录日志,只有在需要的场景下,才会启动切换成基于行的格式记录日志。
5.6版本中默认是基于语句记录日志,但我们一般会改成混合模式mixed。
SBR优点:①技术成熟②生成日志少,特别是大量更新删除操作③能记录下变更操作,可用于审计。
SBR缺点:主从库同时执行found_rows()/sysdate()/uuid()这类函数,可能返回不同的结果。select uuid();主从库结果不一致。
                 有些DML操作,比RBR持有更多的行锁。
                 对于InnoDB表,insert语句使用自增列auto_increment会阻塞其他insert语句。
RBR优点:所有修改都被安全地应用到了从库。缺点:生成更多的日志。
MBR应用场景:SBR记录事件存在安全隐患时,自动将日志记录格式变为基于行格式,也就是RBR模式。

节点自身产生的日志是二进制日志,IO_THREAD接收主库的二进制日志就是中继日志relay log,也是二进制的,中继日志默认按照host_name-relay-bin.nnnnn的命名规则放在data目录下。跟二进制日志一样,中继日志也有一个日志索引文件:mysql-relay-bin.index。中继日志和中继日志索引通过变量relay_log和relay_log_index进行定义。
一旦修改了从库的主机名,复制环境也会受影响,复制进程抛出不能打开或发现中继日志。对于修改了主机名的从库,可修改relay_log_index变量,来解决,不用改relay_log。
(system@localhost) [(none)]> set global relay_log = '/data/mysqldata/3307/binlog/mysql-relay-bin.log';
ERROR 1238 (HY000): Variable 'relay_log' is a read only variable
(system@localhost) [(none)]> set global relay_log_index = '/data/mysqldata/3307/binlog/mysql-relay-bin.index';
ERROR 1238 (HY000): Variable 'relay_log_index' is a read only variable
除中继日志外,从库还会创建两个复制环境的状态文件master.info和relay-log.info,这两个文件默认也放在data目录下。relay_log_info_file  | relay-log.info   
master.info 保存复制环境中连接master节点的配置信息。IO线程负责更新该文件。
relay-log.info 保存处理进度及中继日志文件的位置。 sql线程更新该文件。
在slave启动时,需要读取这两个文件,以便确认从什么位置继续处理日志。

(system@localhost) [test]> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
(system@localhost) [test]> system perror 1872
MySQL error code 1872 (ER_SLAVE_RLI_INIT_REPOSITORY): Slave failed to initialize relay log info structure from the repository


复制过滤规则
对于主库,可以通过--binlog_do_db和--binlog-ignore-db参数,控制哪些数据库下的操作事件被记录或不被记录,也可以在会话级设置所做操作是否记录到二进制日志。
但这种方案有两个缺点:一是粒度太粗,若想过滤某个对象,那实现就有难处了,二是过滤功能简单粗暴,不够灵活,若从库有多个,分析希望复制不同的库对象,那也难以处理。在从库也有忽略参数,控制忽略哪些日志。一系列以--replicate-*开头的参数就是复制环境中的从库定义过滤规则,默认没指定任何的该类参数,那从库将执行所有收到的修改事件。若指定了--replicate-*参数,库级规则优先,而后是表级,但操作是否一定被过滤,还跟主库的日志记录格式有关。--replicate-*参数只是控制从库sql_thread线程是否应用某库或表的变更。
原A库的对象,复制到从库后,希望保存到B库,专用参数replicate-rewrite-db=from_name->to_name,该参数只对表级操作有效,如create、drop、alter database就无效,不支持垮裤的更新操作,多个库有这种需求,则需重复指定多次该参数。
1. 库级过滤规则
主库有--binlog_do_db和--binlog-ignore-db两参数控制库对象的变更是否记录到二进制日志中。从库有--replicate_do_db和--replicate-ignore-db两参数控制是否应用指定库的变更事件。当使用基于语句的日志记录格式时,--replicate_do_db和--replicate-ignore-db两参数过滤的是默认数据库的操作,如主库使用了use a,修改了a库表对象及b库表对象,从库设置了--replicate-ignore-db=a,此时ab库的修改都被忽略。

判断日志格式:row格式:过滤基于指定要过滤的库;statement,过滤基于当前的默认库。
上图是库级过滤规则,此时最下方sql还未执行,还需进行表级检查,才能最终执行sql。
任意启用了二进制日志的从库,都可以再作为主库为其他从库提供复制,因此我们也可以在从库上指定--binlog_do_db和--binlog-ignore-db两参数。
--binlog_do_db也可做忽略某些数据库,当使用基于语句格式记录日志时,服务启动时指定了--binlog_do_db=sales,那么所有不在默认库,即先use sales下发生的变更,都不会记录日志;如果使用基于行格式记录日志时,则不管默认库是哪个,只有sales下对象发生的变更才记录到二进制日志。

2. 表级过滤规则
当库级参数无有效匹配时,从库才检查表级过滤参数。从库检查是否是基于语句复制,如果是且是在存储过程中触发,那从库执行语句并退出,如果是基于行格式日志,从库并不知道主库执行的语句做了什么,因此此时什么也不会应用。
指定了--replicate_do_table和--replicate_wild_do_table,则只执行参数中指定对象的修改事件,如果指定了 --replicate-ignore-table和--replicate-wild-ignore-table参数,则除了参数中指定对象的修改事件不执行外,其他事件都要在本地执行。

 
对于--replicate-*参数,每个参数只能指定一个参数值,比如从库希望过滤abc三个库,则指定三次--replicate-ignore-db参数。

3. 过滤规则应用实例
主库参数文件修改:binlog_format=statement
从库参数文件修改:
binlog_format = mixed
relay_log = /data/mysqldata/3307/binlog/mysql-relay-bin
relay_log_index = /data/mysqldata/3307/binlog/mysql-relay-bin.index
replicate-ignore-db=jssdb
replicate-ignore-table=test.t1
replicate-do-table=test.t2
主库修改全局参数后生效,从库需重启。如果确认从库是否执行了过滤?show slave status\G   Slave_IO_Running: Yes和Slave_SQL_Running: Yes,Seconds_Behind_Master: 0则说明应用了主库传来的日志。



高级应用技巧
通过xtrabackup创建从库,最多只需6步。
1. 主库上建立复制专用账户
sql> create user xtrabackup@'localhost' identified by 'xtrabackup';
sql> grant reload, lock tables, replication client, super on *.* to xtrabackup@'localhost';

2. 创建完整备份
$ innobackupex --defaults-file=/data/mysqldata/3306/my.cnf --user=xtrabackup --password='xtrabackup'  --stream=tar /data/mysqldata/3306/tmp | gzip -> /data/mysqldata/backup/xtrabackup/xbak_`date +%F-%H%M`.tar.gz
解压: $ tar zxvfi xbak_2015-10-16-1009.tar.gz -C /data/mysqldata/backup/xtrabackup/test

3. 配置从库参数文件
$ scp 10.1.5.117:/data/mysqldata/3306/my.cnf /data/mysqldata/3306/my.cnf
根据实际情况进行修改,如修改server_id和端口号,sed -i 's/3306/3307/g'  /data/mysqldata/3306/my.cnf

4. 复制和准备备份集
$ scp 10.1.5.117:/data/mysqldata/backup/xtrabackup/xbak_2015-10-20-1122.tar.gz /data/mysqldata/backup/xtrabackup
$ tar zxvfi xbak_2015-10-20-1122.tar.gz -C /data/mysqldata/3306/data
$ innobackupex --defaults-file=/data/mysqldata/3306/my.cnf --apply-log  /data/mysqldata/3306/data
启库:$ mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf &

5. 配置从库复制环境
对于XtraBackup创建的备份集,主库的二进制日志和位置保存在备份集的xtrabackup_binlog_info文件中。
$ cat /data/mysqldata/3306/data/xtrabackup_binlog_info
mysql-bin.000010        555

执行change master命令:
mysql>
change master to
master_host='10.1.5.117',
master_port=3306,
master_user='rep',
master_password='replication',
master_log_file='mysql-bin.000010',
master_log_pos=555;

6. 从库启动
mysql> start slave;

7.在 从库上检查复制情况
mysql> show slave status\G



利用从库创建备份
几种备份方案比较:
冷备,需停库,物理文件复制,代价高,影响大。
语句级逻辑复制,通过select ... into outfile,步骤太多,操作繁琐,出错率高,适用场景有限。
mysql提供的专用复制命令,如mysqlhotcopy、mysqldump参数太多,适用于小数据量的场景。
XtraBackup联机热备,高效稳定、自动,对事务引擎支持热备。

mysqldump创建一致性库备份时,适用全局锁定(flush tables with read lock)阻塞写,对某些引擎还阻塞读,可把全库备份放在从库执行,备份前先停止sql_thread,此时就相当于不产生数据变更了(但要防止有人在从库上写操作),备份完后,重新开启sql_thread
mysql -usystem -p'123456' -S /data/mysqldata/3306/mysql.sock  -e 'show slave status\G'| egrep "Slave_IO_Running|Slave_SQL_Running|Master_Log_File|Read_Master_Log_Pos|Exec_Master_Log_Pos|Relay_Master_Log_File|Relay_Log_Pos|Seconds_Behind_Master|Last_IO_Errno|Last_IO_Error|Last_SQL_Errno|Last_SQL_Error" >> 1.log

mysql -usystem -p'123456' -S /data/mysqldata/3306/mysql.sock -e 'stop slave sql_thread;'

mysqldump -usystem -p'123456' -S /data/mysqldata/3306/mysql.sock -A -R -l --single-transaction | gzip > /data/mysqldata/backup/dbbak_`date +%F-%H%M`.sql.gz   >> 1.log

mysql -usystem -p'123456' -S /data/mysqldata/3306/mysql.sock -e 'start slave sql_thread;'
只要结束后,Relay_Master_Log_File和Exec_Master_Log_Pos值没有发生变化,就可以认为备份是一致的。
此处mysqldump换成xtrabackup,更没问题。实现全备后,要实现增量备份,只要定期将二进制日志文件保存到备份路径即可。
以下跳过!




复制文件方式的备份
停从库;复制数据文件目录;启动从库。


部署级联从库
从库本地产生的修改写入二进制日志,但默认解析自中继日志的修改事件是不写入二进制日志的,但使用参数--log-slave-updates后,即使是中继日志产生的数据修改,也写入本地的二进制日志中。
主库 117:3306         从库1  117:3307      从库2(中继节点relay slave):  112:3306       从库3:  112:3307


1. 配置relay slave:
log-slave-updates不是动态参数,无法实时修改,只能通过修改参数文件来实现。
$ vi /data/mysqldata/3306/my.cnf
[mysqld]
log-slave-updates 

2. 重启relay slave使参数生效,注意:relay slave不是想停就能停的,正确步骤是先停止sql_thread线程,然后再停。
mysql> stop slave sql_thread; 

3. 查看当前二进制日志文件和位置:
mysql> show master status;


4. 关库:
$ mysqladmin -usystem -p123456 -S /data/mysqldata/3306/mysql.sock shutdown
5. 启库:
$ mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf &     #启库时不要指定用户名和密码

5. 从库3执行change master
mysql>
change master to
master_host='10.1.5.112',
master_port=3306,
master_user='rep',
master_password='replication',
master_log_file='mysql-bin.000001',
master_log_pos=308;

6. mysql> start slave;
7. mysql> show slave status\G



半同步机制:
MySQL本身不支持同步复制,如有需求,只能通过其他技术手段来实现。
从5.5开始,提供半同步复制,保障主从之间数据同步的及时性。半同步处于同步和异步之间,原理:主库在操作完成发给客户端之前,将事务发送给从库。半同步机制下,主库只要确认有一个从库接收到了事务,即可向客户端返回操作成功的信息,主库不需要等待从库也执行成功,只要有一个从库收到事务,并写入本地中继日志就算成功。
1. 配置半同步
半同步复制是以插件方式提供的,show plugins;显示当前安装的插件。
变量plugin_dir存放插件目录,存放自带但没有安装或来自第三方的插件。
mysql> show variables like 'plugin_dir';

[mysql@biee2 plugin]$ ll -thr
总计 1.3M
-rw-r--r-- 1 mysql mysql  227 07-15 05:34 daemon_example.ini
-rwxr-xr-x 1 mysql mysql 237K 09-01 13:53 ha_example.so
-rwxr-xr-x 1 mysql mysql  18K 09-01 14:08 mypluglib.so
-rwxr-xr-x 1 mysql mysql 154K 09-01 14:08 validate_password.so
-rwxr-xr-x 1 mysql mysql 411K 09-01 14:08 semisync_master.so
-rwxr-xr-x 1 mysql mysql 244K 09-01 14:08 semisync_slave.so
-rwxr-xr-x 1 mysql mysql  25K 09-01 14:08 auth.so
-rwxr-xr-x 1 mysql mysql  13K 09-01 14:08 qa_auth_server.so
-rwxr-xr-x 1 mysql mysql  24K 09-01 14:08 qa_auth_interface.so
-rwxr-xr-x 1 mysql mysql  18K 09-01 14:08 qa_auth_client.so
-rwxr-xr-x 1 mysql mysql  12K 09-01 14:08 mysql_no_login.so
-rwxr-xr-x 1 mysql mysql  25K 09-01 14:08 auth_test_plugin.so
-rwxr-xr-x 1 mysql mysql  12K 09-01 14:08 auth_socket.so
-rwxr-xr-x 1 mysql mysql  42K 09-01 14:08 libdaemon_example.so
-rwxr-xr-x 1 mysql mysql  16K 09-01 14:08 adt_null.so
drwxr-xr-x 2 mysql mysql 4.0K 09-01 14:26 debug

加载主库半同步插件:
在Master上执行:
 INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled=1; 
SET GLOBAL rpl_semi_sync_master_timeout=3000; (3s, default 10s) 主库等待从库响应的时间,若超时,则临时转为异步复制。

各个Slave上执行:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled=1; 

检查插件是否处于可用状态:
mysql> show plugins;
mysql> show variables like '%semi_sync%';

必须主从同时配置半同步,只配置一端相当于还是异步。建议将这三个参数写到my.cnf中,避免重启后失效。
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=3000
rpl_semi_sync_slave_enabled=1

从库重启io_thread进程:
mysql> stop slave io_thread;
mysql> start slave io_thread;  

2. 监控半同步复制环境
主库的Rpl_semi_sync_master_status,从库的Rpl_semi_sync_slave_status用于判断当前到底是半同步还是异步。
mysql> show status like '%rpl_semi_sync%'; 




复制环境中的故障切换:
从库随时可以通过change master修改其参照的master,change master并不检查与主库的兼容性问题。预备要切换成主库的从库,必须要指定log_bin参数。
变更为   

主库故障发生时,第一时间通过 show slave status\G 检查各从库slave状态信息,重点关注主库的日志文件、读取位置、已经执行过的日志位置(Master_Log_File、Read_Master_Log_Pos、Exec_Master_Log_Pos),从库IO和SQL线程运行状态,主从之间的延迟间隔Seconds_Behind_Master。也可以通过 show processlist\G  检查当前线程是否有“Slave has read all relay log”字眼,来交叉验证从库的数据应用情况。如果各节点返回的信息都相同,说明各节点数据处于一致性状态,此时可以进行切换。
登录到要切换成主库的从库,执行如下:
stop slave;
reset slave;    --清除与从库有关的配置,删除master.info/relay-log文件。
show master status;

其他从库:
stop slave;
change master to
master_host='10.1.5.117',
master_port=3307,
master_user='rep',
master_password='replication',
master_log_file='mysql-bin.000007',
master_log_pos=120;
start slave;
修改前端应用的连接地址,改为连接到新主库。



延迟切换:
mysql> stop slave;
mysql> change master to master_delay=10;  --从库延迟10秒应用主库变更

mysql> start slave;

通过mysql> show slave status\G 中的
SQL_Delay可以查看到当前设置的延迟秒数。
SQL_Remaining_Delay 当Slave_SQL_Running_State状态是Waiting until master_delay seconds after master executed event时,该列显示的是还有多长时间开始应用。
  评论这张
 
阅读(140)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018