Cheatsheet: MySQL

Template Values

Port (port)

Username (username)

Password (password)

Database (database)

Server/Hostname (server)

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;