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
<div>
</div>To understand what these options means, go to :
http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_compatible