Some MySQL Server Sample Configs

Take these with a grain of salt. Just more data for those of you looking for some real world examples.

These are /etc/my.cnf files from a typical highly available Master-Slave pair.

First the master:

# Master server. Slave is slave01.example.com.
[mysqld]
datadir=/data/app/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
key_buffer_size = 512M
log-bin=/data1/app/mysql/mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-format=MIXED
expire_logs_days = 2
max_binlog_size = 100M
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=2G
innodb_lock_wait_timeout=120
max_connections=300

[mysqld_safe]
log-error=/data1/logs/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Note that some of these settings will vary with the requirements of your environment. In particular the innodb_log_file_size and innodb_buffer_pool_size, as well as max_connections. Also, putting log files (including the bin files used in replication) on a separate volume from the data can help performance in some cases.

And here’s the slave:

# Slave server. Master is master01.example.com.
[mysqld]
datadir=/data/app/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
key_buffer_size = 512M
server-id=2
relay-log-index=/data1/app/mysql/slave-relay-bin.index
relay-log=/data1/app/mysql/slave-relay-bin
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=2G
innodb_lock_wait_timeout=120
max_connections=300
read_only=true

[mysqld_safe]
log-error=/data1/logs/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Marking a slave as read_only in the config file makes sense, as does providing information about its master in the comments.

Further Reading:

The 5 minute DBA: Default my.cnf File
WordPress Optimization Guide

This entry was posted in Database, System Administration on by .

About phil

My name is Phil Lembo. In my day job I’m an enterprise IT architect for a leading distribution and services company. The rest of my time I try to maintain a semi-normal family life in the suburbs of Raleigh, NC. E-mail me at philipATlembobrothersDOTcom. The opinions expressed here are entirely my own and not those of my employers, past, present or future (except where I quote others, who will need to accept responsibility for their own rants).