MySQL Cheat Sheet

General use

Alter Table

Add columnn

alter table <tablename> add COLUMN hash VARCHAR(32) after <name of another column>;

View users

select host, user, password from mysql.user;

Remove history

(this is kept in your home directory)

rm -rf ~/.mysql_history

Remove user

drop user <username>@<host>;

Remove database

drop database <databasename>;

Grant privileges

grant all on <database>.<table> to <user>@<host> identified by '<password>';
grant all on test.* to 'michael'@'172.17.0.34' identified by 'mypassword';

Show granted privileges

SHOW GRANTS FOR 'root'@'localhost';

Delete rows older than certain date

DELETE FROM `table` WHERE `column` < DATE_SUB(NOW(), INTERVAL 3 MONTH);

AWS RDS

After creating the RDS instance, download the CA public key.

wget https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

At this point you can log into the instance using TLS.

mysql -u<root user> -p -h xxxxxxx.xxxxxxxxxx.us-east-2.rds.amazonaws.com --ssl-ca=rds-combined-ca-bundle.pem

Then, as you create users makes sure to require the use of TLS

grant all privileges on <database>.* to '<user name>'@'%' identified by '<password>' require ssl;