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]
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.
mysql -u [username]-p [password][new database]< [dump file]
mysql -u root -p pass123 newdb < olddb_backup.sql