1. Overview
MySQL is a popular open-source database management system that we can use for storing and manipulating data in various applications. MySQL stores its database files in different locations depending on the operating system and the configuration settings.
In this tutorial, we’ll see how to find out where MySQL stores its database files on Linux, and how to change the location if needed.
Also, we ran this code on Ubuntu 20.04.3 with MySQL version 8.0 and root permission to execute the commands.
2. Default Location
By default, MySQL stores database files in /var/lib/mysql. However, we can override the location in the configuration file. Typically, this is the /etc/mysql/mysql.conf.d/mysqld.cnf file.
To find out the current data directory, we can use a basic select command at the mysql prompt:
mysql> select @@datadir;
+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)
This means that MySQL is using /var/lib/mysql as the data directory, where it stores all the database files.
3. Changing the Location
Sometimes, we might want to change the location of MySQL database files for different reasons:
- security
- performance
- backups
To do so, we need to edit the configuration file and change the datadir option. For instance, let’s see how we can change the database files directory to /home/mysql.
3.1. Stop MySQL
Firstly, we’ll stop MySQL to ensure the integrity of the data and prevent any corruption:
$ service mysql stop
In this case, we use service to stop the relevant service.
3.2. Copy Existing Database Directory
Next, let’s copy the existing database directory to the new location with cp:
$ cp -Rp /var/lib/mysql /home/
This’ll create a /home/mysql directory with all the database files inside. Furthermore, we use the -R flag to copy all the files and subdirectories inside the source directory recursively. In addition, we use the -p flag to keep the same permissions, ownership, and timestamps of the original files.
Moreover, it’s a good practice to keep a backup of the original data in case something goes wrong:
$ mv /var/lib/mysql /var/lib/mysql_bkp
In this case, we rename the current directory to mysql_bkp.
3.3. Pointing to the New Location
At this stage, we’ll point MySQL to the new data location by editing the configuration file at /etc/mysql/mysql.conf.d/mysqld.cnf.
In particular, we’ll edit the line that starts with datadir and change it to /home/mysql:
datadir=/home/mysql
Notably, we might have to uncomment the line if it’s commented.
3.4. Restarting MySQL
Finally, we can restart MySQL and confirm that our data directory has changed:
$ service mysql start
$ mysql "select @@datadir;"
+-----------------+
| @@datadir |
+-----------------+
| /home/mysql/ |
+-----------------+
1 row in set (0.00 sec)
The output above means that MySQL is now using /home/mysql as the data directory.
4. Conclusion
In this article, we learned how to find and change the location of MySQL database files on Linux. Also, we saw how to use the select @@datadir; command to check the current data directory. In addition, we discussed how to safely change our current data directory.