MySQL configuration file my. CNF settings

Recommended settings:

For a single running web server, we recommend that you add:

Skip-locking

Skip-name-resolve

Skip-networking

Use "localhost" when connecting to the database in PHP. In this way, the mysql client library will overwrite and try to connect to the local socket .(

We can use PHP. ini

Code:

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

; MySQL ults.

MySQL. default_socket =/tmp/MySQL. Sock it can be seen that UNIX will access/tmp/MySQL. Sock by default)

Some options are described as follows:

My. CNF does not exist by default. You can see it in/usr/local/share/MySQL:

My-huge.cnf

My-innodb-heavy-4G.cnf

My-large.cnf

My-medium.cnf

My-small.cnf

And other files. copy the files that are suitable for your machine configuration to the/etc/My. CNF or MySQL data directory/My. CNF (/var/DB/MySQL) or ~ /. My. CNF. Detailed descriptions are provided in the file.

[Mysqld]

Port = 3306

Serverid = 1

Socket =/tmp/MySQL. Sock

Skip-locking

# Avoid external locks of MySQL to reduce the chance of errors and enhance stability.

Skip-name-resolve

Prohibit MySQL from performing 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!

Back_log = 384

Specify the number of possible MySQL connections. When the MySQL main thread receives many connection requests within a short period of time, this parameter takes effect. 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 stored in the stack within a short 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 listener queue for the incoming TCP/IP connection. Different operating systems have their own limits on the queue size.

Trying to set back_log to be higher than your operating system limit will be invalid. The default value is 50. We recommend that you set the value to an integer smaller than 512 in Linux.

Key_buffer_size = 256 m

# Key_buffer_size specifies the buffer size used for the index. Increasing the size can improve the index processing performance.

This parameter can be set to 384 m or m for servers with around 4 GB of memory.

Note: If this parameter value is set too large, the overall efficiency of the server will be reduced!

Max_allowed_packet = 4 m

Thread_stack = 256 K

Table_cache = 128 K

Sort_buffer_size = 6 m

The buffer size that can be used to query sorting. Note: The allocated memory corresponding to this parameter is exclusive to each connection! If there are 100 connections, the total size of the actually allocated sort buffer is 100 × 6 = 600 mb. Therefore, we recommend that you set the size of a server with around 4 GB to 6-8 Mb.

Read_buffer_size = 4 m

The buffer size that can be used by the read query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!

Join_buffer_size = 8 m

The buffer size that can be used by the Joint query operation. The same as sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!

Myisam_sort_buffer_size = 64 m

Table_cache = 512

Thread_cache_size = 64

Query_cache_size = 64 m

Specify the size of the MySQL Query Buffer. You can run the following command on the MySQL console:

Code:

#> Show variables like '% query_cache % ';

#> Show status like 'qcache % '; if the qcache_lowmem_prunes value is very large, it indicates that there is often insufficient buffer;

If the qcache_hits value is very large, it indicates that the query buffer is used very frequently. If the value is small, it will affect the efficiency, you can consider not to use the Query Buffer; qcache_free_blocks, if the value is very large, it indicates that there are many fragments in the buffer.

Tmp_table_size = 256 m

Max_connections = 768

Specifies the maximum number of connection processes allowed by MySQL. If the too connector connections error is frequently reported during Forum access, you need to increase the value of this parameter.

Max_connect_errorrs = 10000000

Wait_timeout = 10

Specify the maximum connection time of a request. For servers with around 4 GB of memory, you can set it to 5-10.

Thread_concurrency = 8

In this example, the server has two physical CPUs, and each physical CPU supports h.t hyper-threading, therefore, the actual value is 4x2 = 8.

Skip-networking

Enabling this option can completely disable the MySQL TCP/IP connection mode. If the Web server accesses the MySQL database server remotely, do not enable this option! Otherwise, the connection will fail!

1. About mysql5
Mysql5 series database is the latest MySQL database, the more popular release version is mysql-5.0.18. MySQL English official website is http://www.mysql.com

2. Obtain mysql5
All MySQL database programs can be downloaded from its official English website. However, since not all programs have good English skills, therefore, we recommend that you try to download the programs you need from the MySQL Chinese official website. If you cannot find them, go to the English website.
Mysql5 has the following options: Installation version and installation-free version. As the name suggests, the installation version can be used only after installation. The installation version can be used after MySQL is downloaded, or you can use it with simple settings.
Mysql5 installation version:
Windows: http://download.mysql.cn/src/2006/0218/199.html
Linux version (source package): http://download.mysql.cn/src/2006/0208/62.html
Mysql5 installation-free version:
Windows: http://download.mysql.cn/src/2006/0302/205.html

Iii. Install MySQL 5
Windows Installation manual: http://bbs.mysql.cn/thread-261-1-1.html
Linux version (source package) Installation manual: http://bbs.mysql.cn/thread-493-1-2.html

MySQL has an installation version and an installation-free version. You can directly use it if there is no installation program in the unzipped folder.
Windows Installation-free version User Manual: http://bbs.mysql.cn/thread-552-1-1.html

Iv. backup and recovery
The general BACKUP command is mysqldump. Here we will take the TM database as an example to give a brief introduction. For details, refer
Http://info.mysql.cn/install/2006/0410/5521.html
Backup:
# Mysqldump-u root-p tm> tm_20060101. SQL
Enter the password as prompted, and back up all the table structures and data of the TM database to tm_20060101. SQL. Because the backup work is always required, if the data volume occupies a large space,
You can use gzip to compress data. The command is as follows:
# Mysqldump-u root-p tm | gzip> tm_20060101. SQL .gz
You can also back up the data to a remote machine, which is determined by-H, as shown in figure
# Mysqldump-u root-p tm> tm_20060101. SQL-H XXX. XXX
You can back up data directly to a remote computer with the IP address XXX.
When the system crashes, the system is rebuilt, or the database is restored, data can be restored as follows:
# Mysql-u root-P tm <tm_20060101. SQL
Direct Recovery from compressed files:
# Gunzip <tm_20060101. SQL .gz | mysql-u root-P TM

5. FAQs

Q: Why is there no installation file in my "Installer?
A: You may download an installation-free version. Provide detailed version information.

Q: How do I start and disable MySQL?
A: In Linux: for example, My MySQL is installed in/usr/local/MySQL by source code.
Automatic: copy/usr/local/MySQL/share/MySQL. server to/etc/rc. d/init. d/, and
Chkconfig -- add mysql. server to start the MySQL service after it is started.
Manual: Run/usr/local/MySQL/bin/mysqld_safe -- user = MYSQL as root
Windows:
Automatic:
Use cmd to go to the bin folder in the MySQL installation path and run: mysqld-NT -- install
Manual: directly go to the bin folder of the MySQL installation path and execute Net start MySQL.
If you do not want MySQL to start the service when the computer is started, execute: mysqld-NT -- remove
You can also delete the corresponding service in HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Services and restart the computer.
Close MYSQL: mysqladmin-uroot-P Shutdown
Start MYSQL:
Mysqld-NT -- install
Net start MySQL

Q: How do I modify the MySQL user password?
A: distinguish the user password used to connect to the MySQL database from the user password used to connect to the system !!

Mysql-uroot-P
Enter Password
# Log on to MySQL
> Use MySQL
> Update user SET Password = PASSWORD ("new_pass") where user = "username"; # Replace username with the password of the username you want to modify, such as root
> Flush privileges;
> Exit;

Q: How do I log on to MySQL?
A: mysql-uroot-P. Press enter, enter the password, and then press Enter. If you have not modified the password, the default password is null.

Q: How can MySQL be converted into a graphical database? Is there any built-in graphical tool?
A: MySQL comes with a character client, but there are many good graphic management tools such as mysql_center, sqlyog, phpMyAdmin, MySQL query browser, MySQL administrator, and mysqlcc.
Mysql_centerd on this site: http://bbs.mysql.cn/thread-517-1-1.html
Sqlyog searches the Internet. Many sqlyog407 versions are good.
PhpMyAdmin: http://download.mysql.cn/opencode/2006/0207/55.html
Management software provided by mysql.com: http://dev.mysql.com/downloads/gui-tools/5.0.html
I will not provide them one by one. I will search and download them online.

Q: Why does the following prompt appear when I use MySQL-uusername-P to log on to the MySQL server:
Host 'xxx. XXX. XXX. XXX' is not allowed to connect to this MySQL server! ("XXX. XXX" indicates the server to log on)
A: This is because the MySQL server you want to log on to does not allow user username to log on from the IP address XXX.
The solution is to log on to MySQL on the server by performing the following steps:
Mysql-uroot-P
(Enter the password)
Use MySQL
Update user set host = "%" where user = "username ";
Flush privileges;

Q: What is phpMyAdmin?
A: phpMyAdmin is written in PHP and can be used to control and operate MySQL over the Internet. PhpMyAdmin can be used to operate databases, such as creating, copying, and deleting data.
With phpMyAdmin, you can directly use phpMyAdmin to manage all MySQL data and databases without using the MySQL command.

Q: How to Use phpMyAdmin?
A: You need to use phpMyAdmin. After downloading it, release it to the root directory of the web server and get a name, for example, phpMyAdmin. Then enter the following in the address bar of the browser:
Http://X.X.X.X/phpMyAdmin/index.php
You can.
If phpMyAdmin 2.8.0.2 or later is used, an error is reported because the configuration file config is not required by this software by default. default. PHP. This file exists in 2.6.x, but the password is incorrect. For version 2.6.x, you can open this file in a text editor and change the content in the first $ cfg file 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'] = 'your password ';
If you do not set a password, the system will prompt:
Access denied for user 'root' @ 'localhost' (using password: No)

For version 2.8.0.2, the program will prompt you to run a file such as scripts/setup. php or index. php for configuration to generate the config. Inc. php file.

Q: Why am I garbled with MySQL?
A: When mysql5 is installed, the default character set is Swedish Latin1, which does not support Chinese characters. There are many reasons for Garbled text. We recommend you go to The bbs.mysql.cn forum to find the answer. If you post a post, describe your environment, installation process, and usage in detail so that you can quickly analyze your problems.

Q: How do I modify the character set?
A:
Mysql-uroot-P
Enter Password
Select your target database
Mysql> Use dbname
Display 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 the character set, use the set command, for example:
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/|
+ -------------------------- + ---------------------------------------- +
Change 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 on your own.

Q: Why does my phpMyAdmin prompt fail to load MySQL extensions?
A: I. Check whether your PHP Directory and PHP \ ext have the php_mysql.dll file.
2. Prepare the environment variable to add the directory of php_mysql.dll to the path.
3. After some PHP installation programs are installed, there is no ext folder in the installation path (I don't know why). It is best to download a PHP configuration. Then, copy the EXE folder to the installation directory and configure it accordingly.
4. Check whether c: \ WINNT or c: \ PHP in windows. in the INI file, check whether php_mysql.dll is removed. If not, remove it. Then, remove libmysql from the bin folder in the MySQL installation path. the DLL is usually stored in the root directory c: \ PHP for PHP installation, and also in c: \ windows, and then IIS or Apache is restarted. Sometimes, the page is always refreshed. Even if the semicolon is removed, the MySQL extension cannot be loaded. Finally, a new window is opened and try again.

Q: Why is the prompt: "The PHP extension settings mbstring are not found, and the current system seems to be using the wide character set. PhpMyAdmin without mbstring Extensions cannot correctly identify strings and may produce unexpected results ."
A: Because you have not enabled the mbstring extension, solution: Open the PHP configuration file PHP. ini and set
Extension = php_mbstring.dll: Semicolon ";" removed, and changed to the correct path. Generally
EXT/php_mbstring.dll

Q: Why is the following prompt displayed when PHP connects to MySQL?
Fatal error: Call to undefined function mysql_connect ()......
A: It is very likely that the MySQL extension is not loaded. For how to solve this problem, refer to the above method.
If your system does not load the MySQL extension, phpMyAdmin can give a prompt, while general programs cannot.
If phpMyAdmin does not provide a prompt, but you are using the Apache server, try the following method:
First, remove ";" (;) before extension = php_mysql.dll)
Step 2: Copy php_mysql.dll in c: \ PHP \ ext to C: \ WINDOWS (C: \ WINNT,
And restart Apache.

Q: Why can't I view the incubation pool information?
A: The incubator pool of MySQL. CN is a place for beginners to learn. I will not introduce it here. For details, visit: http://bbs.mysql.cn/thread-hang-1-1.html.

Q: Can I use a Chinese Database Name?
A: Yes, but it is not recommended. it is inconvenient to use.

Q: What if I forget the root password?
A:
In Windows:
Open the command line window and stop MySQL service: net stop MySQL
Start MySQL, usually to the installation path of MySQL, find the mysqld-nt.exe
Run: mysqld-NT -- skip-grant-tables
Open a command line window and execute MySQL
> Use MySQL
> Update user SET Password = PASSWORD ("new_pass") where user = "root ";
> Flush privileges;
> Exit
Use CTRL + ALT + DEL to find the mysqld-nt process and kill it. After restarting the mysql-NT service, you can log on with the new password.

In Linux:
If MySQL is running, killall-term mysqld is first killed.
Start MYSQL: Bin/safe_mysqld -- skip-grant-tables &
You can access MySQL without a password.
Then
> 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 is the following prompt displayed:
[Root @ 0-8-2-df-fa-ee ~] # MySQL
Error 2002 (hy000): Can't connect to local MySQL server through socket '/tmp/MySQL. Sock' (2)
Note: It may also be MySQL. Sock in another path.
A: The MySQL service is not started. MySQL. Sock is a file generated after the MySQL service is started,
Generally, killall MySQL is killed first.
Then, start MYSQL based on your installation. For example, during installation
./Configure -- prefix =/usr/local/MySQL
Start with the following command:
/Usr/local/MySQL/bin/mysqld_safe -- user = MySQL &
Then run/usr/local/MySQL/bin/MySQL-u root-P to log on to the MySQL database.
If not, run the following command once:
Sync
Reboot; restart the computer. Be careful when executing the command !!!

Q: Why are the following errors?
#1251-client does not support Authentication Protocol requested by server; consider upgrading MYSQL client

A: The database you are using is more than 4.1. After you connect to the MySQL database through the command line, run the following command:
Update mysql. User SET Password = old_password ("your_password") where host = "your_host" and user = "your_username ";
.

Your_password: change it to your database password, for example, 123
Your_host: changed to the host on which you connect to the database. If it is local, It is localhost.
Your_username: the user who connects to the database, for example, Root

Q: Why does MySQL prompt "data too long for column ......
A: First, make sure that your data size matches your field size.
Check whether the encoding of your database is consistent with that of the database tool!

Q: Why does my wait_timeout modification not take effect?
A: It must be modified at the same time.
Interactive_timeout
Wait_timeout
(Show variables ;)

Source: http://www.blogjava.net/baoyaer/articles/209466.html