drewyangdev/mysql_cheatsheet.md

MySQL Cheatsheet

-- Drew Yang @ DataJoint

Useful MySQL CMD/shell tools

# show wide results
SELECT * FROM table\G;

# Generate random password
pip install horsephrase
horsephrase --c 3 |tr -s ' ' '-'

If possible, all contents follow the order of CRUD(create, read, update, delete)

User Management

# Create user
create user 'username'@'host' identified by 'password';

# Search user
select * from mysql.user;

# Change user's password
ALTER USER user IDENTIFIED BY 'auth_string';

# Delete user
drop user 'username'@'host';

Privilege Management

# Grant privileges
grant usage on `database`.* to 'username'@'host';
grant all privileges on `database`.`table` to 'username'@'host';

# Show privileges by user
show grants for 'username'@'host';

# Show privileges by database
select * from mysql.db where db like 'database%';

# Revoke privileges
revoke usage on `database`.* from 'username'@'host';
revoke all privileges on `database`.`table` from 'username'@'host';

# general read-only
grant select on `database`.`table` to 'username'@'host';

# general read-write
grant select, insert, update, delete, index, references on `database`.`table` to 'username'@'host';

Check Collation and Charset

For Schemas (or Databases - they are synonyms):

SELECT default_character_set_name FROM information_schema.SCHEMATA 
WHERE schema_name = "mydatabasename";

For Tables:

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "mydatabasename"
  AND T.table_name = "tablename";

For Columns:

SELECT character_set_name FROM information_schema.`COLUMNS` 
WHERE table_schema = "mydatabasename"
  AND table_name = "tablename"
  AND column_name = "columnname";

Check space usage by schemas

SELECT table_schema "schema_name",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "size_mb" 
FROM information_schema.tables 
GROUP BY table_schema;

	#!/bin/bash

	s_host=$1
	s_username=$2
	s_password=$3
	t_host=$4
	t_username=$5
	t_password=$6
	schema=$7

	echo $schema

	mysql -u${t_username} -p${t_password} -h ${t_host} -e "create database ${schema};"

	mysqldump -u${s_username} -p${s_password} -h ${s_host} ${schema}\
	--single-transaction --hex-blob --set-gtid-purged=OFF \|
	mysql -u${t_username} -p${t_password} -h ${t_host} -D ${schema}