MySQL Database Import/Export

Quick dump large MySQL database import/export tips.

Dump a MySQL database to file

bash

1mysqldump -u username -p database_name > data-dump.sql

Import a MySQL database from file

bash

1mysql -u username -p new_database < data-dump.sql

For quick large MySQL database import use:

Manual mode by copy paste in terminal.

Start of file

bash

1sed -i '1s/^/SET autocommit = 0;\n/' dump.sql

End of file

bash

1echo 'COMMIT;' >> dump.sql

Or create bash script to run the above commands.

bash

1#!/bin/bash
2# Check if filename is provided as argument
3if [ $# -eq 0 ]; then
4    echo "Usage: $0 <filename>"
5    exit 1
6fi
7
8filename=$1
9
10# Add 'SET autocommit = 0;' at the beginning of the file
11sed -i '1s/^/SET autocommit = 0;\n/' "$filename"
12
13# Append 'echo 'COMMIT;' to the end of the file
14echo 'COMMIT;' >> "$filename"
15
16echo "Now import it. quickly :)"