转载自:https://blog.csdn.net/iefreer/article/details/7740950
1.导出本地数据库数据到本地文件
mysql -A service_db -h your_host -utest -ptest
mysql> select * from t_apps where created>'2012-07-02 00:00:00' into outfile /tmp/apps.csv
2.导出远程数据库数据到本地文件
mysql -A service_db -h your_host -utest -ptest -ss -e "SELECT * from t_apps limit 300;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > apps.csv
(sed部分可略,尤其是处理包含汉字的数据时. 如果内容包含中文,可在select语句前加入set names utf8;)
3.使用mysqldump导出远程或本地数据到本地文件
mysqldump -h your_host -utest -ptest -w "id<300" service_db t_apps > tt.sql
如果只导数据加上 -t or --no-create- info ;
如果只导结构加上 -d or --no-data;
4. mysqldump不能指定导出的列,变通方法如下:
mysql -u USERNAME --password=PASSWORD --database=DATABASE --execute='SELECT `FIELD`, `FIELD` FROM `TABLE` LIMIT 0, 10000 ' -X > file.csv
5. 以下两种方式可以应该也可以
SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'
SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
6.实践中使用如下:
直接导出表push_channel_m1的数据(包括表结构等)
mysqldump -h10.138.230.23 -P4668 -upush_r -p7120e9a36a98640b pushplatform push_channel_m1 --single-transaction > ./push_channel_m1.txt
只导出数据:
mysql -h10.138.230.23 -P4668 -upush_r -p7120e9a36a98640b -e "select channelid,m1 from pushplatform.push_channel_m1" > ./push_channel_m1.txt