Moving the Mysql Data Directory

Published by John on April 4, 2017 Under Sysadmin

Recently, I ran into a space issue on one of my servers and found that 85% of the space was being used by Mariadb(Mysql’s) ibdata1 file and there wasn’t really anywhere else to trim any fat. The ibdata1 file is used to store information about Innodb tables and a rather well known issue is that this file grows over time, but does not shrink.

So, for example, if you were to have an InnoDB database table that was 1GB in size, it might increase ibdata1 by 1GB. Later, if you delete that table, the ibdata1 does not shrink. However, even though it does not shrink in size, it should keep track of how much free space is available and utilize that space first, before it starts growing again.

One of the common recommendations for dealing with this is to export all your databases(except for mysql and performance_schema), drop the databases, delete the ibdata1 and ib_log file, and then re-import them. This should reset your ibdata1 and if you also enable innodb_file_per_table it will result in individual ibdata1 files for each database, making this process less painful down the road if you need to do it again.*

* The above method of shrinking an ibdata1 file just a basic example/overview of the process. If you decide to go this route, please properly research it.

The downside of shrinking the ibdata1 that way is of course the time it is going to take dumping/restoring each database. In mycase, while I will still probabbly do this, I needed a quicker fix and so decided to move the Mysql Data directory to a different partition that had more space.

Moving the Mysql Data Directory

Before you do anything, use mysqldump to backup all your databases! Any sort of change like this has the potential to go wrong, so have a backup in place before you do anything.

The steps below are for a Centos Server, but the steps should be similar on other Linux distributions. The default location for the Mysql Data Folder on Centos is: /var/lib/mysql

You can find it by checking your /etc/my.cnf file and looking for the datadir setting or by using the following query: select @@datadir;

  1. Stop the Mysql Server. On Centos: service mariadb stop
  2. Copy the data folder to the new location, preserving file permissions: cp -rp /var/lib/mysql/ /my_new_location/
  3. Verify the permissions and size of new folder: 
    ls -l /my_new_location/mysql
du -h /my_new_location/mysql
du -h /var/lib/mysql/
  4. Backup your current my.cnf file: cp /etc/my.cnf /etc/my.cnf.back
  5. Edit your my.cnf file to ensure the datadir and socket use the new location: 
    [mysqld]
datadir=/my_new_location/mysql
socket=/my_new_location/mysql/mysql.sock
  6. Add or edit the client section, so it uses the new socket: 
    [client]
socket=/my_new_location/mysql/mysql.sock
  7. Start Mysql Server: service mariadb start
  8. Verify you can login to mysql server and access databases/tables.
  9. If you have issues, stop the server, revert to your old settings using the backup file you made(/etc/my.cnf.back) and restart the server.

After this, you should be able to login to mysql and it should be using the new location. To verify, you can run the query: select @@datadir;

It should show somthing like:

MariaDB [(none)]> select @@datadir;
+-------------------------+
| @@datadir               |
+-------------------------+
| /my_new_location/mysql/ |
+-------------------------+
1 row in set (0.00 sec)

Fixing Issues with Your Webserver

If you are running a web-server w/php(or another language) that access mysql, you will need to make sure it knows where to find the new socket. If you do not, you will get a database connection error, as it will not know where to find the new Mysql Socket.

For PHP, you can edit your php.ini file as follows:

mysqli.default_socket = /my_new_location/mysql/mysql.sock
pdo_mysql.default_socket= /my_new_location/mysql/mysql.sock
mysql.default_socket = /my_new_location/mysql/mysql.sock

Just make sure you use the same value as you did for the socket setting in your /etc/my.cnf file.

After you update your php.ini file, restart your webserver and you should be able to connect to the server.

Other languages, like Java, will probably need to be configured similarly.


No Comments |

Add a Comment