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:

ALTER TABLE Country ENGINE=NDB;
ALTER TABLE City ENGINE=NDB;
ALTER TABLE CountryLanguage ENGINE=NDB;

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:

mysql> ALTER TABLE Country ENGINE=NDB;
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:

 mysql> SHOW CREATE TABLE City\G
*************************** 1. row ***************************
       Table: City
Create Table: CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `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',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) 
     REFERENCES `Country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
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:

mysql> ALTER TABLE Country ENGINE=NDB;
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 Jan 11, 2013

Excluding a Table From Backup

Let's say you have a database that stores not only current transactional data, but also historic data that's unchanging. In particular, you have a large table containing hundreds of gigabytes worth of last year's data, and it won't change. Having backed it up already, you don't need to back it up every time. Is there any way to exclude this table from a backup?

For InnoDB tables with innodb-file-per-table enabled (the default as of MySQL 5.6), MySQL Enterprise Backup supports this feature in inverse. Specifically, you can choose to include specific innodb-file-per-table tables in addition to those stored in the system tablespace.

In order to exclude a specific table, you need to provide a regular expression to the --include option that includes every table except the one you want to exclude. For example, in my sakila database, I know that the payment table is the only one beginning with "p", so if I want to exclude it, I can do this:

# mysqlbackup --datadir=/var/lib/mysql --backup_dir=/backups \
>        --include 'sakila\.[^p].*' backup-and-apply-log

The following link contains detailed information on the --include option:

http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/only-innodb-usage.html

You can also specify filters for non-InnoDB tables using options such as --databases and --databases-list-file. The following link covers a variety of different partial backup techniques:

http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/partial.html 

About

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

Connect

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today