888b d888 .d8888b. .d88888b. 888
8888b d8888 d88P Y88b d88P" "Y88b 888
88888b.d88888 Y88b. 888 888 888
888Y88888P888 888 888 "Y888b. 888 888 888
888 Y888P 888 888 888 "Y88b. 888 888 888
888 Y8P 888 888 888 "888 888 Y8b 888 888
888 " 888 Y88b 888 Y88b d88P Y88b.Y8b88P 888
888 888 "Y88888 "Y8888P" "Y888888" 88888888
888 Y8b
Y8b d88P
"Y88P" 🐬 Awesome MySQL useful queries and commands
Awesome MySQL Queries and Commands 
¶
A curated list of awesome MySQL useful queries and commands. Inspired by awesome-mysql and awesome-bash-commands.
🏅 Of course, this document needs your help, so consider contributing.
Table of Contents¶
Commands¶
Data import¶
Script file¶
Data export¶
Script file¶
Or
mysqldump \
--user=username \
--host=127.0.0.1 \
--protocol=tcp \
--port=3306 -p \
--default-character-set=utf8 \
--skip-triggers \
"database_name" > database_script.sql
GZIP script file¶
Or
mysqldump \
--user=username \
--host=127.0.0.1 \
--protocol=tcp \
--port=3306 -p \
--default-character-set=utf8 \
--skip-triggers \
"database_name" | gzip -c > tables.sql.gz
Use --single-transaction if you got an mysqldump error (because you lack privileges to lock the tables)
Script file with tables only¶
Or
mysqldump \
--user=username \
--host=127.0.0.1 \
--protocol=tcp \
--port=3306 -p \
--default-character-set=utf8 \
--skip-triggers \
"database_name" "table_name1" "table_name2" > mydb_tables.sql
Queries¶
Users and privileges¶
Create a root user equivalent for backward compatibility¶
CREATE USER `my_root_user`@`%` IDENTIFIED WITH mysql_native_password BY 'my_root_pwd';
GRANT Alter, Alter Routine, Create, Create Routine, Create Temporary Tables,
Create User, Create View, Delete, Drop, Event, Execute, File, Grant Option,
Index, Insert, Lock Tables, Process, References, Reload, Replication Client,
Replication Slave, Select, Show Databases, Show View, Shutdown, Trigger, Update,
Super, Create Tablespace
ON *.* TO `my_root_user`@`%`;
Note: The above query creates a user using Native Pluggable Authentication. It can useful for backward compatibility MySQL clients. Due Caching SHA-2 Pluggable Authentication is the default authentication plugin on MySQL 8.
Create a user with specific database privileges¶
CREATE USER `my_user`@`%` IDENTIFIED WITH mysql_native_password BY 'my_password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE
ON `my_database`.* TO `my_user`@`%` WITH GRANT OPTION;
Note: User above is an example-purpose only.
Modify specific user privileges¶
Change an user password¶
Select¶
Finding duplicated values¶
Select one day ago records¶
SELECT *
FROM users
WHERE
`registered` >= CONCAT(SUBDATE(CURDATE(), 1), ' 00:00:00') AND
`registered` < CONCAT(CURDATE(), ' 00:00:00')
Utilities¶
Clean all tables of one existing database¶
Those queries create a database if doesn't exist (optional) and then removes all tables of one specified database. No root privileges are required, only make sure that the user which executes those queries has enough privileges for that particular database.
Warning: This process cleans up the database removing all existing tables permanently. So make sure to do all necessary tests in a development environment first.
-- -----------------------------------------------------
-- `my_database` clean up process
-- -----------------------------------------------------
-- -----------------------------------------------------
-- 1. Create a new `my_database` database if doesn't exits
-- This is optional but requires extra privileges
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `my_database` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
-- -----------------------------------------------------
-- 2. Remove all tables of `my_database` database
-- -----------------------------------------------------
SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
USE `my_database`;
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
FROM information_schema.tables
WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@tables, 'dummy') INTO @tables;
SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;
Show databases size in MB or GB¶
Databases size in GBs
SELECT
TABLE_SCHEMA "DB_NAME",
SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024), 2)) AS "GB"
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA;
Databases size in MBs
SELECT
TABLE_SCHEMA "DB_NAME",
SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2)) AS "MB"
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA;
Show status and open database connections¶
SHOW GLOBAL STATUS LIKE "%conn%";
SHOW GLOBAL STATUS LIKE '%onn%';
SHOW GLOBAL STATUS LIKE '%Connection_errors%';
Show performance schema information per query digest¶
SELECT
SCHEMA_NAME AS "Database",
DIGEST_TEXT AS "Query diggest",
COUNT_STAR AS "Executed times",
AVG_TIMER_WAIT AS "Executed average (picoseconds)",
ROUND((AVG_TIMER_WAIT / 1000 / 1000 / 1000 / 1000), 2) AS "Executed average (seconds)",
QUERY_SAMPLE_TEXT AS "Query sample",
QUERY_SAMPLE_SEEN AS "Query sample seen"
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 15;
Show a summary of current and recent statement events information (picoseconds)¶
SELECT * FROM performance_schema.events_statements_summary_global_by_event_name AS t
ORDER BY t.COUNT_STAR DESC;
Show tables size of current database in GBs¶
SELECT
TABLE_SCHEMA,
TABLE_NAME,
TABLE_ROWS,
ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024/ 1024), 2) TABLE_SIZE_GB
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_SIZE_GB DESC
LIMIT 300;
Summary of all statements executed on each host, along with their associated latencies¶
Other Awesome Lists¶
Contributions¶
Please check out the contribution file.
License¶
To the extent possible under law, Jose Quintana has waived all copyright and related or neighboring rights to this work.