How to handle InnoDB: Unable to lock ibdata1 error - MYSQL

I have recently migrated a self-hosted MySQL from an EC2 instance to another using AMI copy and restore.

and I have seen this error on the newly launched EC2 instance.

Coming from a no-DBA background, I spent a day finding the root cause of this

I am putting it here in the hope it would help someone in the future.

 

This is the exception I have seen in /var/log/mysql/error.log file

2023-02-23T12:25:18.105758Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2023-02-23T12:25:18.105772Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same
InnoDB data or log files.
2023-02-23T12:25:19.106893Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2023-02-23T12:25:19.106909Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same
InnoDB data or log files.
2023-02-23T12:25:20.108033Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2023-02-23T12:25:20.108052Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same
InnoDB data or log files.
2023-02-23T12:25:21.109220Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2023-02-23T12:25:21.109233Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

Let's decode this error a bit and understand what is ibdata and what could be the root cause of this error.

 

What is ibdata1

In MySQL, ibdata1 is a single system tablespace data file. The file is used to store data and indexes of all the tables of a MySQL database running on an InnoDB storage engine.

Mostly this file is found in the data directory of the MySQL

In most cases, the data directory would be /var/lib/mysql but in some special cases the data directory can be somewhere else

So when you are trying to find this file make sure you check what is your data directory

 

How to find my data directory location

You can find the data directory with either of these two things

Using SQL

To show all the directories including data directory

SHOW VARIABLES WHERE Variable_Name LIKE "%dir"

or, you can use the following command

SELECT @@datadir;

 

Using the CONF file

You can find your data directory from the MYSQL configuration file /etc/mysql/mysql.conf.d/mysqld.cnf 

By default, the data directory would be /var/lib/mysql as mentioned earlier but it can be changed directly in this file.

 

Check permissions of ibdata - Problem1

Having found the ibdata file location you can validate the permissions of the ibdata1 file using simple linux rwx permissions

make sure it has the right ownership and in most cases mysql should be the owner and group of this file

 

Check app-armor status  - Problem2

When you are having app armor installed in linux you would havesome file permission constraints that might block the process (mysql) to start

To check if you have AppArmor installed and its status use the following command

sudo aa-status

If mysql is part of the app armor you might find a file named usr.sbin.mysqld under /etc/apparmor.d/ directory

with in the usr.sbin.mysqld file you might have similar entries with set of permission constraints

root@mysqlserver:/etc/apparmor.d# grep -i "var/lib/mysql" usr.sbin.mysqld
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
/var/lib/mysql-files/ r,
/var/lib/mysql-files/** rwk,
/var/lib/mysql-keyring/ r,
/var/lib/mysql-keyring/** rwk,

In case your data directory is different, you should add the directory path into this file as follows

/my/custom/mysql/datadir/ r,
/my/custom/mysql/datadir/** rwk,

Once you have modified the file you can reload your AppArmor with the following command

 sudo service apparmor reload

Now you can check the status once again with the following command

aa-status

 

 

Now try to restart the mysql

After validating the data directory and its permission as well as  app armour policies, you can try to now restart MySQL and this error might get resolved

Or at least it was solved in my case.

Hope it helps someone in the future.

 

Cheers

Sarav AK

---