MySQL standalone production configuration: InnoDB, binlogs, security and backups

#1. Base my.cnf (32 GB RAM, SSD)

[mysqld]
# basics
server_id                      = 1
bind_address                   = 0.0.0.0
datadir                        = /var/lib/mysql
character_set_server           = utf8mb4
collation_server               = utf8mb4_0900_ai_ci
default_time_zone              = '+00:00'
skip_name_resolve              = ON

# InnoDB
innodb_buffer_pool_size        = 22G      # 60-75% of RAM on a dedicated host
innodb_buffer_pool_instances   = 8
innodb_redo_log_capacity       = 8G       # 8.0.30+; replaces innodb_log_file_size
innodb_flush_method            = O_DIRECT
innodb_io_capacity             = 2000
innodb_io_capacity_max         = 4000
innodb_file_per_table          = ON

# durability (keep these at 1 for production)
innodb_flush_log_at_trx_commit = 1
sync_binlog                    = 1

# binlogs (point-in-time recovery, future replicas)
log_bin                        = /var/lib/mysql-binlog/binlog
binlog_format                  = ROW
binlog_row_image               = MINIMAL
binlog_expire_logs_seconds     = 604800   # 7 days
gtid_mode                      = ON
enforce_gtid_consistency       = ON

# connections
max_connections                = 500
thread_cache_size              = 64
wait_timeout                   = 600

# logging
slow_query_log                 = ON
long_query_time                = 0.5
log_error_verbosity            = 2

# security
require_secure_transport       = ON
local_infile                   = OFF

Note

MySQL 8.4 can set many of these automatically with innodb_dedicated_server = ON. If you use it, don't also set the buffer pool or redo log sizes by hand.

#2. Secure the installation

mysql_secure_installation

CREATE USER 'app_rw'@'10.20.1.%' IDENTIFIED BY '...' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE ON sales.* TO 'app_rw'@'10.20.1.%';

CREATE USER 'backup'@'localhost' IDENTIFIED BY '...';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT, SELECT ON *.* TO 'backup'@'localhost';

• No root from remote hosts; DBAs use named accounts.
• caching_sha2_password authentication (the default in 8.x).

#3. Backups with Percona XtraBackup

# weekly full
xtrabackup --backup --user=backup --password=... --target-dir=/backup/full-$(date +%F) --compress
# daily incremental
xtrabackup --backup --user=backup --password=... \
  --target-dir=/backup/inc-$(date +%F) --incremental-basedir=/backup/full-2026-10-18

• Copy binlogs offsite every 5–15 minutes (mysqlbinlog --read-from-remote-server --raw --stop-never) to get point-in-time recovery.
• Use MySQL Enterprise Backup if you have an Enterprise licence. mysqldump and mysqlpump only suit small databases.
• Test a restore: xtrabackup --prepare, then --copy-back, then replay binlogs to a target time.

#4. Monitoring essentials

SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;
SHOW ENGINE INNODB STATUS\G
SHOW GLOBAL STATUS LIKE 'Threads_connected';

Track buffer pool hit rate, history list length, connections, slow queries and disk use.

#5. Ready for replication later

With GTIDs and row-based binlogs already on, adding a replica or moving to InnoDB Cluster later needs no restart or changes to the binlog setup.

Warning

Setting innodb_flush_log_at_trx_commit=2 or sync_binlog=0 makes writes faster, but you can lose up to about a second of committed transactions on a crash. Don't do it on systems of record.