SET GLOBAL
命令来修改。这种方式非常方便,并且如果修改后出现问题,还能马上恢复原设置。但到最后,仍然需要把这个改变写到配置文件中,使之永久生效。[mysqld]
节)innodb_buffer_pool_size
的单位是 byte
,而 max_connection
是没有单位的。RAM
是旧的 2 倍,因此可以把所有的配置项的值都设置成之前的 2 倍”innodb_buffer_pool_size
:这是任何使用 InnoDB 存储引擎的 MySQL 在安装时第一个应该要查看的配置。Buffer pool 是用来缓存数据和索引的:尽可能地设置大一点,以确保在进行大多数读操作时是读内存而不是读磁盘。一般的经验是服务器内存的 2/3 左右,比如设置为 5-6GB(8GB RAM),20-25G(32GB RAM),100-120GB(128GB RAM)。
innodb_log_file_size
:这个选项是设置 redo 日志(重做日志)的大小。redo 日志 是用来确保写入的数据能够快速地写入,并且持久化,还可以用于崩溃恢复(crash recovery)。MySQL 5.1 之前,这个选项很难去进行调整,因为你既想要加大 redo 日志来提高性能,又想要减小 redo 日志来进行快速的崩溃恢复。幸运的是,自 MySQL 5.5 之后,崩溃恢复的性能有了很大的提高,现在你可以拥有快速写入性能的同时,还能满足快速崩溃恢复。一直到 MySQL 5.5,redo 日志的总大小被限制在 4GB (默认有 2 个日志文件)。这个在 MySQL 5.6 中被增加了。
启动的时候设置 innodb_log_file_size = 512M
(也就是 1GB 大小的 redo 日志),这样可以提供充足的写空间。如果你知道你的应用是频繁写入的,并且你使用的 MySQL 版本是 MySQL 5.6,你可以设置 innodb_log_file_size = 4G。
max_connections
:如果你经常遇到 "Too many connections" 的错误,是因为 max_connections 太小了。这个错误很长见到,因为应用程序没有正确地关闭与数据库的连接,你需要设置连接数为比默认 151 更大的值。max_connections 设置过高(如 1000 或更高)的一个主要缺点是当服务器运行 1000 个或者更多的事务时,会响应缓慢甚至没有响应。在应用程序端使用连接池或者在 MySQL 端使用线程池有助于解决这个问题。
从 MySQL 5.5 开始,InnoDB 成为了默认的存储引擎,并且它的使用频率比其他存储引擎的要多得多。这就是要小心配置它的原因。
innodb_file_per_table
:这个配置项会决定 InnoDB 是使用共享表空间 (innodb_file_per_table = OFF
) 来存储数据和索引,还是为每个表使用一个单独的 .ibd 文件 (innodb_file_per_table= ON
)。对每个表使用一个文件的方式,在 drop、truncate
, 或者重建表的时候,会回收这个表空间。在一些高级特性,如压缩的时候也需要开启使用独立表空间。然而这个选项却不能带来性能的提升。你不想使用独立表空间的一个主要场景是:有很多的表(例如:10000 以上张表)。
在 MySQL 5.6 中,这个配置项是默认开启的,因此多数情况下,你无需操作。对于早期的 MySQL 版本,需要在加载数据之前把它设置成 ON ,因为它只对新创建的表有影响。
innodb_flush_log_at_trx_commit
:默认值为 1,表示 InnoDB 完全支持 ACID 特性。例如在在一个主节点上,你主要关注数据安全性,这是最好的设置值。然而它会对速度缓慢的磁盘系统造成很大的开销,因为每次将改变刷新到 redo 日志的时候,都需要额外的 fsync
操作。设置为 2,可靠性会差一点,因为已提交的事务只会 1 秒钟刷新一次到 redo 日志,但在某些情况下,对一个主节点而言,这仍然是可以接受的,而且对于复制关系的从库来说,这是一个很好的值。设置为 0,速度更快,但是在遇到崩溃的时候很可能会丢失一些数据,这只对从库是一个好的设置值。
innodb_flush_method
:这个设置项决定了数据和日志刷新到磁盘的方式。当服务器硬件有 RAID 控制器、断电保护、采取 write-back 缓存机制的时候,最常用的值是 O_DIRECT;其他大多数场景使用默认值 fdatasync。sysbench 是一个帮助你在这两个值之间做出选择好工具。
innodb_log_buffer_size
:这个设置项用来设置缓存还没有提交的事务的缓冲区的大小。默认值(1MB) 一般是够用的,但一旦事务之中带有大 blob/text 字段,这个缓冲区会被很快填满,并引起额外的 I/O 负载。看看 innodb_log_waits 这个状态变量的值,如果不是 0 的话,需要增加 innodb_log_buffer_size。
query_cache_size
:Query Cache(查询缓存)是一个众所周知的瓶颈位,即使在并发量不高的时候也会出现。最好的选择是从一开始就禁用它,通过设置 query_cache_size = 0
(MySQL 5.6 中现在已经默认禁用),并通过其它途径去提高读查询:合适的索引,增加从库去分散读压力,或者使用一个额外的缓存(例如 memcache 或者 redis)。如果你的 MySQL 已经开启了查询缓存,并且没有发现有任何错误,开启查询缓存可能是有利的,如果要禁用它,就需要谨慎了。
log_bin
:如果要让一个节点做为复制关系中的主节点,启用二进制日志(binary log)是必须的。这样的话,同时需要设置全局唯一的 server_id
。 对于单节点,在希望做基于时间点的恢复的时候,开启这个选项也是很有用的:恢复最新的备份和应用二进制日志。二进制日志一旦创建,会被永久保存。所以如果不想耗尽磁盘空间,应该使用 PURGE BINARY LOGS 清理旧的二进制日志文件,或者设置 expire_logs_days
选项指定多少天之后,自动清理过期的二进制日志。
记录二进制日志不是没有开销的。所以,例如是一个复制关系中的从节点,建议禁用二进制日志。
skip_name_resolve
:当一个客户端连接上来的时候,服务端会执行主机名解释操作,当 DNS 很慢时,建立的连接也会很慢。因此建议在启动的时候设置 skip_name_resolve
来禁用 DNS 查找。唯一的局限是 GRANT 语句仅且仅能使用 IP 地址,所以,在已有系统中添加这个选项时需要格外小心。
[client] #password = your_password port = 3306 socket = /Data/apps/mysql/mysql.sock #default-character-set = utf8 [mysqld] port = 3306 socket = /Data/apps/mysql/mysql.sock datadir = /Data/apps/mysql/data log_error = /Data/apps/mysql/data/error.log explicit_defaults_for_timestamp = true skip-external-locking skip_name_resolve character_set_server = utf8 default_storage_engine = InnoDB #线程缓存 thread_cache_size = 128 #innodb innodb_buffer_pool_size = 10G innodb_buffer_pool_instances = 1 innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_data_file_path = ibdata1:1G:autoextend innodb_file_per_table = on innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_io_capacity = 100 innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 2 innodb_open_files = 2000 innodb_file_per_table = on innodb_autoextend_increment = 256 innodb_print_all_deadlocks = 1 innodb_thread_concurrency = 16 max_allowed_packet = 64M max_connections = 300 table_open_cache = 1024 #sort_buffer_size默认256k sort_buffer_size = 1M #read_buffer_size默认128k read_buffer_size = 1M #read_rnd_buffer_size默认256k read_rnd_buffer_size = 4M #join_buffer_size默认256k join_buffer_size = 1M #myisam_sort_buffer_size默认8M myisam_sort_buffer_size = 128M open_files_limit = 65535 max_heap_table_size = 256M tmp_table_size = 256M #myisam key_buffer_size = 512M myisam_sort_buffer_size = 128M bulk_insert_buffer_size = 64M #关闭查询缓存 query_cache_type = 0 query_cache_size = 0 #慢查询 long_query_time = 3 slow_query_log = 1 slow_query_log_file = /Data/apps/mysql/data/slow.log #如果运行的SQL没有使用索引,不会将该SQL记录到慢查询日志中 log_queries_not_using_indexes = OFF #启用事件 event_scheduler = 1 sql_mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION #表名不区分大小写 lower_case_table_names = 1 #活动连接时间,默认8小时,28800秒 interactive_timeout = 28800 #非活动连接时间,300秒 wait_timeout = 1800 log_bin = mysql-bin binlog_cache_size = 2M sync_binlog = 60 relay_log = relay-log relay_log_index = relay-log.index #日志时间戳变量,默认UTC,为了方便查看改为SYSTEM log_timestamps = SYSTEM #log-bin 保留7天,7天前日志自动删除 expire_logs_days = 7 default_password_lifetime = 0 #启用远程表引擎 federated server-id = 91 #启用GTID多线程复制 binlog_format = mixed #log-slave-updates = ON #开启GTID gtid_mode = ON enforce_gtid_consistency = TRUE #开启并行复制MTS slave_parallel_type = LOGICAL_CLOCK slave_parallel_workers = 4 master_info_repository = TABLE relay_log_info_repository = TABLE relay_log_recovery = ON #切换完成后去掉 #replicate_wild_ignore_table = flow.adsense_% #read-only = ON #服务器启动不启动从属线程 skip-slave-start symbolic-links = 0 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout