How to take mysql database backup and restore

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 backup database file that you want to generate
# mysqdump -u [username] -p[password] [database_name] > [dump_file.sql]

Backup of 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]
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.          

    • Related Articles

    • Restore Service on a Suspended Account for Non-Payment

      E2E Networks sends monthly invoices to all its users on their assigned billing cotacts. To avoid any interruption of services, customers are advised to pay the bills on time. Automated notices to pay the total pending amount are sent to the ...

    • Creating server disk image backup

      Creating a server template can prove to be an important feature to have and therefore we provide you with this option with a seamless interface using which you can create a template of your server with just one-click operation. Let's look at a couple ...

    • All you need to know about MySQL

      About MySQL is an open source database management software which allows users to manage the databases on their servers. Simple command lines with the help of MySQL can serve you very effectively in storing, organizing and retrieving data on your ...

    • FAQ's for Backup Plan and Mean Time to Repair

      Files and folders that are lost or system failures can cause significant losses and headache for you. Having a contingency plan that works fast and seamlessly is crucial for web business. Backup plans from E2E Networks safeguard your business by ...