MySQL Cheat Sheet

General use

Advanced Queries

Sub Query

Select from an "array" created from another query. You can only pull from a single column. In this example I am looking for users that are members of a group that has access to a directory.

select user_name from groups where group_name = any (select user_group from permissions where directory like '%some/directory%');

Transactions per second

Something helpful for when you are looking to migrate to the cloud.

Alter Table

Add columnn

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

Alter column

alter table

modify column <column name> DECIMAL(10, 2);

Delete rows older than certain date

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

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';

Show last X rows

MariaDB [<database>]> select * from <table> order by <column> desc limit 10;

View users

select host, user, password from mysql.user;

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;

Scripted Secure Installation

UPDATE mysql.user SET Password=PASSWORD('root') WHERE User='root';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;