How to BackUp and Restore MySQL/MariaDB Database

In this article, we are going to shows that how to BackUp and Restore MySQL/MariaDB Database using Mysqldump.

The mysqldump is a database backup program that is used to backup and restore MySQL/ MariaDB database using command line.

BackUp and Restore MySQL/MariaDB Database

Mysqldump Command Syntax

In the Linux, the basic syntax of the mysqldump command is show as below:

$ mysqldump [options] > filename.sql

Here:

• options – The mysqldump options
• file.sql – The backup file

Backup a Single MySQL Database 

To backup a single mysql Database, run the below command:

$ mysqldump -u root -p database_name > database_name.sql

Replace database_name with your database and database_name.sql is the backup file name that is created when you run the above command.

When you run the above command, It will prompt you to enter the root password. If authentication is success, the backup process will start and will take time depending on the size of your database.

And If you are logged in as same user then the user does not require a password for authentication, so you can omit the -u and -p options:

$ mysqldump database_name > database_name.sql

Backup Multiple MySQL Databases at Once

Mysqldump is also allow you to take the backup of multiple databases at once using the --database option. Run the below command:

$ mysqldump -u root -p --databases database_name_first database_name_second > databases_first_second.sql

When you run the above command, it will create a backup file which contains the both databases.

Backup All MySQL Databases at once

You can also take the backup of all database in a single command using the --all-databases option:

$ mysqldump -u root -p --all-databases > all_databases.sql

The above command will create a backup file which contains the all the databases.

Backup all MySQL databases to separate files at once

Mysqldump command doesn’t have any option to backup all databases to separate files but you can easily do that using a simple bash FOR loop :

for DB in $(mysql -e 'show databases' -s --skip-column-names); do
    mysqldump $DB > "$DB.sql";
done

Create a Compressed MySQL Database Backup file

To create a compressed MySQL database backup file ,simply pipe the output of mysqldump to the gzip command, and redirect it to a file as shown in below:

$ mysqldump database_name | gzip > database_name.sql.gz

Create a Backup with Timestamp

To create a backup file with timestamp, run the below:

$ mysqldump database_name > database_name-$(date +%Y%m%d).sql

When you run the above, it will create a file with the following format database_name-20201003.sql

Restore MySQL/MariaDB Database

You can easily restore a MySQL backup(dump )using the mysql too. The basic syntax to restoring a MySQL dump shown as below:

$ mysql database_name < file.sql

You will need to create a database to import into it. If the database already exists then first delete it.

Restore a Single MySQL Database from a Full MySQL Dump

If you have a backup file includes all your database and you only want to restore only a single database from a full backup file then use --one-database option:

$ mysql --one-database database_name < all_databases.sql

Export and Import a MySQL Database at once

To creating a backup file from one database and then import the backup into another MySQL database then you can use the below command to do that:

$ mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

When you run the above command, it will pipe the output to a mysql client on the remote host and it will import it into a database named remote_database_name.

Before running the above command, make sure that the database is already exists on the remote server.

That’s all

If you face any error and issue in above steps , please use comment box below to report.