MySQL Helpful Commands for copying / moving / dump / importing data

Mysql copy table from one schema to other on CL
mysqldump -uuser -ppassword schemaname tablename | mysql -u -p schemaname



Make copy of a table on MySQL Client
CREATE TABLE newtable LIKE oldtable;
INSERT INTO newtable SELECT * FROM oldtable;


Mysql move table from one schema to other on MySQL Client
RENAME TABLE old_db.table TO new_db.table;


Mysql dump the functions and stored procedures only to an output file on CL
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt schemaname > outputfile.sql


Copy table from one server to another server on CL
mysqldump --opt --quick --single-transaction --host=sourceserver -uuser -ppassword schemaname tablename | mysql -h targetserver -uuser -ppassword schemaname


Copy routines only of one table to another server on CL


mysqldump --host=sourceserver --routines --no-create-info --no-data --no-create-db --skip-opt schemaname > outputfile.sql | mysql -h targetserver -uuser -ppassword schemaname


Import csv from one server to another on CL
mysqlimport -h targetserver --verbose --local --default-character-set=utf8 --columns="column1,column2..."  --fields-terminated-by=","  -uuser -ppassword schemaname filename


Import csv when fields have commas in them on CL
mysqlimport -h targetserver --verbose --local --ignore-lines=1 --default-character-set=utf8 --columns="column1,column2..."  --fields-terminated-by="," --fields-optionally-enclosed-by="\"" -uuser -ppassword schemaname filename

Insert the results of the query in a file on local

select column1, column2... into OUTFILE '/tmp/nameoffile.csv'
from tablename
where condition