1

I was restoring a 200 GB database into a newly installed RHEL machine. However, while restoring I ran into no space available issue.

Is it possible to change the directory where the databases are stored?

4

First, I was able to verify that it was related to space issue only using the below mysql query.

mysql -uUSER -p -e 'SHOW VARIABLES WHERE Variable_Name LIKE "%dir"'

The above command returned me the output as,

+---------------------------+----------------------------+
| Variable_name             | Value                      |
+---------------------------+----------------------------+
| basedir                   | /usr                       |
| character_sets_dir        | /usr/share/mysql/charsets/ |
| datadir                   | /var/lib/mysql/            |
| innodb_data_home_dir      |                            |
| innodb_log_group_home_dir | ./                         |
| lc_messages_dir           | /usr/share/mysql/          |
| plugin_dir                | /usr/lib/mysql/plugin/     |
| slave_load_tmpdir         | /tmp                       |
| tmpdir                    | /tmp                       |
+---------------------------+----------------------------+

As we can see from the above command, the datadir is present in /var/lib/mysql. Now, I needed to ensure that /var doesn't have enough space to accomodate the database in which case my detection is correct. I achieved it using the below command.

df -P /var | tail -1 | cut -d' ' -f 1

The above command basically tells the mount point of a particular directory. As I suspected, the mount point was under /dev/sda2 which had only 40 GB space. Now, changing the datadir is straightforward.

Stop the mysql service using,

service mysqld stop

Edit the file /etc/my.cnf to have the datadir to some partition where we have enough space. (This is for RHEL and may vary depending on the OS Version). After changing the datadir, restart the mysql service using the command,

service mysqld start

References

https://stackoverflow.com/questions/17968287/how-to-find-the-mysql-data-directory-from-command-line-in-windows

https://stackoverflow.com/questions/3274354/how-to-find-out-mount-partition-a-directory-or-file-is-on-linux-server

2

Yes, you can change the location where MySQL database data resides by changing the datadir variable in the /etc/my.cnf

*Make sure to backup your data prior to attempting below steps.

1.) Stop the mysqld service (service mysqld stop).

2.) Edit /etc/my.cnf and change the datadir variable to the location you would like (datadir='insert_dir_here'), and make sure to note the previous location for the next step.

3.) Move the data from the old datadir location to the new location.

4.) Start the mysqld service (service mysqld start).

0

Yes, you can change the location where a MySQL database by changing resides using a setting in the my.cnf file.

  • While this provides an answer to the literal question ("is it possible?" "yes"), it would be helpful if you also include information on which settings should be changed to achieve the desired effect. – a CVn Jun 5 '14 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.