66

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?

Improve this question
3
  • 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, 2013 at 22:14
  • 1
    Did you check that there was no running mysqld before starting it? How?
    – symcbean
    Feb 11, 2013 at 0:52
  • In my case, it happened because both docker and kubernetes were trying to access the same MySQL data location.
    – user1012513
    Jan 6, 2022 at 16:20

12 Answers 12

Reset to default
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

Improve this answer
2
  • 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, 2015 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, 2023 at 16:54
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
Improve this answer
7
  • 1
    @too askubuntu.com/questions/2075/…
    – mloskot
    Oct 31, 2015 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, 2016 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, 2016 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, 2016 at 20:04
  • This worked for me
    – Matt Korostoff
    Aug 8, 2017 at 20:31
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

archived link: https://web.archive.org/web/20120718013320/http://cglreport.zhenhua.info/2008/08/mysql-error-unable-to-lock-ibdata1.html

Improve this answer
5
  • magically helped me.
    – nils petersohn
    May 19, 2015 at 14:39
  • what is the point of cp -a ?, I've already read man page
    – Ilja
    May 20, 2015 at 12:09
  • 3
    Many thanks, it helped. But why?
    – DaviAragao
    Sep 24, 2015 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, 2018 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, 2022 at 3:48
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.

Improve this answer
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, 2013 at 22:26
  • @MichaelHampton can you give little more info about 'world of hurt' and 'start MySQL manually'? Thanks)
    – Serge Kvashnin
    Dec 31, 2013 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
Improve this answer
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!
    – Kyle Burkett
    Jun 20, 2016 at 14:48
3

Check space to make sure it is 100%

df -h

As if its full it wont create .sock file.

Improve this answer
1
  • The answer is about a surprising side-effect, I strongly disagree it would be LQ.
    – peterh
    Feb 4, 2017 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.

Improve this answer
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.

Improve this answer
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, 2016 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.

Improve this answer
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

Improve this answer
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.

Improve this answer
0

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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .