MySQL数据迁移到新目录_mysql数据目录迁移

Ubuntu 14.04*64

mysql  Ver 14.14 Distrib 5.5.49, for debian-linux-gnu (x86_64) using readline 6.3

一、无需通过任何工具增加数据库和数据表，包括从其他地方复制数据库

1、在mysql数据目录下新建一个目录，该目录命名成你的数据库名；

2、从其他机器拷贝数据表，注意：要拷贝*.frm/*.MYD/*.MYI三个文件；

此时使用mysql登录，使用show databases可以看到新加入的目录为新的数据库名，通过use newdatabase和show tables都可以正常使用，但是“select * from 表名”提示如下错误：ERROR 1017 (HY000): Can't find file: './newdatabase/tablename.frm' (errno: 13)，这个问题可以通过第三步解决。

3、修改新建的目录(数据库)的所有权：chown -R mysql:root  newdatabase

4、通过mysql登录检查是否正常，经验证正常

二、整个数据库迁移

原文：http://article.my-addr.com/?show=linux_ubuntu_change_datadir-move_mysql_database_to_other_path

Open theterminal

Stop MySQL with the command

1、 /etc/init.d/mysql stop

Move existing data directory (which is located in/var/lib/mysql) to new dir/usr/new_datadirusing the command

2、 mv /var/lib/mysql /usr/new_datadir

Create symlink from new dir to old one

3、 ln -s /usr/new_datadir /var/lib/mysql

Don't chnage/etc/mysql/my.cnf=)))

Ubuntu uses some security software calledAppArmorthat specifies the areas of your filesystem applications are allowed to access. Unless you modify the AppArmor profile for MySQL, you'll never be able to restart MySQL with the new datadir location.

In the terminal, enter the command

4、 vim /etc/apparmor.d/usr.sbin.mysqld

Duplicate the lines beginning with/var/lib/mysqland replace duplicated strings with/usr/new_datadirIn my case it was:

/var/lib/mysql/ r,

/var/lib/mysql/** rwk,

#

/usr/new_datadir r,

/usr/new_datadir** rwk,

Restart the AppArmor profiles with the command

5、 /etc/init.d/apparmor restart

Restart MySQL with the command

6、/etc/init.d/mysql restart

MySQL should now start without any errors, have fun! See also articleHow to move the mysql datadir, Ubuntu change datadir issue 经测试该步骤是正确的

注意：在第二步如果使用cp的话，会生成新的目录，这样需要通过chown -R mysql:mysql /usr/new_datadir修改目录的权限才能成功启动，否则在/var/log/mysql/error.log中会报如下错误：

InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation.