40

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?

  • And which version of MySQL is this? – Michael Hampton Feb 10 '13 at 22:12
  • 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
  • @MichaelHampton The post has been edited (mySQL version 5.5.9 and increased the log). – Devator Feb 10 '13 at 22:30
  • Did you check that there was no running mysqld before starting it? How? – symcbean Feb 11 '13 at 0:52

10 Answers 10

31

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

  • 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. – Doyle Lewis Dec 26 '15 at 16:47
27

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 
  • 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? – a CVn Mar 15 '16 at 20:04
17

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.

  • 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 9:06
11

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

  • 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
  • 2
    Many thanks, it helped. But why? – DaviAragao Sep 24 '15 at 11:13
  • 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
2

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
  • 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
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.

  • 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
1

Check space to make sure it is 100%

df -h

As if its full it wont create .sock file.

  • The answer is about a surprising side-effect, I strongly disagree it would be LQ. – peterh Feb 4 '17 at 19:26
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.

protected by Sven Feb 4 '17 at 16:15

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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