Home / server / Questions / 314269
Accepted
Jonik
Asked: 2011-09-23 00:29:59 +0800 CST

Moving MySQL data to another directory

  • 772

I'm trying to move data files of a MySQL installation to another place, but it won't work.

When trying to start mysqld, I get this in /var/log/mysql/error.log:

110922  7:27:40 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
110922  7:27:40 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
110922  7:27:40  InnoDB: Initializing buffer pool, size = 512.0M
110922  7:27:40  InnoDB: Completed initialization of buffer pool
110922  7:27:40  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.

The above happens even if I try something as innocent as:

sudo cp -a /var/lib/mysql /var/lib/mysql2

...and change the datadir setting in /etc/mysql/my.cnf to /var/lib/mysql2

(I get the same result if I leave my.cnf as it is, and create a symlink named mysql2 to point to mysql.)

This is somewhat perplexing. File permissions are exactly the same in the copied datadir. And obviously I'm stopping/starting the daemon before doing these changes (sudo service mysql stop etc.) Any idea what I'm doing wrong?

This is Ubuntu 11.04 on Amazon EC2 (64-bit m1.large instance).

(In reality I'd like to move MySQL data to another EBS volume, to a path such as /mnt/data/mysql or /data/mysql, but the above minimal scenario suffices to reproduce the problem.)

ubuntu mysql amazon-ec2

3 Answers

  1. Best Answer

    You are running into AppArmor rules which forbid MySQL from opening files where you put them. If you check your system log files, you'll find a cryptic error message to this effect.

    Solutions include:

    1. Disable AppArmor (not recommended)

    2. Edit the AppArmor rules (complicated)

    3. Use mount bind to make MySQL think that your data files are in the original location while they are actually over on the EBS volume. Revert your changes to datadir.

    I wrote an article for Amazon years back describing community best practices for exactly what you are trying to do including the mount bind example:

    Running MySQL on Amazon EC2 with EBS
    http://ec2ebs-mysql.notlong.com

    Note that the AMI id in the article is old. Using a modern Ubuntu AMI, you'll need to replace /dev/sdh with /dev/xvdh in the mkfs.xfs and /etc/fstab (but not in the ec2 tools command lines).

    • 7

  2. InnoDB: The error means mysqld does not have the access rights to the directory.

    sudo chown -R mysql:mysql /var/lib/mysql2/
    • 3

  3. try to edit your /etc/apparmor.d/local/usr.sbin.mysqld file and put these lines:

    /var/lib/mysql2/ r,
/var/lib/mysql2/** rwk,

    and restart the service (/etc/init.d/mysql restart)

    • 0