Tutorial

How To Move a MySQL Data Directory to a New Location on Ubuntu 16.04

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.
Ubuntu 16.04

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:

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.

  1. mysql -u root -p

When prompted, supply the MySQL root password. Then from the MySQL prompt, select the data directory:

  1. 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:

  1. 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:

  1. 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:

  1. 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:

  1. 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:

  1. 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:

/etc/mysql/mysql.conf.d/mysqld.cnf
. . .
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:

  1. 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:

  1. sudo systemctl restart apparmor

Note: If you skipped the AppArmor configuration step, you would run into the following error message:

Output
Job 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:

Output
Jul 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:

/usr/share/mysql/mysql-systemd-start
. . .
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.

  1. sudo mkdir /var/lib/mysql/mysql -p

Now we’re ready to start MySQL.

  1. sudo systemctl start mysql
  2. sudo systemctl status mysql

To make sure that the new data directory is indeed in use, start the MySQL monitor.

  1. 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:

  1. sudo rm -Rf /var/lib/mysql.bak

Restart MySQL one final time to be sure that it works as expected:

  1. sudo systemctl restart mysql
  2. 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.

Learn more about us


About the authors

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
10 Comments


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!

Interesting. But why not just link the MySQL data directory to the mounting point?

# ln -s /mnt/volume-nyc1-01/mysql /var/lib/mysql

change data dir really trouble me for a while. ONLY your tutorial helps me solve the problem. thanks a lot!

This is how you write a tutorial.

Much like many of the other users here I get an error when I try to run the mysql server again:

sudo systemctl start mysql

It shows me:

Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

The content of “systemctl status mysql.service” is:

   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: deactivating (stop-sigterm) (Result: exit-code) since Sun 2019-07-14 22:02:49 EDT; 1h 2min ago
  Process: 8388 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid (code=exited, status=1/FAILURE)
  Process: 8378 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 902 (code=exited, status=0/SUCCESS)
    Tasks: 26 (limit: 4915)
   CGroup: /system.slice/mysql.service
           └─8390 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Jul 14 23:05:39 daniel-MS-7B24 mysqld[8388]: 2019-07-15T03:05:39.986262Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
Jul 14 23:05:39 daniel-MS-7B24 mysqld[8388]: 2019-07-15T03:05:39.986270Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
Jul 14 23:05:39 daniel-MS-7B24 mysqld[8388]: 2019-07-15T03:05:39.986277Z 0 [Note] Shutting down plugin 'INNODB_CMP'
Jul 14 23:05:39 daniel-MS-7B24 mysqld[8388]: 2019-07-15T03:05:39.986284Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
Jul 14 23:05:39 daniel-MS-7B24 mysqld[8388]: 2019-07-15T03:05:39.986290Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
Jul 14 23:05:39 daniel-MS-7B24 mysqld[8388]: 2019-07-15T03:05:39.986297Z 0 [Note] Shutting down plugin 'INNODB_TRX'
Jul 14 23:05:39 daniel-MS-7B24 mysqld[8388]: 2019-07-15T03:05:39.986303Z 0 [Note] Shutting down plugin 'InnoDB'
Jul 14 23:05:39 daniel-MS-7B24 mysqld[8388]: 2019-07-15T03:05:39.986445Z 0 [Note] InnoDB: FTS optimize thread exiting.
Jul 14 23:05:39 daniel-MS-7B24 mysqld[8388]: 2019-07-15T03:05:39.986601Z 0 [Note] InnoDB: Starting shutdown...
Jul 14 23:05:39 daniel-MS-7B24 systemd[1]: mysql.service: Control process exited, code=exited status=1

The content of “journalctl -xe” is:

Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517134Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517140Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517147Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517153Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517160Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517167Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517174Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517197Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517204Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517211Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517218Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517225Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517231Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517238Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517244Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517250Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517257Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517263Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517270Z 0 [Note] Shutting down plugin 'INNODB_CMP'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517276Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517282Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517289Z 0 [Note] Shutting down plugin 'INNODB_TRX'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.517296Z 0 [Note] Shutting down plugin 'InnoDB'
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.518327Z 0 [Note] InnoDB: FTS optimize thread exiting.
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.518441Z 0 [Note] InnoDB: Starting shutdown...
Jul 14 23:07:12 daniel-MS-7B24 systemd[1]: mysql.service: Control process exited, code=exited status=1
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.618751Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
Jul 14 23:07:12 daniel-MS-7B24 mysqld[9980]: 2019-07-15T03:07:12.619235Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 190714 23:07:12

Any reason why there might be a bottleneck? I followed the instructions precisely, but I am using linux mint.

Can I skip the apparmor settings?

Great, it’s work for me, thanks.

I too had issues restarting mysql in step 4.

There seems to be a typo in step to… once I added a trailing slash - all worked without a hitch

datadir=/mnt/volume-nyc1-01/mysql/

I followed your steps to a T, and everything worked. (Server version: 5.7.23 on Ubuntu 16.04) Thank you for sharing this.

You broke mysql for me. Thanks!

Hello did by the instructions, in the end everything works, but when I restore to the dump database I get an error ERROR 27 (HY000) at line 25: Can't sync file './test/' to disk (Errcode: 5 - Input/output error)

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel