MY.CNF

迈不过友情╰ 2024-03-22 21:06 40阅读 0赞

\#  
\[client\]  
port = 3306  
socket = /var/lib/mysql/mysql.sock

\[mysql\]  
prompt = "\\u@mysqldb \\R:\\m:\\s \[\\d\]> "  
no\_auto\_rehash  
loose-skip-binary-as-hex

\[mysqld\]  
user = mysql  
port = 3306  
\#主从复制或MGR集群中，server\_id记得要不同  
\#另外，实例启动时会生成 auto.cnf，里面的 server\_uuid 值也要不同  
\#server\_uuid的值还可以自己手动指定，只要符合uuid的格式标准就可以  
server\_id = 3306  
\#basedir = /usr/local/mysql  
\#datadir = /mysqldata/mysqldata3306  
datadir=/var/lib/mysql  
socket = /var/lib/mysql/mysql.sock  
pid\_file = /var/run/mysqld/mysqld.pid  
character\_set\_server = UTF8MB4  
skip\_name\_resolve = 1  
\#若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数  
default\_time\_zone = "+8:00"  
\#启用admin\_port，连接数爆满等紧急情况下给管理员留个后门  
\#admin\_address = '127.0.0.1'  
\#admin\_port = 33062

\#performance setttings  
lock\_wait\_timeout = 3600  
open\_files\_limit = 65535  
table\_open\_cache=12000  
innodb\_open\_files=65535  
back\_log = 1024  
max\_connections = 1000  
max\_connect\_errors = 1000000  
table\_open\_cache = 20000  
table\_definition\_cache = 20000  
thread\_stack = 512K  
sort\_buffer\_size = 16M  
join\_buffer\_size = 16M  
read\_buffer\_size = 8M  
read\_rnd\_buffer\_size = 16M  
bulk\_insert\_buffer\_size = 64M  
thread\_cache\_size = 1500  
interactive\_timeout = 200  
wait\_timeout = 200  
tmp\_table\_size = 96M  
max\_heap\_table\_size = 96M  
explicit\_defaults\_for\_timestamp=true  
sql\_mode=NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION

\#log settings  
log\_timestamps = SYSTEM  
log\_error = /var/lib/mysql/error.log  
log\_error\_verbosity = 3  
slow\_query\_log = 1  
\#log\_slow\_extra = 1  
\#slow\_query\_log\_file = /mysqldata/mysqldata3306/slow.log  
long\_query\_time = 0.1  
log\_queries\_not\_using\_indexes = 1  
log\_throttle\_queries\_not\_using\_indexes = 60  
min\_examined\_row\_limit = 100  
log\_slow\_admin\_statements = 1  
log\_slow\_slave\_statements = 1  
\#log\_bin = /mysqldata/mysqldata3306/mybinlog  
binlog\_format = ROW  
sync\_binlog = 1 \#MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能  
binlog\_cache\_size = 4M  
max\_binlog\_cache\_size = 2G  
max\_binlog\_size = 1G  
binlog\_rows\_query\_log\_events = 1  
\#binlog\_expire\_logs\_seconds = 604800  
\#MySQL 8.0.22前，想启用MGR的话，需要设置binlog\_checksum=NONE才行  
\#binlog\_checksum = CRC32  
\#gtid\_mode = ON  
\#enforce\_gtid\_consistency = TRUE  
\#log-bin=master-bin \# 二进制日志文件名  
\#binlog\_format=row \# 其他格式可能造成数据不一致  
log-slave-updates=ON \# 是否记录从服务器同步数据动作  
gtid\_mode=ON \# 开启GTID功能  
enforce-gtid-consistency=ON \# 开启强制GTID一致性  
master-info-repository=TABLE \# 记录IO线程读取已经读取到的master binlog位置，用于slave宕机后IO线程根据文件中的POS点重新拉取binlog  
relay-log-info-repository=TABLE \# 记录SQL线程读取Master binlog的位置，用于Slave 宕机后根据文件中记录的pos点恢复sql线程  
sync-master-info=1 \# 启用确保无信息丢失；任何一个事务提交后, 将二进制日志的文件名及事件位置记录到文件中  
slave-parallel-workers=2 \# 设置从服务器的复制线程数，0表示关闭多线程复制功能  
binlog-checksum=CRC32 \# 设置binlog校验算法  
master-verify-checksum=1 \# 设置主服务器是否校验  
slave-sql-verify-checksum=1 \# 设置从服务器是否校验  
binlog-rows-query-log\_events=1 \# 用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度  
sync\_binlog=1 \# 保证master crash safe，该参数必须设置为1  
innodb\_flush\_log\_at\_trx\_commit=1 \# 保证master crash safe，该参数必须设置为1

\#myisam settings  
\#key\_buffer\_size = 32M  
\#myisam\_sort\_buffer\_size = 128M

\#replication settings  
relay\_log\_recovery = 1  
\#slave\_parallel\_type = LOGICAL\_CLOCK  
slave\_parallel\_workers = 64 \#可以设置为逻辑CPU数量的2倍  
\#binlog\_transaction\_dependency\_tracking = WRITESET  
\#slave\_preserve\_commit\_order = 1  
\#slave\_checkpoint\_period = 2  
\#replication\_optimize\_for\_static\_plugin\_config = ON  
\#replication\_sender\_observe\_commit\_only = ON

\#mgr settings  
\#loose-plugin\_load\_add = 'mysql\_clone.so'  
\#loose-plugin\_load\_add = 'group\_replication.so'  
\#loose-group\_replication\_group\_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"  
\#MGR本地节点IP:PORT，请自行替换  
\#loose-group\_replication\_local\_address = "172.16.16.10:33061"  
\#MGR集群所有节点IP:PORT，请自行替换  
\#loose-group\_replication\_group\_seeds = "172.16.16.10:33061,172.16.16.11:33061,172.16.16.12:33061"  
\#loose-group\_replication\_start\_on\_boot = OFF  
\#loose-group\_replication\_bootstrap\_group = OFF  
\#loose-group\_replication\_exit\_state\_action = READ\_ONLY  
\#loose-group\_replication\_flow\_control\_mode = "DISABLED"  
\#loose-group\_replication\_single\_primary\_mode = ON  
\#loose-group\_replication\_communication\_max\_message\_size = 10M  
\#loose-group\_replication\_unreachable\_majority\_timeout = 30  
\#loose-group\_replication\_member\_expel\_timeout = 5  
\#loose-group\_replication\_autorejoin\_tries = 288

\#innodb settings  
\#transaction\_isolation = REPEATABLE-READ  
innodb\_buffer\_pool\_size = 40G  
innodb\_buffer\_pool\_instances = 4  
innodb\_data\_file\_path = ibdata1:12M:autoextend  
innodb\_file\_per\_table = 1  
\#innodb\_flush\_log\_at\_trx\_commit = 1 \#MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能  
innodb\_log\_buffer\_size = 32M  
innodb\_log\_file\_size = 1G \#如果线上环境的TPS较高，建议加大至1G以上，如果压力不大可以调小  
innodb\_log\_files\_in\_group = 3  
\#loose-innodb\_redo\_log\_capacity = 3G  
innodb\_max\_undo\_log\_size = 4G  
\# 根据您的服务器IOPS能力适当调整  
\# 一般配普通SSD盘的话，可以调整到 10000 - 20000  
\# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000  
innodb\_io\_capacity = 4000  
innodb\_io\_capacity\_max = 8000  
innodb\_open\_files = 65535  
innodb\_flush\_method = O\_DIRECT  
innodb\_lru\_scan\_depth = 4000  
innodb\_lock\_wait\_timeout = 10  
innodb\_rollback\_on\_timeout = 1  
innodb\_print\_all\_deadlocks = 1  
innodb\_online\_alter\_log\_max\_size = 4G  
\#innodb\_print\_ddl\_logs = 1  
innodb\_status\_file = 1  
\#注意: 开启 innodb\_status\_output & innodb\_status\_output\_locks 后, 可能会导致log\_error文件增长较快  
innodb\_status\_output = 0  
innodb\_status\_output\_locks = 1  
innodb\_sort\_buffer\_size = 67108864  
innodb\_adaptive\_hash\_index = OFF  
\#提高索引统计信息精确度  
innodb\_stats\_persistent\_sample\_pages = 500  
innodb\_adaptive\_hash\_index = 0  
\#loose-sql\_generate\_invisible\_primary\_key = ON

\#innodb monitor settings  
innodb\_monitor\_enable = "module\_innodb"  
innodb\_monitor\_enable = "module\_server"  
innodb\_monitor\_enable = "module\_dml"  
innodb\_monitor\_enable = "module\_ddl"  
innodb\_monitor\_enable = "module\_trx"  
innodb\_monitor\_enable = "module\_os"  
innodb\_monitor\_enable = "module\_purge"  
innodb\_monitor\_enable = "module\_log"  
innodb\_monitor\_enable = "module\_lock"  
innodb\_monitor\_enable = "module\_buffer"  
innodb\_monitor\_enable = "module\_index"  
innodb\_monitor\_enable = "module\_ibuf\_system"  
innodb\_monitor\_enable = "module\_buffer\_page"  
\#innodb\_monitor\_enable = "module\_adaptive\_hash"

\#pfs settings  
performance\_schema = 1  
performance\_schema\_instrument = '%memory%=on'  
\#loose-performance\_schema\_instrument = '%lock%=on'

\[mysqldump\]  
quick