Wednesday, April 29, 2009

MySQL cheat-sheet

Well, now that MySQL is Oracle's SQL, I dunno how long this information will remain useful. But, here it goes:


If you've installed MySQL by HomeBrew:

mysql.server start


sudo /usr/local/mysql/bin/mysqld_safe
cnt-z, bg

Set root password

mysqladmin -u root -pcurrentpassword password 'newpassword'


mysql -p -u root

Create DB

create database foo;
use foo;

Create User

create user 'bar'@'localhost' identified by 'some_pass';
grant all privileges on foo.* to 'bar'@'localhost';
grant all privileges on foo.* to 'bar'@'localhost' with grant option;

Show Users

select host, user, password from mysql.user;


mysqladmin -p -u root shutdown

Load data from a table

LOAD DATA infile '/temp/myfile.tsv' INTO TABLE my_table IGNORE 1 lines;

You might get ERROR 13 (HY000): Can't get stat of ... caused by permissions. I get around it by giving full permissions to the file and its parent directory. See man stat for more.

Dump and restore data

mysqldump -p -u [user] [dbname] | gzip > [filename]
gunzip < [filename] | mysql -p -u [user] [dbname]

Docs for the mysqladmin tool and other client programs. SQL syntax docs for create table and select, insert, update, and delete.

BTW, where a server isn't needed, I'm starting to like SQLite a lot.

1 comment:

  1. Loading data can also be done through the mysqlimport utility, with the same restrictions on 'stat'ing the file.

    mysqlimport --fields-terminated-by=, --lines-terminated-by="\n" -u me -p mydatabase temp.csv