MySQL database import and export

Here's a run-down of some useful command line commands to manage the import/export operations of your MySQL database using mysqldump and mysql.

Note that the export operations will lock the database down, preventing any non-read operations to be executed on it along with slowing it down significantly.

Of course, if your user has no password, you can omit that parameter from these commands. Beware that by default your data will be exported using the UTF-8 character set, so if your database uses a different encoding, you must add this option to the export commands:

–default-character-set=NAME

To perform a full backup of a single schema, you can run this command from command line:

mysqldump -u YOUR_USERNAME -pYOUR_PASSWORD SCHEMA_TO_EXPORT > BACKUP_FILE.sql

To export multiple schemas into a single backup you can use:

mysqldump -u YOUR_USERNAME -pYOUR_PASSWORD –databases SCHEMA_1 .. SCHEMA_N > BACKUP_FILE.sql

And to export ALL schemas, run:

mysqldump -u YOUR_USERNAME -pYOUR_PASSWORD –all-databases > BACKUP_FILE.sql

Should you need to backup the tables' structure only, run this instead:

mysqldump -u YOUR_USERNAME -pYOUR_PASSWORD –no-data SCHEMA_TO_EXPORT > BACKUP_FILE.sql

If you only need the data instead, you can exclude the CREATE statements from your export with:

mysqldump -u YOUR_USERNAME -pYOUR_PASSWORD –no-create-info SCHEMA_TO_EXPORT > BACKUP_FILE.sql

note that to import said data afterwards, the destination schema must have the exact same structure else it will fail!

Finally, to restore your backup, use:

mysql -u YOUR_USERNAME -pYOUR_PASSWORD SCHEMA_TO_IMPORT_IN < BACKUP_FILE.sql