Differences
This shows you the differences between two versions of the page.
Next revision Both sides next revision | |||
mysql_configuration [2013/10/01 12:54] 127.0.0.1 external edit |
mysql_configuration [2015/06/01 16:54] steve [Development Environment Configuration] |
||
---|---|---|---|
Line 9: | Line 9: | ||
* [[http://dev.digitaltrike.com/~steve/downloads/mysql/configuration/|Downloads]] | * [[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 ==== | ||