mysqldump使用场景

1、导出多张表 mysqldump -uroot -pdbpasswd dbname test1 test2 test3 --single-transaction >db.sql; 2、导出where条件的数据，并加gzip压缩 mysqldump -uname -ppwd dbname tbname --single-transaction --where="id>10" | gzip > backupfile.sql.gz 相应的导入： gunzip解压： gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename 3、mysqldump导出全部数据 mysqldump -uroot -p --master-data=2 --single-transaction -R -E --triggers -A -q > all.sql 其中--master-data=2代表备份时刻记录master的Binlog位置和Position，--single-transaction意思是获取一致性快照，-R意思是备份存储过程和函数，--triggres的意思是备份触发器，-A代表备份所有的库。更多信息请自行mysqldump --help查看。

导出文本为csv格式

mysql -udba -pXZ5rjoVvWrPp --socket=/data/mysql_3309/mysql.sock -e "SELECT id_card,adjust_amount,get_package_time from houbank_applet.t_applet_package_user INTO OUTFILE \"/var/lib/mysql-files/test.csv\" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';" select into outfile用法 SELECT ... FROM TABLE_A INTO OUTFILE "/path/to/file" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; load data infile用法 LOAD DATA INFILE "/path/to/file" INTO TABLE table_name; 注意：如果导出时用到了FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'语句，那么LODA时也要加上同样的分隔限制语句。还要注意编码问题。

导出文本为txt格式

mysql -udba -pXZ5rjoVvWrPp --socket=/data/mysql_3309/mysql.sock -e "SELECT id_card,adjust_amount,get_package_time from houbank_applet.t_applet_package_user;" > /root/scripts/output/user01.txt