Moving a mysql innodb data partition from one computer to the other

Question

/etc/mysql/conf.d/myserver:

[mysqld]
innodb_file_per_table
datadir          = /elastidb/lib/mysql
#log-bin          = /elastiblockdb/log/mysql/mysql-bin.log

general_log      = true
log = /elastiblockdb/log/mysql/mysql_general.log

log-error        = /elastiblockdb/log/mysql/mysql_error.log
max_binlog_size  = 1000M
log-slow-queries = /elastiblockdb/log/mysql/mysql-slow.log
long_query_time  = 10
innodb_buffer_pool_size = 4500M
innodb_additional_mem_pool_size = 200M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2

i have two identical ubuntu machine on amazon servers. two of the machines have identical /etc/mysql configuration files. the problem is that when i unmount /elastidb (mysql data) on one server and remount it on the other, when i try to start mysql on the other server i get the following error:

/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
091104 13:53:13 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
091104 13:53:14  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.
091104 13:53:14 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

when i remove the directive datadir from my configuration file then mysql server starts but without loading the appropriate db data from my desired location.

I want to be able to quickly move that mysql data storage from one machine to the other. what may be the problem ?

both machines are on ubuntu 9.04 jaunty 64 bits.

Answer 1

1. Does the mysql/plugin.frm error occur on both machines? My guess is so.

   Did the datadir come from a pre-5.1 installation? Run mysql_upgrade to get rid of this.

2. Even when using innodb_file_per_table MySQL will still create a single shared tablespace.

   The lack of innodb_data_home_dir and innodb_data_file_path variables in the config you're provided should mean that a 10M auto-extending tablespace is created under your datadir. But if the second machine is complaining about it missing then it suggests that maybe it isn't under your datadir.

   I'd suggest having a hunt around for this file (ibdata1) on the first (working) machine. If it exists outside the datadir, then shutdown MySQL, move it and configure those variables.

Answer 2

I had a similar issue (same error message, but on Ubuntu server 10.04, and I changed the mysql datadir to point at /opt/mysql).

The problem was that apparmor was blocking attempts by /usr/sbin/mysqld to read /opt/mysql. I found lines like:

type=1503 audit(1291916635.209:18):  operation="open" pid=1201 parent=1 profile="/usr/sbin/mysqld" requested_mask="r::" denied_mask="r::" fsuid=105 ouid=105 name="/opt/mysql/mysql/plugin.frm"
mysql main process (928) terminated with status 1

In my /var/log/syslog.

I edited /etc/apparmor.d/usr.sbin.mysqld, replacing all instances of '/var/lib/mysql' with '/opt/mysql'.

After that mysql started properly.

Answer 3

In my case the problem was apparmor. Since my machine is purposed for testing only, I chose the simplest way for solving it:

apt-get remove apparmor

Answer 4

mysql/plugin.frm is a file that store in over data folder if u use to link mysql link c:\programdata\mysql\mysql server 5.1\data\dir if file not found then u reinstall the mysql it is visual basic file