Server Configuration

Complete guide to MySQL server configuration, variables, and system settings including timezone management.

Server Configuration

MySQL server configuration is crucial for optimal performance, security, and functionality. This guide covers all aspects of server configuration including the critical timezone management that every SQL architect must understand.

Configuration Files

Main Configuration File Locations

Linux/Unix Systems

# Common locations (checked in order)
/etc/mysql/my.cnf
/etc/my.cnf
~/.my.cnf
/usr/etc/my.cnf

# Debian/Ubuntu specific
/etc/mysql/mysql.conf.d/mysqld.cnf

# Check which files are being used
mysqld --help --verbose | grep -A 1 'Default options'

Windows Systems

# Installation directory
C:\Program Files\MySQL\MySQL Server 8.0\my.ini

# ProgramData directory
C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Configuration File Structure

[mysql]
# MySQL client settings
default-character-set = utf8mb4

[mysqld]
# MySQL server settings
bind-address = 127.0.0.1
port = 3306
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock

[mysqldump]
# mysqldump utility settings
quick
quote-names
max_allowed_packet = 16M

Time Zone Management

Understanding MySQL Time Zones

MySQL handles time zones at multiple levels:

  1. System Time Zone: Operating system time zone
  2. Server Time Zone: MySQL server default time zone
  3. Session Time Zone: Connection-specific time zone

Viewing Current Time Zone Settings

-- Show all time zone settings
SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone;

-- Show current time in different formats
SELECT NOW(), UTC_TIMESTAMP(), UNIX_TIMESTAMP();

-- Show time zone offset
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP()) as timezone_offset;

Setting Time Zones

Global Time Zone Setting

-- Set global time zone to UTC
SET GLOBAL time_zone = 'UTC';

-- Set global time zone to specific offset
SET GLOBAL time_zone = '+05:30';  -- India Standard Time
SET GLOBAL time_zone = '-05:00';  -- Eastern Standard Time
SET GLOBAL time_zone = '+00:00';  -- UTC

-- Set global time zone to named timezone
SET GLOBAL time_zone = 'Asia/Kolkata';
SET GLOBAL time_zone = 'America/New_York';
SET GLOBAL time_zone = 'Europe/London';

Session Time Zone Setting

-- Set session time zone
SET time_zone = 'UTC';
SET time_zone = '+05:30';
SET time_zone = 'Asia/Tokyo';

-- Use system time zone
SET time_zone = SYSTEM;

Configuration File Time Zone

[mysqld]
# Set default time zone in configuration file
default-time-zone = '+00:00'
# OR
default-time-zone = 'UTC'

Loading Time Zone Tables

# Load time zone information (run as root)
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

# For specific time zone
mysql_tzinfo_to_sql /usr/share/zoneinfo/Asia/Kolkata | mysql -u root -p mysql

# Load from specific directory
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Time Zone Queries and Operations

-- Show available time zones
SELECT * FROM mysql.time_zone_name LIMIT 20;

-- Convert between time zones
SELECT 
    NOW() as local_time,
    CONVERT_TZ(NOW(), @@session.time_zone, 'UTC') as utc_time,
    CONVERT_TZ(NOW(), @@session.time_zone, '+05:30') as ist_time;

-- Time zone conversion examples
SELECT 
    '2024-01-15 10:30:00' as original,
    CONVERT_TZ('2024-01-15 10:30:00', 'UTC', 'Asia/Kolkata') as ist_time,
    CONVERT_TZ('2024-01-15 10:30:00', 'UTC', 'America/New_York') as est_time;

-- Show time zone transition information
SELECT * FROM mysql.time_zone_transition 
WHERE time_zone_id = (SELECT time_zone_id FROM mysql.time_zone_name WHERE name = 'Asia/Kolkata');

Server Variables

Viewing Server Variables

-- Show all variables
SHOW VARIABLES;

-- Show specific variable
SHOW VARIABLES LIKE 'max_connections';

-- Show variables with pattern
SHOW VARIABLES LIKE '%timeout%';
SHOW VARIABLES LIKE '%cache%';
SHOW VARIABLES LIKE '%buffer%';

-- Using SELECT statement
SELECT @@global.max_connections;
SELECT @@session.autocommit;

Setting Server Variables

Session Variables

-- Set session variables (affect current connection only)
SET SESSION autocommit = 0;
SET @@session.sql_mode = 'STRICT_TRANS_TABLES';
SET SESSION query_cache_type = OFF;

-- Common session settings
SET SESSION sql_safe_updates = 1;
SET SESSION foreign_key_checks = 0;
SET SESSION unique_checks = 0;

Global Variables

-- Set global variables (affect new connections)
SET GLOBAL max_connections = 200;
SET @@global.query_cache_size = 134217728;
SET GLOBAL slow_query_log = ON;

-- Performance related settings
SET GLOBAL innodb_buffer_pool_size = 1073741824;  -- 1GB
SET GLOBAL key_buffer_size = 268435456;  -- 256MB
SET GLOBAL table_open_cache = 2000;

Critical Server Variables

Connection Settings

-- Connection management
SHOW VARIABLES LIKE 'max_connections';          -- Default: 151
SHOW VARIABLES LIKE 'max_user_connections';     -- Default: 0 (unlimited)
SHOW VARIABLES LIKE 'wait_timeout';             -- Default: 28800 (8 hours)
SHOW VARIABLES LIKE 'interactive_timeout';      -- Default: 28800 (8 hours)
SHOW VARIABLES LIKE 'connect_timeout';          -- Default: 10 seconds

-- Set connection timeouts
SET GLOBAL wait_timeout = 600;          -- 10 minutes
SET GLOBAL interactive_timeout = 600;   -- 10 minutes
SET GLOBAL max_connections = 300;

Memory Settings

-- Buffer sizes
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  -- Most important for InnoDB
SHOW VARIABLES LIKE 'key_buffer_size';          -- For MyISAM tables
SHOW VARIABLES LIKE 'sort_buffer_size';         -- Per-connection sort buffer
SHOW VARIABLES LIKE 'read_buffer_size';         -- Sequential scan buffer
SHOW VARIABLES LIKE 'join_buffer_size';         -- Join operation buffer

-- Set memory configurations
SET GLOBAL innodb_buffer_pool_size = 2147483648;  -- 2GB
SET GLOBAL key_buffer_size = 134217728;           -- 128MB

Security Settings

-- Security related variables
SHOW VARIABLES LIKE 'local_infile';           -- File loading capability
SHOW VARIABLES LIKE 'secure_file_priv';       -- File operation directory
SHOW VARIABLES LIKE 'sql_mode';               -- SQL mode restrictions

-- Set security variables
SET GLOBAL local_infile = OFF;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO';

Performance Configuration

InnoDB Configuration

-- InnoDB specific settings
SHOW VARIABLES LIKE 'innodb%';

-- Critical InnoDB variables
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';      -- 70-80% of available RAM
SHOW VARIABLES LIKE 'innodb_log_file_size';         -- Log file size
SHOW VARIABLES LIKE 'innodb_log_buffer_size';       -- Log buffer size
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; -- Durability vs performance

-- Configure InnoDB for performance
SET GLOBAL innodb_buffer_pool_size = 4294967296;    -- 4GB
SET GLOBAL innodb_log_buffer_size = 16777216;       -- 16MB
SET GLOBAL innodb_flush_log_at_trx_commit = 2;      -- Better performance, less durability

Query Cache Configuration (MySQL 5.7 and earlier)

-- Query cache settings (deprecated in MySQL 8.0)
SHOW VARIABLES LIKE 'query_cache%';

-- Configure query cache
SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 134217728;  -- 128MB
SET GLOBAL query_cache_limit = 2097152;   -- 2MB max query size

Temporary Table Configuration

-- Temporary table settings
SHOW VARIABLES LIKE '%tmp%';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'tmp_table_size';

-- Configure temporary tables
SET GLOBAL tmp_table_size = 134217728;        -- 128MB
SET GLOBAL max_heap_table_size = 134217728;   -- 128MB

Logging Configuration

Error Log

-- Error log settings
SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'log_warnings';

-- Enable error logging
SET GLOBAL log_error = '/var/log/mysql/error.log';

General Query Log

-- General log settings
SHOW VARIABLES LIKE 'general_log%';

-- Enable general query log
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- Disable general log (for performance)
SET GLOBAL general_log = OFF;

Slow Query Log

-- Slow query log settings
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- Enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2.0;  -- Log queries taking > 2 seconds
SET GLOBAL log_queries_not_using_indexes = ON;

Binary Log

-- Binary log settings
SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'binlog%';
SHOW VARIABLES LIKE 'sync_binlog';

-- Configure binary logging
SET GLOBAL sync_binlog = 1;              -- Sync to disk after each transaction
SET GLOBAL binlog_cache_size = 32768;    -- Binary log cache size
SET GLOBAL max_binlog_size = 1073741824; -- 1GB max size per file

Character Set and Collation

Viewing Character Sets

-- Show available character sets
SHOW CHARACTER SET;

-- Show current character set settings
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- Show database character sets
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA;

Setting Character Sets

-- Set character set variables
SET GLOBAL character_set_server = 'utf8mb4';
SET GLOBAL collation_server = 'utf8mb4_unicode_ci';

-- Session character set
SET SESSION character_set_client = 'utf8mb4';
SET SESSION character_set_connection = 'utf8mb4';
SET SESSION character_set_results = 'utf8mb4';

Configuration File Character Set

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

Network Configuration

Network Settings

-- Network related variables
SHOW VARIABLES LIKE 'bind_address';
SHOW VARIABLES LIKE 'port';
SHOW VARIABLES LIKE 'max_allowed_packet';
SHOW VARIABLES LIKE 'net_buffer_length';

-- Configure network settings
SET GLOBAL max_allowed_packet = 67108864;  -- 64MB
SET GLOBAL net_buffer_length = 16384;      -- 16KB

SSL Configuration

-- SSL settings
SHOW VARIABLES LIKE '%ssl%';

-- Check SSL status
SHOW STATUS LIKE 'Ssl_cipher';
SELECT * FROM performance_schema.session_status WHERE VARIABLE_NAME LIKE 'Ssl%';

Advanced Configuration

Performance Schema

-- Performance schema settings
SHOW VARIABLES LIKE 'performance_schema%';

-- Enable/disable performance schema
SET GLOBAL performance_schema = ON;  -- Requires restart

-- Configure performance schema memory
SHOW VARIABLES LIKE 'performance_schema_max%';

Event Scheduler

-- Event scheduler settings
SHOW VARIABLES LIKE 'event_scheduler';

-- Enable event scheduler
SET GLOBAL event_scheduler = ON;

-- Check running events
SHOW EVENTS;
SHOW PROCESSLIST;

Plugin Configuration

-- Show loaded plugins
SHOW PLUGINS;

-- Install plugins
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Plugin variables
SHOW VARIABLES LIKE 'validate_password%';

Configuration Best Practices

Production Configuration Template

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

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Time zone
default-time-zone = '+00:00'

# Connection settings
max_connections = 200
max_user_connections = 50
wait_timeout = 600
interactive_timeout = 600

# Memory settings (adjust based on available RAM)
innodb_buffer_pool_size = 2G
key_buffer_size = 256M
sort_buffer_size = 2M
read_buffer_size = 128K
join_buffer_size = 256K

# InnoDB settings
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1

# Query cache (MySQL 5.7 and earlier)
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

# Logging
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

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

# Security
local_infile = 0

Performance Tuning Variables

-- High-performance configuration
SET GLOBAL innodb_buffer_pool_size = 8589934592;     -- 8GB
SET GLOBAL innodb_log_file_size = 536870912;         -- 512MB
SET GLOBAL innodb_flush_log_at_trx_commit = 2;       -- Better performance
SET GLOBAL sync_binlog = 0;                          -- Better performance, less durability
SET GLOBAL table_open_cache = 4000;
SET GLOBAL thread_cache_size = 50;
SET GLOBAL query_cache_size = 268435456;             -- 256MB

Monitoring Configuration

Status Variables

-- Monitor server status
SHOW STATUS;
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Threads_%';
SHOW STATUS LIKE 'Uptime%';

-- Connection statistics
SHOW STATUS LIKE 'Aborted_%';
SHOW STATUS LIKE 'Max_used_connections';

-- InnoDB status
SHOW STATUS LIKE 'Innodb_%';

Process Monitoring

-- Show running processes
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- Process information
SELECT * FROM information_schema.processlist;

-- Long-running queries
SELECT * FROM information_schema.processlist 
WHERE command != 'Sleep' AND time > 300;

Troubleshooting Configuration

Common Configuration Issues

Memory Problems

-- Check memory usage
SHOW VARIABLES LIKE '%buffer%';
SHOW VARIABLES LIKE '%cache%';
SHOW STATUS LIKE 'Created_tmp_disk_tables';
SHOW STATUS LIKE 'Created_tmp_tables';

-- If too many temp tables on disk
SET GLOBAL tmp_table_size = 268435456;  -- 256MB
SET GLOBAL max_heap_table_size = 268435456;

Connection Issues

-- Check connection limits
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Threads_connected';

-- Increase connections if needed
SET GLOBAL max_connections = 300;

Time Zone Issues

-- Check time zone consistency
SELECT @@global.time_zone, @@session.time_zone, NOW(), UTC_TIMESTAMP();

-- Fix time zone problems
SET GLOBAL time_zone = 'UTC';
SET SESSION time_zone = 'UTC';

Configuration Validation

Verify Configuration Changes

-- Check if variable was set
SHOW VARIABLES LIKE 'variable_name';

-- Verify the change took effect
SELECT @@global.variable_name;

-- Check if restart is required
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  -- Requires restart

Configuration File Syntax Check

# Test configuration file syntax
mysqld --defaults-file=/etc/mysql/my.cnf --help --verbose > /dev/null

# Check configuration file loading
mysqld --print-defaults

Next Steps

After mastering server configuration:

  1. Performance Monitoring: Monitor your configured server
  2. Backup & Recovery: Configure backup systems
  3. Security: Implement security configurations
  4. Database Operations: Apply configurations to database management

Quick Reference

Essential Configuration Commands

-- Show current settings
SHOW VARIABLES LIKE 'setting_name';

-- Set global variable
SET GLOBAL variable_name = value;

-- Set session variable
SET SESSION variable_name = value;

-- Time zone management
SET GLOBAL time_zone = 'UTC';
SELECT @@global.time_zone, @@session.time_zone;

-- Reload configuration
FLUSH TABLES;
FLUSH PRIVILEGES;