-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Wojtek Bogusz wrote:
> hi. i have a problem with mysql. maybe some of you know
how to solve it
> please.
>
> mysql is crushing down saying than:
>
> InnoDB: Database page corruption on disk or a failed
>
>
> I was trying to mysqlcheck it but it keeps loosing
connection. also i
> cannot use myisamchk program as my installation is uses
InnoDB and not
> ISAM. i was searching whole morning for the tools to
repair the database
> but all i found was for ISAM engine. please help!
I've had this happen to me as well, and I agree that it's
very
frustrating. The solution is not necessarily intuitive
either, as the
MySQL documentation still maintains that InnoDB corruption
only happens
due to flaws in the underlying filesystem (e.g. disk
corruption). That
said, here is how I've fixed this sort of thing in the
past--read
through it carefully, and be aware that this is an
at-your-own-risk
procedure:
(1) In your my.cnf file, add "innodb_force_recovery =
4" to your mysqld
section. What's happening right now is that mysqld is
trying to reload
its logs and buffers, but it's encountering an error, and
when that
happens it shuts down and restarts, which leaves it in an
endless loop
(if you tail -f /var/log/mysqld.log you'll see this
happening). This
also makes it impossible to do any manual recovery, because
the mysqld
is constantly starting and stopping, preventing you from
connecting to
it for more than a few seconds at a time. By adding the
"innodb_force_recovery = 4" to your my.cnf and
restarting mysqld,
though, you tell mysqld not to try reloading its logs and
buffers at
all. This prevents the endless series of restarts, and lets
you use
your recovery tools, now that you can get a stable
connection to the
database. Note: you can't leave the database in this state,
even though
it appears stable--it's in read-only mode, for repair
purposes, and
won't let you write new records to it.
(2) Use mysqldump to create dump files of all of your
databases, one at
a time. For the Maia database, for instance, you'd do
something like this:
mysqldump -u amavis -p maia > maia-dump.sql
Do this for any other databases you have as well (or at
least any
databases that are stored in InnoDB tables).
(3) Next, use mysql to connect to the mysqld and DROP the
affected
databases, one at a time, and CREATE new ones. e.g.
DROP DATABASE maia;
CREATE DATABASE maia;
(4) Before you can try reloading your dump file, you'll need
to comment
out the "innodb_force_recovery = 4" line in your
/etc/my.cnf and restart
mysqld. Watch the mysqld.log file after the restart to see
whether the
endless error loop is back, or whether things seem stable.
If things
look stable, you can reload the databases from their dump
files, e.g.
mysql -u amavis -p maia < maia-dump.sql
If that succeeds, you're done--your databases should be back
online and
working properly. If the endless error loop is still going
on, though,
proceed with the next step.
(5) If you got to this point, the endless error loop is
still happening
even though you've DROPped and reCREATEd your database.
That means the
corruption lies within your /var/log/mysql/ibdata* files
(i.e. MySQL's
InnoDB tablespace). There's no "official" way to
repair this sort of
corruption, but there is a brute-force approach that has
worked for me
in the past. Be sure you have /all/ of your databases
backed up as
advised in step (2)!
(6) The brute-force approach involves deleting the ibdata*
files, the
ib_logfile* files, and the /var/lib/mysql/mysql subdirectory
(i.e. the
"mysql" database). This effectively wipes out
every trace of all your
databases, so when you restart mysqld (comment out the
"innodb_force_recovery" line in your /etc/my.cnf
first) it thinks it's a
brand-new installation. It will create a brand-new set of
ibdata* and
ib_logfile* files, and a new "mysql" database.
(7) You'll need to set the root password for MySQL again,
and you'll
need to issue the GRANT statements again for any database
users (e.g.
your amavis user), since these tables will have been wiped
out in step (6).
(8) Finally, you can recreate your databases (e.g.
"CREATE DATABASE
maia;") and reload them from their dump files, as in
step (4). After
the reload, everything should be working fine again.
If even this (fairly radical) approach doesn't solve your
problem, it's
very likely that you /do/ have a physical disk issue to look
into. Run
a filesystem check and perhaps check the S.M.A.R.T. status
of the
drive(s), if they're S.M.A.R.T.-capable.
- --
Robert LeBlanc <rjl renaissoft.com>
Renaissoft, Inc.
Maia Mailguard <http://www.maiamail
guard.com/>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
iD8DBQFGjB0iGmqOER2NHewRAoJuAJ0WnHJPFIB28iq4pYIDh7OcNKYpTACf
ctXy
+hdvnvD3eqyQ+TRFi65qY48=
=ImYV
-----END PGP SIGNATURE-----
_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users
|
