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`@`%`;

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;

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'root2'@'%';

ALTER USER 'username'@'localhost' IDENTIFIED BY 'my_new_password';

SELECT code, COUNT(code) duplicates FROM client GROUP BY code HAVING duplicates > 1;

SELECT * 
FROM users
WHERE
  `registered` >= CONCAT(SUBDATE(CURDATE(), 1), ' 00:00:00') AND 
  `registered` < CONCAT(CURDATE(), ' 00:00:00')

-- -----------------------------------------------------
-- `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;

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;

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 GLOBAL STATUS LIKE "%conn%";
SHOW GLOBAL STATUS LIKE '%onn%';
SHOW GLOBAL STATUS LIKE '%Connection_errors%';

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;

SELECT * FROM performance_schema.events_statements_summary_global_by_event_name AS t
ORDER BY t.COUNT_STAR DESC;

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;

SELECT * FROM sys.host_summary_by_statement_type;