MySql Backup and Restore
has average rating
8
out of 10.
Total 9 users rated.
Description
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 in delimited text files
The abve 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, database is the name of the database.
Take backup of a database
Restore a database
Copying data from one server to another
Where remote_host indicates a remote server where you want to take backup.
Dump several databases with single command
Dump all databases using --all-databases option
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, database is the name of the database.
Where path_to_new_directory is the directory where backup will be taken.
