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:
Stop MySQL server:
sudo /etc/init.d/mysql stop
Move MySQL data to the new drive:
sudo mv /var/lib/mysql /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/
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
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
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
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.