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