The right way to transfer MySQL database to a different drive

By
Sohaib
-
0
8
Adv1


Adv2

Step 1:

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

mysql -u root -p

Discover out the place the knowledge listing is situated:

mysql> choose @@datadir;

Output
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

Step 2:

Now you may cease the server and examine the standing:

sudo systemctl cease mysql

sudo systemctl standing mysql

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

sudo rsync -av /var/lib/mysql /mnt/new-volume-01

Keep in mind to create a backup of the unique knowledge in case you want to revert at a later stage:

sudo mv /var/lib/mysql /var/lib/mysql.bak

Step 3:

Edit the MySQL configuration to replace the datadir to the brand new location:

sudo vi /and many others/mysql/mysql.conf.d/mysqld.cnf

Discover the datadir key and swap the worth along with your new mount path:

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

Step 4:

Replace AppArmor to level to a brand new location:

sudo vi /and many others/apparmor.d/tunables/alias

Create a brand new line on the backside that references your new mount path, aliased again to /var/lib/mysql.

...
[label /etc/apparmor.d/tunables/alias]
alias /var/lib/mysql/ -> /mnt/new-volume-01/mysql/,
...

Restart AppArmor to tug within the new configuration.

sudo systemctl restart apparmor

Step 5:

Begin the MySQL server for adjustments to take impact.

sudo systemctl begin mysql
sudo systemctl standing mysql

Login to MySQL once more to confirm it’s utilizing the brand new listing:

mysql -u root -p

Re-run the command from earlier than to confirm:

mysql> choose @@datadir;

Output
+---------------------------+
| @@datadir                 |
+---------------------------+
| /mnt/new-volume-01/mysql/ |
+---------------------------+
1 row in set (0.00 sec)
Adv3