3

I'm setting up MySQL Cluster. As it uses ENGINE=NDBCLUSTER, how could I export my databases from MySQL Server specifying the ENGINE?

Is there a way to do this?

All my tables are MYISAM

  • WHat do you want to achieve? If convert table from MyISAM to NDB - ALTER TABLE comments ENGINE=ndb; – Mindaugas Riauba Sep 25 '14 at 19:01
  • I want to export them with ENGINE=NDBCLUSTER not with ENGINE=MYISAM... I'm not able to do that wile the database is now in production. – tachomi Sep 25 '14 at 19:28
  • Do conventional mysqldump and then convert engine=myisam to engine=ndbcluster using sed. Or even text editor with search/replace may suffice. – Mindaugas Riauba Sep 25 '14 at 19:47
  • The thing is that my .sql file is about 50 GB, this could be a problem right? :S – tachomi Sep 25 '14 at 19:56
3

You should create two mysqldumps

  • Schema Only, No Data
  • Data Only, No Schema

Using sed, mysqldump all databases except information_schema, mysql, performance_schema

Create a script with the following commands

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"

SQL="SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')"
DB_CSV=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
DBLIST=`echo "${DB_CSV}" | sed 's/,/ /g'`
#
# Schema Only, No Data
#
mysqldump ${MYSQL_CONN} -d -B ${DBLIST}|sed 's/ENGINE=MyISAM/ENGINE=NDBCLUSTER' > schema_only.sql
#
# Data Only, No Schema
#
mysqldump ${MYSQL_CONN} -t -B ${DBLIST} > data_only.sql

Run this script. You should have two files

  • schema_only.sql
    • Should have all the CREATE TABLE commands
    • Should have no data
    • All ENGINE=MyISAM will now say ENGINE=NDBCLUSTER
  • data_only.sql : All the INSERTs to reload the tables

Run the schema_only.sql` script in the MySQL NDB Cluster setup.

Check to make sure every table was created.

Make sure there are now special options prevent the table creation, such as

  • FULLTEXT indexes
  • ROW_FORMAT option

If something went wrong, you can always hand edit schema_only.sql and reload it.

Once every table is in place, run data_only.sql in the MySQL NDB Cluster.

Give it a Try !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.