How to change the storage location of a MySQL database?

To change the storage location of the MySQL database, you can follow these steps:

1. To stop the MySQL service, open the command prompt or terminal window and enter the following command.

sudo service mysql stop

1. Copy the MySQL data directory: By default, MySQL’s data directory is located in /var/lib/mysql. You can copy this directory to a new storage location. For example, if you want to copy the data directory to a new location /home/mysql_data, you can use the following command:

sudo cp -R /var/lib/mysql /home/mysql_data

1. Edit the MySQL configuration file by opening the my.cnf file. On Ubuntu, this file is usually located at /etc/mysql/my.cnf. Use a text editor to open this file.

sudo vi /etc/mysql/my.cnf

1. Locate the datadir option in the configuration file and change its value to the path of the new data directory. For example, update it to /home/mysql_data.

datadir = /home/mysql_data

1. Save and close the configuration file.
2. Change file permissions: Make sure the owner and group of the new data directory and its contents match the MySQL user (usually mysql). You can modify permissions using the following command:

sudo chown -R mysql:mysql /home/mysql_data

1. Start the MySQL service by inputting the following command.

sudo service mysql start

After completing the above steps, the storage location of the MySQL database will be changed to a new directory. Make sure to back up your data to prevent any unexpected situations.