Ahoj,
aby si predosiel do buducna problemom s obnovou zalohy, odporucam
zalohovat pomocou xtrabackup utilitky -
.
Tomas
On Mon, Aug 6, 2012 at 10:08 AM, Dusan Simek
<pixelfields(a)gmail.com> wrote:
no chybu to samozrejme vyhodi a zapise do logu
neco takoveho:
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f4490cf1850): insert into events
(eventid,source,object,objectid,clock,value) values
(71594,0,0,13699,1343957163,2)
Connection ID (thread ID): 14
Status: NOT_KILLED
The manual page at
http://dev.mysql.com/doc/mysql/en/crashing.html
contains information that should help you find out what is causing
the crash. 120805 21:38:23 mysqld_safe Number of processes running
now: 0 120805 21:38:23 mysqld_safe mysqld restarted
120805 21:38:23 [Warning] '--skip-locking' is deprecated and will
be removed in a future release. Please use
'--skip-external-locking' instead. 120805 21:38:23 [Note] Plugin
'FEDERATED' is disabled. 120805 21:38:23 InnoDB: Initializing
buffer pool, size = 192.0M 120805 21:38:23 InnoDB: Completed
initialization of buffer pool InnoDB: Log scan progressed past the
checkpoint lsn 46 3585383713 120805 21:38:23 InnoDB: Database was
not shut down normally! InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the
doublewrite InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 46
3585383743 InnoDB: 1 transaction(s) which must be rolled back or
cleaned up InnoDB: in total 397 row operations to undo
InnoDB: Trx id counter is 0 302035200
InnoDB: Last MySQL binlog file position 0 532, file
name ./mysql-bin.013426 InnoDB: Starting in background the
rollback of uncommitted transactions InnoDB: Cleaning up trx with
id 0 295634833 120805 21:38:23 InnoDB: Rollback of non-prepared
transactions completed 120805 21:38:23 InnoDB: Started; log
sequence number 46 3585383743 120805 21:38:23 [Note] Recovering
after a crash using mysql-bin 120805 21:38:23 [Note] Starting
crash recovery... 120805 21:38:23 [Note] Crash recovery finished.
120805 21:38:23 [Note] Event Scheduler: Loaded 0 events
120805 21:38:23 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.63-0+squeeze1-log' socket:
'/var/run/mysqld/mysqld.sock' port: 3306 (Debian)
120805 21:38:33 InnoDB: Assertion failure in thread
140313926240000 in file ../../../storage/innobase/btr/btr0cur.c
line 178 InnoDB: Failing assertion: btr_page_get_next(get_page,
mtr) == buf_frame_get_page_no(page)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to
http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB:
http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
takze info o te taulce tam je, ovsem jaky koliv popus o repair
skonci crashem a restartem enginu.
Takze nakonec jsem to vyresil nasledovne:
dle dokumentace tady:
http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html
jsem zmenil promennou innodb_force_recovery na 3, kdy se povedlo
vydumpovat databaze bez crache enginu. Pak odinstalovat
mysql-server, smazat vse ve /var/lib/mysql, nainstalovat znova
mysql-server, natahnout dumpy.
Slo by to asi opravit i z nejake starsi zalohy, ale takhle jsem
mel alespon nejake relativne nejnovejsi data.
Diky.
Dusan
On 08/06/2012 09:08 AM, Jirka Bourek wrote:
> Když ten mysqlcheck doběhne takhle, tak v logu DB nic není? Třeba
> popis chyby nebo info o tom, že se DB restartovala. Pokud si
> dobře vzpomínám, ve výpisu show table status by mohlo být nějaké
> info ke konkrétní tabulce, proč ji nejde obnovit. (Pokud nemáš
> nějak divně přenastavené lgoování, info o poškozených tabulkách
> by tam mělo být.)
>
> Btw. nevím, proč se při opravě tabulky pouští optimize table,
> možná by bylo lepší zkontrolovat a opravit poškozené tabulky
> ručně (na verzi 5 se oprava normálně dělá sama, když se přistoupí
> k poškozené tabulce, tzn. ruční oprava bude potřeba jenom tam,
> kde je nějaký problém.)
>
> Dusan Simek wrote:
>> Diky ...
>> zatim jsem tedy pokrocil takto:
>>
>> 1/ myisam tabulky vypadaji ok, tam repair, check, optimize
>> proslo ok 2/ inndodb tabulky konci errorem:
>>
>> mysqlcheck: Got error: 2013: Lost connection to MySQL server
>> during query when executing 'OPTIMIZE TABLE ... '
>>
>> zatim to tedy vypada ze budu muset udelat neco takoveho:
>>
>>
>>
http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corr…
>>
>>
>>
>> pokud tedy nemate nikdo nejaky jiny napad.
>>
>> Diky.
>>
>> Dusan
>>
>> On 08/05/2012 04:25 PM, Pavel Snajdr wrote:
>>> -----BEGIN PGP SIGNED MESSAGE-----
>>> Hash: SHA256
>>>
>>> Prinejhorsim ti experimentalne zpristupnim zalohu a budes si
>>> moct vytahnout drivejsi snapshoty mysql dat, doufam ze aspon
>>> jeden z nich bude konzistentni.
>>>
>>> S pozdravem
>>>
>>> Pavel Snajdr
>>>
>>> +421 948 816 186 | +420 720 107 791 | 110-010-956
>>> CTO of Relbit | Predseda vpsFree.cz, o.s. | RHCE
>>>
http://relbit.com |
http://vpsfree.cz |
https://www.redhat.com
>>>
>>> On 08/05/2012 03:50 PM, Dusan Simek wrote:
>>>> Nezkousel, zkusim, o ty poskozeny intergite jsem taky
>>>> premyslel.
>>>>
>>>> V priloze by mel byt my.cnf, jestli to projde.
>>>>
>>>> Dusan
>>>>
>>>> On 08/05/2012 03:45 PM, Tomas Srnka wrote:
>>>>>
http://www.cafewebmaster.com/repair-all-mysql-databases-tables-php
>>>>> -> take nieco si skusal? Mozes mat poskodenu integritu
>>>>> niektorych tabuliek. Idealne si napis skript, co zbehne CHECK
>>>>> a REPAIR na vsetky tabulky vo vsetkych DB.
>>>>>
>>>>> On Aug 5, 2012, at 3:38 PM, Dusan Simek
>>>>> <pixelfields(a)gmail.com> wrote:
>>>>>
>>>>>> Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil
>>>>>> mysql. Chtel bych poprosit o info, jak to opravit, pokud s
>>>>>> tim mate nekdo nejakou zkusenost. Situace je nasledujici:
>>>>>>
>>>>>> Pri nekterych requestech (je to nahodne, 10 requestu ok, 11
>>>>>> zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate
>>>>>> spojeni. V logu vidim nasledujici:
>>>>>>
>>>>>> bud:
>>>>>>
>>>>>> OperationalError: (2013, "Lost connection to MySQL server
at
>>>>>> 'reading initial communication packet', system error:
104")
>>>>>>
>>>>>> nebo:
>>>>>>
>>>>>> OperationalError: (2003, "Can't connect to MySQL server
on
>>>>>> '83.167.228.14' (111)")
>>>>>>
>>>>>> nebo:
>>>>>>
>>>>>> OperationalError: (2002, "Can't connect to local MySQL
server
>>>>>> through socket '/var/run/mysqld/mysqld.sock' (2)")
>>>>>>
>>>>>> mysql vypada, ze bezi normalne:
>>>>>>
>>>>>> netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306
>>>>>> 0.0.0.0:* LISTEN 26693/mysqld
>>>>>>
>>>>>> ps ax | grep mysql 12488 ? S 0:04 /bin/sh
>>>>>> /usr/bin/mysqld_safe 26869 ? Sl 0:02
>>>>>> /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql
>>>>>> --user=mysql --log-error=/var/lib/mysql/msa.err
>>>>>> --pid-file=/var/lib/mysql/msa.pid
>>>>>> --socket=/var/run/mysqld/mysqld.sock --port=3306
>>>>>>
>>>>>> socket vypada ok:
>>>>>>
>>>>>> ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096
>>>>>> Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 ..
>>>>>> srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock
>>>>>>
>>>>>>
>>>>>> Kdyz zkusim mysql restartovat, dostanu nasledujici:
>>>>>>
>>>>>> /etc/init.d/mysql restart Stopping MySQL database server:
>>>>>> mysqld. Starting MySQL database server: mysqld. Checking for
>>>>>> corrupt, not cleanly closed and upgrade needing tables..
>>>>>> msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost
>>>>>> connection to MySQL server during query ERROR 2002 (HY000):
>>>>>> Can't connect to local MySQL server through socket
>>>>>> '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000):
Can't
>>>>>> connect to local MySQL server through socket
>>>>>> '/var/run/mysqld/mysqld.sock' (2) ....
>>>>>>
>>>>>> v logu pri startu vidim toto:
>>>>>>
>>>>>> Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading
>>>>>> MySQL tables if necessary. Aug 5 10:26:33 msa
>>>>>> /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the
>>>>>> '--basedir' option is always ignored Aug 5 10:26:33 msa
>>>>>> /etc/mysql/debian-start[30664]: Looking for 'mysql' as:
>>>>>> /usr/bin/mysql Aug 5 10:26:33 msa
>>>>>> /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck'
as:
>>>>>> /usr/bin/mysqlcheck Aug 5 10:26:33 msa
>>>>>> /etc/mysql/debian-start[30664]: This installation of MySQL is
>>>>>> already upgraded to 5.1.63, use --force if you still need to
>>>>>> run mysql_upgrade Aug 5 10:26:33 msa
>>>>>> /etc/mysql/debian-start[30672]: Checking for insecure root
>>>>>> accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]:
>>>>>> Triggering myisam-recover for all MyISAM tables
>>>>>>
>>>>>> Diky za jakykoliv tip.
>>>>>>
>>>>>> Dusan _______________________________________________
>>>>>> Community-list mailing list Community-list(a)lists.vpsfree.cz
>>>>>>
http://lists.vpsfree.cz/listinfo/community-list
>>>>>
>>>>> _______________________________________________ Community-list
>>>>> mailing list Community-list(a)lists.vpsfree.cz
>>>>>
http://lists.vpsfree.cz/listinfo/community-list
>>>>>
>>>>
>>>> _______________________________________________ Community-list
>>>> mailing list Community-list(a)lists.vpsfree.cz
>>>>
http://lists.vpsfree.cz/listinfo/community-list
>>> -----BEGIN PGP SIGNATURE-----
>>> Version: GnuPG v1.4.12 (GNU/Linux)
>>> Comment: Using GnuPG with Mozilla -
http://enigmail.mozdev.org/
>>>
>>> iF4EAREIAAYFAlAeglAACgkQdh+64ds5DaacSwD/eG8sDfT9ycREamDJW5D6Iny3
>>> 1Vy3XCg3RahKLAqrfjAA/R7jp3yPam63I5dtv/dbFTXw27WfyfUtAaXQT3UdxljG
>>> =Fpgl
>>> -----END PGP SIGNATURE-----
>>>
_______________________________________________
>>> Community-list mailing list
>>> Community-list(a)lists.vpsfree.cz
>>>
http://lists.vpsfree.cz/listinfo/community-list
>>>
>> _______________________________________________
>> Community-list mailing list
>> Community-list(a)lists.vpsfree.cz
>>
http://lists.vpsfree.cz/listinfo/community-list
> _______________________________________________
> Community-list mailing list
> Community-list(a)lists.vpsfree.cz
>
http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________
Community-list mailing list
Community-list(a)lists.vpsfree.cz
http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________
Community-list mailing list
Community-list(a)lists.vpsfree.cz