How mysql works _ MySQL

How to deal with mysql problems bitsCN.com

How to deal with mysql problems

Record and solve the problem of using mysql at work.

1 pipe broken or connection broken

The connection to the mysql client using pol is inexplicably disconnected, which is generally caused by active disconnection from the server. you need to check the wait_timeout between maxIdle and mysql in the connection pool. The former must be small, otherwise, the connection is automatically disconnected by the server.

2. slave server load balancer under the domain name

Currently, free connection pools such as c3p0 and dbcp use lifo in thread pools. if you use a domain name to manage mysql slave instances under multiple ip addresses, you may find that the connections and loads of each slave are not balanced.

Solution: 1) carefully adjust idleTime and min/max poolsize; 2) add a proxy layer to manage the connection of these thread pools. However, in this case, you need to know the ip address of each connection, but the driver does not. there is a way to obtain the ip address through SQL: select @ hostname to check the host of the server. in this way, when used with lookup server, the ip address is also obtained; in addition, you can also directly manage it based on the host. it is not intuitive without ip addresses. 3) write your own driver, but it takes time and verification ~~~

3. the unique key deletion + insert compound operation (unbind + bind)

Common: when binding a business, if another unique key has been bound to bind, unbind first and then bind. This is an example of replace.

Note: 1) If there are multiple unique keys (including primary keys) and the newly inserted row is the same as multiple unique keys, mysql will delete multiple first, insert this entry. 2) replace into cannot obtain the original value of the previous row. for example, replace into xxx on duplicate key update set a = a + 1 is equivalent: set a = default (a) + 1; 3) the table must have a unique key; otherwise, it is equivalent to insert;

Mysql processing algorithm: 1) try insert; 2) if false, del with uniqu/primary key; 3) try insert again

BitsCN.com