To dump data in MySQL command line is one of the easiest things to do.  All you have to do is follow these 3 easy steps in your linux command line:

1.  Dump the schema only.  This will  not contain any data, procedures or triggers, but only create info.  This will allow you to easily import your original schema later on.

mysqldump -uroot -proot -d --skip-triggers myDatabase > myDatabase_schema.sql

The -u specifies your username, and must be followed by your username e.g. -uroot. This is the same with your password, -proot.

The -d tells mysqldump to not dump any data in the schema and the –skip-triggers will skip all triggers and procedures.

The myDabase is the name of your database and the > myDatabase_schema.sql is where you want to dump it.

2. Dump the triggers/procedures. This will only contain the triggers and procedures contained within your database.

mysqldump -uroot -proot -d --no-create-info myDatabase > myDatabase_triggers_procs.sql

Here we have a –no-create-info to tell mysqldump that no create statements must be allowed, thus eliminating any schema sql and again the -d parameter to skip all data.

3. Dump the data only without triggers and create statements.

mysqldump -uroot -proot --skip-triggers --no-create-info myDatabase > myDatabase_base_data.sql

Finally we can dump the data only. The –skip-triggers skips all triggers and procedures, whils the –no-create-info skips sql create statements.

This will allow you to have 3 different sql files:

  • myDatabase_schema.sql (only contains the schema)
  • myDatabase_triggers_procs.sql (only contains triggers/procs)
  • myDatabase_base_data.sql (only contains data)

Now to import.

This is fairly trivial.

All you have to remember is to import your data in a sequence.

First the schema, then the triggers and procedures and then finally the base data.  The schema will create your tables, the triggers and procs can then be added to your tables and finally the tables can be populated with data.

1. Import schema

mysql -uroot -proot myDatabase < myDatabase_schema.sql

2. Import triggers/procs

mysql -uroot -proot myDatabase < myDatabase_triggers.sql

3. Import base data

mysql -uroot -proot myDatabase < myDatabase_base_data.sql

And your done. :-)