Tuesday, November 29, 2022
Google search engine
HomeLinux TutorialsHow to Backup and Restore MySQL Databases using Mysqldump

How to Backup and Restore MySQL Databases using Mysqldump

This tutorial explains how to backup and restore MySQL or MariaDB databases from the command line using the mysqldump utility.
You can also use the mysqldump utility to transfer your MySQL database to another MySQL server.

Mysqldump Command Syntax

Before going into how to use the mysqldump command, let’s start by reviewing the basic syntax.
The mysqldump utility expressions take the following form:

mysqldump [options] > file.sql

options – The mysqldump options
file.sql – The dump (backup) file
To use the mysqldump command the MySQL server must be accessible and running

Backup a Single MySQL Database

he most common use case of the mysqldump tool is to backup a single database.

For example, to create a backup of the database named database_name using the user root and save it to a file named database_name.sql you would run the following command:

mysqldump -u root -p database_name > database_name.sql

You will be prompted to enter the root password. After successful authentication, the dump process will start. Depending on the database size, the process can take some time.

If you are logged in as the same user that you are using to perform the export and that the user does not require a password, you can omit the -u and -p options:

mysqldump database_name > database_name.sql

Backup Multiple MySQL Databases

To backup multiple MySQL databases with one command you need to use the –database option followed by the list of databases you want to backup. Each database name must be separated by space.

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

The command above will create a dump file containing both databases.

Backup All MySQL Databases

Use the --all-databases option to back up all the MySQL databases:

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

Same as with the previous example the command above will create a single dump file containing all the databases.

Backup all MySQL databases to separate files

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

Restoring a MySQL dump

Restoring a MySQL dump
You can restore a MySQL dump using the mysql tool. The command general syntax is as follows:

mysql  database_name < file.sql

In most cases you’ll need to create a database to import into. If the database already exists, first you need to delete it.

Restore a Single MySQL Database from a Full MySQL Dump

If you backed up all your databases using the -all-databases option and you want to restore a single database from a backup file which contains multiple databases use the –one-database option as shown below:

mysql --one-database database_name < all_databases.sql

Export and Import a MySQL Database in One Command
Instead of creating a dump file from one database and then import the backup into another MySQL database you can use the following one-liner:

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

The command above 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 command, make sure the database already exists on the remote server.

If you have any questions or feedback, feel free to leave a comment.

YOU CAN SUPPORT DEVNINJA WITH A CUP OF COFFEE

As we continue to grow, we would wish to reach and impact more people who visit and take advantage of the guides we have on our blog. This is a big task for us and we are so far extremely grateful for the kind people who have shown amazing support for our work over the time we have been online. to search or browse the published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or more ) as a token of appreciation.

Support Us

DevNinja
DevNinja
System & Network Administrator Ninja
RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

6 − two =

- Advertisment -
Google search engine

Most Popular

Recent Comments