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:

[mysqldump]<br> user = my_mysql_username<br> password = my_password

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 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