Running out of Disk Space? Move innodb-tables to another partition (with MySQL 5.6)

Recently i had to manage big database installation that was running out of disk space. The partition on which the mysql datadir was located only had a few gigabytes free. Resizing the partition was not possible without a long downtime so that was no option. The installation had only innodb-tables so using symlinks was also not possible.

Luckily they were using MySQL 5.6 and the server had another partition with more than enough disk space available so i decided to use that partition for the biggest tables.

First get the 15 biggest tables:

root@mysqlserver [(none)]> set global innodb_stats_on_metadata=0; select table_schema, table_name "Table Name",round(sum(data_length+index_length)/1024/1024/1024,4) "Size (GB)" from information_schema.tables group by table_schema,table_name ORDER BY round(sum(data_length+index_length)) DESC LIMIT 15;
 Query OK, 0 rows affected (0.00 sec)

+------------------+-------------------------------+-----------+
 | table_schema | Table Name | Size (GB) |
 +------------------+-------------------------------+-----------+
 | bigdb | really_big_table | 55.2498 |
 | foo | bar | 25.6894 |
 | foo | bar2 | 20.1347 |
 | bigdb | asdf | 14.0842 |
 [...]

So, “bigdb.really_big_table” is the biggest table. Lets check how it was created:


 root@mysqlserver [(none)]> use bigdb;
 root@mysqlserver [(none)]> show create table really_big_table \G
 *************************** 1. row ***************************
 Table: really_big_table
 Create Table: CREATE TABLE `really_big_table` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `foo` varchar(30) NOT NULL DEFAULT '0',
 `bar` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 ) ENGINE=InnoDB AUTO_INCREMENT=1231243249 DEFAULT CHARSET=utf8

Create a new identical table with the data directory on the different partition:

root@mysqlserver [bigdb]> create table really_big_table_new ( \
 -> `id` int(11) NOT NULL AUTO_INCREMENT, \
 -> `foo` varchar(30) NOT NULL DEFAULT '0',\
 -> `bar` int(11) NOT NULL, \
 -> PRIMARY KEY (`id`), \
 ) ENGINE=InnoDB AUTO_INCREMENT=1231243249 DEFAULT CHARSET=utf8 data directory='/path/to/big_partition/mysql-datadir2/';

And then select everything from the current into the new table (Remember to STOP SLAVE if you do that on a SLAVE or LOCK TABLE if you do that on a production server):

root@mysqlserver [bigdb]> insert into really_big_table_new select * from really_big_table ;
Query OK, 38164689 rows affected (15 min 43.07 sec)
Records: 38164689 Duplicates: 0 Warnings: 0

Then drop the current table and rename the new one:

root@mysqlserver [bidb]> drop table really_big_table;
 Query OK, 0 rows affected (0.71 sec)
 root@mysqlserver [bigdb]> rename table really_big_table_new to really_big_table;
 Query OK, 0 rows affected (0.01 sec)

You have successfully moved the biggest to a different partition.

Please keep in mind that if you use binary logging that your binary logs gonna grow very big so maybe you want to purge them afterwards:

http://dev.mysql.com/doc/refman/5.6/en/purge-binary-logs.html

Share/Bookmark

Leave a Reply