MySQL is a widely-used open-source relational database management system (RDBMS) that powers countless web applications and services. By default, MySQL stores its data in a predefined directory, which may not always be suitable for your requirements. For instance, you might want to move the data directory to another location for better performance, security, or to utilize a separate disk or partition.
In this comprehensive guide, we will walk you through the process of changing the default MySQL data directory on Linux systems, ensuring a smooth transition with minimal downtime.
Step 1: Preparing the New Data Directory
The first step is to create a new directory where you want to store the MySQL data. Ensure that the new location has adequate storage space for your current and future data requirements. Create the new directory using the following command, replacing /new/mysql/data with your desired path:
sudo mkdir -p /new/mysql/data
Next, set the ownership and permissions for the new directory to match those of the default MySQL data directory:
sudo chown -R mysql:mysql /new/mysql/datasudo chmod 750 /new/mysql/data
Step 2: Stop the MySQL Service
Before making any changes to the MySQL configuration, you must stop the MySQL service to avoid data corruption or loss. Use the following command to stop the service:
sudo systemctl stop mysqld
Step 3: Copy the Existing Data to the New Directory
Now that the MySQL service is stopped, it is safe to copy the existing data to the new directory. To preserve file permissions and ownership, use the rsync command:
sudo rsync -av /var/lib/mysql/* /new/mysql/data
Replace /var/lib/mysql with the current MySQL data directory path if it differs on your system.
Step 4: Update the MySQL Configuration
To inform MySQL of the new data directory, you must update the configuration file. Open the MySQL configuration file (typically located at /etc/my.cnf or /etc/mysql/my.cnf) using your preferred text editor:
sudo nano /etc/my.cnf
Locate the [mysqld] section and update the datadir and socket options to point to the new directory:
1 2 3 | [mysqld] datadir=/new/mysql/data socket=/new/mysql/data/mysql.sock |
If these options do not exist, add them to the [mysqld] section. Save and close the configuration file.
Step 5: Update the Systemd Configuration (Optional)
If your system uses systemd to manage the MySQL service, you may need to update the systemd configuration to reflect the new data directory. Open the MySQL systemd service file (usually located at /usr/lib/systemd/system/mysqld.service or /lib/systemd/system/mysql.service) with a text editor:
sudo nano /usr/lib/systemd/system/mysqld.service
Find the ExecStart line and add the --datadir and --socket options:
1 | ExecStart=/usr/sbin/mysqld --datadir=/new/mysql/data --socket=/new/mysql/data/mysql.sock |
Save and close the file. Reload the systemd configuration using the following command:
sudo systemctl daemon-reload
Step 6: Update the MySQL Client Configuration
To ensure that MySQL clients can connect to the server using the new socket file, update the client configuration. Open the MySQL client configuration file (usually located at /etc/my.cnf or /etc/mysql/my.cnf) with a text editor:
sudo nano /etc/my.cnf
Locate the `[client]section and update thesocket` option to point to the new directory:
1 2 | [client] socket=/new/mysql/data/mysql.sock |
If the socket option does not exist, add it to the [client] section. Save and close the configuration file.
Step 7: Start the MySQL Service
Now that the necessary configurations have been updated, start the MySQL service with the following command:
sudo systemctl start mysqld
Step 8: Verify the Changes
To verify that the changes have been applied successfully, check the MySQL status and ensure that it is running:
sudo systemctl status mysqld
Additionally, you can connect to the MySQL server using a client such as the mysql command-line tool and verify that the new data directory is being used:
mysql -u root -p -e 'SHOW VARIABLES WHERE Variable_Name = "datadir";'
This command should display the new data directory path.
Step 9: Remove or Backup the Old Data Directory (Optional)
Once you have confirmed that MySQL is using the new data directory and everything is working as expected, you can either remove the old data directory or create a backup for safekeeping:
sudo rm -rf /var/lib/mysql
Or
sudo mv /var/lib/mysql /var/lib/mysql_backup
Replace /var/lib/mysql with the current MySQL data directory path if it differs on your system.
Conclusion
Changing the default MySQL data directory on Linux systems is a straightforward process that can help improve performance, security, or better utilize available storage resources. By following this comprehensive guide, you can easily move your MySQL data to a new location, update the necessary configurations, and ensure a seamless transition with minimal downtime.