hive 元数据库 Too many connections

元数据换成mysql，请参考：hive mysql 安装配置，spark从kafka读取数据后，入库hdfs时，会连接mysql元数据库，spark采用的是长连接，当spark关闭时，连接会处于睡眠状态。长时间不处理，mysql的连接数就会被占满。

1，java报错如下

5月 01 01:36:14 namenode1 start.sh[402144]: Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections
5月 01 01:36:14 namenode1 start.sh[402144]: at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
5月 01 01:36:14 namenode1 start.sh[402144]: at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
5月 01 01:36:14 namenode1 start.sh[402144]: at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
5月 01 01:36:14 namenode1 start.sh[402144]: at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.Util.getInstance(Util.java:386)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1709)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2488)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2521)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2306)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:839)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
5月 01 01:36:14 namenode1 start.sh[402144]: at sun.reflect.GeneratedConstructorAccessor56.newInstance(Unknown Source)
5月 01 01:36:14 namenode1 start.sh[402144]: at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
5月 01 01:36:14 namenode1 start.sh[402144]: at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:421)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:350)
5月 01 01:36:14 namenode1 start.sh[402144]: at java.sql.DriverManager.getConnection(DriverManager.java:664)
5月 01 01:36:14 namenode1 start.sh[402144]: at java.sql.DriverManager.getConnection(DriverManager.java:208)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.jolbox.bonecp.BoneCP.obtainRawInternalConnection(BoneCP.java:361)
5月 01 01:36:14 namenode1 start.sh[402144]: at com.jolbox.bonecp.BoneCP.<init>(BoneCP.java:416)
5月 01 01:36:14 namenode1 start.sh[402144]: ... 100 more

2，查看一下mysql连接情况

mysql> show processlist;
+------+----------+-----------------+------+-------------+---------+---------------------------------------------------------------+------------------+
| Id   | User     | Host            | db   | Command     | Time    | State                                                         | Info             |
+------+----------+-----------------+------+-------------+---------+---------------------------------------------------------------+------------------+
|   42 | rep_user | namenode2:50226 | NULL | Binlog Dump | 1402393 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 6709 | hive     | datanode1:32942 | hive | Sleep       |    3433 |                                                               | NULL             |
| 6710 | hive     | datanode1:32944 | hive | Sleep       |    3433 |                                                               | NULL             |
| 6711 | hive     | datanode1:32946 | hive | Sleep       |    3433 |                                                               | NULL             |
| 6712 | hive     | datanode1:32948 | hive | Sleep       |    3433 |                                                               | NULL             |
| 6713 | hive     | datanode1:32950 | hive | Sleep       |    3416 |                                                               | NULL             |
| 6714 | hive     | datanode1:32952 | hive | Sleep       |    3416 |                                                               | NULL             |
| 6715 | hive     | datanode1:32954 | hive | Sleep       |    3414 |                                                               | NULL             |
| 6716 | hive     | datanode1:32956 | hive | Sleep       |    3414 |                                                               | NULL             |
| 6717 | hive     | datanode1:32958 | hive | Sleep       |    3193 |                                                               | NULL             |
| 6718 | hive     | datanode1:32960 | hive | Sleep       |    3193 |                                                               | NULL             |
| 6719 | hive     | datanode1:32962 | hive | Sleep       |    3193 |                                                               | NULL             |
| 6720 | hive     | datanode1:32964 | hive | Sleep       |    3193 |                                                               | NULL             |
| 6721 | hive     | datanode1:32968 | hive | Sleep       |    3174 |                                                               | NULL             |
| 6722 | hive     | datanode1:32970 | hive | Sleep       |    3174 |                                                               | NULL             |

3，mysql长连接默认检测时间

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.01 sec)

mysql默认设置中，如果一个数据库连接超过8小时没有使用，服务器将断开这条连接

4，解决办法

# vim /etc/my.cnf  //增加以下内容,并重启

[mysqld]
max_connections=1000
interactive_timeout=1800
wait_timeout=1800

增大连接数，并且半个小时检查一下长连接