JavaChap Blog Java and Technology musings for the masses

MySQL Import and Export

Here are few MySQL Queries/commands that i regularly use for exporting and importing MySQL data.  This is for my reference, but just thought this might be useful for newbies.

MySQL Table Data Export and Import

• To export the just table data please use the following query

  SELECT * INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM mytable;
  

• To import the csv file into table execute the following SQL Query.

  LOAD DATA INFILE 'data.txt' INTO TABLE mytable FIELDS TERMINATED BY ',';
  

Copy the contents of one database to another

• First, create the new database

  create database db2;
  

• Then, from the command-line, do the following to copy the contents from db1 to db2.

  $ mysqldump -u root --password=pass db1 | mysql -u root --password=pass db2
  

MySQL Dump

• To export the MySQL database into a dump file, please execute the following command

  $ mysqldump -u root --password=pass db1 > dbdump.sql
  

Create MySQL User

• Creating MySQL user and granting access

  CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'myuser';
  GRANT ALL ON *.* TO 'myuser'@'localhost' IDENTIFIED BY 'myuser';