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 the terminal
• 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_datadir using 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 called AppArmor that 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/mysql and 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 article How 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.