Exporting data with mysqldump Table of contents
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
-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
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
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
Comments →Exporting data with mysqldump