导出

1. 仅仅导出数据到文件中（默认tab分割）

select * from UM_USER into outfile '/tmp/UM_USER.txt';

2. 按指定格式导出

    字段分割用 逗号，内容用引号，换行用\n    ( CSV 格式)

select * from UM_USER into outfile  '/tmp/um_1.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
select * from UM_USER into outfile  '/tmp/um_4.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

3. 带建库建表 语句的导出方式

mysqldump -uroot -p -h172.29.4.9 --opt --databases --routines --events --flush-logs --single-transaction  --default-character-set=utf8 bitmap_enterprise | gzip > /tmp/bitmap_enterprise.sql.gz

4. 导出指定表的数据和结构

mysqldump  enterpriseuser -uroot -p123456 --tables deviceuser > /tmp/deviceuser_0531.sql

5. 导出指定库的数据

mysqldump  --single-transaction --master-data=2 -uroot -p123456  bitmap_enterprise > /tmp/bitmap_enterprise_0531.sql

6. 导出表数据以及结构

mysqldump  database -u username -ppassword --tables table_name1 table_name2 table_name3>D:\db_script.sql

导入

1. mysql命令导入

mysql -ua3 -pa32015 -h172.23.6.96 < /tmp/um.sql
mysql -uroot -p123456 enterpriseuser < /tmp/deviceuser_0531.sql

2. source导入

source /tmp/um.sql

3. load data导入

load data local infile '/tmp/UM_USER_1.txt' into table UM_USER FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';   

--只导出表结构
mysqldump -uroot -p123456 -d dbname table1 table2 > dbname.sql
--只导出数据
mysqldump -uroot -p123456 -t dbname table1 table2 > dbname.sql
--带条件导出数据
mysqldump -uroot -p -h192.168.31.1 -t enterprise event_device_bitmap  --where "starttime_day < 20211001 limit 100"> event_device_bitmap.sql