MySQL keeps crashing: InnoDB: Unable to lock ./ibdata1, error: 11

Question

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?

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. — Krzysztof Księżyk, Feb 10 '13 at 22:14
Did you check that there was no running mysqld before starting it? How? — symcbean, Feb 11 '13 at 00:52
In my case, it happened because both docker and kubernetes were trying to access the same MySQL data location. — user1012513, Jan 06 '22 at 16:20

score 47 · Answer 1 · answered Feb 10 '13 at 23:04

47

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

answered Feb 10 '13 at 23:04

Brigo

1,534
11
8

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. – Arielle Lewis Dec 26 '15 at 16:47
Do not `kill -9`. It is almost always a bad idea. Use regular `kill` (aka `kill -TERM`). If that fails, try `kill -QUIT`. Only if that fails too, use `kill -9`, and then carefully check for inconsistencies afterwards. – Tilman Schmidt May 29 '23 at 16:54

score 30 · Answer 2 · answered Feb 10 '13 at 23:01

30

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

answered Feb 10 '13 at 23:01

Brigo

1,534
11
8

magically helped me. – nils petersohn 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
2

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
I am using kubernetes, so restarting `mysql`, or performing `service restart` is not an option, as it destroys the pod. However, if these files are in a persistence volue (which was my case), this is the answer you are looking for! worked flawlessly for me. – Prasannjeet Singh Dec 12 '22 at 03:48

score 30 · Answer 3 · edited Feb 06 '15 at 02:52

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

edited Feb 06 '15 at 02:52

Kate

652
5
18

answered Feb 05 '15 at 22:21

Jens

401
4
2

1

@too http://askubuntu.com/questions/2075/whats-the-difference-between-service-and-etc-init-d – 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 08 '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 08 '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
This worked for me – Matt Korostoff Aug 08 '17 at 20:31
tail -f /var/log/mysql/error.log # if you see here something like "another mysqld process using the same InnoDB data or log files", then there is another and you have to kill it e.g. via pkill mysql or service mysql restart – Lars Moelleken Feb 02 '18 at 08:14
@too The `service` autocompletion is available in Ubuntu 18.04, I am not sure at which version it was added, but it is available nowadays. I've always thought the `init.d` scripts are ugly, so I'm glad `service` is being used more and more – dtasev Oct 07 '20 at 14:04

score 22 · Answer 4 · answered Feb 10 '13 at 22:16

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.

answered Feb 10 '13 at 22:16

Michael Hampton

244,070
43
506
972

`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) – Serge Kvashnin Dec 31 '13 at 09:06

Ali Caner Öner · Answer 5 · 2016-01-13T11:36:06.707

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

edited Jan 13 '16 at 11:36

answered Jan 13 '16 at 11:28

Ali Caner Öner

171
1
2

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! – Kyle Burkett Jun 20 '16 at 14:48

score 3 · Answer 6 · edited Feb 04 '17 at 16:12

3

Check space to make sure it is 100%

df -h

As if its full it wont create .sock file.

edited Feb 04 '17 at 16:12

Uwe Keim

2,420
5
30
47

answered Feb 04 '17 at 14:30

RicardasSorryxas

39
1

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

score 2 · Answer 7 · answered Sep 28 '20 at 12:07

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.

score 1 · Answer 8 · answered Mar 15 '16 at 18:22

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.

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 08 '16 at 14:36

score 0 · Answer 9 · answered May 29 '23 at 11:58

Was facing a similar issue Unable to lock ./ibdata1, error: 11 while running MySQL in a container with EFS shared volume on AWS ECS (to persist MySQL between Instances lifecycle)

The issue was another Instance was accessing the volume filesystem, causing MySQL to not be able to lock the file

Solved by exiting the shared filesystem on that Instance, letting MySQL container lock it

score 0 · Answer 10 · edited Jan 24 '17 at 06:07

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.

edited Jan 24 '17 at 06:07

Slipeer

3,295
2
21
33

answered Jan 20 '17 at 19:24

Sachin

1

score 0 · Answer 11 · edited Mar 23 '17 at 11:41

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

service mysql stop && pkill -f mysql

Get rid of all mysql processes

vi /etc/mysql/my.cnf

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

mv /var/lib/mysql /var/lib/mysql2

Rename datadir to a new name

service mysql start

Prepare your tambourine

score 0 · Answer 12 · edited Jan 27 '18 at 10:57

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.

edited Jan 27 '18 at 10:57

Colt

2,029
6
21
27

answered Jan 26 '18 at 12:48

fevangelou

201
2
4

MySQL keeps crashing: InnoDB: Unable to lock ./ibdata1, error: 11

12 Answers12

Linked