Create MySQL database back ups using mysqldump

mysqldump is a handy CLI tool that helps you create database backups by creating a .sql dump file that replicates the database schema and content.

Create the dump file

With authentication:

mysqldump -u <username> -p <database> > <dump_name>

Without authentication

If you’ve used something like mysql_config_editor to configure passwordless authentication , you can simply run:

mysqldump <database> > <dump_name>

With CREATE DATABASE statement

By default, mysqldump will not add a CREATE DATABASE IF NOT EXISTS statement but we can tell it do so by passing the -B or --databases command. See man mysqldump for more info.

mysqldump -B <database> > <dump_file>

Import the dump file

Once we’ve created the dump file, we can apply it to an existing database by running:

mysql <database> < <dumpfile>

Sources

• mysqldump reference