是否有任何 MySQL 基准测试工具？[关闭]

Question

Jean-Rémy Revy

Asked: 2012-06-08 00:37:36 +0800 CST2012-06-08 00:37:36 +0800 CST 2012-06-08 00:37:36 +0800 CST

JBoss 不使用数据源对 MySQL 从站进行故障转移

772

我正在尝试使用主/从配置设置一个高可用的 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 **

等等 ...

我之前遇到过问题，因为我的应用服务器用户（[email protected]）没有在数据库上被授予。但现在已经解决了。

这是我的 MySQL 配置文件（以防我忘记了什么）：

Master (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/日志/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 [email protected]                                                                                               |
+-----------------------------------------------------------------------------------------------------------------+
| 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                                    |
+-----------------------------------------------------------------------------------------------------------------+

有人可以给我提示，以解决这个问题吗？

1 个回答

Voted

Jean-Rémy Revy · Answer 1 · 2012-06-08T01:28:46+08:00

Best Answer

Jean-Rémy Revy

2012-06-08T01:28:46+08:002012-06-08T01:28:46+08:00

确实，我似乎“忘记”了一个参数

<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

2

JBoss 不使用数据源对 MySQL 从站进行故障转移

如何查看 Oracle 中的数据库列表？

mysql innodb_buffer_pool_size 应该有多大？

列出指定表的所有列

从 .frm 和 .ibd 文件恢复表？

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

如何选择每组的第一行？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

JBoss 不使用数据源对 MySQL 从站进行故障转移

1 个回答

相关问题