Home / server / Questions / 472480
Accepted
virtualeyes
Asked: 2013-01-26 10:46:04 +0800 CST

MySQL: Increased buffer pool, ibdata1 corrupt?

  • 772

I tried increasing innodb_buffer_pool_size via my.cnf from default 128m to 256m, but on restart attempt, mysql shutdown failed with:

130125 11:49:55 InnoDB: Initializing buffer pool, size = 256.0M
130125 11:49:55 InnoDB: Completed initialization of buffer pool
InnoDB: Unable to lock ./ibdata1, error: 11

MySQL is up and running but the any attempt to "mysql -u root -p" via terminal blows up with:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

I touch'd/chown'd mysql.sock and mysqld.pid in appropriate locations (as they were missing, not good), but still no luck getting into mysql.

Have a dump from last night, but would love to get a dump from today to see if ibdata1 is corrupted (read/write operations seem fine, so if it was corrupted mysql would shutdown, no?)

Needless to say, worried about trying to restart! We have a Java application that connects to MySQL via a connection pool; is the locking happening there?

At any rate, ideas for how to approach the situation appreciated. Might clone the VM MySQL runs on and import /var/lib/mysql directory to see what's going on, but if it's simply a matter of recreating the sock and pid files and making the restarting, no point in wasting an afternoon.

mysql

2 Answers

  1. Best Answer

    Something else is holding a file lock on ibdata1. Use lsof on ibdata1 and figure out who is holding the lock.

    • 4

  2. Got this resolved, mysql.sock and mysql.pid files went AWOL so I:

    touch /var/lib/mysql/mysql.sock
chown mysql.mysql /var/lib/mysql/mysql.sock
touch /var/run/mysqld/mysqld.pid
chown mysql.mysql /var/run/mysqld/mysqld.pid
echo [pid of running mysqld] > /var/run/mysqld/mysqld.pid

    mysqld service was running so client site had been fine, but given the alarming errors in the log I was under the impression that ibdata file had been corrupted: "Could not open or create data files...InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful do not remove old data files which contain your precious data!"

    I mean, it's hard not to think the sky is falling given the voluminous warnings and errors -- looks like the sh*t has hit the fan when, in this case, it hasn't at all.

    Ran the above via terminal session followed by a service mysql restart and voila, still in business with buffer pool size increased to 256MB as I had originally intended when I made the change to my.cnf this morning.

    As for the cause of the problem, I had run mysqltuner.pl to check on performance bottlenecks; this must create a new mysqld process in addition to the running mysqld process, which remains connected after the script runs (grep'ing running processes at the time of failed restart there were 4 mysqld processes 2 for root mysqld_safe and 2 for mysql mysqld).

    Killing the mysqltuner.pl created process did not solve the problem as mysql.sock and mysql.pid files went with it, and then I couldn't get into mysql client. Looking into the logs I feared the worst and spent a couple of hours scouring the net.

    Much ado about nothing ;-)

    • 0