MySQL configuration file my.cnf settings

Transfer from http://www.blogjava.net/baoyaer/articles/209466.html

Setup recommendations:

For a single running Web server, it is recommended to add:

Skip-locking

Skip-name-resolve

Skip-networking

Use "LOCALHOST" when you link the database to PHP. This way the MySQL client library overwrites it and attempts to connect to the local socket. (

We can get it from php.ini.

Code:

; Default socket name for local MySQL connects. If empty, uses the built-in

; MySQL defaults.

Mysql.default_socket =/tmp/mysql.sock See UNIX will access/tmp/mysql.sock by default)

The following are some of the options explained:

MY.CNF does not exist by default. You can see it under/usr/local/share/mysql/:

My-huge.cnf

My-innodb-heavy-4g.cnf

My-large.cnf

My-medium.cnf

My-small.cnf

and other documents. Copy the files that fit your machine configuration to the/ETC/MY.CNF or MySQL data directory/my.cnf (/var/db/mysql) or ~/.my.cnf. File with detailed instructions

[Mysqld]

Port = 3306

ServerID = 1

Socket =/tmp/mysql.sock

Skip-locking

# avoid MySQL external lock, reduce the chance of error increase stability.

Skip-name-resolve

Disable MySQL for DNS resolution of external connections, and Use this option to eliminate the time for DNS resolution for MySQL. However, it is important to note that if this option is turned on, all remote host connection authorizations will use IP address mode, otherwise MySQL will not be able to handle the connection request properly!

Back_log = 384

Specifies the number of possible connections for MySQL. When the MySQL main thread receives very many connection requests in a very short time, this parameter takes effect and the main thread takes a short time to check the connection and start a new thread.

The value of the Back_log parameter indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops responding to a new request. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. Different operating systems have its own limitations on this queue size.

Attempting to set a limit of back_log above your operating system will be invalid. The default value is 50. For Linux systems, the recommended setting is an integer less than 512.

Key_buffer_size = 256M

# KEY_BUFFER_SIZE Specifies the buffer size to use for the index, increasing it to get better index processing performance.

The parameter can be set to 256M or 384M for a server that has around 4GB.

Note: This parameter value setting is too large to be the overall efficiency of the server down!

Max_allowed_packet = 4M

Thread_stack = 256K

Table_cache = 128K

Sort_buffer_size = 6M

The size of the buffer that can be used when the query is sorted. Note: This parameter corresponds to the allocation of memory per connection exclusive! If there are 100 connections, the actual allocated total sort buffer size is 100x6 = 600MB. Therefore, the recommended setting for a server that has around 4GB is 6-8m.

Read_buffer_size = 4M

The size of the buffer that can be used by the read query operation. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!

Join_buffer_size = 8M

The size of the buffer that the Federated query operation can use, like sort_buffer_size, which allocates memory for each connection alone!

Myisam_sort_buffer_size = 64M

Table_cache = 512

Thread_cache_size = 64

Query_cache_size = 64M

Specifies the size of the MySQL query buffer. You can observe this by executing the following commands in the MySQL console:

Code:

# > SHOW VARIABLES like '%query_cache% ';

# > Show status Like ' qcache% '; if the value of Qcache_lowmem_prunes is very large, it indicates that buffering is often insufficient;

If the value of qcache_hits is very large, it indicates that the query buffer is used very frequently, and if the value is smaller it will affect efficiency, then you can consider not querying the buffer; Qcache_free_blocks, if the value is very large, it indicates that there is a lot of fragmentation in the buffer.

Tmp_table_size = 256M

Max_connections = 768

Specifies the maximum number of connection processes allowed by MySQL. If the too many connections error is frequently encountered when accessing the forum, you need to increase the parameter value.

Max_connect_errors = 10000000

Wait_timeout = 10

Specifies the maximum connection time for a request, which can be set to 5-10 for a server with about 4GB of memory.

Thread_concurrency = 8

The parameter value is the number of server logical CPUs x2, in this case, the server has 2 physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4x2 = 8

Skip-networking

Turn on this option to completely turn off MySQL's TCP/IP connection, and do not turn on this option if the Web server is accessing the MySQL database server remotely. Otherwise it will not connect properly!

First, about MYSQL5
MYSQL5 SeriesThe database is the latest version of MySQL database, the more popular distribution is mysql-5.0.18. MySQL English official website is http://www.mysql.com

Ii. acquisition of MySQL5
All the programs about MySQL database can be in its English official websiteDownload to, however, in view of not everyone's English is very good, so we recommend that you try to download the Chinese official website of the MySQL program you need, if not found, then go to the English website.
MySQL5 hasInstallation version andFree installation version of the point, as the name implies, the installation version is required to be installed before you can use, free version of MySQL can be used after downloading, or simple settings can be used.
MYSQL5 installation Version:
Windows version: http://download.mysql.cn/src/2006/0218/199.html
Linux version (source bundle): http://download.mysql.cn/src/2006/0208/62.html
MySQL5 Free installation version:
Windows version: http://download.mysql.cn/src/2006/0302/205.html

Third, MySQL5 installation
Installation manual for Windows edition: http://bbs.mysql.cn/thread-261-1-1.html
Linux version (source bundle) Installation manual: Http://bbs.mysql.cn/thread-493-1-2.html

MySQL has the installation version and the free installation version, the free installation version of the Unpacked folder does not have the installation program, can be used directly.
User manual for Windows Free Edition: http://bbs.mysql.cn/thread-552-1-1.html

Iv. Backup and Recovery
General backup command is mysqldump, here is a TM database for example, do a brief introduction, detailed information reference
Http://info.mysql.cn/install/2006/0410/5521.html
Backup:
#mysqldump-U root-p TM > Tm_20060101.sql
Follow the prompts to enter the password, this will be the TM database all the table structure and data back to Tm_20060101.sql, because the total backup work, if the data volume conference occupies a lot of space,
This is the data that can be compressed using gzip, with the following command:
#mysqldump-U root-p TM | gzip > tm_20060101.sql.gz
You can also back up toRemote machines, made with-H, such as
#mysqldump-U root-p TM > Tm_20060101.sql-h xxx.xxx.xxx.xxx
You can back up directly to a remote computer with an IP address of xxx.xxx.xxx.xxx.
When the system crashes, rebuilds the system, or recovers the database, you can recover the data like this:
#mysql-U root-p TM < Tm_20060101.sql
To restore directly from a compressed file:
#gunzip < tm_20060101.sql.gz | Mysql-u Root-p TM

Five, FAQ

Q: Why is there no installation file in "Installer"?
A: You may be downloading a free version of the installation. Please give detailed version information.

Q: How do I start and close MySQL?
A:linux: For example, my MySQL is installed in the source mode/usr/local/mysql
Auto: Copy/usr/local/mysql/share/mysql/mysql.server to/etc/rc.d/init.d/, then
Chkconfig--add Mysql.server can start the MySQL service on the boot.
Manual: Execute/usr/local/mysql/bin/mysqld_safe as root--user=mysql
Under Windows:
Automatic:
In cmd mode, under the Bin folder of the MySQL installation path, execute: mysqld-nt--install
Manual: Execute net start MySQL directly to the bin folder of the MySQL installation path.
If you do not want MySQL to start the service when the computer starts, do: Mysqld-nt--remove
You can also delete the corresponding service and restart the computer in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services.
Close Mysql:mysqladmin-uroot-p shutdown
Start MySQL:
Mysqld-nt--install
net start MySQL

Q: How to modify MySQL'sUser Password?
A: Here please distinguish the user password and the user password of the system connected to MySQL database!!

Mysql-uroot-p
Enter password
#登录mysql
>use MySQL
>update User Set Password=password ("New_pass") where user= "UserName"; #userName换成你要修改的用户名的密码, like Root.
>flush privileges;
>exit;

Q: How do I log in to MySQL?
A:mysql-uroot-p Enter the password after the return, and then return. If you do not modify it, the default password is blank.

How does Q:mysql become a graphical database? Does it have its own graphical tools?
A:mysql a client with a character, but there are a lot of good graphics like Mysql_center, SQLyog, PhpMyAdmin, Mysql Query browser,mysql Administrator, MYSQLCCManagement tools.
Mysql_centerd on this site: http://bbs.mysql.cn/thread-517-1-1.html
SQLyog go online Search, a lot of, SQLyog407 version is good.
Phpmyadmin:http://download.mysql.cn/opencode/2006/0207/55.html
Management provided by mysql.comSoftware: http://dev.mysql.com/downloads/gui-tools/5.0.html
Other I do not provide one by one to search the Internet to download.

Q: Why do I see the following prompt when logging in to MySQL server with mysql-uusername-p:
Host ' xxx.xxx.xxx.xxx ' isn't allowed to connect to this MySQL server! ("xxx.xxx.xxx.xxx" stands for the loginServer
A: This is because the MySQL server you are logging into does not allow the user to log in username from xxx.xxx.xxx.xxx this IP address.
The workaround is to log in to MySQL on the server with the following steps:
Mysql-uroot-p
(Enter password)
Use MySQL
Update user set host= "%" where user= "username";
Flush privileges;

Q: What is phpMyAdmin?
A:phpmyadmin is aPHP is written to control and operate MySQL over the Internet. With phpMyAdmin, you can fully manipulate the database, such as setting up, copying/deleting data, and so on.
With phpMyAdmin, you can manage all MySQL data and databases without using the MySQL command at all, using phpMyAdmin directly.

Q: How do I use phpMyAdmin?
A: To use phpMyAdmin, after downloading, release it to the Web server root directory, take a name, for example, called phpMyAdmin, and then enter in the browser's address bar:
http://X.X.X.X/phpMyAdmin/index.php
You can do it.
If it is phpmyadmin 2.8.0.2 or above, such use will be error, because the default does not require the software configuration file config.default.php, in the 2.6.X version of the file, but the password is not correct. For the 2.6.X version, you can open this file directly with a text editor and change the contents of the first $cfg to your username and password:
$cfg [' Servers '] [$i] [' auth_type '] = ' config '; authentication method (config, HTTP or cookie based)?
$cfg [' Servers '] [$i] [' user '] = ' root '; MySQL User
$cfg [' Servers '] [$i] [' password '] = ' own password ';
If you do not set a password, you will be prompted:
Access deniedFor user ' root ' @ ' localhost ' (using Password:no)

If the version is 2.8.0.2, the program prompts you to run a file such as scripts/setup.php or index.php to configure it to generate config.inc.php files.

Q: Why do I use MySQL to appearGarbled?
A: The default character set when installing MYSQL5 is the Swedish code latin1, which does not support Chinese. And there are many reasons for garbled, suggest to the bbs.mysql.cn forum to find the answer. Please describe your environment and install it in detail.Process and use the information so that everyone can quickly analyze your problem.

Q: How do I modify the character set?
A:
Mysql-uroot-p
Enter password
Select your target database
Mysql> Use dbname
Show current Character Set
Mysql> Show variables like '%char% ';
+--------------------------+----------------------------------------+
| variable_name | Value |
+--------------------------+----------------------------------------+
| character_set_client | gb2312 |
| character_set_connection | gb2312 |
| Character_set_database | gb2312 |
| Character_set_results | gb2312 |
| Character_set_server | gb2312 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/local/mysql/share/mysql/charsets/|
+--------------------------+----------------------------------------+
7 Rows in Set (0.00 sec)
It is best to keep the character set uniform. If you want to modify which character set, use the SET command, such as:
Mysql> set character_set_client GBK;
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like '%char% ';
+--------------------------+----------------------------------------+
| variable_name | Value |
+--------------------------+----------------------------------------+
| character_set_client | GBK |
| character_set_connection | gb2312 |
| Character_set_database | gb2312 |
| Character_set_results | gb2312 |
| Character_set_server | gb2312 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/local/mysql/share/mysql/charsets/|
+--------------------------+----------------------------------------+
and change it back:
Mysql> set character_set_client=gb2312;
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like '%char% ';
+--------------------------+----------------------------------------+
| variable_name | Value |
+--------------------------+----------------------------------------+
| character_set_client | gb2312 |
| character_set_connection | gb2312 |
| Character_set_database | gb2312 |
| Character_set_results | gb2312 |
| Character_set_server | gb2312 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/local/mysql/share/mysql/charsets/|
+--------------------------+----------------------------------------+
7 Rows in Set (0.00 sec)
Try it yourself and you'll find out.

Q: Why does my phpmyadmin hint not load mysql extension?
A: Look under your PHP directory and Php\ext There is no php_mysql.dll this file.
Second, prepare the environment variable to add the directory where the Php_mysql.dll is added to the path.
Third, some PHP installation program installed, in the installation path, and no Ext folder (do not know why), it is best to download a configuration of PHP, and then, the EXE folder to the installation directory, and then the corresponding configuration.
Four, look at the C:\Winnt or C:\Windows under the php.ini file Php_ Mysql.dll the front, (semicolon) is removed, if not removed please remove, and then, the MySQL installation path under the Bin folder under the Libmysql.dll in the installation of PHP root directory is generally C:\php, but also under the C:\windows also put each one, Then restart IIS or Apache. Sometimes you always refresh a page, even if you remove the semicolon also can not load MySQL extension, finally opened a new window, try again.

Q: Why does the hint: "There is no PHP extension set mbstring, and the current system seems to be using a wide character set." A phpMyAdmin that does not have a mbstring extension does not correctly recognize the string and can produce unexpected results. "
A: Because you did not open mbstring in the extension, workaround: Open php config file php.ini, will
Extension=php_mbstring.dll in front of the semicolon ";" removed, and changed to the correct path, generally for
Ext/php_mbstring.dll

Q: Why does PHP connect to MySQL with the following tips?
Fatal error:call to undefined function mysql_connect () ...
A: It is possible that the MySQL extension is not loaded and how to resolve it, please refer to the method above.
Because if your system does not load the MySQL extension, phpMyAdmin can give a hint, while the general program cannot.
If phpMyAdmin does not give a hint, and you happen to use the Apache server, try the method:
The first is to remove; Extension=php_mysql.dll the front ";" (; for comment)
The second step is to copy the Php_mysql.dll in the C:\php\ext to C:\WINDOWS (C:\WINNT),
and re-start Apache.

Q: Why can't I look at the hatch pool information?
a:mysql.cn Incubation pool is a special place for beginners to learn, here I do not introduce more, please visit: http://bbs.mysql.cn/thread-369-1-1.html.

Q: Can you name the Chinese database?
A: Yes, but not recommended, inconvenient to use.

Q: If I forgetRoot password, what should I do?
A:
Under Windows:
Open a command-line window to stop the MySQL service: Net stop MySQL
Start MySQL, general to MySQL installation path, find Mysqld-nt.exe
Execution: Mysqld-nt--skip-grant-tables
Also open a command-line window to execute MySQL
>use MySQL
>update User Set Password=password ("New_pass") where user= "root";
>flush privileges;
>exit
Use Ctrl+alt+del, find mysqld-nt process kill it, restart the Mysql-nt service, you can log in with the new password

Under Linux:
If MySQL is running, first kill it: Killall-term mysqld.
Start Mysql:bin/safe_mysqld--skip-grant-tables &
You can go to MySQL without a password.
Then there is
>use MySQL
>update User Set Password=password ("New_pass") where user= "root";
>flush privileges;
Kill MySQL again and start MySQL in a normal way.

Q: Why does the following prompt appear:
[[email protected] ~]# MySQL
ERROR 2002 (HY000): Can ' t connect to local MySQL server through socket '/tmp/mysql.sock ' (2)
Note: It may also be the mysql.sock under other paths
A: The MySQL service does not start, Mysql.sock is the start of the generated file after the MySQL service starts,
Usually first kill MySQL service: killall MySQL
Then, you can start MySQL according to your own installation situation. For example, when installing, use the
./configure--prefix=/usr/local/mysql's
Start with the following command:
/usr/local/mysql/bin/mysqld_safe--user=mysql &
Then go to execute/usr/local/mysql/bin/mysql-u root-p log into MySQL database
If not, execute the following command at a time:
Sync
Reboot restart the computer, please be careful when performing!!!

Q: Why the following error occurred
#1251-client does not the support authentication protocol requested by server; Consider upgrading MySQL client

A: The database you are using is above 4.1, after you connect the MySQL database with the command line, execute the following command:
UPDATE mysql.user SET Password=old_password ("Your_password") WHERE host= "Your_host" and user= "your_username";
can be resolved.

Your_password: Change to Youpassword to connect to the database, e.g. 123
Your_host: Change to the host you connect to the database , if it is local, localhost
Your_username: Change to the user you are connecting to the database, such as root

Q: Why MySQL prompts me: Data too long for column ...
A: First make sure your data size matches the size of your field.
Check your database code again, the database encoding is consistent with the code of the Operational database Tools!


Q: Why I modified wait_timeout not effective
A: Need to be modified at the same time
Interactive_timeout
Wait_timeout
will only take effect (show variables;)


Keep adding, if there are errors, please correct me!

MySQL configuration file my.cnf settings