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
No comments:
Post a Comment