[vpsFree.cz: community-list] mysql problem

Tue Aug 7 12:16:37 CEST 2012

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

Kluci, nechcete se na ty 1 radkovy maily presunout radsi na nas Jabber
MUC? :)

Diky :]

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/07/2012 12:14 PM, [redacted] wrote:
> Replikace není záloha :) Já používám replikaci k tomu, abych mohl
> dělat dumpy na slave a ne na masteru, kvůli zátěži.
> 
> Dne 7.8.2012 12:13, Tomas Volf napsal(a):
>> Ja pouzivam replikaci primo zabudovanou v MySQL :)
>> 
>> http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
>> 
>> On Tue, 07 Aug 2012 07:52:59 +0200 Dusan Simek
>> <pixelfields at gmail.com> wrote:
>> 
>>> 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:
> 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
>>>>>>>> _______________________________________________ 
>>>>>>>> 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
>>>> 
>>> _______________________________________________ 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/

iF4EAREIAAYFAlAg6wUACgkQdh+64ds5DaafuwEA3ZlMyz5+hxeQJH71YMlAg9VA
Lim9NygBUba6vTDv7AoBAONoLcu7UYaG7ZsP+sgrOm5MIhDBPCRWJKFMzla0IjAG
=CIk7
-----END PGP SIGNATURE-----