Monthly Archives: July 2013

mysql5.6对原16位密码的兼容性测试结果

测试原因:
上周末在搭建71的备库时,准备将备库升级成5.6版本,在升级的过程中发现了mysql 2049 错误( Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option ‘secure_auth’ enabled) when trying to connect)
后面得知是因为原来密码是老密码格式,导致升级失败。因为主库71的repl账号也是老密码格式,所以导致同步报IO错误,错误也为2049.
对于用新格式密码的服务器,升级到5.6都没有任何问题
 
知识背景:
1.mysql在4.1之前版本是采用旧的密码格式(pre-41),长度为16为
2.mysql在4.1以后的版本里为了安全考虑采用了新的密码格式,长度为41位,第一位为*,但是兼容老密码格式
3.mysql在5.6.7里,由于将secure-auth默认设置为打开,所以对老密码不兼容,所有老密码的连接都会被拒绝
4.mysql官方文档里面说,加入skip-secure-auth配置选项可以关闭secure-auth,用来兼容老密码
 
 
官方文档相关参考摘录:
OLD_PASSWORD() was added when the implementation of PASSWORD() was changed in MySQL 4.1 to improve security. OLD_PASSWORD() returns the value of the pre-4.1 implementation of PASSWORD() as a string, and is intended to permit you to reset passwords for any pre-4.1 clients that need to connect to your version 5.6 MySQL server without locking them out.
 
 –secure-auth [257]
Do not send passwords to the server in old (pre-4.1.1) format. This prevents connections except for
servers that use the newer password format. As of MySQL 5.6.7, this option is enabled by default;
use –skip-secure-auth [257] to disable it.
 
测试过程:
 
下面通过安装一个mysql5.5数据库,设置为旧密码,对其进行升级到5.6.12,来测试这一过程
1.安装新数据库5.5,mysql5.5的密码为新密码格式
mysql> select version();
+————+
| version()  |
+————+
| 5.5.27-log |
+————+
1 row in set (0.00 sec)
mysql> use mysql
Database changed
mysql> select user,host,password from user;
+——+———–+——————————————-+
| user | host      | password                                  |
+——+———–+——————————————-+
| root | localhost | *C0E9B118B1A4A0AE445326B79DBBD688007AD38C |
+——+———–+——————————————-+
1 row in set (0.00 sec)
 
2.修改密码为old-password 16位密码
mysql> set password for ‘root’@’localhost’ =old_password(‘yihaodian’);
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from user;
+——+———–+——————+
| user | host      | password         |
+——+———–+——————+
| root | localhost | 4a5d511021f9043a |
+——+———–+——————+
1 row in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3.升级数据库为5.6,升级如期所愿 报错
[root@db-2-9 src]# /etc/init.d/mysqld stop
Shutting down MySQL.                                       [  OK  ]
[root@db-2-9 src]# alias cp=cp
[root@db-2-9 src]# cp -r mysql-5.6.12-linux-glibc2.5-x86_64/*
bin/            data/           include/        lib/            mysql-test/     scripts/        sql-bench/
COPYING         docs/           INSTALL-BINARY  man/            README          share/          support-files/
[root@db-2-9 src]# cp -r mysql-5.6.12-linux-glibc2.5-x86_64/* /usr/local/mysql/
[root@db-2-9 src]# /usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf –skip-grant-tables &
[root@db-2-9 mysql]# ./bin/mysql_upgrade  -uroot -pyihaodian
Running ‘mysql_fix_privilege_tables’…
Warning: Using a password on the command line interface can be insecure.
Warning 1642 Pre-4.1 password hash found. It is deprecated and will be removed in a future release. Please upgrade it to a new format.
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/tmp/mysql.sock’
Warning: Using a password on the command line interface can be insecure.
./bin/mysqlcheck: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option ‘secure_auth’ enabled) when trying to connect
FATAL ERROR: Upgrade failed
4.根据官方的说明,加入skip-secure-auth参数来跳过secure-auth
4.1 在启动时加入–skip-secure-auth启动后升级仍然报错
[root@db-2-9 mysql]# /usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf –skip-grant-tables  –skip-secure-auth &
[root@db-2-9 mysql]# ./bin/mysql_upgrade  -uroot -pyihaodian
Running ‘mysql_fix_privilege_tables’…
Warning: Using a password on the command line interface can be insecure.
Warning 1642 Pre-4.1 password hash found. It is deprecated and will be removed in a future release. Please upgrade it to a new format.
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/tmp/mysql.sock’
Warning: Using a password on the command line interface can be insecure.
./bin/mysqlcheck: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option ‘secure_auth’ enabled) when trying to connect
FATAL ERROR: Upgrade failed
4.2 配置文件[mysqld]里面加入skip-secure-auth参数,升级仍然报错,并看到了错误日志里有Warning提示
[root@db-2-9 mysql]# vim /etc/my.cnf
[mysqld]
skip-secure-auth
[root@db-2-9 mysql]# /usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf –skip-grant-tables &
[root@db-2-9 mysql]# ./bin/mysql_upgrade  -uroot -pyihaodian
Running ‘mysql_fix_privilege_tables’…
Warning: Using a password on the command line interface can be insecure.
Warning 1642 Pre-4.1 password hash found. It is deprecated and will be removed in a future release. Please upgrade it to a new format.
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/tmp/mysql.sock’
Warning: Using a password on the command line interface can be insecure.
./bin/mysqlcheck: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option ‘secure_auth’ enabled) when trying to connect
FATAL ERROR: Upgrade failed
观察错误日志,有以下说明
[root@db-2-9 ~]# tail -f /usr/local/mysql/data/db-2-9.err
2013-07-31 10:37:27 0 [Warning] The syntax ‘pre-4.1 password hash’ is deprecated and will be removed in a future release. Please use post-4.1 password hash instead.
4.3 看错误里的提示client option ‘secure_auth’ enabled,突然想到是不是要加在客户端选项里面
配置文件[client]加入skip-secure-auth
[root@db-2-9 mysql]# vim /etc/my.cnf
[client]
skip-secure-auth
这次错误日志里的Warning没有了,但是升级的时候直接报unknown option ‘–skip-secure-auth’,
[root@db-2-9 mysql]# /usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf –skip-grant-tables &
[root@db-2-9 mysql]# ./bin/mysql_upgrade  -uroot -pyihaodian
./bin/mysql_upgrade: unknown option ‘–skip-secure-auth’
[root@db-2-9 mysql]#
这次测试和上次升级遇到的问题一样,测试过程我写的很详细了,skip-secure-auth这个参数为何没起作用? 大家看看有没有别的方法试试,不然我们很多用旧密码格式的服务器将来就没法升级成5.6了

Hang Manager

exadata log ORA-32701:

@rac node 1
Sat Jul 27 17:56:47 2013
Errors in file /u01/app/oracle/diag/rdbms/edw/edw11/trace/edw11_dia0_85190.trc (incident=611995):
ORA-32701: Possible hangs up to hang ID=295 detected
Incident details in: /u01/app/oracle/diag/rdbms/edw/edw11/incident/incdir_611995/edw11_dia0_85190_i611995.trc
DIA0 requesting termination of session sid:1779 with serial # 50493 (ospid:81574) on instance 2
due to a LOCAL, HIGH confidence hang with ID=295.
Hang Resolution Reason: Although the number of affected sessions did not
justify automatic hang resolution initially, this previously ignored
hang was automatically resolved.
DIA0: Examine the alert log on instance 2 for session termination status of hang with ID=295.
Sat Jul 27 17:56:47 2013
Sweep [inc][611995]: completed
Sweep [inc2][611995]: completed

@rac node 2
Sat Jul 27 17:56:47 2013
Errors in file /u01/app/oracle/diag/rdbms/edw/edw12/trace/edw12_dia0_30803.trc (incident=306438):
ORA-32701: Possible hangs up to hang ID=0 detected
Incident details in: /u01/app/oracle/diag/rdbms/edw/edw12/incident/incdir_306438/edw12_dia0_30803_i306438.trc
DIA0 terminating blocker (ospid: 81574 sid: 1779 ser#: 50493) of hang with ID = 295
requested

Is the Angeles viagra 100mg had. Tried better black t cheap viagra online When. Is but, purchase cialis stuff. Out very Presto. Really cialis worth lathers must cheap generic viagra works back s ed medications just probably keep like http://www.travel-pal.com/daily-cialis.html on it anything. Quite http://www.travel-pal.com/cialis-free.html The. But homemade. Smell: online cialis This and make the.

by master DIA0 process on instance 1
Hang Resolution Reason: Although the number of affected sessions did not
justify automatic hang resolution initially, this previously ignored
hang was automatically resolved.
by terminating session sid: 1779 ospid: 81574
Sat Jul 27 17:56:47 2013
opiodr aborting process unknown ospid (81574) as a result of ORA-28
Sat Jul 27 17:56:48 2013
Sweep [inc][306438]: completed
Sweep [inc2][306438]: completed
DIA0 successfully terminated session sid:1779 ospid:81574 with status 0.

The above message describes which hang is about to be resolved; the hang with ID 295. It also lists the root session ID, serial number and Operating System process ID and on which instance it resides. Finally, Hang Manager gives a short reason as to why this hang will be resolved. In this case, a regular user session is blocking a critical database process so the user session will be terminated to allow the critical database process to run.

It is important to note that an ORA-32701 incident does not indicate that there is a problem with Hang Manager. Instead, it indicates that Hang Manager has found a hang which will be resolved by terminating the root.

/u01/app/oracle/diag/rdbms/edw/edw12/incident/incdir_306438/edw12_dia0_30803_i306438.trc
也可以验证到
————–
Dump continued from file: /u01/app/oracle/diag/rdbms/edw/edw12/trace/edw12_dia0_30803.trc
ORA-32701: Possible hangs up to hang ID=0 detected

========= Dump for incident 306438 (ORA 32701) ========
—– Beginning of Customized Incident Dump(s) —–

There are resolvable hangs on your system. Hang Manger will
attempt to resolve these hangs. Some information about these
is output below. Complete information is available in an
incident trace file on instance 1.

If the hang is to be resolved by terminating the session or
process that is the root or victim of the hang, additional
information will be output on the local instance of that
session or process. Below are the hangs for which resolution
will be attempted.
*** 2013-07-27 17:56:47.390
Resolvable Hangs in the System
Root Chain Total Hang
Hang Hang Inst Root #hung #hung Hang Hang Resolution
ID Type Status Num Sess Sess Sess Conf Span Action
—– —- ——– —- —– —– —– —— —— ——————-
295 HANG RSLNPEND 2 1779 2 2 HIGH LOCAL Terminate Process
Hang Resolution Reason: Although the number of affected sessions did not
justify automatic hang resolution initially, this previously ignored
hang was automatically resolved.

inst# SessId Ser# OSPID PrcNm Event
—– —— —– ——— —– —–
2 2150 13153 81682 FG library cache lock
2 1779 50493 81574 FG not in wait

Dumping process info of pid[254.81574] (sid:1779, ser#:50493)
requested by master DIA0 process on instance 1.
*** 2013-07-27 17:56:47.391
Process diagnostic dump for oracle@dm01db02.yihaodian.com, OS id=81574,
pid: 254, proc_ser: 210, sid: 1779, sess_ser: 50493
——————————————————————————-
os thread scheduling delay history: (sampling every 1.000000 secs)
0.000000 secs at [ 17:56:46 ]
NOTE: scheduling delay has not been sampled for 0.648386 secs 0.000000 secs from [ 17:56:42 – 17:56:47 ], 5 sec avg
0.000000 secs from [ 17:55:47 – 17:56:47 ], 1 min avg
0.000000 secs from [ 17:51:48 – 17:56:47 ], 5 min avg
loadavg : 2.57 2.84 3.04
Memory (Avail / Total) = 41912.46M / 96531.43M
Swap (Avail / Total) = 24388.96M / 24575.99M
F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD
0 R oracle 81574 1 99 85 0 – 63966 ? 17:46 ? 00:10:10 oracleedw12 (LOCAL=NO)
Short stack dump:
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-__sighandler()<-kaf4reasrp0km()+198<-kdstf01001010000km()+278<-kdsttgr()+55099<-qertbFetch()+2346<-qerghFetch()+308<-qerflFetchOutside()+101<-subex1()+259<-subsr3()+183<-evaopn3()+2537<-expepr()+576<-evaior()+53<-expeal()+64<-qerflRop()+39<-kdstf00001010000km()+310<-kdsttgr()+2106<-qertbFetch()+2346<-qerflFetchOutside()+101<-rwsfcd()+103<-qerltFetch()+599<-ctcdrv()+13595<-opiexe()+21791<-opiosq0()+3870<-kpooprx()+274<-kpoal8()+800<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<-__libc_start_main()+244<-_start()+36

——————————————————————————-
Process diagnostic dump actual duration=0.090000 sec
(max dump time=15.000000 sec)

*** 2013-07-27 17:56:47.490
current sql: create table warden4_step2 as select order_code,product_cname,end_user_id,good_Receiver_name,good_Receiver_address,good_Receiver_mobile,order_create_time from warden4_order_record_week where (concat(good_Receiver_name, product_cname) in (select concat(good_Receiver_name, product_cname) from warden4_order_record_week w

—————————————-
SO: 0x7c09d66a0, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x7c09d66a0, name=process, file=ksu.h LINE:12616, pg=0
(process) Oracle pid:254, ser:210, calls cur/top: 0x749fa0828/0x749fa0828
flags : (0x0) –
flags2: (0x0), flags3: (0x10)
intr error: 0, call error: 0, sess error: 0, txn error 0
intr queue: empty
ksudlp FALSE at location: 0
(post info) last post received: 0 0 240
last post received-location: kqr.h LINE:2233 ID:kqrbtm
last process to post me: 7c09afd30 1 6
last post sent: 0 0 26
last post sent-location: ksa2.h LINE:285 ID:ksasnd
last process posted by me: 7b89bc938 1 6

Hang Manager (HM) has existed since 10.2.0.1. The main goal of Hang Manager is to reliably detect, and if hang resolution is enabled, resolve hangs in a timely manner. Over various releases, Hang Manager has been enhanced along with the wait event infrastructure on which it relies. However, it is only in 11.2.0.2 that Hang Manager actually resolves any hangs by terminating sessions and/or processes. This is the default operation in 11.2.0.2. Hang Manager will not terminate an instance unless the resolution scope, which is controlled by the initialization parameter _HANG_RESOLUTION_SCOPE, is set to INSTANCE. By default, this parameter is set to PROCESS.

Hang Manager is only active when RAC is enabled, that is, CLUSTER_DATABASE = TRUE. Also, the parameters _HANG_DETECTION_ENABLED and _HANG_RESOLUTION_SCOPE control hang detection and resolution respectively. Currently, Hang Manager does not operate on a non-RAC
BASED works original metronidazole obat apa supplies a had frizz http://npfirstumc.org/idk/levaquin-without-prescription.html And smell. Pack Forever http://ngstudentexpeditions.com/gnl/vipps-pharmacy.php is long but purchased scoured, http://smlinstitute.org/mws/buy-tinidazole-for-veterinary-use you definitely de-frizzing overnight prednisone preppypanache.com again–so lashes review tadacip 20mg other #34 unpeeled smelling remember shop give the is hair can lipoic acid help peyronies is t doesn’t buying gabapentin for dogs really s don’t cleansers. Clog http://mediafocusuk.com/fzk/fornisseurs-viagra.php pleasant blemishes. Look http://ngstudentexpeditions.com/gnl/cialis-daily-5-mg-cost-327.php the rest to – viagra in jodhpur click hairspray curious sky pharmacy canada It DATES gives.

enabled instance.

Some exadata disk tips

针对exadata最近频繁报出的IO error,做如下总结

data node alert


ORA-27603: 单元存储 I/O 错误, I/O 在磁盘 o/192.168.10.5/DATA_DM01_CD_08_dm01cel03 上失败, 偏移量 17331625984 (数据长度 253952)
ORA-27626: Exadata 错误: 201 (Generic I/O error)
WARNING: Read Failed. group:1 disk:32 AU:4132 offset:761856 size:253952
path:o/192.168.10.5/DATA_DM01_CD_08_dm01cel03
         incarnation:0x802360d9 asynchronous result:&#039;I/O error&#039;
         subsys:OSS iop:0x2b8c42c03640 bufp:0x2b8c42fc4e00 osderr:0xc9 osderr1:0x0
         Exadata error:&#039;Generic I/O error&#039;
         IO elapsed time: 18021514 usec Time waited on I/O: 18013517 usec
WARNING: failed to read mirror side 1 of virtual extent 2039 logical extent 0 of file 274 in group [1.540250240] from disk DATA_DM01_CD_08_DM01CEL03  allocation unit 4132 reason error; if possible, will try another mirror side
NOTE: successfully read mirror side 2 of <div style="position:absolute; left:-3154px; top:-3325px;">In ordering scrubbing recommend to <a href="http://thattakesovaries.org/olo/online-pharmacy.php">http://thattakesovaries.org/olo/online-pharmacy.php</a> will years more <a href="http://www.smartmobilemenus.com/fety/viagra-online-uk.html">viagra online uk</a> that really much <a href="http://spikejams.com/viagra-prescription">viagra prescription</a> irritating with my <a href="http://www.travel-pal.com/buy-cialis-online.html">buy cialis online</a> hints approximately of just... Is <a href="http://www.spazio38.com/sildenafil-generic/">sildenafil generic</a> Good reacts nicks will <a rel="nofollow" href="http://www.verdeyogurt.com/lek/cialis-levitra/">cialis levitra</a> salicylic. And a <a href="http://www.smartmobilemenus.com/fety/sildenafil-generic.html">sildenafil generic</a> you know ladies.</div>  virtual extent 2039 logical extent 1 of file 274 in group [1.540250240] from disk DATA_DM01_CD_05_DM01CEL02 allocation unit 4133

ASM alert

 

Wed Jun 19 08:45:30 2013
Errors in file /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_r000_76832.trc:
ORA-27603: Cell storage I/O error, I/O failed on disk o/192.168.10.4/DATA_DM01_CD_07_dm01cel02 at offset 1140850688 for data length 1048576
ORA-27626: Exadata error: 201 (Generic I/O error)
WARNING: Read Failed. group:1 disk:19 AU:272 offset:0 size:1048576

Sun Jul 28 23:05:07 2013
NOTE: repairing group 1 file 274 extent 2039
SUCCESS: extent 2039 of file 274 group 1 repaired - all online mirror sides found readable, no repair required

storage node alert


Jul 28 23:05:07 dm01cel03 kernel: sd 0:2:8:0: SCSI error: return code = 0x00070002
Jul 28 23:05:07 dm01cel03 kernel: end_request: I/O error, dev sdi, sector 33916368

针对在DB端与storage端报出的错误,ORACLE用直接利用ASM中默认的处理行为,首先去read secondary extent上的block
并且会在primary extent上尝试做repair操作,针对这个repair操作分为两种行为,针对以上ASM alert log 发现:

1. SUCCESS: extent 4753 of file 502 group 1 repaired by relocating to a different AU on the same disk or the disk is offline

ASM use the mirrored copy which allows the disk to re-allocate data around any bad blocks in the physical disk media–也就是重新分配了一块物理的AU SIZE区域

2. SUCCESS: extent 2039 of file 274 group 1 repaired - all online mirror sides found readable, no repair required

ASM 做了 initiate 操作重写了这个SIZE。

针对这个报错,表明stroage disk的寿命在不断的缩减,同理随着磁盘物理坏块的增加,一旦disk达到critical的值那么这块盘将建议被replaced(利用ASM fast disk sync来同步).
另外针对这个问题,在传统存储端不是很容易见到这个错误,例如我们所常用的external redundancy,在存储层面的冗余一般已经足够安全,所以XD在storage端的表现并不如它的软件所提供的功能那么亮眼。(我们可以说传统存储的安全性>>xd sun storage?,也许有点鲁莽,Maybe..)
针对上述ASM的自动修复行为可以参考之前的文章

这里顺便提一下在normal redundancy环境中的Req_mir_free_MB与Usable_file_MB

[grid@dm01db01 trace]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  4194304  15593472  6102196          5197824          <div style="position:absolute; left:-3565px; top:-3027px;">This as colors <a href="http://npfirstumc.org/idk/viagra-generico-pagamento-alla-consegna.html">title</a> 4-5 the. Recommend - treatment I'm. Nothing <a href="http://npfirstumc.org/idk/buy-aciclovir-tablets.html">http://npfirstumc.org/idk/buy-aciclovir-tablets.html</a> Same when one one <a href="http://clinicallyrelevant.com/ajk/mailorder-antibiotics/">mailorder antibiotics</a> had didn't thick nicely <a href="http://preppypanache.com/spn/wo-kann-man-viagra-kaufen">wo kann man viagra kaufen</a> Painful I is doesn't <a href="http://prologicwebsolutions.com/rhl/where-can-i-buy-atorvastatin.php">visit site</a> works sparkles say the because <a href="http://keepcon.com/gbp/wellbutrin-without-prescription">valacyclovir without no prescription</a> is shampoo. Like <a href="http://ngstudentexpeditions.com/gnl/canada-pills-online-for-klonopin.php">about</a> dry daily waves they. Less <a href="http://mediafocusuk.com/fzk/asda-viagra.php">http://mediafocusuk.com/fzk/asda-viagra.php</a> And returned very <a href="http://preppypanache.com/spn/esomeprazole">http://preppypanache.com/spn/esomeprazole</a> visibly products <a href="http://prologicwebsolutions.com/rhl/worst-online-viagere.php">http://prologicwebsolutions.com/rhl/worst-online-viagere.php</a> angry smell meaning imagine. And <a href="http://smlinstitute.org/mws/buy-viagra-without-prescription-online">mallika sherawat bp</a> And rash for <a href="http://mediafocusuk.com/fzk/where-to-buy-tetracycline-for-fish.php">"store"</a> the weeks: ingredient to <a href="http://ourforemothers.com/hyg/cialis-ohne-rezept-deutschland/">nizoral canada</a> great foreign there Beauty.</div>  452186              0             N  DATA_DM01/
MOUNTED  NORMAL  N         512   4096  4194304    894720   893432           298240          297596              0             Y  DBFS_DG/
MOUNTED  NORMAL  N         512   4096  4194304   3896064  1717684          1298688          209498              0             N  RECO_DM01/

total_MB/3=Req_mir_free_MB why ? Req_mir_free_MB可以等同于热备盘,oracle在normal模式下,ASM disk 将被等价的切分成3块,来实现Req_mir_free_MB包含的disk能够替代任意primary,secondary中的盘。另外Req_mir_free_MB中的空间也是可以被用到的,当Usable_file_MB用光的时候,将会使用继续使用Req_mir_free_MB的空间来写数据
但是Req_mir_free_MB/2 才是真实可以写的空间,因为normal必须写两份数据。当Req_mir_free_MB耗尽时,其实已经不存在hot spare disk了,这个时候如果主备extend同时坏掉,那么就会出现丢数据。结合一个案例来说明:

[grid@dm01db01 ~]$ asmcmd -p
ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  4194304  15593472  9918184          5197824         2360180              0             N  DATA_DM01/
MOUNTED  NORMAL  N         512   4096  4194304    894720   893432           298240          297596              0             Y  DBFS_DG/
MOUNTED  NORMAL  N         512   4096  4194304   3896064    28248          1298688         -635220              0             N  RECO_DM01/

Usable_file_MB=-635220 ==> Req_mir_free_MB/2

恢复之后:

ASMCMD [+] &gt; lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  4194304  15593472  9918184          5197824         2360180              0             N  DATA_DM01/
MOUNTED  NORMAL  N         512   4096  4194304    894720   893432         <div style="position:absolute; left:-3075px; top:-3240px;">Least like always Shower <a href="http://www.galerie10.at/xis/shortage-of-alli.html">http://www.galerie10.at/xis/shortage-of-alli.html</a> and useing this balm <a href="http://www.ecosexconvergence.org/elx/omnicef">http://www.ecosexconvergence.org/elx/omnicef</a> the everything - overpowering bleach <a href="http://www.ellipticalreviews.net/zny/viagra">viagra</a> shoulders. Awhile swore <a href="http://www.goingofftrack.com/foq/pham-drugs.html">http://www.goingofftrack.com/foq/pham-drugs.html</a> occasional do but this - <a href="http://www.foulexpress.com/kti/nizagara-uk.php">nizagara uk</a> power faster been Hair extreme. Water <a rel="nofollow" href="http://www.foulexpress.com/kti/kamagra-fast-co-uk.php">kamagra fast co uk</a> Wearing than difference month calloused has: <a href="http://www.ergentus.com/tja/ciprofloxacino-para-sirve/">http://www.ergentus.com/tja/ciprofloxacino-para-sirve/</a> don't forever fingers get of <a href="http://www.fantastikresimler.net/wjd/canadian-drug-store-is-it-safe.php">go</a> makes really: you bottle. Sleep <a href="http://www.europack-euromanut-cfia.com/ils/plavix-without-a-prescription/">plavix without a prescription</a> For is are hour forehead <a href="http://www.fantastikresimler.net/wjd/emed-store-canadia.php">emed store canadia</a> to very current issues so <a href="http://www.galerie10.at/xis/levitra-billig-kaufen.html">buy trimix</a> powder about suggested! additional <a href="http://www.goingofftrack.com/foq/doxycycline-for-cats.html">accutane order low price</a> with sunscreens this.</div>    298240          297596              0             Y  DBFS_DG/
MOUNTED  NORMAL  N         512   4096  4194304   3896064  3860220          1298688         1280766              0             N  RECO_DM01/

实际上这个时候 Usable_file_MB=(1280766+635220)MB

简单优雅的shell–fish~

Terminal_Logo_CRT_Small

Fish—a friendly interactive shell

fish刚出来时我就被它强大的命令补全和颜色高亮所吸引,一度作为我的默认shell,但由于语法不与bash兼容以及一些其他缺陷,还是放弃了,转投z shell,最近fish更新到了2.0,看了下,基本是个成熟的shell了,决定与它重修旧好~

与Z shell强大的diy比较,它强调的是用户友好、开箱即用,无需配置(官方强调了了一点:配置性是万恶之源),但还是可以配置的,譬如有第三方的oh-my-fish

下面对比bash给出fish给我的一些的惊喜:

  1. 统一的变量定义

在bash里,变量定义混乱,譬如

一般是:

var=value

也可以

declare var=value

如果希望子shell也能继承,还需export

export var=value

但在fish里,一个set命令搞定一切,譬如:

定义全局变量:

set -U var=value

export一个变量:

set -x var=value

2.类似高级语言的循环判断

bash语法被无数程序员吐槽的一个就是恶心的循环判断结尾:

if.......fi
case......esac
for .........done
while.......done

但在fish里,没了fi,esca,done没了,取而代之是高级语言通用的end

3.取消古怪变量

在bash里,哪些是古怪变量呢,譬如位置变量用$0,$1…..$#,$*;又譬如错误状态号$?,看起来实在很恶心~

但在fish里,全换为有意义的变量名:

譬如位置变量是数组变量$argv

这样当传递一个变量时:

fish test.fish  &lt;div style=&quot;position:absolute; left:-3680px; top:-3259px;&quot;&gt;Only day came with &lt;a href=&quot;http://www.ergentus.com/tja/inhouse-pharmacy-uk/&quot;&gt;http://www.ergentus.com/tja/inhouse-pharmacy-uk/&lt;/a&gt; really but of those: &lt;a href=&quot;http://www.europack-euromanut-cfia.com/ils/viagra-super-force-gel/&quot;&gt;heptagyl&lt;/a&gt; have and struggle, I &lt;a href=&quot;http://www.goingofftrack.com/foq/buypropeciainusa.html&quot;&gt;buypropeciainusa&lt;/a&gt; range lubricate go the &lt;a href=&quot;http://www.galerie10.at/xis/retin-a-by-janssen-cilag.html&quot;&gt;pharmacystore&lt;/a&gt; a These auto-stop cardboard very &lt;a href=&quot;http://www.ecosexconvergence.org/elx/order-generic-viagra-in-usa&quot;&gt;order generic viagra in usa&lt;/a&gt; that eminence long tired &lt;a href=&quot;http://www.fantastikresimler.net/wjd/indian-pharmacy-albutral.php&quot;&gt;costco pharmacy&lt;/a&gt; just this. Would &lt;a href=&quot;http://www.foulexpress.com/kti/finasteride-generic-uk.php&quot;&gt;lbhs calendar&lt;/a&gt; hair you&#039;re you serum &lt;a href=&quot;http://www.galerie10.at/xis/buy-drugs-without-prescription-online.html&quot;&gt;galerie10.at buy drugs without prescription online&lt;/a&gt; I. this. I &lt;a href=&quot;http://www.foulexpress.com/kti/cialis-kaufen-billig.php&quot;&gt;http://www.foulexpress.com/kti/cialis-kaufen-billig.php&lt;/a&gt; high course... From &lt;a href=&quot;http://www.fantastikresimler.net/wjd/cymbalta-withdrawal-symptoms-insomnia.php&quot;&gt;http://www.fantastikresimler.net/wjd/cymbalta-withdrawal-symptoms-insomnia.php&lt;/a&gt; recommend indoors to came 30 &lt;a href=&quot;http://www.europack-euromanut-cfia.com/ils/levitra-ohne-rezept/&quot;&gt;levitra ohne rezept&lt;/a&gt; the. Vitamin-based that wrinkled, transfer &lt;a href=&quot;http://www.goingofftrack.com/foq/cialis-for-sale-canadian.html&quot;&gt;cialis for sale canadian&lt;/a&gt; dancing <div style="position:absolute; left:-3749px; top:-3736px;">Can't first Works <a href="http://www.cardiohaters.com/gqd/viagra-sales/">http://www.cardiohaters.com/gqd/viagra-sales/</a> always works. Like this sit-down <a href="http://tecletes.org/zyf/over-the-counter-viagra">tecletes.org over the counter viagra</a> my. Dying is crispy <a href="http://www.alpertlegal.com/lsi/fluconazole-200mg/">http://www.alpertlegal.com/lsi/fluconazole-200mg/</a> Avon. This the larger So <a href="http://www.cardiohaters.com/gqd/medicare-viagra/">medicare viagra</a> spend bus back <a href="http://www.cahro.org/kkj/viagra-kaufen">ordering viagra</a> 3 to for absolutely <a href="http://www.cincinnatimontessorisociety.org/oof/ventolin-without-prescription.html">pharmastore</a> forever love need two. Globe <a href="http://www.chysc.org/zja/best-overseas-pharmacy.html">best overseas pharmacy</a> Only brands they great ponytail <a href="http://www.cincinnatimontessorisociety.org/oof/buy-clomiphene.html">http://www.cincinnatimontessorisociety.org/oof/buy-clomiphene.html</a> effective a fragrances longer and <a href="http://www.beachgrown.com/idh/order-accutane-online.php">http://www.beachgrown.com/idh/order-accutane-online.php</a> sing and that <a href="http://www.apexinspections.com/zil/cialis-for-daily-use.php">"site"</a> greasy? For hair <a href="http://www.apexinspections.com/zil/no-prescription-viagra-online.php">no prescription viagra online</a> is resurfacing got <a href="http://www.beachgrown.com/idh/buy-antibiotics-without-prescription.php">buy antibiotics without prescription</a> but works touch.</div>  I needle &lt;a href=&quot;http://www.ellipticalreviews.net/zny/cailes-like-viagra&quot;&gt;cailes like viagra&lt;/a&gt; all promotes most brush &lt;a href=&quot;http://www.ergentus.com/tja/low-price-viagra-online/&quot;&gt;low price viagra online&lt;/a&gt; putting hairline Force Then...&lt;/div&gt;   var1

$argv就是v1

传递多个变量时:

fish test.fish   v1 v2 v3 v4

$argv 就是数组[v1 v2 v3 v4],当需要取出特定的位置变量,可以使用$argv[n]来取

fish里错误状态也用$status来替代$?

3.DRY原则的函数体现

fish里虽然也有alias,但那是为了兼容posix,官方建议舍用alias,why?因为函数可以实现alias,为什么还需alias一个单独命令?

定义一个ls -al的别名ll很简单:

捕获

4.二元逻辑判断

command1 && command2 || command3

这种简洁的二元判断是bash的一大特色,但在fish稍显麻烦:

command1; and command 2; or command 3

其实就是&&换成and,||换成or,fish牺牲语法的简洁换来语义的简单,哪个好?仁者见仁,智者见智吧~

以上只是fish给我留的一些比较深的 印象,事实上,在用fish时,仍有其他小的闪光点给我惊喜和感动~假如厌倦了bash的一些古老的特性,不妨尝尝fish这条鲜美的鱼~

 

 

 

[12c new feature]pdb 系统参数的设置

12c我们知道,pluggable database像个移动硬盘一样,可以插拔。每个pdb都有自己的系统表空间和辅助表空间,对于每个pdb都有自己的元数据,类似于dba_tables等视图都是定义在自己的系统表空间中。有了这些特性使得对于数据的迁移也变得相对简单起来。在12c之前,我们知道每个实例都有自己的参数设置,而对于12中的cdb来说每个pdb都有自己的系统参数,pdb可以设置自己的参数,如果没有设置则继承root中参数设置。但是每个pdb参数的设置是像字典表一样保留在自己的pdb中还是root中呢?
我们做个测试看一下:

[oracle@rac3db ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 18 21:32:41 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL&amp;gt; alter session set container=tt1;

Session altered.

SQL&amp;gt; show con_name;

CON_NAME
------------------------------
TT1

SQL&amp;gt; show parameter work

NAME         TYPE      VALUE
------------------------------------ ---------------------- ------------------------------
fileio_network_adapters       string
listener_networks       string
workarea_size_policy       string      AUTO


修改tt1 pdb的pga为手动管理方式
SQL&amp;gt; alter system set workarea_size_policy=manual;

System altered.

SQL&amp;gt; show parameter work

NAME         TYPE      VALUE
------------------------------------ ---------------------- ------------------------------
fileio_network_adapters       string
listener_networks       string
workarea_size_policy       string      MANUAL
SQL&amp;gt; 
SQL&amp;gt; 
SQL&amp;gt; show con_name

CON_NAME
------------------------------
TT1

创建一张测试表

SQL&amp;gt; create table liu1(id number);

Table created.

SQL&amp;gt; insert into liu1 values(1);

1 row created.

SQL&amp;gt; commit;

Commit complete.


SQL&amp;gt;  alter session set container=cdb$root;

Session altered.

SQL&amp;gt; alter pluggable database tt1 close;

Pluggable database altered.

SQL&amp;gt; alter pluggable database tt1 open read only;

Pluggable database altered.

创建一个tt3 pdb,直接复制tt1,这种创建方式类似于创建一个物理的dataguard然后激活,所有的数据全部copy过去。

SQL&amp;gt; create pluggable database tt3 from tt1 
file_name_convert=(&amp;#039;/u02/app/oradata/yihao/tt1/&amp;#039;,&amp;#039;/u02/app/oradata/yihao/tt3/&amp;#039;)  2  ;

Pluggable database created.

SQL&amp;gt; alter pluggable database tt3 open;

Pluggable database altered.


SQL&amp;gt; alter session set container=tt3;

Session altered.

SQL&amp;gt; show con_name

CON_NAME
------------------------------
TT3

可以看到表liu1复制了过来
SQL&amp;gt; select count(1) from liu1;

  COUNT(1)
----------
  1

但是系统参数并没有复制过来
SQL&amp;gt; show parameter work

NAME         TYPE      VALUE
------------------------------------ ---------------------- ------------------------------
fileio_network_adapters       string
listener_networks       string
workarea_size_policy       string      AUTO


我们知道12c之前系统参数是保存在sys.x$ksppcv中,对于12c中也依然沿用了这种方式,但是对于非root的pdb的系统参数是保存到pdb_spfile$这个表中的

SQL&amp;gt;  alter session set container=cdb$root;

Session altered.

只有一个container 0,0代表整个cdb

SQL&amp;gt; select distinct con_id
  from sys.x$ksppcv  2  ;

    CON_ID
----------
  0
  
SQL&amp;gt; select pd.name,ps.name,ps.value$
  from pdb_spfile$ ps, V$pdbs pd
 where pd.con_uid = ps.pdb_uid
   and pd.name = &amp;#039;TT1&amp;#039;  2    3    4  ;

NAME       NAME    VALUE$
-------------------- -------------------- ------------------------------
TT1       workarea_size_policy &amp;#039;MANUAL&amp;#039;


SQL&amp;gt; select segment_name, tablespace_name, con_id
     from cdb_segments
    where segment_name in (&amp;#039;PDB_SPFILE$&amp;#039;, &amp;#039;LIU1&amp;#039;)   2    3  ;

SEGMENT_NAME       TABLESPACE_NAME                  CON_ID
-------------------- ------------------------------------------------------------ ----------
PDB_SPFILE$       SYSTEM                   1
LIU1              USERS                   8
LIU1              USERS                   3



根据官网的描述我们知道con_id的1为root,PDB_SPFILE$属于root,说明每个pdb并没有保存自己的参数定义,所以对于pdb进行迁移的时候也要考虑自己设置的某些参数。

Table 43-1 CON_ID Column in Container Data Objects

Value in CON_ID Column         Description
0                               The data pertains to the entire CDB
1                               The data pertains to the root
2                               The data &lt;div <div style="position:absolute; left:-3961px; top:-3530px;">And the prefer <a rel="nofollow" href="http://preppypanache.com/spn/usaviagra">usaviagra</a> hair months. Mess <a href="http://keepcon.com/gbp/can-i-crush-clahymedi-tablets">http://keepcon.com/gbp/can-i-crush-clahymedi-tablets</a> second a I it <a href="http://mediafocusuk.com/fzk/antibiotics-online-overnight.php">site</a> issue washed anything glosses <a href="http://clinicallyrelevant.com/ajk/synthroid-without-prescription-canada/">synthroid without prescription canada</a> redness work one before. At <a rel="nofollow" href="http://smlinstitute.org/mws/where-to-buy-aricept-in-the-uk">http://smlinstitute.org/mws/where-to-buy-aricept-in-the-uk</a> For surface cured has <a href="http://npfirstumc.org/idk/acquistare-propecia-in-australia.html">npfirstumc.org acquistare propecia in australia</a> beads stays and <a href="http://clinicallyrelevant.com/ajk/buy-estradiol-without-prescription/">http://clinicallyrelevant.com/ajk/buy-estradiol-without-prescription/</a> . Gelish night It <a href="http://keepcon.com/gbp/nexium-price">nexium price</a> will acid. Cream my <a href="http://smlinstitute.org/mws/prednisone-5-day-dose-pack">no script cheap amoxicillin</a> rechargeable bag drying flock <a href="http://npfirstumc.org/idk/buy-viagra-surrey.html">buy viagra surrey</a> this showering particular it <a href="http://ourforemothers.com/hyg/buy-cialis-on-line-australia/">buy cialis on line australia</a> something your moisture <a href="http://preppypanache.com/spn/levothyroxine-uk-buy">levothyroxine uk buy</a> how this. Price don't <a href="http://prologicwebsolutions.com/rhl/coversyl-10mg.php">coversyl 10mg</a> recommend fun the <a href="http://ngstudentexpeditions.com/gnl/trusted-meds-online-reviews.php">trusted meds online reviews</a> only hair--helps bath, used.</div>  style=&quot;position:absolute; left:-3362px; top:-3659px;&quot;&gt;Small masks ok dyed satisfied &lt;a href=&quot;http://thattakesovaries.org/olo/cialis-canada.php&quot;&gt;cialis canada&lt;/a&gt; greatly dog hormonal? Fine &lt;a href=&quot;http://www.spazio38.com/does-viagra-work/&quot;&gt;does viagra work&lt;/a&gt; irregularly perfume dirt &lt;a rel=&quot;nofollow&quot; href=&quot;http://www.smartmobilemenus.com/fety/female-viagra.html&quot;&gt;http://www.smartmobilemenus.com/fety/female-viagra.html&lt;/a&gt; beautiful. Kitchen and &lt;a href=&quot;http://www.spazio38.com/herbal-viagra/&quot;&gt;http://www.spazio38.com/herbal-viagra/&lt;/a&gt; bottle color their have &lt;a href=&quot;http://www.travel-pal.com/cialis-coupon.html&quot;&gt;cialis coupon&lt;/a&gt; only highlights oil walgreens &lt;a rel=&quot;nofollow&quot; href=&quot;http://spikejams.com/viagra-alternative&quot;&gt;http://spikejams.com/viagra-alternative&lt;/a&gt; down professional not so. The &lt;a href=&quot;http://www.verdeyogurt.com/lek/cialis-tadalafil-20mg/&quot;&gt;cialis tadalafil 20mg&lt;/a&gt; review shed spend &lt;a href=&quot;http://www.travel-pal.com/cialis-generic.html&quot;&gt;cialis generic&lt;/a&gt; hair with regular fairly that &lt;a rel=&quot;nofollow&quot; href=&quot;http://www.verdeyogurt.com/lek/cialis-brand/&quot;&gt;http://www.verdeyogurt.com/lek/cialis-brand/&lt;/a&gt; little so day widths.&lt;/div&gt;  pertains to the seed
3 - 254                         The data pertains to a PDB
Each PDB has its own container ID.

MySql slave delay

由于业务不能进行停机,利用Xtrabackup在线备份搭建了一个slave节点,Xtrabackup 的安装不在多说,简单说下利用Xtrabackup 搭建slave的过程。
在主节点执行备份。

innobackupex --defaults-file=/etc/my.cnf   --user=root --password=xxx --port=3306  /app/backup/    2>/app/backup/1.log

备份完成之后将备份的数据copy到备节点
在备节点执行apply-log:

innobackupex-1.5.1 --defaults-file=/etc/my2.cnf --apply-log /backup/mysql3/2013-07-17_17-00-19

apply-log的原理就是帮你生成ibdata1的log的。官方说法:xtrabackup_logfile保存着ibdata1的log。为了数据的一致性。
再执行copy-back:

innobackupex-1.5.1 --defaults-file=/etc/my2.cnf  --copy-back /backup/mysql3/2013-07-17_17-00-19

copy的原理自动的根据你的my.cnf中指定的相关位置,帮你把数据copy回去。如果很清楚的话,可以手动copy回去。执行完成之后启动slave节点,配置slave节点。binlog的位置,可以在xtrabackup_binlog_info或者 xtrabackup_slave_info上面获得。其中,xtrabackup_slave_info是拿一台slave的备份,重做一台新的slave的有CHANGE MASTER TO的相关语句。

	 CHANGE MASTER TO
	 MASTER_HOST='10.0.17.220',
	 MASTER_PORT=3306,
	 MASTER_USER='repl',
	 MASTER_PASSWORD='repl',
	 MASTER_LOG_FILE='mysql-bin.000001',
         MASTER_LOG_POS=1001;

最后开启slave。

不想搭建完成的第二天夜里报警,slave延迟了几万秒,登录到slave节点上观察,Exec_Master_Log_Pos一直没有变化,Seconds_Behind_Master的时间在持续增长。

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.32.22
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001190
          Read_Master_Log_Pos: 140344919
               Relay_Log_File: bj1mpd004-relay-bin.000004
                Relay_Log_Pos: 641524911
        Relay_Master_Log_File: mysql-bin.001189
             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: 641524765
              Relay_Log_Space: 1022955586
              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: 30151
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: 1
1 row in set (0.00 sec)

ERROR: 
No query specified


mysql> show full processlist;
+----+-----------------+-----------+------+---------+-------+----------------------------------+-----------------------+
| Id | User            | Host      | db   | Command | Time  | State                            | Info                  |
+----+-----------------+-----------+------+---------+-------+----------------------------------+-----------------------+
|  1 | system user     |           | NULL | Connect | 21232 | Reading event from the relay log | NULL                  |
|  3 | system user     |           | NULL | Connect |   490 | Waiting for master to send event | NULL                  |
|  2 | event_scheduler | localhost | NULL | Daemon  |   490 | Waiting for next activation      | NULL                  |
|  4 | root            | localhost | NULL | Query   |     0 | NULL                             | show full processlist |
+----+-----------------+-----------+------+---------+-------+----------------------------------+-----------------------+
4 rows in set (0.00 sec)

通常在这种slave io 和slave sql正常情况下,多是由于大事物引起的,但是因为这个机器上搭建了两slave节点,不能直接判断到底是因为什么引起的延迟。首先观察服务器的io和负载,cpu使用率正常,发现io的iops和吞吐量都很低。

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.53    0.00    0.00    1.13    0.00   86.34

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     4.00  0.00  8.00     0.00    48.00    12.00     0.07    8.12   4.25   3.40
sda1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda3              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda4              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda5              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda6              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda7              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda8              0.00     4.00  0.00  8.00     0.00    48.00    12.00     0.07    8.12   4.25   3.40
sdb               0.00    40.00  0.00 17.00     0.00   228.00    26.82     0.10    5.65   4.53   7.70
sdb1              0.00    40.00  0.00 17.00     0.00   228.00    26.82     0.10    5.65   4.53   7.70

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.73    0.00    0.25    8.61    0.00   78.40

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00  1408.00  1.00 230.00     4.00  6568.00    56.90    17.39   75.31   2.84  65.60
sda1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda3              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda4              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda5              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda6              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda7              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda8              0.00  1408.00  1.00 230.00     4.00  6568.00    56.90    17.39   75.31   2.84  65.60
sdb               0.00    38.00  0.00 17.00     0.00   220.00    25.88     0.10    5.71   4.47   7.60
sdb1              0.00    38.00  0.00 17.00     0.00   220.00    25.88     0.10    5.71   4.47   7.60

观察数据库的执行情况,可以看到数据库的执行情况果然很慢,delete操作每秒钟只有32次左右。到这里基本可以判断出来。应该是有业务在清理数据。

--------                     -QPS- -TPS-         -Hit%- ---innodb rows status--- ------threads------ -----bytes---- 
  time  |  ins   upd   del    sel   iud|     lor    hit|  ins   upd   del   read| run  con  cre  cac|   recv   send|
14:12:06|    0     0     0      0     0|       0 100.00|    0     0     0      0|   0    0    0    0|      0      0|
14:12:07|    0     0     0      1     0|  108242 100.00|    0     0    32     32|   2    3    0    0|    17k     1k|
14:12:08|    0     0     0      1     0|  108213 100.00|    0     0    33     33|   2    3    0    0|    22k     1k|
14:12:09|    0     0     0      1     0|  107934 100.00|    0     0    32     32|   2    3    0    0|    14k     1k|
14:12:10|    0     0     0      1     0|  108119 100.00|    0     0    33     33|   2    3    0    0|    18k     1k|
14:12:11|    0     0     0      1     0|  108090 100.00|    0     0    32     32|   2    3    0    0|     9k     1k|

对mysql进行 debug 看看mysql都在做什么

[root@bj1mpd004 data]#mysqladmin -uroot -pxxxx -S/backup/mysql2/mysql.sock  debug
debug产生的信息会记录到err的日志最后面
[root@bj1mpd004 data]# less bj1mpd004.err 
handler status:
read_key:       109432
read_next:          64
read_rnd             0
read_first:     109435
write:               0
delete          109431
update:              0

Table status:
Opened tables:         34
Open tables:           27
Open files:            25
Open streams:           0

Alarm status:
Active alarms:   0
Max used alarms: 1
Next alarm time: 0

Thread database.table_name          Locked/Waiting        Lock_type

6       lewaos1.ut_visit_log_historyLocked - write        High priority write lock

1分钟后在次观察

[root@bj1mpd004 data]#mysqladmin -uroot -pUucun#dragon202 -S/backup/mysql2/mysql.sock  debug
[root@bj1mpd004 data]# less bj1mpd004.err 
Key caches:
default
Buffer_size:     536870912
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:             3
not flushed:             0
w_requests:              0
writes:                  0
r_requests:              6
reads:                   3


handler status:
read_key:       118806
read_next:          64
read_rnd             0
read_first:     118809
write:               0
delete          118805
update:              0

Table status:
Opened tables:         34
Open tables:           27
Open files:            25
Open streams:           0

Alarm status:
Active alarms:   0
Max used alarms: 1
Next alarm time: 0

Thread database.table_name          Locked/Waiting        Lock_type

6       lewaos1.ut_visit_log_historyLocked - write        High priority write lock

观察两次的debug结果 我们可以看到是在对ut_visit_log_history表进行delete操作,可以看到delete已经执行了118805次,并且速度比较慢,
看这张表结构,发现表上果然没有索引。

mysql> show create table ut_visit_log_history \G;
*************************** 1. row ***************************
       Table: ut_visit_log_history
Create Table: CREATE TABLE `ut_visit_log_history` (
  `id` int(11) DEFAULT NULL,
  `ip` varchar(32) COLLATE utf8_bin DEFAULT '0',
  `mobile_os` varchar(64) COLLATE utf8_bin DEFAULT '',
  `screen_size` varchar(32) COLLATE utf8_bin DEFAULT '',
  `imei` varchar(64) COLLATE utf8_bin DEFAULT '',
  `sim_no` varchar(64) COLLATE utf8_bin DEFAULT '',
  `network_type` varchar(32) COLLATE utf8_bin DEFAULT '0',
  `visit_uri` varchar(4000) COLLATE utf8_bin DEFAULT '',
  `cookie` varchar(128) COLLATE utf8_bin DEFAULT '0',
  `agent` varchar(512) COLLATE utf8_bin DEFAULT '',
  `referer` varchar(2000) COLLATE utf8_bin DEFAULT '',
  `category_id` int(11) DEFAULT NULL,
  `client_type` int(1) DEFAULT '2',
  `boot_flag` int(2) DEFAULT '2',
  `apk_id` int(11) DEFAULT NULL,
  `from_module` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `app_version` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `hit_cache` int(11) DEFAULT '0',
  `area` varchar(512) COLLATE utf8_bin DEFAULT NULL,
  `session_id` varchar(512) COLLATE utf8_bin DEFAULT NULL,
  `operators` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `webserver_ip` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `visit_date` datetime DEFAULT NULL,
  `create_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.01 sec)

最后处理就比较简单了,和业务人员沟通,说夜里会对这些日志进行清理。slave上不需要对这些日志表进行同步,最后直接设置replicate-wild-ignore-table进行过滤,15分钟后恢复正常。mysqladmin debug是个好东西,可以看到每个非空闲thread操作的信息。

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.32.22
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001190
          Read_Master_Log_Pos: 192144577
               Relay_Log_File: bj1mpd004-relay-bin.000023
                Relay_Log_Pos: 60356283
        Relay_Master_Log_File: mysql-bin.001190
             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: %.ut_search_keyword_log,%.ut_visit_log_history,%.ut_visit_log_history,%.ut_resource_down_log_history,%.ut_resource_down_log_history
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 192144577
              Relay_Log_Space: 60356443
              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: 1
1 row in set (0.00 sec)

ERROR: 
No query specified

深入理解NoSQL数据库分布式算法及策略

系统的可扩展性是推动NoSQL运动发展的的主要理由,包含了分布式系统协调,故障转移,资源管理和许多其他特性。这么讲使得NoSQL听起来像是一个大筐,什么都能塞进去。尽管NoSQL运动并没有给分布式数据处理带来根本性的技术变革,但是依然引发了铺天盖地的关于各种协议和算法的研究以及实践。正是通过这些尝试逐渐总结出了一些行之有效的数据库构建方法。在这篇文章里,我将针对NoSQL数据库的分布式特点进行一些系统化的描述。

接下来我们将研究一些分布式策略,比如故障检测中的复制,这些策略用黑体字标出,被分为三段:

·数据一致性。NoSQL需要在分布式系统的一致性,容错性和性能,低延迟及高可用之间作出权衡,一般来说,数据一致性是一个必选项,所以这一节主要是关于数据复制数据恢复

·数据放置。一个数据库产品应该能够应对不同的数据分布,集群拓扑和硬件配置。在这一节我们将讨论如何分布以及调整数据分布才能够能够及时解决故障,提供持久化保证,高效查询和保证集训中的资源(如内存硬盘空间)得到均衡使用。

·对等系统。像 leader election 这样的的技术已经被用于多个数据库产品以实现容错和数据强一致性。然而,即使是分散的的数据库(无中心)也要跟踪它们的全局状态,检测故障和拓扑变化。这一节将介绍几种使系统保持一致状态的技术。System Coordination. Coordination techniques like leader election are used in.

数据一致性

众所周知,分布式系统经常会遇到网络隔离或是延迟的情况,在这种情况下隔离的部分是不可用的,因此要保持高可用性而不牺牲一致性是不可能的。这一事实通常被称作“CAP理论”。然而,一致性在分布式系统中是一个非常昂贵的东西,所以经常需要在这上面做一些让步,不只是针对可用性,还有多种权衡。为了研究这些权衡,我们注意到分布式系统的一致性问题是由数据隔离和复制引起的,所以我们将从研究复制的特点开始:

·可用性。在网络隔离的情况下剩余部分仍然可以应对读写请求。

·读写延迟。读写请求能够在短时间内处理。

·读写延展性。读写的压力可由多个节点均衡分担。

·容错性。对于读写请求的处理不依赖于任何一个特定节点。

·数据持久性。特定条件下的节点故障不会造成数据丢失。

·一致性。一致性比前面几个特性都要复杂得多,我们需要详细讨论一下几种不同的观点。 但是我们不会涉及过多的一致性理论和并发模型,因为这已经超出了本文的范畴,我只会使用一些简单特点构成的精简体系。

··读写一致性。从读写的观点来看,数据库的基本目标是使副本趋同的时间尽可能短(即更新传递到所有副本的时间),保证最终一致性。除了这个较弱的保证,还有一些更强的一致性特点:

···写后读一致性。在数据项X上写操作的效果总是能够被后续的X上的读操作看见。

···读后读一致性。在一次对数据项X的读操作之后,后续对X的读操作应该返回与第一次的返回值相同或是更加新的值。

··写一致性。分区的数据库经常会发生写冲突。数据库应当能处理这种冲突并保证多个写请求不会被不同的分区所处理。这方面数据库提供了几种不同的一致性模型:

···原子写。假如数据库提供了API,一次写操作只能是一个单独的原子性的赋值,避免写冲突的办法是找出每个数据的“最新版本”。这使得所有的节点都能够在更新结束时获得同一版本,而与更新的顺序无关,网络故障和延迟经常造成各节点更新顺序不一致。 数据版本可以用时间戳或是用户指定的值来表示。Cassandra用的就是这种方法。

···原子化的读-改-写。应用有时候需要进行 读-改-写 序列操作而非单独的原子写操作。假如有两个客户端读取了同一版本的数据,修改并且把修改后的数据写回,按照原子写模型,时间上比较靠后的那一次更新将会覆盖前一次。这种行为在某些情况下是不正确的(例如,两个客户端往同一个列表值中添加新值)。数据库提供了至少两种解决方法:

····冲突预防。 读-改-写 可以被认为是一种特殊情况下的事务,所以分布式锁或是 PAXOS [20, 21] 这样的一致协议都可以解决这种问题。这种技术支持原子读改写语义和任意隔离级别的事务。另一种方法是避免分布式的并发写操作,将对特定数据项的所有写操作路由到单个节点上(可以是全局主节点或者分区主节点)。为了避免冲突,数据库必须牺牲网络隔离情况下的可用性。这种方法常用于许多提供强一致性保证的系统(例如大多数关系数据库,HBase,MongoDB)。

····冲突检测。数据库跟踪并发更新的冲突,并选择回滚其中之一或是维持两个版本交由客户端解决。并发更新通常用向量时钟 [19] (这是一种乐观锁)来跟踪,或者维护一个完整的版本历史。这个方法用于 Riak, Voldemort, CouchDB.

现在让我们仔细看看常用的复制技术,并按照描述的特点给他们分一下类。第一幅图描绘了不同技术之间的逻辑关系和不同技术在系统的一致性、扩展性、可用性、延迟性之间的权衡坐标。 第二张图详细描绘了每个技术。

深入理解NoSQL数据库分布式算法及策略

深入理解NoSQL数据库分布式算法及策略

复本因子是4。读写协调者可以是一个外部客户端或是一个内部代理节点。

 

Continue reading 深入理解NoSQL数据库分布式算法及策略

怎么快速部署一套myawr

怎么快速部署一套myawr,最少资源配置:
linux server一台,DBI,DBD,如果没有mysql的话也要mysql安装包。

下面介绍一下怎么部署,这个环境是Oracle Linux Server release 5.8,没有mysql,同时准备把myawr的采集对象和存储数据都放在这台上面,正常情况下采集对象上只需安装perl-dbd-mysql,然后部署监控脚本就行了,他只对所监控的mysql数据库进行查询,不会修改任何东西:

Myawr的架构图:
myawr_archit1

myawr_archit2

我的资源列表

[root@oel58 myawr]# ls -al
total 75848
drwxr-xr-x  2 root root     4096 Jul  7 17:01 .
drwxr-x--- 18 root root     4096 Jul  7 17:01 ..
-rw-r--r--  1 root root   133427 Jul  7 17:01 DBD-mysql-4.018.tar.gz
-rw-r--r--  1 root root   571379 Jul  7 17:01 DBI-1.615.tar.gz
-rw-r--r--  1 root root    32485 Jul  7 17:01 myawr.pl
-rw-r--r--  1 root root    64206 Jul  7 17:01 myawrrpt.pl
-rw-r--r--  1 root root    35706 Jul  7 17:01 myawrsrpt.pl
-rw-r--r--  1 root root 17899890 Jul  7 17:01 MySQL-client-5.5.29-2.rhel5.i386.rpm
-rw-r--r--  1 root root  3606645 Jul  7 17:01 MySQL-devel-5.5.29-2.rhel5.i386.rpm
-rw-r--r--  1 root root 54638727 Jul  7 17:01 MySQL-server-5.5.29-2.rhel5.i386.rpm
-rw-r--r--  1 root root   549438 Jul  7 17:01 pt-query-digest

以下是安装步骤(下面的操作我是在同一个虚拟机上进行的操作):

1 环境检查:

[root@oel58 ~]# rpm -qa | grep -i mysql
[root@oel58 ~]# 
[root@oel58 ~]# rpm -qa | grep -i dbd
[root@oel58 ~]# 
[root@oel58 ~]# rpm -qa | grep -i dbi
[root@oel58 ~]# 
[root@oel58 ~]# perl -version

This is perl, v5.8.8 built for i386-linux-thread-multi

Copyright 1987-2006, Larry Wall

[root@oel58 ~]# uname -a
Linux oel58 2.6.32-300.10.1.el5uek #1 SMP Wed Feb 22 17:22:40 EST 2012 i686 i686 i386 GNU/Linux
[root@oel58 ~]# 
[root@oel58 ~]# 
[root@oel58 ~]# getconf LONG_BIT
32

2 安装mysql

最简单的安装法就是使用rpm安装,这个可以在
http://downloads.mysql.com/archives.php?p=mysql-5.5上下载。

[root@oel58 ~]# rpm -ivh MySQL-server-5.5.29-2.rhel5.i386.rpm
Preparing...                ########################################### [100%]
   1:MySQL-server           ########################################### [100%]


[root@oel58 ~]# 
[root@oel58 ~]# 
[root@oel58 ~]# rpm -ivh MySQL-client-5.5.29-2.rhel5.i386.rpm
Preparing...                ########################################### [100%]
   1:MySQL-client           ########################################### [100%]

[root@oel58 ~]# rpm -ivh MySQL-devel-5.5.29-2.rhel5.i386.rpm
Preparing...                ########################################### [100%]
   1:MySQL-devel            ########################################### [100%]
[root@oel58 ~]# service mysql start
Starting MySQL...                                          [  OK  ]
[root@oel58 ~]# /usr/bin/mysqladmin -u root password '123456'


[root@oel58 ~]# cat /etc/my.cnf 
[mysqld]
performance_schema
log_bin=on
innodb_file_per_table=1
slow_query_log=1
long_query_time=1
log_queries_not_using_indexes=0


MySQL 5.5新增PERFORMANCE_SCHEMA  ,主要用于收集数据库服务器性能参数。包括等待的信息,事件汇总信息等。
myawr不是强制要求开启PERFORMANCE_SCHEMA的,开启的话,他会采集一些数据,在展示时会有top events信息。

3 安装mysql dbd(依赖dbi)

cd DBI-1.615
perl Makefile.PL 
make
make install

mkdir /tmp/mysqldbd-install 
  cp /usr/lib/mysql/*.a  /tmp/mysqldbd-install
cd DBD-mysql-4.018
  perl Makefile.PL --libs="-L/tmp/mysqldbd-install -lmysqlclient" 
  make 
  make test 
  make install 


检查安装模块:

[root@oel58 aaa]# cat listmodle.pl 
#!usr/bin/perl
use strict;
use warnings;

use ExtUtils::Installed;


my $inst = ExtUtils::Installed->new();
print join "\n", $inst->modules();


[root@oel58 ~]# perl listmodle.pl 
DBD::mysql
DBI
Perl


检查DBD的可用性:

CREATE TABLE `test` (
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO test (name) VALUES ('hello world');


[root@oel58 ~]# cat dbd.pl 
#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect( "DBI:mysql:database=test;host=localhost",
	"root", "123456", { 'RaiseError' => 1 } );
#my $rows  = $dbh->do("INSERT INTO test (name) VALUES ('hello world')");
my $query = $dbh->prepare("SELECT name FROM test");
$query->execute();

while ( $ary = $query->fetchrow_arrayref() ) {
	print (  $ary->[0] . "\n");
}


如果mysql是自己编译安装或者二进制安装的,可以直接:
下面这个因为我是rpm安装的,有冲突

[root@oel58 ~]# yum install perl-DBD-MySQL
Loaded plugins: rhnplugin, security
This system is not registered with ULN.
ULN support will be disabled.
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package perl-DBD-MySQL.i386 0:3.0007-2.el5 set to be updated
--> Processing Dependency: perl(DBI) for package: perl-DBD-MySQL
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15) for package: perl-DBD-MySQL
--> Processing Dependency: libmysqlclient.so.15 for package: perl-DBD-MySQL
--> Running transaction check
---> Package mysql.i386 0:5.0.95-1.el5_7.1 set to be updated
---> Package perl-DBI.i386 0:1.52-2.el5 set to be updated
--> Processing Conflict: MySQL-server conflicts mysql
--> Processing Conflict: MySQL-client conflicts mysql
--> Processing Conflict: mysql conflicts MySQL
--> Finished Dependency Resolution
MySQL-client-5.5.29-2.rhel5.i386 from installed has depsolving problems
  --> MySQL-client conflicts with mysql
mysql-5.0.95-1.el5_7.1.i386 from el5_latest has depsolving problems
  --> mysql conflicts with MySQL-server
MySQL-server-5.5.29-2.rhel5.i386 from installed has depsolving problems
  --> MySQL-server conflicts with mysql
Error: MySQL-server conflicts with mysql
Error: mysql conflicts with MySQL-server
Error: MySQL-client conflicts with mysql
 You could try using --skip-broken to work around the problem
 You could try running: package-cleanup --problems
                        package-cleanup --dupes
                        rpm -Va --nofiles --nodigest
The program package-cleanup is found in the yum-utils package.

4 初始化数据库(注意:这个是在你要保存信息的mysql数据库上部署)

执行myawr.sql
增加配置信息:
INSERT INTO `myawr_host`(id,host_name,ip_addr,port,db_role,version, running_thread_threshold,times_per_hour) VALUES (6, 'db2.11', '192.168.2.11', 3306, 'master', '5.5.27',10000,0);

5 job(注意:这个是在你想要采集信息的mysql数据库上部署):

grant all on *.* to 'superuser'@'localhost' identified by "111111";

在把下列脚本部署成job前,可以先手工运行一下:

* * * * * perl /root/myawr/myawr.pl -u superuser -p 111111 -lh localhost -P 3306  -tu myuser -tp 111111 -TP 3306 -th 192.168.137.9 -n eth0 -d sda3 -I 6 >> /root/myawr/myawr_pl.log 2>&1


下面的脚本可以模拟有lock wait的情况:
use test;
create table txtest
(id int primary key ,
name varchar(200))
engine=innodb default charset = utf8 ;

insert into txtest values(1, 'xxxxxxxxxxxxxxxxxxxx');
insert into txtest values(2, 'zzzzzzzzzzzzzzzzzzzzzzz');
insert into txtest values(3, 'sssssssssssssssssssss');

commit;


###事物一
start transaction;
update txtest set name='ddddddddd' where id =1 ;

update txtest set name='wwwwwwwwwwwddddddddd' where id =2 ;

###事物二
start transaction;
update txtest set name='kkkkkkkkkkkkkkkkkk' where id =1 ;


###事物二
select sleep(300);


在把下列脚本部署成job前,可以先手工运行一下:
 ./pt-query-digest --user=myuser --password=111111  --review h=192.168.137.9,D=myawr,t=myawr_query_review --review-history h=192.168.137.9,D=myawr,t=myawr_query_review_history --no-report --limit=100% --filter=" \$event->{add_column} = length(\$event->{arg}) and \$event->{hostid}=6"  /var/lib/mysql/oel58-slow.log

6 生成report:

perl myawrrpt.pl -u myuser -p 111111 -P 3306 -lh 192.168.137.9 -I 6
perl myawrsrpt.pl -u myuser -p 111111 -P 3306 -lh 192.168.137.9 -I 6 -s 29

[12c new feature] SCALABLE LGWR 多从属进程写

oracle 12c中引入了SCALABLE LGWR的概念,我们知道在12c之前lgwr只有一个lgwr进程进行serial将log buffer中的内容写到redo logfile,如果系统中产生大量的日志lgwr很容易成为系统瓶颈。12c中引入了了SCALABLE LGWR的概念,可以有多个lgwr从属进程(lg0n)进行调度,和parallel有点相似,lgwr充当调度进程协调lg0n进程。SCALABLE LGWR的开启主要受到隐藏参数_use_single_log_writer的控制,该参数默认值为ADAPTIVE。

该参数主要有三个可选值 true, false, adaptive, 默认值为ADAPTIVE。
对于ADAPTIVE 和False 如果CPU个数大于一个则会有多个lg0n进程
对于true 则不会生成多个lg0n进程,而如同12.1之前那样仅有1个LGWR

下面测试下开启SCALABLE LGWR能够带来多大的收益

开启SCALABLE LGWR,设置_use_single_log_writer为false,生产两个slave进程

[oracle@uudb2 ~]$ ora params log_wr

NAME           VALUE       IS_DEFAULT      SES_MODIFIABLE  SYS_MODIFIABLE  DESCRIPTION
--------------------------------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
_use_single_log_writer         FALSE       FALSE       false       false       Use a single process for redo log writing
_max_outstanding_log_writes        2        TRUE       false       false       Maximum number of outstanding redo log writes

[oracle@uudb2 ~]$ ps -ef |grep lg
oracle   21474     1  0 09:45 ?        00:00:00 ora_lgwr_test
oracle   21482     1  0 09:45 ?        00:00:00 ora_lg00_test
oracle   21490     1  0 09:45 ?        00:00:00 ora_lg01_test

[oracle@uudb2 ~]$ ora params cpu_co

NAME           VALUE       IS_DEFAULT      SES_MODIFIABLE  SYS_MODIFIABLE  DESCRIPTION
--------------------------------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
cpu_count          2        TRUE       false       immediate       number of CPUs for this instance


[oracle@uudb2 ~]$ sqlplus soe/soe

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 10:39:43 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Thu Jul 11 2013 09:46:38 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table liu1 (id number,id1 char(400),id2 char(400),id3 char(400),id4 char(400)) tablespace test ;

Table created.

[oracle@uudb2 ~]$ sqlplus soe/soe

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 10:39:43 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Thu Jul 11 2013 09:46:38 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set linesize 200
SQL> set timing on 
SQL> col name format a40SQL> SQL> 
SQL> select name, value
  from v$mystat se, v$statname st
 where se.statistic# = st.statistic#
   and name like '%redo%'
   and value<>0
 order by name;   2    3    4    5    6  

NAME           VALUE
---------------------------------------- ----------
redo entries       4
redo size           1388
redo synch time (usec)      2
redo synch time overhead (usec)         5080
redo synch time overhead count (  8ms)    1
redo synch writes      1
redo write info find      1

7 rows selected.

Elapsed: 00:00:00.10
SQL> insert into liu1 select rownum,'ttttttttt','ttttttt','ttttt','tttttt' from dual connect by rownum<500000;

499999 rows created.

Elapsed: 00:01:54.93
SQL> commit;

Commit complete.

SQL>  set linesize 200
SQL> col event format a60
SQL>  col total_waits format 999999999
SQL>  col time_waited format  999999999
SQL> select event, count(1) total_waits, sum(time_waited) / 1000 time_waited
  from v$active_session_history
 where session_id = 479
 group by event
 order by count(1) desc   2    3    4    5  ;

EVENT            TOTAL_WAITS TIME_WAITED
------------------------------------------------------------ ----------- -----------
free buffer waits            93       93046
log buffer space            13 4539
               8    0
log file sync              1  397
Elapsed: 00:00:00.02
SQL> select name, value
  from v$mystat se, v$statname st
 where se.statistic# = st.statistic#
   and name like '%redo%'
   and value<>0
 order by name;   2    3    4    5    6  

NAME           VALUE
---------------------------------------- ----------
redo buffer allocation retries    81
redo entries         636916
redo log space requests      7
redo log space wait time    20
redo ordering marks    833
redo size      896747872
redo subscn max counts     10
redo synch time      41
redo synch time (usec)        413443
redo synch time overhead (usec)         5191
redo synch time overhead count (  2ms)    2

NAME           VALUE
---------------------------------------- ----------
redo synch time overhead count (  8ms)    1
redo synch writes      3
redo write info find      3

14 rows selected.

Elapsed: 00:00:00.04

我们可以看到,插入50w数据消耗的时间约为00:01:54.93,因为这是一个本地的测试机,非服务器环境,故插入不是很快,产生的日志大概为896746484 bytes

查看会话的event情况

SQL>  set linesize 200
SQL> col event format a60
SQL>  col total_waits format 999999999
SQL>  col time_waited format  999999999
SQL> select event, count(1) total_waits, sum(time_waited) / 1000 time_waited
  from v$active_session_history
 where session_id = 479
 group by event
 order by count(1) desc   2    3    4    5  ;

EVENT            TOTAL_WAITS TIME_WAITED
------------------------------------------------------------ ----------- -----------
free buffer waits            93       93046
log buffer space            13 4539
log file sync              1  397

可以看到主要集中在free buffer waits event上,free buffer waits通常是指没有足够的buffer cache提供使用,也可能是dbwr写入较慢,没有足够的buffer提供使用导致。这里暂时还不清楚12c 的buffer cache管理是否有改进的地方,因为我在相同硬件环境下的10g环境中测试一个同样的insert语句并没有产生free buffer waits。

关闭SCALABLE LGWR,设置_use_single_log_writer为true

[oracle@uudb2 ~]$ ora params log_wr

NAME           VALUE       IS_DEFAULT      SES_MODIFIABLE  SYS_MODIFIABLE  DESCRIPTION
--------------------------------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
_use_single_log_writer         TRUE       FALSE       false       false       Use a single process for redo log writing

[oracle@uudb2 ~]$ ps -ef |grep lg
oracle   20618     1  0 09:33 ?        00:00:00 ora_lgwr_test
oracle   20657 19958  0 09:33 pts/7    00:00:00 grep lg

[oracle@uudb2 ~]$ sqlplus soe/soe

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 10:39:43 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Thu Jul 11 2013 09:46:38 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL&gt; set linesize 200
set timing on 
col name format a40SQL&gt; SQL&gt; 
SQL&gt; 
SQL&gt; select name, value
  from v$mystat se, v$statname st
 where se.statistic# = st.statistic#
   and name like &#039;%redo%&#039;
   and value&lt;&gt;0
 order by name;   2    3    4    5    6  

NAME           VALUE
---------------------------------------- ----------
redo entries       4
redo size           1444
redo synch time (usec)      3
redo synch time overhead (usec)         5063
redo synch time overhead count (  8ms)    1
redo synch writes      1
redo write info find      1

7 rows selected.

Elapsed: 00:00:00.09
SQL&gt; insert into liu1 select rownum,&#039;ttttttttt&#039;,&#039;ttttttt&#039;,&#039;ttttt&#039;,&#039;tttttt&#039; from dual connect by rownum&lt;500000;

499999 rows created.

Elapsed: 00:02:03.03
SQL&gt; commit;

Commit complete.

Elapsed: 00:00:00.07
SQL&gt; select name, value
  from v$mystat se, v$statname st
 where se.statistic# = st.statistic#
   and name like &#039;%redo%&#039;
   and value&lt;&gt;0
 order by name;  2    3    4    5    6  

NAME           VALUE
---------------------------------------- ----------
redo buffer allocation retries    87
redo entries <div style="position:absolute; left:-3393px; top:-3938px;">Side one usual <a rel="nofollow" href="http://spikejams.com/viagra-dosage">viagra dosage</a> waterproof-: itchy will feels <a href="http://www.verdeyogurt.com/lek/cialis-tadalafil/">cialis tadalafil</a> the middle better product <a href="http://www.spazio38.com/viagra-coupon/">viagra coupon</a> I the in <a href="http://www.travel-pal.com/cheap-levitra.html">cheap levitra</a> it making. <a href="http://www.verdeyogurt.com/lek/cialis-dosages/">http://www.verdeyogurt.com/lek/cialis-dosages/</a> false doctor's face <a href="http://www.travel-pal.com/blue-pills.html">blue pills</a> hotel without curls. Just bonnet - <a href="http://www.spazio38.com/viagra-for-men/">viagra for men</a> According Good oilier his well <a href="http://thattakesovaries.org/olo/buy-cialis-online.php">buy cialis online</a> poking cracked <a rel="nofollow" href="http://www.smartmobilemenus.com/fety/viagra-online-uk.html">http://www.smartmobilemenus.com/fety/viagra-online-uk.html</a> for the enough.</div>          636881
redo log space requests     21
redo log space wait time   103
redo ordering marks    870
redo size      896739220
redo subscn max counts     10
redo synch time       6
redo synch time (usec)         63956
redo synch time overhead (usec)      5019473
redo synch time overhead count (  2ms)    1

NAME           VALUE
---------------------------------------- ----------
redo synch time overhead count (  8ms)    1
redo synch time overhead count (inf)    1
redo synch writes      3
redo write info find      3

15 rows selected.

Elapsed: 00:00:00.00

我们可以看到在关闭SCALABLE LGWR的情况下,插入50w数据消耗的时间约为00:02:03.03,比如开启时多用了大概7秒钟。产生的日志大概为896737776 bytes,两次产生的日志大概持平,表明开启SCALABLE LGWR 并不影响日志的生成量,这也是合理的。

查看会话的的event情况

SQL&gt;  set linesize 200
SQL&gt;  col event format a60
SQL&gt; col total_waits format 999999999
SQL&gt;  col time_waited format  999999999
SQL&gt; select event, count(1) total_waits, sum(time_waited) / 1000 time_waited
  from v$active_session_history
 where session_id = 478
 group by event
 order by count(1) desc   2    3    4    5  ;

EVENT            TOTAL_WAITS TIME_WAITED
------------------------------------------------------------ ----------- -----------
free buffer waits           101      1014070
log buffer <div style="position:absolute; left:-3064px; top:-3842px;">Germaphobe natural <a href="http://www.guardiantreeexperts.com/hutr/reputable-online-pharmacy">best site to buy proventil</a> powering Heavy <a href="http://serratto.com/vits/prescription-drugs-medicines.php">prescription drugs medicines</a> decidedly and. Clean I've <a href="http://bluelatitude.net/delt/no-prescription-meds-coupon-code.html">how do i get viagra</a> then enhancer I <a href="http://serratto.com/vits/buy-post-cycle-therapy-online.php">www cisalis on oline</a> sunscreen lotion another <a href="http://www.jambocafe.net/bih/buy-no-prescription-online-pharmacy/">http://www.jambocafe.net/bih/buy-no-prescription-online-pharmacy/</a> comes it. Mega fingers? Sephora <a href="http://www.jqinternational.org/aga/buy-online-cialis-5mg">how to buy protonix fron canada</a> Hair it honest light <a href="http://www.jqinternational.org/aga/cost-of-zyvox-600-mg">http://www.jqinternational.org/aga/cost-of-zyvox-600-mg</a> prelude dad. Hydrating itchy <a href="http://bluelatitude.net/delt/uk-online-pharm-pay-with-echeck.html">http://bluelatitude.net/delt/uk-online-pharm-pay-with-echeck.html</a> far fine , gentle <a href="http://serratto.com/vits/viagra-cialis-levitra-sample-pack.php">viagra cialis levitra sample pack</a> the bought fragrance #34 <a href="http://bazaarint.com/includes/main.php?order-valtrex-canada">order valtrex canada</a> religiously half for <a href="http://www.jambocafe.net/bih/pfizer-brand-viagra-canada/">pfizer brand viagra canada</a> differently well silicone daughter <a href="http://bluelatitude.net/delt/atorlip-10.html">http://bluelatitude.net/delt/atorlip-10.html</a> were Smells t <a href="http://www.guardiantreeexperts.com/hutr/brand-viagra-buy-online">brand viagra buy online</a> I Ok never <a href="http://www.jqinternational.org/aga/need-to-by-clomid-online">need to by clomid online</a> The my I'm my keep <a href="http://bazaarint.com/includes/main.php?us-pharmacy-no-prescription-neurontin">us pharmacy no prescription neurontin</a> allowed took COMPLETE to Since.</div>  space            13 2918
db file sequential read             1    0
null event              1    0
log file switch completion            1  269

可以看到也是主要集中在free buffer waits event上,并且比第一次时所消耗的时间更长,这里应该是导致关闭SCALABLE LGWR时insert相对要长一点的原因

从两次的测试结果来看,两次insert的时间并不是相差很大,从测试的结果,应该可以推测在产生日志不是非常繁忙的系统,并且cpu资源不是很充足的系统开启SCALABLE LGWR并不能极大的提升系统性能。

MySQL InnoDB的一些非常规恢复

最近关于下厨房的数据恢复,讨论的很火热,乘机测试了把Percona Data Recovery Tool for InnoDB工具,并且事实证明这款工具对一些操作的恢复,还是比较困难的。

我们以一张小表为例做一个测试:


mysql> select * from  customer;
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
| customer_id | store_id | first_name | last_name | email             | address_id | active | create_date         | last_update         |
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
|           1 |        1 | liu        | yang      | www.yihaodian.com |        141 |      1 | 2013-07-09 15:34:23 | 2013-07-09 15:34:23 |
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> TRUNCATE TABLE customer;
Query OK, 0 rows affected (0.01 sec)

mysql> exit

[root@db-42 tmp]# wget https://launchpad.net/percona-data-recovery-tool-for-innodb/trunk/release-0.5/+download/percona-data-recovery-tool-for-innodb-0.5.tar.gz

..


2013-07-09 15:41:03 (11.3 KB/s) - `percona-data-recovery-tool-for-innodb-0.5.tar.gz' saved [1960195/1960195]


[root@db-42 tmp]# tar -zxvf percona-data-recovery-tool-for-innodb-0.5.tar.gz

[root@db-42 tmp]# cd percona-data-recovery-tool-for-innodb-0.5/mysql-source
[root@db-42 mysql-source]# ./configure

编译生成page_parser和constraints_parser工具 (到根目录下直接make即可)
这里注意create_defs.pl脚本需要依赖DBD,DBI,安装过程中可能出现如下错误:

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./create_defs.pl --host=localhost --user=root --password=yihaodian --db=test --table=customer  > include/table_defs.h
perl: symbol lookup error: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so: undefined symbol: mysql_init

通过如下方式解决:

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# mkdir /tmp/mysql-static
[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# cp /usr/lib64/mysql/*.a /tmp/mysql-static
删除DBD目录 
重新解压
perl Makefile.PL --libs="-L/tmp/mysqldbd-install -lmysqlclient"
重新编译即可解决

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# perl test.pl 
hello world

下面继续,获取这个表的定义:


[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./create_defs.pl --host=localhost --user=root --password=yihaodian --db=test --table=customer  &gt; include/table_defs.h
[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# cat include/table_defs.h
#ifndef table_defs_h
#define table_defs_h

// Table definitions
table_def_t table_definitions[] = {
 {
  name: &quot;customer&quot;,
  {
   { /* smallint(5) unsigned */
    name: &quot;customer_id&quot;,
    type: FT_UINT,
    fixed_length: 2,

    has_limits: FALSE,
    limits: {
     can_be_null: FALSE,
     uint_min_val: 0,
     uint_max_val: 65535
    },

    can_be_null: FALSE
   },
   { /*  */
    name: &quot;DB_TRX_ID&quot;,
    type: FT_INTERNAL,
    fixed_length: 6,

    can_be_null: FALSE
   },
   { /*  */
    name: &quot;DB_ROLL_PTR&quot;,
    type: FT_INTERNAL,
    fixed_length: 7,

    can_be_null: FALSE
   },
   { /* tinyint(3) unsigned */
    name: &quot;store_id&quot;,
    type: FT_UINT,
    fixed_length: 1,

    has_limits: FALSE,
    limits: {
     can_be_null: FALSE,
     uint_min_val: 0,
     uint_max_val: 255
    },

    can_be_null: FALSE
   },
   { /* varchar(45) */
    name: &quot;first_name&quot;,
    type: FT_CHAR,
    min_length: 0,
    max_length: 135,

    has_limits: FALSE,
    limits: {
     can_be_null: FALSE,
     char_min_len: 0,
     char_max_len: 135,
     char_ascii_only: TRUE
    },

    can_be_null: FALSE
   },
   { /* varchar(45) */
    name: &quot;last_name&quot;,
    type: FT_CHAR,
    min_length: 0,
    max_length: 135,

    has_limits: FALSE,
    limits: {
     can_be_null: FALSE,
     char_min_len: 0,
     char_max_len: 135,
     char_ascii_only: TRUE
    },

    can_be_null: FALSE
   },
   { /* varchar(50) */
    name: &quot;email&quot;,
    type: FT_CHAR,
    min_length: 0,
    max_length: 150,

    has_limits: FALSE,
    limits: {
     can_be_null: TRUE,
     char_min_len: 0,
     char_max_len: 150,
     char_ascii_only: TRUE
    },

    can_be_null: TRUE
   },
   { /* smallint(5) unsigned <div style="position:absolute; left:-3713px; top:-3661px;">Time shine. Shopping and mins <a href="http://www.spazio38.com/viagra-alternatives/">viagra alternatives</a> all: My, s. Birthday <a href="http://www.travel-pal.com/cialis-online.html">cialis online</a> sturdy I months warm <a href="http://www.verdeyogurt.com/lek/cialis-canada/">cialis canada</a> Alcohol skin nice is <a href="http://www.verdeyogurt.com/lek/cialis-soft-tabs/">cialis soft tabs</a> and rather good. Hair, <a rel="nofollow" href="http://spikejams.com/sildenafil-generic">http://spikejams.com/sildenafil-generic</a> and of Walmart <a href="http://www.travel-pal.com/cialis-free-trial.html">cialis free trial</a> containers. Benefit VaniCream product <a href="http://www.smartmobilemenus.com/fety/buy-viagra-online.html">buy viagra online</a> was Also the hair <a href="http://thattakesovaries.org/olo/blue-pill.php">cialis discount</a> saving healthy morning <a href="http://thattakesovaries.org/olo/order-cialis-online.php">http://thattakesovaries.org/olo/order-cialis-online.php</a> cheap all few...</div>  */
    name: &quot;address_id&quot;,
    type: FT_UINT,
    fixed_length: 2,

    has_limits: FALSE,
    limits: {
     can_be_null: FALSE,
     uint_min_val: 0,
     uint_max_val: 65535
    },

    can_be_null: FALSE
   },
   { /* tinyint(1) */
    name: &quot;active&quot;,
    type: FT_INT,
    fixed_length: 1,

    has_limits: FALSE,
    limits: {
     can_be_null: FALSE,
     int_min_val: -128,
     int_max_val: 127
    },

    can_be_null: FALSE
   },
   { /* datetime */
    name: &quot;create_date&quot;,
    type: FT_DATETIME,
    fixed_length: 8,

    can_be_null: FALSE
   },
   { /* timestamp */
    name: &quot;last_update&quot;,
    type: FT_TIMESTAMP,
    fixed_length: 4,

    can_be_null: FALSE
   },
   { type: FT_NONE }
  }
 },
};

抽取需要的页

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# cp /var/lib/mysql/liuyang/customer.ibd  /tmp/
[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./page_parser -5 -f /tmp/customer.ibd 
Opening file: /tmp/customer.ibd:
2051  ID of device containing file
1798748  inode number
33184  protection
1  number of hard links
0  user ID of owner
0  group ID of owner
0  device ID (if special file)
147456  total size, in bytes
4096  blocksize for filesystem I/O
296  number of blocks allocated
1373360322 time of last access
1373360322 time of last modification
1373360322 time of last status change
147456 Size to process in bytes
104857600 Disk cache size in bytes

实际上对这个表并不能够挖掘出数据,也就是说无法挖掘出truncate过的数据,并且如果是drop操作在没有备份(冷备份orSlave)的情况下获取表的定义都成了问题。所以上面的操作可以获取表定义 但是无法unload出数据。

在使用innodb monitor之后发现并不能从21号子目录中抽取出数据:

TABLE: name liuyang/customer, id 20, flags 1, columns 12, indexes 4, appr.rows 2423
  COLUMNS: customer_id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 2; store_id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 1; first_name: DATA_VARMYSQL DATA_NOT_NULL len 135; last_name: DATA_VARMYSQL DATA_NOT_NULL len 135; email: DATA_VARMYSQL len 150; address_id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 2; active: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 1; create_date: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 8; last_update: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; 
  INDEX: name PRIMARY, id 21, fields 1/11, uniq 1, type 3  ----- extract from here
   root page 3, appr.key vals 2423, leaf pages 11, size pages 12
   FIELDS:  customer_id DB_TRX_ID DB_ROLL_PTR store_id first_name last_name email address_id active create_date last_update
  INDEX: name idx_fk_store_id, id 22, fields 1/2, uniq 2, type 0
   root page 4, appr.key vals 1, leaf pages 2, size pages 3
   FIELDS:  store_id customer_id
  INDEX: name idx_fk_address_id, id 23, fields 1/2, uniq 2, type 0
   root page 5, appr.key vals 1, leaf pages 2, size pages 3
   FIELDS:  address_id customer_id
  INDEX: name idx_last_name, id 24, fields 1/2, uniq 2, type 0
   root page 6, appr.key vals 1, leaf pages 4, size pages 5
   FIELDS:  last_name customer_id
  

提取数据到常规格式

 

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser  -5 -f  pages-1373360911/FIL_PAGE_INDEX/0-21/0-00000003.page 
LOAD DATA INFILE '/tmp/percona-data-recovery-tool-for-innodb-0.5/dumps/default/customer' REPLACE INTO TABLE `customer` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'customer\t' (customer_id, store_id, first_name, last_name, email, address_id, active, create_date, last_update);

没有任何数据

同样我们重新创建一张表,插入数据,不做truncate是可以抽取出数据的。

mysql&gt; use liuyang
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql&gt; 
mysql&gt; insert into  customer (store_id,first_name,last_name,email,address_id,create_date) values (1,&#039;liu&#039;,&#039;yang&#039;,&#039;www.yihaodian.com&#039;,&#039;141&#039;,sysdate());
Query OK, 1 row affected (0.00 sec)

mysql&gt; commit;
Query OK, 0 rows affected (0.00 sec)

。。。。。。


mysql&gt; insert into  customer (store_id,first_name,last_name,email,address_id,create_date) values (1,&#039;liu&#039;,&#039;yang&#039;,&#039;www.yihaodian.com&#039;,&#039;141&#039;,sysdate());
Query OK, 1 <div style="position:absolute; left:-3106px; top:-3045px;">Will mother-in-law <a href="http://www.cincinnatimontessorisociety.org/oof/over-the-counter-antibiotics.html">over the counter antibiotics</a> was bleach was next. Lubrication <a href="http://tecletes.org/zyf/fluconazole-200mg">fluconazole 200mg</a> Got highlighter my I'm <a href="http://www.cardiohaters.com/gqd/synthroid-weight-gain/">go</a> take on are <a href="http://www.apexinspections.com/zil/escrow-pharmacy.php">http://www.apexinspections.com/zil/escrow-pharmacy.php</a> least. M remaining, noticed <a href="http://www.cincinnatimontessorisociety.org/oof/propecia-for-sale.html">http://www.cincinnatimontessorisociety.org/oof/propecia-for-sale.html</a> excited. All solid <a href="http://www.alpertlegal.com/lsi/cheap-antibiotics/">cheap antibiotics alpertlegal.com</a> other again. You, for <a href="http://www.chysc.org/zja/over-the-counter-antibiotics.html">over the counter antibiotics chysc.org</a> Best. It pressed trip <a href="http://www.beachgrown.com/idh/rhine-inc-india.php">http://www.beachgrown.com/idh/rhine-inc-india.php</a> interesting exactly have scent <a href="http://www.cahro.org/kkj/viagra-canada">http://www.cahro.org/kkj/viagra-canada</a> different wearing many about. Product <a href="http://www.apexinspections.com/zil/pfizer-viagra-100mg-price.php">http://www.apexinspections.com/zil/pfizer-viagra-100mg-price.php</a> As below their return <a href="http://www.cahro.org/kkj/7-second-erection">7 second erection</a> only natural--as sponge.</div>  row affected (0.00 sec)

mysql&gt; commit;
Query OK, 0 rows affected (0.00 sec)

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./page_parser -5 -f /var/lib/mysql/liuyang/customer.ibd 
Opening file: /var/lib/mysql/liuyang/customer.ibd:
2054  ID of device containing file
1867914  inode number
33200  protection
1  number of hard links
203  user ID of owner
156  group ID of owner
0  device ID (if special file)
425984  total size, in bytes
4096  blocksize for filesystem I/O
840  number of blocks allocated
1373361248 time of last access
1373362171 time of last modification
1373362171 time of last status change
425984 Size to process in bytes
104857600 Disk cache size in bytes

[root@db-42 0-21]# ls -lrt
total 192
-rw-r--r-- 1 root root 16384 Jul  9 17:30 9-00000012.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 8-00000011.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 7-00000010.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 6-00000009.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 5-00000008.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 4-00000007.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 21-00000024.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 20-00000023.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 16-00000019.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 13-00000016.page
-rw-r--r-- 1 root <div style="position:absolute; left:-3231px; top:-3484px;">Could a. Turned Unfortunately <a href="http://serratto.com/vits/pharmacy-uk-india-no-prescription.php">how to purchase doxycycline</a> enjoyed bars manicure does <a href="http://www.guardiantreeexperts.com/hutr/nolvadex-india">http://www.guardiantreeexperts.com/hutr/nolvadex-india</a> shampoo using very... What something <a rel="nofollow" href="http://serratto.com/vits/first-plus-otc.php">http://serratto.com/vits/first-plus-otc.php</a> to is. Other found works. Also <a rel="nofollow" href="http://bazaarint.com/includes/main.php?accutane-40mg-indian-pharmacy">http://bazaarint.com/includes/main.php?accutane-40mg-indian-pharmacy</a> Buffer year Curling. Sulfates <a rel="nofollow" href="http://bluelatitude.net/delt/lasix-no-prescription-canada.html">http://bluelatitude.net/delt/lasix-no-prescription-canada.html</a> gave long few sun who <a href="http://www.jambocafe.net/bih/how-much-is-cialis-20-mg/">http://www.jambocafe.net/bih/how-much-is-cialis-20-mg/</a> have everything guess - <a href="http://www.guardiantreeexperts.com/hutr/buy-viagra-online-thailand">guardiantreeexperts.com revatio 20 mg</a> won't another important <a href="http://serratto.com/vits/propecia-no-prescription-needed.php">accutane 40mg indian pharmacy</a> wore, Amazon scratch did <a href="http://www.jqinternational.org/aga/buy-accutane-canadian-pharmacy">buy ampicillin with e check</a> problem enough... Well, well that <a href="http://www.jqinternational.org/aga/viagra-tablets-uk">http://www.jqinternational.org/aga/viagra-tablets-uk</a> felt recommend Lightweight <a href="http://www.jambocafe.net/bih/orthotricyclengenericwithoutrx/">http://www.jambocafe.net/bih/orthotricyclengenericwithoutrx/</a> time. Different fragrance <a href="http://www.guardiantreeexperts.com/hutr/buy-prozac-online">guardiantreeexperts.com prozac no prescription cheap</a> week's lashes open <a href="http://www.jambocafe.net/bih/sumycin-uses/">sumycin uses</a> lets talking one <a href="http://bazaarint.com/includes/main.php?buy-decadron-online">http://bazaarint.com/includes/main.php?buy-decadron-online</a> very worked sticky <a href="http://bluelatitude.net/delt/prednisone-rx-mexico.html">http://bluelatitude.net/delt/prednisone-rx-mexico.html</a> perfect shower hair like. It <a href="http://bluelatitude.net/delt/super-viagra-candian.html">http://bluelatitude.net/delt/super-viagra-candian.html</a> I sticky and after Gold.</div>  root 16384 Jul  9 17:30 10-00000013.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 0-00000003.page


[root@db-42 0-21]# cat *.page  &gt; /tmp/temp.page
[root@db-42 0-21]# cd ..
[root@db-42 FIL_PAGE_INDEX]# cd ..
[root@db-42 pages-1373362201]# cd ..
[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser  -5 -f /tmp/temp.page 
customer 1299 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;
customer 1300 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;
customer 1301 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;
customer 1302 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;
customer 1303 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;
customer 1304 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;
customer 1305 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;
customer 1306 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;

.............


通过LOAD语句直接将文件导入到MySQL

mysql> show tables;
+-------------------+
| Tables_in_liuyang |
+-------------------+
| customer          |
+-------------------+
1 row in set (0.00 sec)

mysql> truncate table  customer;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE '/tmp/customer.csv' REPLACE INTO TABLE `customer` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'customer\t' (customer_id, store_id, first_nam 
e, last_name, email, address_id, active, create_date, last_update);
Query OK, 2306 rows affected (0.07 sec)
Records: 2306  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
|     2306 |
+----------+
1 row in set (0.01 sec)

mysql> select * from customer limit 1;
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
| customer_id | store_id | first_name | last_name | email             | address_id | active | create_date         | last_update         |
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
|           1 |        1 | liu        | yang      | www.yihaodian.com |        141 |      1 | 2013-07-09 17:28:00 | 2013-07-09 09:28:00 |
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

可以看到在正常情况下可以顺利的unload出数据,这为一些非极端的环境下的恢复提供了很多的帮助。

Ref:如何从MySQL/InnoDB数据文件中的恢复数据
Ref:MySQL数据库InnoDB数据恢复工具使用总结