Wednesday, February 21, 2024
HomeSoftware EngineeringThe best way to transfer MySQL database to a different drive

The best way to transfer MySQL database to a different drive


Step 1:

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

mysql -u root -p

Discover out the place the information listing is situated:

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

Step 2:

Now you possibly can cease the server and test the standing:

sudo systemctl cease mysql
sudo systemctl standing mysql

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

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

Bear in mind to create a backup of the unique information in case it’s good 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 so forth/mysql/mysql.conf.d/mysqld.cnf

Discover the datadir key and swap the worth together 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 so forth/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 drag within the new configuration.

sudo systemctl restart apparmor

Step 5:

Begin the MySQL server for modifications 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)
RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments