Powerful enterprise-class cloud infrastructure
Secured virtual desktop secure environment
Set up an isolated private cloud environment
Safeguard your cloud servers against threats
Prevent server overload and maximize performance
Effortlessly add block storage to your servers
Provide hosting services to your clients
English
Here are the steps to provide a step-by-step guide for relocating the MySQL data directory to a new location on an Ubuntu 20.04 server. This process is useful for scenarios where the existing data directory has space limitations or when optimizing performance by utilizing alternative storage solutions, such as network block storage.
Before proceeding with the MySQL data directory relocation, ensure you have the following prerequisites in place:
*Note: If you’ve configured the root MySQL user to use a password, connect using the following command:
1.2. Supply the MySQL user password when prompted, and then execute the following SQL statement to retrieve the active data directory:
1.3. The output will confirm the current data directory location, typically found in MySQL’s `datadir` variable. For example:
1.4. Exit the MySQL prompt by typing:
1.5. To maintain data integrity, stop the MySQL service:
1.6. Verify the MySQL service is inactive by using:
Ensure the “Active” line states “inactive (dead),” indicating that MySQL has shut down successfully.
2.1. Now that MySQL is stopped, copy the existing database directory (`/var/lib/mysql`) to the new destination (`/mnt/volume-nyc1-01`) using `rsync`. The `-a` flag preserves permissions and other properties, while `-v` provides verbose output for progress monitoring:
2.2. After the `rsync` command completes, rename the current data directory with a `.bak` extension to avoid confusion:
3.1. MySQL’s data directory is specified in the `/etc/mysql/mysql.conf.d/mysqld.cnf` configuration file. Edit this file to reflect the new data directory using a text editor of your choice (e.g., `nano`):
3.2. Locate the line that starts with `datadir=` and uncomment it by removing the ‘#’ symbol. Update the path to match the new data directory, like so:
3.3. Save and exit the file (for `nano`, press `CTRL + X`, then `Y`, and finally `ENTER`).
4.1. To allow MySQL to write to the new directory, establish an alias between the default directory and the new location by editing the AppArmor alias file:
4.2. At the end of the file, uncomment the following line and add the alias rule:
4.3. Save and exit the file.
4.4. Apply the AppArmor changes by restarting AppArmor:
*Note: If you skipped the AppArmor configuration step, you will receive an error message when attempting to start MySQL.
5.1. Start MySQL once again. You may encounter an error related to `mysql-systemd-start`, a script that manages MySQL through systemd. Inspect this script:
5.2. The script checks for the existence of either a directory or symbolic link matching the default data directory path. To address this, create the necessary directory structure:
5.3. Start MySQL:
5.4. Confirm MySQL’s status:
6.1. Verify the new data directory is in use by starting the MySQL monitor:
6.2. Query for the value of the data directory:
6.3. Confirm that MySQL is using the new location.
6.4. Exit the MySQL monitor and return to the command prompt:
6.5. After verifying data integrity, remove the backup data directory:
6.6. Restart MySQL one final time and confirm it’s working as expected:
If the “Active” line states “active (running),” this confirms that MySQL is operational.
The MySQL data directory has been successfully relocated to a new location. This process ensures data integrity and provides the flexibility to manage data storage efficiently.
Have additional questions? Search below:
