一、使用mysql数据导出进行备份时,会备份整个表的数据,有时候只想备份一部分数据,这个时候可以使用如下方法:
1. 使用insert into 和 select结合:
insert into talbe_bak(`id`,`name`)
select `id`,`name` from table_data where stat_time >= "2017-08-30" and stat_time < "2017-9-03";
这样会将满足where限制的数据备份到新的table_bak表中。
如果表结构相同:
insert into talbe_bak
select * from table_original where company_id = 60 and stat_time = "2017-12-30";
如果想备份所有数据:
insert into talbe_bak
select * from table_original;
2. 使用select into outfile将数据备份到文件:
SELECT `id` INTO OUTFILE "/tmp/result.text" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_data;
这种方法需要有运行mysql服务的机器的操作权限。部分云端数据库不会提供对运行mysql服务的机器的操作权限,所以这种方法不一定适用。
3. mysqldump -w
mysqldump -u root -p password table_data –-where='stat_time >= "2017-08-30"' > tmp.dump
mysqldump -h 127.0.0.1 -u test -p <database> --where="date >= '2018-07-18 00:00:00'" > /home/zzz/dump.sql # if no table name, dump data of all tables mysqldump -h 127.0.0.1 -u root -pPassword <database_name> > ./test_database.sql # add password in command, make it possible to run background
mysqldump -h 127.0.0.1 -u test -p <database> <table> --where="date >= '2018-07-18 00:00:00'" > /home/zzz/dump.sql # dump data in table indentified by table_name
mysqldump --skip-triggers --compact --no-create-info
-h 127.0.0.1 -u test -p <database> --where="date >= '2018-07-18 00:00:00'" > /home/zzz/dump.sql
mysqldump -u root -p database_name | mysql -h other-host.com database_name # 直接将数据导出到其他服务器 # 导入备份数据
mysql -u root -h 127.0.0.1 -p <database_name> < /backup/mysql_data/test_database.sql
mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql # mydb, t1, t2, t3三张表的数据导出
mysqldump -u USERNAME -pPASSWORD DATABASE --ignore-table=DATABASE.table1 > database.sql
这种方法可以导出远程服务器的数据。
https://dba.stackexchange.com/a/8892/174516 # 导入到不通数据库
二、如果想在两张结构不同的表之间迁移数据,一张表的一部分字段,需要迁移到另一个表:
insert into data_base.destination (start_time, end_time, sales, prices)
select start_time, end_time, sales, prices from data_base.original;
还可以使用replace into
replace into data_base.destination (start_time, end_time, sales, prices)
select start_time, end_time, sales, prices from data_base.original where start_time > "2017-01-01";
可以使用IGNORE关键字
忽略重复:
insert ignore into data_base.destination (start_time, end_time, sales, prices)
select start_time, end_time, sales, prices from data_base.original;
可以使用ON DUPLICATE KEY UPDATE更新
insert into data_base.destination (start_time, end_time, sales, prices)
select start_time, end_time, sales, prices
from data_base.original
where start_time > "2017-01-01"
on duplicate key update start_time=values(start_time), end_time=values(end_time), sales=values(sales), prices=values(prices);
可以只更新一部分字段:
insert into data_base.destination (start_time, end_time, sales, prices)
select start_time, end_time, sales, prices
from data_base.original
where start_time > "2017-01-01"
on duplicate key update start_time=values(start_time), end_time=values(end_time);
此部分ref:https://segmentfault.com/a/1190000002716966
PS:支持跨库,但是不支持跨服务器
mysqldump: https://www.cnblogs.com/chenmh/p/5300370.html