Friday, December 6, 2013

Mysql commands

Login to MySQL

mysql --user=xxx --password=xxx --database=xxx

Dump a database

mysqldump --user=xxx --password=xxx dbname

Dump a single table with data

mysqldump --user=xxx --password=xxx dbname tablename

Dump a single table with only some of the data using --where

mysqldump --user=xxx --password=xxx dbname tablename --where="db='blog3'"

Dump a single table with data in tab separated format (for import to XL)

mysqldump --user=xxx --password=xxx dbname tablename -T path
The -T path (example: -T /tmp) tells mysqldump where to create a tablename.sql file with the table definition, and a tablename.txt file with the tab separated data from the table.

Dump a single table structure without the data

mysqldump --user=xxx --password=xxx --no-data dbname tablename

Grant all privileges on a database to a user, be sure to flush privileges after to make the security change effective immediately

GRANT ALL ON db1.* TO 'username'@'localhost';
FLUSH PRIVILEGES;