1

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.

3

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.

  • I tried that solution, but It continues giving me the same errors. – Juan Jose Polanco Arias Sep 4 '14 at 20:55
  • I used the commands: sudo chown -R mysql:mysql /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/ and sudo chmod -R g+rwx /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/ with no luck. – Juan Jose Polanco Arias Sep 4 '14 at 21:24
  • 2
    At this point I'd try using sudo -u mysql -g mysql and see if you can read and write files under the datadir. It could still be permission problems in the ancestor directories, or it could be AppArmor interfering and giving misleading error messages. Try disabling AppArmor temporarily and see if that makes the errors go away. – Bill Karwin Sep 4 '14 at 22:06
  • What is your filesystem? Try creating a symbolic link to your new mysql-db-new as /var/lib/mysql and backout steps 3 and 4? Warning, total guess! – Vérace Sep 4 '14 at 23:39
-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.