Cheatsheet: MySQL

Server operations

start a server

mysql.server start

stop a server

mysql.server stop

open prompt/shell

mysql -h "server-name" -u "root" "-pXXXXXXXX" "database-name"

execute command from a shell

mysql -h "server-name" -u "root" "-pXXXXXXXX" "database-name" < "filename.sql"

Create new database and user

open mysql prompt as root (password is empty)

mysql -u root -p

create database

create database my_db;
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 my_db.* TO 'my_user'@'localhost';

check permissions

SHOW GRANTS FOR 'my_user'@'localhost';

mysql -u my_user --password=my_password

Database operations

list all databases

show databases;

use a database

use my_db;

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;

PrivacyTermsFAQChangelogContact Us

Have kids? Check our other webiste