title: Some MySQL Server Sample Configs link: https://onemoretech.wordpress.com/2013/08/28/some-mysql-server-sample-configs/ author: phil2nc description: post_id: 6292 created: 2013/08/28 10:49:13 created_gmt: 2013/08/28 14:49:13 comment_status: closed post_name: some-mysql-server-sample-configs status: publish post_type: post

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: [code language="bash" gutter="false"] # 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 [/code] 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: [code language="bash" gutter="false"] # 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 [/code] 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

Copyright 2004-2019 Phil Lembo