search
HomeDatabaseMysql Tutorialmysql配置文件例子_MySQL

bitsCN.com

mysql配置文件例子

 

[mysqld] 

port        = 3306

socket        = /tmp/mysql.sock

# 设置mysql的安装目录 

basedir=D://mysql-5.6.14-win32

# 设置mysql数据库的数据的存放目录,必须是data

datadir=D://mysql-5.6.14-win32//data

#innodb_log_arch_dir 默认datadir

#innodb_log_group_home_dir  默认datadir

# 设置mysql服务器的字符集,默认编码

default-character-set=utf8

 

#连接数的操作系统监听队列数量,如果经常出现“拒绝连接”错误可适当增加此值

back_log = 50

#不使用接听TCP / IP端口方法,mysqld通过命名管道连接

#skip-networking

# 最大连接数量

max_connections = 100

#打开表的线程数量限定,最大4096,除非用mysqld_safe打开限制

table_open_cache = 2048

#MySql 服务接收针对每个进程最大查询包大小

max_allowed_packet = 16M

#作用于SQL查询单笔处理使用的内存缓存,如果一笔操作的二进制数据超过了限定大小,将会在磁盘上开辟空间处理,一般设为 1-2M即可,默认1M

binlog_cache_size = 2M

#单个内存表的最大值限定

max_heap_table_size = 64M

#为每个线程分配的排序缓冲大小

sort_buffer_size = 8M

#join 连表操作的缓冲大小,根据实际业务来设置,默认8M

join_buffer_size = 32M

#操作多少个离开连接的线程的缓存

thread_cache_size = 8

#并发线程数量,默认为8,可适当增加到2倍以内。如果有多个CPU可以乘 上CPU的数量。双核CPU可以乘 上当前最核数再乘 上70%-85%

thread_concurrency = 16

#专用于具体SQL的缓存,如果提交的查询与几次中的某查询相同,并且在query缓存中存在,则直接返回缓存中的结果。

query_cache_size = 64M

#对应上一条设置,当查询的结果超过下面设置的大小时,将不会趣入到上面设置的缓存区中,避免了一个大的结果占据大量缓存。

query_cache_limit = 2M

#设置加全文检索中的最小单词长度。

#ft_min_word_len = 4

#CREATE TABLE 语句的默认表类型,如果不自己指定类型,则使用下行的类型

default-storage-engine = InnoDB

#线程堆栈大小,mysql说它自己用的堆栈大小不超过64K。这个值可适当设高一点(在我的项目中都是共用同一个数据库连接的),默认192K

thread_stack = 800K

#设置事务处理的级别,默认 REPEATABLE-READ,一般用它就即可,以下二行按顺序对应,

#可读写未提交的数据,创建未提交的数据副本读写,未提交之前可读不可写,只允许串行序列招行事务。

# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

transaction_isolation = REPEATABLE-READ

#单一内存临时表在内存中的大小,超过此值自动转换到磁盘操作

tmp_table_size = 64M

#启动二进制日志功能,可通过它实现时间点恢复最新的备份

#log-bin=mysql-bin

#二进制日志格式,对就上一条,-建议混合格式

#binlog_format=mixed

#转换查询为缓慢查询

slow_query_log

#对应上一条,如果一个查询超过了下条设定的时间则执行上一条。

long_query_time = 2

#自定义主机ID识别符,用于主从或多服务器之间识别,为 一个 int 类型

server-id = 1

#一般用来缓存MyISAM表的主键,也用于临时的磁盘表缓存主键,上面多次出现临时磁盘表,所以就算不用MyISAM也最好为其设置一个不小的值,默认32M

key_buffer_size = 64M

#全表扫描MyISAM表时的缓存,每个线程拥有下行的大小。

read_buffer_size = 2M

#排序操作时与磁盘之间的缓存,分到每个线程,默认16M

read_rnd_buffer_size = 24M

#MyISAM使用特殊树形进行批量插入时的缓存,如insert ... values(..)(..)(..)

bulk_insert_buffer_size = 64M

#MyISAM索引文件的最大限定,

myisam_max_sort_file_size = 10G

#如果一个myisam表有一个以上的索引, MyISAM可以使用一个以上线程来排序并行它们。较耗硬件资源,如果你的环境不错,可以增加此值。

myisam_repair_threads = 2

#自动检查和修复无法正确关闭MyISAM表。

myisam_recover

# *** INNODB Specific options ***

#开启下条将会禁用 INNODB

#skip-innodb

#一般不用设置或者说设了也没多大用,InnoDB会自己与操作系统交互管理其附加内存池所使用InnoDB的存储数据的大小

innodb_additional_mem_pool_size = 16M

#innodb整体缓冲池大小,不宜过大,设为本地内存的 50%-75% 比较合适,在本机开发过程中可以设得较小一点如 200M

innodb_buffer_pool_size = 200M

#InnoDB的数据存储在一个或多个数据文件组成的表空间

innodb_data_file_path = ibdata1:10M:autoextend

#用于异步IO操作的线程数量,默认为 4 ,可适当提高

innodb_file_io_threads = 8

#线程数内允许的InnoDB内核,不宜太高

innodb_thread_concurrency = 16

#InnoDB的事务日志快存行为,默认为 1,为0可减轻磁盘I/0操作,还有以为2

innodb_flush_log_at_trx_commit = 1

#InnoDB的用于的缓冲日志数据的大小

innodb_log_buffer_size = 8M

#日志文件,可设置为25%-90%的总体缓存大小,默认 256M. 修改此项要先删除datadir/ib_logfileXXX 

innodb_log_file_size = 256M

#日志组数量,默认为3

innodb_log_files_in_group = 3

#InnoDB的日志文件位置。默认是MySQL的datadir

#innodb_log_group_home_dir

#InnoDB最大允许的脏页缓冲池的百分比,默认90

innodb_max_dirty_pages_pct = 80

#事务死锁超时设定

innodb_lock_wait_timeout = 120

 

[client]

port        = 3306

socket        = /tmp/mysql.sock

# 设置mysql客户端的字符集

default-character-set=utf8

 

[mysqldump]

quick

max_allowed_packet = 16M

 

[mysql]

no-auto-rehash

 

# Only allow UPDATEs and DELETEs that use keys.

#safe-updates

 

[WinMySQLAdmin] 

# 指定mysql服务启动启动的文件

Server=D://mysql-5.6.14-win32//bin//mysqld.exe 

bitsCN.com
Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationHow to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLAn efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesSteps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool