Thursday Apr 11, 2013

Converting InnoDB Tables to MySQL Cluster

If you're considering using MySQL Cluster, you might be tempted to try it out on one of your existing databases. MySQL Cluster 7.3 (available as a development release at the time of this post) supports foreign keys, so it seems sensible to try migrating an InnoDB database with foreign keys to MySQL Cluster.

What won't work

For this example, I'm using the world_innodb.sql script available from the MySQL sample databases. It's a simple database with three tables: Country, City, and CountryLanguage. Both City and CountryLanguage have foreign keys referencing the Country table's Code field.

So, you might try something like this:


Sadly, this won't work; InnoDB won't let you convert a table from InnoDB to another engine if the table is at either end of a foreign key, to avoid dangling constraints. If you try to convert an InnoDB table to NDB using one of the above statements, this happens:

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Now, the clever among you might be aware of the  foreign_key_checks variable, which disables InnoDB's foreign key constraint checking when you turn it off. It's useful when you're importing data from a script and don't want to check constraints until the import has finished. However, you can't use it to switch off contraints when changing a table's storage engine; it's designed to be used temporarily, for data, so it won't allow dangling constraints. Similarly, the ndb_deferred_constraints variable can't be used for this purpose either, because it doesn't affect InnoDB's constraint protection.

So how do I do it?

There are two ways to migrate InnoDB tables with foreign keys to NDB.

  1. Dump the database and edit the script so each table specifies ENGINE=NDB before re-importing the script to a new database
  2. Drop the constraints, alter the tables to use the NDB engine, and recreate the constraints

Dumping the database and editing the script is a straightforward use of mysqldump and a text editor.

Dropping and recreating the constraints is more interesting, so I'll walk through that.

First, I use SHOW CREATE TABLE to see the constraints' definitions, and particularly their names:

*************************** 1. row ***************************
       Table: City
Create Table: CREATE TABLE `City` (
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) 
     REFERENCES `Country` (`Code`)
1 row in set (0.02 sec)

Running the same command for the CountryLanguage table gives me the following constraint (the Country table does not have any foreign keys defined): 

CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) 
    REFERENCES `Country` (`Code`) 

Now I know what they're called, I can drop them:

mysql> ALTER TABLE City 
    -> DROP FOREIGN KEY city_ibfk_1;
Query OK, 4079 rows affected (0.95 sec)
Records: 4079  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE CountryLanguage 
    -> DROP FOREIGN KEY countryLanguage_ibfk_1;
Query OK, 984 rows affected (0.58 sec)
Records: 984  Duplicates: 0  Warnings: 0

Then I can run the ALTER TABLE statements shown at the top of this post:

Query OK, 239 rows affected (4.15 sec)
Records: 239  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE City ENGINE=NDB; Query OK, 4079 rows affected (4.02 sec) Records: 4079  Duplicates: 0  Warnings: 0   mysql> ALTER TABLE CountryLanguage ENGINE=NDB; Query OK, 984 rows affected (4.32 sec) Records: 984  Duplicates: 0  Warnings: 0

Finally, I can recreate the constraints based on their definitions from the output of the SHOW CREATE TABLE statements above:

mysql> ALTER TABLE City 
    -> ADD CONSTRAINT `city_ibfk_1` 
    -> FOREIGN KEY (`CountryCode`) 
    -> REFERENCES `Country` (`Code`);
Query OK, 0 rows affected (1.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE CountryLanguage 
    -> ADD CONSTRAINT `countryLanguage_ibfk_1` 
    -> FOREIGN KEY (`CountryCode`) 
    -> REFERENCES `Country` (`Code`);
Query OK, 0 rows affected (1.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now that we have our constraints back, I can see if they work:

mysql> DELETE FROM Country WHERE Code='IRL';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code)

All is as expected, and now my InnoDB data is working in my cluster. 

Friday Apr 05, 2013

Running Out of Physical Disk Space

Problem: You've a large table (or two) in a database on a partition that's running out of space, and you want to see if you can move that table to another drive.

Solution: Well, several actually. No silver bullet, but several options, some with conditions and some that require preparation. Let's look at some background information first.

How MySQL Stores Data 

OK, that's somewhat of an ambitious heading for an incidental paragraph or two, so to tone it back a bit, I'll summarise briefly.

  • The data directory is where MySQL stores databases, and it's set by the datadir server option. Each database is stored in a subdirectory of the data directory.
    You can also save a considerable amount of space without moving data around, by using features of the various storage engines, for example if you enable compression on InnoDB tables. If you don't use InnoDB, you can consider using the ARCHIVE storage engine to compress data.
  • Storage engines are what MySQL uses to do the grunt work of storing and retrieving data. InnoDB is the default and is quite full-featured and robust, but some systems still use MyISAM, which is fast but not as well-featured or robust. Other storage engines are available.
  • MyISAM and InnoDB store data in the data directory. 
    • MyISAM stores data in .MYD files and index information in .MYI files, both in the database subdirectories mentioned above.
    • InnoDB stores data and index information in .ibd files in the database subdirectories, when you have the innodb_file_per_table option enabled. This is the default in MySQL 5.6, but must be enabled in 5.5; when disabled, InnoDB stores all data and indexes in a shared tablespace in the root of the data directory.

Now you know where the data lives, it's time to look at how to move it somewhere else.

Moving the Whole Data Directory 

As the datadir option controls where MySQL stores its data, one option we have is to move the contents of the current data directory to a new partition, and then change the datadir option to point to that location. 

For example, on my machine, the datadir option points to the /var/lib/mysql directory. As expected, it contains a directory for each of my databases, and it also contains the InnoDB system tablespace files. To increase the space available, I could add a new drive and point the datadir there.

Consider the following scenario:

  • Add a new hard drive, partitioned using a suitable file system such as ext4. That file system is visible to my OS as /dev/sdf1
  • Back everything up to provide a rollback route.
  • Ensure that the datadir option points to the /var/lib/mysql directory (or note the directory otherwise. we'll assume /var/lib/mysql here)
  • Mount the partition in /data by doing the following:
# mount /dev/sdf1 /data
  • Give the mysql user ownership of the new filesystem:
# chown mysql:mysql /data
  • Stop MySQL
  • Copy the contents of /var/lib/mysql to /data
  • # cp -a /var/lib/mysql/* /data 
  • Set the datadir option to point to /data
  • Restart MySQL

Beware of the various Mandatory Access Control subsystems that might affect you. If you've got AppArmor installed, you'll also need to change the profile for mysqld so it can access files in the new directory. If you've got SELinux installed, you'll need to add a context mapping for MySQL.

In fact, you don't even have to point MySQL to the new partition. If the mountain won't go to Mohammed... let's undo the last couple of steps:

  • Assume you haven't set the datadir option to point to /data, and that it still points to /var/lib/mysql. Also assume MySQL is still stopped. Now the data directory contents are at the new location, but on its next boot MySQL still looks in /var/lib/mysql.
  • Unmount the new partition /dev/sdf1 (or you can use its mountpoint, which is easier to type):

# umount /data

  • Mount /dev/sdf1 to /var/lib/mysql

# mount /dev/sdf1 /var/lib/mysql
  • Restart MySQL
Assuming it all works, you should then modify the file /etc/fstab to automatically mount /dev/sdf1 to /var/lib/mysql on reboot, so persisting your new configuration

But I Only Want to Move One Database

Moving a whole database to another location is achieved by using symbolic links. Simple summary (read the link for more details):

  • Make the new partition as in the example above
  • Rather than move the whole data directory, this time just move a single database directory; this only works with innodb_file_per_table enabled, because otherwise the InnoDB data is in the shared tablespace and can't be moved
  • At this point you can either mount the new partition into the database directory, or use symlinks as described in the link above.

But I Only Want to Move One Table

Ah, now we're getting interesting. The technique for doing this depends on the version of MySQL you're running—I'll talk about 5.5 and 5.6—and what storage engine the table uses.

Firstly, in MySQL 5.5 you can use symbolic links only for tables that use the MyISAM storage engine, and only on certain operating systems such as Linux and Oracle Solaris. The technique is similar to that described above, although you can also use the DATA DIRECTORY and INDEX DIRECTORY options to CREATE TABLE when creating the tables if you want to set up the links from within MySQL rather than at the Linux command prompt. You should not use symbolic links to InnoDB tables, because InnoDB stores some information about each table in the shared tablespace, even when you use the innodb_file_per_table option.

MySQL 5.6 also allows you to copy or move InnoDB tables to another server by performing a file copy, by using the transportable tablespaces feature.

In MySQL 5.6 you can also move InnoDB tables to a different location by using the DATA DIRECTORY option to CREATE TABLE. When you do so, the tablename.ibd file is moved to a subdirectory of that location named for the table's database, and a corresponding tablename.isl file created in the database's directory that acts as a link to the table's .ibd file. The .isl file is not an actual symbolic link, but is treated as such by MySQL.

Plenty of Space

All told, you have many options available to you regardless of which storage engine or version you use, although it won't surprise you to know that MySQL 5.6 gives more options than previous versions. Don't forget that modern UNIX-like operating systems often have mandatory access control systems such as AppArmor, SELinux, or Extended Policy, so be sure to do your homework before moving files around.


Jeremy Smyth writes MySQL training courses, and likes exploring interesting questions that come up from novices and experts alike.



« April 2013 »