Differences
This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
mysql_configuration [2013/10/01 12:54] 127.0.0.1 external edit |
mysql_configuration [2015/06/01 16:55] steve [MySQL Configuration] |
||
|---|---|---|---|
| Line 3: | Line 3: | ||
| * [[MySQL]] | * [[MySQL]] | ||
| * [[MySQL Character Sets]] | * [[MySQL Character Sets]] | ||
| - | * [[MySQL Configuration Changelog]] | ||
| * [[MySQL Logs]] | * [[MySQL Logs]] | ||
| * [[MySQL Optimization]] | * [[MySQL Optimization]] | ||
| - | * [[http://dev.digitaltrike.com/~steve/downloads/mysql/configuration/|Downloads]] | ||
| - | |||
| - | ==== Development Environment Configuration ==== | ||
| - | |||
| - | Here is the configuration for tahiti: | ||
| - | |||
| - | <code> | ||
| - | # Digital Trike MySQL Configuration | ||
| - | # | ||
| - | # Ubuntu specific | ||
| - | # Optimized for 2GB installation | ||
| - | # UTF-8 connections | ||
| - | # InnoDB default engine | ||
| - | # Binary logging | ||
| - | # | ||
| - | # Percona configuration: | ||
| - | # - Production server | ||
| - | # - Non-dedicated | ||
| - | # - OLTP | ||
| - | # - Non-replica | ||
| - | # - Amazon AWS | ||
| - | # - No virtualization | ||
| - | # - Hardware RAID | ||
| - | # - 4 CPUs | ||
| - | # - 2 GB RAM | ||
| - | # - Linux | ||
| - | # - Less than 100 tables | ||
| - | # - Percona Server 5.5 | ||
| - | # - Data directory /var/lib/mysql | ||
| - | # - Preferred engine InnoDB | ||
| - | # - Percona Server | ||
| - | # - InnoDB buffer pool - 1456M | ||
| - | # - InnoDB log files - 128M | ||
| - | # - InnoDB 100% durable | ||
| - | # - Use O_DIRECT | ||
| - | # - Binary logging | ||
| - | # - Thread cache - 50 | ||
| - | # - Key buffer - 32M | ||
| - | # - Connection limit - 500 | ||
| - | # - Table cache - unset | ||
| - | # - Strict SQL mode | ||
| - | # - SYSDATE() deterministic | ||
| - | # - Force InnoDB to start | ||
| - | # - Make InnoDB stricter | ||
| - | # | ||
| - | # Digital Trike configuration: | ||
| - | # - UTF8 connections | ||
| - | # - Log queries not using indexes | ||
| - | # - Slow query log: 1 second | ||
| - | # - Slow query log: /var/log/mysql/mysql-slow.log | ||
| - | # | ||
| - | # Ubuntu configuration: | ||
| - | # - port 3306 | ||
| - | # - socket: /var/run/mysqld/mysqld.sock | ||
| - | # - datadir: /var/lib/mysql | ||
| - | # - tmpdir: /tmp | ||
| - | # - error log: /var/log/mysql/mysql.log | ||
| - | # - skip external locking | ||
| - | # - Binary log size of 100 MB | ||
| - | |||
| - | [client] | ||
| - | # Ubuntu | ||
| - | port = 3306 | ||
| - | socket = /var/run/mysqld/mysqld.sock | ||
| - | |||
| - | # Digital Trike | ||
| - | default-character-set = utf8 | ||
| - | |||
| - | [mysqld_safe] | ||
| - | # Ubuntu | ||
| - | socket = /var/run/mysqld/mysqld.sock | ||
| - | nice = 0 | ||
| - | |||
| - | # Digital Trike | ||
| - | character-set-server = utf8 | ||
| - | |||
| - | [mysqld] | ||
| - | # Ubuntu | ||
| - | user = mysql | ||
| - | socket = /var/run/mysqld/mysqld.sock | ||
| - | port = 3306 | ||
| - | basedir = /usr | ||
| - | datadir = /var/lib/mysql | ||
| - | tmpdir = /tmp | ||
| - | skip-external-locking | ||
| - | # bind-address = 127.0.0.1 | ||
| - | log_error = /var/log/mysql/mysql.log | ||
| - | pid-file = /var/run/mysqld/mysqld.pid | ||
| - | |||
| - | # Ubuntu defaults - unused | ||
| - | # key_buffer = 16M | ||
| - | # max_allowed_packet = 16M | ||
| - | # thread_stack = 192K | ||
| - | # thread_cache_size = 8 | ||
| - | # myisam-recover = BACKUP | ||
| - | # query_cache_limit = 1M | ||
| - | # query_cache_size = 16M | ||
| - | # expire_logs_days = 10 | ||
| - | # max_binlog_size = 100M | ||
| - | |||
| - | # Digital Trike - Encoding | ||
| - | character-set-server = utf8 | ||
| - | |||
| - | # Digital Trike - Logging | ||
| - | log_queries_not_using_indexes = 1 | ||
| - | slow_query_log = 1 | ||
| - | slow_query_log_file = /var/log/mysql/mysql-slow.log | ||
| - | long_query_time = 1 | ||
| - | max_binlog_size = 100M | ||
| - | |||
| - | # Percona - General | ||
| - | default_storage_engine = InnoDB | ||
| - | |||
| - | # Percona - MyISAM | ||
| - | key_buffer_size = 32M | ||
| - | myisam_recover = FORCE,BACKUP | ||
| - | |||
| - | # Percona - Safety | ||
| - | max_allowed_packet = 16M | ||
| - | max_connect_errors = 1000000 | ||
| - | # sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY | ||
| - | # sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE | ||
| - | sysdate_is_now = 1 | ||
| - | innodb = FORCE | ||
| - | innodb_strict_mode = 1 | ||
| - | |||
| - | # Percona - Binary Logging | ||
| - | log_bin = /var/lib/mysql/mysql-bin | ||
| - | expire_logs_days = 14 | ||
| - | sync_binlog = 1 | ||
| - | |||
| - | # Percona - Caches and Limits | ||
| - | tmp_table_size = 32M | ||
| - | max_heap_table_size = 32M | ||
| - | query_cache_type = 0 | ||
| - | query_cache_size = 0 | ||
| - | max_connections = 500 | ||
| - | thread_cache_size = 50 | ||
| - | open_files_limit = 65535 | ||
| - | table_definition_cache = 1024 | ||
| - | table_open_cache = 2048 | ||
| - | |||
| - | # Digital Trike - Caching | ||
| - | # query_cache_type = 1 | ||
| - | # query_cache_size = 128M | ||
| - | |||
| - | # Percona - InnoDB | ||
| - | innodb_flush_method = O_DIRECT | ||
| - | innodb_log_files_in_group = 2 | ||
| - | innodb_log_file_size = 5242880 | ||
| - | innodb_flush_log_at_trx_commit = 1 | ||
| - | innodb_file_per_table = 1 | ||
| - | # Tweaked for tahiti because it's using so much RAM | ||
| - | innodb_buffer_pool_size = 256M | ||
| - | # 2GB RAM (original configuration) | ||
| - | # innodb_buffer_pool_size = 1456M | ||
| - | # 3GB, 4GB RAM | ||
| - | # innodb_buffer_pool_size = 2G | ||
| - | |||
| - | [mysqldump] | ||
| - | # Ubuntu | ||
| - | quick | ||
| - | quote-names | ||
| - | max_allowed_packet = 16M | ||
| - | |||
| - | # Digital Trike | ||
| - | default-character-set = utf8 | ||
| - | |||
| - | [mysql] | ||
| - | # Ubuntu | ||
| - | |||
| - | # Digital Trike | ||
| - | default-character-set = utf8 | ||
| - | |||
| - | [mysqladmin] | ||
| - | |||
| - | # Digital Trike | ||
| - | default-character-set = utf8 | ||
| - | |||
| - | [mysqlcheck] | ||
| - | |||
| - | # Digital Trike | ||
| - | default-character-set = utf8 | ||
| - | |||
| - | [mysqlimport] | ||
| - | |||
| - | # Digital Trike | ||
| - | default-character-set = utf8 | ||
| - | |||
| - | [isamchk] | ||
| - | # Ubuntu - unused key_buffer | ||
| - | # key_buffer = 16M | ||
| - | !includedir /etc/mysql/conf.d/ | ||
| - | </code> | ||
| ==== Update Runtime Configuration ==== | ==== Update Runtime Configuration ==== | ||
