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'