Tuesday, December 12

How to Backup a Mysql Database

Google+ Pinterest LinkedIn Tumblr +

The backup of data should be one of the top priorities for any business. When you consider that databases can store anything from customer orders to credit card or bank details, the importance of correctly handling that data becomes clear. This article will address a method of efficiently backing up a MySQL database.

If you have shell access to the server on which the MySQL database is stored, by far the easiest method of backup is by using a tool called mysqldump. The most common way to use mysqldump is to invoke the tool and pipe the resulting output to an SQL dump file. This file contains all the SQL statements necessary to recreate that database again, should the original be lost.

mysqldump provides the following syntax:

Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]


Examples:

Backup one database

To backup a database called “Customers” with a username of “root” and a password of “pass123”, you would do the following:

mysqldump -u root -p pass123 Customers > CustomersBackup.sql


Backup specific tables

Here, we will backup table1, table2 and table3 of the Customers database.

mysqldump -u root -p pass123 Customers table1 table2 table3 > CustomersBackup.sql


Backup multiple databases

To backup multiple databases, an additional parameter is required. In this example we will backup the “Customers” and “Orders” databases.

mysqldump -u root -p pass123 --databases Customers Orders > CustomersOrders.sql


Backup all databases

mysqldump -u root -p pass123 --all-databases > alldatabases.sql


Restoring a MySQL database from an SQL dump file.

Syntax:

mysql -u [username]-p [password][new database]< [dump file]

Example:

mysql -u root -p pass123 newdb < olddb_backup.sql


Share.

About Author

Leave A Reply