Over a million developers have joined DZone.

How to Export and Import MySQL Databases

· Java Zone

Navigate the Maze of the End-User Experience and pick up this APM Essential guide, brought to you in partnership with CA Technologies

You can export the MySQL database with schema table definitions and data separated. Here is a simple bash shell script that will export an database.

# file: database-export.sh
DB_NAME=mydb
DB_SCHEMA_FILE=mydb.sql
DB_DATA_FILE=mydb-data.sql
DB_USERNAME=root
DB_PASSWORD=secret

# export schema only from mysql 
echo "`date` Exporting database $DB_NAME schema to $DB_SCHEMA_FILE"
mysqldump --no-data --routines -u $DB_USERNAME -p$DB_PASSWORD $DB_NAME > $DB_SCHEMA_FILE

# export data only from mysql
echo "`date` Exporting database $DB_NAME data to $DB_DATA_FILE"
mysqldump --single-transaction --quick --no-autocommit --no-create-info --extended-insert=false -u $DB_USERNAME -p$DB_PASSWORD $DB_NAME > $DB_DATA_FILE

echo "`date` Done."

The schema export with "--routines" will export stored proc as well. When exporitng data you have to be little more careful. The "--single-transaction" will ensure your data is good. The "--no-autocommit" can really speed up your import later on certain OS.

Now to import these files back, you can create an empty database and then run the following.

# file: database-import.sh
# import schema and data into mysql
echo "`date` Importing Schema $DB_SCHEMA_FILE into $DB_NAME"
mysql -f -u $DB_USERNAME -p$DB_PASSWORD $DB_NAME < $DB_SCHEMA_FILE
echo "`date` Importing DATA $DB_DATA_FILE into $DB_NAME"
mysql -f -u $DB_USERNAME -p$DB_PASSWORD $DB_NAME < $DB_DATA_FILE

echo "`date` Done."

The import with "-f" option will allow you to continue even if you already have duplicated data in the tables; it will simply ignore and continue.

These two combo commands will allow you to quicly backup and restore a MySQL database.

Enjoy.

PS: I have come to learn that "mysqldump" with procedures will insert an extra line like "DEFINER 'user'@'hostname'", and it may create problem if you use different users to export/import or even run the application! Unfortunately there is no exclude from the "mysqldump" tool. So you have to manually strip that off if that's creating a problem for you.

mysqldump --no-data --routines -u $DB_USERNAME -p$DB_PASSWORD $DB_NAME | perl -pi -e 's/DEFINER="\w+"@"\w+" //g' > $DB_SCHEMA_FILE

You can vote the issue here: http://bugs.mysql.com/bug.php?id=24680


Thrive in the application economy with an APM model that is strategic. Be E.P.I.C. with CA APM.  Brought to you in partnership with CA Technologies.

Topics:

Published at DZone with permission of Zemian Deng, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}