Tuesday, October 22, 2013

Mysql Backup & Restore

MYSQL Queries to create CSV file dynamically:

1) How to Export data from mysql Table to plane text file
SELECT *
FROM billing
INTO OUTFILE 'C:/Temp.Txt'

2) How to Export data from mysql table to csv file

SELECT *
FROM billing
INTO OUTFILE 'C:/Billing.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'


3) How to Import CSV file to mysql table

LOAD DATA INFILE 'C:/Billing.CSV'
REPLACE
INTO TABLE billing
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'


4) How to Take Dump of a mysql database
MYSQLDUMP -u username -ppassword databasename > dumpfilename.sql

5) How to Restore dump into mysql database
mysql -u username -ppassword databasename < dumpfilename.sql e.g. "D:\Program Files\MySQL\MySQL Server 5.0\bin"\mysqldump -uroot -pcodeit babajis >d:/db.sql 

No comments:

Post a Comment