Search This Blog

2013/10/22

Mysql Backup & Restore

MYSQL Queries to create CSV file dynamically:

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+


1) How to Export data from mysql Table to plane text file
SELECT *
FROM billing
INTO OUTFILE '/var/lib/mysql-files/Temp.Txt'

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

SELECT *
FROM billing
INTO OUTFILE '/var/lib/mysql-files/Billing.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'


3) How to Import CSV file to mysql table

LOAD DATA INFILE '/var/lib/mysql-files/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. mysqldump -uroot -pcodeit babajis < d:/db.sql

or other way round is
first create database babajis then
source ' d:/db.sql'

When craeting dump of a table bydefault column header not get
added in csv file generated

To get Column Header in output csv file we need to manually
hard code headers as follow

SELECT 'ColName1', 'ColName2', 'ColName3'
UNION ALL
SELECT ColName1, ColName2, ColName3
    FROM YourTable
    INTO OUTFILE '/path/outfile'

No comments:

Post a Comment