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

MySQLmysql-5.6

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.

Best Answer

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.

Related Question