@[TOC]

What is my.ini?

Ini is the configuration file used in the MySQL database. You can modify this file to update the configuration.

Where is my.ini?

My.ini is stored in the root directory of MySql installation, as shown in the following figure :(I am lazy, use WAMP, please find your own installation directory)

The configuration of my.ini is as follows:

In fact, it can be roughly divided into two parts: client parameters and server parameters. The server parameters also include InnoDB storage engine parameters.

Client parameters

[mysql] [client] [mysql] [client] [mysql] [mysql]

  1. The default port is 3306. If you need to change the port number, you can change it here.

  2. The default-character-set parameter is the client’s default character set. If you want it to support Chinese, you can set it to GBK or UTF8.

  3. There is also a password parameter, where the value of the password parameter can be set to login without entering a password

# CLIENT SECTION # ---------------------------------------------------------------------- # # The following options will  be read by MySQL client applications. # Note that only client applications shipped by MySQL are guaranteed# to read this section. If you want your own MySQL client program to
# honor these values, you need to specify it as an option during the
# MySQL client library initialization.
#
[client]

port=3306

[mysql]

default-character-set=gb2312
Copy the code

Server outage parameters:

The following is the introduction of parameters:

  1. The port parameter also represents the port of the database.

  2. The basedir parameter indicates the installation path of MySQL.

  3. The datadir parameter indicates the location where the MySQL data files are stored, as well as the location where the database tables are stored.

  4. The default-character-set parameter represents the default character set, which is server-side.

  5. Default-storage-engine Specifies the default storage engine.

There are two engine MyISAM and InnoDB, with what you need, details you can refer to the following blog post: yangyongli.blog.csdn.net/article/det…

  1. The sql-mode parameter indicates the parameter of the SQL mode. You can use this parameter to check the severity of the SQL statement.

  2. The max_connections parameter indicates the maximum number of simultaneous connections allowed to access the MySQL server, one of which is reserved for the administrator’s exclusive use.

  3. The query_cache_size parameter indicates the size of the cache for a query. The cache can store information previously queried using the SELECT statement, and the information can be directly removed from the cache for a second query.

  4. The table_cache parameter represents the total number of open tables for all processes.

  5. The tmp_table_size parameter represents the total number of temporary tables in memory.

  6. The thread_cache_size parameter preserves the cache of the client thread.

  7. The myisam_max_sort_file_size parameter indicates the maximum temporary file size allowed by MySQL for index reconstruction.

  8. The myisam_sort_BUFFer_SIZE parameter indicates the cache size when the index is rebuilt.

  9. The key_buffer_SIZE parameter represents the cache size of the keyword.

  10. The read_buffer_SIZE parameter represents the cache size for full table scans of MyISAM tables.

  11. The read_rnd_BUFFer_SIZE parameter indicates that sorted data is stored in this cache.

  12. The sort_buffer_SIZE parameter indicates the cache size used for sorting

# SERVER SECTION # ---------------------------------------------------------------------- # # The following options will  be read by the MySQL Server. Make sure that# you have installed the server correctly (see above) so it reads this
# file.
#
[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306


#Path to installation directory. All paths are usually resolved relative to this.
basedir="E:/Java/Mysql/"

#Path to the database root
datadir="C: / ProgramData/MySQL/MySQL Server 5.5 / Data/"

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=gb2312

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=100

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size=0

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_cache=256

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=35M


# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before. This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance # improvement if you have a good thread implementation.) thread_cache_size=8 #*** MyISAM Specific options # The maximum size of the temporary file MySQL is allowed to use while # recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE. # If the file-size would be bigger than this, the index will be created # through the key cache (which is slower). myisam_max_sort_file_size=100G # If the temporary file used for fast index creation would be bigger # than using the key cache by the amount specified here, then prefer the # key cache method. This is mainly used to force long character keys in # large tables to use the slower  key cache method to create the index. myisam_sort_buffer_size=69M # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8- 64.M as it will also be
# used for internal temporary disk tables.
key_buffer_size=55M

# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_buffer_size=64K
read_rnd_buffer_size=256K

# This buffer is allocated when MySQL needs to rebuild the index in
# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE
# into an empty table. It is allocated per thread so be careful with
# large settings.
sort_buffer_size=256K
Copy the code

InnoDB storage engine uses:

The following is a brief description of the parameters:

  1. The innodb_additional_mem_pool_size parameter represents the additional memory pool used to store the contents of the InnoDB table.

  2. The innodb_flush_LOG_at_trx_COMMIT parameter sets the commit time. If set to 1, InnoDB writes transaction logs to disk after each commit.

  3. The innodb_log_BUFFer_size parameter indicates the size of the cache used to store log data.

  4. The innodb_buffer_pool_size parameter indicates the size of the cache. InnoDB uses a buffer pool class to hold indexes and raw data.

  5. The innodb_log_file_size parameter indicates the size of the log file.

  6. The innodb_thread_concurrency parameter indicates the maximum number of threads allowed in the InnoDB storage engine.

#*** INNODB Specific options ***


# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
#skip-innodb

# Additional memory pool that is used by InnoDB to store metadata
# information. If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS. As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size=3M

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=1

# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size=2M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system. Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=107M

# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size=54M

# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency=18
Copy the code

The Chinese translation is my.ini

[client]  
port=3306  

[mysql]  
default-character-set=gbk  

[mysqld]
port = 3306
socket = /tmp/mysql.sock
Set mysql installation directory
basedir=F:\\Hzq Soft\\MySql Server 51GA
Mysql > select * from 'data'; mysql > select * from 'data'
datadir=F:\\Hzq Soft\\MySql Server 51GA\\data
# innodb_log_arch_dir datadir by default
# innodb_log_group_home_dir datadir by default
Mysql server character set, default encoding
default-character-set=utf8

# connection count Specifies the number of queues that the operating system listens to, which can be appropriately increased if "deny connections" errors occur frequently
back_log = 50
Mysqld connects via named pipes instead of using the TCP/IP port method
#skip-networking
# Maximum number of connections
max_connections = 90
# limit on the number of threads to open the table, Max 4096, unless mysqLD_safe is used to open the limit
table_open_cache = 2048
MySql service receives maximum query package size for each process
max_allowed_packet = 16M
If the size of binary data in an operation exceeds the limit, space will be allocated on disk for processing. The default value is 1M
binlog_cache_size = 2M
The maximum value of a single memory table
max_heap_table_size = 64M
Size of the sort buffer allocated for each thread
sort_buffer_size = 8M
#join Specifies the buffer size for table operations. The default value is 8M
join_buffer_size = 32M
# cache the number of threads leaving the connection
thread_cache_size = 8
The number of concurrent threads, which defaults to 8, can be increased to less than 2 times. If you have multiple cpus you can multiply by the number of cpus. Dual-core cpus can be multiplied by the current maximum number of cores and then multiplied by 70%-85%
thread_concurrency = 16
If the submitted query is the same as one of the several queries and exists in the query cache, the result in the cache is returned directly.
query_cache_size = 64M
If the size of the query exceeds the size set below, the result will not be entered into the cache set above, avoiding a large result occupying a large cache.
query_cache_limit = 2M
Set the minimum word length in full text search.
#ft_min_word_len = 4
#CREATE the default TABLE type for the TABLE statement. If you do not specify the type yourself, use the downstream type
default-storage-engine = InnoDB
Mysql says it uses a stack size of no more than 64K. This value can be set a little higher (RCA projects all share the same database connection) and defaults to 192K
thread_stack = 800K
REPEATABLE-READ: set transaction level to REPEATABLE-READ
Uncommitted data can be read and written before committing. Only serial sequences are allowed to transact.
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = REPEATABLE-READ
The size of a single memory temporary table in memory, exceeding this value is automatically converted to disk operations
tmp_table_size = 64M
Enable binary logging, which can be used to restore the latest backup in time
#log-bin=mysql-bin
Binary log format, for the last one, - recommended mixed format
#binlog_format=mixed
Convert queries to slow queries
slow_query_log
If a query exceeds the time set for the next query, the previous query will be executed.
long_query_time = 2
# Custom host ID identifier, used between primary and secondary or multiple servers, is an int
server-id = 1
Select * from MyISAM; select * from MyISAM; select * from MyISAM; select * from MyISAM
key_buffer_size = 56M
# cache for full table scan of MyISAM table, each thread has downstream size.
read_buffer_size = 2M
Cache between disk and sort operation, allocated to each thread, default 16M
read_rnd_buffer_size = 16M
#MyISAM cache when using a special tree for bulk inserts, such as insert... values(..) (..) (..)
bulk_insert_buffer_size = 64M
#MyISAM index file maximum limit,
myisam_max_sort_file_size = 12G
# If a MyISAM table has more than one index, MyISAM can use more than one thread to sort and parallel them. Expensive hardware, increase this value if your environment is good.
myisam_repair_threads = 2
# Autocheck and fix that MyISAM table cannot be closed correctly.
myisam_recover
# *** INNODB Specific options ***
# Enable next bar will disable INNODB
#skip-innodb
InnoDB interacts with the operating system to manage the size of the data InnoDB uses for its additional memory pool
innodb_additional_mem_pool_size = 16M
Innodb buffer pool size, not too large, set to 50%-75% of the local memory is appropriate, in the native development process can be set to smaller such as 64M,256M
innodb_buffer_pool_size = 256M
#InnoDB data is stored in a tablespace consisting of one or more data files
innodb_data_file_path = ibdata1:10M:autoextend
The number of threads used for asynchronous IO operations, which defaults to 4, can be increased appropriately
innodb_file_io_threads = 8
# The number of threads allowed by InnoDB kernel should not be too high
innodb_thread_concurrency = 14
InnoDB transaction log cache behavior, default is 1, 0 to reduce disk I/0 operation, there are 2
innodb_flush_log_at_trx_commit = 1
The size of InnoDB's buffer log data
innodb_log_buffer_size = 16M
# Log files, can be set to 25%-90% of the total cache size, default 256M. To modify this item, delete datadir\ib_logfileXXX first
innodb_log_file_size = 256M
# Number of log groups, default is 3
innodb_log_files_in_group = 3
#InnoDB log file location. The default is the MySQL datadir
#innodb_log_group_home_dir
#InnoDB maximum allowable percentage of dirty page buffer pools, default 90
innodb_max_dirty_pages_pct = 90
Transaction deadlock timeout setting
innodb_lock_wait_timeout = 120

[client]
port = 3306
socket = /tmp/mysql.sock
Mysql client character set
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 service startup file
Server=F:\\myweb\\MySql Server\\bin\\mysqld.exe
Copy the code