MySQL 8.x Setup and Configuration#

MySQL 8.x is the current production series. It introduced caching_sha2_password as the default auth plugin, CTEs, window functions, and a redesigned data dictionary. Getting it installed is straightforward; getting it configured correctly for production takes more thought.

Installation#

Package Managers#

On Ubuntu/Debian, the MySQL APT repository gives you the latest 8.x:

# Add the MySQL APT repo
wget https://dev.mysql.com/get/mysql-apt-config_0.8.30-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.30-1_all.deb
sudo apt update
sudo apt install mysql-server

On RHEL/Rocky/AlmaLinux:

sudo dnf install https://dev.mysql.com/get/mysql84-community-release-el9-1.noarch.rpm
sudo dnf install mysql-community-server
sudo systemctl enable --now mysqld
# Grab the temporary root password
sudo grep 'temporary password' /var/log/mysqld.log

On macOS:

brew install mysql
brew services start mysql

Docker#

docker run -d \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=changeme \
  -e MYSQL_DATABASE=appdb \
  -e MYSQL_USER=appuser \
  -e MYSQL_PASSWORD=apppass \
  -p 3306:3306 \
  -v mysql-data:/var/lib/mysql \
  mysql:8.4

Mount a custom config file into the container:

docker run -d \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=changeme \
  -v /path/to/my.cnf:/etc/mysql/conf.d/custom.cnf \
  -v mysql-data:/var/lib/mysql \
  mysql:8.4

Helm (Kubernetes)#

The Bitnami chart is the standard choice:

helm install mysql oci://registry-1.docker.io/bitnamicharts/mysql \
  --set auth.rootPassword=changeme \
  --set auth.database=appdb \
  --set auth.username=appuser \
  --set auth.password=apppass \
  --set primary.persistence.size=20Gi

For custom my.cnf settings via Helm values:

primary:
  configuration: |-
    [mysqld]
    innodb_buffer_pool_size = 2G
    max_connections = 200

Secure Installation#

After installing from a package manager, run mysql_secure_installation:

sudo mysql_secure_installation

This walks through removing anonymous users, disabling remote root login, removing the test database, and reloading privilege tables. On fresh installs from the MySQL APT or YUM repo, the root account uses auth_socket (Debian) or a temporary password (RHEL). Check accordingly.

For Docker and Helm deployments, these steps are handled by the environment variables and entrypoint scripts, but you should still disable remote root access in production.

my.cnf Configuration#

MySQL reads configuration from several locations in order: /etc/my.cnf, /etc/mysql/my.cnf, ~/.my.cnf. On Debian-based systems, files in /etc/mysql/conf.d/ and /etc/mysql/mysql.conf.d/ are included automatically.

Here is a practical starting configuration for a server with 8 GB of RAM dedicated to MySQL:

[mysqld]
# InnoDB buffer pool: 50-70% of available RAM for a dedicated server
innodb_buffer_pool_size = 5G

# Allow the buffer pool to resize dynamically in chunks
innodb_buffer_pool_instances = 4

# Redo log size: larger values improve write throughput, slower crash recovery
# MySQL 8.0.30+ uses innodb_redo_log_capacity instead of innodb_log_file_size
innodb_redo_log_capacity = 2G

# Connections
max_connections = 200
wait_timeout = 300
interactive_timeout = 300

# Character set: always use utf8mb4 for full Unicode support
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

# Authentication plugin
# MySQL 8.0+ defaults to caching_sha2_password
# Use mysql_native_password only if legacy clients require it
# default-authentication-plugin is deprecated in 8.4; use authentication_policy
authentication_policy = caching_sha2_password,,

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

# Binary logging (required for replication and point-in-time recovery)
log_bin = mysql-bin
binlog_expire_logs_seconds = 604800
server_id = 1

# Temporary tables
tmp_table_size = 64M
max_heap_table_size = 64M

# Thread handling
thread_cache_size = 16

Key decisions in this config:

innodb_buffer_pool_size is the single most impactful setting. It caches data and indexes in memory. On a dedicated database server, set it to 50-70% of total RAM.
innodb_redo_log_capacity replaces the old innodb_log_file_size * innodb_log_files_in_group calculation. Larger redo logs buffer more writes before flushing to tablespace files.
max_connections should match your actual concurrency. Each connection consumes memory. If you set this to 1000 but your app pools at 50, the overhead is wasted.
character-set-server = utf8mb4 is non-negotiable. MySQL’s utf3 is a broken 3-byte encoding that cannot store emoji or many CJK characters. Always use utf8mb4.

Creating Users and Databases#

-- Create a database
CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- Create an application user with appropriate privileges
CREATE USER 'appuser'@'%' IDENTIFIED BY 'strong-password-here';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP
  ON appdb.* TO 'appuser'@'%';

-- Create a read-only user for reporting
CREATE USER 'reader'@'10.0.%' IDENTIFIED BY 'another-password';
GRANT SELECT ON appdb.* TO 'reader'@'10.0.%';

-- Create an admin user (not root) for DBA tasks
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'dba-password';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;

FLUSH PRIVILEGES;

The host part of the user specification matters. 'appuser'@'%' allows connections from any host. 'reader'@'10.0.%' restricts to the 10.0.x.x subnet. For security, scope users to the narrowest host pattern that works.

Verifying the Installation#

SELECT VERSION();
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'character_set_server';
SHOW STATUS LIKE 'Threads_connected';

Test connectivity from your application host:

mysql -h db-host -u appuser -p appdb -e "SELECT 1"

If you get Authentication plugin 'caching_sha2_password' cannot be loaded, your client library is too old. Either upgrade the client or create the user with mysql_native_password as a temporary workaround, then upgrade your client libraries.