[vpsFree.cz: community-list] mysql problem

Tue Aug 7 07:52:59 CEST 2012

Ahoj,
kazdy ma asi nejakou tu utilitku na zalohy mysql, ja uz leta pouzivam 
mysql-zrm:

https://zmanda.com/backup-mysql.html

Jinak diky za dalsi tip.

Dusan

On 08/06/2012 12:46 PM, Tomas Srnka wrote:
> Ahoj,
>
> aby si predosiel do buducna problemom s obnovou zalohy, odporucam
> zalohovat pomocou xtrabackup utilitky -
> https://prasiatko.vpsfree.cz/wiki/doku.php?id=mysql_zalohovanie#xtrabackup
> .
>
> Tomas
>
> On Mon, Aug 6, 2012 at 10:08 AM, Dusan Simek <pixelfields at 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-corruption/
>>>>
>>>>
>>>>
>>>> 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 at 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 at lists.vpsfree.cz
>>>>>>>> http://lists.vpsfree.cz/listinfo/community-list
>>>>>>>
>>>>>>>
>>>>>>> _______________________________________________ Community-list
>>>>>>> mailing list Community-list at lists.vpsfree.cz
>>>>>>> http://lists.vpsfree.cz/listinfo/community-list
>>>>>>>
>>>>>>
>>>>>>
>>>>>> _______________________________________________ Community-list
>>>>>> mailing list Community-list at 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 at lists.vpsfree.cz
>>>>> http://lists.vpsfree.cz/listinfo/community-list
>>>>>
>>>> _______________________________________________
>>>> Community-list mailing list
>>>> Community-list at lists.vpsfree.cz
>>>> http://lists.vpsfree.cz/listinfo/community-list
>>>
>>> _______________________________________________
>>> Community-list mailing list
>>> Community-list at lists.vpsfree.cz
>>> http://lists.vpsfree.cz/listinfo/community-list
>>
>> _______________________________________________
>> Community-list mailing list
>> Community-list at lists.vpsfree.cz
>> http://lists.vpsfree.cz/listinfo/community-list
> _______________________________________________
> Community-list mailing list
> Community-list at lists.vpsfree.cz
> http://lists.vpsfree.cz/listinfo/community-list
>