MySQL命令行数据表的导入和导出

一、使用mysqldump和mysql数据表的导出与导入
1、数据表的导出有where子句:
[bjadmin]$ mysqldump -uroot -proot dev2_biz --no-create-db --no-create-info --where="logTime > '2014-11-06 00:00:00'" player_deposit_log > /home/bjadmin/dev_super/player_deposit_log.sql;
[bjadmin]$ 
2、数据表的导出无where子句:
[bjadmin]$ mysqldump -uroot -proot dev2_biz player_deposit_log > /home/bjadmin/dev_super/player_deposit_log.sql;

[bjadmin]$ 

数据表的导入
1、登入mysql后:
mysql> use dev2_biz;
Database changed
mysql> source /home/bjadmin/dev_super/player_deposit_log.sql;
mysql>

2、不用登录myql:
[bjadmin]$ mysql -uroot -proot -e "source /home/bjadmin/dev_super/player_deposit_log.sql" dev2_biz          
[bjadmin]$ 

二、使用select...into outfile和load data infile数据表的导出与导入:
1、数据表的导出
mysql> select * into outfile '/home/bjadmin/dev_super/player_deposit_log.txt'                               
    -> fields terminated by ','
    -> lines terminated by '\n' 
    -> from dev2_biz.player_deposit_log 
    -> where logTime > '2014-11-06 00:00:00'
    -> ;
Query OK, 5 rows affected (0.00 sec)


2、数据表的导入
mysql> load data infile '/home/bjadmin/dev_super/player_deposit_log.txt' into table dev2_biz.player_deposit_log 
    -> fields terminated by ','
    -> lines terminated by '\n'
    -> ;
Query OK, 5 rows affected (0.04 sec)

Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

@see http://dev.mysql.com/doc/refman/5.7/en/load-data.html
The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. 
LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE.(See Section 13.2.9.1, “SELECT ... INTO Syntax”.) 
To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA INFILE. 
The syntax of the FIELDS and LINES clauses is the same for both statements. Both clauses are optional, 
but FIELDS must precede LINES if both are specified.

To ignore foreign key constraints during the load operation, issue a SET foreign_key_checks = 0 statement before executing LOAD DATA.

Examples
To make a backup of an entire database:
shell> mysqldump db_name > backup-file.sql
To load the dump file back into the server:
shell> mysql db_name < backup-file.sql