How to backup and restore MySQL or MariaDB database using mysqldump

In this tutorial we will learn to backup and restore MySQL or MariaDB database using mysqldump.

We will start by first taking backup of just the database structure. By this we mean no data that are inside the tables of the database.

Next we will learn to take backup of database structure and the data saved in it.

Finally we will learn to restore data into a database using the database backup dump we created.

Take backup of MySQL or MariaDB database structure

In the following example we are taking the backup of a MySQL or MariaDB database structure.

$ mysqldump -u [username] -p --no-data [databaseName] > [dumpFileName.sql]

Note! You will be prompted to enter your password when you run the above command in the terminal.

Let's say we want to take the backup of mydb database and save it in a file mydb_dump.sql file. And let's say the username and password is dbuser and dbpassword respectively. So, we will run the following command in the terminal.

$ mysqldump -u dbuser -p --no-data mydb > mydb_dump.sql

If we want to create dump file in a specific location then we will have to write the full path like /workspace/database/mydb_dump.sql and the dump will be created.

Take backup of multiple MySQL or MariaDB database structure

To take structure backup of multiple databases we use the following.

$ mysqldump -u [username] -p --no-data --databases databaseName1 databaseName2 ... > [dumpFileName.sql]

In the following example we are taking backup of 3 databases mydb, blogdb and wpdb.

$ mysqldump -u dbuser -p --no-data --databases mydb blogdb wpdb > mydb_blogdb_wpdb_dump.sql

Take backup of all MySQL or MariaDB databases structure

To take backup of all the databases structure we use the following.

$ mysqldump -u [username] -p --no-data --all-databases > [dumpFileName.sql]

In the following example we are taking backup of all the databases.

$ mysqldump -u dbuser -p --no-data --all-databases > alldb_dump.sql

Take backup of a MySQL or MariaDB database structure and data

To take backup of database structure and data we drop the --no-data option.

$ mysqldump -u [username] -p [databaseName] > [dumpFileName.sql]

In the following example we are taking backup of mydb database structure and data.

$ mysqldump -u dbuser -p mydb > dbdump.sql

Restore data in a MySQL or MariaDB database

To restore first login to MySQL or MariaDB and create the database then exit and then run the following command.

$ mysql -u [username] -p [databaseName] < [dumpFileName.sql]

In the following example we are restoring the data in the mydb database.

$ mysql -u dbuser -p mydb < mydb_dump.sql

Full commands:

$ mysql -u root -p
Enter password:

mysql> create database mydb;

mysql> exit

$ mysql -u dbuser -p mydb < mydb_dump.sql