Mysql configuration file my. cnf configuration and configuration parameters, mysqlmy. cnf

Mysql configuration file my. cnf configuration and configuration parameters, mysqlmy. cnf

Mysql configuration file my. cnf

Mysql is installed without the my. cnf file.

1. You can copy the mysql sample configuration file, such as the my-medium.cnf to/etc/my. cnf, and then modify/etc/my. cnf configuration/usr/share/doc/MySQL-server-5.5.38/my-medium.cnf (Example MySQL config file for medium systems with little memory (32 M-64 M ))

You can find the file (my. cnf/my-medium.cnf) or> whereis my. cnf.

Examples of all mysql configuration files are stored in:

# Ll/usr/share/doc/MySQL-server-5.5.38/

2. refer to the following article to modify the my. cnf file (Example MySQL config file for very large systems)

Modify the MySQL time zone:

View: mysql>ShowVariablesLike'% Time_zone % ';

1. You can modify my. cnf
Add under [mysqld]
Default-time-zone = timezone
To modify the time zone. For example:
Default-time-zone = '+ 8:00'
Restart msyql> # service mysql restart after modification
Note that you must add it under [mysqld]. Otherwise, the unknown variable 'default-time-zone = + 8:00 'will appear'

2. online modification through the command line (not tested)

We can see that someone modified it through the command line. After trying it, we found that we could only change the timezone (and only affected by the current client), but the time for investigation was still the original CST time, and we didn't restart mysql either, all are restored. Therefore, this method does not work. You still need to modify the my. cnf configuration file. The following is a test:

1. Set time_zone = timezone
2. For example, Beijing time (GMT + 0800)
3. Set time_zone = '+ 8:00'; as follows:
4. Mysql> set time_zone = '+ 8:00 ';
6. Mysql> show variables like '% time_zone % ';
7. + ------------------ + -------- +
8. | Variable_name | Value |
9. + ------------------ + -------- +
10. | System_time_zone | CST |
11. | Time_zone | + 08:00 |
12. + ------------------ + -------- +

Mysql> flush privileges; # It takes effect immediately. At this time, the mysql time zone has been changed correctly, and the system time zone uses Beijing time. # The timezone can be changed (and only affected by the current client), but the time for investigation is still the original CST time, and mysql cannot be restarted, and all data is recovered. Therefore, this method does not work. You still need to modify the my. cnf configuration file.

Verification:

Mysql> show variables like '% time_zone % ';

Mysql> select CURTIME (); or mysql> select now ();

The following article describes how to configure the parameters of my. cnf for your reference:

The linux mysql compilation and installation and my. cnf configuration file details: http://blog.itpub.net/26690043/viewspace-719495/

There is also an article: "linux mysql configuration file my. cnf details" http://www.cnblogs.com/captain_jack/archive/2010/10/12/1848496.html

Mysql compilation and installation in linux and detailed description of my. cnf configuration file

This article mainly describes how to compile and install the Mysql database on CentOS6.0, and some collected Mysql master configuration files my. cnf. Although the Mysql installation package has been provided on the system, to ensure the unification of the entire system and the optimization of some performance parameters, we need to compile and install Mysql to meet our needs. The official Mysql website provides the Mysql Server Source code package and the compiled binary installation package for download. You can visit the official website to download the required Mysql version. This article uses Mysql 5.1.

Download Mysql
# Wget http://mysql.mirrors.hoobly.com/Downloads/MySQL-5.1/mysql-5.1.57.tar.gz

Compile and install Mysql
# Tar zxvf mysql-5.1.57.tar.gz
# Cd mysql-5.1.57
#./Configure -- prefix =/usr/local/webserver/mysql /\
-- Enable-Cycler \
-- With-extra-charsets = complex \
-- Enable-thread-safe-client \
-- With-big-tables \
-- With-readline \
-- With-ssl \
-- With-embedded-server \
-- Enable-local-infile \
# Make \
# Make install

Next, create the mysql user and related directories, which are customized according to your habits.

# Mkdir-p/data/mysqldata
# Mkdir-p/data/mysqldata/database
# Mkdir-p/data/mysqldata/log
# Mkdir-p/data/mysqldata/pid
# Groupadd mysql
# Useradd-g mysql-d/data/mysqldata/database-s/sbin/nologin mysql
# Chgrp-R mysql/usr/local/webserver/mysql /.
# Chown-R root: mysql/usr/local/webserver/mysql /*
# Chown-R mysql: mysql/data/mysqldata /*

The following are the Mysql master configuration file my. cnf parameters and details:

# Example MySQL config file for very large systems. # The following configuration parameters apply to servers with MB of memory. # You can copy this file to #/etc/my. cnf to set global options, # mysql-data-dir/my. cnf to set server-specific options (in this # installation this directory is/data/mysql/data) or #~ /. My. cnf to set user-specific options. # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "-- help" option. # The following options will be passed to all MySQL clients [client] # password = your_passwordport = 3306 socket =/tmp/mysql. sock # Here follows entries for some specific programs # The MySQL server [Mysqld] user = mysqlport = 3306 socket =/tmp/mysql. sockbasedir =/usr/local/Web server/mysqldatadir =/data/mysqldata/databaselog-error =/data/mysqldata/log/mysql_error.logpid-file =/data/mysqldata/pid/mysql. pid # BDB related options # use this option if your MySQL service is supported by BDB but you are not prepared to use it. this saves memory and may accelerate some things. skip-bdb # Avoid MySQL external locks, reduce the chance of errors, and enhance stability. Skip-locking # forbid MySQL to perform DNS resolution on external connections. This option can eliminate the time for MySQL to perform DNS resolution. However, if this option is enabled, IP addresses are required for all remote host connection authorizations. Otherwise, MySQL cannot process connection requests normally! Skip-name-resolve # It is the default table type when a new table is created. If no table type is executed during creation, this value will be used # mysql supports four storage engines by default: CSV, MRG_MYISAM, MEMORY, and MyISAM. By default, InnoDB Storage engine is not supported (MEMORY consumption is relatively large ). because the memory is small, MyISAM storage engine is recommended. default_table_type = MyISAM # sets the default transaction isolation level, available level: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLEtransaction_isolation = READ-UNCOMMITTED # limits the maximum number of files mysqld can open, value: max_connections * 5 or max_connections + table_cache * 2open_files_limit = 600 # B Ack_log is the number of connections that the operating system can maintain in the listening queue. The queue stores the connections before the MySQL Connection Manager thread processes them. if you have a very high connection rate and an "connection refused" error, you should increase the value here. check your operating system documentation to obtain the maximum value of this variable. if you set back_log to a value higher than your operating system limit, it will be ineffective. Back_log = 40 # keyword buffer size, which is generally used to Buffer Index blocks of the MyISAM table. do not set a value greater than 30% of your available memory, because some of the memory is also used by the OS to buffer row data, even if you do not use the MyISAM table, you also need to set up the 8-64 M memory because it will also be used by the internal temporary disk table. key_buffer_size = 32 M # maximum size of the request packet that the service can process and the maximum request size that the service can process (required when working with a large BLOB field ), the size of each connection. the size dynamically increases the heap size used by the max_allowed_packet = 16 M # thread. This capacity is reserved for each connection. mySQL itself usually does not need more than 64 KB of memory. If you use UDF functions that require a large number of heap operations, or your operating system needs more heap operations, you may need to set it to a higher level. Thread_stack = 192 K # number of tables opened by all threads. Adding this value increases the number of file descriptors required by mysqld. In this case, you need to confirm that the "open-files-limit" variable in [mysqld_safe] allows at least 4096 files to be opened. Table_cache = 60 # allow external file-level locks. Opening a file lock will negatively affect the performance. This option is used only when you run multiple database instances on the same file (note that there are still other constraints !). Or you use other software dependencies on the file layer to lock the MyISAM Table external-locking = FALSE # The sort buffer is used to process sorting caused by order by and group by queues, if the sorted data cannot be placed in the SORT buffer, a disk-based merged category used as an alternative will be used to view the "Sort_merge_passes" status variable. Each thread is allocated when sorting occurs. Sort_buffer_size = 256 K # buffer size used for full table scan in the MyISAM table. When full table scan is required, it is allocated in the corresponding thread. Read_buffer_size = 1 M # This buffer is used to optimize full Union (full JOINs without indexes). Similar Union has very bad performance in most cases. However, setting this value to a large value can reduce the performance impact. Use the "Select_full_join" status variable to view the total number of conjoins. When full Union occurs, join_buffer_size = 256 K is allocated to each thread. # When a row is read from a sorted sequence, row data will be read from this buffer to prevent disk seek. If you increase this value, you can improve the performance of many order by statements. Each thread is allocated when necessary. Read_rnd_buffer_size = 4 M # MyISAM uses a special tree-like cache to make insertion burst (these inserts are, INSERT... SELECT, INSERT... VALUES (...), (...), ..., And load data infile) faster. This variable limits the number of bytes of the buffer tree in each process. Setting 0 will disable this optimization. For optimization, do not set this value to greater than "key_buffer_size ". When burst inserts are detected, the buffer is allocated. Bulk_insert_buffer_size = 2 M # This buffer is allocated when MySQL needs to re-index a REPAIR, OPTIMIZE, ALTER, and load data infile in an empty table. This is allocated in each thread. Therefore, be careful when setting the callback. Myisam_sort_buffer_size = 4 M # maximum temporary file size (when REPAIR, alter table, or load data infile) allowed when MySQL re-creates an index ). If the file size is greater than this value, the index will be created through the key-value buffer (more slow) myisam_max_sort_file_size = 10G # If the temporary file used for faster index creation index is greater than the specified value, use the key-value buffer method. This is mainly used to force the long string key in a large table to use the slow key-value buffering method to create an index. Myisam_max_extra_sort_file_size = 10G # If a table has more than one index, MyISAM can use more than one thread to fix them through parallel sorting. This is a good choice for users with multiple CPUs and a large amount of memory. Myisam_repair_threads = 1 # automatically check and fix the MyISAM table that is not properly closed. myisam_recoverthread_cache = 128 # Number of threads we reserve in the cache for reuse. When a client is disconnected, if the thread in the cache is smaller than thread_cache_size. The client thread is put into the cache. This can greatly reduce the overhead of thread creation when you need a large number of new connections. (If you have a good thread model, this does not significantly improve the performance .) thread_cache_size = 10 # The Query Buffer is often used to buffer SELECT results and directly return results in the next same query. Enabling the Query Buffer can greatly improve the server speed. If you have a large number of identical queries and rarely modify tables. Check the "Qcache_lowmem_prunes" status variable to check whether the current value is high enough for your load. Note: When your table changes frequently or if your original query is different each time. Query buffering may cause performance degradation rather than performance improvement. Query_cache_size = 0 M # The result is buffered only when the value is smaller than this value. This setting is used to protect the Query Buffer and prevent a large result set from overwriting all other query results. query_cache_limit = 2Mquery_cache_min_res_unit = 4 K # maximum size of a temporary table in the internal memory. If a table grows to a greater value than this value, it is automatically converted to a disk-based table. This limit applies to a single table, rather than the maximum capacity allowed by the independent memory table. tmp_table_size = 512 K. This option is used to prevent accidental creation of an ultra-large memory table from permanently consuming all memory resources. max_heap_table_size = 32 M # All queries that use more than this time (in seconds) are considered as slow queries. Do not use "1" here. Otherwise, all queries or even very fast query pages will be recorded (because the current time of MySQL can only be accurate to the second level ). Ong_query_time = 1 # record more information in slow logs. Generally, it is best to enable this option. When this option is enabled, queries that do not use indexes are recorded as slow queries appended to slow logs. log_long_format # the maximum number of sessions allowed by the MySQL service at the same time, one of the connections will be retained as Administrator Logon by SUPER permission. Even if the maximum number of connections has been reached. max_connections = 200 # specifies the maximum connection time of a request. Wait_timeout = 30 # maximum number of errors allowed for each client connection. If this limit is reached. This client will be blocked by the MySQL service until "flush hosts" is executed or the service is restarted. This value will be added for invalid passwords and other connection errors. View the status of "Aborted_connects" to obtain the Global Counter. max_connect_errors = 200expire_logs_days = 7 # Try number of CPU's * 2 for thread_concurrency # This allows the application to give the thread system a prompt to give the desired number of threads at the same time. This value is only valid for systems that support the thread_concurrency () function (for example, Sun Solaris ). You can try to use [number of CPUs] * (2 .. 4) as thread_concurrency value thread_concurrency = 8 # Don't listen on a TCP/IP port at all. this can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) wil L render mysqld useless! # Skip-networking # Federatedskip-federated is disabled by default # Replication Master Server (default) # binary logging is required for replication # log-bin = mysql-bin # In a transaction, binlog records the cache size of the SQL status, for multi-statement transactions, you can add this value to obtain greater performance. All the statuses from the transaction will be buffered in the binlog buffer and then written to the binlog at one time after the commit. If the transaction is larger than this value, it will be replaced by a temporary file on the disk. This buffer is created during the first update of each connection transaction status. binlog_cache_size = 2Mmax_binlog_cache_size = 4Mmax_binlog_size = 512 M # the unique service identification number is located between 1 and 2 ^ 32-1. This value must be set on both master and slave. If "master-host" is not set, the default value is 1. However, if this option is ignored, MySQL will not take effect as the master. server-id = 1 # Replication Slave (comment out master section to use this) # To configure this host as a replication slave, you can choose between # two methods: #1) use the change master to command (fully described in our manual)-# the syntax is: # change master to MASTER_HOST = 
Modify the parameter value as needed and save it as the/etc/my. cnf file.
Initialize the mysql database
#/Usr/local/webserver/mysql/bin/mysql_install_db \
-- Basedir =/usr/local/webserver/mysql \
-- Datadir =/data/mysqldata/database \
-- User = mysql
Finally, start and close the mysql server.
#/Usr/local/webserver/mysql/bin/mysqld_safe -- user = mysql & // start the service
#/Usr/local/webserver/mysql/bin/mysqladmin-u root-p shutdown // stop the service