mysql导入导出数据

导出某表某些数据

mysql -uroot -p -S /tmp/mysql_3308.sock db_settle -e "SELECT

settle_no,

partner,

partner_name,

null as 'out_partner',

null as 'out_partner_name',

contract_no,

trade_count,

trade_amount,

refund_amount,

settle_amount,

refund_count,

fee,

open_fee,

service_fee,

remit_amount,

settle_time,

start_time,

end_time,

create_time,

modify_time,

pay_total_no,

remit_time,

status,

remit_suc_time,

err_code,

err_msg,

null as 'user_profit_amount',

settle_type,

case when busi_type1 is null then 0 elsebusi_type1 end as 'trade_type_1',

case when busi_type2 is null then 0 elsebusi_type2 end as 'trade_type_2',

case when busi_type3 is null then 0 elsebusi_type3 end as 'trade_type_3',

task_time

FROM db_settle.t_settle_summary

where create_time >= '2018-02-0100:00:00' INTO OUTFILE'/mysqldata/mysql3308/t_settle_summary_new.bak'"

 

导出数据导入到另一表：

LOAD DATA INFILE"/mysqldata/t_settle_summary_new.bak" into tabledw.t_settle_summary_20180207;

 

Mysqldump导出表（只导出数据，不含建表语句）

备份原表：

mysqldump -uroot -p --skip-extended-insert--databases dw --tables t_transfer_20180207 > t_transfer_20180207.bak

 

mysqldump -uroot -p  --no-create-info --skip-extended-insert--databases dw --tables t_transfer_20180204 > t_transfer_20180204.bak

 

sed -i 's/`t_transfer_20180204`/`t_transfer_20180207`/g't_transfer_20180204.bak

 

导入，shell导入比source快一点儿

source /data/t_transfer_20180204.bak

mysql -uroot -p dw <t_transfer_20180204.bak

 

 

896 mysqldump -uroot -p dw t_bank_tran_20171120--where="create_time<'2016-12-04 00:04:22'" >/data/sql/tran_test.sql

  903  mysqldump -uroot -p --skip-opt  dw t_bank_tran_20171120--where="create_time<'2016-12-04 00:04:22'" >/data/sql/tran_test.sql

  906  mysqldump -uroot -p --skip-opt--no-create-info  dw t_bank_tran_20171120--where="create_time<'2016-12-04 00:04:22'" >/data/sql/tran_test.sql

  909  mysqldump -uroot -p --skip-opt   dw t_bank_tran_20171120--where="create_time<'2016-12-04 00:04:22'" >/data/sql/tran_test.sql

  912  mysqldump -uroot -p    dw t_bank_tran_20171120--where="create_time<'2016-12-04 00:04:22'" >/data/sql/tran_test.sql

innobackupex --no-timestamp --user=backup--password=xxx --parallel=4 --slave-info --safe-slave-backup--socket=/tmp/mysql_3309.sock /newmysqldata/orderdb

innobackupex  --user=backup --password=xxx --parallel=4--slave-info --safe-slave-backup --socket=/tmp/mysql_3309.sock  /newmysqldata/orderdb

innobackupex  --defaults-file=/mysqldata/mysql3309/my.cnf --user=backup--password=xxx --parallel=4 --slave-info --safe-slave-backup--socket=/tmp/mysql_3309.sock /newmysqldata/orderdb