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.

[root@mysql01 ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7841411
Server version: 10.1.16-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show global status like "Questions";
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| Questions     | 549355331 |
+---------------+-----------+
1 row in set (0.08 sec)

MariaDB [(none)]> show global status like "Uptime";
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Uptime        | 27537171 |
+---------------+----------+
1 row in set (0.00 sec)

To do this without a calculator

MariaDB [(none)]> select (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Questions') DIV (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='UPTIME');
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Questions') DIV (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='UPTIME') |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                           19 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

User Management

Add User

create user 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

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;