How to move MySQL database to another drive

Step 1:

Login to your MySQL server, enter your password when prompted:

mysql -u root -p
Code language: plaintext (plaintext)

Find out where the data directory is located:

mysql> select @@datadir;
Code language: plaintext (plaintext)
Output +-----------------+ | @@datadir | +-----------------+ | /var/lib/mysql/ | +-----------------+ 1 row in set (0.00 sec)
Code language: plaintext (plaintext)

Step 2:

Now you can stop the server and check the status:

sudo systemctl stop mysql
Code language: plaintext (plaintext)
sudo systemctl status mysql
Code language: plaintext (plaintext)

It’s time to make a copy to your new mount location:

sudo rsync -av /var/lib/mysql /mnt/new-volume-01
Code language: plaintext (plaintext)

Remember to create a backup of the original data in case you need to revert at a later stage:

sudo mv /var/lib/mysql /var/lib/mysql.bak
Code language: plaintext (plaintext)

Step 3:

Edit the MySQL configuration to update the datadir to the new location:

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
Code language: plaintext (plaintext)

Find the datadir key and swap the value with your new mount path:

... datadir=/mnt/new-volume-01/mysql ...
Code language: plaintext (plaintext)

Step 4:

Update AppArmor to point to a new location:

sudo vi /etc/apparmor.d/tunables/alias

Create a new line at the bottom that references your new mount path, aliased back to /var/lib/mysql.

... [label /etc/apparmor.d/tunables/alias] alias /var/lib/mysql/ -> /mnt/new-volume-01/mysql/, ...
Code language: plaintext (plaintext)

Restart AppArmor to pull in the new configuration.

sudo systemctl restart apparmor
Code language: plaintext (plaintext)

Step 5:

Start the MySQL server for changes to take effect.

sudo systemctl start mysql sudo systemctl status mysql
Code language: plaintext (plaintext)

Login to MySQL again to verify it is using the new directory:

mysql -u root -p
Code language: plaintext (plaintext)

Re-run the command from before to verify:

mysql> select @@datadir;
Code language: plaintext (plaintext)
Output +---------------------------+ | @@datadir | +---------------------------+ | /mnt/new-volume-01/mysql/ | +---------------------------+ 1 row in set (0.00 sec)
Code language: plaintext (plaintext)

Tags:
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments