mysql mysqldump 导入导出整张表, 按条件导入导出

 

mysql 全表导出导出   sql导出导入

DB_NAME=”xxx”

USER_NAME=”xxx”

PASSWD=”XcuOX+&elyi#H[vPmvzLkIi^Kx7f”

HOST=”xxx”

MYSQL_ETL=”mysql  -h $HOST  -D$DB_NAME -u$USER_NAME  -p$PASSWD  -P4053 –local-infile -e “

### 删除数据

DELSQL_ETL=”mysql  -h $HOST  -D$DB_NAME -u$USER_NAME  -p$PASSWD  -P4053  -e “

#导出整张表

mysqldump  -h $HOST   -u$USER_NAME  -p$PASSWD -P4053 –quick –extended-insert=false suda_portal_data s_suda_project > t.txt    

#导入整张表

mysqldump  -h $HOST   -u$USER_NAME  -p$PASSWD -P4053 suda_portal_data      < channel_topic.sql

#导出sql查询结果

$MYSQL_ETL “SET character_set_results = utf8;select * from s_suda_project” >a.txt

$MYSQL_ETL -N “SET character_set_results = utf8;select * from s_suda_project” >a.txt

-N 不导出标题

#导入sql查询结果

$MYSQL_ETL “LOAD DATA LOCAL INFILE ‘C:\\Users\\wangting1\\Desktop\\loaddata.txt’ INTO TABLE f_suda_data  FIELDS  TERMINATED BY ‘\t'”

  (day_key, site, channel,rcolumn, SHARE, pv,uv)

$MYSQL_ETL “LOAD DATA LOCAL INFILE ‘C:\\Users\\wangting1\\Desktop\\loaddata.txt’ INTO TABLE f_suda_data  FIELDS  TERMINATED BY ‘\t’   (day_key, site, channel,rcolumn, SHARE, pv,uv)”

sql3=”load data local infile ‘$resultpath/result.txt’ into table rpt_sheet_mobileportal_push_detail ;”

echo $dbcreate “$sql3”

$dbcreate “$sql3”

自增的字段可以用 “”  代替

 

 

---

 

命令行下具体用法如下： 

mysqldump -u用戶名 -p密码 -d 数据库名 表名 > 脚本名;

 

导出整个数据库结构和数据
mysqldump -h localhost -uroot -p123456 database > dump.sql

导出单个数据表结构和数据
mysqldump -h localhost -uroot -p123456  database table > dump.sql