> For the complete documentation index, see [llms.txt](https://close.gitbook.io/yun-wei-bi-ji/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://close.gitbook.io/yun-wei-bi-ji/centos/mysql/mysql-pei-zhi-wen-jian-chang-yong-pei-zhi.md).

# MYSQL 配置文件常用配置

### mysql 配置文件常用配置

```ini
[client]
# 端口
port        = 3306

# sock 位置
socket      = /tmp/mysql.sock



[mysqld]

# 等待超时时间
wait_timeout = 10

# 每个连接数, 二进制日志缓存大小(4096的倍数)
binlog_cache_size = 192K

# 每个连接数, 每个线程的堆栈大小
thread_stack = 384K

# 关联表缓存大小
join_buffer_size = 4096K
query_cache_type = 1
max_heap_table_size = 1024M


# 端口
port        = 3306

# sock 位置
socket      = /tmp/mysql.sock

# 数据存放目录
datadir = /var/lib/mysql
skip-external-locking
performance_schema_max_table_instances=400
table_definition_cache=400


# 用于索引的缓冲区大小，常设置: 内存 *2
key_buffer_size = 512M


# 介绍到的最大数据
max_allowed_packet = 100G


# 表缓存(最大不要超过2048)
table_open_cache = 1024


# 每个连接数, 每个线程排序的缓冲大小
sort_buffer_size = 2048K


# 每个连接数，读入缓冲区大小
read_buffer_size = 2048K

# 每个链接数，随机读取缓冲区大小
read_rnd_buffer_size = 1024K



# 线程池大小。 常设置: 内存 * 64
thread_cache_size = 192


# 查询缓存, 不开启请设为0;常设置: 内存 * 64
query_cache_size = 256M


# 临时表缓存大小; 常设置: 内存 * 64
tmp_table_size = 1024M


# SQL 模块
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


# 最大链接数
max_connections = 400


# 最大错误链接数
max_connect_errors = 100


# 打开文件限制数量
open_files_limit = 65535



# 设置存储默认引擎
default_storage_engine = InnoDB
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql

# 用于在数据库崩溃或损坏时尝试恢复数据，1~6对应级别
#innodb_force_recovery=1

# Innodb 缓冲区大小, 常设置: 内存 * 128
innodb_buffer_pool_size = 1024M


# Innodb 日志文件大小
innodb_log_file_size = 256M


#  Innodb 日志缓冲区大小, 常设置: 内存 * 64
innodb_log_buffer_size = 64M


# Innodb 锁等待超时时间
innodb_lock_wait_timeout = 500


innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_flush_log_at_trx_commit = 1




# 开启 binlog 二进制
log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3


# 设置只读模式
#read_only = 1
#super_read_only = 1
```

## mysql innodb\_force\_recovery的各个恢复级别的参数说明

恢复级别参数说明：

* 1(SRV\_FORCE\_IGNORE\_CORRUPT):忽略检查到的corrupt页。
* 2(SRV\_FORCE\_NO\_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
* 3(SRV\_FORCE\_NO\_TRX\_UNDO):不执行事务回滚操作。
* 4(SRV\_FORCE\_NO\_IBUF\_MERGE):不执行插入缓冲的合并操作。
* 5(SRV\_FORCE\_NO\_UNDO\_LOG\_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
* 6(SRV\_FORCE\_NO\_LOG\_REDO):不执行前滚的操作。


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://close.gitbook.io/yun-wei-bi-ji/centos/mysql/mysql-pei-zhi-wen-jian-chang-yong-pei-zhi.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.