[TOC]
Export to a CSV File
mysql -uexampleuser -pletmein exampledb -B -e "select * from \`person\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv
mysql -B -hHOST -uUSER -pPASSWORD DATABASE -e "select * from Gateways;" > gateways-tab-delimited.txt
-H and -X options to format output in html and xml respectively
Import from a CSV File
LOAD DATA INFILE '/home/admin/my.csv' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
mysql ... host -uUser -pPass
show databases;
use DATABASENAME;
show tables;
SELECT * FROM tablename; (all columns and rows)
SELECT * FROM tablename LIMIT 5;
SELECT * FROM tablename where lastName like 'user6%';
SELECT type, COUNT(name) FROM products GROUP BY type
UPDATE tablename SET columnname='value in quotes' WHERE lastName like 'user%';
SHOW STATUS;
SHOW PROCESSLIST;
apt-get install mytop
mytop -hhostname -uUser -pPassword
select count(*) as col_0_0_ from FileVer filever0_ where pstfilever0_.ID='ab1fe52'
mysql> create index FileVerIDIndex on FileVer(resourceID);
MYSQLDUMP
mysqldump -P <port#> -h <server ip>-u mysql_user -ppassword database_name table_name > backup.sql
mysqldump -P 3306 -hhostname.rds.amazonaws.com -uroot -ppassword database_name table_name > backup.sql
mysqldump --lock-tables=false -hhostname -uroot -ppassword database_name > backup.sql
mysql -hhostname -uroot -p < tablename-showtables.sql
(show tables;)
mysql -hhostname.amazonaws.com -uroot -ppassword tablename < tablename-showtables.sql
mysqldump --all-databases -hhostname.rds.amazonaws.com -uroot -ppassword > fulldb-dump.sql