mysql database no space available

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

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

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