MySQL does not start - Ubuntu 14.04 LTS after migration to new disk

Question

I have installed in a Virtualbox VM a 64-bit desktop version of Ubuntu 14.04 LTS and MySQL Server 5.6. I had some databases in the machine's main virtual hard disk but then I was going to open a SQL dump with a size of 6 GB, and the disk didn't have enough space to open it. So, I decided to create a new virtual disk (13 GB) and attach it to the VM.

After formatting the new disk I created a new folder called mysql-db-new in the new volume. Now I will give you the entire process I did to migrate the databases from the main volume to the new volume:

1. Stop MySQL server:

   sudo /etc/init.d/mysql stop
   

2. Move MySQL data to the new drive:

   sudo mv /var/lib/mysql /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/
   

3. Edit /etc/mysql/my.cnf to point the new location (initially /var/lib/mysql):

   datadir = /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql
   

4. Configure AppArmor with the new path:

   sudo gedit /etc/apparmor.d/usr.sbin.mysqld
   

   All lines with /var/lib/mysql were replaced with the new location /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

5. Change permissions and group of the new location:

   sudo chgrp mysql /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql
   
   sudo chmod 755 /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql
   

6. Restart MySQL:

   sudo /etc/init.d/mysql start
   

Then after all this steps, MySQL start failed. Then I watched the /var/log/mysql/error.log and this is the output:

140904 15:25:14 mysqld_safe Starting mysqld daemon with databases from /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql
140904 15:25:14 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
140904 15:25:14 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140904 15:25:14 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Table 'plugin' is read only
140904 15:25:14 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
140904 15:25:14 InnoDB: The InnoDB memory heap is disabled
140904 15:25:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140904 15:25:14 InnoDB: Compressed tables use zlib 1.2.8
140904 15:25:14 InnoDB: Using Linux native AIO
140904 15:25:14 InnoDB: Initializing buffer pool, size = 128.0M
140904 15:25:14 InnoDB: Completed initialization of buffer pool
140904 15:25:14 InnoDB: highest supported file format is Barracuda.
140904 15:25:14  InnoDB: Waiting for the background threads to start
140904 15:25:15 InnoDB: 5.5.38 started; log sequence number 1637757
140904 15:25:15 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
140904 15:25:15 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
140904 15:25:15 [Note] Server socket created on IP: '127.0.0.1'.
140904 15:25:15 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'host' is read only
140904 15:25:15 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

So I don't know what's really going on. My first thought was on the permissions but I gave enough permissions on the new MySQL database location folder.

I will appreciate your help.

Answer 1

You only changed the ownership and permissions for the mysql datadir, not all the files below it. You should apply those changes recursively with the -R option.

I would do it this way:

$ sudo chown -R mysql:mysql /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

$ sudo chmod -R g+rwX /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

The datadir doesn't need to be other-accessible.

---

Also make sure that the full chain of directories above (/media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/) are readable & executable by mysql:mysql.

Answer 2

I also face this problem.My solution is to give all the 'r' and 'x' permissions for the target datadir's parent path and it work.