How to export mysql databases with different ENGINE?

Question

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

Answer 1

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 !!!