# # This group is read both both by the client and the server # use it for options that affect everything # [client] #user = captain #password = PASS #host = 127.0.0.1 port = 3306 socket = /var/lib/mysql/mysql.sock #/var/run/mysqld/mysqld.sock ## Default is Latin1, if you need UTF-8 set this (also in server section) default-character-set = utf8 [mysqld_safe] socket = /var/lib/mysql/mysql.sock #/var/run/mysqld/mysqld.sock nice = 0 open_files_limit = 8192 # You possibly have to adapt your O/S settings as well user = mysql log-error = /var/log/mysqld.log #hostname_error.log [mysql] #no-auto-rehash # faster start of mysql but no tab completition no_auto_rehash max_allowed_packet = 128M #16M prompt = '\u@\h [\d]> ' default_character_set = utf8 # Possibly this setting is correct for most recent Linux systems # [mysqld] local-infile = 0 #innodb_force_recovery = 6 ## ============================================================================== ### * Basic Settings ### ============================================================================== user = mysql # Connection and Thread variables pid-file = /var/lib/mysql/mysql.pid # /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock # /var/lib/mysql/mysql.sock port = 3306 #basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 0 query-cache-size = 0 max-connections = 15000 thread-cache-size = 5000 open-files-limit = 150000 table-definition-cache = 1024 table-open-cache = 50000 language = /usr/share/mysql/english lc_messages_dir = /usr/share/mysql lc_messages = en_US ## Disable external locking (system locking) in MySQL, affects only MyISAM ### http://dev.mysql.com/doc/refman/5.5/en/external-locking.html ##skip-locking skip-external-locking # key-buffer-size = 32M # ## This replaces the startup script and checks MyISAM tables if needed ## the first time they are touched myisam-recover = FORCE,BACKUP # max-allowed-packet = 128M #16M # max-connect-errors = 1000000 # ### Disable 'INSERT DELAYED' statements in MySQL ### http://dev.mysql.com/doc/refman/5.5/en/insert-delayed.html max_delayed_threads = 0 ## ============================================================================== ### * Character sets ### ============================================================================== ### Default is Latin1, if you need UTF-8 set all this (also in client section) ### #default-character-set = utf8 #default-collation = utf8_general_ci #character_set_server = utf8 #collation_server = utf8_general_ci # ### Force MySQL to generate short (pre-4.1) password hashes for new passwords, ### useful for compatibility when the server must support clients < MySQL 4.1 ### http://dev.mysql.com/doc/refman/5.5/en/old-client.html #old_passwords = 0 # ### Do not resolve host names when checking client connections, use only IP ### http://dev.mysql.com/doc/refman/5.5/en/dns.html skip-name-resolve # ### localhost which is more compatible and is not less secure. ### http://dev.mysql.com/doc/refman/5.5/en/can-not-connect-to-server.html bind-address = 127.0.0.1 ## ============================================================================== ### * MyISAM ### ============================================================================== ### This replaces the startup script and checks MyISAM tables if needed ### the first time they are touched. On error, make copy and try a repair. myisam_recover = FORCE,BACKUP # ### ============================================================================== ### * Fine Tuning ### ============================================================================== ### http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html ### ### Info: calculate memory use -> ### memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections ### ### Maximum permitted number of simultaneous client connections for MySQL ### http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html ### default: 151 ### range: 1 .. 100000 max_connections = 505 # used 80# Values < 1000 are typically good ## The maximum number of simultaneous connections permitted ### default: 0 ### range: 0 .. 4294967295 max_user_connections = 500 #used 40 # Limit one specific user/application # ## The number of outstanding connection requests MySQL can have, it's the size ## of the listen() queue for incoming TCP/IP connections. The maximum value on ## Linux is directed by net.ipv4.tcp_max_syn_backlog (1024 by default) ### default: 50 ### range: 1 .. 65535 back_log = 256 # ### Info: Reduced timeout to prevent idle clients holding connections ### Number of seconds MySQL server waits for a connect packet before responding ### with 'bad handshake' ### default 10 connect_timeout = 5 # ### Number of second MySQL server waits for activity on a *NONINTERACTIVE* conn ### default: 28800 ### range: 1 .. 2147483 wait_timeout = 60 # ### Number of seconds MySQL server waits for activity on an *INTERACTIVE* conn ### default: 28800 (min. 1) interactive_timeout = 30 # ### Stack size for each thread. The default of 192KB (256KB for 64-bit systems) ### is large enough for normal operation. If the thread stack size is too small, ### it limits the complexity of the SQL statements that the server can handle, ### the recursion depth of stored procedures, and other memory-consuming actions ### default: 192K -> 32Bit & 256K -> 64Bit # thread_stack = 256K # ### How many threads the MySQL server should cache for reuse ### default: 0 ### range: 0 .. 16384 thread_cache_size = 505 #used 4 # Up to max_connections makes sense # ### Try number of CPU's*2 for thread_concurrency ### default: 10 ### range: 1 .. 512 thread_concurrency = 8 # ### Buffers # ### default: 2M (2097144) # Session variables sort_buffer_size = 4M # Could be too big for many small sorts join_buffer_size = 4M # Resist to change this parameter if you do not know what you are doing # ### default 0.125M (131072) ### range: 8200 .. 2147479552 read_buffer_size = 2M #Resist to change this parameter if you do not know what you are doing # ### default: 0.25M (262144) ### range: 8200 .. 4294967295 read_rnd_buffer_size = 1M #Resist to change this parameter if you do not know what you are doing # ### default: 8M (8388608) ### range: 4 .. 18446744073709547520 myisam_sort_buffer_size = 32M # ### default: 8M (8388608) ### range: 0 .. 18446744073709547520 bulk_insert_buffer_size = 16M # ### Maximum size of internal in-memory temporary table in MySQL ### http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html ### default: system dependent ### range: 1024 .. 4294967295 tmp_table_size = 32M #used512M # Make sure your temporary results do NOT contain BLOB/TEXT attributes # # MEMORY variables ### This variable sets the maximum size to which user-created MEMORY ### tables are permitted to grow. ### http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html ### deafult: 16M (16777216) ### range: 16384 .. 1844674407370954752 max_heap_table_size = 512M # Should be greater or equal to tmp_table_size # ### Maximum number of temporary tables a client can keep open at the same time. ### default: 32 ### range: 1 .. 18446744073709547520 ## max_tmp_tables = 32 # ### The number of files that the operating system permits mysqld to open. This is the real value permitted by the system and might be different from the value you gave using the --open-files-limit option to mysqld or mysqld_safe. The value is 0 on systems where MySQL cannot change the number of open files. open-files-limit = 8192 # ### Changes the number of file descriptors available to MySQL ### default: 0 ### range: 0 .. 65535 open_files_limit = 8192 # ### The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. ### default: 64 ### range: 1 .. 524288 table_cache = 128k # # Other buffers and caches table_definition_cache = 400 # As big as many tables you have ### The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. table_open_cache = 512 #used 128 # connections x tables/connection (~2) # # MyISAM variables ### Controls the amount of memory allocated for MyISAM key cache/buffer size ### http://dev.mysql.com/doc/refman/5.5/en/myisam-key-cache.html ### deafult: 8M (8388608) ### range: 8 .. 4294967295 key_buffer_size = 32M # Set to 25 - 33 % of RAM if you still use MyISAM ### alias for key_buffer_size key_buffer = 32M # ### default: 1 (AUTO) ### range: 0 (NEVER) | 1 (AUTO) | 2 (ALWAYS) concurrent_insert = 1 # ### ============================================================================== ### * Query Cache Configuration ### ============================================================================== ### Cache only tiny result sets, so we can fit more in the query cache. ### Enable it for vast improvement and it may be all you need to tweak. ### http://dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html # ### for more write intensive setups, set to DEMAND or OFF ### default: 1 ### range: 0 (OFF) | 1 (ON) | 2 (DEMAND) query_cache_type = 1 # Set to 0 to avoid global QC Mutex # ### Do not cache results that are larger than this number of bytes ### default: 1M (1048576) ### range: 0 .. 18446744073709547520 query_cache_limit = 2M # ### The amount of memory allocated for caching query results ### default: 0 ### range: 0 .. 18446744073709547520 query_cache_size = 128M # Avoid too big (> 128M) QC because of QC clean-up lock! # ### The minimum size for blocks allocated by the query cache ### deafult: 4K (4096) ### range: 512 .. 18446744073709547520 ##query_cache_min_res_unit = 4K # ### The allocation size of memory blocks that are allocated for objects ### created during statement parsing and execution ### default: 8K (8192) ### range: 1024 .. 18446744073709547520 ##query_alloc_block_size = 8K # ### ============================================================================== ### * Logging and Replication ### ============================================================================== ### Both location gets rotated by the cronjob. ### Be aware that this log type is a performance killer. ### As of 5.1 you can enable the log at runtime! ### http://dev.mysql.com/doc/refman/5.5/en/server-logs.html ##general_log_file = /var/log/mysql/mysql.log ##general_log = 1 # ### Error logging can also goes to syslog due ### to /etc/mysql/conf.d/mysqld_safe_syslog.cnf. ### We do want to know about network errors and such ### so disable produce additional warning messages to MySQL error log ### http://dev.mysql.com/doc/refman/5.5/en/error-log.html log_error = /var/log/mysqld.log log_warnings = 2 #used 0 # innodb_print_all_deadlocks = 1 # wsrep_log_conflicts = 1 # for Galera only! # # Slow Query Log # ### Enable the slow query log to see queries with especially long duration ### http://dev.mysql.com/doc/refman/5.5/en/query-log.html slow_query_log = 0 #used 1 #slow_query_log_file = /var/log/mysql/mariadb-slow.log slow_query_log_file = /var/log/mysql-slow.log log_queries_not_using_indexes = 1 long_query_time = 0.5 #used 10 min_examined_row_limit = 100 ##log_slow_rate_limit = 1000 ##log_slow_verbosity = query_plan # ##log-queries-not-using-indexes ##log_slow_admin_statements # ### The following can be used as easy to replay backup logs or for replication. ### note: if you are setting up a replication slave, see README.Debian about ### other settings you may need to change. # Binary logging and Replication # server-id = 1 # report_host = master1 # auto_increment_increment = 2 # auto_increment_offset = 1 # log_bin = /var/log/mysql/mariadb-bin # log_bin_index = /var/log/mysql/mariadb-bin.index ## not fab for performance, but safer # sync_binlog = 1 # binlog_cache_size = 1M # binlog_stmt_cache_size = 1M # Since 5.5 expire_logs_days = 10 max_binlog_size = 100M # Make bigger for high traffic to reduce number of files binlog_format = ROW # Use MIXED if you experience some troubles # binlog_row_image = MINIMAL # Since 5.6 # auto_increment_increment = 2 # For Master/Master set-ups use 2 for both nodes # auto_increment_offset = 1 # For Master/Master set-ups use 1 and 2 ## Slave variables # relay_log = /var/log/mysql/relay-bin # relay_log_index = /var/log/mysql/relay-bin.index # relay_log_info_file = /var/log/mysql/relay-bin.info # log_slave_updates = 1 # Use if Slave is used for Backup and PiTR # read_only = 0 # Set to 1 to prevent writes on Slave # skip_slave_start = 0 # To avoid start of Slave threads # ### If applications support it, this stricter sql_mode prevents some ### mistakes like inserting invalid dates etc. #sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL #sql_mode = TRADITIONAL,ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES # Be careful changing this afterwards skip_name_resolve = 1 # Set to 1 if you do not trust your DNS or experience problems # ### ============================================================================== ### * InnoDB ### ============================================================================== ### InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. ### Read the manual for more InnoDB related options. There are many! ### http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb.html # # innodb_strict_mode = ON default_storage_engine = InnoDB # innodb-flush-method = O_DIRECT # innodb-log-files-in-group = 2 # ## The size in bytes of each log file in a log group in InnoDB innodb_log_file_size = 500M # ### InnoDB memory buffer (cache data and indexes of its tables), the larger you ### set this value, the less disk I/O is needed to access data in tables. On a ### dedicated database server, you may set this to up to 80% of the machine ### physical memory size. ### http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html ### http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/ innodb_buffer_pool_size = 512M # # Go up to 80% of your available RAM # innodb_buffer_pool_instances = # Bigger if huge InnoDB Buffer Pool or high concurrency # innodb-file-per-table = 1 # slow-query-log = 1 slow-query-log-file = /var/log/mysql-slow.log # ### The size in bytes of a memory pool InnoDB uses to store data dictionary ### information and other internal data structures. innodb_additional_mem_pool_size = 500K # ### Size of buffer that InnoDB uses to write to the log files on disk innodb_log_buffer_size = 8M # ### Store each InnoDB table and its indexes in its own file, by default InnoDB ### tables and indexes are stored in system tablespace. ### (Suggested by Jordi Prats from systemadmin.es) ### http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html innodb_file_per_table = 1 # Is the recommended way nowadays innodb_file_format_check = 1 # innodb_file_format = Barracuda # For dynamic and compressed InnoDB tables # ## Full ACID support for InnoDB, for each transaction = commit + flush to disk ## 0 = write + flush once every second (best perfomance) ## 1 = write + flush after each commit (best durability + safe) ## 2 = write log buffer at every commit, but flush it once every second innodb_flush_log_at_trx_commit = 0 innodb_open_files = 400 innodb_io_capacity = 400 # ### How InnoDB will flush data to the disk: by default uses fsync() causing ### "double buffering" data in OS cache and buffer pool. O_DIRECT bypass OS cache ### avoiding double buffering, tip suggested by Jordi Prats (systemadmin.es). ### http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html # innodb_flush_method = O_DIRECT # O_DIRECT is sometimes better for direct attached storage # innodb_write_io_threads = 8 # If you have a strong I/O system or SSD # innodb_read_io_threads = 8 # If you have a strong I/O system or SSD # innodb_io_capacity = 1000 # If you have a strong I/O system or SSD ### Number of OS threads that can run concurrently inside the InnoDB engine, rest ### of the threads have to wait in a FIFO queue for execution. The optimal ### setting depends on the number of CPUs, number of disks, and the kind of load. ### A recommended value is 2 times the number of CPUs plus the number of disks. ##innodb_thread_concurrency = 0 # ### ============================================================================== ### * Security Features ### ============================================================================== # ### Disable 'LOAD DATA LOCAL' feature on MySQL ### http://dev.mysql.com/doc/refman/5.5/en/load-data-local.html local_infile = 0 # If you are security aware secure_auth = 1 # If you are security aware # ### Disable symbolic link support on MySQL ### http://dev.mysql.com/doc/refman/5.5/en/symbolic-links.html skip-symbolic-links # ## Do not permit SELECT statements that probably need to examine more than ## max_join_size rows, or row combinations or that are likely to do more than ## max_join_size disk seeks. Setting this variable to a value other than DEFAULT ## resets the value of sql_big_selects to 0 ### deafault: 16777216 TB (18446744073709551615) ### range: 1 .. 18446744073709551615 ## max_join_size = 256M # ### Read the manual, too, if you want chroot! ### chroot = /var/lib/mysql/ # ### For generating SSL certificates I recommend the OpenSSL GUI "tinyca". ### ## ssl-ca =/etc/mysql/cacert.pem ## ssl-cert =/etc/mysql/server-cert.pem ## ssl-key =/etc/mysql/server-key.pem # # # # Galera specific MySQL parameter # # default_storage_engine = InnoDB # Galera only works with InnoDB # innodb_flush_log_at_trx_commit = 0 # 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 # # # WSREP parameter # # wsrep_provider = none # Start mysqld without Galera # wsrep_provider = /usr/lib/galera/libgalera_smm.so # Location of Galera Plugin # wsrep_provider_options = 'gcache.size = 1G' # Depends on you workload, WS kept for IST # # wsrep_cluster_name = "My cool Galera Cluster" # Same Cluster name for all nodes # wsrep_cluster_address = "gcomm://" # Initial Cluster start # wsrep_cluster_address = "gcomm://192.168.0.2,192.168.0.3" # Start other nodes like this # # wsrep_node_name = "Node A" # Unique node name # wsrep_node_address = 192.168.0.1 # Our address where replication is done # wsrep_node_incoming_address = 10.0.0.1 # Our external interface where application comes from # wsrep_causal_reads = 1 # If you need realy full-synchronous replication (Galera 3.5 and older) # wsrep_sync_wait = 1 # If you need realy full-synchronous replication (Galera 3.6 and newer) # wsrep_slave_threads = 1 # 4 - 8 per core, not more than wsrep_cert_deps_distance # wsrep_sst_method = mysqldump # SST method (initial full sync): mysqldump, rsync, rsync_wan, xtrabackup # wsrep_sst_auth = sst:secret # Username/password for sst user # wsrep_sst_receive_address = 192.168.0.1 # Our address where to receive SST [mysqldump] quick quote-names max_allowed_packet = 128M #16M # # [isamchk] key_buffer = 16M #sort_buffer = 4M #read_buffer = 2M #write_buffer = 2M #[client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d