ubuntu server 14.04修改mysql数据库目录

mysql

参考https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04

安装mysql

sudo apt-get install mysql-server

安装过程中设置mysql root用户的密码

安装完成后

使用mysql -u root -p进入mysql的命令行

可以查看当前的datadir

mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

mysql> 
mysql> quit;
Bye

也可以直接查看my.conf配置文件/etc/mysql/my.cnf

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

停止mysql服务

sudo service mysql stop

同步数据库文件到新的目录

sudo rsync -av /var/lib/mysql /data

可以看到mysql目录已经同步到了/data/目录下

并且目录和文件的权限等保持不变

drwx------  4 mysql mysql 4096 Nov 20 22:09 mysql/

修改mysql的配置文件

将datadir修改为

#datadir                = /var/lib/mysql
datadir         = /data/mysql

修改apparmor的控制规则

在文件/etc/apparmor.d/tunables/alias后面添加

以便mysqld能够读写新的目录

alias /var/lib/mysql/ -> /data/mysql/,

因为mysql的配置中是/var/lib/mysql/

$ cat /etc/apparmor.d/usr.sbin.mysqld 
# vim:syntax=apparmor
# Last Modified: Tue Jun 19 17:37:30 2007
#include <tunables/global>

/usr/sbin/mysqld {
  #include <abstractions/base>
  #include <abstractions/nameservice>
  #include <abstractions/user-tmp>
  #include <abstractions/mysql>
  #include <abstractions/winbind>

  capability dac_override,
  capability sys_resource,
  capability setgid,
  capability setuid,

  network tcp,

  /etc/hosts.allow r,
  /etc/hosts.deny r,

  /etc/mysql/*.pem r,
  /etc/mysql/conf.d/ r,
  /etc/mysql/conf.d/* r,
  /etc/mysql/*.cnf r,
  /usr/lib/mysql/plugin/ r,
  /usr/lib/mysql/plugin/*.so* mr,
  /usr/sbin/mysqld mr,
  /usr/share/mysql/** r,
  /var/log/mysql.log rw,
  /var/log/mysql.err rw,
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/lib/mysql-files/ r,
  /var/lib/mysql-files/** rwk,
  /var/log/mysql/ r,
  /var/log/mysql/* rw,
  /var/run/mysqld/mysqld.pid rw,
  /var/run/mysqld/mysqld.sock w,
  /run/mysqld/mysqld.pid rw,
  /run/mysqld/mysqld.sock w,

  /sys/devices/system/cpu/ r,

  # Site-specific additions and overrides. See local/README for details.
  #include <local/usr.sbin.mysqld>
}

否则mysql无法启动

错误日志/var/log/mysql/error.log

161120 22:51:43 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
161120 22:51:43 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
161120 22:51:43 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
161120 22:51:43 InnoDB: The InnoDB memory heap is disabled
161120 22:51:43 InnoDB: Mutexes and rw_locks use GCC atomic builtins
161120 22:51:43 InnoDB: Compressed tables use zlib 1.2.8
161120 22:51:43 InnoDB: Using Linux native AIO
161120 22:51:43 InnoDB: Initializing buffer pool, size = 128.0M
161120 22:51:43 InnoDB: Completed initialization of buffer pool
161120 22:51:43  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.
161120 22:51:44 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
161120 22:51:44 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
161120 22:51:44 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
161120 22:51:44 InnoDB: The InnoDB memory heap is disabled
161120 22:51:44 InnoDB: Mutexes and rw_locks use GCC atomic builtins
161120 22:51:44 InnoDB: Compressed tables use zlib 1.2.8
161120 22:51:44 InnoDB: Using Linux native AIO
161120 22:51:44 InnoDB: Initializing buffer pool, size = 128.0M
161120 22:51:44 InnoDB: Completed initialization of buffer pool
161120 22:51:44  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.
161120 22:51:45 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
161120 22:51:45 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
161120 22:51:45 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
161120 22:51:45 InnoDB: The InnoDB memory heap is disabled
161120 22:51:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins
161120 22:51:45 InnoDB: Compressed tables use zlib 1.2.8
161120 22:51:45 InnoDB: Using Linux native AIO
161120 22:51:45 InnoDB: Initializing buffer pool, size = 128.0M
161120 22:51:45 InnoDB: Completed initialization of buffer pool
161120 22:51:45  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.

使apparmor的修改生效

sudo service apparmor reload

启动mysql服务

sudo service mysql start

可以看到datadir已经变为/data/mysql/

mysql> select @@datadir;
+--------------+
| @@datadir    |
+--------------+
| /data/mysql/ |
+--------------+
1 row in set (0.00 sec)