Migrating Mysql innoDB or MyISAM database to NDB or mysql cluster

Migrating Mysql innoDB or MyISAM database to NDB or mysql cluster

Create users and schema

create database clusterdb;

CREATE USER ‘user’@’localhost’ IDENTIFIED BY ‘pass’;

GRANT ALL PRIVILEGES ON *.* TO ‘ ‘user’@’localhost’ WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO ‘user’@’%’ WITH GRANT OPTION;

FLUSH PRIVILEGES;

commit;

exit;

In your existing sql dumps , replace all occurrences of InnoDB/ MyISAM with ndb

%s/InnoDB/ndb

%s/MyISAM/ndb

Import the dump to the newly created cluster db

mysql –h host -u user –ppasswd schema < backup.sql

issues during import

  1. Need to change some varchars into TEXT , this means you need to change the table mapping in your app(hibernate mapping files , some code in our dao,service classes) too , think about it.

ERROR 1118 (42000) at line 667: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8052. You have to change some columns to TEXT or BLOBs

2.roller weblogger which we internally use , has prefix keys in its schema. You need to work around that to make it work with ndb.

ERROR 1089 (HY000) at line 1280: Incorrect prefix key; the used key part isn’t a string, the used length is longer than the key part, or the storage engine doesn’t support unique prefix keys

In sql dump change similar entries as below

UNIQUE KEY `ea_name_uq` (`entryid`,`name`(40)),

to

UNIQUE KEY `ea_name_uq` (`entryid`,`name`),

3.ERROR 1005 (HY000) at line 1511: Can’t create table ‘schema.instruments’ (errno: 136)

http://bugs.mysql.com/bug.php?id=28447

4. ERROR 1005 (HY000) at line 2329: Can’t create table ‘schema.purchases’ (errno: 708)

http://lists.mysql.com/cluster/881

for both 3 and 4 , Add extra params to config.ini file

[NDBD]

# IP address of the first storage node

NodeId=3

HostName=domU-12-31-39-0B-79-62.compute-1.internal

DataDir=/usr/local/mysql/data

BackupDataDir=/usr/local/mysql/backup

DataMemory=2048M

MaxNoOfOrderedIndexes =1024

MaxNoOfUniqueHashIndexes = 512

MaxNoOfAttributes=5000


Advertisements

Post a Comment

Required fields are marked *

*
*

%d bloggers like this: