Host ‘172.16.1.102’ is not allowed to connect to this MySQL server

Host ‘172.16.1.102’ is not allowed to connect to this MySQL server

使用Activemq的JDBC存储消息时，启动Activemq时启动不了，查看日志发现时数据库连接的问题。

异常信息如下：

ERROR | Failed to start Apache ActiveMQ (localhost, null) | org.apache.activemq.broker.BrokerService | main
java.io.IOException: Cannot create PoolableConnectionFactory (null,  message from server: "Host '172.16.1.102' is not allowed to connect to this MySQL server")
	at org.apache.activemq.util.IOExceptionSupport.create(IOExceptionSupport.java:46)[activemq-client-5.15.14.jar:5.15.14]
	at org.apache.activemq.store.jdbc.TransactionContext.lockAndWrapped(TransactionContext.java:96)[activemq-jdbc-store-5.15.14.jar:5.15.14]
	at org.apache.activemq.store.jdbc.TransactionContext.getConnection(TransactionContext.java:68)[activemq-jdbc-store-5.15.14.jar:5.15.14]
	at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.loadAdapter(JDBCPersistenceAdapter.java:456)[activemq-jdbc-store-5.15.14.jar:5.15.14]
	at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.createAdapter(JDBCPersistenceAdapter.java:439)[activemq-jdbc-store-5.15.14.jar:5.15.14]
	at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.getAdapter(JDBCPersistenceAdapter.java:388)[activemq-jdbc-store-5.15.14.jar:5.15.14]
	at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.init(JDBCPersistenceAdapter.java:301)[activemq-jdbc-store-5.15.14.jar:5.15.14]
	at org.apache.activemq.broker.LockableServiceSupport.preStart(LockableServiceSupport.java:99)[activemq-broker-5.15.14.jar:5.15.14]
	at org.apache.activemq.util.ServiceSupport.start(ServiceSupport.java:54)[activemq-client-5.15.14.jar:5.15.14]
	at org.apache.activemq.broker.BrokerService.doStartPersistenceAdapter(BrokerService.java:693)[activemq-broker-5.15.14.jar:5.15.14]
	at org.apache.activemq.broker.BrokerService.startPersistenceAdapter(BrokerService.java:677)[activemq-broker-5.15.14.jar:5.15.14]
	at org.apache.activemq.broker.BrokerService.start(BrokerService.java:641)[activemq-broker-5.15.14.jar:5.15.14]
	at org.apache.activemq.xbean.XBeanBrokerService.afterPropertiesSet(XBeanBrokerService.java:73)[activemq-spring-5.15.14.jar:5.15.14]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)[:1.8.0_161]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)[:1.8.0_161]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)[:1.8.0_161]
	at java.lang.reflect.Method.invoke(Method.java:498)[:1.8.0_161]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1748)[spring-beans-4.3.29.RELEASE.jar:4.3.29.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1685)[spring-beans-4.3.29.RELEASE.jar:4.3.29.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1615)[spring-beans-4.3.29.RELEASE.jar:4.3.29.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)[spring-beans-4.3.29.RELEASE.jar:4.3.29.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:481)[spring-beans-4.3.29.RELEASE.jar:4.3.29.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312)[spring-beans-4.3.29.RELEASE.jar:4.3.29.RELEASE]
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)[spring-beans-4.3.29.RELEASE.jar:4.3.29.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308)[spring-beans-4.3.29.RELEASE.jar:4.3.29.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)[spring-beans-4.3.29.RELEASE.jar:4.3.29.RELEASE]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:756)[spring-beans-4.3.29.RELEASE.jar:4.3.29.RELEASE]
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867)[spring-context-4.3.29.RELEASE.jar:4.3.29.RELEASE]
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542)[spring-context-4.3.29.RELEASE.jar:4.3.29.RELEASE]
	at org.apache.xbean.spring.context.ResourceXmlApplicationContext.<init>(ResourceXmlApplicationContext.java:64)[xbean-spring-4.18.jar:4.18]
	at org.apache.xbean.spring.context.ResourceXmlApplicationContext.<init>(ResourceXmlApplicationContext.java:52)[xbean-spring-4.18.jar:4.18]
	at org.apache.activemq.xbean.XBeanBrokerFactory$1.<init>(XBeanBrokerFactory.java:104)[activemq-spring-5.15.14.jar:5.15.14]
	at org.apache.activemq.xbean.XBeanBrokerFactory.createApplicationContext(XBeanBrokerFactory.java:104)[activemq-spring-5.15.14.jar:5.15.14]
	at org.apache.activemq.xbean.XBeanBrokerFactory.createBroker(XBeanBrokerFactory.java:67)[activemq-spring-5.15.14.jar:5.15.14]
	at org.apache.activemq.broker.BrokerFactory.createBroker(BrokerFactory.java:71)[activemq-broker-5.15.14.jar:5.15.14]
	at org.apache.activemq.broker.BrokerFactory.createBroker(BrokerFactory.java:54)[activemq-broker-5.15.14.jar:5.15.14]
	at org.apache.activemq.console.command.StartCommand.runTask(StartCommand.java:87)[activemq-console-5.15.14.jar:5.15.14]
	at org.apache.activemq.console.command.AbstractCommand.execute(AbstractCommand.java:63)[activemq-console-5.15.14.jar:5.15.14]
	at org.apache.activemq.console.command.ShellCommand.runTask(ShellCommand.java:154)[activemq-console-5.15.14.jar:5.15.14]
	at org.apache.activemq.console.command.AbstractCommand.execute(AbstractCommand.java:63)[activemq-console-5.15.14.jar:5.15.14]
	at org.apache.activemq.console.command.ShellCommand.main(ShellCommand.java:104)[activemq-console-5.15.14.jar:5.15.14]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)[:1.8.0_161]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)[:1.8.0_161]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)[:1.8.0_161]
	at java.lang.reflect.Method.invoke(Method.java:498)[:1.8.0_161]
	at org.apache.activemq.console.Main.runTaskClass(Main.java:262)[activemq.jar:5.15.14]
	at org.apache.activemq.console.Main.main(Main.java:115)[activemq.jar:5.15.14]

发现是权限的原因，进行下面的操作就可以了

mysql> use mysql
Database changed
mysql> UPDATE USER SET HOST = '%' WHERE USER = 'root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

---

重启之后又报错了，这次错误是这样的。

java.sql.SQLException: Cannot create PoolableConnectionFactory (The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.)

查找资料发现是由于数据库和系统时区差异所造成的，在jdbc连接的url后面加上serverTimezone=GMT 既可以了。

于是修改了activemq.xml文件，

<property name="url" value="jdbc:mysql://192.168.10.132:3306/activemq?relaxAutoCommit=true&serverTimezone=GMT"/> 

---

重启activemq服务，发现还是启动不了，再次查看日志的错误信息

 ERROR | Failed to load: class path resource [activemq.xml], reason: Line 131 in XML document from class path resource [activemq.xml] is invalid; nested exception is org.xml.sax.SAXParseException; lineNumber: 131; columnNumber: 109; The reference to entity "serverTimezone" must end with the ';' delimiter. | org.apache.activemq.xbean.XBeanBrokerFactory | main
org.springframework.beans.factory.xml.XmlBeanDefinitionStoreException: Line 131 in XML document from class path resource [activemq.xml] is invalid; nested exception is org.xml.sax.SAXParseException; lineNumber: 131; columnNumber: 109; The reference to entity "serverTimezone" must end with the ';' delimiter.

网上搜索后发现是由于&的原因， 在转义字符问题，在.xml文件中，有几种字符必须转义才可以使用

   				& 替换为 &

                > 替换为 >

                < 替换为 &lt;

                " 替换为 &quot;

                ' 替换为 &apos;

注意：后面的分号要加上去

修改之后终于重启成功了