MySQL Backup and Restore

for a single table backup:

select into statement in mysql: 

Use the CREATE TABLE SELECT syntax.

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

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

SELECT * INTO OUTFILE C:\\publisher_backup.txt FROM publisher;

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.

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

ARG Argentina

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 :

"ABW","Aruba","North America"

"AFG","Afghanistan","Asia"

"AGO","Angola","Africa"

"AIA","Anguilla","North America"

"ALB","Albania","Europe"

"AND","Andorra","Europe"