Exporting data with mysqldump

Exporting data with mysqldump Thumbnail image

The mysqldump command is very useful if you need to make an sql dump (export) from MySql (MariaDB).

Dump all databases as SQL dump

The command is straightforward to use:

mysqldump -u my_mysql_user -p --all-databases > output.sql

(The -u my_mysql_user is for the my_mysql_user user. Obviously change my_mysql_user to whatever mysql user you want to use (You might want to change this to -u root). The -p option lets you enter a password.

Dump all databases as a gzip file

If you want to save the file as a .gzip (to save on disk space) then you should pipe it to gzip (by adding the green part below).

mysqldump -u my_mysql_user -p --all-databases | gzip -3 > output.sql.gz

Using a .my.cnf (defaults file) to store username and password

However, using the -u my_mysql_user -p options isn't ideal if you want to include it in a script. If you want to include mysqldump in a script or crontab then you should use a .my.cnf (defaults file).

Step 1: Create a ~/.my.cnf file

Your .my.cnf file should look something like this:

Be aware of the security issues of having a .my.cnf with user/pass saved! Make sure it isn't readable by other users! Also you can put this file anywhere, and specify its location (as we will do below).

Then you can do this command:

mysqldump --defaults-file=/root/.my.cnf > output.sql

If you are running this from a script that is set to just export the data (no writing any database to the DB) then you should create a new user that is only allowed to do read commands. See this page for an overview. There is no reason to have your actual mysql root user pass saved in a .my.cnf!

How to export just one database using mysqldump

Use the following command to export just one database table.

mysqldump -u my_mysql_user -p table_name_here > output_sql_dump.sql

Or to gzip it:

mysqldump -u my_mysql_user -p table_name_here | gzip -3 > output_gzip.sql.gz

Importing from an sql dump into MySQL or MariaDB

Importing from a sql dump back into mysql is easy. You just use the normal mysql command.

Be sure to have a backup of your current database before you import any data! Do not skip this!

If the sql dump is compressed (like in the gzip examples above) make sure it is uncompressed first

mysql --user=YourUser -p < sql_dump.sql

If your sql dump does not include database information (no `USE your_database_name` in the sql) then you will want to specify which db table to use:

mysql --user=YourUser -p your_db_name < /your_dir/foo/bar/your_sql_dump.sql

webdevetc profile pic
webdevetc

I am a 29 year old freelance backend web developer from London, mostly focusing on PHP and Laravel lately. This is my site - I mostly write about PHP here. Contact me here (especially for any contracting jobs early 2019 in London ;) ).

Leave a Comment