MySQL数据库配置详解

2024-07-24 12:35:51

字体：大中小

来源：转载

供稿：网友

　　下面是我生产环境中MySQL的配置详解：

　　[mysqld]

　　mysql服务端配置

　　server-id=1

　　服务ID，每个服务的服务ID不同即可

　　log-bin=mysql-bin

　　定义bin_log的位置和名称，mysql操作记录二进制日志

　　relay_log=mysql-realy-bin

　　定义relay_log的位置和名称，从服务器解析主服务器二进制文件后记录的文件

　　relay_log_index=slave-mysql-realy-bin.index

　　定义relay_log_index的位置和名称

　　expire_logs_days=15

　　超过15天的binlog删除

　　binlog_format=mixed

　　mysql复制方式为混合模式

　　说明：mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

　　① STATEMENT模式（SBR）

　　每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

　　② ROW模式（RBR）

　　不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

　　③ MIXED模式（MBR）

　　以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

　　auto-increment-increment=2

　　自增量每次增加的值改为2

　　auto-increment-offset= 1

　　第一次加载数值时的偏移值的个位值是1

　　innodb_flush_log_at_trx_commit=1

　　# 0：如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作（执行是由mysql的master thread线程来执行的。

　　# 主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交）默认的日志文件是ib_logfile0,ib_logfile1

　　# 1：当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。

　　# 2：如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。

　　# 每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有缓存的，所以这个写入并不能保证数据已经写入到物理磁盘

　　# 默认值1是为了保证完整的ACID。当然，你可以将这个配置项设为1以外的值来换取更高的性能，但是在系统崩溃的时候，你将会丢失1秒的数据。

　　# 设为0的话，mysqld进程崩溃的时候，就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。

　　# 总结

　　# 设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2，如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能

　　replicate-ignore-db=sys

　　复制时忽略的数据库

　　datadir=/data/mysql_data

　　数据存储目录

　　socket=/var/lib/mysql/mysql.sock

　　对服务器端指定套接字文件路径

　　对mysql.sock来说，其作用是程序与mysqlserver处于同一台机器，发起本地连接时可用。

　　character_set_server=utf8

　　设置字符集

　　sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

　　# mysql数据库的中有一个环境变量sql_mode,定义了mysql应该支持的sql语法，数据校验等

　　# STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制

　　# NO_ZERO_IN_DATE：这个模式影响了是否允许日期中的月份和日包含0。如果开启此模式，2016-01-00是不允许的，但是0000-02-01是允许的。它实际的行为受到 strict mode是否开启的影响1。

　　# NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期。它实际的行为受到 strictmode是否开启的影响2。

　　# ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL

　　# NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户

　　# NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

　　symbolic-links=0

　　不支持符号链接

　　log-error=/var/log/mysqld.log

　　错误日志文件目录

　　pid-file=/var/run/mysqld/mysqld.pid

　　进程文件目录

　　innodb_buffer_pool_size=8G

　　InnoDB缓冲池大小（这里设置为内存大小的一半）

　　# InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM

　　# 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.

　　# 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%

　　# 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.

　　# 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,

　　# 所以不要设置的太高.

　　innodb_log_file_size=256M

　　确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间

　　innodb_flush_method=O_DIRECT

　　InnoDB 用来刷新日志的方法为O_DSYNC

　　# 表空间总是使用双重写入刷新方法

　　# 默认值是 “fdatasync”, 另一个是 “O_DSYNC”.

　　# 一般来说，如果你有硬件 RAID 控制器，并且其独立缓存采用 write-back 机制，并有着电池断电保护，那么应该设置配置为 O_DIRECT

　　# 否则，大多数情况下应将其设为 fdatasync

　　max_connections=500

　　MySQL的最大连接数

　　# 如果服务器的并发连接请求量比较大，建议提高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。

　　innodb_autoextend_increment=128

　　每次自动扩展的增量大小

　　# 增加InnoDB系统空间最简单的方法就是，在配置文件中配置autoextend.

　　# #该innodb_autoextend_increment设置不会影响每个表的单独表空间文件或常规表空间文件。无论innodb_autoextend_increment设置如何，这些文件都是自动扩展的。初始扩展是少量的，之后以4MB的增量扩展。

　　# 默认是8M, 如果一次insert数据量比较多的话, 可以适当增加.

（编辑：武林网）

上一篇：mysql5.7.18安装

下一篇：找出mysql最占硬盘的数据