Tuesday, September 15, 2015

Standardized MySQL Configuration File For Standalone, Replication, & Clustering [feedly]

Standardized MySQL Configuration File For Standalone, Replication, & Clustering
http://www.itchyninja.com/standardized-mysql-configuration-file-for-standalone-replication-clustering/

-- via my feedly.com reader

In order to simplify the configuration of MySQL for standalone nodes, clusters, and replication configurations, we decided it would be easiest to maintain a common my.cnf file.  We have to admit, the idea wasn't ours; we picked the idea up from www.fromdual.com and thought it was such a great idea, we decided to implement it as well.

Below is our version of a standardized my.cnf implementing several of our best practices.  We hope it will be of benefit to you.

########################################################################################################  # my.cnf (Configuration file for MySQL)  #  # Provided by Itchy Ninja Software to implement general best practices for MySQL regardless of server   # type. We chose a single file instead of maintaining multiple versions of the configuration file.  # Based upon http://www.fromdual.com/mysql-configuration-file-sample   #  # Sections are included for Percona XTRADB Cluster and replication (Master/Master and Master/Slave).  # You can leave commented out any sections and variables which do not apply to your setup.  ########################################################################################################    [client]  port = 3306  socket = /mysql/tmp/mysql.sock  # ssl-cert = /etc/mysql-ssl/client-cert.pem  # ssl-key = /etc/mysql-ssl/client-key.pem    [mysql]  no_auto_rehash  max_allowed_packet = 1G  # prompt = '\u@\h [\d]> '  default_character_set = utf8  # safe-updates    [myisamchk]  key_buffer_size = 256M  sort_buffer_size = 256M  read_buffer = 2M  write_buffer = 2M    [mysqldump]  quick  triggers  events  routines  single-transaction  master-data = 2 # Store master binary log filename and position in dumpfile but comment out  max_allowed_packet = 1G    [mysqlhotcopy]  interactive-timeout    [mysqld_safe]  open_files_limit = 8192 # Depends on your OS setting  user = mysql  log-error = /mysql/logs/mysqld.error.log    [mysqld]  ########################################################################################################  # General MySQL settings for all server types (stand alone, PXC, replication, etc.)  ########################################################################################################    ;-------------------------------------------------------------------------------------------------------  ; Buffers and caches  ;-------------------------------------------------------------------------------------------------------  read_buffer_size = 128K # Do not change unless necessary  read_rnd_buffer_size = 256K # Do not change unless necessary  join_buffer_size = 128K # Do not change unless necessary  table_definition_cache = 400 # Set to as many tables as you have  table_open_cache = 512 # connections x tables/connection (~2)    ;-------------------------------------------------------------------------------------------------------  ; Character Sets  ;-------------------------------------------------------------------------------------------------------  character_set_server = utf8 # For UTF8  collation_server = utf8_general_ci # For UTF8     ;-------------------------------------------------------------------------------------------------------  ; Connections  ;-------------------------------------------------------------------------------------------------------  port = 3306  socket = /mysql/tmp/mysql.sock  max_connections = 505 # Keep this as low as possible  max_user_connections = 500 # Limit one specific user/application  max_connect_errors = 1000 # This is likely too high  thread_cache_size = 505 # Up to max_connections  # thread_concurrency = 4 # Set to # of CPUs * 2 - Will be removed in future  # skip-networking    ;-------------------------------------------------------------------------------------------------------  ; Error log  ;-------------------------------------------------------------------------------------------------------  log_error = /mysql/logs/error.log  log_warnings = 2  innodb_print_all_deadlocks = 1    ;-------------------------------------------------------------------------------------------------------  ; Event scheduler (should be off for Galera)  ;-------------------------------------------------------------------------------------------------------  event_scheduler = ON    ;-------------------------------------------------------------------------------------------------------  ; File locations  ;-------------------------------------------------------------------------------------------------------  datadir = /mysql/data   tmpdir = /mysql/tmp   pid-file = /mysql/tmp/mysql.pid    ;-------------------------------------------------------------------------------------------------------  ; General query log  ;-------------------------------------------------------------------------------------------------------  # general_log_file = /mysql/logs/general.log  # general_log = 0    ;-------------------------------------------------------------------------------------------------------  ; Networking  ;-------------------------------------------------------------------------------------------------------  max_allowed_packet = 1G # Some servers have issues with lower setting  net_read_timeout = 300  net_write_timeout = 300  wait_timeout = 43200    ;-------------------------------------------------------------------------------------------------------  ; Performance Schema (Best disabled unless needed to improve performance)  ;-------------------------------------------------------------------------------------------------------  performance_schema = OFF     ;-------------------------------------------------------------------------------------------------------  ; Query Cache  ;-------------------------------------------------------------------------------------------------------  query_cache_type = 0 # Set to 0 to disable query cache  query_cache_size = 64M # Avoid too big (> 128M) QC because of QC clean-up lock!    ;-------------------------------------------------------------------------------------------------------  ; Security  ;-------------------------------------------------------------------------------------------------------  local_infile = 0 # If you are security aware  secure_auth = 1 # If you are security aware  sql_mode = TRADITIONAL,ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  skip_name_resolve = 1 # Set to 1 to prevent DNS host lookups  ssl-ca = /etc/mysql-ssl/ca.pem  ssl-cert = /etc/mysql-ssl/server-cert.pem  ssl-key = /etc/mysql-ssl/server-key.pem    ;-------------------------------------------------------------------------------------------------------  ; Session variables  ;-------------------------------------------------------------------------------------------------------  sort_buffer_size = 2M # Could be too big for many small sorts    ;-------------------------------------------------------------------------------------------------------  ; Slow query log  ;-------------------------------------------------------------------------------------------------------  slow_query_log = ON  slow_query_log_file = /mysql/logs/slow.log  log_queries_not_using_indexes = 1  long_query_time = 2  min_examined_row_limit = 100    ;-------------------------------------------------------------------------------------------------------  ; Storage Engines  ;-------------------------------------------------------------------------------------------------------  default_storage_engine = InnoDB   key_buffer_size = 8M # Set to 25 - 33 % of RAM if you use MyISAM  max_heap_table_size = 64M # MEMORY table: should be greater or equal to tmp_table_size  myisam_recover_options = 'BACKUP,FORCE' # Since 5.5  # myisam_sort_buffer_size = 64M  innodb_buffer_pool_dump_at_shutdown = ON # Dump buffer pool at shutdown to a flat file  innodb_buffer_pool_load_at_startup = ON # Load buffer pool at startup to warm up the buffer pool  innodb_buffer_pool_instances = 2 # Bigger if huge InnoDB Buffer Pool or high concurrency  # innodb_buffer_pool_size = 128M # For MyISAM  # innodb_buffer_pool_size = 512M # For InnoDB with 1G of RAM  # innodb_buffer_pool_size = 1536M # For InnoDB with 2G of RAM  innodb_buffer_pool_size = 3072M # For InnoDB with 4G of RAM  # innodb_buffer_pool_size = 6G # For InnoDB with 8G of RAM  # innodb_buffer_pool_size = 12G # For InnoDB with 16G of RAM  # innodb_buffer_pool_size = 24G # For InnoDB with 32G of RAM  # innodb_buffer_pool_size = 48G # For InnoDB with 64G of RAM  innodb_data_home_dir = /mysql/data  innodb_data_file_path = ibdata1:10M:autoextend  innodb_file_format = Barracuda # For dynamic and compressed InnoDB tables  innodb_file_format_check = 1  innodb_file_per_table = 1   innodb_flush_log_at_trx_commit = 1 # 1 for durability, 0 or 2 for performance  # innodb_flush_method = O_DIRECT # O_DIRECT is sometimes better for direct attached storage  # innodb_io_capacity = 1000 # If you have a strong I/O system or SSD  innodb_lock_wait_timeout = 50  innodb_log_buffer_size = 8M # Bigger if innodb_flush_log_at_trx_commit = 0  innodb_log_file_size = 512M # Bigger means more write throughput but longer recovery time  innodb_log_files_in_group = 4  innodb_log_group_home_dir = /mysql/data  innodb_strict_mode = ON  # innodb_read_io_threads = 8 # If you have a strong I/O system or SSD  # innodb_write_io_threads = 8 # If you have a strong I/O system or SSD  skip-external-locking  # transaction_isolation = REPEATABLE-READ     ;-------------------------------------------------------------------------------------------------------  ; Temporary Tables  ;-------------------------------------------------------------------------------------------------------  tmp_table_size = 156M  max_heap_table_size = 160M    ########################################################################################################  # Replication & Point-In-Time Recovery via Binary Logging  ########################################################################################################    ;-------------------------------------------------------------------------------------------------------  ; Binary logging (Required for both replication and point-in-time recovery)  ;-------------------------------------------------------------------------------------------------------  log_bin = /mysql/data/mysql-bin  log_bin-index = /mysql/data/mysql-bin.index  binlog_cache_size = 1M  # binlog_stmt_cache_size = 1M # Since 5.5  max_binlog_size = 128M # Make bigger for high traffic to reduce number of files  sync_binlog = 1 # Set to 1 or higher to reduce potential loss of binary-log data  expire_logs_days = 7 # We will survive easter holidays  binlog_format = ROW # Use MIXED if you experience some troubles  # binlog_row_image = MINIMAL # Since 5.6    ;-------------------------------------------------------------------------------------------------------  ; Server id (required for replication but ok to set for standalone)  ;-------------------------------------------------------------------------------------------------------  server_id = 1 # Set to 1 for a master, 2 or above for a slave    ;-------------------------------------------------------------------------------------------------------  ; GTID replication  ;-------------------------------------------------------------------------------------------------------  # master-info-repository = TABLE  # enforce-gtid-consistency  # gtid_mode = ON    ;-------------------------------------------------------------------------------------------------------  ; Replication slave  ;-------------------------------------------------------------------------------------------------------  # log_slave_updates = ON # Use if Slave is used for Backup and PiTR (also for Galera)  # read_only = ON # Set to 1 to prevent writes on Slave  # skip_slave_start = 0 # 0: Do start slave automatically, 1: Don't start slave automatically  # slave_max_allowed_packet = 1G # Bug in some versions of MySQL  # relay-log = /mysql/logs/relay.log # Name of MySQL relay log file  # relay-log-info-file = /mysql/logs/relay-log.info # Name of MySQL relay info file  # relay-log-index = /mysql/logs/relay-log.index # Name of MySQL relay log index file  # replicate-do-db = db_name  # replicate-ignore-db = db_name  # log-slow-slave-statements = 2  # master-connect-retry = 60    ;-------------------------------------------------------------------------------------------------------  ; Master/Master replication  ;-------------------------------------------------------------------------------------------------------  # auto_increment_increment = 2 # For Master/Master replication (Set to number of nodes)  # auto_increment_offset = 1 # For Master/Master replication    ########################################################################################################  # Galera cluster (Percona XTRADB Cluster or MariaDB Cluster)  ########################################################################################################    ;-------------------------------------------------------------------------------------------------------  ; Enable / Disable Galera replication  ;-------------------------------------------------------------------------------------------------------  # wsrep_provider = none # Start mysqld without Galera  # wsrep_provider = /usr/lib64/libgalera_smm.so # Location of Galera plugin    ;-------------------------------------------------------------------------------------------------------  ; General Galera configuration  ;-------------------------------------------------------------------------------------------------------  # wsrep_provider_options = "gmcast.peer_timeout=PT10S; gcache.size = 10G; evs.keepalive_period=PT3S; evs.suspect_timeout=PT30S; evs.inactive_timeout=PT1M; evs.install_timeout=PT1M"    ;-------------------------------------------------------------------------------------------------------  ; Cluster configuration  ;-------------------------------------------------------------------------------------------------------  # wsrep_cluster_name = "my_cluster_name" # Cluster name - must be same for all nodes  # wsrep_cluster_address = gcomm://192.168.0.1,192.168.0.2,192.168.0.3 # List all cluster node IP addresses    ;-------------------------------------------------------------------------------------------------------  ; Node configuration  ;-------------------------------------------------------------------------------------------------------  # wsrep_node_name = "server_hostname" # Unique node name  # wsrep_node_address = 192.168.0.1 # Address where replication is done (this server's IP address)  # wsrep_node_incoming_address = 192.168.0.1 # Our external interface where application comes from    ;-------------------------------------------------------------------------------------------------------  ; Full syncronous replication  ;-------------------------------------------------------------------------------------------------------  # wsrep_causal_reads = 1 # Galera 3.5 and older  # wsrep_sync_wait = 1 # Galera 3.6 and newer    ;-------------------------------------------------------------------------------------------------------  ; Parallel threading  ;-------------------------------------------------------------------------------------------------------  # wsrep_slave_threads = 8 # 4 - 8 per core, not more than wsrep_cert_deps_distance    ;-------------------------------------------------------------------------------------------------------  ; SST configuration  ;-------------------------------------------------------------------------------------------------------  # wsrep_sst_method = xtrabackup-v2 # SST method (initial full sync): mysqldump, rsync, rsync_wan, xtrabackup  # wsrep_sst_auth = "sstuser:s3cr3t" # Username/password for sst user  # wsrep_sst_receive_address = 192.168.0.1 # Our address where to receive SST (local server's IP address)    ;-------------------------------------------------------------------------------------------------------  ; Logging  ;-------------------------------------------------------------------------------------------------------  # wsrep_log_conflicts = 1 # Log conflicts to error log    ;-------------------------------------------------------------------------------------------------------  ; Required general configuration of MySQL  ;-------------------------------------------------------------------------------------------------------  # Uncomment the following for Galera and comment out above any of these same variables  # default_storage_engine = InnoDB # Galera only works with InnoDB  # innodb_flush_log_at_trx_commit = 2 # Durability is achieved by committing to the Group  # innodb_autoinc_lock_mode = 2 # For parallel applying  # binlog_format = ROW # Galera only works with RBR  # query_cache_type = 0 # Use QC with Galera only in a Master/Slave set-up  # query_cache_size = 0  # log_slave_updates = 1 # For Point-In-Time Recovery  # innodb_locks_unsafe_for_binlog = 1  # event_scheduler = OFF # Should be disabled for galera as it is unsupported    ########################################################################################################  # HOWTO: Setting up SSL connections  ########################################################################################################  # 1. Create directory  # mkdir -p /etc/mysql-ssl/ && cd /etc/mysql-ssl  #  # 2. Create CA certificate  # openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem  #  # 3. Create server certificate, remove passphrase, and sign it  # openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem  # openssl rsa -in server-key.pem -out server-key.pem  # openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem  #  # 4. Create client certificate, remove passphrase, and sign it  # openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem  # openssl rsa -in client-key.pem -out client-key.pem  # openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem  #  # 5. Restart MySQL  # service mysql restart  #  # Example: Create standard user requiring SSL  # GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'%' IDENTIFIED BY 'secret' REQUIRE SSL;  #  # Example: Create replication user requiring SSL  # GRANT REPLICATION SLAVE ON *.* to 'repl'@'%' REQUIRE SSL;  # STOP SLAVE;  # CHANGE MASTER MASTER_SSL=1, MASTER_SSL_CA='/etc/mysql-ssl/ca.pem', MASTER_SSL_CERT='/etc/mysql-ssl/client-cert.pem', MASTER_SSL_KEY='/etc/mysql-ssl/client-key.pem';  # START SLAVE;  ########################################################################################################

PlanetMySQL Voting: Vote UP / Vote DOWN