如何更改 MySQL 数据目录？

How to change MySQL data directory?

提问by MySQL DBA

Is it possible to change my default MySQL data directory to another path? Will I be able to access the databases from the old location?

是否可以将我的默认 MySQL 数据目录更改为另一个路径？我可以从旧位置访问数据库吗？

回答by user1341296

1. Stop MySQL using the following command:

   sudo /etc/init.d/mysql stop
   

2. Copy the existing data directory (default located in /var/lib/mysql) using the following command:

   sudo cp -R -p /var/lib/mysql /newpath
   

3. edit the MySQL configuration file with the following command:

   sudo gedit /etc/mysql/my.cnf   # or perhaps /etc/mysql/mysql.conf.d/mysqld.cnf
   

4. Look for the entry for datadir, and change the path (which should be /var/lib/mysql) to the new data directory.

5. In the terminal, enter the command:

   sudo gedit /etc/apparmor.d/usr.sbin.mysqld
   

6. Look for lines beginning with /var/lib/mysql. Change /var/lib/mysqlin the lines with the new path.

7. Save and close the file.

8. Restart the AppArmor profiles with the command:

   sudo /etc/init.d/apparmor reload
   

9. Restart MySQL with the command:

   sudo /etc/init.d/mysql restart
   

10. Now login to MySQL and you can access the same databases you had before.

1. 使用以下命令停止 MySQL：

   sudo /etc/init.d/mysql stop
   

2. /var/lib/mysql使用以下命令复制现有数据目录（默认位于）：

   sudo cp -R -p /var/lib/mysql /newpath
   

3. 使用以下命令编辑 MySQL 配置文件：

   sudo gedit /etc/mysql/my.cnf   # or perhaps /etc/mysql/mysql.conf.d/mysqld.cnf
   

4. 查找 的条目datadir，并将路径（应该是/var/lib/mysql）更改为新数据目录。

5. 在终端中，输入命令：

   sudo gedit /etc/apparmor.d/usr.sbin.mysqld
   

6. 查找以/var/lib/mysql.开头的行。/var/lib/mysql使用新路径更改行。

7. 保存并关闭文件。

8. 使用以下命令重新启动 AppArmor 配置文件：

   sudo /etc/init.d/apparmor reload
   

9. 使用以下命令重新启动 MySQL：

   sudo /etc/init.d/mysql restart
   

10. 现在登录到 MySQL，您可以访问与以前相同的数据库。

回答by sMyles

Quick and easy to do:

快速简便的操作：

# Create new directory for MySQL data
mkdir /new/dir/for/mysql

# Set ownership of new directory to match existing one
chown --reference=/var/lib/mysql /new/dir/for/mysql

# Set permissions on new directory to match existing one
chmod --reference=/var/lib/mysql /new/dir/for/mysql

# Stop MySQL before copying over files
service mysql stop

# Copy all files in default directory, to new one, retaining perms (-p)
cp -rp /var/lib/mysql/* /new/dir/for/mysql/

Edit the /etc/my.cnffile, and under [mysqld]add this line:

编辑/etc/my.cnf文件，并在下面[mysqld]添加这一行：

datadir=/new/dir/for/mysql/

If you are using CageFS (with or without CloudLinux) and want to change the MySQL directory, you MUST add the new directory to this file:

如果您使用 CageFS（有或没有 CloudLinux）并想要更改 MySQL 目录，您必须将新目录添加到此文件：

/etc/cagefs/cagefs.mp

And then run this command:

然后运行这个命令：

cagefsctl --remount-all

回答by RageZ

you would have to copy the current data to the new directory and to change your my.cnfyour MySQL.

您必须将当前数据复制到新目录并更改您my.cnf的 MySQL。

[mysqld]
datadir=/your/new/dir/
tmpdir=/your/new/temp/

You have to copy the database when the server is not running.

您必须在服务器未运行时复制数据库。

回答by H6.

First you should stop the mysql server. e.g.

首先你应该停止mysql服务器。例如

# /etc/init.d/mysql stop

After that you should copy the old data directory (e.g. /var/lib/mysql) incl. privileges to your new directory via

之后，您应该复制旧数据目录（例如 /var/lib/mysql），包括。您的新目录的权限通过

# cp -R -p /var/lib/mysql /new/data/dir

now you can change in /etc/mysql/my.cnfthe data new and restart the server

现在您可以更改/etc/mysql/my.cnf新数据并重新启动服务器

# /etc/init.d/mysql restart

回答by Sirac

If like me you are on debian and you want to move the mysql dir to your home or a path on /home/..., the solution is :

如果像我一样使用 debian 并且想将 mysql 目录移动到您的家或 /home/... 上的路径，则解决方案是：

• Stop mysql by "sudo service mysql stop"
• change the "datadir" variable to the new path in "/etc/mysql/mariadb.conf.d/50-server.cnf"
• Do a backup of /var/lib/mysql : "cp -R -p /var/lib/mysql /path_to_my_backup"
• delete this dir : "sudo rm -R /var/lib/mysql"
• Move data to the new dir : "cp -R -p /path_to_my_backup /path_new_dir
• Change access by "sudo chown -R mysql:mysql /path_new_dir"
• Change variable "ProtectHome" by "false" on "/etc/systemd/system/mysqld.service"
• Reload systemd by "sudo systemctl daemon-reload"
• Restart mysql by "service mysql restart"

• 通过“sudo service mysql stop”停止 mysql
• 将“datadir”变量更改为“/etc/mysql/mariadb.conf.d/50-server.cnf”中的新路径
• 备份 /var/lib/mysql : "cp -R -p /var/lib/mysql /path_to_my_backup"
• 删除这个目录：“sudo rm -R /var/lib/mysql”
• 将数据移动到新目录：“cp -R -p /path_to_my_backup /path_new_dir
• 通过“sudo chown -R mysql:mysql /path_new_dir”更改访问权限
• 在“/etc/systemd/system/mysqld.service”上将变量“ProtectHome”更改为“false”
• 通过“sudo systemctl daemon-reload”重新加载systemd
• 通过“service mysql restart”重启mysql

One day to find the solution for me on the mariadb documentation. Hope this help some guys!

有一天在 mariadb 文档上为我找到了解决方案。希望这可以帮助一些人！

回答by wfbarksdale

I wanted to keep a database on my machine, but also have a data on my external hard drive, and switch between using the two.

我想在我的机器上保留一个数据库，但在我的外部硬盘驱动器上也有一个数据，并在两者之间切换。

If you are on a Mac, and installed MySQL using Homebrew, this should work for you. Otherwise, you will just need to substitute the appropriate locations for the MySQL datadiron your machine.

如果您使用的是 Mac，并且使用 Homebrew 安装了 MySQL，那么这应该适合您。否则，您只需要datadir在您的机器上替换 MySQL 的适当位置。

#cd to my data dir location
cd /usr/local/var/

#copy contents of local data directory to the new location
cp -r mysql/ /Volumes/myhd/mydatadir/

#temporarily move the old datadir
mv mysql mysql.local

#symlink to the new location
ln -s /Volumes/myhd/mydatadir mysql

Then to when you want to switch back simply do:

然后当你想切换回来时，只需执行以下操作：

mv mysql mysql.remote

mv mysql.local mysql

and you are using your local database again. Hope that helps.

并且您再次使用本地数据库。希望有帮助。

回答by Murlo

First stop mysqld

第一站mysqld

mysql_install_db --user=mysql \
                 --basedir=/opt/mysql/mysql \
                 --datadir=/opt/mysql/mysql/data

Then change datadir in your /etc/mysql/my.cnf
Start mysqld

然后在您的/etc/mysql/my.cnf
Start mysqld 中更改 datadir

Notes:
#1: probably you have to adjust your SELinux settings (try out with SELinux disabled in case of troubles), Apparmor (Ubuntu) may also be issue.

注意：
#1：可能您必须调整您的 SELinux 设置（如果出现问题，请尝试禁用 SELinux），Apparmor (Ubuntu) 也可能有问题。

#2: see MySQL Install DB Reference

#2：参见MySQL 安装数据库参考

回答by naddame

First stop your mysql

首先停止你的mysql

sudo service mysql stop

copy mysql data to the new location.

将mysql数据复制到新位置。

sudo cp -rp /var/lib/mysql /yourdirectory/

if you use apparmor, edit the following file and do the following

如果您使用apparmor，请编辑以下文件并执行以下操作

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Replace where /var/lib/ by /yourdirectory/ then add the follwoing if no exist to the file

将 where /var/lib/ 替换为 /yourdirectory/ 然后将以下内容添加到文件中（如果不存在）

    /yourdirectory/mysql/ r,
    /yourdirectory/mysql/** rwk,

Save the file with the command

使用命令保存文件

:wq

Edit the file my.cnf

编辑文件 my.cnf

sudo vim /etc/mysql/my.cnf

Replace where /var/lib/ by /yourdirectory/ then save with the command

用 /yourdirectory/ 替换 where /var/lib/ 然后用命令保存

:wq

finally start mysql

最后启动mysql

sudo service mysql start

@see more about raid0, optimization ici

@查看更多关于raid0，优化ici

回答by Rob

This solution works in Windows 7 using Workbench. You will need Administrator privileges to do this. It creates a junction (like a shortcut) to wherever you really want to store your data

此解决方案适用于使用 Workbench 的 Windows 7。您将需要管理员权限才能执行此操作。它创建了一个连接（就像一个快捷方式）到你真正想要存储数据的任何地方

Open Workbench and select INSTANCE - Startup / Shutdown Stop the server

打开 Workbench 并选择 INSTANCE - Startup / Shutdown 停止服务器

Install Junction Master from https://bitsum.com/junctionmaster.php

从https://bitsum.com/junctionmaster.php安装 Junction Master

Navigate to C:\ProgramData\MySQL\MySQL Server 5.6

导航到 C:\ProgramData\MySQL\MySQL Server 5.6

Right click on Data and select "MOVE and then LINK folder to ..." Accept the warning Point destination to "Your new data directory here without the quotes" Click MOVE AND LINK

右键单击数据并选择“移动，然后将文件夹链接到...”接受警告将目标指向“您的新数据目录在此处不带引号”单击移动和链接

Now go to "Your new data directory here without the quotes"

现在转到“这里没有引号的新数据目录”

Right click on Data Go to the security tab Click Edit Click Add Type NETWORK SERVICE then Check Names Click OK Click the Allow Full Control checkbox and then OK

右键单击数据转到安全选项卡单击编辑单击添加类型网络服务然后检查名称单击确定单击允许完全控制复选框然后确定

Go back to Workbench and Start the server

返回 Workbench 并启动服务器

This method worked for me using MySQL Workbench 6.2 on Windows 7 Enterprise.

这种方法在 Windows 7 Enterprise 上使用 MySQL Workbench 6.2 对我有用。

回答by Pavel

Everything as @user1341296 said, plus...

@user1341296 所说的一切，加上......

You better not to change /etc/mysql/my.cnfInstead you want to create new file/etc/mysql/conf.d/ext.cnf(any name, but extension should be cnf)

您最好不要更改/etc/mysql/my.cnf而是要创建新文件/etc/mysql/conf.d/ext.cnf（任何名称，但扩展名应该是cnf）

And put in it your change:

并将您的更改放入其中：

[mysqld]
datadir=/vagrant/mq/mysql

In this way

通过这种方式

• You do not need to change existing file (easier for automation scripts)
• No problems with MySQL version (and it's configs!) update.

• 您不需要更改现有文件（自动化脚本更容易）
• MySQL 版本（以及它的配置！）更新没有问题。