# Find which config files MySQL is actually reading on your system
mysql --help | grep -A1 'Default options'
# Output: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

# Show which option files were used for a running instance
SELECT @@global.datadir;    -- data directory also shows install path

# /etc/mysql/my.cnf — annotated production example

[client]
port     = 3306
socket   = /var/run/mysqld/mysqld.sock

[mysqld]
# --- Basic ---
user             = mysql
port             = 3306
datadir          = /var/lib/mysql
socket           = /var/run/mysqld/mysqld.sock
pid-file         = /var/run/mysqld/mysqld.pid

# --- Character Set (always set explicitly) ---
character_set_server = utf8mb4
collation_server     = utf8mb4_unicode_ci

# --- InnoDB (most important section) ---
innodb_buffer_pool_size        = 12G   # 75% of 16 GB server
innodb_buffer_pool_instances   = 8
innodb_log_file_size           = 512M
innodb_flush_log_at_trx_commit = 1     # ACID safe
innodb_io_capacity             = 2000  # for SSD

# --- Connections ---
max_connections      = 200
wait_timeout         = 28800
interactive_timeout  = 28800
thread_cache_size    = 16

# --- Binary Log ---
log_bin              = /var/log/mysql/mysql-bin.log
binlog_format        = ROW
expire_logs_days     = 7
sync_binlog          = 1

# --- Slow Query Log ---
slow_query_log       = ON
slow_query_log_file  = /var/log/mysql/mysql-slow.log
long_query_time      = 1

[mysqld]
# InnoDB tuning for a 32 GB dedicated database server with SSD
innodb_buffer_pool_size        = 24G   # 75% of 32 GB
innodb_buffer_pool_instances   = 8     # one per ~3 GB
innodb_log_file_size           = 1G
innodb_flush_log_at_trx_commit = 1     # ACID compliant
innodb_io_capacity             = 4000  # NVMe SSD
innodb_io_capacity_max         = 8000

[mysqld]
# Maximum simultaneous connections (each uses ~1 MB RAM)
max_connections       = 200

# Seconds an idle non-interactive connection waits before MySQL closes it
wait_timeout          = 28800   # 8 hours (reduce to 300 in production with pooling)

# Seconds for interactive sessions (mysql CLI)
interactive_timeout   = 28800

# Connection backlog queue
back_log              = 150

# Thread cache — reuse threads instead of creating/destroying per connection
thread_cache_size     = 32

[mysqld]
# Enable binary logging (required for replication and point-in-time recovery)
log_bin                    = /var/log/mysql/mysql-bin.log
binlog_format              = ROW              # safest for replication
binlog_row_image           = MINIMAL          # log only changed columns
sync_binlog                = 1                # flush to disk on each commit

# Retention (MySQL 5.7 uses expire_logs_days; 8.0 uses binlog_expire_logs_seconds)
expire_logs_days           = 7                # MySQL 5.7
# binlog_expire_logs_seconds = 604800         # MySQL 8.0 (7 days)

max_binlog_size            = 100M             # rotate file when it reaches this size

-- Show all variables (very long list)
SHOW VARIABLES;

-- Filter by pattern
SHOW VARIABLES LIKE 'innodb_buffer%';
SHOW VARIABLES LIKE 'max_connections';

-- From performance_schema (more detail, filterable)
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME LIKE 'innodb%'
ORDER BY VARIABLE_NAME;

-- Check current session variables
SHOW SESSION VARIABLES LIKE 'long_query_time';

-- Change globally (applies to all new connections immediately)
SET GLOBAL max_connections = 300;
SET GLOBAL slow_query_log  = ON;

-- Change for current session only (does not affect other connections)
SET SESSION long_query_time = 0.5;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';

-- MySQL 8.0+: persist to mysqld-auto.cnf (survives restart, no file editing needed)
SET PERSIST max_connections = 300;

-- MySQL 8.0+: persist only — does NOT apply to the running instance
-- (use for variables that require restart, like innodb_log_file_size)
SET PERSIST_ONLY innodb_log_file_size = 1073741824;  -- 1G, takes effect after restart

-- View what has been persisted via SET PERSIST or SET PERSIST_ONLY
SELECT * FROM performance_schema.persisted_variables;

-- Remove a persisted variable (reverts to my.cnf value on next restart)
RESET PERSIST max_connections;

-- Reset ALL persisted variables
RESET PERSIST;

# systemd (modern Linux — preferred)
systemctl start mysql
systemctl stop  mysql
systemctl restart mysql
systemctl status mysql
systemctl enable mysql    # start on boot

# mysqld_safe (legacy wrapper script — still used on some systems)
mysqld_safe --user=mysql &
mysqladmin -u root -p shutdown

[mysqld]
# Disable Performance Schema entirely (not recommended in production)
performance_schema = OFF

# Or reduce P_S overhead by disabling specific expensive consumers:
# Disable in mysql client at runtime:
# UPDATE performance_schema.setup_consumers
# SET ENABLED = 'NO' WHERE NAME = 'events_statements_history_long';

-- Enable general log at runtime (do NOT leave this on in production)
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/var/log/mysql/mysql-general.log';

-- Watch what your application sends to MySQL in real time
SHOW VARIABLES LIKE 'general_log%';

-- Disable after debugging
SET GLOBAL general_log = OFF;

[mysqld]
# Error log — always enable in production
log_error          = /var/log/mysql/mysql-error.log
log_error_verbosity = 2   # 1=errors only, 2=errors+warnings, 3=notes

# On systemd systems, log to journald instead
# log_error = stderr

[mysqld]
# Internal temporary table size limits
tmp_table_size        = 64M    # max size of an in-memory temp table
max_heap_table_size   = 64M    # max size of user-created MEMORY tables
# If a temp table exceeds tmp_table_size, it spills to disk (much slower)
# Monitor with: SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

-- Verify a variable is set to what you expect
SELECT @@global.innodb_buffer_pool_size / 1024 / 1024 / 1024 AS buffer_pool_gb;
SELECT @@global.max_connections;
SELECT @@global.slow_query_log;

-- Check if SET PERSIST has been applied
SELECT * FROM performance_schema.persisted_variables
ORDER BY VARIABLE_NAME;

-- Check running instance vs persisted values
SELECT g.VARIABLE_NAME,
       g.VARIABLE_VALUE  AS runtime_value,
       p.VARIABLE_VALUE  AS persisted_value
FROM performance_schema.global_variables g
LEFT JOIN performance_schema.persisted_variables p USING (VARIABLE_NAME)
WHERE p.VARIABLE_VALUE IS NOT NULL
ORDER BY g.VARIABLE_NAME;

-- Check current SQL mode
SELECT @@sql_mode;

-- MySQL 8.0 default includes STRICT_TRANS_TABLES, which rejects
-- invalid data instead of silently truncating or substituting defaults

-- Common modes and what they control:
-- STRICT_TRANS_TABLES: reject invalid inserts (wrong type, too long, out of range)
-- NO_ZERO_DATE: reject '0000-00-00' as a valid date
-- NO_ZERO_IN_DATE: reject dates like '2024-00-15'
-- ONLY_FULL_GROUP_BY: require GROUP BY to include all non-aggregate SELECT columns
-- ERROR_FOR_DIVISION_BY_ZERO: return error instead of NULL for divide by zero

-- Set a custom SQL mode (production recommendation)
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY';

-- In my.cnf:
-- [mysqld]
-- sql_mode = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY

[mysqld]
# Character set
character_set_server = utf8mb4
collation_server     = utf8mb4_unicode_ci

# InnoDB — set to 50-60% of RAM on a shared VPS (OS needs memory too)
innodb_buffer_pool_size        = 2G
innodb_log_file_size           = 256M
innodb_flush_log_at_trx_commit = 1

# Connections
max_connections     = 100
wait_timeout        = 300        # 5 minutes idle timeout
thread_cache_size   = 8

# Logging
slow_query_log      = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time     = 1
log_error           = /var/log/mysql/mysql-error.log