MySQL TutorialIntroduction Installation of MySQL Getting MySQL server information RDBMS Concepts Data Definition Language Data types Managing Databases Managing Tables Managing Views Inserting records in a table Updating records in a table Deleting records from a table Retrieving Records Data Control Language Variables in MySQL Procedures in MySQL Built-In functions in MySQL User Defined functions in MySQL Triggers in MySQL Cursors in MySQL Transaction control Event scheduler Tuning MySQL Server Performance Managing users Importing and Exporting data Database Testing Workbench PHPMyAdmin
Importing and exporting data in MySQL
Exporting database/ Taking back up
To dump the data from any query into the file, you can use below syntax.
mysqldump.exe –e –uroot -p**** -hlocalhost DEALS > “C:Usersdb1.sql” mysqldump.exe –e –uroot -p**** -hlocalhost DEALS > “C:UsersMySQLdb1.sql”
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.
select * from dup into OUTFILE ‘c:\abc.txt’;
To Grant File privilege, you need to execute below statements.
SHOW GRANTS FOR ‘adam’@’localhost’
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.
GRANT FILE ON . to ‘adam’@’localhost’ FLUSH PRIVILEGES;
You can view the file path by executing below query
select * from dup into OUTFILE ‘c:\abc.txt’ Fields terminated by ‘,’ enclosed by ‘”‘ lines terminated by ‘ ’;
SHOW VARIABLES LIKE ‘secure_file_priv’;
You can also use mysqlinput tool to import the data. Another way to load data in a table is by using LOAD command.
mysql –u[user name] -p[password] -h[hostname] [database name] < C:[filename].sql
You can also specify the format of the data being loaded.
LOAD DATA LOCAL INFILE ‘c:\abc.txt’ into dup;
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.
Complex problems, Simple Solutions