Mysql – Recover an Innodb thesql database from an EBS on Ec2

amazon ec2innodbMySQLUbuntu

My instance crashed on AWs, and i had to create a new one and mount my Vol that contain my web application and the Mysql Database.

After i stoped the Mysql Server i mounted the vol and also mounted mysql folders from the colume to /var/lib/mysq, /var/log/mysal and /etc/mysql using this command lines:

echo "/mnt/vol/etc/mysql /etc/mysql     none bind" | sudo tee -a /etc/fstab
sudo mount /etc/mysql

echo "/mnt/vol/var/lib/mysql /var/lib/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /var/lib/mysql

echo "/mnt/vol/var/log/mysql /var/log/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /var/log/mysql

[Tutorial ref : aws.amazon.com/articles/1663 ]

Then i tried to run the Mysql again, with :

# service mysql start

but with no joy.

And when i run mysqld i got this errors messages :

# mysqld
140123 19:34:56 [Warning] Can't create test file /mnt/vol/var/lib/mysql/ip-15-120-63-11.lower-test
140123 19:34:56 [Warning] Can't create test file /mnt/vol/var/lib/mysql/ip-15-120-63-11.lower-test
140123 19:34:56 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
140123 19:34:56 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
140123 19:34:56  InnoDB: Initializing buffer pool, size = 8.0M
140123 19:34:56  InnoDB: Completed initialization of buffer pool
140123 19:34:56  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

I can see Permission issue for InnoDB! but what i am curious about is that i get this erro when i try to run my mysql command

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

Solution :

  • Step 1

Now Mysql have an apparmor profile enabled by default that will prevent mysql to access non standard area of the file system.

To fix this, i had to add /mnt/vol/var/lib/mysql (or whatever non standard path you are using) to the list of authorized paths in the apparmor mysql profile you will find in /etc/apparmor.d/.

In my case i found it under :

# sudo vi /etc/apparmor.d/usr.sbin.mysqld

NB: You can also put mysql profile in complain mode using "sudo aa-complain" as described in the server guide should show you this in the logs.

  • Step 2

Run Mysqld to be sure that everything is okey:

# mysqld
  • Step 3

Run mysql and check the it status :

# sudo service mysql start
# sudo service mysql status

You have to find your Mysql up and running now.
You canalso check mysql log file for more details under /var/log/mysql/error.log

Best Answer

InnoDB: Cannot continue operation.

The MySQL server is refusing to start, so it's not running, and you're not going to be able to connect with the mysql command line utility until it is... but you don't need it running to fix the permissions.

The permissions you need to fix are filesystem permissions, with some variant of this: 

$ sudo chmod -R mysql:mysql /var/lib/mysql
$ sudo chmod -R mysql:mysql /var/log/mysql

(Don't copy/paste this unless you are clear on what it does.)

After that, you should be able to start the server if the files are intact and the paths were correct.

Also, I'm not sure where you found the steps you originally used, but I assume the purpose of this exercise is to get the server running again so you can make a backup, after which you'll stop it and unmount those "bind" mounts.

Related Question