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.04PostedJuly 62.1k views MYSQL BLOCK STORAGE STORAGE ubuntu 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 F Eatures, this tutorial would guide you through relocating MySQL ' s data directory.

Prerequisites

To the This guide, you'll need:

• An Ubuntu 16.04 server with a non-root user with sudo privileges. You can learn more on 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.04guide 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 "to" use Block Storage on Digitalocean Guide.

No matter what underlying storage your 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 u Sing 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. MySQL is configured for use of the default data directory, so that's the /var/lib/mysql/, directory we need to Move. Once you ' ve confirmed this, type to exit 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

systemctlDoesn ' t display the outcome of all service management commands, so if you want to being sure you ' ve succeeded, use the follow ing command:

• sudo systemctl status MySQL

You can be sure it's shut down if the final line of the "output tells you" is stopped:

Output

. . .Jul 18 11:24:20 ubuntu-512mb-nyc1-01 systemd[1]: Stopped MySQL Community Server.

Now, the server was shut down, we'll copy the existing database directory to the new location with rsync . Using -a The flag preserves the permissions and other directory properties, while -v provides verbose output so you c An 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, would dump the contents of the directory into the rsync mount point instead of transferring it into a containing mysql directory:

• sudo rsync-av/var/lib/mysql /mnt/volume-nyc1-01

Once rsync The are 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 was set to in the datadir /var/lib/mysql /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, begins with and change the datadir= path which follows to reflect, the new location.

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 succ Essfully.

Step 3-configuring AppArmor Access Control Rules

We ' ll need to-tell AppArmor-let MySQL write to the new directory by creating an alias between the default directory and The new location. To does this, edit the AppArmor alias file:

• sudo nano/etc/apparmor.d/tunables/alias

At the bottom of the file, add the following alias rule:

/etc/apparmor.d/tunables/alias

/mnt/volume-nyc1-01/mysql/,. . .

For the changes-take effect, restart AppArmor:

• sudo systemctl restart AppArmor

Note:if 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 a explicit connection between AppArmor and the data directory, this error can take some Tim E to figure out.

Step 4-restarting MySQL

The next step is to start MySQL, and if you don't, 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 D Irectory, -d , or a symbolic link, -L , that matches, the 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 1fiif [ ! -d /var/lib/mysql/mysql ] && [ ! -L /var/lib/mysql/mysql ]; then echo "MySQL system database not found. Please run mysql_install_db tool." exit 1fi. . .

Since We need these to start the server, we'll create the minimal directory structure to pass the script ' s environment C Heck.

• 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 this 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 so you ' ve restarted MySQL and confirmed that it's using the new location, take the opportunity to ensure that your D Atabase is fully functional. Once you ' ve verified the integrity for any existing data, you can remove the backup Data directory:

• sudo rm-rf/var/lib/mysql.bak

Restart MySQL One final time to being 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 AppArmor ACLs to accommodate t He adjustment. Although we were using a Block Storage device, the instructions here should is 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:

• The Mysql Data Directory
• Setting up multiple Data directories

How to Move a MySQL Data Directory to a New location on Ubuntu 16.04