Share a few common error problems and solutions for Mysql _mysql

1. Question: MySQL DNS anti-solution: Skip-name-resolve

The error log has a similar warning:

1.120119 16:26:04 [Warning] IP address ' 192.168.1.10 ' could the not to resolved:name or service not known
2.120119 16:26:04 [Warning] IP address ' 192.168.1.14 ' could the not to resolved:name or service not known
3.120119 16:26:04 [Warning] IP address ' 192.168.1.17 ' could the not to resolved:name or service not known

Show Processlist finds a large number of connections similar to the following:

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 reverse parsing (IP does not reverse the domain name), which can speed up the database response time.

Modify configuration file Add and reboot required:

[Mysqld]
Skip-name-resolve

2. Issue error log: Error:can ' t create a new thread (errno)

Database server problem, database operation could not create new thread. There are generally 3 reasons for this:
1, MySQL thread open too much.
2), the server system memory overflow.
3), environmental software damage or system damage.

"Problem Solving"

1.1 Enter the user table in the MySQL database of phpMyAdmin, edit the users of the database, modify the Max_connections value. Make the appropriate changes to a smaller point.
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 Optimization of the Web site program SQL and so on

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

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

This hint occurs when you make a database deletion because it contains the files that you put in, such as *.txt files or *.sql files, as long as you go in and delete the file in the execution.

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

Decisively Delete can!!

4. Export data quickly, but slow to import to a new library:

MySQL exported SQL statements may be very very slow to import, experienced only 4 million records imported, has been used for nearly 2 hours. A reasonable use of several parameters when exporting can greatly speed up the import.

-E uses multiple-line insert syntax that includes several values lists;
--max_allowed_packet=xxx the maximum size of the buffer between client/server communication;
--net_buffer_length=xxx TCP/IP and socket communication buffer sizes, creating lines of length up to Net_buffer_length
Note: Max_allowed_packet and net_buffer_length cannot be larger than the target database configuration, or they may be wrong.

First determine the parameter values of the target library

Mysql> Show variables like ' Max_allowed_packet ';
+--------------------+---------+
| variable_name | Value |
+--------------------+---------+
| Max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in Set (0.00 sec)

Mysql> Show variables like ' net_buffer_length ';

+-------------------+-------+
| variable_name | Value |
+-------------------+-------+
| Net_buffer_length | 16384 |
+-------------------+-------+
1 row in Set (0.00 sec)

Write the mysqldump command based on the parameter value, such as:

MYSQL>MYSQLDUMP-UROOT-P database name-e--max_allowed_packet=1048576-net_buffer_length=16384 > SQL file

For example:

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

SQL that can be imported before 2 hours is now ready in 10 seconds.