In this page, we are going to discuss how to take backups of MySQL databases and restoring them.
Take backup by copying table files
You can simple copy the table files of a MySQL database to a separate location and accomplish a backup. Later, when necessary, you simply copy them back to the original location to restore the database.
If you are using WAMP server for MySQL, then you can find table files within wamp > bin > mysql > mysql5.1.36 > data > databaseName. Where databaseName is a folder whose name is same as your database.
If you have installed MySQL manually, then you can find table files within Program Files > MySQL >MySQL Server 5.0 > data > databaseName. Where databaseName is a folder whose name is same as your database.
Remember in mysql5.1.36 and MySQL Server 5.0, 5.1.36 and 5.0 indicates the version of the MySQL, which differs if you are working on a different version of MySQL.
Take backup of delimited text files
The above MySQL statement will take a backup of the publisher table into a file called publisher_backup.txt located in the C drive of your windows system.
Using LOAD DATA INFILE statement, you can restore data from the delimited text files.
Take backup using mysqldump
mysqldump command can be executed from mysql prompt. For all the code for mysqldump commands bellow, the database is the name of the database.
Take backup of a database
mysqldump database > backup-file.sql;
Restore a database
mysql database < backup-file.sql;
Copying data from one server to another
mysqldump --opt database | mysql --host=remote_host -C database
Where remote_host indicates a remote server where you want to take backup.
Dump several databases with single command
mysqldump --databases database1 [database2 ...] > backup_of_databases.sql
Dump all databases using --all-databases option
mysqldump --all-databases > backup_of_all_databases.sql
Take backup using mysqlhotcopy
mysqlhotcopy command can be used to take backup of MyISAM and ARCHIVE tables. It runs on Unix.
For all the code for mysqlhotcopy commnd bellow, the database is the name of the database.
mysqlhotcopy database [/path_to_new_directory]
Where path_to_new_directory is the directory where backup will be taken.
Save MySQL query results into a text or CSV file
Here is an easy way to write MySQL query results into a .txt or .CSV files through the select statement.
Save query result into a .txt file :
mysql> SELECT code, name, continent FROM country INTO OUTFILE 'e:/country.txt';
Query OK, 239 rows affected (0.03 sec)
Sample from .txt file :
ABW Aruba North America
AFG Afghanistan Asia
AGO Angola Africa
AIA Anguilla North America
ALB Albania Europe
AND Andorra Europe
ANT Netherlands Antilles North America
ARE United Arab Emirates Asia
Save query result into a country.csv file :
mysql> SELECT code, name, continent FROM country INTO OUTFILE 'e:/country.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 239 rows affected (0.00 sec)
Sample from country.csv file :