Cheatsheet: MySQL

Server operations

start a server

mysql.server start

stop a server

mysql.server stop

open prompt/shell

mysql -h "{{server}}" -u "{{username}}" "-p{{password}}" "{{database}}"

specify port explicitly

mysql -h "{{server}}" -P {{port}} -u "{{username}}" "-p{{password}}" "{{database}}"

execute command from a shell

mysql -h "{{server}}" -u "{{username}}" "-p{{password}}" -e 'SHOW DATABASES'

execute command from a file

mysql -h "{{server}}" -u "{{username}}" "-p{{password}}" "{{database}}" < "filename.sql"

If installed with homebrew

start a server

brew services start mysql

stop a server

brew services stop mysql

Create new database and user

open mysql prompt as root (password is empty)

mysql -u {{username}} -p

create database

create database {{database}};
set global log_bin_trust_function_creators=1;

create user with a password

CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_password';

grant permissions to a db

GRANT ALL PRIVILEGES ON {{database}}.* TO 'my_user'@'localhost';

check permissions

SHOW GRANTS FOR 'my_user'@'localhost';

mysql -u {{username}} --password={{password}}

Database operations

list all databases

show databases;

use a database

use {{database}};

list all tables

show tables;

describe a table

describe my_table;

query tables size information

SELECT 
  table_schema AS `Database`, 
  table_name AS `Table`, 
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS `Size (MB)` 
FROM 
  information_schema.tables 
WHERE 
  table_schema = '<YOUR DB NAME>'
ORDER BY 
  (data_length + index_length) DESC;

Resource Group Operations

list all resource groups

SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS;

create a new resource group

CREATE RESOURCE GROUP rg_name TYPE = USER VCPU = 0-3 THREAD_PRIORITY = 5;

alter an existing resource group

ALTER RESOURCE GROUP rg_name VCPU = 0-5 THREAD_PRIORITY = 10;

drop a resource group

DROP RESOURCE GROUP rg_name;

assign a resource group to a thread

SET RESOURCE GROUP rg_name FOR thread_id;

use a resource group in a query using a comment hint

SELECT /*+ RESOURCE_GROUP(rg_name) */ * FROM my_table WHERE column = 'value';