---
title: MySQL backup
---
 
# 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

```bash
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

```bash
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.

```bash
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**.

```bash
mysqldump -u root -p123 --all-databases > all-databases.sql
```

### Restoring databases in MySQL


The syntax for restoring the databases in **mysql** is as follows

```bash
mysql -u [username] -p[password] [database_name] < [dump_file.sql]
```

The syntax for restoring the databases in **mysql** at **remote host** is as follows

```bash
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.

```bash
mysql -u root -p123 function < function.sql
```

Similarly, you can restore multiple databases and all databases using **mysqldump** program.


---