两种方法修改ubuntu下mysql 的data文件夹

最新推荐文章于 2024-11-24 17:48:35 发布

原创最新推荐文章于 2024-11-24 17:48:35 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #ubuntu #command #数据库 #terminal #delete

本文介绍如何在Ubuntu系统中更改MySQL数据库文件的存储位置，包括停止服务、复制数据目录、修改配置文件、调整AppArmor安全策略等步骤。

一

1、关掉数据库 sudo /etc/init.d/mysql stop

2、假设我们指定的数据库文件目录为/db
创建目录 /db,并修改其拥有者及所属群组为mysql:mysql.命令：chown mysql:mysql db
修改mysql配置文件my.cnf：将datadir=/var/lib/mysql改为datadir=/db

3、修改ubuntu中的安全设置
sudo gedit /etc/apparmor.d/usr.sbin.mysqld 在这个文件里面加入权限设定，将原来的
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
更换成
/db/mysql/ r,
/db/mysql/** rwk,

4、重新初始化数据文件：执行sudo mysql_install_db

5、启动mysql数据库服务：sudo /etc/init.d/mysql start

6、设置mysql数据库root密码：mysqladmin -u root password ‘new-password’

7、在/etc/mysql/debian.cnf中找到你的debian-sys-maint用户密码，在mysql中重新建立这个用户.

此时我们会发现/db目录下出现mysql相关文件。如果我们新建数据库，其数据库文件也将出现在db目录下。表示我们修改的mysql数据库文件存储目录正确。

二

* Open the terminal
* Stop MySQL with the command

/etc/init.d/mysql stop

* Copy the existing data directory (which is located in /var/lib/mysql) using the command

cp -R -p /var/lib/mysql /usr/new_datadir

* All you need are the data files. Delete the others with the command

rm /usr/new_datadir

(You will get a message about not being able to delete some directories, but do not care about them)
* Edit the MySQL configuration file with the command

vim /etc/mysql/my.cnf

    * Find the entry for datadir, change the path to the new data directory.
    * But there is a trick involved here. 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

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

    * Copy the lines beginning with /var/lib/mysql
    * Comment out the originals with hash marks (#), and paste the lines below the originals.
    * Now change /var/lib/mysql in the two new lines with /usr/new_datadir. Save and close the file.
    * Restart the AppArmor profiles with the command

/etc/init.d/apparmor restart

* Restart MySQL with the command

/etc/init.d/mysql restart

* MySQL should now start without any errors and your data will be stored in the new location. See also article Linux Ubuntu move mysql database to other path in 5 minutes