63

I have a simple webserver (Debian 6.0 x86, DirectAdmin with 1 GB of memory and still 10 GB free space, mySQl version 5.5.9), however the mySQL server keeps crashing and I need to kill all mySQL processes to be able to restart it again.

/var/log/mysql-error.log output:

130210 21:04:26 InnoDB: Using Linux native AIO
130210 21:04:34 InnoDB: Initializing buffer pool, size = 128.0M
130210 21:05:42 InnoDB: Completed initialization of buffer pool
130210 21:05:48 InnoDB: Initializing buffer pool, size = 128.0M
130210 21:06:22 InnoDB: Initializing buffer pool, size = 128.0M
130210 21:06:27 mysqld_safe mysqld from pid file /usr/local/mysql/data/website.pid ended
130210 21:06:29 mysqld_safe mysqld from pid file /usr/local/mysql/data/website.pid ended
130210 21:07:22 InnoDB: Completed initialization of buffer pool
130210 21:07:51 mysqld_safe mysqld from pid file /usr/local/mysql/data/website.pid ended
130210 21:08:33 InnoDB: Completed initialization of buffer pool
130210 21:12:03 [Note] Plugin 'FEDERATED' is disabled.
130210 21:12:47 InnoDB: The InnoDB memory heap is disabled
130210 21:12:47 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
130210 21:12:47 InnoDB: Compressed tables use zlib 1.2.3
130210 21:12:47 InnoDB: Using Linux native AIO
130210 21:13:11 InnoDB: highest supported file format is Barracuda.
130210 21:13:23 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130210 21:14:05  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
130210 21:17:53  InnoDB: Unable to open the first data file
InnoDB: Error in opening ./ibdata1
130210 21:17:53  InnoDB: Operating system error number 11 in a file operation.

I have found a topic on the mySQL website here however there's no solution for it.

Any ideas anyone?

2
  • I don't understand - this part of log file tells about problem with MySQL start not about cause of error. The best thing is to remove source of the problem. Feb 10 '13 at 22:14
  • 1
    Did you check that there was no running mysqld before starting it? How?
    – symcbean
    Feb 11 '13 at 0:52

11 Answers 11

44

another approach from one comment in the same blog:

this helped me:

lsof -i:3306

Then kill it (the process number)

kill -9 PROCESS

e.g. kill -9 13498

Then try to restart MySQL again.

via http://www.webhostingtalk.com/archive/index.php/t-1070293.html

1
  • 1
    service mysql restart was showing no running process already, but lsof found it. Killed it, service mysql start, and now the flood of process failed emails can stop. Many thanks. Dec 26 '15 at 16:47
30

with ubuntu 14.04. I'm experiencing this problem when I try to restart via

/etc/init.d/mysql restart

Instead try

service mysql restart 
8
  • 1
    Thanks, it helped. But why?
    – too
    Jun 24 '15 at 21:28
  • 1
    @too askubuntu.com/questions/2075/…
    – mloskot
    Oct 31 '15 at 20:38
  • @too, if you have both an old-style init.d script and an upstart config for a job, you have to use service job start, otherwise if you start it with the init.d script, Upstart will not know about it and it could attempt to boot another instance. (At least that's the case with MySQL's default init scripts.)
    – wireman
    Jan 8 '16 at 12:04
  • @wireman: that explains why other packages including knot (DNS server) have similar issues. When they decided to enforce it? I think /etc/init.d is superior as it allows shell completion thus relieves user from excessive typing. Progress to the power of -1 :)
    – too
    Jan 8 '16 at 21:50
  • @too Bash tab completion is customizable. Don't have anything Ubuntu handy, but seems odd that nobody would have thought of tab-completing service names. Maybe submit a feature request to Canonical through their bug tracker?
    – user
    Mar 15 '16 at 20:04
23

Solution

make a copy of the original files (ibdata1, ib_logfile0, ib_logfile1...).

mv ibdata1 ibdata1.bak 
cp -a ibdata1.bak ibdata1

http://cglreport.zhenhua.info/2008/08/mysql-error-unable-to-lock-ibdata1.html

4
  • magically helped me. May 19 '15 at 14:39
  • what is the point of cp -a ?, I've already read man page
    – Ilja
    May 20 '15 at 12:09
  • 3
    Many thanks, it helped. But why?
    – DaviAragao
    Sep 24 '15 at 11:13
  • 1
    I had this problem after a failed restart on a db running in a docker container. I am making an educated guess as to why this works and it is that some meta data is stored in the file. moving it and copying it to it's original location removes the metadata determining it is locked. the -a operation maintains the file attributes, including SELinux related attributes, but not the metadata, during the copy.
    – JDL
    May 25 '18 at 18:19
22

The most common cause of this problem is trying to start MySQL when it is already running.

To resolve it, kill off any running instances of MySQL and then restart it using your normal startup scripts, e.g. service mysql start.

Don't attempt to start MySQL manually when using distribution-packaged versions unless you are prepared for a world of hurt.

2
  • however the mySQL server keeps crashing - I don't restart mySQL. It just crashes itself, after which I do need to restart it obviously. ;-)
    – Devator
    Feb 10 '13 at 22:26
  • @MichaelHampton can you give little more info about 'world of hurt' and 'start MySQL manually'? Thanks) Dec 31 '13 at 9:06
6

This helped me to solve it:

Delete all ibdata files and let mysql create them.

stop mysql:

service mysql stop

go to mysql library:

cd /var/lib/mysql/

move innodb files somewhere in case you need it:

mv ib* /root/

start mysql:

service mysql start
1
  • Scrolling through helpful answers I thought surely this looks like it will work since the error is complaining about not being able to lock that file. After moving, mysql did recreate them but still is complaining... crazy! Jun 20 '16 at 14:48
3

Check space to make sure it is 100%

df -h

As if its full it wont create .sock file.

1
  • The answer is about a surprising side-effect, I strongly disagree it would be LQ.
    – peterh
    Feb 4 '17 at 19:26
2

As other users are reporting, this is usually due to MySQL not being properly terminated upon systemctl stop mysql.service .

I noticed on Ubuntu 18.04 that filling in the root password twice in /etc/mysql/debian.cnf made systemctl stop and start the MySQL server process properly which eliminated the problem.

1

Came here from googling of the same repeating error but with error code 13 (InnoDB: Unable to lock ./ibdata1, error: 13). After trying lot of solutions around the internet, invented one that helped me (apparmor!)

Add these lines to the config /etc/apparmor.d/usr.sbin.mysqld (and reload apparmor and mysql of course):

/path/to/mysql/data/ r,
/path/to/mysql/data/** rwk,

The main differences between often solutions: two rules (for dir itself and for all files inside, note the double **) and k option to allow mysql to lock files.

Hope this help someone.

1
  • You can also add it to /etc/apparmor.d/local/usr.sbin.mysqld. Create the file if it doesn't exist. For more details, please see /etc/apparmor.d/local/README
    – knb
    Jun 8 '16 at 14:36
0

Please check that you have pid-file parameter in the [mysql] section of my.cnf file. If it is not present, the unable to lock ...ibdata1.. error:1 will occur.

0

Simple, but faster than the way with "cp -a". And helped when "cp -a" and everything other couldn't.

  1. service mysql stop && pkill -f mysql

Get rid of all mysql processes

  1. vi /etc/mysql/my.cnf

Change parameter datadir=/var/lib/mysql to datadir=/var/lib/mysql2 (or just add if you don't have)

  1. mv /var/lib/mysql /var/lib/mysql2

Rename datadir to a new name

  1. service mysql start

Prepare your tambourine

0

If none of the other solutions work, the problem probably stems from AppArmor misconfiguration.

So just do:

$ apt install apparmor-profiles

and then restart MySQL (notice how fast it'll restart).

I noticed a file missing related to AppArmor when doing:

$ systemctl status mysql.service

Hence why I thought something was wrong with AppArmor's configuration.

Not the answer you're looking for? Browse other questions tagged or ask your own question.