In this article, we will delve into the process of moving a MySQL database to another disk or partition in the CentOS 7 operating system. This step may be motivated by the need to improve database performance or the requirement for data backup on a separate storage. The following steps should be performed with caution, and it's recommended to backup the database before initiating the process.
Preparation
1. Backup the Database: Before starting the database migration, it's important to create a backup. This can be done using the mysqldump command:
mysqldump -u root -p --all-databases > all_databases.sql
2. Create a New Storage: Assuming you have a disk or partition available where you want to move the database. This disk should be attached and prepared for use. You can use the lsblk command to display available disks and fdisk or parted to create a new partition.
Migration Process
1. Stop MySQL Service: Before starting the migration, it's necessary to stop the MySQL service to prevent data corruption. Stop the service using the following command:
systemctl stop mysqld
2. Copy the Data Directory: MySQL typically stores data in the /var/lib/mysql directory. This directory needs to be copied to the new disk or partition. Use the rsync command to copy while preserving permissions and directory structure:
rsync -av /var/lib/mysql /new/disk/target/directory/
3. Configure MySQL to Use the New Location: After copying the data, it's necessary to modify the MySQL configuration file to reflect the new database location. Edit the /etc/my.cnf file or the relevant configuration file of your MySQL installation:
[mysqld]
datadir=/new/disk/target/directory/
4. Fix Permissions: It's important for MySQL to have the correct permissions on the new data directory. Set permissions using the chown and chmod commands:
chown -R mysql:mysql /new/disk/target/directory/
chmod -R 755 /new/disk/target/directory/
5. Restart MySQL Service: After modifying configuration files and setting permissions, you can restart the MySQL service:
systemctl start mysqld
6. Verify Functionality: After restarting the service, it's advisable to verify that MySQL is running correctly and has access to the data in the new location. You can use the mysql command to log in to the server and check the availability of databases.
Moving a MySQL database to a new disk or partition on CentOS 7 can significantly contribute to better performance and more efficient data backup. It's important to proceed methodically and not forget to backup data before making any changes. If needed, don't hesitate to refer to CentOS and MySQL documentation for further information and advanced configuration options.
