MySQL database Exception Handling Summary

We know that exceptions in any database are inevitable. In this article, weMySQL databaseUsageException HandlingIn conclusion, let's take a look at this part.

Question 1:When mysql is started, Starting MySQL... Manager of pid-file quit without updating f [failed] Starting MySQL... Manager of pid-file quit without updating [failed] appears.

Solution:

First:

1. Delete the suffix names of all temporary files in the/usr/local/mysql/data/mysql-bin. * folder.

2. Restart mysql after binary installation) service mysqld start.

Second:

Edit/etc/my. cnf:

 
 

  
  
[mysqld]  
  
  
 
  
  
datadir=/var/lib/mysql 
 
 

The MySQL server stores the database in the directory defined by the datadir variable.

View existing processes and then kill them:

 
 

  
  
ps -aux|grep mysql  
  
  
 
  
  
8016 pts/2 00:00:00 mysqld_safe  
  
  
 
  
  
8037 pts/2 00:00:00 mysqld 
 
 

Restart mysql.

Third:

Comment out in/etc/my. cnf

Skip-federated => # skip-federated

Fourth:

Sometimes this error occurs when datadir =/var/lib/mysql is set in/etc/my. cnf to restart mysql.

If not set separately, the default mysql data directory is under the mysql installation directory.

Question 2:Fatal error: cocould not find./bin/my_print_defaults appears when the system generates the database by itself.

Solution:/Usr/local/mysql/scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysql -- datadir =/usr/local/mysql/data

Question 3:Mysql_connect () [<a href = 'function. mysql-connect '> function. mysql-connect </a>]: Can't connect to local MySQL server through socket '/var/lib/mysql. sock '(13)/eccore/model/mysql. php 94 or Starting MySQL/etc/init. d/mysql: line 327: -- socket =/var/lib/mysql. sock.

Solution:

1> Add the following code to allow mysql to apply the file to the tmp folder. The default address is/var/lib/mysql. sock.

Modify/etc/my. cnf

 
 

  
  
[mysql]  
  
  
 
  
  
socket=/tmp/mysql.sock 
 
 

2> go to the/tmp folder to create mysql. sock

Touch mysql. sock

3> restart mysql

/Etc/init. d/mysqld restart

Question 4:Host A mysql accesses host B mysql: mysql-uroot-proot-P3306-h192.168.100.52 with the following ERROR: ERROR 2003 (HY000): Can't connect to MySQL server on '123. 168.100.52 '(113)

Solution:

1. The server must be accessible on the Internet (public network). If a firewall exists, open port 3306 (the default listening port of MySQL );

Disable all firewalls/etc/rc. d/init. d/iptables stop

Add Rules vim/etc/sysconfig/iptables

2. Create a remote user on the mysql server. The simplest way is

Grant all privileges on *. * to username @ "%" identified by "root"

Question 5:There are several cases of MySQL server has gone away.

Solution:

1. applications such as PHP) execute MYSQL statements in batches for a long time.

Solution:

Add or modify the following two variables in the my. cnf file:

 
 

  
  
wait_timeout=2880000 
  
  
 
  
  
interactive_timeout = 2880000 
 
 

For details about the two variables, refer to google or the official manual.

If you cannot modify my. cnf, you can set CLIENT_INTERACTIVE when connecting to the database. For example:

 
 

  
  
sql = "set interactive_timeout=24*3600";  
  
  
 
  
  
mysql_real_query(...) 
 
 

2. Execute an SQL statement, but the SQL statement is too large or the statement contains BLOB or longblob fields. For example, processing image data.

Solution:

Add or modify the following variables in the my. cnf file:

Max_allowed_packet = 10 M (you can also set the size you need)

The max_allowed_packet parameter is used to control the maximum length of the Communication buffer.

Here is a summary of some related exception handling in the MySQL database. I hope this introduction will be helpful to you.