返回
网站建设知识
ubuntuserver14.04修改mysql数据库目录
2025-07-22 10:01  点击:1

安装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 = mysqlpid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockport = 3306basedir = /usrdatadir = /var/lib/mysqltmpdir = /tmplc-messages-dir = /usr/share/mysqlskip-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/usr/sbin/mysqld {#include#include#include#include#includecapability 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}

否则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 disabled161120 22:51:43 InnoDB: Mutexes and rw_locks use GCC atomic builtins161120 22:51:43 InnoDB: Compressed tables use zlib 1.2.8161120 22:51:43 InnoDB: Using Linux native AIO161120 22:51:43 InnoDB: Initializing buffer pool, size = 128.0M161120 22:51:43 InnoDB: Completed initialization of buffer pool161120 22:51:43 InnoDB: Operating system error number 13 in a file operation.InnoDB: The error means mysqld does not have the access rights toInnoDB: the directory.InnoDB: File name ./ibdata1InnoDB: 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 disabled161120 22:51:44 InnoDB: Mutexes and rw_locks use GCC atomic builtins161120 22:51:44 InnoDB: Compressed tables use zlib 1.2.8161120 22:51:44 InnoDB: Using Linux native AIO161120 22:51:44 InnoDB: Initializing buffer pool, size = 128.0M161120 22:51:44 InnoDB: Completed initialization of buffer pool161120 22:51:44 InnoDB: Operating system error number 13 in a file operation.InnoDB: The error means mysqld does not have the access rights toInnoDB: the directory.InnoDB: File name ./ibdata1InnoDB: 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 disabled161120 22:51:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins161120 22:51:45 InnoDB: Compressed tables use zlib 1.2.8161120 22:51:45 InnoDB: Using Linux native AIO161120 22:51:45 InnoDB: Initializing buffer pool, size = 128.0M161120 22:51:45 InnoDB: Completed initialization of buffer pool161120 22:51:45 InnoDB: Operating system error number 13 in a file operation.InnoDB: The error means mysqld does not have the access rights toInnoDB: the directory.InnoDB: File name ./ibdata1InnoDB: 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)