MySQL Configuration Guide

Introduction

This guide covers essential MySQL configuration for both WAMP and XAMPP environments, including security settings, performance optimization, and common customizations.

Initial Security Setup

Setting Root Password

Using phpMyAdmin

  1. Open phpMyAdmin (http://localhost/phpmyadmin)
  2. Click on “User accounts” tab
  3. Click “Edit privileges” for root user
  4. Click “Change password”
  5. Enter and confirm new password
  6. Click “Go”

Using MySQL Command Line

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';
FLUSH PRIVILEGES;

Creating New Database User

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Configuration File Location

WAMP

C:\wamp64\bin\mysql\mysql[version]\my.ini

XAMPP Windows

C:\xampp\mysql\bin\my.ini

XAMPP macOS

/Applications/XAMPP/xamppfiles/etc/my.cnf

XAMPP Linux

/opt/lampp/etc/my.cnf

Common Configuration Settings

Basic Settings

[mysqld]
# Port configuration
port = 3306

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

# Maximum connections
max_connections = 100

# Query cache (MySQL 5.7 and earlier)
query_cache_type = 1
query_cache_size = 16M

Performance Tuning

# Buffer pool size (set to 70-80% of RAM for dedicated servers)
innodb_buffer_pool_size = 256M

# Log file size
innodb_log_file_size = 64M

# Thread cache
thread_cache_size = 8

# Table cache
table_open_cache = 2000

Memory Settings

# Key buffer size (for MyISAM tables)
key_buffer_size = 16M

# Sort buffer
sort_buffer_size = 2M

# Read buffer
read_buffer_size = 1M

Changing Default Port

If port 3306 is already in use:

  1. Open MySQL configuration file
  2. Find the line port = 3306
  3. Change to available port (e.g., port = 3307)
  4. Restart MySQL service
  5. Update connection strings in your applications

Enabling Remote Connections

Edit Configuration File

[mysqld]
bind-address = 0.0.0.0

Grant Remote Access

CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';
FLUSH PRIVILEGES;

Warning: Only enable remote access on secure networks.

Logging Configuration

Error Log

log_error = /path/to/mysql-error.log

General Query Log (for debugging)

general_log = 1
general_log_file = /path/to/mysql-general.log

Slow Query Log

slow_query_log = 1
slow_query_log_file = /path/to/mysql-slow.log
long_query_time = 2

Storage Engine Configuration

default-storage-engine = InnoDB
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2

Character Set Configuration

UTF-8 (utf8mb4) Setup

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

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

Applying Configuration Changes

  1. Save the configuration file
  2. Restart MySQL service:

WAMP

XAMPP

Linux

sudo /opt/lampp/lampp stopmysql
sudo /opt/lampp/lampp startmysql

Verifying Configuration

SHOW VARIABLES LIKE 'variable_name';

Example:

SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'character_set%';

Best Practices

  1. Regular Backups: Configure automatic backups
  2. Security: Never use empty passwords in production
  3. Monitoring: Enable slow query log to identify performance issues
  4. Updates: Keep MySQL updated to the latest stable version
  5. Documentation: Document all configuration changes

Troubleshooting

MySQL Won’t Start After Configuration Change

  1. Check the error log
  2. Verify syntax in configuration file
  3. Restore previous working configuration
  4. Restart the service

Configuration Changes Not Applied

  1. Ensure you edited the correct configuration file
  2. Verify you restarted MySQL service
  3. Check for syntax errors

Next Steps

Additional Resources