Skip to main content

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.