How to change the MySQL database file directory in Ubuntu

Preface

The company's Ubuntu server places the directories of various systems on different logical partitions. For example, the default directory /var/lib/mysql for storing MySQL database files is located in a separate partition, and the system directory /var/ is located in a separate partition. However, the size of this partition is fixed and is not enough to store the entire database file. However, the size of another system directory /data reaches T level, which greatly meets the needs, so there is a need to change the database file directory.

The following is the process I tried by referring to some articles on the Internet:

1. Stop the database service:

Use /etc/init.d/mysql stop or stop mysql

2. Create a directory for the database file (such as /mysqldb) at the target location (/data) and copy (if you no longer use the default location, you can directly mv the original database file to the new directory) the original database file directory to this directory:

cd /data

mkdir mysqldb

cp -r /var/lib/mysql /data/mysqldb/

After a period of waiting, there is a copy of the original default mysql database file directory "/mysql" under /data/mysqldb/

3. Modify the my.cnf file

# vim /etc/mysql/my.cnf

Change datadir = /var/lib/mysql datadir = /data/mysqldb/mysql

In addition, since the current my.cnf has socket = /var/run/mysqld/mysqld.sock (not sock = /var/lib/mysql/mysql.sock as mentioned on the Internet), that is, the location of the socket is not where the database file is located, you do not need to do what other web pages say, and use the following command to make a mysql.sock link:

ln -s /data/mysqldb/mysql/mysql.sock /var/lib/mysql/mysql.sock (need to copy a copy from /home/data/mysql)

4. Modify the database permissions:

# chown -R mysql:mysql /data/mysqldb/mysql/ ← Change the ownership of the database file directory to mysql

# chmod 700 /data/mysqldb/mysql/whois/ ← Change the whois attribute of the database directory to 700

# chmod 660 /data/mysqldb/mysql/whois/* ← Change the attribute of the data table in the database to 660

5. Modify the file usr.sbin.mysqld

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

Bundle

/var/lib/mysql r,

/var/lib/mysql/** rwk,

Change to

/data/mysqldb/mysql/ r,

/data/mysqldb/mysql/** rwk,

Note: Without this step, the database service will not be able to restart. It seems to be restarting, but it is stuck and unresponsive.

6. Start mysql server

/etc/init.d/apparmor restart

/etc/init.d/mysql restart (or use restart mysql)

Done!

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.