求助：从库mysqldump 时，mysql 有时会导致重启……
16lz
2021-01-22
从库mysqldump时，mysql有时会导致重启……
数据文件大约200GB，平时备份（mysqldump）压缩后的大小大约20GB
最近，从库用mysqldump备份时，errorlog中出现很多：
InnoDB:Warning:alongsemaphorewait:
--Thread1183631680haswaitedat../../storage/innobase/include/btr0btr.icline28for241.00secondsthesemaphore:
S-lockonRW-latchat0x2aacda28d898createdinfilebuf/buf0buf.cline550
awriter(threadid1183631680)hasreserveditinmodeexclusive
numberofreaders0,waitersflag1
Lasttimereadlockedinfilebtr/btr0pcur.cline246
Lasttimewritelockedinfilebuf/buf0buf.cline1823
InnoDB:######StartsInnoDBMonitorfor30secstoprintdiagnosticinfo:
InnoDB:Pendingpreads1,pwrites0
=====================================
12021710:40:47INNODBMONITOROUTPUT
=====================================
Persecondaveragescalculatedfromthelast27seconds
----------
SEMAPHORES
----------
OSWAITARRAYINFO:reservationcount62196,signalcount62194
--Thread1183631680haswaitedat../../storage/innobase/include/btr0btr.icline28for256.00secondsthesemaphore:
S-lockonRW-latchat0x2aacda28d898createdinfilebuf/buf0buf.cline550
awriter(threadid1183631680)hasreserveditinmodeexclusive
numberofreaders0,waitersflag1
Lasttimereadlockedinfilebtr/btr0pcur.cline246
Lasttimewritelockedinfilebuf/buf0buf.cline1823
Mutexspinwaits0,rounds98353,OSwaits1500
RW-sharedspins118450,OSwaits59162;RW-exclspins1996,OSwaits1404
------------
TRANSACTIONS
------------
Trxidcounter091046918
Purgedonefortrx'sn<091046756undon<00
Historylistlength21
LISTOFTRANSACTIONSFOREACHSESSION:
---TRANSACTION091046818,notstarted,processno4553,OSthreadid1183832384
MySQLthreadid154,queryid294386localhostroot
---TRANSACTION091046875,ACTIVE1300sec,processno4553,OSthreadid1183631680fetchingrows,threaddeclaredinsideInnoDB500
mysqltablesinuse1,locked0
MySQLthreadid134,queryid294200localhostbackupSendingdata
SELECT/*!40001SQL_NO_CACHE*/*FROM`table1`
Trxreadviewwillnotseetrxwithid>=091046876,sees<091046876
--------
FILEI/O
--------
I/Othread0state:waitingfori/orequest(insertbufferthread)
I/Othread1state:waitingfori/orequest(logthread)
I/Othread2state:doingfilei/o(readthread)evset
I/Othread3state:waitingfori/orequest(writethread)
Pendingnormalaioreads:64,aiowrites:0,
ibufaioreads:0,logi/o's:0,synci/o's:0
Pendingflushes(fsync)log:0;bufferpool:0
192399OSfilereads,256617OSfilewrites,7538OSfsyncs
1pendingpreads,0pendingpwrites
0.00reads/s,0avgbytes/read,0.00writes/s,0.00fsyncs/s
-------------------------------------
INSERTBUFFERANDADAPTIVEHASHINDEX
-------------------------------------
Ibuf:size1,freelistlen20875,segsize20877,
53381inserts,53381mergedrecs,31096merges
Hashtablesize18563003,nodeheaphas1buffer(s)
0.00hashsearches/s,0.00non-hashsearches/s
---
LOG
---
Logsequencenumber433634229620
Logflushedupto433634229620
Lastcheckpointat433634229620
0pendinglogwrites,0pendingchkpwrites
125902logi/o'sdone,0.00logi/o's/second
----------------------
BUFFERPOOLANDMEMORY
----------------------
Totalmemoryallocated9945977032;inadditionalpoolallocated13424640
Dictionarymemoryallocated648760
Bufferpoolsize549824
Freebuffers0
Databasepages549823
Modifieddbpages0
Pendingreads64
Pendingwrites:LRU0,flushlist0,singlepage0
Pagesread3798473,created3084,written142124
0.00reads/s,0.00creates/s,0.00writes/s
Nobufferpoolpagegetssincethelastprintout
--------------
ROWOPERATIONS
--------------
1queriesinsideInnoDB,0queriesinqueue
2readviewsopeninsideInnoDB
Mainthreadprocessno.4553,id1182828864,state:waitingforserveractivity
Numberofrowsinserted90188,updated33099,deleted0,read249911055
0.00inserts/s,0.00updates/s,0.00deletes/s,0.00reads/s
----------------------------
ENDOFINNODBMONITOROUTPUT
============================
InnoDB:######Diagnosticinfoprintedtothestandarderrorstream
其中还出现过宕机：
InnoDB:Error:semaphorewaithaslasted>600seconds
InnoDB:Weintentionallycrashtheserver,becauseitappearstobehung.
1202178:45:16InnoDB:Assertionfailureinthread1162561856infilesrv/srv0srv.cline2236
InnoDB:Weintentionallygenerateamemorytrap.
InnoDB:Submitadetailedbugreporttohttp://bugs.mysql.com.
InnoDB:Ifyougetrepeatedassertionfailuresorcrashes,even
InnoDB:immediatelyafterthemysqldstartup,theremaybe
InnoDB:corruptionintheInnoDBtablespace.Pleasereferto
InnoDB:http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB:aboutforcingrecovery.
1202178:45:16-mysqldgotsignal11;
Thiscouldbebecauseyouhitabug.Itisalsopossiblethatthisbinary
oroneofthelibrariesitwaslinkedagainstiscorrupt,improperlybuilt,
ormisconfigured.Thiserrorcanalsobecausedbymalfunctioninghardware.
Wewilltryourbesttoscrapeupsomeinfothatwillhopefullyhelpdiagnose
theproblem,butsincewehavealreadycrashed,somethingisdefinitelywrong
andthismayfail.
key_buffer_size=267108864
read_buffer_size=3145728
max_used_connections=3
max_threads=300
threads_connected=1
Itispossiblethatmysqldcoulduseupto
key_buffer_size+(read_buffer_size+sort_buffer_size)*max_threads=2110308K
bytesofmemory
Hopethat'sok;ifnot,decreasesomevariablesintheequation.
thd:0x0
Attemptingbacktrace.Youcanusethefollowinginformationtofindout
wheremysqlddied.Ifyouseenomessagesafterthis,somethingwent
terriblywrong...
stack_bottom=(nil)thread_stack0x30000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x20)[0xa16470]
/usr/local/mysql/bin/mysqld(handle_segfault+0x368)[0x609c34]
/lib64/libpthread.so.0[0x3d5d60e7c0]
/usr/local/mysql/bin/mysqld(srv_error_monitor_thread+0x2f3)[0x94ca0d]
/lib64/libpthread.so.0[0x3d5d6064a7]
/lib64/libc.so.6(clone+0x6d)[0x3d5cad3c2d]
Themanualpageathttp://dev.mysql.com/doc/mysql/en/crashing.htmlcontains
informationthatshouldhelpyoufindoutwhatiscausingthecrash.
12021708:45:17mysqld_safeNumberofprocessesrunningnow:0
12021708:45:17mysqld_safemysqldrestarted
1202178:45:17[Note]Plugin'FEDERATED'isdisabled.
InnoDB:Thelogsequencenumberinibdatafilesdoesnotmatch
InnoDB:thelogsequencenumberintheib_logfiles!
1202178:45:22InnoDB:Databasewasnotshutdownnormally!
InnoDB:Startingcrashrecovery.
InnoDB:Readingtablespaceinformationfromthe.ibdfiles...
InnoDB:Restoringpossiblehalf-writtendatapagesfromthedoublewrite
InnoDB:buffer...
InnoDB:LastMySQLbinlogfileposition0547,filename/usr/local/mysql/log/mysql-bin.000035
1202178:45:27InnoDB:Started;logsequencenumber433565657939
1202178:45:27[Note]Recoveringafteracrashusing/usr/local/mysql/log/mysql-bin
1202178:45:27[Note]Startingcrashrecovery...
1202178:45:27[Note]Crashrecoveryfinished.
1202178:45:42[Note]SlaveSQLthreadinitialized,startingreplicationinlog'mysql-bin.000037'atposition89665957,relaylog'/usr/local/mysql/log/relay-
bin.000238'position:89666102
1202178:45:42[Note]SlaveI/Othread:connectedtomaster'user@host:3306',replicationstartedinlog'mysql-bin.000037'atposition131046151
1202178:45:42[Note]EventScheduler:Loaded0events
1202178:45:42[Note]/usr/local/mysql/bin/mysqld:readyforconnections.
Version:'5.1.50-log'socket:'/usr/local/mysql/tmp/mysql.sock'port:3306MySQLCommunityServer(GPL)
烦请各位大侠帮忙分析一下，谢谢！！！
用mysqldump备200G的库有点大了如果还频繁重启建议用PerconaXtraBackup，在线备份的是物理文件而不是导数据
数据文件大约200GB，平时备份（mysqldump）压缩后的大小大约20GB
最近，从库用mysqldump备份时，errorlog中出现很多：
InnoDB:Warning:alongsemaphorewait:
--Thread1183631680haswaitedat../../storage/innobase/include/btr0btr.icline28for241.00secondsthesemaphore:
S-lockonRW-latchat0x2aacda28d898createdinfilebuf/buf0buf.cline550
awriter(threadid1183631680)hasreserveditinmodeexclusive
numberofreaders0,waitersflag1
Lasttimereadlockedinfilebtr/btr0pcur.cline246
Lasttimewritelockedinfilebuf/buf0buf.cline1823
InnoDB:######StartsInnoDBMonitorfor30secstoprintdiagnosticinfo:
InnoDB:Pendingpreads1,pwrites0
=====================================
12021710:40:47INNODBMONITOROUTPUT
=====================================
Persecondaveragescalculatedfromthelast27seconds
----------
SEMAPHORES
----------
OSWAITARRAYINFO:reservationcount62196,signalcount62194
--Thread1183631680haswaitedat../../storage/innobase/include/btr0btr.icline28for256.00secondsthesemaphore:
S-lockonRW-latchat0x2aacda28d898createdinfilebuf/buf0buf.cline550
awriter(threadid1183631680)hasreserveditinmodeexclusive
numberofreaders0,waitersflag1
Lasttimereadlockedinfilebtr/btr0pcur.cline246
Lasttimewritelockedinfilebuf/buf0buf.cline1823
Mutexspinwaits0,rounds98353,OSwaits1500
RW-sharedspins118450,OSwaits59162;RW-exclspins1996,OSwaits1404
------------
TRANSACTIONS
------------
Trxidcounter091046918
Purgedonefortrx'sn<091046756undon<00
Historylistlength21
LISTOFTRANSACTIONSFOREACHSESSION:
---TRANSACTION091046818,notstarted,processno4553,OSthreadid1183832384
MySQLthreadid154,queryid294386localhostroot
---TRANSACTION091046875,ACTIVE1300sec,processno4553,OSthreadid1183631680fetchingrows,threaddeclaredinsideInnoDB500
mysqltablesinuse1,locked0
MySQLthreadid134,queryid294200localhostbackupSendingdata
SELECT/*!40001SQL_NO_CACHE*/*FROM`table1`
Trxreadviewwillnotseetrxwithid>=091046876,sees<091046876
--------
FILEI/O
--------
I/Othread0state:waitingfori/orequest(insertbufferthread)
I/Othread1state:waitingfori/orequest(logthread)
I/Othread2state:doingfilei/o(readthread)evset
I/Othread3state:waitingfori/orequest(writethread)
Pendingnormalaioreads:64,aiowrites:0,
ibufaioreads:0,logi/o's:0,synci/o's:0
Pendingflushes(fsync)log:0;bufferpool:0
192399OSfilereads,256617OSfilewrites,7538OSfsyncs
1pendingpreads,0pendingpwrites
0.00reads/s,0avgbytes/read,0.00writes/s,0.00fsyncs/s
-------------------------------------
INSERTBUFFERANDADAPTIVEHASHINDEX
-------------------------------------
Ibuf:size1,freelistlen20875,segsize20877,
53381inserts,53381mergedrecs,31096merges
Hashtablesize18563003,nodeheaphas1buffer(s)
0.00hashsearches/s,0.00non-hashsearches/s
---
LOG
---
Logsequencenumber433634229620
Logflushedupto433634229620
Lastcheckpointat433634229620
0pendinglogwrites,0pendingchkpwrites
125902logi/o'sdone,0.00logi/o's/second
----------------------
BUFFERPOOLANDMEMORY
----------------------
Totalmemoryallocated9945977032;inadditionalpoolallocated13424640
Dictionarymemoryallocated648760
Bufferpoolsize549824
Freebuffers0
Databasepages549823
Modifieddbpages0
Pendingreads64
Pendingwrites:LRU0,flushlist0,singlepage0
Pagesread3798473,created3084,written142124
0.00reads/s,0.00creates/s,0.00writes/s
Nobufferpoolpagegetssincethelastprintout
--------------
ROWOPERATIONS
--------------
1queriesinsideInnoDB,0queriesinqueue
2readviewsopeninsideInnoDB
Mainthreadprocessno.4553,id1182828864,state:waitingforserveractivity
Numberofrowsinserted90188,updated33099,deleted0,read249911055
0.00inserts/s,0.00updates/s,0.00deletes/s,0.00reads/s
----------------------------
ENDOFINNODBMONITOROUTPUT
============================
InnoDB:######Diagnosticinfoprintedtothestandarderrorstream
其中还出现过宕机：
InnoDB:Error:semaphorewaithaslasted>600seconds
InnoDB:Weintentionallycrashtheserver,becauseitappearstobehung.
1202178:45:16InnoDB:Assertionfailureinthread1162561856infilesrv/srv0srv.cline2236
InnoDB:Weintentionallygenerateamemorytrap.
InnoDB:Submitadetailedbugreporttohttp://bugs.mysql.com.
InnoDB:Ifyougetrepeatedassertionfailuresorcrashes,even
InnoDB:immediatelyafterthemysqldstartup,theremaybe
InnoDB:corruptionintheInnoDBtablespace.Pleasereferto
InnoDB:http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB:aboutforcingrecovery.
1202178:45:16-mysqldgotsignal11;
Thiscouldbebecauseyouhitabug.Itisalsopossiblethatthisbinary
oroneofthelibrariesitwaslinkedagainstiscorrupt,improperlybuilt,
ormisconfigured.Thiserrorcanalsobecausedbymalfunctioninghardware.
Wewilltryourbesttoscrapeupsomeinfothatwillhopefullyhelpdiagnose
theproblem,butsincewehavealreadycrashed,somethingisdefinitelywrong
andthismayfail.
key_buffer_size=267108864
read_buffer_size=3145728
max_used_connections=3
max_threads=300
threads_connected=1
Itispossiblethatmysqldcoulduseupto
key_buffer_size+(read_buffer_size+sort_buffer_size)*max_threads=2110308K
bytesofmemory
Hopethat'sok;ifnot,decreasesomevariablesintheequation.
thd:0x0
Attemptingbacktrace.Youcanusethefollowinginformationtofindout
wheremysqlddied.Ifyouseenomessagesafterthis,somethingwent
terriblywrong...
stack_bottom=(nil)thread_stack0x30000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x20)[0xa16470]
/usr/local/mysql/bin/mysqld(handle_segfault+0x368)[0x609c34]
/lib64/libpthread.so.0[0x3d5d60e7c0]
/usr/local/mysql/bin/mysqld(srv_error_monitor_thread+0x2f3)[0x94ca0d]
/lib64/libpthread.so.0[0x3d5d6064a7]
/lib64/libc.so.6(clone+0x6d)[0x3d5cad3c2d]
Themanualpageathttp://dev.mysql.com/doc/mysql/en/crashing.htmlcontains
informationthatshouldhelpyoufindoutwhatiscausingthecrash.
12021708:45:17mysqld_safeNumberofprocessesrunningnow:0
12021708:45:17mysqld_safemysqldrestarted
1202178:45:17[Note]Plugin'FEDERATED'isdisabled.
InnoDB:Thelogsequencenumberinibdatafilesdoesnotmatch
InnoDB:thelogsequencenumberintheib_logfiles!
1202178:45:22InnoDB:Databasewasnotshutdownnormally!
InnoDB:Startingcrashrecovery.
InnoDB:Readingtablespaceinformationfromthe.ibdfiles...
InnoDB:Restoringpossiblehalf-writtendatapagesfromthedoublewrite
InnoDB:buffer...
InnoDB:LastMySQLbinlogfileposition0547,filename/usr/local/mysql/log/mysql-bin.000035
1202178:45:27InnoDB:Started;logsequencenumber433565657939
1202178:45:27[Note]Recoveringafteracrashusing/usr/local/mysql/log/mysql-bin
1202178:45:27[Note]Startingcrashrecovery...
1202178:45:27[Note]Crashrecoveryfinished.
1202178:45:42[Note]SlaveSQLthreadinitialized,startingreplicationinlog'mysql-bin.000037'atposition89665957,relaylog'/usr/local/mysql/log/relay-
bin.000238'position:89666102
1202178:45:42[Note]SlaveI/Othread:connectedtomaster'user@host:3306',replicationstartedinlog'mysql-bin.000037'atposition131046151
1202178:45:42[Note]EventScheduler:Loaded0events
1202178:45:42[Note]/usr/local/mysql/bin/mysqld:readyforconnections.
Version:'5.1.50-log'socket:'/usr/local/mysql/tmp/mysql.sock'port:3306MySQLCommunityServer(GPL)
烦请各位大侠帮忙分析一下，谢谢！！！
4 个解决方案
#1
用mysqldump备200G的库有点大了如果还频繁重启建议用PerconaXtraBackup，在线备份的是物理文件而不是导数据
更多相关文章
- VFP+SQL Server 2000怎样进行数据库备份和恢复？
- sqlserver 差异备份与还原示例
- sql2000在还原备份时,我发现我新的备份,在机子上还原,还经常是上
- MongoDB备份恢复与导入导出
- mysql 数据库备份和还原 实践
- 直接的文件备份和用sqlserver的备份有什么区别
- 浅谈mysql的备份
- 10个MySQL数据库备份教程推荐
- 第 5 章 MySQL 备份与恢复