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
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
To show all the directories including data directory
SHOW VARIABLES WHERE Variable_Name LIKE "%dir"
or, you can use the following command
Using the CONF file
You can find your data directory from the MYSQL configuration file
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
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
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
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.
Signup for Exclusive "Subscriber-only" Content