MySQL Server : Open Source Database : MySQL Export / Import
MySQL Import |
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;
MySQL Export |
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
Download Examples |
Recent Comments