mysql problem

5 Aug 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@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@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@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@lists.vpsfree.cz
>>>>>>>>>>>> http://lists.vpsfree.cz/listinfo/community-list
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>> 
_______________________________________________ Community-list
>>>>>>>>>>> mailing list Community-list@lists.vpsfree.cz 
>>>>>>>>>>> http://lists.vpsfree.cz/listinfo/community-list
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>> 
_______________________________________________ Community-list
>>>>>>>>>> mailing list Community-list@lists.vpsfree.cz 
>>>>>>>>>> http://lists.vpsfree.cz/listinfo/community-list
>>>>>>>> _______________________________________________ 
>>>>>>>> Community-list mailing list 
>>>>>>>> Community-list@lists.vpsfree.cz 
>>>>>>>> http://lists.vpsfree.cz/listinfo/community-list
>>>>>>>> 
>>>>>>> _______________________________________________ 
>>>>>>> Community-list mailing list 
>>>>>>> Community-list@lists.vpsfree.cz 
>>>>>>> http://lists.vpsfree.cz/listinfo/community-list
>>>>>> _______________________________________________ 
>>>>>> Community-list mailing list 
>>>>>> Community-list@lists.vpsfree.cz 
>>>>>> http://lists.vpsfree.cz/listinfo/community-list
>>>>> _______________________________________________ 
>>>>> Community-list mailing list 
>>>>> Community-list@lists.vpsfree.cz 
>>>>> http://lists.vpsfree.cz/listinfo/community-list
>>>> _______________________________________________ 
>>>> Community-list mailing list Community-list@lists.vpsfree.cz 
>>>> http://lists.vpsfree.cz/listinfo/community-list
>>>> 
>>> _______________________________________________ Community-list
>>> mailing list Community-list@lists.vpsfree.cz 
>>> http://lists.vpsfree.cz/listinfo/community-list
>> _______________________________________________ Community-list
>> mailing list Community-list@lists.vpsfree.cz 
>> http://lists.vpsfree.cz/listinfo/community-list
> 
> _______________________________________________ Community-list
> mailing list Community-list@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-----

Dusan Simek

Tomas Srnka

Tomas Srnka

Dusan Simek

Tomas Srnka

Pavel Snajdr

Dusan Simek

Jirka Bourek

Dusan Simek

Tomas Srnka

Dusan Simek

Tomas Volf

Jan Harsa

Pavel Snajdr

Ján Šmatlík

tags

participants (7)