How To Move a MySQL Data Directory to a New Location Linux Mint 18.2

2017-08-30

---

Introduction

I have been tinkering with MySQL databases since early 2000s. Early on I decided to store my data in a non-default directory to have an explicit location of my choosing. I find there are issues every time I port my data over to a fresh OS install. It was no different porting my data from Linux mint 17.2 to 18.2.

It took several hours to discover that a lot of effort was placed in linking MySQLs default data directory to /var/lib/mysql. First there are symbolic links that always point back to /var even after you change the data dir in my.cnf. Even more surprisingly, was the use of AppArmor to restrict this change. AppArmor is a Linux kernel security module that was released in 1998 so why Linux Mint used this to restrict data directory location is unknown to me. Anyhow because it caused at least an hour of lost time I wanted to document and increase visibility on this change.

Note, in my posts I will not be detailing basic commands such as cp or service stop/start. Also, I attempt to minimize the number of steps in the procedure to keep it basic. I want the procedure to be more of a future reminder rather than by a step-by-step tutorial for beginners.

Procedure

1. Shut down mysql daemon.

2. Move the default mysql data directory (/var/lib/mysql) to desired file location.

2021 - configuration file is 50-server.cnf

2021 - did not use apparmor but sudo vi /lib/systemd/system/mariadb.service ProtectHome setting

3. Change the data dir to the file location of choice by changing line [datadir=/var/lib/mysql]. In current version the configuration file is found [/etc/mysql/mysql.conf.d/mysqld.conf].

4. Open apparmor alias file found [/etc/apparmor.d/tunables/alias] and add line [alias /var/lib/mysql/ -› /desired/mysql].

5. Restart apparmor.

6. Must create minimal folder to ensure symbolic links are not broken during mysql daemon start-up.

7. Now start-up mysql daemon.

8. Log into mysql and check that it is refering to correct data location, using command [select @@datadir]

9. Copy all database directors and ensure ownership and permissions are set correctly.

2021-08-23. Need to go to /etc/mysql/mariadb.conf.d/50-server.cnf and /lib/systemd/system/mariadb.service.

Set ProtectHome=false

sudo systemctl daemon-reload

2024-02-03. These basic instructions still work for moving the home directory.

Conclusion

This procedure will move MySQL's data directory to new location and update Ubuntu's AppArmor ACLs to accomodate the adjustment. This procedure is valid for linux mint 18.2. As mentioned previously, procedure will likely require adjustment for other Ubuntu distributions and even for future Linux Mint releases.