IntroductionDatabases grow over time, sometimes outgrowing the space on the file system. You can also run into input/output (I/O) contention when they’re located on the same partition as the rest of the operating system. Redundant Array of Independent Disks (RAID), network block storage, and other devices, can offer redundancy and other desirable features. Whether you’re adding more space, evaluating ways to optimize performance, or wanting to take advantage of other storage features, this tutorial will guide you through relocating MySQL’s data directory. Show
PrerequisitesTo complete this guide, you will need:
In this tutorial, we’re moving the data to a block storage device
mounted at No matter what underlying storage you use, this guide can help you move the data directory to a new location. Step 1 — Moving the MySQL Data DirectoryTo prepare for moving MySQL’s data directory, let’s verify the current location by starting an interactive MySQL session using the administrative credentials. Run the following command to open the MySQL server prompt:
Note: If you configured your root MySQL user to authenticate using a password, you can connect to MySQL as this user with the following command:
When prompted, supply the MySQL user password. Then from the MySQL prompt, run the following
This output confirms that MySQL is configured to use the default data directory,
To ensure the integrity of the data, shut down MySQL before making changes to the data directory:
Note that
You can confirm it’s shut down if the
Now that the server is shut down, you can copy the existing database directory, Note: Be sure there is no trailing slash on the directory, which may be added if you use tab completion. When there’s a trailing slash,
Once the
Now you’re ready to proceed with the next step and begin configuration. Step 2 — Pointing to the New Data LocationMySQL has several ways to override configuration values. By default, the
Find the line that begins with /etc/mysql/mysql.conf.d/mysqld.cnf
Once you’ve made this update, save and exit the file. If you’re using Step 3 — Configuring AppArmor Access Control RulesIn this step, you need to tell AppArmor to let MySQL write to the new directory by creating an alias between the default directory and the new location. AppArmor is a security module in the Linux kernel that allows system administrators to restrict program
capabilities through program profiles, rather than users themselves. Start by opening up and editing the AppArmor
At the bottom of the file, uncomment the following line and add the alias rule: /etc/apparmor.d/tunables/alias
When you’re finished, save and exit the file. For the changes to take effect, restart AppArmor:
Note: If you skipped the AppArmor configuration step, you will receive the following error message:
Since this message doesn’t make an explicit connection between AppArmor and the data directory, this error can take some time to figure out. Once you’ve properly configured AppArmor, you can move on to the next step. Step 4 — Restarting MySQLNow it’s time to start MySQL. If you do, however, you’ll run into another error. Instead of an AppArmor issue, this error is caused by
This script checks for the existence of either a directory, /usr/share/mysql/mysql-systemd-start
After you’ve inspected this file, close it without making any changes. Since you need either an appropriate directory or symbolic link to start the server, you must create the minimal directory structure to pass the script’s environment check:
Now you’re ready to start MySQL:
Confirm MySQL is running by checking the status:
To ensure that the new data directory is indeed in use, start the MySQL monitor:
Now query for the value of the data directory again:
After you’ve restarted MySQL and confirmed that it’s using the new location, take the opportunity to ensure that your database is fully functional. Once you’ve finished, exit the database as in the following and return to the command prompt:
Now that you’ve verified the integrity of any existing data, you can remove the backup data directory:
Then restart MySQL one final time:
And finally, confirm it’s working as expected by checking the status:
If the ConclusionIn this tutorial, you learned how to move MySQL’s data directory to a new location and update Ubuntu’s AppArmor access control lists to accommodate the adjustment. Although we were using a block storage device, the instructions here should be suitable for redefining the location of the data directory regardless of the underlying technology. For more information on managing MySQL’s data directories, check out the following sections in the official MySQL documentation:
How do I move MySQL to another drive?Move MySQL data files to another drive. Stop MySQL service. ... . Modify the path of the new location of your data files. ... . Move the original data files (originally located by default in C:\ProgramData\MySQL\MySQL Server 5.5\data) to the new destination (in the example "E:\MySQL data") ... . Start Franson NMEA service.. What is Datadir in MySQL?The mysql directory corresponds to the mysql system schema, which contains information required by the MySQL server as it runs. This database contains data dictionary tables and system tables.
How can I change location in MySQL?Changing the default MySQL/MariaDB Data Directory. Step 1: Identify Current MySQL Data Directory. ... . Step 2: Copy MySQL Data Directory to a New Location. ... . Step 3: Configure a New MySQL Data Directory. ... . Step 4: Set SELinux Security Context to Data Directory. ... . Step 5: Create MySQL Database to Confirm Data Directory.. What is the default path for MySQL?For MySQL 5.7 on Windows, the default installation directory is C:\Program Files\MySQL\MySQL Server 5.7 for installations performed with MySQL Installer. If you use the ZIP archive method to install MySQL, you may prefer to install in C:\mysql .
|