How To Move a MySQL Data Directory to a New Location on Ubuntu 20.04 | DigitalOcean (2023)


Databases grow over time, sometimes outgrowing the space on the file system. You can also run into input/output (I/O) contention when they’re located on the same partition as the rest of the operating system. Redundant Array of Independent Disks (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 wanting to take advantage of other storage features, this tutorial will guide you through relocating MySQL’s data directory.


To complete this guide, you will need:

  • An Ubuntu 20.04 server with a non-root user with sudo privileges and a firewall enabled. You can learn more about how to set up a user with these privileges in our Initial Server Setup with Ubuntu 20.04 guide.

  • A MySQL server. If you haven’t already installed MySQL, check out our guide on How To Install MySQL on Ubuntu 20.04.

In this tutorial, 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 following documentation on Block Storage Volumes on DigitalOcean.

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. Run the following command to open the MySQL server prompt:

  1. sudo mysql

Note: If you configured your root MySQL user to authenticate using a password, you can connect to MySQL as this user with the following command:

  1. mysql -u root -p

When prompted, supply the MySQL user password. Then from the MySQL prompt, run the following SELECT statement. This will return this MySQL instance’s active data directory, which is always recorded in MySQL’s datadir variable::

  1. SELECT @@datadir;


+-----------------+| @@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 you need to move. Once you’ve confirmed this, write exit to leave the monitor and return to your command prompt:

  1. exit



To ensure the integrity of the data, shut down MySQL before making changes to the data directory:

  1. sudo systemctl stop mysql

Note that systemctl doesn’t display the outcome of all service management commands, so if you want to check if you’ve succeeded, use the following command:

  1. sudo systemctl status mysql

You can confirm it’s shut down if the Active line in the output states it’s inactive (dead) as highlighted in the following example:


● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset:> Active: inactive (dead) since Wed 2022-03-23 19:03:49 UTC; 5s ago Process: 3415 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS) Main PID: 3415 (code=exited, status=0/SUCCESS) Status: "Server shutdown complete"

Now that the server is shut down, you can copy the existing database directory, /var/lib/mysql, to the new location, /mnt/volume-nyc1-01, 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 command is complete, rename the current folder with a .bak extension and keep it until you’ve confirmed the move was successful. By renaming it, you’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 you’re ready to proceed with the next step and begin 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 in your preferred text editor to reflect the new data directory. Here we’ll use nano:

  1. sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Find the line that begins with datadir=. Uncomment the line by deleting the pound sign (#) and change the path to reflect the new location. In this case, the updated file contents will be as follows:


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

Once you’ve made this update, save and exit the file. If you’re using nano, you can do this by pressing CTRL + X, then Y, and ENTER. Now it’s almost time to bring up MySQL again, but before that, there’s one more thing to configure in order to be successful.

Step 3 — Configuring AppArmor Access Control Rules

In this step, you need to tell AppArmor to let MySQL write to the new directory by creating an alias between the default directory and the new location. AppArmor is a security module in the Linux kernel that allows system administrators to restrict program capabilities through program profiles, rather than users themselves. Start by opening up and editing the AppArmor alias file:

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

At the bottom of the file, uncomment the following line and add the alias rule:


. . .alias /var/lib/mysql/ -> /mnt/volume-nyc1-01/mysql/,. . .

When you’re finished, save and exit the file.

For the changes to take effect, restart AppArmor:

  1. sudo systemctl restart apparmor

Note: If you skipped the AppArmor configuration step, you will receive the following error message:


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

Since this message doesn’t make an explicit connection between AppArmor and the data directory, this error can take some time to figure out.

Once you’ve properly configured AppArmor, you can move on to the next step.

Step 4 — Restarting MySQL

Now it’s time to start MySQL. If you do, however, you’ll run into another error. Instead of an AppArmor issue, this error is caused by mysql-systemd-start, a script that supports managing MySQL through systemd. You can inspect this script with the following command:

  1. nano /usr/share/mysql/mysql-systemd-start

This script checks for the existence of either a directory, -d, or a symbolic link, -L, that matches the default data directory path. If it doesn’t find either of these, the script will trigger an error and prevent MySQL from starting:


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

After you’ve inspected this file, close it without making any changes.

Since you need either an appropriate directory or symbolic link to start the server, you must create the minimal directory structure to pass the script’s environment check:

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

Now you’re ready to start MySQL:

  1. sudo systemctl start mysql

Confirm MySQL is running by checking the status:

  1. sudo systemctl status mysql


● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset:> Active: active (running) since Wed 2022-03-23 20:51:18 UTC; 4s ago Process: 17145 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=> Main PID: 17162 (mysqld) Status: "Server is operational" Tasks: 38 (limit: 1132) Memory: 376.7M CGroup: /system.slice/mysql.service └─17162 /usr/sbin/mysqld

To ensure that the new data directory is indeed in use, start the MySQL monitor:

  1. mysql -u sammy -p

Now query for the value of the data directory again:

  1. SELECT @@datadir;


+----------------------------+| @@datadir |+----------------------------+| /mnt/volume-nyc1-01/mysql/ |+----------------------------+1 row in set (0.01 sec)

After 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 finished, exit the database as in the following and return to the command prompt:

  1. exit



Now that you’ve verified the integrity of any existing data, you can remove the backup data directory:

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

Then restart MySQL one final time:

  1. sudo systemctl restart mysql

And finally, confirm it’s working as expected by checking the status:

  1. sudo systemctl status mysql


● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset:> Active: active (running) since Wed 2022-03-23 20:53:03 UTC; 4s ago Process: 17215 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=> Main PID: 17234 (mysqld) Status: "Server is operational" Tasks: 38 (limit: 1132) Memory: 368.9M CGroup: /system.slice/mysql.service └─17234 /usr/sbin/mysqld

If the Active line states active(running) this confirms that MySQL is working.


In this tutorial, you learned how to move MySQL’s data directory to a new location and update Ubuntu’s AppArmor access control lists 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 information on managing MySQL’s data directories, check out the following sections in the official MySQL documentation:


How do I move a MySQL data directory to a new location? ›

How to move the MySQL database to a different drive
  1. Stop the MySQL service.
  2. Create a Folder on the new drive (e.g. ​​​D:\Mysql Database\)
  3. Give Full Control to the NETWORK SERVICE user. ...
  4. Copy the "Data" folder to the new data location. ...
  5. Make a backup copy of the "my.ini" file. ...
  6. Edit the "my.ini" file using Wordpad.
Jun 3, 2022

How to change MySQL path in Ubuntu? ›

A Comprehensive Guide to Changing the Default MySQL Data Directory on Ubuntu & Debian
  1. Step 1: Preparing the New Data Directory. ...
  2. Step 2: Stop the MySQL Service. ...
  3. Step 3: Copy the Existing Data to the New Directory. ...
  4. Step 4: Update the MySQL Configuration. ...
  5. Step 5: Update the MySQL Client Configuration.
Apr 9, 2023

Can I copy MySQL data directory to another server? ›

For example, using a binary backup is one way to copy databases from one MySQL server to another. For MyISAM, binary portability means that you can directly copy the files for a MyISAM table from one MySQL server to another on a different machine and the second server will be able to access the table.

How to change MySQL path in Linux? ›

How to change PostgreSQL database data directory
  1. Step 1 - Identify current data directory path. SHOW data_directory;
  2. Step 2- Stop Postgresql services. systemctl status <service_name> ...
  3. Step 3 - create a blank directory on the target path. ...
  4. Step 4 - Use initdb to create creates a new PostgreSQL database cluster.
Sep 1, 2020

How do I move a SQL database location? ›

In SQL Server, you can move system and user databases by specifying the new file location in the FILENAME clause of the ALTER DATABASE statement. Data, log, and full-text catalog files can be moved in this way.

How to check MySQL path in Ubuntu? ›

  1. Open up MySQL's configuration file: less /etc/my.cnf.
  2. Search for the term "datadir": /datadir.
  3. If it exists, it will highlight a line that reads: datadir = [path]
  4. You can also manually look for that line. ...
  5. If that line does not exist, then MySQL will default to: /var/lib/mysql.
Aug 7, 2017

How to copy MySQL database from one computer to another in Ubuntu? ›

To copy a MySQL database, you need to follow these steps:
  1. First, create a new database using CREATE DATABASE statement.
  2. Second, export all the database objects and data of the database from which you want to copy using mysqldump tool.
  3. Third, import the SQL dump file into the new database.

How to copy MySQL database in Linux? ›

The explanation is as:
  1. Use the clause mysqldump to create the backup of the database.
  2. Use the -u flag with the user_name to connect the MySQL server.
  3. Use the -p flag for the password of the user.
  4. Replace the database with the database name which you want to clone.
  5. Use the “>” sign to create a backup.

How to do migration in MySQL? ›

How to migrate a MySQL database to a new server
  1. Go to the Database menu and select Copy Databases.
  2. On the Copy Database page that opens, select the source and target connections.
  3. Select the required databases and the Include Data and Drop if exists on target checkboxes if needed.

How to change MySQL address? ›

If you want to configure MySQL and bind the IP addresses, you should edit the configuration file /etc/mysql/mysql. conf. d/mysqld. cnf and change the default IP address value by separating each address with a comma.

How to change MySQL port in Ubuntu? ›

Go to config file of mysql server :
  1. sudo nano /etc/mysql/my.cnf. #change socket port for client :
  2. port = 3306 to port = 6606. #change mysqld port for server :
  3. port =3306 to port =6606. #change bind address :
Jan 20, 2016

How to reconfigure MySQL on a different port? ›

Configuring the port for MySQL
  1. Determine the port number you want to use. You can use any available port number. ...
  2. Open the MySQL configuration file. ...
  3. Locate the line that starts with "port" in the configuration file. ...
  4. Change the port number to the desired number. ...
  5. Save the configuration file and restart the MySQL server.
Mar 23, 2023

How do I move MySQL data directory in Windows? ›

First of all you will be stopping the MySQL servers by going to Services. Once the MySQL server service is stopped, you will copy the Data directory for MySQL Server to new location. Go to configuration file my. ini and then update the location for data directory.

How do I move a database from one location to another? ›

To move a database by using detach and attach

In a Windows Explorer or Windows Command Prompt window, move the detached database file or files and log file or files to the new location. You should move the log files even if you intend to create new log files.

How do I find my MySQL data directory location? ›

Under Windows, the default data directory is C:\mysql\data. The data directory location can be specified explicitly when you start up the server by using a --datadir=dir_name option. This is useful if you want to place the directory somewhere other than its default location.

How to migrate data in MySQL? ›

How to migrate a MySQL database to a new server
  1. Go to the Database menu and select Copy Databases.
  2. On the Copy Database page that opens, select the source and target connections.
  3. Select the required databases and the Include Data and Drop if exists on target checkboxes if needed.

Top Articles
Latest Posts
Article information

Author: Lakeisha Bayer VM

Last Updated: 25/01/2024

Views: 5603

Rating: 4.9 / 5 (69 voted)

Reviews: 92% of readers found this page helpful

Author information

Name: Lakeisha Bayer VM

Birthday: 1997-10-17

Address: Suite 835 34136 Adrian Mountains, Floydton, UT 81036

Phone: +3571527672278

Job: Manufacturing Agent

Hobby: Skimboarding, Photography, Roller skating, Knife making, Paintball, Embroidery, Gunsmithing

Introduction: My name is Lakeisha Bayer VM, I am a brainy, kind, enchanting, healthy, lovely, clean, witty person who loves writing and wants to share my knowledge and understanding with you.