[vpsFree.cz: community-list] mysql problem

Tue Aug 7 12:13:14 CEST 2012

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