2

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.)

3 Answers 3

7

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).

4
  • Wow, never heard of AppArmor before. What exactly is the downside to disabling it? I suspect in this case it might not matter much if at all. (The MySQL db can only be accessed by a local process, and the whole instance can only be accessed from certain selected IP addresses...)
    – Jonik
    Sep 22, 2011 at 9:53
  • 1
    AppArmor is an important part of the overall security protection of your server. It helps limit the extent of damage from potential compromises of various pieces of software. AppArmor configurations cover far more than just MySQL. The workaround in (3) is fairly straight-forward and even eliminates the need to edit the MySQL configuration. Sep 22, 2011 at 10:11
  • Ok, thanks, I'll try the mount bind approach. By the way, could putting mysqld in complain mode (instead of enforce mode) in AppArmor, as described here, also be a viable workaround?
    – Jonik
    Sep 22, 2011 at 11:21
  • Got MySQL working using the mount bind trick. Thanks again!
    – Jonik
    Sep 22, 2011 at 12:10
3

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

sudo chown -R mysql:mysql /var/lib/mysql2/
3
  • Well, I wish it was this easy, but no, this didn't help. Note that I used cp -a to create the copy, so permissions were already exactly like in the original directory.
    – Jonik
    Sep 22, 2011 at 9:24
  • 1
    Double check with ls -la /var/lib/mysql2/.
    – quanta
    Sep 22, 2011 at 9:35
  • 1
    I already triple-checked before asking the question - the issue is not as obvious as that. But I think Eric Hammond is on to it
    – Jonik
    Sep 22, 2011 at 9:56
0

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)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .