Import and Export Mysql Data

Simple Data Export

  • To export data from table to text file using command line we use the this query
  • mysql> SELECT * FROM user INTO OUTFILE 'c:/mysqldb/user';
    Query OK, 3 rows affected (0.00 sec)
  • We select all rows (3) from the Employee table into the user file located in the c:/mysqldb directory. We need to have permissions to write to that directory.
  • Now delete all records from user table and import data from text file user which is inside the c:/mysqldb directory
  • mysql>DELETE FROM user;
    Query OK, 3 rows affected (0.13 sec)
    mysql>LOAD DATA INFILE 'c:/mysqldb/user' INTO TABLE tkhts.user;
    Query OK, 3 rows affected (0.13 sec)
    Records:3 Deleted:0 Skipped:0 Warning:0
    import export data