MySQL Server : Open Source Database : MySQL Export / Import

• MySQL Import
• MySQL Export
• Downloads Examples

mysqlimport : A Data Import utility

The mysqlimport client provides a command-line interface to the LOAD DATA INFILE SQL statement.

shell> mysqlimport [options] db_name textfile1 [textfile2 . . .]

For each text file named on the command line, mysqlimport strips any extension from the file name and uses the result to determine the name of the table into which to import the file’s contents. For example, files named patient.txt, patient.text, and patient all would be imported into a table named patient.

shell> mysql -e 'CREATE TABLE customer(id INT, name VARCHAR(30))' ctest
shell> ed
a
1001     John
1011     James
.
w customer.txt
32
q
shell> od -c customer.txt
0000000   1   0   0  1 \t   J   o   h  n \n   1   0
0000020   1  1 \t   J   a   m   e   s  \n
0000040
shell> mysqlimport --local ctest customer.txt
ctest.customer: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
shell> mysql -e 'SELECT * FROM customer' ctest
+-------+---------------+
| id    | name          |
+-------+---------------+
|  1001 | John          |
|  1011 | James         |
+-------+---------------+

source command

source command help to import large files in windows or linux.

shell>mysql -h 192.160.0.29 -u myDBUser -p my_db
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 177
Server version: 5.7.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>use my_db;
mysql> source /home/anuj/myfolder/scriptfile_name.sql
mysql> show tables;

mysqldump : A Database Backup utility

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

Syntax

shell> mysqldump [options] > dump.sql

shell>mysqldump -u <db_username> -h <db_host_server> 
-p db_name table_name > dump_file_name.sql

Example

shell> mysqldump -h 192.160.0.29 -u myDBUser 
-p my_db > my_db_bkp_04122001.sql
Enter Password : XXXXXX

mysqldump does not dump the INFORMATION_SCHEMA or performance_schema database by default.
To dump any of these, name it explicitly on the command line and also use the –skip-lock-tables option.
You can also name them with the –databases option.

shell> mysqldump -h 192.160.0.29 -u myDBUser 
-p my_db --skip-lock-tables > my_db_bkp_04122001.sql
Enter Password : XXXXXX

Dumping table records into file

shell>mysql -h 192.160.0.29 -u myDBUser -p my_db 
-e "select * from mydb_table where id in (100463, 100729, 101615, 101699);" 
| sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > /home/anuj/mydb_tab_files2.csv