Several common bug problems with MySQL

MySQL common several error problems and solutions: 1. Problem: MySQL DNS inverse solution: skip-name-resolve

The error log has a similar warning:

Click (here) to collapse or open

1. 120119 16:26:04 [Warning] IP address ' 192.168.1.10 ' could not being resolved:name or service not known
2. 120119 16:26:04 [Warning] IP address ' 192.168.1.14 ' could not being resolved:name or service not known
3. 120119 16:26:04 [Warning] IP address ' 192.168.1.17 ' could not being resolved:name or service not known

A number of connections similar to the following are found through show Processlist:

Click (here) to collapse or open

1. |592|unauthenticated user|192.168.1.10:35320| null| Connect| |login| null|
2. |593|unauthenticated user|192.168.1.14:35321| null| Connect| |login| null|
3. |594|unauthenticated user|192.168.1.17:35322| null| Connect| |login| null|

The role of the Skip-name-resolve parameter:no more anti-parsing (IP does not reverse into a domain name), which can speed up the response time of the database. Modify the configuration file additions and require a restart:

Click (here) to collapse or open

1. [Mysqld]
2. Skip-name-resolve

In fact, in [mysqld] the following line to join Skip-name-resolve restart MySQL service.

The following is a more detailed explanation:

Phenomenon:

When the program connects to MySQL, the MySQL error.log inside hints:

[Warning] IP address ' 10.0.0.220 ' could not being resolved:name or service not known

Reason:

The MySQL database server is not configured with/etc/hosts, and there is no DNS service, causing the parsing to fail when the MYSQLD thread resolves the host name corresponding to the IP.

Resources:

MySQL Domain name resolution:

When a new client tries to create a connection with mysqld, Mysqld generates a new thread to handle the request. The new thread will first check if the hostname of the requested connection is in the hostname buffer of MySQL, and if not, the thread will attempt to resolve the host name of the requested connection.

The parsing logic is as follows:

A. The MySQL thread resolves the acquired IP address into a hostname by GETHOSTBYADDR () and then resolves the acquired hostname to an IP address through gethostbyname (), ensuring that the corresponding relationship between the hostname and IP address is accurate;

B. If the operating system supports GETHOSTBYADDR_R () and Gethostbyname_r () calls using secure processes, the MYSQLD thread can use both to optimize host name resolution;

C. If the operating system does not support security thread calls, the MYSQLD process first makes a mutex and then calls Gethostbyaddr () and gethostbyname () to resolve the host name. At this point, no other process can resolve the host name again until the first process releases the hostname of the host name buffer pool; <-------MySQL manual inside the host name mentioned here, meaning should refer to the same IP address and the corresponding first hostname relationship.

When you start the mysqld process, you can use the--skip-name-resolve parameter to disable the DNS hostname resolution feature, and when disabled, you can only use the IP address in the MySQL authorization form.

If the DNS in your environment is very slow or has many hosts, you can improve the responsiveness of your database by disabling the DNS resolution feature--skip-name-resolve or by increasing the host_cache_size size.

Disable host name buffering: Use the--skip-host-cache parameter; refresh hostname buffer: Execute flush hosts or execute mysqladmin flush-hosts;

Disable TCP/IP connections: Use the--skip-networking parameter.

Experiment:
# grep 192.168.1.1/etc/hosts
192.168.1.1 Hostname_online

Sql> Grant Usage on * * to [E- Mail protected]' h_tt_% ' identified by ' root ';

Sql> Flush hosts;

# mysql-h 192.168.1.1-uroot-proot

ERROR 1045 (28000): Access denied for user ' root ' @ ' hostname_online ' (using Password:yes) # # IP resolves to hostname_online, not h_tt_ %, access is denied.

# grep 192.168.1.1/etc/hosts

192.168.1.1 Hostname_online

192.168.1.1 h_tt_1

# mysql-h 192.168.1.1-uroot-proot

ERROR 1045 (28000): Access denied for user ' root ' @ ' hostname_online ' (using Password:yes) # # # MYSQLD did not flush IP and hostname information in the host pool buffer pool, IP corresponds to hostname_online at this time

Sql> Flush hosts;

# mysql-h 192.168.1.1-uroot-proot

ERROR 1045 (28000): Access denied for user ' root ' @ ' hostname_online ' (using Password:yes) # Mysqld parse/etc/hosts inside the same IP pair The host name relationship that follows this IP is no longer resolved when the first hostname relationship should be

# grep 192.168.1.1/etc/hosts

192.168.1.1 h_tt_1

192.168.1.1 Hostname_online

Sql> Flush hosts;

# mysql-h 192.168.1.1-uroot-proot

Sql> exit

"Experiment:" verifies that the same IP is no longer resolved after resolving the first host name relationship for the same IP:

Sql>grant usage On * * to [E- Mail protected]' h_tt_% ' identified by ' root ';

Sql>flush hosts;

# grep h_tt/etc/hosts # grep h_tt/etc/hosts

192.168.1.1hostname_online 192.168.1.1h_tt_1

192.168.1.1h_tt_1 192,168.1.2h_tt_1

Access to MySQL is denied; MySQL can be accessed from two IPs.

Conclusion

This experiment verifies the explanation of "how MySQL Uses DNS" in the MySQL manual above.

That is, mysqld thread parsing/etc/hosts is, IP as a unique identity, in time an IP corresponding to multiple host names, but the mysqld thread only resolves the first correspondence, no matter how many of the following the IP corresponding to the different host name records, MYSQLD process will not be resolved, are not valid.

"Applicable environment:"

There is no DNS server, the host is very much, or do not want to maintain the/etc/hosts inside the manually configured IP and hostname corresponding list, you can perform the hostname "%" when MySQL authorization or disable the IP and hostname resolution (--skip-name-resolve)

2. Issue error log: Error:can ' t create a new thread (errno 12)
Database server problem, the database operation cannot create a new thread. There are generally 3 reasons for this:
1), MySQL thread is open too much.
2), Server system memory overflow.
3), environmental software damage or system damage. "Problem Solving" "Problem solving"

Click (here) to collapse or open

1. 1) Enter the user table in the MySQL database of phpMyAdmin, edit the users of the database, and modify the value of max_connections. A little bit more appropriately.
2. 2) Contact the server administrator to check the server's memory and system is normal, if the server memory is tight, please check which processes consume the server's memory, and consider whether to increase the server's memory to improve the overall system load capacity.
3. 3) MySQL version changed to stable version
4. 4) Optimize the website program SQL and so on

3. Operation Error: Error 1010 (HY000): Error dropping database

Click (here) to collapse or open

1. mysql> drop Database Xjtrace;
2. Error 1010 (HY000): Error dropping database (can ' t rmdir './xjtrace/

In the database to delete this prompt, the reason is that under database contains their own files, such as *.txt files or *.sql files, as long as in the file deleted in the implementation.

Click (here) to collapse or open

1. Mysql>drop database xjtrace;
2. Query OK, 0 rows Affected (0.00 sec)

Decisively Delete can!! 4. Export data very quickly, when importing into the new library is very slow: MySQL exported SQL statements may be very very slow import, experienced the import only 4 million records, it took nearly 2 hours. Reasonable use of several parameters during export can greatly speed up the import. -e uses multi-line insert syntax that includes several values lists;
--MAX_ALLOWED_PACKET=XXX CustomerThe maximum number of buffers that communicate between end/serverSize
--net_buffer_length=xxx TCP/IP and socket communication buffer size, create line lengths up to Net_buffer_length Note: Max_allowed_packet and Net_buffer_ The length cannot be larger than the configured value of the target database, or an error may occur. First determine the parameter values of the target library

Click (here) to collapse or open

1. Mysql> Show variables like ' Max_allowed_packet ';
2. +--------------------+---------+
3. | variable_name | Value |
4. +--------------------+---------+
5. | Max_allowed_packet | 1048576 |
6. +--------------------+---------+
7. 1 row in Set (0.00 sec)
9. Mysql> Show variables like ' net_buffer_length ';
11. +-------------------+-------+
12. | variable_name | Value |
13. +-------------------+-------+
14. | Net_buffer_length | 16384 |
15. +-------------------+-------+
16. 1 row in Set (0.00 sec)

Write mysqldump commands based on parameter values, such as:
MYSQL&GT;MYSQLDUMP-UROOT-P database name-e--max_allowed_packet=1048576-net_buffer_length=16384 > SQL file for example:

Click (here) to collapse or open

1. mysql> mysqldump-uroot-p xjtrace-e--max_allowed_packet=1048576--net_buffer_length=16384 >xjtrace_data_ ' date +%f '. sql

SQL that was imported 2 hours ago can now be completed in a few 10 seconds.

MySQL server has gone away for importing data hints at a time

MySQL server has gone away for importing data hints at a time

Http://roll.sohu.com/20131217/n391910784.shtml

MySQL restricts the size of packets accepted by the server according to the configuration file. Sometimes large inserts and updates are limited by the Max_allowed_packet parameter, causing the write or update to fail.

View current configuration:

Copy CodeThe code is as follows: Show VARIABLES like '%max_allowed_packet% ';
The results shown are:
Copy CodeThe code is as follows: +--------------------+---------+
| variable_name | Value |
+--------------------+---------+
| Max_allowed_packet | 1048576 |
+--------------------+---------+
The above description is currently configured to: 1M. Here's how to fix it:
1. Edit the My.ini in the MySQL installation directory and add the following at the end:
max_allowed_packet=16m
2. Restart the MySQL service to

Several common bug problems with MySQL