Importing and exporting data in MySQL

Exporting database/ Taking back up

 
mysqldump.exe –e –uroot -p**** -hlocalhost DEALS > “C:Usersdb1.sql”
mysqldump.exe –e –uroot -p**** -hlocalhost DEALS > “C:UsersMySQLdb1.sql”

To dump the data from any query into the file, you can use below syntax.

 
select * from dup into OUTFILE ‘c:\abc.txt’;

Note that the file should not exist there before executing the query. If the file is already there, you will get error saying can not overwrite, file already exists. To run above query, you should have File creation privilege. You can check the privileges of any user by executing below command.

 
SHOW GRANTS FOR ‘adam’@’localhost’

To Grant File privilege, you need to execute below statements.

 
GRANT FILE ON . to ‘adam’@’localhost’
FLUSH PRIVILEGES;

If the server is running with –secure-file-priv option, you will not able to execute above query. To disable secure-file-priv option, you will have to comment this in my.ini file. You can also specify the format of the output file as shown below.

 
select * from dup into OUTFILE ‘c:\abc.txt’
Fields terminated by ‘,’ enclosed by ‘”‘
lines terminated by ‘
’;

You can view the file path by executing below query

 
SHOW VARIABLES LIKE ‘secure_file_priv’;

Importing database

 
mysql –u[user name] -p[password] -h[hostname] [database name] < C:[filename].sql

You can also use mysqlinput tool to import the data. Another way to load data in a table is by using LOAD command.

 
LOAD DATA LOCAL INFILE ‘c:\abc.txt’ into dup;

You can also specify the format of the data being loaded.

 
LOAD DATA LOCAL INFILE ‘c:\abc.txt’ into table dup

Fields terminated by ‘,’ enclosed by ‘”‘

lines terminated by ‘
’;

Here is the output of above example.