MySql Cheat Sheet

mysql-cheat-sheet.md

MySQL Cheat Sheet

TOC

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

---

General

Host value examples: 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>;

Update a users password

SET PASSWORD FOR '<user>'@'<host>' = PASSWORD('<pass>');

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;

Copy table structure

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

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

Show MySQL version

SHOW VARIABLES LIKE "%version%";

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>');

Find duplicate records by a specific column

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

Delete duplicate records, but leave one

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

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

SHOW ENGINE INNODB STATUS;

Disable cache

Per query

SELECT SQL_NO_CACHE ...;

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

SET SESSION query_cache_type=<level>;

Globally

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

Backup/Restore

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

If you are dumping a live database which is large it is advised to use the following options --single-transaction --quick --lock-tables=false

Find Replace in entire database

$ mysqldump -u user -p databasename > ./db.sql
$ sed -i 's/oldString/newString/g' ./db.sql
$ mysql -u user -p databasename < ./db.sql

Duplicate index check

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

Finding out largest tables on MySQL Server

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

• Using group by on multiple columns

TOC