mysql-cheat-sheet.md

MySql Cheat Sheet

TOC

• General
• Users, privileges
• Table structure
• Relations
• Misc

---

General

host: localhost, %, 127.0.0.1, %.example.com, 192.168.1.%, 192.168.1.0/255.255.255.0

Create database

CREATE DATABASE `<database>` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Users, privileges

List users

SELECT `User`, `Host` FROM `mysql`.`user`;

Add a user

CREATE USER '<user>'@'<host>' IDENTIFIED BY '<pass>';

Remove a user

As of MySQL 5.1+ it will remove privileges as well.

DROP USER '<user>'@<host>;

Show privileges

SHOW GRANTS FOR '<user>'@'<host>';

Grant privileges

GRANT SELECT, INSERT, UPDATE, DELETE ON <database>.* TO '<user>'@'<host>';
FLUSH PRIVILEGES;

Revoke privileges

REVOKE INSERT, UPDATE, DELETE ON <database>.* FROM '<user>'@'<host>';
FLUSH PRIVILEGES;

Privileges list

• data: SELECT, INSERT, UPDATE, DELETE, FILE
• structure: CREATE, ALTER, INDEX, DROP, CREATE TEMPORARY TABLES, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, CREATE VIEW, EVENT, TRIGGER
• administration: GRANT, SUPER, PROCESS, RELOAD, SHUTDOWN, SHOW DATABASES, LOCK TABLES, REFERENCES, REPLICATION CLIENT, REPLICATION SLAVE, CREATE USER

TOC

Table structure

Create table

CREATE TABLE [IF NOT EXISTS] `<table_name>` (
...
) ENGINE InnoDb CHARACTER SET utf8 COLLATE utf8_general_ci;

Rename table

RENAME TABLE `<database>`.`<old_table_name>` TO `<database>`.`<new_table_name>`;

Remove table

DROP TABLE [IF EXISTS] `<table_name>`;

TOC

Relations

Show relations

SHOW CREATE TABLE `<table_name>` \G

Add relation

ALTER TABLE `<table_name>` ADD CONSTRAINT `<relation_name>` FOREIGN KEY `<index_name>` (`<column_name>`) REFERENCES `<related_table_name>` (`<related_column_name>`) ON DELETE [RESTRICT | CASCADE | SET NULL | NO ACTION];

Remove relation

ALTER TABLE `<table_name>` DROP FOREIGN KEY `<relation_name>`;

TOC

Misc

Foreign key check

SET foreign_key_checks = 0;
SET foreign_key_checks = 1;

Reset auto increment

ALTER TABLE `<table_name>` AUTO_INCREMENT = 1;

Concatenate (non NULL) values from a group

SELECT `movie`, GROUP_CONCAT(`time` SEPARATOR ',') AS `running_times` FROM `schedule` WHERE `movie` = 'A Beautiful Mind' GROUP BY `movie` ORDER BY NULL

Search & Replace Data

UPDATE `<table_name>` SET `<field_name>` = REPLACE(`<field_name>`, '<string_to_find>', '<string_to_replace>');

Troubleshooting

SHOW ENGINE INNODB STATUS

Disable cache

SELECT SQL_NO_CACHE ...; (per query)

SET SESSION query_cache_type=0/1/2; (per session, use 0 to turn OFF, 1 to turn ON)

SHOW VARIABLES LIKE 'query_cache_size';
SET GLOBAL query_cache_size=0 (globally)
SET GLOBAL query_cache_size=<old_value>

Backup/Restore

$ mysqldump -u <user> -p <database> > <filename.sql>
$ mysql -u <user> -p <database> < <filename.sql>

TOC