Creating a dump file of a MySQL database for developement in other machine

Create the dump file using the following :

mysqldump --user=username --password --databases MY_DATABASE --single-transaction --add-drop-table --no-tablespaces --skip-disable-keys > dump_sit.sql

Add -h remote-server-address if you're creating from remote database server.

Afterwards, edit the dump file and put these lines at the beginning:

SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0; And put these lines at the end: SET FOREIGN_KEY_CHECKS = 1; COMMIT; SET AUTOCOMMIT = 1;

For example, using sed on Linux to insert at the beginning of the file:
sed -i '1s/^/SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0;\n/' dump_sit.sql

Appending at the end of the file :
echo "SET FOREIGN_KEY_CHECKS = 1; COMMIT; SET AUTOCOMMIT = 1;" >> dump_sit.sql

To understand what these options means, go to :
http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_compatible