Re: [vpsFree.cz: community-list] mysql problem

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(a)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(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 >>>> >>>>

>>>> >>>> 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 >>>> >>>>

>>>> 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… >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>>

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 >>>>>>>>>> >>>>>>>>>> >>>>>>>>>>

>>>>>>>>>> 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 >>>>>>>>>>> _______________________________________________ >>>>>>>>>>> >>>>>>>>>>>

>>>>>>>>>>> http://lists.vpsfree.cz/listinfo/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 -----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-----