john pfeiffer
  • Home
  • Categories
  • Tags
  • Archives

Mysql mysqldump csv

[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

  • « unittest data driven dynamic test cases
  • xml minidom »

Published

Oct 22, 2015

Category

sql

~193 words

Tags

  • csv 1
  • mysql 18
  • mysqldump 2
  • sql 18