# # This group is read both both by the client and the server # use it for options that affect everything # [client] 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 [mysqld] local-infile = 0 #innodb_force_recovery = 4 ## ============================================================================== ### * Basic Settings ### ============================================================================== user = mysql pid-file = /var/lib/mysql/mysql.pid #/var/run/mysqld/mysqld.pid socket = /var/lib/mysql/mysql.sock #/var/run/mysqld/mysqld.sock port = 3306 #basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp 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 # ## This replaces the startup script and checks MyISAM tables if needed ## the first time they are touched myisam-recover = BACKUP # ### 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 = 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 = 80 ## The maximum number of simultaneous connections permitted ### default: 0 ### range: 0 .. 4294967295 max_user_connections = 40 # ## 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 = 4 # ### Try number of CPU's*2 for thread_concurrency ### default: 10 ### range: 1 .. 512 thread_concurrency = 8 # ### Buffers # ### default: 2M (2097144) sort_buffer_size = 4M join_buffer_size = 4M # ### default 0.125M (131072) ### range: 8200 .. 2147479552 read_buffer_size = 2M # ### default: 0.25M (262144) ### range: 8200 .. 4294967295 read_rnd_buffer_size = 1M # ### 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 = 512M # ### 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 # ### 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 = 128 # ### 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 = 128 # ### 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 ### 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 # ### 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 = 768M # ### 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 = 0 # ### 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 = 1 ##slow_query_log_file = /var/log/mysql/mariadb-slow.log ##long_query_time = 10 ##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. ##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 expire_logs_days = 10 max_binlog_size = 100M ### slaves ##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 ##read_only # ### If applications support it, this stricter sql_mode prevents some ### mistakes like inserting invalid dates etc. ##sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL # ### ============================================================================== ### * 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 # default_storage_engine = InnoDB # ### The size in bytes of each log file in a log group in InnoDB ##innodb_log_file_size = 5M # ### 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 = 768M # ### 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 # ## 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 = 1 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 # ### 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 # ### 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 # # [mysqldump] quick quote-names max_allowed_packet = 16M # # [mysql] #no-auto-rehash # faster start of mysql but no tab completition # # [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