Tutorial
How To Move a MySQL Data Directory to a New Location on Ubuntu 16.04
Not using Ubuntu 16.04?Choose a different version or distribution.
Introduction
Databases grow over time, sometimes outgrowing the space on the file system. You can also run into I/O contention when they’re located on the same partition as the rest of the operating system. RAID, network block storage, and other devices can offer redundancy and other desirable features. Whether you’re adding more space, evaluating ways to optimize performance, or looking to take advantage of other storage features, this tutorial will guide you through relocating MySQL’s data directory.
Prerequisites
To complete this guide, you will need:
-
An Ubuntu 16.04 server with a non-root user with
sudoprivileges. You can learn more about how to set up a user with these privileges in our Initial Server Setup with Ubuntu 16.04 guide. -
A MySQL server. If you haven’t already installed MySQL, the How To Install MySQL on Ubuntu 16.04 guide can help you.
In this example, we’re moving the data to a block storage device mounted at /mnt/volume-nyc1-01. You can learn how to set one up in the How To Use Block Storage on DigitalOcean guide.
No matter what underlying storage you use, this guide can help you move the data directory to a new location.
Step 1 — Moving the MySQL Data Directory
To prepare for moving MySQL’s data directory, let’s verify the current location by starting an interactive MySQL session using the administrative credentials.
- mysql -u root -p
When prompted, supply the MySQL root password. Then from the MySQL prompt, select the data directory:
- select @@datadir;
Output+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)
This output confirms that MySQL is configured to use the default data directory, /var/lib/mysql/, so that’s the directory we need to move. Once you’ve confirmed this, type exit to leave the monitor.
To ensure the integrity of the data, we’ll shut down MySQL before we actually make changes to the data directory:
- sudo systemctl stop mysql
systemctl doesn’t display the outcome of all service management commands, so if you want to be sure you’ve succeeded, use the following command:
- sudo systemctl status mysql
You can be sure it’s shut down if the final line of the output tells you the server is stopped:
Output. . .
Jul 18 11:24:20 ubuntu-512mb-nyc1-01 systemd[1]: Stopped MySQL Community Server.
Now that the server is shut down, we’ll copy the existing database directory to the new location with rsync. Using the -a flag preserves the permissions and other directory properties, while-v provides verbose output so you can follow the progress.
Note: Be sure there is no trailing slash on the directory, which may be added if you use tab completion. When there’s a trailing slash, rsync will dump the contents of the directory into the mount point instead of transferring it into a containing mysql directory:
- sudo rsync -av /var/lib/mysql /mnt/volume-nyc1-01
Once the rsync is complete, rename the current folder with a .bak extension and keep it until we’ve confirmed the move was successful. By re-naming it, we’ll avoid confusion that could arise from files in both the new and the old location:
- sudo mv /var/lib/mysql /var/lib/mysql.bak
Now we’re ready to turn our attention to configuration.
Step 2 — Pointing to the New Data Location
MySQL has several ways to override configuration values. By default, the datadir is set to /var/lib/mysql in the /etc/mysql/mysql.conf.d/mysqld.cnf file. Edit this file to reflect the new data directory:
- sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Find the line that begins with datadir= and change the path which follows to reflect the new location.
In our case, the updated file looks like the output below:
. . .
datadir=/mnt/volume-nyc1-01/mysql
. . .
This seems like the right time to bring up MySQL again, but there’s one more thing to configure before we can do that successfully.
Step 3 — Configuring AppArmor Access Control Rules
We’ll need to tell AppArmor to let MySQL write to the new directory by creating an alias between the default directory and the new location. To do this, edit the AppArmor alias file:
- sudo nano /etc/apparmor.d/tunables/alias
At the bottom of the file, add the following alias rule:
. . .
[label /etc/apparmor.d/tunables/alias]
alias /var/lib/mysql/ -> /mnt/volume-nyc1-01/mysql/,
. . .
For the changes to take effect, restart AppArmor:
- sudo systemctl restart apparmor
Note: If you skipped the AppArmor configuration step, you would run into the following error message:
OutputJob for mysql.service failed because the control process
exited with error code. See "systemctl status mysql.service"
and "journalctl -xe" for details.
The output from both systemctl and journalctl concludes with:
OutputJul 18 11:03:24 ubuntu-512mb-nyc1-01 systemd[1]:
mysql.service: Main process exited, code=exited, status=1/FAILURE
Since the messages don’t make an explicit connection between AppArmor and the data directory, this error can take some time to figure out.
Step 4 — Restarting MySQL
The next step is to start MySQL, but if you do, you’ll run into another error. This time, instead of an AppArmor issue, the error happens because the script mysql-systemd-start checks for the existence of either a directory, -d, or a symbolic link, -L, that matches two default paths. It fails if they’re not found:
. . .
if [ ! -d /var/lib/mysql ] && [ ! -L /var/lib/mysql ]; then
echo "MySQL data dir not found at /var/lib/mysql. Please create one."
exit 1
fi
if [ ! -d /var/lib/mysql/mysql ] && [ ! -L /var/lib/mysql/mysql ]; then
echo "MySQL system database not found. Please run mysql_install_db tool."
exit 1
fi
. . .
Since we need these to start the server, we will create the minimal directory structure to pass the script’s environment check.
- sudo mkdir /var/lib/mysql/mysql -p
Now we’re ready to start MySQL.
- sudo systemctl start mysql
- sudo systemctl status mysql
To make sure that the new data directory is indeed in use, start the MySQL monitor.
- mysql -u root -p
Look at the value for the data directory again:
Output+----------------------------+
| @@datadir |
+----------------------------+
| /mnt/volume-nyc1-01/mysql/ |
+----------------------------+
1 row in set (0.01 sec)
Now that you’ve restarted MySQL and confirmed that it’s using the new location, take the opportunity to ensure that your database is fully functional. Once you’ve verified the integrity of any existing data, you can remove the backup data directory:
- sudo rm -Rf /var/lib/mysql.bak
Restart MySQL one final time to be sure that it works as expected:
- sudo systemctl restart mysql
- sudo systemctl status mysql
Conclusion
In this tutorial, we’ve moved MySQL’s data directory to a new location and updated Ubuntu’s AppArmor ACLs to accommodate the adjustment. Although we were using a Block Storage device, the instructions here should be suitable for redefining the location of the data directory regardless of the underlying technology.
For more on managing MySQL’s data directories, see these sections in the official MySQL documentation:
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
author
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Become a contributor for community
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
DigitalOcean Documentation
Full documentation for every DigitalOcean product.
Resources for startups and SMBs
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
The developer cloud
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Get started for free
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.
Interesting. But why not just link the MySQL data directory to the mounting point?
I got as far as re-starting MySQL, and got this error:
Suggestions?
Which version of mysql-server did you use? I could not get it work for mysql version (5.7.16-0ubuntu0.16.04.1) in ubuntu 16.04 64 bit. In my case the problem was “Can’t change dir to ‘/var/lib/mysql/’ (Errcode: 13 - Permission denied)”. I tried to create a sym link of /var/lib/mysql to /media/gias/PortableGias/mysql. The permission problem is baffling, as you can see below the user mysql should have the right permission.
sudo ls -al /media/gias/GiasPortable/mysql total 110662 drwx------ 1 mysql mysql 568 Nov 13 22:23 . drwxrwxrwx 1 root root 4096 Nov 13 22:08 … -rwxrwxrwx 1 mysql mysql 56 Nov 13 22:05 auto.cnf -rwxrwxrwx 1 root mysql 0 Nov 13 22:06 debian-5.7.flag -rwxrwxrwx 1 mysql mysql 413 Nov 13 22:06 ib_buffer_pool -rwxrwxrwx 1 mysql mysql 12582912 Nov 13 22:23 ibdata1 -rwxrwxrwx 1 mysql mysql 50331648 Nov 13 22:23 ib_logfile0 -rwxrwxrwx 1 mysql mysql 50331648 Nov 13 22:05 ib_logfile1 drwxrwxrwx 1 mysql mysql 12288 Nov 13 22:05 mysql drwxrwxrwx 1 mysql mysql 24576 Nov 13 22:05 performance_schema drwxrwxrwx 1 mysql mysql 28672 Nov 13 22:06 sys
ls -al /var/lib/mysql lrwxrwxrwx 1 root root 30 Nov 13 22:15 /var/lib/mysql -> /media/gias/GiasPortable/mysql
I followed the apparmor suggestions properly, but still got the above error.
Thanks a lot for your help!
I’ve been trying this for ages, realised I had a typo in my apparmor config, and fixed it up.
But still, when I’m trying to restart MySQL I’m getting this error:
Here’s what I get from “systemctl status mysql.service”:
Any ideas what I’m doing wrong?
I’m getting this error: (if I set the datadir to an empty directory it works)
Mar 13 18:13:34 xxxx systemd[1]: Starting MySQL Community Server… Mar 13 18:13:34 xxxx mysql-systemd-start[18223]: 2017-03-13T17:13:34.175118Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_tim Mar 13 18:13:34 xxxx mysql-systemd-start[18223]: 2017-03-13T17:13:34.179577Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting. Mar 13 18:13:34 xxxx mysql-systemd-start[18223]: 2017-03-13T17:13:34.180231Z 0 [ERROR] Aborting
Thank you for this guide , but you might at the top clarify that this might be interesting only if you want to run multiple mysql instances on the same server. The guide above did not work for me and left me with a not working mysql server. When starting up it was still looking for the var/lib/mysql directory and I could not figure out what went wrong since I followed your guide by the letter.
For a single mysql instance data directory move ( what most users might be looking for ) do the following :
mv /var/lib/mysql /new/datadir ln -s /new/datadir /var/lib/mysql
You might need check the owner/group and change them accordingly . sudo chown -R mysql:mysql /new/datadir/mysql/ *
Chown command : chown -R user:user [dir OR file]
NO NEED to change the my.cnf
Apparmor edit was not needed on my system.
sudo service mysql restart and voila it was done in 5 min.
one of the worst article… after following the steps i got apparmor error. unable to start mysql . :(
I tried all posiible solution and your but same error with error log,please help me am stuck,your an expert and iam newbie to servers and thanks in adavance: Job for mysql.service failed because the control process exited with error code. See “systemctl status mysql.service” and “journalctl -xe” for details.
Jan 5 17:57:31 CampuseraDroplet systemd[1]: mysql.service: Control process exited, code=exited status=1 Jan 5 17:57:31 CampuseraDroplet systemd[1]: Failed to start MySQL Community Server. Jan 5 17:57:31 CampuseraDroplet systemd[1]: mysql.service: Unit entered failed state. Jan 5 17:57:31 CampuseraDroplet systemd[1]: mysql.service: Failed with result ‘exit-code’. Jan 5 17:57:31 CampuseraDroplet systemd[1]: mysql.service: Service hold-off time over, scheduling restart. Jan 5 17:57:31 CampuseraDroplet systemd[1]: Stopped MySQL Community Server. Jan 5 17:57:31 CampuseraDroplet systemd[1]: mysql.service: Start request repeated too quickly. Jan 5 17:57:31 CampuseraDroplet systemd[1]: Failed to start MySQL Community Server. Jan 5 17:57:31 CampuseraDroplet systemd[1]: mysql.service: Unit entered failed state. Jan 5 17:57:31 CampuseraDroplet systemd[1]: mysql.service: Failed with result ‘exit-code’.
Job for mysql.service failed because the control process exited with error code. See “systemctl status mysql.service” and “journalctl -xe” for details.
root@CampuseraDroplet:~# tail -30 /var/log/mysql/error.log 2018-01-05T11:56:22.047595Z 0 [Note] Shutting down plugin ‘INNODB_FT_DELETED’ 2018-01-05T11:56:22.047599Z 0 [Note] Shutting down plugin ‘INNODB_FT_DEFAULT_STO PWORD’ 2018-01-05T11:56:22.047602Z 0 [Note] Shutting down plugin ‘INNODB_METRICS’ 2018-01-05T11:56:22.047605Z 0 [Note] Shutting down plugin ‘INNODB_TEMP_TABLE_INF O’ 2018-01-05T11:56:22.047608Z 0 [Note] Shutting down plugin ‘INNODB_BUFFER_POOL_ST ATS’ 2018-01-05T11:56:22.047611Z 0 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE_LR U’ 2018-01-05T11:56:22.047615Z 0 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE’ 2018-01-05T11:56:22.047618Z 0 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX_ RESET’ 2018-01-05T11:56:22.047621Z 0 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX’ 2018-01-05T11:56:22.047625Z 0 [Note] Shutting down plugin ‘INNODB_CMPMEM_RESET’ 2018-01-05T11:56:22.047628Z 0 [Note] Shutting down plugin ‘INNODB_CMPMEM’ 2018-01-05T11:56:22.047649Z 0 [Note] Shutting down plugin ‘INNODB_CMP_RESET’ 2018-01-05T11:56:22.047653Z 0 [Note] Shutting down plugin ‘INNODB_CMP’ 2018-01-05T11:56:22.047656Z 0 [Note] Shutting down plugin ‘INNODB_LOCK_WAITS’ 2018-01-05T11:56:22.047659Z 0 [Note] Shutting down plugin ‘INNODB_LOCKS’ 2018-01-05T11:56:22.047661Z 0 [Note] Shutting down plugin ‘INNODB_TRX’ 2018-01-05T11:56:22.047664Z 0 [Note] Shutting down plugin ‘InnoDB’ 2018-01-05T11:56:22.047855Z 0 [Note] InnoDB: FTS optimize thread exiting. 2018-01-05T11:56:22.048380Z 0 [Note] InnoDB: Starting shutdown… 2018-01-05T11:56:22.149021Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/ mysql/ib_buffer_pool 2018-01-05T11:56:22.149466Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 18 0105 11:56:22 2018-01-05T11:56:23.370580Z 0 [Note] InnoDB: Shutdown completed; log sequence nu mber 2674079 2018-01-05T11:56:23.372460Z 0 [Note] InnoDB: Removed temporary tablespace data f ile: “ibtmp1” 2018-01-05T11:56:23.372481Z 0 [Note] Shutting down plugin ‘MEMORY’ 2018-01-05T11:56:23.372494Z 0 [Note] Shutting down plugin ‘CSV’ 2018-01-05T11:56:23.372500Z 0 [Note] Shutting down plugin ‘sha256_password’ 2018-01-05T11:56:23.372503Z 0 [Note] Shutting down plugin ‘mysql_native_password ‘ 2018-01-05T11:56:23.372914Z 0 [Note] Shutting down plugin ‘binlog’ 2018-01-05T11:56:23.373409Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
Might be a “noob” mistake but just in case anyone else has the same problem, that trailing comma in the apparmor alias config is critical. Even if that is the only active line in the file.
/etc/apparmor.d/tunables/alias . . . alias /var/lib/mysql/ -> /mnt/volume-nyc1-01/mysql/, . . .