How to take mysql database backup and restore
Introduction
It is always important to backup all your databases stored in your server every now and then just to make sure that you don’t come across any situation where you have to repent for not doing so. One method of taking a backup of mysql databases and restoring them is to use mysqldump program. For those who aren’t familiar with mysqldump
“The mysqldump client is a utility that performs logical backups, producing a set of SQL statements that can be run to reproduce the original schema objects, table data, or both. It dumps one or more MySQL database for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.”
The best thing about mysqldump client is that it is really easy to use once you get the hang of it. This article will briefly explain how to take backup of mysql databases and restore them using the mysqldump command.
Creating a backup
The syntax for using mysqldump command to create database backup is as follows
Wherein,
- [username] = Valid MySQL username
- [password] = password of the username
- [database_name] = Name of database whose backup is to be taken
- [dump_file.sql] = Name of the backup database file that you want to generate
mysqdump -u [username] -p[password] [database_name] > [dump_file.sql]
Backup of a single database
Let’s consider we need to take backup of a database with database name function, then the command to be run in the terminal will be as follows
mysqldump -u root -p123 function > function.sql
You won’t receive any confirmation message upon successful execution of the above command. Now the backup of the database function is dumped into a single file called function.sql.
Backup of multiple databases
You can take backup of multiple databases by running just a single command line in the terminal. Let’s take 2 databases function1 and function2 for this example.
mysqldump -u root -p123 --databases function1 function2 > function.sql
Backup of all the databases
If you can take backup of all the databases, you can do so by running a single command in the terminal. Executing this command will dump all the databases into a single sql file all-databases.sql.
mysqldump -u root -p123 --all-databases > all-databases.sql
Restoring databases in MySQL
The syntax for restoring the databases in mysql is as follows
mysql -u [username] -p[password] [database_name] < [dump_file.sql]
The syntax for restoring the databases in mysql at remote host is as follows
mysql -h [hostIP] -u [username] -p[password] [database_name] < [dump_file.sql]
For restoring a single database, you must first create an empty database on the target machine and restore using mysql command.
Please note that if you provide an existing database (with data stored in it) as the target for the restore, all the data will be overwritten upon execution of the following command.
mysql -u root -p123 function < function.sql
Similarly, you can restore multiple databases and all databases using mysqldump program.