MySQL — Commands & Recipes¶

Connection & Basics¶

# Connect
mysql -h localhost -u root -p

# Create database
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Import
mysql -u root -p mydb < dump.sql

# Dump
mysqldump -u root -p --single-transaction mydb > backup.sql

Essential Queries¶

-- Database sizes
SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables GROUP BY table_schema ORDER BY 2 DESC;

-- Active connections
SHOW PROCESSLIST;

-- Kill long query
KILL <process_id>;

-- InnoDB status
SHOW ENGINE INNODB STATUS\G

-- Index usage
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

Performance Tuning¶

# my.cnf essentials
[mysqld]
innodb_buffer_pool_size = 4G        # 70-80% of RAM
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1  # ACID compliance
innodb_flush_method = O_DIRECT
max_connections = 200

Group Replication Setup¶

-- On each node
SET SQL_LOG_BIN=0;
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
SET SQL_LOG_BIN=1;

CHANGE REPLICATION SOURCE TO SOURCE_USER='repl', SOURCE_PASSWORD='password'
  FOR CHANNEL 'group_replication_recovery';

-- Bootstrap first node
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

-- Join other nodes
START GROUP_REPLICATION;

Sources¶

MySQL Reference