MySQL Tutorial
Introduction 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 PHPMyAdminImporting 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.
Web development and Automation testing
solutions delivered!!