通知设置 新通知
Mysql主从复制Seconds_Behind_Master说明
koyo 发表了文章 0 个评论 2873 次浏览 2016-06-25 22:35
在之前我一直误以为Seconds_Behind_Master是表示slave比master落后多少,如果这个值为0的表示主从已经处于一致了(在非同步模式下,现在官方最多也只在5.5中增加了半同步复制)。但是最近我终于认识到之前的错误理解。
首先我们需要明白的一点:Seconds_Behind_Master表示slave上SQL thread与IO thread之间的延迟,我们都知道在MySQL的复制环境中,slave先从master上将binlog拉取到本地(通过IO thread),然后通过SQL thread将binlog重放,而Seconds_Behind_Master表示本地relaylog中未被执行完的那部分的差值。手册上的定义:
In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.所以如果slave拉取到本地的relaylog(实际上就是binlog,只是在slave上习惯称呼relaylog而已)都执行完,此时通过show slave status看到的会是0,那么Seconds_Behind_Master的值为0是否表示主从已经处于一致了呢?
答案几乎是否定的!为什么几乎是否定的?因为绝大部分的情况下复制都是异步的,异步就意味着master上的binlog不是实时的发送到slave上,所以即使Seconds_Behind_Master的值为0依然不能肯定主从处于一致,这也是我之前强调非同步复制的原因(现在已经有公司在做同步复制了,比如网易自己实现了VSR,VirtualSynchronized Replication,由于同步复制性能较差,所以网易再实现同步复制的同时还打了group commit的补丁)。所以如果我们要以这个参数来估计主从延迟多久的话至少得在一个比较好的网络环境中,这样才能保证几乎master上的binlog都已经发送到slave上。
上面解释了Seconds_Behind_Master这个值的真正含义,那么它的值到底是怎么计算出来的呢?实际上在binlog中每个binlog events都会附上执行时的timestamp,所以在在确定Seconds_Behind_Master的值时MySQL是通过比较当前系统的时间戳与当前SQL thread正在执行的binlog event的上的时间戳做比较,这个差值就是Seconds_Behind_Master的值。也许你会有疑问那要是两台服务器之间的时钟不一致怎么办?确实会存在这种情况,那么此时这个值的可靠性就不大了,手册上对此也进行了说明:
This time difference computation works even if the master and slave do not have identical clock times, provided that the difference,Seconds_Behind_Master的值除了是非负数之外还可能是NULL,它是由如下几种情况导致的:
computed when the slave I/O thread starts, remains constant from then on. Any changes—including NTP updates—can lead to clock
skews that can make calculation of Seconds_Behind_Master less reliable
- []SQL thread没运行[/][]IO thread没运行[/][]slave没有连接到master。[/]
接下来再简单介绍一下异步复制/半同步复制之间的区别。
异步复制,master上的操作记录binlog的同时不关心binlog是否已经被slave接收。
半同步复制,master上的操作记录binlog的同时会关心binlog是否被slave接受。但是由于它的处理逻辑问题可能丢一个事务,如下图所示:
这样的处理流程存在一个问题,当存储引擎提交(storage commit)后,此时如果master挂了那么会存在主从不一致,对于这个问题orczhou好像自己对源码进行了修改更改storage commit的顺序来达到一个增强的半同步复制。
MySQL 复制夯住排查以及原理探讨
koyo 发表了文章 0 个评论 2620 次浏览 2016-06-25 21:36
研发反应,有台从库和主库不同步。由于业务读操作是针对从库的,数据不同步必定会带来数据的不一致,业务获取的结果会受影响,所以这个问题必须尽快解决。
登上服务器,查看 MySQL 的从库状态,并没有任何报错信息。刷新从库状态,发现状态没有任何变化,Exec_Master_Log_Pos 卡住不动。
二、故障分析
为了安全起见,此文略去 MySQL 版本以及其他可能会带来安全问题的信息,接下来逐步分析问题。
首先查看从库状态
mysql> SHOW SLAVE STATUS \G此时的 Slave_IO_State 为 Queueing master event to the relay log,而不是正常状态下的 Waiting for master to send event。刷新多次,状态没有任何变化,Exec_Master_Log_Pos 不变,从而导致 Seconds_Behind_Master 一直不变。
***********************[i][b][/i] 1. row [/b]*************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: masterIP
Master_User: replUser
Master_Port: masterPort
Connect_Retry: 60
Master_Log_File: binlog.000296
Read_Master_Log_Pos: 364027786
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 250
Relay_Master_Log_File: binlog.000283
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 594374863
Relay_Log_Space: 13803486573
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 256219
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: masterID
1 row in set (0.00 sec)
接下来查看 PROCESSLIST 状态:
mysql> SHOW FULL PROCESSLIST;从以上的结果来看,没有任何异常。
+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+
| 51378 | system user | | NULL | Connect | 1121183 | Waiting for master to send event | NULL |
| 88917 | system user | | NULL | Connect | 245327 | Reading event from the relay log | NULL |
| 106029 | userA | xxx.xxx.xxx.xxx:14057 | NULL | Sleep | 14504 | | NULL |
| 106109 | userA | xxx.xxx.xxx.xxx:15077 | databaseA | Sleep | 79 | | NULL |
| 106110 | userA | xxx.xxx.xxx.xxx:15081 | databaseA | Sleep | 13000 | | NULL |
| 106116 | userB | xxx.xxx.xxx.xxx:15096 | databaseA | Sleep | 357 | | NULL |
| 106117 | userB | xxx.xxx.xxx.xxx:15097 | NULL | Sleep | 12964 | | NULL |
| 106119 | root | localhost | NULL | Query | 0 | NULL | SHOW FULL PROCESSLIST |
| 106126 | userB | xxx.xxx.xxx.xxx:15173 | NULL | Sleep | 12856 | | NULL |
| 106127 | userB | xxx.xxx.xxx.xxx:15180 | databaseA | Sleep | 12849 | | NULL |
| 106766 | userA | xxx.xxx.xxx.xxx:17960 | databaseA | Sleep | 64 | | NULL |
+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+
11 rows in set (0.00 sec)
既然从上述信息中得不到任何对排查问题有帮助的信息,那么我们可以试着分析 MySQL 的 binlog,看 Pos 为 594374863 的点发生了什么操作。
分析日志我们可以使用 mysqlbinlog 命令,指定 start-position 为夯住的那个点,并重定向到文件。
/usr/local/mysql/bin/mysqlbinlog --no-defaults -v --start-position="594374863" \查看输出结果,发现端倪了,以下是摘抄的部分结果:
binlog.000283 > /XXX/binlog.sql
/[i]!40019 SET @@session.max_insert_delayed_threads=0[/i]/;从以上输出中,我们可以知道,从夯住的那个点开始,binlog 记录的信息就出现了异常,可以推测在主库有大操作。另外,针对出现问题库,查看主库和从库的表数量,发现从库的表数量多于主库,有几个临时表出现。可以推测的,主库有删表的操作,从库同步夯住,导致同步异常,主库删表的操作还没来得及同步到从库。
/[i]!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0[/i]/;
DELIMITER /[i]![/i]/;
# at 4
#150814 17:43:15 server id 21 end_log_pos 107 Start: binlog v 4, server v x.x.xx-log created 150814 17:43:15
BINLOG '
M7jNVQ8VAAAAZwAAAGsAAAAAAAQANS41LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/[i]![/i]/;
# at 594374863
#150814 18:09:36 server id 21 end_log_pos 594374945 Query thread_id=210702841 exec_time=43 error_code=0
SET TIMESTAMP=1439546976/[i]![/i]/;
SET @@session.pseudo_thread_id=210702841/[i]![/i]/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/[i]![/i]/;
SET @@session.sql_mode=0/[i]![/i]/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/[i]![/i]/;
/[i]!\C utf8 [/i]//[i]![/i]/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/[i]![/i]/;
SET @@session.lc_time_names=0/[i]![/i]/;
SET @@session.collation_database=DEFAULT/[i]![/i]/;
BEGIN
/[i]![/i]/;
# at 594374945
# at 594375036
# at 594376047
# at 594377085
# at 594378123
# at 594379152
# at 594380187
# at 594381165
# at 594382194
# at 594383223
# at 594384252
# at 594385269
# at 594386307
# at 594387282
# at 594388299
# at 594389265
# at 594390270
# at 594391299
# at 594392310
# at 594393327
# at 594394344
# at 594395340
# at 594396336
# at 594397332
经过和研发沟通,确认了两点。第一,确实有大操作,程序有大量的批量插入,而且是用的 LOAD DATA LOCAL INFILE;第二,主库确实有删表的操作,这几张表都是临时表。
三、故障解决
既然问题找到了,那解决办法自然就有了。既然从库的表多于主库,而且这几张表是临时数据,我们可以过滤掉对这几张表的同步操作。具体思路如下:在主库备份临时表(虽然研发说数据不重要,但还是以防万一,DBA 还是谨慎为好),然后通知研发临时切走从库的流量,修改配置文件,指定 replicate-ignore-table 参数,重启 MySQL。
接下来就是具体的解决步骤,首先备份数据。备份时不加 –master-data 参数和 –single-transaction。究其原因,–master-data 禁用 –lock-tables 参数,在和 –single-transaction 一起使用时会禁用 –lock-all-tables。在备份开始时,会获取全局 read lock。 –single-transaction 参数设置默认级别为 REPEATABLE READ,并且在开始备份时执行 START TRANSACTION。在备份期间, 其他连接不能执行如下语句:ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE。MySQL 同步夯住,如果加了上述参数,mysqldump 也会夯住。mysqldump 会 FLUSH TABLES、LOCK TABLES,如果有 –master-data 参数,会导致 Waiting for table flush。同样,有 –single-transaction 参数,仍然会导致 Waiting for table flush。另外,还可以看到 Waiting for table metadata lock,此时做了 DROP TABLE 的操作。此时可以停掉 MySQL 同步来避免这个问题。
为了保险起见,我们在主库加大 expire_logs_days 参数,避免 binlog 丢失。
mysql> SHOW VARIABLES LIKE '%expire%';接着修改从库的配置文件:
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 3 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> SET GLOBAL expire_logs_days=5;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE '%expire%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 5 |
+------------------+-------+
1 row in set (0.00 sec)
vim /xxx/xxxx/xxx/my.cnf在 mysqld 后,加入如下配置:
replicate-ignore-table=databaseA.tableA然后重启 MySQL:
replicate-ignore-table=databaseA.tableB
replicate-ignore-table=databaseA.tableC
replicate-ignore-table=databaseA.tableD
/xxx/xxx/xxx/xxx/mysqld restart登录 MySQL 从库,查看从库状态,并定时刷新状态,我们可以看到的是,Exec_Master_Log_Pos 在递增,Seconds_Behind_Master 在递减,证明问题已经解决了。
mysql> SHOW SLAVE STATUS \G需要注意的是,待同步完成后,需要把从库配置文件中增加的 replicate-ignore-table参数注释,并重启 MySQL。
***********************[i][b][/i] 1. row [/b]*************************
Slave_IO_State: Waiting for master to send event
Master_Host: masterIP
Master_User: replUser
Master_Port: masterPort
Connect_Retry: 60
Master_Log_File: binlog.000319
Read_Master_Log_Pos: 985656691
Relay_Log_File: relaylog.000004
Relay_Log_Pos: 709043542
Relay_Master_Log_File: binlog.000284
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: databaseA.tableA,databaseA.tableB,databaseA.tableC,databaseA.tableD
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 709043399
Relay_Log_Space: 38579192969
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 258490
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: masterID
1 row in set (0.00 sec)
四、原理探讨
在主库运行 LOAD DATA LOCAL INFILE,主库和从库时这样同步的:
在主节点:
- []执行 LOAD DATA LOCAL INFILE;[/][]拷贝使用的整个文本文件内容到二进制日志;[/][]添加 LOAD DATA LOCAL INFILE 到最新的二进制日志。[/]
复制所有主库的二进制日志到从库的中继日志;
在从节点:
- []检查中继日志中的文本文件;[/][]从多个中继日志文件中读取所有的块;[/][]文本文件存放在 /tmp 文件夹中;[/][]从中继日志中读取 LOAD DATA LOCAL INFILE;[/][]在 SQL 线程中执行 LOAD DATA LOCAL INFILE。[/]
在从节点执行的 1-4 步骤中,IO 线程会呈现 Reading event from the relay log 状态,持续地为下一个 LOAD DATA LOCAL INFILE 命令提取 CSV 行。此时从库会持续落后,一旦从库落后时间较长,会导致 SQL 线程阻塞,呈现 Queueing master event to the relay log 状态,从而复制夯住。
五、小结
这样的故障,归根结底还是研发写的程序还有优化的余地。大批量的数据插入,这在 MySQL 中是不推荐使用的。我们可以这样:第一,一条 SQL 语句插入多条数据;第二,在事务中进行插入处理;第三,分批插入,在程序中设置 auto_commit 为 0,分批插入完成后,手动 COMMIT;第四,需要使用 LOAD DATA LOCAL INFILE 时,设置 sync_binlog 为 1。
分享原文:https://dbarobin.com/2015/08/22/mysql-replication-hanging/
作者:温国兵老师
删除清空Mysql表中数据记录
koyo 发表了文章 0 个评论 2209 次浏览 2016-06-25 00:18
delete from Table_Name;不带where参数的delete语句可以删除mysql表中所有内容,使用truncate table也可以清空mysql表中所有内容。
truncate table Table_Name;
效率上truncate比delete快,但truncate删除后不记录mysql日志,不可以恢复数据。
delete的效果有点像将mysql表中所有记录一条一条删除到删完,而truncate相当于保留mysql表的结构,重新创建了这个表,所有的状态都相当于新表。
Mysql通过binlog恢复数据
koyo 发表了文章 0 个评论 2655 次浏览 2016-06-24 23:49
Mysql开启binlog记录如下:
[mysqld]用mysqlbinlog可以查看binlog日志里的信息
log-bin=/data/mysqldir/binlog/mysql-binlog //定义binlog路径及名称
1、根据位置恢复
# mysqlbinlog --start-position=144 --stop-position=240 mysql-binglog.000002 > /data/sql/pos.sql然后导入就好
mysql> source /data/sql/pos.sql
2、根据日期
# ./mysqlbinlog --start-date="2016-06-23 19:30:07" --stop-date="2016-06-24 17:21:28" mysql-binlog.000003 > /root/sql/time.sql导入数据
mysql> source /root/sql/time.sqlOR
#./mysqlbinlog --start-date=如果有多个binlog文件,中间用空格隔开,打上完全路径。
"2016-06-23 19:30:07" --stop-date="2016-06-24 17:21:28" mysql-binlog.000003 |mysql -u root -p'admin@login'
Mysql的log_slave_updates参数说明
koyo 发表了文章 0 个评论 4093 次浏览 2016-06-23 21:17
架构说明:最近部署了MySQL的集群环境,详细情况如上图所示MMA和MMB为主主复制,MMA和MSA为主从复制;在测试的过程中发现了以下问题:
- []MMA和MMB的主主复制没有问题(从MMA写入数据能同步到MMB,从MMB写入数据也能够同步到MMA);[/][]MMA和MSA主从复制也没有问题,当从MMA写入的时候,数据可以写入到MSA;[/][]但是当从MMB写入的时候,数据就不能写入到MSA;[/]
问题原因是:MMA Mysql参数log_slave_updates参数的状态为NO。
MySQL的官网说明如下:
Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. Prior to MySQL 5.5, the server would not start when using the --log-slave-updates option without also starting the server with the --log-bin option, and would fail with an error; in MySQL 5.5, only a warning is generated. (Bug #44663) --log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:A -> B -> C
Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log.
- []MMA从MMB同步数据过来的时候,log_slave_updates参数用来控制MMA是否把所有的操作写入到binary log,默认的情况下mysql是关闭的;[/]
- []MSA数据的更新需要通过读取到MMA的binary log才能进行更新,当从MMB写入数据的时候MMA是没有写binary log的,所以MRA也就没有更新操作。[/]
问题的解决方法:
log_slave_updates:默认值为OFF; Dynamic Variable:NO
处理方法:修改配置文件my.cnf,在mysql数据库MMA的配置文件[mysqld]下增加一行log_slave_updates=1,然后暂停所有数据库的同步> stop slave; 最后重启MMA数据库让参数生效,打开所有数据库的同步> start slave;
总结:
设置完该参数后,数据库的架构MMA和MMB为主主同步,MSA通过MMA进行主从同步就完善了!
应用的写操作中MMB上面进行,读操作中MSA上面进行(如果读操作很多的话,可以在MMA上面架设多台只读数据库),当MMB发生故障后,系统的写操作自动迁移到MMA上面。这种架构基本可以保证大部分公司的应用需求。
Mysql的binlog介绍和数据查看
koyo 发表了文章 0 个评论 3058 次浏览 2016-06-20 09:44
1、binlog,即二进制日志,它记录了数据库上的所有改变.
2、改变数据库的SQL语句执行结束时,将在binlog的末尾写入一条记录,同时通知语句解析器,语句执行完毕.
3、binlog格式
- []基于语句,无法保证所有语句都在从库执行成功,比如update ... limit 1;[/][]基于行,将每一次改动记为binlog中的一行.在执行一个特别复杂的update或者delete操作时,基于行的格式会有优势.[/]
show binlog events;2、查看指定binlog文件的内容
show binlog events in 'mysql-bin.000013';3、查看当前正在写入的binlog文件
show master status\G4、获取binlog文件列表
show binary logs;方式二:用mysqlbinlog工具查看需要注意的事件:[list=1][]不要查看当前正在写入的binlog文件[/][]不要加--force参数强制访问[/][]如果binlog格式是行模式的,请加 -vv参数[/]
本地查看
1、基于开始/结束时间
mysqlbinlog --start-datetime='2016-06-19 00:00:00' --stop-datetime='2016-09-19 02:01:01' -d 库名 二进制文件2、基于pos值
mysqlbinlog --start-postion=307 --stop-position=1000 -d 库名 二进制文件
远程查看
指定开始/结束时间,并把结果重定向到本地t.binlog文件中.
mysqlbinlog -u username -p password -hMysql_MasterA -P3306 \
--read-from-remote-server --start-datetime='2016-06-10 23:00:00' --stop-datetime='2016-06-10 23:30:00' mysql-bin.000003 > t.binlog
不同场景下 MySQL 的迁移方案
koyo 发表了文章 0 个评论 2217 次浏览 2016-06-19 23:40
MySQL 迁移是 DBA 日常维护中的一个工作。迁移,究其本义,无非是把实际存在的物体挪走,保证该物体的完整性以及延续性。就像柔软的沙滩上,两个天真无邪的小孩,把一堆沙子挪向其他地方,铸就内心神往的城堡。
生产环境中,有以下情况需要做迁移工作,如下:
- []磁盘空间不够。比如一些老项目,选用的机型并不一定适用于数据库。随着时间的推移,硬盘很有可能出现短缺;[/][]业务出现瓶颈。比如项目中采用单机承担所有的读写业务,业务压力增大,不堪重负。如果 IO 压力在可接受的范围,会采用读写分离方案;[/][]机器出现瓶颈。机器出现瓶颈主要在磁盘 IO 能力、内存、CPU,此时除了针对瓶颈做一些优化以外,选择迁移是不错的方案;[/][]项目改造。某些项目的数据库存在跨机房的情况,可能会在不同机房中增加节点,或者把机器从一个机房迁移到另一个机房。再比如,不同业务共用同一台服务器,为了缓解服务器压力以及方便维护,也会做迁移。[/]
- []研发将 102 的读业务切到主库;[/][]确认 102 MySQL 状态(主要看 PROCESS LIST,MASTER STATUS),观察机器流量,确认无误后,停止 102 从节点的服务;[/][]104 新建 MySQL 实例,建成以后,停止 MySQL 服务,并且将整个数据目录 mv 到其他地方做备份,注意,此处操作的是 104,也就是未来的从库;[/][]将 102 的整个 mysql 数据目录使用 rsync 拷贝到 104;[/][]拷贝的同时,在 101 授权,使 104 有拉取 binlog 的权限(REPLICATION SLAVE, REPLICATION CLIENT);[/][]待拷贝完成,修改 104 配置文件中的 server_id,注意不要和 102 上的一致;[/][]在 104 启动 MySQL 实例,注意配置文件中的数据文件路径以及数据目录的权限;[/][]进入 104 MySQL 实例,使用 SHOW SLAVE STATUS 检查从库状态,可以看到 Seconds_Behind_Master 在递减;[/][]Seconds_Behind_Master 变为 0 后,表示同步完成,此时可以用 pt-table-checksum 检查 101 和 104 的数据一致,但比较耗时,而且对主节点有影响,可以和开发一起进行数据一致性的验证;[/][]除了做数据一致性验证外,还需要验证账号权限,以防业务迁走后访问出错;[/][]和研发协作,将之前 102 从节点的读业务切到 104;[/][]利用 102 的数据,将 103 变为 101 的从节点,方法同上;[/][]接下来到了关键的地方了,我们需要把 104 变成 103 的从库;[/][]104 STOP SLAVE;[/]
- []业务迁移之前,断掉 103 和 101 的同步关系;[/][]做完上述步骤,可以和研发协调,把 101 的读写业务切回 102,读业务切到 104。需要注意的是,此时 101 和 103 均可以写,需要保证 101 在没有写入的情况下切到 103,可以使用 FLUSH TABLES WITH READ LOCK 锁住 101,然后业务切到 103。注意,一定要业务低峰执行,切记;[/][]切换完成后,观察业务状态;[/][]如果业务没有问题,证明迁移成功。[/]
- []1.113 针对 7936 实例使用 innobackupex 做数据备份,注意需要指定数据库,并且加上 slave-info 参数;[/][]备份完成后,将压缩文件拷贝到 2.117;[/][]2.117 创建数据目录以及配置文件涉及的相关目录;[/][]2.117 使用 innobackupex 恢复日志;[/][]2.117 使用 innobackupex 拷贝数据;[/][]2.117 修改配置文件,注意如下参数:replicate-ignore-db、innodb_file_per_table = 1、read_only = 1、 server_id;[/][]2.117 更改数据目录权限;[/][]1.112 授权,使 2.117 有拉取 binlog 的权限(REPLICATION SLAVE, REPLICATION CLIENT);[/][]2.117 CHANGE MASTE TO 1.112,LOG FILE 和 LOG POS 参考 xtrabackup_slave_info;[/][]2.117 START SLAVE,查看从库状态;[/][]2.117 上建立 7939 的方法类似,不过配置文件需要指定 replicate-wild-do-table;[/][]和开发一起进行数据一致性的验证和验证账号权限,以防业务迁走后访问出错;[/][]做完上述步骤,可以和研发协调,把相应业务迁移到 2.117 的 7938 实例和 7939 实例。观察业务状态;[/][]如果业务没有问题,证明迁移成功。[/]
- []数据库迁移,如果涉及事件,记住主节点打开 event_scheduler 参数;[/][]不管什么场景下的迁移,都要随时关注服务器状态,比如磁盘空间,网络抖动;另外,对业务的持续监控也是必不可少的;[/][]CHANGE MASTER TO 的 LOG FILE 和 LOG POS 切记不要找错,如果指定错了,带来的后果就是数据不一致或者搭建主从关系失败;[/][]执行脚本不要在 $HOME 目录,记住在数据目录;[/][]迁移工作可以使用脚本做到自动化,但不要弄巧成拙,任何脚本都要经过测试;[/][]每执行一条命令都要三思和后行,每个命令的参数含义都要搞明白;[/][]多实例环境下,关闭 MySQL 采用 mysqladmin 的形式,不要把正在使用的实例关闭了;[/][]从库记得把 read_only = 1 加上,这会避免很多问题;[/][]每台机器的 server_id 必须保证不一致,否则会出现同步异常的情况;[/][]正确配置 replicate-ignore-db 和 replicate-wild-do-table;[/][]新建的实例记得把 innodb_file_per_table 设置为 1,上述中的部分场景,因为之前的实例此参数为 0,导致 ibdata1 过大,备份和传输都消耗了很多时间;[/][]使用 gzip 压缩数据时,注意压缩完成后,gzip 会把源文件删除;[/][]所有的操作务必在从节点或者备节点操作,如果在主节点操作,主节点很可能会宕机;[/][]xtrabackup 备份不会锁定 InnoDB 表,但会锁定 MyISAM 表。所以,操作之前记得检查下当前数据库的表是否有使用 MyISAM 存储引擎的,如果有,要么单独处理,要么更改表的 Engine。[/]
- []任何迁移 LOG FILE 以 relay_master_log_file(正在同步 master 上的 binlog 日志名)为准,LOG POS 以 exec_master_log_pos(正在同步当前 binlog 日志的 POS 点)为准;[/][]使用 rsync 拷贝数据,可以结合 expect、nohup 使用,绝对是绝妙组合;[/][]在使用 innobackupex 备份数据的同时可以使用 gzip 进行压缩;[/][]在使用 innobackupex 备份数据,可以加上 --slave-info 参数,方便做从库;[/][]在使用 innobackupex 备份数据,可以加上 --throttle 参数,限制 IO,减少对业务的影响。还可以加上 --parallel=n 参数,加快备份,但需要注意的是,使用 tar 流压缩,--parallel 参数无效;[/][]做数据的备份与恢复,可以把待办事项列个清单,画个流程,然后把需要执行的命令提前准备好;[/][]本地快速拷贝文件夹,有个不错的方法,使用 rsync,加上如下参数:-avhW --no-compress --progress;[/][]不同分区之间快速拷贝数据,可以使用 dd。或者用一个更靠谱的方法,备份到硬盘,然后放到服务器上。异地还有更绝的,直接快递硬盘。[/]
- []第一,迁移的目的是让业务平稳持续地运行;[/][]第二,迁移的核心是怎么延续主从同步,我们需要在不同服务器和不同业务之间找到方案;[/][]第三,业务切换需要考虑不同 MySQL 服务器之间的权限问题;需要考虑不同机器读写分离的顺序以及主从关系;需要考虑跨机房调用对业务的影响。[/]
读者在实施迁移的过程中,可以参考此文提供的思路。但怎样保证每个操作正确无误地运行,还需要三思而后行。
分享阅读原文:https://dbarobin.com/2015/09/15/migration-of-mysql-on-different-scenes/
文章作者:温国兵老师
Mysql各类备份引擎适配
Ansible 发表了文章 0 个评论 2603 次浏览 2016-06-16 15:39
对于大规模备份,考虑使用物理方法:
- []mysqlbackup(适于InnoDB、MyISAM及其他表)[/][]mysqlhotcopy(适于MyISAM表)[/][]Xtrabackup(适于InnoDB及MyISAM表)[/][]LVM(适于各类表)[/]
MySQL高可用之MHA
chris 发表了文章 0 个评论 2646 次浏览 2016-05-10 22:56
MHA是由日本人yoshinorim(原就职于DeNA现就职于FaceBook)开发的比较成熟的MySQL高可用方案。MHA能够在30秒内实现故障切换,并能在故障切换中,最大可能的保证数据一致性。目前淘宝也正在开发相似产品TMHA,目前已支持一主一从。
MHA架构
MHA由MHA Manager和MHA Node组成,如下图所示:
MHA Manager:
运行一些工具,比如masterha_manager工具实现自动监控MySQL Master和实现master故障切换,其它工具实现手动实现master故障切换、在线mater转移、连接检查等等。一个Manager可以管理多个master-slave集群
MHA Node:
部署在所有运行MySQL的服务器上,无论是master还是slave。主要作用有三个。
Ⅰ、保存二进制日志
如果能够访问故障master,会拷贝master的二进制日志
II、应用差异中继日志
从拥有最新数据的slave上生成差异中继日志,然后应用差异日志。
III、清除中继日志
在不停止SQL线程的情况下删除中继日志
MHA工作原理
当master出现故障时,通过对比slave之间I/O线程读取masterbinlog的位置,选取最接近的slave做为latestslave。其它slave通过与latest slave对比生成差异中继日志。在latest slave上应用从master保存的binlog,同时将latest slave提升为master。最后在其它slave上应用相应的差异中继日志并开始从新的master开始复制。
在MHA实现Master故障切换过程中,MHA Node会试图访问故障的master(通过SSH),如果可以访问(不是硬件故障,比如InnoDB数据文件损坏等),会保存二进制文件,以最大程度保证数据不丢失。MHA和半同步复制一起使用会大大降低数据丢失的危险。
当前高可用方案
Heartbeat+DRBD:
- []开销:需要额外添加处于被动状态的master server(并不处理应用流量)[/][]性能:为了实现DRBD复制环境的高可用,innodb-flush-log-at-trx-commit和sync-binlog必须设置为1,这样会导致写性能下降。[/][]一致性:在master上必要的binlog时间可能会丢失,这样slave就无法进行复制,导致产生数据一致性问题[/]
- []半同步复制大大减少了“binlog events只存在故障master上”的问题。[/][]在提交时,保证至少一个slave(并不是所有的)接收到binlog,因此一些slave可能没有接收到binlog。[/]
- []在二进制文件中添加全局事务ID(global transaction id)需要更改binlog格式,在5.1/5.5版本中不支持。[/][]在应用方面有很多方法可以直线全局事务ID,但是仍避免不了复杂度、性能、数据丢失或者一致性的问题。[/]
PXC:
PXC实现了服务高可用,数据同步时是并发复制。但是仅支持InnoDB引擎,所有的表都要有主键。锁冲突、死锁问题相对较多等等问题。
MHA的优势
1、故障切换快
在主从复制集群中,只要从库在复制上没有延迟,MHA通常可以在数秒内实现故障切换。9-10秒内检查到master故障,可以选择在7-10秒关闭master以避免出现裂脑,几秒钟内,将差异中继日志(relay log)应用到新的master上,因此总的宕机时间通常为10-30秒。恢复新的master后,MHA并行的恢复其余的slave。即使在有数万台slave,也不会影响master的恢复时间。
DeNA在超过150个MySQL(主要5.0/5.1版本)主从环境下使用了MHA。当mater故障后,MHA在4秒内就完成了故障切换。在传统的主动/被动集群解决方案中,4秒内完成故障切换是不可能的。
2、master故障不会导致数据不一致
当目前的master出现故障是,MHA自动识别slave之间中继日志(relay log)的不同,并应用到所有的slave中。这样所有的salve能够保持同步,只要所有的slave处于存活状态。和Semi-Synchronous Replication一起使用,(几乎)可以保证没有数据丢失。
3、无需修改当前的MySQL设置
MHA的设计的重要原则之一就是尽可能地简单易用。MHA工作在传统的MySQL版本5.0和之后版本的主从复制环境中。和其它高可用解决方法比,MHA并不需要改变MySQL的部署环境。MHA适用于异步和半同步的主从复制。
启动/停止/升级/降级/安装/卸载MHA不需要改变(包扩启动/停止)MySQL复制。当需要升级MHA到新的版本,不需要停止MySQL,仅仅替换到新版本的MHA,然后重启MHA Manager就好了。
MHA运行在MySQL 5.0开始的原生版本上。一些其它的MySQL高可用解决方案需要特定的版本(比如MySQL集群、带全局事务ID的MySQL等等),但并不仅仅为了master的高可用才迁移应用的。在大多数情况下,已经部署了比较旧MySQL应用,并且不想仅仅为了实现Master的高可用,花太多的时间迁移到不同的存储引擎或更新的前沿发行版。MHA工作的包括5.0/5.1/5.5的原生版本的MySQL上,所以并不需要迁移。
4、无需增加大量的服务器
MHA由MHA Manager和MHA Node组成。MHA Node运行在需要故障切换/恢复的MySQL服务器上,因此并不需要额外增加服务器。MHA Manager运行在特定的服务器上,因此需要增加一台(实现高可用需要2台),但是MHA Manager可以监控大量(甚至上百台)单独的master,因此,并不需要增加大量的服务器。即使在一台slave上运行MHA Manager也是可以的。综上,实现MHA并没用额外增加大量的服务。
5、无性能下降
MHA适用与异步或半同步的MySQL复制。监控master时,MHA仅仅是每隔几秒(默认是3秒)发送一个ping包,并不发送重查询。可以得到像原生MySQL复制一样快的性能。
6、适用于任何存储引擎
MHA可以运行在只要MySQL复制运行的存储引擎上,并不仅限制于InnoDB,即使在不易迁移的传统的MyISAM引擎环境,一样可以使用MHA。
分享阅读:原文