MySQL Cheat Sheet

Table of Contents:

General

Host value examples: localhost, %, 127.0.0.1, %.example.com, 192.168.1.%, 192.168.1.0/255.255.255.0.

Create database 

1

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

Users, privileges

List users 

1

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

Add a user 

1

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

Remove a user

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

1

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

Show privileges 

1

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

Grant privileges 

1
2

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

Revoke privileges 

1
2

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 

1
2
3

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

Copy table structure 

1

CREATE TABLE `<destination_database>`.`<table_name>` LIKE `<source_database>`.`<table_name>`;

Rename table 

1

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

Remove table 

1

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

TOC

Relations

Show relations 

1

SHOW CREATE TABLE `<table_name>` \G

Add relation 

1

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 

1

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

TOC

Misc

Show MySQL version 

1

SHOW VARIABLES LIKE "%version%";

Foreign key check 

1
2

SET foreign_key_checks = 0;
SET foreign_key_checks = 1;

Reset auto increment 

1

ALTER TABLE `<table_name>` AUTO_INCREMENT = 1;

Concatenate (non NULL) values from a group 

1

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 

1

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

Find duplicate records by a specific column 

1

SELECT *, COUNT(<column>) AS dup FROM `<table>` GROUP BY <column> HAVING dup > 1

Delete duplicate records, but leave one 

1
2

DELETE FROM <table> WHERE id NOT IN (SELECT * FROM (SELECT MIN(t.id) FROM <table> t GROUP BY t.<column>) x) -- keep records with the lowest ID
DELETE FROM <table> WHERE id NOT IN (SELECT * FROM (SELECT MAX(t.id) FROM <table> t GROUP BY t.<column>) x) -- keep records with the highest ID
1
2

DELETE t1 FROM <table> t1, <table> t2 WHERE t1.<column> = t2.<column> AND t1.id > t2.id -- keep records with the lowest ID
DELETE t1 FROM <table> t1, <table> t2 WHERE t1.<column> = t2.<column> AND t1.id < t2.id -- keep records with the highest ID

Troubleshooting 

1

SHOW ENGINE INNODB STATUS;

Disable cache

Per query

1

SELECT SQL_NO_CACHE ...;

Per session, use 0 to turn OFF, 1 to turn ON. Accepted <level> values: 0, 1, 2.

1

SET SESSION query_cache_type=<level>;

Globally

1
2
3

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

Backup/Restore 

1
2

$ mysqldump [-h <host>] -u <user> -p [-d|--no-data] <database> [<table> ...] > <filename.sql>
$ mysql [-h <host>] -u <user> -p <database> < <filename.sql>

Duplicate index check

Use pt-duplicate-key-checker from Percona Toolkit (documentation).

Finding out largest tables on MySQL Server 

1
2
3
4
5
6
7
8
9

SELECT CONCAT(table_schema, '.', table_name),
    CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
    CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
    CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
    CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
    ROUND(index_length / data_length, 2) idxfrac
FROM information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10;

TOC

Resources

TOC