Mysql

JBoss 不使用數據源對 MySQL 從站進行故障轉移

  • June 18, 2012

我正在嘗試使用主/從配置設置一個高可用的 MySQL 集群。如果第一個節點出現故障，我希望從屬設備成為主節點（例如，不是只讀的而是可寫的）（就像在這個問題中一樣：https ://stackoverflow.com/questions/9409111/database-fail-over-in-jboss-數據源）

因此，我正在使用帶有 Connector/J 驅動程序的數據源：

<datasources>

   <local-tx-datasource>
   <jndi-name>alfresco-datasource</jndi-name>
   <connection-url>jdbc:mysql://10.1.2.13,10.1.2.14:3306/alfresco</connection-url>
   <driver-class>org.gjt.mm.mysql.Driver</driver-class>
   <user-name>alfresco</user-name>
   <password>alfresco</password>
   <exception-sorter-class-name>
       org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
   </exception-sorter-class-name>


   <connection-property name="readOnly">false</connection-property>
   <failOverReadOnly>false</failOverReadOnly>

   <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>

   <metadata>
     <type-mapping>mySQL</type-mapping>
   </metadata>
 </local-tx-datasource>
</datasources>

我在這裡讀了很多關於這個的文章，但我還不確定這是否足夠（事實上我確信它不是；））。

為了測試我的配置，我只是停止第一個實例：

service mysql stop

一旦我使用 Ibatis 的應用程序（Alfresco，依賴於 Activiti）檢測到出現故障，它就會向我發送大量消息，說連接失去/被拒絕，……所以故障轉移不起作用。

   GRAVE: Error while closing command context
   org.apache.ibatis.exceptions.PersistenceException: 
   ### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

   The last packet successfully received from the server was 1 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
   ### The error may exist in org/activiti/db/mapping/entity/Job.xml
   ### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectNextJobsToExecute_mysql
   ### The error occurred while executing a query
   ### SQL: select *      from ACT_RU_JOB      where (RETRIES_ > 0)       and (DUEDATE_ is null or DUEDATE_ <= ?)       and (LOCK_OWNER_ is null or LOCK_EXP_TIME_ <= ?)
   ### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

   The last packet successfully received from the server was 1 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
       at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
       at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:77)
       at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:191)
       at org.activiti.engine.impl.persistence.entity.JobManager.findNextJobsToExecute(JobManager.java:97)
       at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:49)
       at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:32)
       at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
       at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:42)
       at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
       at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
       at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
       at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
       at org.activiti.engine.impl.jobexecutor.JobAcquisitionThread.run(JobAcquisitionThread.java:63)
   Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure


[...]


Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
   at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2552)
   at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3002)
   ... 45 more
09:54:00,270 WARN  [LocalManagedConnectionFactory] Destroying connection that is not valid, due to the following exception: com.mysql.jdbc.JDBC4LoadBalancedMySQLConnection@33dd0edd
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 31 875 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.

[...]

09:54:00,355 WARN  [JBossManagedConnectionPool] Destroying connection that could not be successfully matched: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@34775a91[state=DESTROYED mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@244f22a3 handles=0 lastUse=1339055604851 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@3d01c1a context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@53e4f0ea xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@476b0df4 txSync=null]
09:54:01,702 ERROR [STDERR] Thu Jun 07 09:54:01 CEST 2012 WARN: Connection to primary host failed

EXCEPTION STACK TRACE:



** BEGIN NESTED EXCEPTION **

等等 …

我之前遇到過問題，因為我的應用伺服器使用者（alfresco@10.1.2.11）沒有在數據庫上被授予。但現在已經解決了。

這是我的 MySQL 配置文件（以防我忘記了什麼）：

大師 (10.1.2.13)

$$ mysqld $$ user = s-mysql port = 3306 basedir =/app/mysql/current datadir =/app/data/mysql log-error =/app/log/mysql/master log =/app/log/mysql/query.log

#clustering
server_id       =1
log-bin         =/app/log/mysql/log-bin/log-bin.master
innodb_flush_log_at_trx_commit=1
sync_binlog     =1
skip-slave-start
expire_logs_days=10

binlog-do-db    =alfresco

從站 (10.1.2.14)

[mysqld]
user            = s-mysql
port            = 3306
basedir         =/app/mysql/current
datadir         =/app/data/mysql
log-error       =/app/log/mysql/slave
log             =/app/log/mysql/query.log

# Clustering
server_id       =2
log-bin         =/app/log/mysql/log-bin/log-bin.slave
expire_logs_days=10
skip-slave-start
relay-log-index =/app/log/mysql/log-relay/relay-log.slave.index
relay-log       =/app/log/mysql/log-relay/relay-log.slave

binlog-do-db    =alfresco
replicate-do-db =alfresco

奴隸特權

SHOW GRANTS for 'alfresco'@'10.1.2.11';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for alfresco@10.1.2.11                                                                                               |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'alfresco'@'10.1.2.11' IDENTIFIED BY PASSWORD     '*F9F457310EF9364D42AABE6D8C24A21712C000B5' |
| GRANT ALL PRIVILEGES ON `alfresco`.* TO 'alfresco'@'10.1.2.11' WITH GRANT OPTION                                    |
+-----------------------------------------------------------------------------------------------------------------+

有人可以給我提示，以解決這個問題嗎？

確實，我似乎“忘記”了一個參數

<autoReconnect>true</autoReconnect>

我說要小心忘記了，因為正如每個文件所說：https ://community.jboss.org/wiki/SetUpAMysqlDatasource

自動重新連接

警告：如果您使用託管事務，請勿啟用自動重新連接

自動重新連接不會在數據庫中保留事務狀態。

如果你總是使用 auto-commit=true 也沒關係。

我不確定我是否可以啟動它……我仍在質疑。

編輯 拆分執行緒，因為我的第一個問題是（自動）回答的。如果我得到一些資訊，我會添加一些關於 Alfresco、自動送出和託管事務的資訊。

讀寫模式問題：https ://stackoverflow.com/questions/10945261/jboss-alfresco-cannot-write-on-slave-after-failover

引用自：https://dba.stackexchange.com/questions/19453

相關問答

Mysql

MySQL 故障轉移：如何在主從配置中將應用程序切換到新的主伺服器？

  • April 3, 2019
檢視問答
Mysql

避免 MySQL Fabric 成為單點故障

  • September 25, 2017
檢視問答
Mysql

具有自動故障轉移功能的 MySQL HA

  • May 13, 2017
檢視問答
Mysql

同步同步和負載平衡 InnoDB 數據庫的最佳解決方案是什麼

  • May 28, 2022
檢視問答
Mysql

起搏器和半同步複製

  • April 28, 2021
檢視問答
Sql-Server

SQL Server Always On AG – 自動故障轉移是否需要被動輔助節點？

  • March 29, 2021
檢視問答