Category Archives: mysql

Design for “seckilling ” on MySQL

Today I read a nice article , which give a new design for mysql database . In this article , Author (Mr.lou) uses OneSQL (a branch of MySQL) for the whole test.

you can read from : http://www.onexsoft.com/?page_id=378

This article explain how to use mysql in seckilling scenario (especially for E-Commerce commany )

If you want to use transaction in RDBMS database, you must manual start transaction and commit in the end of your operation.
So,commit will be cost of the total process, round-trip cost will be the most important cost as author said

(For simple update operations, without regard to conflict of IO and locks, a statement execution time is about 0.1ms, network delay under normal conditions is 0.4-0.8ms, that the waiting time is longer than the transaction commit time to really notice the SQL execution several times, showing that the network is a single-line transaction update performance killer)

How to avoid this ? We can start a transaction to manually control SQL commit. When autocommit=1 was set on MySQL server implicitly commit will happen after every SQLs.

An explicit transaction is when statement(s) are wrapped within an explicitly defined transaction code block – for MySQL, that’s START TRANSACTION. It also requires an explicitly made COMMIT or ROLLBACK statement at the end of the transaction. Nested transactions is beyond the scope of this topic.

Implicit transactions are slightly different from explicit ones. Implicit transactions do not require explicity defining a transaction. However, like explicit transactions they require a COMMIT or ROLLBACK statement to be supplied.

Using OneSQL ,Commit operation can be done on MySQL server side to avoid network round-trip. In total logical process, everything will be controlled by yourself .

In seckilling scenario, this optimization will give a tremendous performance promotion.

Actually ,we can also use Redis or memcached for this situation, Redis CAS feature witch MULTI in serial process will be a good choice.

Best practice – try to offload request before database. Order creation should be asynchronous (put operations in a serial async queue is the best choice)

基于消息队列的DW构建

基于消息队列的DW构建

多数公司在拆分数据库的同时需要考虑到汇总的问题,因为很多业务涉及到聚合,分类的需求。

可能遇到的问题如下:

1. 如何实时的获取聚合数据
2. 如何构建数据仓库以支持后续的分析。

我的上家公司采取的策略如下

在应用与数据库之间构造中间层聚合,在所有获取数据端通过内存运算得到业务需求,通过细分SQL,拆分SQL到不同节点从而保证数据的正确性,这点与TDDL也是类似的。

数仓层面,通过自定义ETL,从所有数据源抽取数据,落入Exadata集群,离线日志等数据落入hadoop集群。从而区分不同时间维度,差异类别需求的分析。

这里给出一个新的思路方案

结合Canal+roma (之前发过PDF) 通过消费roma的实时消息队列,构建一个实时的DW,因此一些简单的实时查询可以直接在DW上实现,而不需要依赖中间层的内存聚合。

DW可以采用MySQL实现,与前端MySQL采用源生复制技术,从而简化了整个ETL抽取过程。

这个架构的缺点在于:MySQL应对海量数据的时候能力不足,所以后面还要接入hadoop,hive等分析平台来处理各种日志类型数据。

目前一般的BI系统都是基于商业软件来实现的。对于中小型公司,这种架构可以节省很大的成本。

Client端的消息聚合:

etl_roma

拆分以及汇总:

split1split2

整体架构:

DW_arch

MHA with binlog server

In this post ,we mainly talk about MHA GTID behavior, we test different cases and find something is different from previous versions .

we have four machines for this test.

environment:


master server: 10.0.128.77
slave server : 10.0.128.110/113/114
port : 3306 

————————————————————————————–

we first do normal failover .

kill master server

we find MHA outputs:


Tue Dec 30 13:32:14 2014 - [warning] Got error on MySQL connect ping: DBI connect(';host=10.0.128.77;port=3306;mysql_connect_timeout=1','dbadmin',...) failed: Lost connection to MySQL server at 'reading initial communication packet', system error: 111 at /usr/share/perl5/vendor_perl/MHA/HealthCheck.pm line 97
2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Tue Dec 30 13:32:14 2014 - [info] Executing secondary network check script: /usr/bin/masterha_secondary_check -s 10.0.128.113 -s 10.0.128.114  --user=root  --master_host=10.0.128.77  --master_ip=10.0.128.77  --master_port=3306 --master_user=dbadmin --master_password=NV7yVBpn88cg4WJCVlZd --ping_type=CONNECT
Tue Dec 30 13:32:14 2014 - [info] Executing SSH check script: exit 0
Tue Dec 30 13:32:14 2014 - [info] HealthCheck: SSH to 10.0.128.77 is reachable.
Monitoring server 10.0.128.113 is reachable, Master is not reachable from 10.0.128.113. OK.
Monitoring server 10.0.128.114 is reachable, Master is not reachable from 10.0.128.114. OK.
Tue Dec 30 13:32:14 2014 - [info] Master is not reachable from all other monitoring servers. Failover should start.
Tue Dec 30 13:32:16 2014 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Tue Dec 30 13:32:16 2014 - [warning] Connection failed 2 time(s)..
Tue Dec 30 13:32:18 2014 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Tue Dec 30 13:32:18 2014 - [warning] Connection failed 3 time(s)..
Tue Dec 30 13:32:20 2014 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Tue Dec 30 13:32:20 2014 - [warning] Connection failed 4 time(s)..
Tue Dec 30 13:32:20 2014 - [warning] Master is not reachable from health checker!
Tue Dec 30 13:32:20 2014 - [warning] Master 10.0.128.77(10.0.128.77:3306) is not reachable!
Tue Dec 30 13:32:20 2014 - [warning] SSH is reachable.
Tue Dec 30 13:32:20 2014 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/masterha/test.cnf again, and trying to connect to all servers to check server status..
Tue Dec 30 13:32:20 2014 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Dec 30 13:32:20 2014 - [info] Reading application default configuration from /etc/masterha/test.cnf..
Tue Dec 30 13:32:20 2014 - [info] Reading server configuration from /etc/masterha/test.cnf..
Tue Dec 30 13:32:20 2014 - [info] GTID failover mode = 1
Tue Dec 30 13:32:20 2014 - [info] Dead Servers:
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.77(10.0.128.77:3306)
Tue Dec 30 13:32:20 2014 - [info] Alive Servers:
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.110(10.0.128.110:3306)
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.113(10.0.128.113:3306)
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.114(10.0.128.114:3306)
Tue Dec 30 13:32:20 2014 - [info] Alive Slaves:
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.110(10.0.128.110:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 13:32:20 2014 - [info]     GTID ON
Tue Dec 30 13:32:20 2014 - [info]     Replicating from 10.0.128.77(10.0.128.77:3306)
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.113(10.0.128.113:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 13:32:20 2014 - [info]     GTID ON
Tue Dec 30 13:32:20 2014 - [info]     Replicating from 10.0.128.77(10.0.128.77:3306)
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.114(10.0.128.114:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 13:32:20 2014 - [info]     GTID ON
Tue Dec 30 13:32:20 2014 - [info]     Replicating from 10.0.128.77(10.0.128.77:3306)
Tue Dec 30 13:32:20 2014 - [info] Checking slave configurations..
Tue Dec 30 13:32:20 2014 - [info]  read_only=1 is not set on slave 10.0.128.110(10.0.128.110:3306).
Tue Dec 30 13:32:20 2014 - [info]  read_only=1 is not set on slave 10.0.128.113(10.0.128.113:3306).
Tue Dec 30 13:32:20 2014 - [info]  read_only=1 is not set on slave 10.0.128.114(10.0.128.114:3306).
Tue Dec 30 13:32:20 2014 - [info] Checking replication filtering settings..
Tue Dec 30 13:32:20 2014 - [info]  Replication filtering check ok.
Tue Dec 30 13:32:20 2014 - [info] Master is down!
Tue Dec 30 13:32:20 2014 - [info] Terminating monitoring script.
Tue Dec 30 13:32:20 2014 - [info] Got exit code 20 (Master dead).
Tue Dec 30 13:32:20 2014 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Dec 30 13:32:20 2014 - [info] Reading application default configuration from /etc/masterha/test.cnf..
Tue Dec 30 13:32:20 2014 - [info] Reading server configuration from /etc/masterha/test.cnf..
Tue Dec 30 13:32:20 2014 - [info] MHA::MasterFailover version 0.56.
Tue Dec 30 13:32:20 2014 - [info] Starting master failover.
Tue Dec 30 13:32:20 2014 - [info] 
Tue Dec 30 13:32:20 2014 - [info] * Phase 1: Configuration Check Phase..
Tue Dec 30 13:32:20 2014 - [info] 
Tue Dec 30 13:32:20 2014 - [info] GTID failover mode = 1
Tue Dec 30 13:32:20 2014 - [info] Dead Servers:
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.77(10.0.128.77:3306)
Tue Dec 30 13:32:20 2014 - [info] Checking master reachability via MySQL(double check)...
Tue Dec 30 13:32:20 2014 - [info]  ok.
Tue Dec 30 13:32:20 2014 - [info] Alive Servers:
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.110(10.0.128.110:3306)
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.113(10.0.128.113:3306)
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.114(10.0.128.114:3306)
Tue Dec 30 13:32:20 2014 - [info] Alive Slaves:
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.110(10.0.128.110:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 13:32:20 2014 - [info]     GTID ON
Tue Dec 30 13:32:20 2014 - [info]     Replicating from 10.0.128.77(10.0.128.77:3306)
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.113(10.0.128.113:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 13:32:20 2014 - [info]     GTID ON
Tue Dec 30 13:32:20 2014 - [info]     Replicating from 10.0.128.77(10.0.128.77:3306)
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.114(10.0.128.114:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 13:32:20 2014 - [info]     GTID ON
Tue Dec 30 13:32:20 2014 - [info]     Replicating from 10.0.128.77(10.0.128.77:3306)
Tue Dec 30 13:32:20 2014 - [info] Starting GTID based failover.
Tue Dec 30 13:32:20 2014 - [info] 
Tue Dec 30 13:32:20 2014 - [info] ** Phase 1: Configuration Check Phase completed.
Tue Dec 30 13:32:20 2014 - [info] 
Tue Dec 30 13:32:20 2014 - [info] * Phase 2: Dead Master Shutdown Phase..
Tue Dec 30 13:32:20 2014 - [info] 
Tue Dec 30 13:32:20 2014 - [info] Forcing shutdown so that applications never connect to the current master..
Tue Dec 30 13:32:20 2014 - [info] Executing master IP deactivation script:
Tue Dec 30 13:32:20 2014 - [info]   /usr/local/dbadmin/mhascripts/master_ip_failover_pajk01.pl --orig_master_host=10.0.128.77 --orig_master_ip=10.0.128.77 --orig_master_port=3306 --command=stopssh --ssh_user=root  
Disabling the VIP on old master: 10.0.128.77 
RTNETLINK answers: Cannot assign requested address
Tue Dec 30 13:32:20 2014 - [info]  done.
Tue Dec 30 13:32:20 2014 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Tue Dec 30 13:32:20 2014 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Tue Dec 30 13:32:20 2014 - [info] 
Tue Dec 30 13:32:20 2014 - [info] * Phase 3: Master Recovery Phase..
Tue Dec 30 13:32:20 2014 - [info] 
Tue Dec 30 13:32:20 2014 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Tue Dec 30 13:32:20 2014 - [info] 
Tue Dec 30 13:32:20 2014 - [info] The latest binary log file/position on all slaves is master-bin.000001:3679
Tue Dec 30 13:32:20 2014 - [info] Retrieved Gtid Set: a861ddd1-8f4e-11e4-aeac-86f5fb218bef:1-15
Tue Dec 30 13:32:20 2014 - [info] Latest slaves (Slaves that received relay log files to the latest):
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.110(10.0.128.110:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 13:32:20 2014 - [info]     GTID ON
Tue Dec 30 13:32:20 2014 - [info]     Replicating from 10.0.128.77(10.0.128.77:3306)
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.113(10.0.128.113:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 13:32:20 2014 - [info]     GTID ON
Tue Dec 30 13:32:20 2014 - [info]     Replicating from 10.0.128.77(10.0.128.77:3306)
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.114(10.0.128.114:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 13:32:20 2014 - [info]     GTID ON
Tue Dec 30 13:32:20 2014 - [info]     Replicating from 10.0.128.77(10.0.128.77:3306)
Tue Dec 30 13:32:20 2014 - [info] The oldest binary log file/position on all slaves is master-bin.000001:3679
Tue Dec 30 13:32:20 2014 - [info] Retrieved Gtid Set: a861ddd1-8f4e-11e4-aeac-86f5fb218bef:1-15
Tue Dec 30 13:32:20 2014 - [info] Oldest slaves:
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.110(10.0.128.110:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 13:32:20 2014 - [info]     GTID ON
Tue Dec 30 13:32:20 2014 - [info]     Replicating from 10.0.128.77(10.0.128.77:3306)
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.113(10.0.128.113:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 13:32:20 2014 - [info]     GTID ON
Tue Dec 30 13:32:20 2014 - [info]     Replicating from 10.0.128.77(10.0.128.77:3306)
Tue Dec 30 13:32:20 2014 - [info]   10.0.128.114(10.0.128.114:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 13:32:20 2014 - [info]     GTID ON
Tue Dec 30 13:32:20 2014 - [info]     Replicating from 10.0.128.77(10.0.128.77:3306)
Tue Dec 30 13:32:20 2014 - [info] 
Tue Dec 30 13:32:20 2014 - [info] * Phase 3.3: Determining New Master Phase..
Tue Dec 30 13:32:20 2014 - [info] 
Tue Dec 30 13:32:20 2014 - [info] Searching new master from slaves..
Tue Dec 30 13:32:20 2014 - [info]  Candidate masters from the configuration file:
Tue Dec 30 13:32:20 2014 - [info]  Non-candidate masters:
Tue Dec 30 13:32:20 2014 - [info] New master is 10.0.128.110(10.0.128.110:3306)
Tue Dec 30 13:32:20 2014 - [info] Starting master failover..
Tue Dec 30 13:32:20 2014 - [info] 
From:
10.0.128.77(10.0.128.77:3306) (current master)
 +--10.0.128.110(10.0.128.110:3306)
 +--10.0.128.113(10.0.128.113:3306)
 +--10.0.128.114(10.0.128.114:3306)

To:
10.0.128.110(10.0.128.110:3306) (new master)
 +--10.0.128.113(10.0.128.113:3306)
 +--10.0.128.114(10.0.128.114:3306)
Tue Dec 30 13:32:20 2014 - [info] 
Tue Dec 30 13:32:20 2014 - [info] * Phase 3.3: New Master Recovery Phase..
Tue Dec 30 13:32:20 2014 - [info] 
Tue Dec 30 13:32:20 2014 - [info]  Waiting all logs to be applied.. 
Tue Dec 30 13:32:20 2014 - [info]   done.
Tue Dec 30 13:32:20 2014 - [info] Getting new master's binlog name and position..
Tue Dec 30 13:32:20 2014 - [info]  mysql-bin.000001:4142
Tue Dec 30 13:32:20 2014 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.128.110', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx';
Tue Dec 30 13:32:20 2014 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000001, 4142, a861ddd1-8f4e-11e4-aeac-86f5fb218bef:1-15
Tue Dec 30 13:32:20 2014 - [info] Executing master IP activate script:
Tue Dec 30 13:32:20 2014 - [info]   /usr/local/dbadmin/mhascripts/master_ip_failover_pajk01.pl --command=start --ssh_user=root --orig_master_host=10.0.128.77 --orig_master_ip=10.0.128.77 --orig_master_port=3306 --new_master_host=10.0.128.110 --new_master_ip=10.0.128.110 --new_master_port=3306 --new_master_user='dbadmin' --new_master_password='NV7yVBpn88cg4WJCVlZd'  
Set read_only=0 on the new master.
Enabling the VIP - 10.0.128.35/24 on the new master - 10.0.128.110 
Tue Dec 30 13:32:23 2014 - [info]  OK.
Tue Dec 30 13:32:23 2014 - [info] ** Finished master recovery successfully.
Tue Dec 30 13:32:23 2014 - [info] * Phase 3: Master Recovery Phase completed.
Tue Dec 30 13:32:23 2014 - [info] 
Tue Dec 30 13:32:23 2014 - [info] * Phase 4: Slaves Recovery Phase..
Tue Dec 30 13:32:23 2014 - [info] 
Tue Dec 30 13:32:23 2014 - [info] 
Tue Dec 30 13:32:23 2014 - [info] * Phase 4.1: Starting Slaves in parallel..
Tue Dec 30 13:32:23 2014 - [info] 
Tue Dec 30 13:32:23 2014 - [info] -- Slave recovery on host 10.0.128.113(10.0.128.113:3306) started, pid: 31607. Check tmp log /var/log/masterha/test/10.0.128.113_3306_20141230133220.log if it takes time..
Tue Dec 30 13:32:23 2014 - [info] -- Slave recovery on host 10.0.128.114(10.0.128.114:3306) started, pid: 31608. Check tmp log /var/log/masterha/test/10.0.128.114_3306_20141230133220.log if it takes time..
Tue Dec 30 13:32:24 2014 - [info] 
Tue Dec 30 13:32:24 2014 - [info] Log messages from 10.0.128.113 ...
Tue Dec 30 13:32:24 2014 - [info] 
Tue Dec 30 13:32:23 2014 - [info]  Resetting slave 10.0.128.113(10.0.128.113:3306) and starting replication from the new master 10.0.128.110(10.0.128.110:3306)..
Tue Dec 30 13:32:24 2014 - [info]  Executed CHANGE MASTER.
Tue Dec 30 13:32:24 2014 - [info]  Slave started.
Tue Dec 30 13:32:24 2014 - [info]  gtid_wait(a861ddd1-8f4e-11e4-aeac-86f5fb218bef:1-15) completed on 10.0.128.113(10.0.128.113:3306). Executed 0 events.
Tue Dec 30 13:32:24 2014 - [info] End of log messages from 10.0.128.113.
Tue Dec 30 13:32:24 2014 - [info] -- Slave on host 10.0.128.113(10.0.128.113:3306) started.
Tue Dec 30 13:32:24 2014 - [info] 
Tue Dec 30 13:32:24 2014 - [info] Log messages from 10.0.128.114 ...
Tue Dec 30 13:32:24 2014 - [info] 
Tue Dec 30 13:32:23 2014 - [info]  Resetting slave 10.0.128.114(10.0.128.114:3306) and starting replication from the new master 10.0.128.110(10.0.128.110:3306)..
Tue Dec 30 13:32:24 2014 - [info]  Executed CHANGE MASTER.
Tue Dec 30 13:32:24 2014 - [info]  Slave started.
Tue Dec 30 13:32:24 2014 - [info]  gtid_wait(a861ddd1-8f4e-11e4-aeac-86f5fb218bef:1-15) completed on 10.0.128.114(10.0.128.114:3306). Executed 0 events.
Tue Dec 30 13:32:24 2014 - [info] End of log messages from 10.0.128.114.
Tue Dec 30 13:32:24 2014 - [info] -- Slave on host 10.0.128.114(10.0.128.114:3306) started.
Tue Dec 30 13:32:24 2014 - [info] All new slave servers recovered successfully.
Tue Dec 30 13:32:24 2014 - [info] 
Tue Dec 30 13:32:24 2014 - [info] * Phase 5: New master cleanup phase..
Tue Dec 30 13:32:24 2014 - [info] 
Tue Dec 30 13:32:24 2014 - [info] Resetting slave info on the new master..
Tue Dec 30 13:32:24 2014 - [info]  10.0.128.110: Resetting slave info succeeded.
Tue Dec 30 13:32:24 2014 - [info] Master failover to 10.0.128.110(10.0.128.110:3306) completed successfully.
Tue Dec 30 13:32:24 2014 - [info] 

----- Failover Report -----

test: MySQL Master failover 10.0.128.77(10.0.128.77:3306) to 10.0.128.110(10.0.128.110:3306) succeeded

Master 10.0.128.77(10.0.128.77:3306) is down!

Check MHA Manager logs at a3-relay00.sh for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 10.0.128.77(10.0.128.77:3306)
Selected 10.0.128.110(10.0.128.110:3306) as a new master.
10.0.128.110(10.0.128.110:3306): OK: Applying all logs succeeded.
10.0.128.110(10.0.128.110:3306): OK: Activated master IP address.
10.0.128.113(10.0.128.113:3306): OK: Slave started, replicating from 10.0.128.110(10.0.128.110:3306)
10.0.128.114(10.0.128.114:3306): OK: Slave started, replicating from 10.0.128.110(10.0.128.110:3306)
10.0.128.110(10.0.128.110:3306): Resetting slave info succeeded.
Master failover to 10.0.128.110(10.0.128.110:3306) completed successfully.

failover to new master 10.0.128.110:

create new database liuyang3 on 10.0.128.110:


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| liuyang2           |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> create database liuyang3;
Query OK, 1 row affected (0.00 sec)



show all slaves status :

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.128.110
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4296
               Relay_Log_File: hadoop-vm-datanode3-relay-bin.000002
                Relay_Log_Pos: 562
        Relay_Master_Log_File: mysql-bin.000001
             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: 4296
              Relay_Log_Space: 780
              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: 0
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: 2
                  Master_UUID: 909ee6b1-8f51-11e4-aebf-00163f00801f
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 909ee6b1-8f51-11e4-aebf-00163f00801f:1
            Executed_Gtid_Set: 909ee6b1-8f51-11e4-aebf-00163f00801f:1,
a861ddd1-8f4e-11e4-aeac-86f5fb218bef:1-15
                Auto_Position: 1
1 row in set (0.00 sec)

start from new GTID — This GTID is new and we can also see old master GTID


#cat /data/mysql/data/auto.cnf 
[auto]
server-uuid=909ee6b1-8f51-11e4-aebf-00163f00801f

this GTID was gathered when slave has been started.

start old master 10.0.128.77 again and add it to the cluster.


Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CHANGE MASTER TO MASTER_HOST='10.0.128.110', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='rep';
Query OK, 0 rows affected, 2 warnings (0.32 sec)

mysql> start slave ;
Query OK, 0 rows affected, 1 warning (0.06 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| liuyang2           |
| liuyang3           |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

database liuyang3 appeared.

———————————————————————–

initialize cluster:

drop all database;


master: 10.0.128.110
slave: 10.0.128.77/114/113


load data into master. we keep one specific slave has lag and set “sync_binlog=1” on all slaves.


mysql>  delimiter $$
mysql> 
mysql>  CREATE PROCEDURE myprocedure()
    ->  BEGIN
    ->          DECLARE i INT DEFAULT 1;
    -> 
    ->          CREATE  TABLE test
    ->              (ascii_code int, ascii_char CHAR(1));
    -> 
    ->          WHILE (i<=10000) DO
    ->                 INSERT INTO test VALUES(i,CHAR(i));
    ->                 SET i=i+1;
    ->          END WHILE;
    -> 
    ->  END$$
Query OK, 0 rows affected (0.03 sec)

mysql>  delimiter ;
mysql> 
mysql>  call myprocedure();
Query OK, 1 row affected, 1 warning (5 min 53.61 sec)

mysql> select count(*) from liuyang1.test;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.01 sec)

10.0.128.113/114 are both consistent with master

mysql> select count(*) from liuyang1.test;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.01 sec)


           Retrieved_Gtid_Set: 4f1dfbe6-8ffc-11e4-b318-00163f00801f:1-10003
            Executed_Gtid_Set: 4f1dfbe6-8ffc-11e4-b318-00163f00801f:1-10003
                Auto_Position: 1

10.0.128.77 has a huge lag:

           Retrieved_Gtid_Set: 4f1dfbe6-8ffc-11e4-b318-00163f00801f:1-2548
            Executed_Gtid_Set: 4f1dfbe6-8ffc-11e4-b318-00163f00801f:1-1979
                Auto_Position: 1
                
  

now kill master server:

oldest slave will change master to 10.0.128.113:


Tue Dec 30 16:36:15 2014 - [info] Retrieved Gtid Set: 4f1dfbe6-8ffc-11e4-b318-00163f00801f:1-10003
Tue Dec 30 16:36:15 2014 - [info] Latest slaves (Slaves that received relay log files to the latest):
Tue Dec 30 16:36:15 2014 - [info]   10.0.128.113(10.0.128.113:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 16:36:15 2014 - [info]     GTID ON
Tue Dec 30 16:36:15 2014 - [info]     Replicating from 10.0.128.110(10.0.128.110:3306)
Tue Dec 30 16:36:15 2014 - [info]   10.0.128.114(10.0.128.114:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 16:36:15 2014 - [info]     GTID ON
Tue Dec 30 16:36:15 2014 - [info]     Replicating from 10.0.128.110(10.0.128.110:3306)
Tue Dec 30 16:36:15 2014 - [info] The oldest binary log file/position on all slaves is mysql-bin.000001:885010
Tue Dec 30 16:36:15 2014 - [info] Retrieved Gtid Set: 4f1dfbe6-8ffc-11e4-b318-00163f00801f:1-2864
Tue Dec 30 16:36:15 2014 - [info] Oldest slaves:
Tue Dec 30 16:36:15 2014 - [info]   10.0.128.77(10.0.128.77:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
Tue Dec 30 16:36:15 2014 - [info]     GTID ON
Tue Dec 30 16:36:15 2014 - [info]     Replicating from 10.0.128.110(10.0.128.110:3306)
Tue Dec 30 16:36:15 2014 - [info] 
Tue Dec 30 16:36:15 2014 - [info] * Phase 3.3: Determining New Master Phase..
Tue Dec 30 16:36:15 2014 - [info] 
Tue Dec 30 16:36:15 2014 - [info] Searching new master from slaves..
Tue Dec 30 16:36:15 2014 - [info]  Candidate masters from the configuration file:
Tue Dec 30 16:36:15 2014 - [info]  Non-candidate masters:
Tue Dec 30 16:36:15 2014 - [info] New master is 10.0.128.113(10.0.128.113:3306)

and MHA will control 10.0.128.77 to do this command:


SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('4f1dfbe6-8ffc-11e4-b318-00163f00801f:1-10003',0) AS Result 

</per>

show slave status\G;


<pre class="brush: text; gutter: false; first-line: 1; highlight: []; html-script: false">
shows:

           Retrieved_Gtid_Set: 4f1dfbe6-8ffc-11e4-b318-00163f00801f:2786-5067
            Executed_Gtid_Set: 4f1dfbe6-8ffc-11e4-b318-00163f00801f:1-3929
                Auto_Position: 1


this means oldest slave 10.0.128.77 changes to 10.0.128.113 for the new master . Star from transaction id 2785:

Retrieved_Gtid_Set: 4f1dfbe6-8ffc-11e4-b318-00163f00801f:1-2785 in old master
Retrieved_Gtid_Set: 4f1dfbe6-8ffc-11e4-b318-00163f00801f:2786-5067 in current master

wait a long time ……

           Retrieved_Gtid_Set: 4f1dfbe6-8ffc-11e4-b318-00163f00801f:2786-10003
            Executed_Gtid_Set: 4f1dfbe6-8ffc-11e4-b318-00163f00801f:1-10003
                Auto_Position: 1
                
                
mysql> select count(*) from  liuyang1.test;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+

oldest slave 10.0.128.77 was OK !

—————————-

TEST more details:

We test two cases:

1. just 10.0.128.77 has lag — make 10.0.128.77 as candidate master

2. all slaves have lag — make 10.0.128.114 as candidate master

initialize environment

all slave should get new GTID:

master: 10.0.128.113

slave: 10.0.128.110/77/114

make all slaves have lag manually .

First rerun procedure to initialize data


mysql>  call myprocedure();
Query OK, 1 row affected, 1 warning (1 min 9.98 sec)

kill master MHA will choose a new candidate master and do slave recovery:

10.0.128.110/114 position:

           Retrieved_Gtid_Set: f776aa4d-908d-11e4-b6ce-00163f008023:1-10006
            Executed_Gtid_Set: f776aa4d-908d-11e4-b6ce-00163f008023:1-10006
                Auto_Position: 1
                

10.0.128.77 position:

           Retrieved_Gtid_Set: f776aa4d-908d-11e4-b6ce-00163f008023:1-1269
            Executed_Gtid_Set: f776aa4d-908d-11e4-b6ce-00163f008023:1-684
                Auto_Position: 1
  
 
  +--10.0.128.114(10.0.128.114:3306)
Wed Dec 31 10:09:50 2014 - [info] 
Wed Dec 31 10:09:50 2014 - [info] * Phase 3.3: New Master Recovery Phase..
Wed Dec 31 10:09:50 2014 - [info] 
Wed Dec 31 10:09:50 2014 - [info]  Waiting all logs to be applied.. 
Wed Dec 31 10:10:26 2014 - [info]   done.
Wed Dec 31 10:11:28 2014 - [info]  Replicating from the latest slave 10.0.128.110(10.0.128.110:3306) and waiting to apply..
Wed Dec 31 10:11:28 2014 - [info]  Waiting all logs to be applied on the latest slave.. 
Wed Dec 31 10:11:28 2014 - [info]  Resetting slave 10.0.128.77(10.0.128.77:3306) and starting replication from the new master 10.0.128.110(10.0.128.110:3306)..
Wed Dec 31 10:11:28 2014 - [info]  Executed CHANGE MASTER.
Wed Dec 31 10:11:28 2014 - [info]  Slave started.
Wed Dec 31 10:11:28 2014 - [info]  Waiting to execute all relay logs on 10.0.128.77(10.0.128.77:3306)..
 
 
 
 mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: System lock
                  Master_Host: 10.0.128.110
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 487380
               Relay_Log_File: a3-oracle-128-77-relay-bin.000002
                Relay_Log_Pos: 23897
        Relay_Master_Log_File: mysql-bin.000001
             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: 458828
              Relay_Log_Space: 52664
              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: 333
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: 2
                  Master_UUID: 354a7e91-908e-11e4-b6d0-00163f00801f
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: f776aa4d-908d-11e4-b6ce-00163f008023:1539-1722
            Executed_Gtid_Set: f776aa4d-908d-11e4-b6ce-00163f008023:1-1622
                Auto_Position: 1
 
 

MHA will control 77 to change master to 10.0.128.110 (prior is 10.0.128.113)

so 77 start from new transaction (old is 1-1538, new is 1539-1722)

we can wait until reach 10006 on lastest

 
 dbadmin     | 10.0.128.25:17331 | NULL | Query   |   60 | Waiting for the slave SQL thread to advance position | SELECT MASTER_POS_WAIT('mysql-bin.000001','2831783',0) AS Result 

in 10.0.128.110 show master status:


| mysql-bin.000001 |  2831783 |              |                  | f776aa4d-908d-11e4-b6ce-00163f008023:1-10006 |

until 77 reached transaction 10006 ,MHA will continue:


Wed Dec 31 10:36:14 2014 - [info]  master_pos_wait(mysql-bin.000001:2831783) completed on 10.0.128.77(10.0.128.77:3306). Executed 4229 events.
Wed Dec 31 10:36:14 2014 - [info]   done.
Wed Dec 31 10:36:14 2014 - [info]   done.
Wed Dec 31 10:36:14 2014 - [info] Getting new master's binlog name and position..
Wed Dec 31 10:36:14 2014 - [info]  master-bin.000001:2831783
Wed Dec 31 10:36:14 2014 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.128.77', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx';
Wed Dec 31 10:36:14 2014 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: master-bin.000001, 2831783, f776aa4d-908d-11e4-b6ce-00163f008023:1-10006
Wed Dec 31 10:36:14 2014 - [info] Executing master IP activate script:
Wed Dec 31 10:36:14 2014 - [info]   /usr/local/dbadmin/mhascripts/master_ip_failover_pajk01.pl --command=start --ssh_user=root --orig_master_host=10.0.128.113 --orig_master_ip=10.0.128.113 --orig_master_port=3306 --new_master_host=10.0.128.77 --new_master_ip=10.0.128.77 --new_master_port=3306 --new_master_user='dbadmin' --new_master_password='NV7yVBpn88cg4WJCVlZd'  
Set read_only=0 on the new master.
Enabling the VIP - 10.0.128.35/24 on the new master - 10.0.128.77 
sysfs read broadcast value: No such file or directory
falling back to default broadcast value
Wed Dec 31 10:36:17 2014 - [info]  OK.
Wed Dec 31 10:36:17 2014 - [info] ** Finished master recovery successfully.
Wed Dec 31 10:36:17 2014 - [info] * Phase 3: Master Recovery Phase completed.
Wed Dec 31 10:36:17 2014 - [info] 
Wed Dec 31 10:36:17 2014 - [info] * Phase 4: Slaves Recovery Phase..
Wed Dec 31 10:36:17 2014 - [info] 
Wed Dec 31 10:36:17 2014 - [info] 
Wed Dec 31 10:36:17 2014 - [info] * Phase 4.1: Starting Slaves in parallel..
Wed Dec 31 10:36:17 2014 - [info] 
Wed Dec 31 10:36:17 2014 - [info] -- Slave recovery on host 10.0.128.110(10.0.128.110:3306) started, pid: 13177. Check tmp log /var/log/masterha/test/10.0.128.110_3306_20141231100948.log if it takes time..
Wed Dec 31 10:36:17 2014 - [info] -- Slave recovery on host 10.0.128.114(10.0.128.114:3306) started, pid: 13178. Check tmp log /var/log/masterha/test/10.0.128.114_3306_20141231100948.log if it takes time..
Wed Dec 31 10:36:18 2014 - [info] 
Wed Dec 31 10:36:18 2014 - [info] Log messages from 10.0.128.114 ...
Wed Dec 31 10:36:18 2014 - [info] 
Wed Dec 31 10:36:17 2014 - [info]  Resetting slave 10.0.128.114(10.0.128.114:3306) and starting replication from the new master 10.0.128.77(10.0.128.77:3306)..
Wed Dec 31 10:36:17 2014 - [info]  Executed CHANGE MASTER.
Wed Dec 31 10:36:18 2014 - [info]  Slave started.
Wed Dec 31 10:36:18 2014 - [info]  gtid_wait(f776aa4d-908d-11e4-b6ce-00163f008023:1-10006) completed on 10.0.128.114(10.0.128.114:3306). Executed 0 events.
Wed Dec 31 10:36:18 2014 - [info] End of log messages from 10.0.128.114.
Wed Dec 31 10:36:18 2014 - [info] -- Slave on host 10.0.128.114(10.0.128.114:3306) started.
Wed Dec 31 10:36:18 2014 - [info] 
Wed Dec 31 10:36:18 2014 - [info] Log messages from 10.0.128.110 ...
Wed Dec 31 10:36:18 2014 - [info] 
Wed Dec 31 10:36:17 2014 - [info]  Resetting slave 10.0.128.110(10.0.128.110:3306) and starting replication from the new master 10.0.128.77(10.0.128.77:3306)..
Wed Dec 31 10:36:18 2014 - [info]  Executed CHANGE MASTER.
Wed Dec 31 10:36:18 2014 - [info]  Slave started.
Wed Dec 31 10:36:18 2014 - [info]  gtid_wait(f776aa4d-908d-11e4-b6ce-00163f008023:1-10006) completed on 10.0.128.110(10.0.128.110:3306). Executed 0 events.
Wed Dec 31 10:36:18 2014 - [info] End of log messages from 10.0.128.110.
Wed Dec 31 10:36:18 2014 - [info] -- Slave on host 10.0.128.110(10.0.128.110:3306) started.
Wed Dec 31 10:36:18 2014 - [info] All new slave servers recovered successfully.
Wed Dec 31 10:36:18 2014 - [info] 
Wed Dec 31 10:36:18 2014 - [info] * Phase 5: New master cleanup phase..
Wed Dec 31 10:36:18 2014 - [info] 
Wed Dec 31 10:36:18 2014 - [info] Resetting slave info on the new master..
Wed Dec 31 10:36:18 2014 - [info]  10.0.128.77: Resetting slave info succeeded.
Wed Dec 31 10:36:18 2014 - [info] Master failover to 10.0.128.77(10.0.128.77:3306) completed successfully.
Wed Dec 31 10:36:18 2014 - [info] 

try to recover other slaves but other slave are both newest. So change master to 77 directly.

——————————————————————–

2. all slaves have lag and 114 is candidate master.

slaves will do not merge master’s binlog (I don’t know why)

initialize cluster again:

master : 10.0.128.113

slave : 10.0.128.114(candidate master)/77/110

drop table test ;

Run procedure again:

we got final result .

mysql> select count(*) from liuyang1.test;
+----------+
| count(*) |
+----------+
|     5852 |
+----------+

we lost 4148 records.

Wed Dec 31 10:50:33 2014 - [info] * Phase 3.3: New Master Recovery Phase..
Wed Dec 31 10:50:33 2014 - [info] 
Wed Dec 31 10:50:33 2014 - [info]  Waiting all logs to be applied.. 
Wed Dec 31 10:50:33 2014 - [info]   done.
Wed Dec 31 10:51:35 2014 - [info] Getting new master's binlog name and position..
Wed Dec 31 10:51:35 2014 - [info]  mysql-bin.000001:4488549
Wed Dec 31 10:51:35 2014 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.128.114', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx';
Wed Dec 31 10:51:35 2014 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000001, 4488549, 5d04dd5c-908e-11e4-b6d1-86f5fb218bef:1,
f776aa4d-908d-11e4-b6ce-00163f008023:1-15861
Wed Dec 31 10:51:35 2014 - [info] Executing master IP activate script:
Wed Dec 31 10:51:35 2014 - [info]   /usr/local/dbadmin/mhascripts/master_ip_failover_pajk01.pl --command=start --ssh_user=root --orig_master_host=10.0.128.113 --orig_master_ip=10.0.128.113 --orig_master_port=3306 --new_master_host=10.0.128.114 --new_master_ip=10.0.128.114 --new_master_port=3306 --new_master_user='dbadmin' --new_master_password='NV7yVBpn88cg4WJCVlZd'  
Set read_only=0 on the new master.
Enabling the VIP - 10.0.128.35/24 on the new master - 10.0.128.114 
Wed Dec 31 10:51:38 2014 - [info]  OK.
Wed Dec 31 10:51:38 2014 - [info] ** Finished master recovery successfully.
Wed Dec 31 10:51:38 2014 - [info] * Phase 3: Master Recovery Phase completed.
Wed Dec 31 10:51:38 2014 - [info] 
Wed Dec 31 10:51:38 2014 - [info] * Phase 4: Slaves Recovery Phase..
Wed Dec 31 10:51:38 2014 - [info] 
Wed Dec 31 10:51:38 2014 - [info] 
Wed Dec 31 10:51:38 2014 - [info] * Phase 4.1: Starting Slaves in parallel..
Wed Dec 31 10:51:38 2014 - [info] 
Wed Dec 31 10:51:38 2014 - [info] -- Slave recovery on host 10.0.128.77(10.0.128.77:3306) started, pid: 13508. Check tmp log /var/log/masterha/test/10.0.128.77_3306_20141231105020.log if it takes time..
Wed Dec 31 10:51:38 2014 - [info] -- Slave recovery on host 10.0.128.110(10.0.128.110:3306) started, pid: 13509. Check tmp log /var/log/masterha/test/10.0.128.110_3306_20141231105020.log if it takes time..
Wed Dec 31 11:01:34 2014 - [info] 
Wed Dec 31 11:01:34 2014 - [info] Log messages from 10.0.128.77 ...
Wed Dec 31 11:01:34 2014 - [info] 
Wed Dec 31 10:51:38 2014 - [info]  Resetting slave 10.0.128.77(10.0.128.77:3306) and starting replication from the new master 10.0.128.114(10.0.128.114:3306)..
Wed Dec 31 10:52:41 2014 - [info]  Executed CHANGE MASTER.
Wed Dec 31 10:52:41 2014 - [info]  Slave started.
Wed Dec 31 11:01:34 2014 - [info]  gtid_wait(5d04dd5c-908e-11e4-b6d1-86f5fb218bef:1,
f776aa4d-908d-11e4-b6ce-00163f008023:1-15861) completed on 10.0.128.77(10.0.128.77:3306). Executed 855 events.
Wed Dec 31 11:01:34 2014 - [info] End of log messages from 10.0.128.77.
Wed Dec 31 11:01:34 2014 - [info] -- Slave on host 10.0.128.77(10.0.128.77:3306) started.
Wed Dec 31 11:02:03 2014 - [info] 
Wed Dec 31 11:02:03 2014 - [info] Log messages from 10.0.128.110 ...
Wed Dec 31 11:02:03 2014 - [info] 
Wed Dec 31 10:51:38 2014 - [info]  Resetting slave 10.0.128.110(10.0.128.110:3306) and starting replication from the new master 10.0.128.114(10.0.128.114:3306)..
Wed Dec 31 10:52:41 2014 - [info]  Executed CHANGE MASTER.
Wed Dec 31 10:52:41 2014 - [info]  Slave started.
Wed Dec 31 11:02:03 2014 - [info]  gtid_wait(5d04dd5c-908e-11e4-b6d1-86f5fb218bef:1,
f776aa4d-908d-11e4-b6ce-00163f008023:1-15861) completed on 10.0.128.110(10.0.128.110:3306). Executed 142 events.
Wed Dec 31 11:02:03 2014 - [info] End of log messages from 10.0.128.110.
Wed Dec 31 11:02:03 2014 - [info] -- Slave on host 10.0.128.110(10.0.128.110:3306) started.
Wed Dec 31 11:02:03 2014 - [info] All new slave servers recovered successfully.
Wed Dec 31 11:02:03 2014 - [info] 
Wed Dec 31 11:02:03 2014 - [info] * Phase 5: New master cleanup phase..
Wed Dec 31 11:02:03 2014 - [info] 
Wed Dec 31 11:02:03 2014 - [info] Resetting slave info on the new master..
Wed Dec 31 11:02:03 2014 - [info]  10.0.128.114: Resetting slave info succeeded.
Wed Dec 31 11:02:03 2014 - [info] Master failover to 10.0.128.114(10.0.128.114:3306) completed successfully.
Wed Dec 31 11:02:03 2014 - [info] 

10.0.128.114 do not merge newest binlog from 10.0.128.113 (although it can ssh to 113 server )

————————————————————————————-

add binlog server and make 77 server as candidate master

[binlog1]
hostname=10.0.128.252

10.0.128.252 is manager node machine. we set binlog server in this machine.

#ls -l /data/mysql/data/
total 2896
-rw-rw-r– 1 mysql mysql 2960898 Dec 31 13:57 mysql-bin.000001

drop test table

run procedure again

we check lastest slave

10.0.128.114 :

           Retrieved_Gtid_Set: 1cc021e4-90b0-11e4-b7ad-00163f008023:1-3357
            Executed_Gtid_Set: 1cc021e4-90b0-11e4-b7ad-00163f008023:1-3356
  

10.0.128.77:

           Retrieved_Gtid_Set: 1cc021e4-90b0-11e4-b7ad-00163f008023:1-102
            Executed_Gtid_Set: 1cc021e4-90b0-11e4-b7ad-00163f008023:1-48
            

10.0.128.110:

           Retrieved_Gtid_Set: 1cc021e4-90b0-11e4-b7ad-00163f008023:1-344
            Executed_Gtid_Set: 1cc021e4-90b0-11e4-b7ad-00163f008023:1-270           

and all slaves have lag : 10.0.128.77>10.0.128.110>10.0.128.114 (lastest slave)

now we kill master server:

 Waiting to execute all relay logs on 10.0.128.77(10.0.128.77:3306)..
Wed Dec 31 13:59:28 2014 - [info]  master_pos_wait(mysql-bin.000001:1219763) completed on 10.0.128.77(10.0.128.77:3306). Executed 37 events.
Wed Dec 31 13:59:28 2014 - [info]   done.
Wed Dec 31 13:59:28 2014 - [info]   done.
Wed Dec 31 13:59:28 2014 - [info] -- Saving binlog from host 10.0.128.252 started, pid: 15893
Wed Dec 31 13:59:29 2014 - [info] 
Wed Dec 31 13:59:29 2014 - [info] Log messages from 10.0.128.252 ...
Wed Dec 31 13:59:29 2014 - [info] 
Wed Dec 31 13:59:28 2014 - [info] Fetching binary logs from binlog server 10.0.128.252..
Wed Dec 31 13:59:28 2014 - [info] Executing binlog save command: save_binary_logs --command=save --start_file=mysql-bin.000001  --start_pos=1275770 --output_file=/var/log/masterha/test/saved_binlog_binlog1_20141231135735.binlog --handle_raw_binlog=0 --skip_filter=1 --disable_log_bin=0 --manager_version=0.56 --oldest_version=5.6.17-log  --binlog_dir=/data/mysql/data 
  Creating /var/log/masterha/test if not exists..    ok.
 Concat binary/relay logs from mysql-bin.000001 pos 1275770 to mysql-bin.000001 EOF into /var/log/masterha/test/saved_binlog_binlog1_20141231135735.binlog ..
 Concat succeeded.
Wed Dec 31 13:59:29 2014 - [info] scp from root@10.0.128.252:/var/log/masterha/test/saved_binlog_binlog1_20141231135735.binlog to local:/var/log/masterha/test/saved_binlog_10.0.128.252_binlog1_20141231135735.binlog succeeded.
Wed Dec 31 13:59:29 2014 - [info] End of log messages from 10.0.128.252.
Wed Dec 31 13:59:29 2014 - [info] Saved mysqlbinlog size from 10.0.128.252 is 4885833 bytes.
Wed Dec 31 13:59:29 2014 - [info] Applying differential binlog /var/log/masterha/test/saved_binlog_10.0.128.252_binlog1_20141231135735.binlog ..
Wed Dec 31 13:59:39 2014 - [info] Differential log apply from binlog server succeeded.
Wed Dec 31 13:59:39 2014 - [info] Getting new master's binlog name and position..
Wed Dec 31 13:59:39 2014 - [info]  master-bin.000001:3485577

MHA will scp binlog server binlog and Concat binlog, all slaves are consistent in the end. (same with old behavior)

check all slaves:

           Retrieved_Gtid_Set: 1cc021e4-90b0-11e4-b7ad-00163f008023:4311-10003
            Executed_Gtid_Set: 1cc021e4-90b0-11e4-b7ad-00163f008023:1-10003
                Auto_Position: 1




mysql> select count(*) from liuyang1.test;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+

no data lost. and every slaves got final transaction 10003.

—————————————————————-

add an other binlog server (master server)

[binlog1]
hostname=10.0.128.252

[binlog2]
hostname=10.0.128.113

add 113 to cluster:

master : 10.0.128.113

slave : 10.0.128.77/110/114

run procedure again:


mysql> delimiter ;

mysql> call myprocedure();

10.0.128.77:


           Retrieved_Gtid_Set: 1cc021e4-90b0-11e4-b7ad-00163f008023:10004-10327
            Executed_Gtid_Set: 1cc021e4-90b0-11e4-b7ad-00163f008023:1-10151,
cfd90b74-90af-11e4-b7ab-86f5fb218bef:1
                Auto_Position: 1
   

10.0.128.110:

           Retrieved_Gtid_Set: 1cc021e4-90b0-11e4-b7ad-00163f008023:10004-10621
            Executed_Gtid_Set: 1cc021e4-90b0-11e4-b7ad-00163f008023:1-10177,
cfd90b74-90af-11e4-b7ab-86f5fb218bef:1
                Auto_Position: 1

10.0.128.114:

           Retrieved_Gtid_Set: 1cc021e4-90b0-11e4-b7ad-00163f008023:10004-12723
            Executed_Gtid_Set: 1cc021e4-90b0-11e4-b7ad-00163f008023:1-12722,
cfd90b74-90af-11e4-b7ab-86f5fb218bef:1
                Auto_Position: 1
       

now we remove binlog on 10.0.128.252 manually and kill master server :

find these messages:

Wed Dec 31 15:26:16 2014 - [info] Executing binlog save command: save_binary_logs --command=save --start_file=mysql-bin.000003  --start_pos=1243184 --output_file=/var/log/masterha/test/saved_binlog_binlog1_20141231152512.binlog --handle_raw_binlog=0 --skip_filter=1 --disable_log_bin=0 --manager_version=0.56 --oldest_version=5.6.17-log  --binlog_dir=/data/mysql/data 
Failed to save binary log: Binlog not found from /data/mysql/data! If you got this error at MHA Manager, please set "master_binlog_dir=/path/to/binlog_directory_of_the_master" correctly in the MHA Manager's configuration file and try again.
 at /usr/bin/save_binary_logs line 123
	eval {...} called at /usr/bin/save_binary_logs line 70
	main::main() called at /usr/bin/save_binary_logs line 66
Wed Dec 31 15:26:16 2014 - [error][/usr/share/perl5/vendor_perl/MHA/MasterFailover.pm, ln660] Failed to save binary log events from the binlog server. Maybe disks on binary logs are not accessible or binary log itself is corrupt?
Wed Dec 31 15:26:16 2014 - [info] End of log messages from 10.0.128.252.
Wed Dec 31 15:26:16 2014 - [warning] Got error from 10.0.128.252.
Wed Dec 31 15:26:18 2014 - [info] 
Wed Dec 31 15:26:18 2014 - [info] Log messages from 10.0.128.113 ...
Wed Dec 31 15:26:18 2014 - [info] 
Wed Dec 31 15:26:16 2014 - [info] Fetching binary logs from binlog server 10.0.128.113..
Wed Dec 31 15:26:16 2014 - [info] Executing binlog save command: save_binary_logs --command=save --start_file=mysql-bin.000003  --start_pos=1243184 --output_file=/var/log/masterha/test/saved_binlog_binlog2_20141231152512.binlog --handle_raw_binlog=0 --skip_filter=1 --disable_log_bin=0 --manager_version=0.56 --oldest_version=5.6.17-log  --binlog_dir=/data/mysql/data 
  Creating /var/log/masterha/test if not exists..    ok.
  
  

MHA check binlog server 1 and find error with binlog then try to recover from binlog server 2.

With this feature we can add master server as first binlog server to guarantee no binlog loss.If master server crash and can not been reached,binlog server 2 will also provide service.

Final:

 MHA has changed failover procedure when turn on GTID. 
 
 1.If you want to use binlog server you must open GTID (I don't know why Yoshi design like this)
 2.MHA will not use relay-log to reocver (apply_diff_relay_logs will not be used ,just change master to lastest slave to recover with GTID)
 3.if there's no binlog server setting. MHA will ignore master binlog (even Manager node can ssh to Master server)
 4.if you do not set binlog server on MHA, data may lost (MHA just keep all slaves consistent,so if lastest slave has lag data will be lost)
 5.you can set multiple binlog server ,MHA will check them orderly,you can even set Master server as binlog server.


使用q和pt-find了解机器上的库表

当DBA换工作到新环境时,肯定需要大概了解下各台机器有什么库表,如下sql就能打印这台机器上所有非系统自带的库名和表名:

select TABLE_SCHEMA,TABLE_NAME from information_schema.tables where TABLE_SCHEMA not in ('mysql','information_schema','performance_schema');

但有这样一种情况–机器因为分库分表虽然有几十个库和成百上千的表,但大部分库表类型其实是相同的,如下所示:

+--------------+-------------------+
| db_a01     | tab_c01         |  
| db_a02     | tab_c02         |  
............
............
| db_b10    | tab_d09         |  
| db_b10    | tab_d10         |  
+--------------+-------------------+

上诉表格虽然有20个库(db_a01~db_a10db_b01~db_b10)、每个库有20个表(tab_c01~tab_c10tab_d01~tab_d10),一共400张表,直接这么看很眼花,但其实只有两类数据库–db_adb_b,两类表–tab_ctab_d;使用pt-find找出机器上所有库表然后通过q做合并就能简化显示结果:

pt-find -h IP -u USER -p PASSWORD --noquote --dbregex "[^(mysql\. | performance_schema\. | information_schema\.)]" | q -d '.' "select distinct(rtrim(c1,'0|1|2|3|4|5|6|7|8|9') || '.' || rtrim(c2,'0|1|2|3|4|5|6|7|8|9')) from -"

最终400个表简化成4种类型的库.表:

+--------------+-------------------+
| db_a.tab_c          | 
| db_a.tab_d          | 
| db_b.tab_c          | 
| db_b.tab_d          | 
+--------------+-------------------+

因为q基于SQLite,不像MariaDB支持REGEXP_REPLACE()函数用正则匹配数字,否则上述命令能更简洁~

MyAWR : MySQL Workload Report V3 release


myawr-v3 release now !

history of myawr:

————————–
v1: create by noodba

mysql load information and system information 

v2: create by louis liu

myawr_v2 add :

1 more os information module.

2 multiple instance support (extend table by db_port)

3 modified some bugs (mistake value)

4 add different statistics views

v3: create by louis liu

base on myawr_v2

myawr_v3 add sys schema information

(statment statistics/index&table stat/IO latency/file&table IO stat and so on)

including performance schema and information schema

so you need open performance schema on your mysql database

myawr_v3 now support mysql 5.6 GA and will continue support mysql feature version

before install myawr_v3 you need run sys_schema to create formatted views for data collecting.

Overview: Myawr_v3 mysql performance web show

Download:https://github.com/ylouis83/myawr/tree/master

关于Dell 推出第13代服务器的一些想法

戴尔近日推出了旗下的13G服务器,其主力机型为R730xd,包含了诸多的特性,为其成为主流db server以及规模存储集群打下了良好的基础。

具体参考:http://www.storagereview.com/dell_poweredge_13g_r730xd_review
http://www.storagereview.com/dell_poweredge_gen13_servers_released

具体增强为:

1.CPU 为intel haswall最新架构,减少了功能的损耗。
2.更多的插槽,扩展为可支持18块1.8寸SSD的槽位 以及多种磁盘混插的模式。
3.DDR4 memory 拥有更高的主频
4.更加智能的基于iDRAC的装机模式
5.扩展的万兆网卡
6.基于iDRAC8的自动管理功能 包括服务器性能的监控,邮件报警(app端)等等。
7.Sandisk的缓存技术取代之前的LSI的(是否与LSI被希捷收购有关 ?)
8.增强的新一代的RAID卡 更大的内存以及基于RAID卡的直接系统日志收集等(依然采用电池)。
9.NFC技术的运用(自动扫描bios信息等)
10.NVMe协议的支持 (支持 NVMe_SSD 全面拥抱Intel ?)

等等

根据戴尔sales的描述,R730xd为下一代db-server,hadoop server 以及云计算server.在这里针对hadoop server持保留意见,其18块ssd的插槽扩展虽然增加了ssd的整体容量,但对于hadoop这类应用,或者对于目前hadoop的软件架构,SSD是否能发挥其应有的性能,facebook的测试给出了答案。

http://hadoopblog.blogspot.com/2012/05/hadoop-and-solid-state-drives.html

Also, a SSD device can support 100K to 200K operations/sec while a spinning disk controller can possibly 
issue only 200 to 300 ops/sec. This means that random reads/writes are not a bottleneck on SSDs. 
On the other hand, most of our existing database technology is designed to store data in spinning disks, 
so the natural question is "can these databases harness the full potential of the SSDs"?

结合两张图我们来看结论:

HdfsPreadImageCache4G

结论为现在HADOOP/hbase 并不能将SSD的性能优势发挥的玲离尽致 hadoop修改代码后的瓶颈依然存在(JAVA DFSClient),hbase线程锁导致cpu利用率低下,这归根于传统的数据库基于机械硬盘IO的设计,不过这一点在oracle上解决的非常好(oracle 在unix/linux是基于进程的数据库)。

最后如Dhruba Borth所说

@Sujoy: you are absolutely right. In fact, we currently run multiple servers instances per SSD 
just to be able to utilize all the IOPs. This is kindof-a-poor man's solution to the problem. 
Also, you have to have enough CPU power on the server to be able to drive multiple database 
instances on the same machine.

Facebook通过多实例并用server来以最小的成本达到硬件的最大性能,这类似于早期的mysql,mysql的多线程架构并不能在SMP NUMA架构的机器中充分利用CPU的能力,所以衍生出了NUMA多实例,多种绑定CPU的策略。所以在传统的数据库架构下要契合最新的硬件并不是一件很轻松的事。

另外针对线程以及进程(在unix时代对线程支持不是非常好,所以如oracle pg等数据库采用了进程的方式,mysql采用线程在早期对CPU的利用也是十分低下的) 可以暂且认为线程是近代DB的一种趋势(不知道准不准确)因为线程本省对于进程来说是具有一定优势的(内存的共享 以及更小的创建代价,更低的CPU上下文切换代价)

关于Partial write for all MySQL and Oracle

很久之前看过这个帖子,当时没有细究,这几天看了下mysql相关的internal 突然想起这个问题,跟三四个朋友讨论了下 想把这个问题展开讲一讲。

源帖子内容见:https://community.oracle.com/thread/1087650?start=0&tstart=0

这个帖子的内容为一个老外问oracle如何避免partial write的,也就是大家所说的半个IO问题,IO 在写到一半的时候发生断裂,(瞬间断线,瞬间crash瞬间存储或者机器故障等) 虽然这个情况极少发生,但是在非常规恢复的时候也是经常遇到的,例如某个block内部出现不一致,也就是我们所谓的逻辑坏块。
我仔细的又过了一遍帖子,发现下面的几个回答完全不准确,或者有点张冠李戴的意思。

首先我们看看什么是mysql 的double write:

—–引自 mysqlperformace blog

为什么要用doublewrite?
目的是为了保证出现部分写失效(partial page write)–即数据页写到一半时就出现故障–时的数据安全性。Innodb并不在日志中记录整个数据页,而是使用一种称之为“生理”日志的技术,即日志项中只包含页号、对数据进行的操作(如更新一行记录)和日志序列号等信息。这一日志结构的优点是能够减少写入到日志的数据量,但这也要求要保持页内部的一致性。页的版本号是没关系的,数据页可以是当前版本(这时Innodb(故障恢复时)会跳过对页的更新操作)若是早期版本(这时Innodb将执行更新操作)。但如果页内部数据不一致,恢复将不能进行。

部分写失效
什么是部分写失效,为什么会出现这一现象呢?部分写失效指的是提交给操作系统的写数据页操作只完成了一部分。例如一个16K的Innodb数据页中只有4K被更新了,其余部分数据还是旧的。大部分的部分写失效都在断电时产生,在操作系统崩溃时也可能出现,这是由于操作系统可能将一个写16K数据的操作分割成多个写操作(这可能由文件碎片导致),而在执行这多个写操作的过程中发出的故障。当使用软件RAID技术时,数据页可能恰好跨越分片(stripe)的边界,这时也需要执行多个IO操作,因此也可能导致部分写失效。当使用硬件RAID又没有使用带电池的缓存时,断电时出现部分写失效也是可能的。当发送到磁盘本身只有一个写操作时,理论上硬件可以保证写操作即使在断电时也可以完成,因为驱动器内部应该积累有足够的电量来完成这一操作。但实话说我并不知道事实是不是这样,这很难检测,而且这也不是部分写失效的唯一原因。我所知道的只是部分写失效可能会出现,当Innodb实现 doublewrite功能前,我遇到过很多由于这一原因而导致数据被破坏。

doublewrite如何工作?
你可以将doublewrite看作是在Innodb表空间内部分配的一个短期的日志文件,这一日志文件包含100个数据页。Innodb在写出缓冲区中的数据页时采用的是一次写多个页的方式,这样多个页就可以先顺序写入到doublewrite缓冲区并调用fsync()保证这些数据被写出到磁盘,然后数据页才被定出到它们实际的存储位置并再次调用fsync()。故障恢复时Innodb检查doublewrite缓冲区与数据页原存储位置的内容,若数据页在doublewrite缓冲区中处于不一致状态将被简单的丢弃,若在原存储位置中不一致则从doublewrite缓冲区中还原。

doublewrite缓冲区对MySQL有何影响?
虽然doublewrite要求每个数据页都要被写二次,其性能开销远远小于二倍。写出到doublewrite缓冲区时是顺序写,因此开销很小。 doublewrite同时还可以降低Innodb执行的fsync()操作,即不需要写每个页时都调用一下fsync(),而可以提交多个写操作最后再调用一次fsync()操作,这使得操作系统可以优化写操作的执行顺序及并行使用多个存储设备。但在不使用doublewrite技术时也可以用这些优化,事实上这些优化是与doublewrite同时实现的。因此总体来说,我预计使用doublewrite技术带来的性能开销不会超过5%到10%。

能否禁用doublewrite?
如果你不关心数据一致性(比如使用了RAID0)或文件系统可以保证不会出现部分写失效,你可以通过将innodb_doublewrite参数设置为0还禁用doublewrite。但通常这可能带来更大的麻烦。

这里暂且不讨论为何mysql不开启double write会容易出现断裂的写操作. 在mysql中数据写的单元是以page为单位 1page=16KB 而在oracle中是以block为单位 block可以指定大小。但是写入OS的时候都是以OS block为单位,也就是说如果写入OS block时发生partial write 同样会出现逻辑问题。

这里我们看一个老外的回答:

It’s an interesting question. I think the clue is in the link you provided: “Such logging structure is geat as it require less data to be written to the log, however it requires pages to be internally consistent.”

What that’s saying (I think!) is that the contents of the innodb transaction log can only be replayed to datafile pages which are ‘clean’ -and that’s true for Oracle, too. You can’t apply Oracle redo to an Oracle database block that is internally corrupted because some of its consituent “os pages” were written at a different time from others. When such partial writes happen, you get what’s called a “fractured block”, warnings in the alert log …and the data file is regarded as corrupt from that point on.

Oracle’s fix to this potential problem, however, is also hinted at in the article you linked to: “Innodb does not log full pages to the log files”. That’s an interesting sentence because , you see, Oracle does write full pages to the logs! I should immediately qualify that: it only does so when you take a “hot backup” using O/S copy commands -because it’s only then that you have to worry about the problem. In other words, you only have to worry about the fact that you can only apply redo to an internally consistent database block if you’re actually in the business of applying redo… and you’re only doing that in the event of a recovery. And complete recoveries in Oracle (as opposed to mere crash recoveries) require you to have restored something from backup. So, it’s only during the backup process that you only have to worry about the problem of fractured blocks -and so it’s only then that Oracle says, ‘if you have put the tablespace into hot backup mode (alter tablespace X begin backup), then the first time a block of data is changed, the entire block should be written in a consistent state into the redo (transaction) logs. Then, if the datafile copy of the block in the backup turns out to be fractured, we’ve got a known good copy in the redo we can restore in its place. And once you have a clean block as a starting point, you can continue to apply redo from that point on’.

Oracle has an alternative (and more up to date) mechanism for achieving this “I know your data block is clean” starting state, though. It’s called RMAN -the Oracle backup and recovery tool. Unlike your OS copy command, it’s an Oracle utility… so it understands the concept of Oracle blocks, and it can therefore check that a block that’s been copied has been copied consistently, with all its constituent OS ‘pages’ written coherently to disk in the same state. It knows how to compare input and output in a way no OS command could ever hope to do. So when RMAN copies a data block hot, it reads the copy, compares it with the original -and if it sees the copy is fractured, it just has another go copying the block again. Repeat until the copy is indeed verified as a good copy of the original. No need to write the block into the transaction log at all, because you know that the backup file itself contains the necessary clean block copy.

So, putting that into practice. Let’s say your server corrupts data on the disk for whatever reason and, in the process, your Oracle instance dies. You try and restart Oracle, but you get told that recovery is needed (you might get a message that file 16, for example, can’t be read). So you restore file 16 from your hot backup taken with OS commands. In that backup, one of the blocks is fractured, because only part of the Oracle block had hit disk at the point the backup was performed. So you restore a fractured block. But that’s not a problem, because as redo is replayed, you’ll find the clean copy of the block in the redo stream, and restore that over the top of the fractured block. The rest of the redo can then be replayed without a problem. Or, you restore file 16 using RMAN… and what it restores cannot be fractured, because it checks for that before it reports the original backup a success. Therefore, you restore a clean copy of file 16, and can apply redo to it without drama. Either way, you get your database recovered.

So, the article you linked to nails the important matter: “It does not matter which page version it is – it could be “current” version in which case Innodb will skip page upate operation or “former” in which case Innodb will perform update. If page is inconsistent recovery can’t proceed.” Absolutely true of Oracle, too. But Oracle has two alternatives for ensuring that a clean version of the block is always available: write a whole block into redo if it’s changed whilst the database is being backed up with OS commands, or make sure you only write clean blocks into the backup if you’re using RMAN -and you achieve that by multiple reads of the block, as many as are necessary to ensure the output is clean.

Oracle’s solutions in these regards are, I think, a lot more efficient than double-writing every block all the time, because the only time you have to worry that what’s on disk isn’t consistent is, as your linked article again points out, when ‘power failure’ or ‘os crash’ happens. That is, during some sort of failure. And the response to failure that involves corruption is always to restore something from backup… so, it’s really only that backup that needs to worry about ‘clean pages’. Instead of writing everything twice to disk during normal running (which sounds like a potentially enormous overhead to me!), therefore, Oracle only has to employ protective measures during the backup process itself (which should, ordinarily, be a mere fraction of ‘normal running’ time). The overhead is therefore only encountered sparingly and not something you need worry about as a potentially-constant performance problem.

In closing, I’ll second Aman’s observation that it is generally and usually the case that any variation away from the default 8K block size is a bad idea. Not always, and there may be justification for it in extremis… but you will certainly be at risk of encountering more and weirder bugs than if you stick to the defaults.

这一段回答可谓一点儿也没讲到重点 主要回答oracle 采用避免partial write的几个方法,注意 这里是在特殊场景下 如何避免partial write.而不是数据库机理. 我们看一下mysql怎么同样避免这个问题 —> ( oracle hot backup/RMAN backup)
传统Oracle的热备,备份读取与DBWR写文件并行执行,因此可能读取到Fractured Block(不一致的块),解决办法是对于备份的文件,DBWR写的Dirty Page同时写入Redo Log,用于Fractured Block的恢复。RMAN备份,会检查每个读取页面的一致性,不一致就重新读取。Percona的XtraBackup,采用类似于RMAN的方案。
如何检查备份读取的页面是否是一致的,其实方法很简单:无论是Oracle/InnoDB的页面,都会在页面的页头与页尾存储相同的SCN /CheckSum。当页头/页尾的SCN/CheckSum相同,说明页面处于一致状态,否则就说明页面损坏(Fractured Block),备份重新读取损坏页面即可。
所以这一段可以理解为当你需要备份的时候 如何避免partial write 因为在这个时候最容易发生断裂的块或者页。而此前别人提问的是oracle是否有类似double write的手法避免常规的partial write.

我们继续看下面的回答:

user646745 wrote:
Thanks HJR for detailed analysis.

But the double write mechanism works without restore from a backup an apply redo: before write the dirty buffer blocks, innodb flush the blocks in the double write buffer to disk which is a sequential disk area (so it’s fast),
————————-
before write the dirty buffer blocks, logwr flush the blocks in the redo buffer to disk which is a sequential disk area (so it’s fast),

so even if partial write happen, the the blocks in double write buffer already persistent in disk,
————————-
so even if partial write happen, the the blocks in redo buffer already persistent in disk,

and when mysql restart, innodb can compare the blocks flushed from double write buffer with the datafile blocks,
————————-
and when mysql restart, smon can compare control file scn with the datafile blocks scn,

if they matched (means no partial write), then everything is fine, if not matched (partial write happen), just overwrite the datafile blocks with the blocks from double write buffer.
————————-
if they matched (means no partial write), then everything is fine, if not matched (partial write happen), just apply the redo from the redo logs.

So no media recover is required.
————————-
sounds like media recovery to me

Based on your anaysis, oracle needs media recover.
————————-
Based on your analysis, so does mysql. It just applies it in a very slightly different fashion, and calls it all something else.

这里的回答又有误点. 他说道 “so even if partial write happen, the the blocks in double write redo buffer already persistent in disk” 这句话存在明显的误导,首先partial write发生的时候,redo是无法恢复
一个块内不一致的块的,redo只能负责recover作用,但这不是针对块内部的恢复 而是trasaction或者media的.

oracle recovery 分为 instance/crash recovery and media recovery 其本质区别在于instance recovery 需要online redo logfile 即apply 增量检查点之后的redolog. 而media recovery又分为 datafile /block media
recovery 其本质为恢复一个旧的datafile或者block 可能需要用到归档日志。注意这里的前提为需要有备份。restore datafile /block 只能在备份中恢复,而之后的recover是要用到archive log。 这里的media recover可以
对应为解决partial write的方法。

对于一个内部不一致的page mysql无法通过redo去恢复 :redo有效应用的前提是data的一致性,当data flush到磁盘时发生故障,比如16K只写了4K并且redo条目是change vector形式,属于逻辑记录,那么在InnoDB recovery时,redo就不知道从哪开始跑起。

对于oracle来说 内部不一致的block.仅仅有 redo也是不够的.我们可以通过dbf检查到逻辑问题的block,去repair这个block,前提是需要有备份。这里用到的方法是media recovery里的block recovery.
我们可以使用bbed 或者一些OS的工具来模拟这些逻辑坏块,并且往往发现数据库是可以正常open的,只有在访问到这些block的时候才会出现逻辑问题。所以说备份重于一切,oracle在处理这些”半个IO”或者一些静默丢失(storage),在没有备份的情况下也是很乏力的。