Oracle Communications Calendar Server relies on native capabilities offered by MySQL Server and the application server for a high-availability solution. This chapter provides details on one such scenario: setting up replication of MySQL Server by using the JDBC Connector/J driver in Oracle GlassFish Server and Oracle WebLogic Server. If you want more sophisticated solutions for MySQL Server high availability, refer to the MySQL Server documentation, for example, the High Availability and Scalability chapter in MySQL 5.5 Reference Manual.
Other high availability solutions are also known in the MySQL Server community. You should decide which solution best fits your deployment and requirements.
Note:
The examples in this information are intended for only one Calendar front end per Calendar back end.MySQL Server, as well as third parties, offer a wide range of high availability options ranging from completely manual to high-end MySQL Server HA solutions. MySQL Server implements manual asynchronous replication, provided within the product itself. This has been in use for some time and is stable. Failover, failback, and resynchronizing nodes, and adding a node, are all done manually. See the topic on replication in MySQL 5.5 Reference Manual for more information.
MySQL Server 5.5 provides semi-synchronous replication in which the master node tries to sync with at least one other node before completing the request, subject to a timeout.
This section describes a simple example of MySQL Server asynchronous replication configuration for MySQL Server 5.5.8 consisting of one master and one slave.
To configure asynchronous replication:
Edit the /etc/my.cnf file for both master and slave MySQL Server hosts as follows:
[mysqld] basedir = /opt/mysql/mysql datadir = /var/opt/sun/comms/davserver/db default-storage-engine = InnoDB character-set-server = utf8 transaction-isolation = READ-COMMITTED server-id=1 log-bin=mysql-bin innodb_flush_log_at_trx_commit=1 sync_binlog=1 binlog-format=ROW skip-name-resolve
Note the following:
server-id: The unique id for each server numbered greater than 0. (The master here is 1, the slave is 2.)
log-bin: Turns on binary logging for replication.
innodb_flush_log_at_trx_commit: Recommended.
sync_binlog: Recommended.
binlog-format: Must be ROW because of transaction-isolation level used in Calendar Server and MySQL Server 5.5.8.
skip-name-resolve: This is a workaround if you experience the following slave error:
ERROR 1042 (HY000): Can't get hostname for your address.
Do not set log-slave-updates.
Follow the replication procedures described in the "Replication" chapter of MySQL 5.5 Reference Manual to complete the configuration.
In this example, both nodes have a log-bin and do not have log-slave-updates. Some procedures in the "Replication" chapter might include log-slave-updates for specific purposes, but they have been left out from these instructions, assuming most of the time a failed node retains its data and does not need the log-slave-updates data on the other node. These instructions also assume that the binary log removal over time might indicate a node that lost its data, and so is not able to get all data from the other node's logs anyhow.
This section describes an example of a manually-controlled HA configuration. It is similar to the preceding asynchronous replication example, except that each node is set to be the slave of the other. Because both nodes have turned on binary logs, each node logs the data that comes to it. The configuration is the same as asynchronous (assuming the replication user exists on both nodes).
This is not very different from one-way synchronization considering that even in master-slave replication you can set up the master as a slave to the original slave when you are trying to resynchronize a failed master.
However, another way of using two-way replication is to assign specific client data to each node, and use the opposite node for the slave. The result is that each node replicates the other in parallel. It's important that any specific client data be assigned to only one master at a time or else inconsistencies might occur.
When managing MySQL Server replication, it is important to understand synchronization issues. If data arrives at one node in a different order as it does in a replicated node, replication might fail and halt.
For example, given one-way replication, imagine that the master receives data called "dog" and at the same time a client believing the master is not available sends "cat" to the slave. The master has "dog" in row 1, and the slave has cat in row 1. The data between the nodes has become inconsistent. When the slave receives "dog" from the master relay, it is not able to put it into row 1 as it exists on the master. The nodes become inconsistent and replication fails and halts.
MySQL Server has no internal mechanism to synchronize this automatically, and when the MySQL Server server comes up as an active slave, it accepts new connections and also tries to catch up with the master in parallel.
These issues need to be considered when executing manual replication procedures as well as when using automatic functions of any other program.
JDBC Connector/J for MySQL Server has various capabilities for load balancing and high availability. The example configuration in this section shows how to use JDBC Connector/J for MySQL Server for an automatic failover, assuming a manual resynchronization and failback. This example assumes the use of MySQL Server 5.5.8.
On the application server, make the following connector configuration:
Note:
The following information pertains to configuring the Calendar Server database. You might also want to configure the same for the iSchedule database if it exists on the same host.For GlassFish Server:
Enable connection validation
Enable any failure close all connection
Transaction isolation: Guaranteed read-committed
For WebLogic Server, see the discussion about using JDBC drivers with WebLogic Server in the Administering JDBC Data Sources for Oracle WebLogic Server.
Note:
Most of the JDBC data source recommended settings are available by default.The MySQL5.1.x connector is installed by default.
Use the following properties:
GlassFish Server:
user mysql password mysql URL jdbc:mysql://masterhost:3306,slavehost:3306/caldav autoReconnect true failOverReadOnly false autoReconnectForPools true roundRobinLoadBalance false secondsBeforeRetryMaster 2147483647 queriesBeforeRetryMaster 2147483647
WebLogic Server:
user mysql URL jdbc:mysql://masterhost:3306,slavehost:3306/caldav failOverReadOnly false roundRobinLoadBalance false secondsBeforeRetryMaster 2147483647 queriesBeforeRetryMaster 2147483647
For more information, refer to the "MySQL Server Connector/J" chapter on configuration parameters in MySQL 5.5 Reference Manual. The MySQL Server replication configuration is two-way with each node being both a master and slave to the other. The example shows a failure on master1, and master1 failing over to master2.
Note:
secondsBeforeRetryMaster and queriesBeforeRetryMaster are set to a very large value to prevent the application server from failing back to master1 once it has experienced a failover. This prevents new data from being written to master1 before master1 has had a chance to catch up. Otherwise, data might become inconsistent.Failover and recovering of this example works as follows:
Fail over.
You need a way to be alerted that a node has gone down. Once you are alerted, you must address the situation quickly to control when master1 comes back up and is resynchronzied and new connections are made to it.
Recover master1.
If master1's data is damaged or lost, you must reload master1 from master2 described in the MySQL Server replication notes.
Also, if master1 has data that did not make it to master2 before it failed over, it might be easier to reload master1. This situation is less likely if semi-synchronous replication is used.
You can check the binary log position on master1 with show master status, compared to master2's show slave status, to determine if master2 received all of master1's data without error.
Bring back master1.
At this point it, might be useful to shut down GlassFish Server to make sure that master1 does not receive new connections before it can resynchronize with master2. Other procedures are possible, but master1 needs to resynchronize before it receives new GlassFish Server connections.
Bring up master1.
Verify it has resynchronized with master2 by using show master status on master2 versus show slave status on master1.
Once master1 is caught up, you can restart the application server and it should return connections to master1, effectively failing back.
Note:
Be sure to verify, test, and refine any HA procedure before putting it into production.The test described in this section uses the following software and servers:
MySQL Server 5.5.32 Enterprise Version
JDBC Connector/J 5.1.5
Two MySQL Server servers named Master and Slave
Oracle Solaris 11
GlassFish Server 3.2.x
Oracle WebLogic Server 12.2.1.3
On host Master, create a user named mysql that has replication permission on Master. In this case, user mysql is the same user name that Calendar Server uses itself to connect to MySQL Server.
GRANT REPLICATION SLAVE ON *.* TO 'mysql'@'%';
On both hosts Master and Slave, edit the /etc/my.cnf config file as follows.
[mysqld] basedir = /opt/mysql/mysql datadir = /var/opt/sun/comms/davserver/db default-storage-engine = InnoDB character-set-server = utf8 transaction-isolation = READ-COMMITTED server-id=1 log-bin=mysql-bin innodb_flush_log_at_trx_commit=1 sync_binlog=1 binlog-format=ROW skip-name-resolve
Modify the application server and JDBC Connector/J configuration to fail over from Master to Slave.
For GlassFish Server:
The following is for the caldav database. Do the same for the iSchedule database.
DataSource Classname: com.mysql.jdbc.jdbc2.optional.MysqlDataSource Resource Type: javax.sql.DataSource Enable 'connection validation' Enable 'on any failure close all connection' transaction isolation: Guaranteed read-committed user mysql password mysql URL jdbc:mysql://Master:3306,Slave:3306/caldav autoReconnect true failOverReadOnly false autoReconnectForPools true roundRobinLoadBalance false secondsBeforeRetryMaster 2147483647 queriesBeforeRetryMaster 2147483647
For WebLogic Server:
When WebLogic Server is used to deploy Calendar Server, set the required parameters in WebLogic Server Administration Console as follows:
Log in to WebLogic Server Administration Console
Click Lock & Edit.
In the Domain Structure section, click the domain name. For example, domain1.
Navigate to Services and then Data Sources.
JDBC Datasources - defaultbackend and ischedulebackend are displayed in the Configuration tab
Select defaultbackend from the list and perform the following modifications:
Navigate to the Connection Pool tab.
Under Properties, add these parameters as key value pairs:
user mysql URL=jdbc:mysql://Master:3306,Slave:3306/caldav failOverReadOnly false roundRobinLoadBalance false secondsBeforeRetryMaster 2147483647 queriesBeforeRetryMaster 2147483647
Note:
password=password is not included in the list because the WebLogic Server documentation does not recommend to provide a password.Click ischedulebackend datasource. Follow the steps provided in step 3 to set the properties.
Ensure that the correct database name is entered in the properties list:
URL=jdbc:mysql://Master:3306,Slave:3306/ischedule
Click Activate Changes.
Restart WebLogic Server.
Ensure that WebLogic Server Administration or Managed Server logs do not show any errors.
Note:
secondsBeforeRetryMaster and queriesBeforeRetryMaster are set to a high value to prevent the application server and Connector/J from failing back if the master were to come back up.Initialize both hosts Master and Slave.
Run the following command, if the servers were previously functioning as a slave:
stop slave;
Remove all Calendar Server data from both hosts so that the databases are synchronized.
For example:
davadmin db init -H localhost -t mysql -u mysql -d caldav davadmin db init -H localhost -t mysql -u mysql -d ischedule
Substitute your names for caldav and ischedule.
Caution:
These commands completely remove the calendar data.On both Master and Slave, run the following command:
reset slave;
On both Master and Slave, run the following command:
reset master;
Set up Slave to be synchronized with Master.
Run the following command on Master:
show master status; File = mysql-bin.000001 Position = 107
Note the File and Position to set parameters on Slave.
Run the following command on Slave:
CHANGE MASTER TO MASTER_HOST='Master', MASTER_USER='mysql', MASTER_PASSWORD='mysql', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
Start Slave:
start slave;
Restart the application server to load Calendar Server.
Verify both Master and Slave.
use caldav; select count(*) from Resources;
There should be one row after the application server starts.
Run data through Calendar Server (that is, use Calendar Server to create or change events, and so on, to cause calendar data to be stored).
Verify that data is accumulating on both Master and Slave.
For example, use the following MySQL commands to look at data in the tables:
use caldav; select * from Resources;
You can also use this command:
select count(*) from Resources;
You can also use the following status commands:
show master status; show slave status;
Stop Master and observe the failover.
The failover could take up to 60 seconds or more, as the connections time out.
Use the following command to observe that Slave is continuing to operate:
select count(*) from Resources;
The test described in this section uses the following software and servers:
MySQL Server 5.5.32 Enterprise Version
JDBC Connector/J 5.1.5
Two MySQL Server servers named Master1 and Master2
Oracle Solaris 11
Oracle GlassFish Server 3.2.x
Oracle WebLogic Server 12.2.1.3
On both hosts Master1 and Master2, create a user that has replication permission.
In this case, user mysql is the same user name that Calendar Server uses itself to connect to MySQL Server.
Run the following command both Master1 and Master2.
GRANT REPLICATION SLAVE ON *.* TO 'mysql'@'%';
On both hosts Master1 and Master2, edit the /etc/my.cnf config file as follows.
[mysqld] basedir = /opt/mysql/mysql datadir = /var/opt/sun/comms/davserver/db default-storage-engine = InnoDB character-set-server = utf8 transaction-isolation = READ-COMMITTED server-id=1 log-bin=mysql-bin innodb_flush_log_at_trx_commit=1 sync_binlog=1 binlog-format=ROW skip-name-resolve
Modify the application server and JDBC Connector/J configuration to fail over from Master1 to Master2.
GlassFish Server:
The following is for the caldav database, you should also do the same for the iSchedule database.
DataSource Classname: com.mysql.jdbc.jdbc2.optional.MysqlDataSource Resource Type: javax.sql.DataSource Enable 'connection validation' Enable 'on any failure close all connection' transaction isolation: Guaranteed read-committed user mysql password mysql URL jdbc:mysql://Master1:3306,Master2:3306/caldav autoReconnect true failOverReadOnly false autoReconnectForPools true roundRobinLoadBalance false secondsBeforeRetryMaster 2147483647 queriesBeforeRetryMaster 2147483647
For WebLogic Server:
When WebLogic Server is used to deploy Calendar Server, set the required parameters from WebLogic Server Administration Console as follows:
Log in to WebLogic Server Administration Console.
Click Lock & Edit.
In the Domain Structure section, click domain name. For example, domain1.
Navigate to Services and then Data Sources.
JDBC Datasources - defaultbackend and ischedulebackend are displayed in the Configuration tab.
Select defaultbackend from the list and perform the following modifications:
Navigate to the Connection Pool tab.
Under Properties, add these parameters as key value pairs:
user mysql URL jdbc:mysql://Master1:3306,Master2:3306/caldav failOverReadOnly false roundRobinLoadBalance false secondsBeforeRetryMaster 2147483647 queriesBeforeRetryMaster 2147483647
Note:
password=password is not included in the list because the WebLogic Server documentation does not recommend to provide a password.Click ischedulebackend datasource. Follow the steps provided in step 3 to set the properties.
Ensure that the correct database name is entered in the properties list:
URL=jdbc:mysql://Master:3306,Slave:3306/ischedule
Click Activate Changes.
Restart WebLogic Server.
Ensure that errors are not seen in WebLogic Server Administration or Managed Server logs.
secondsBeforeRetryMaster and queriesBeforeRetryMaster are set to a high value to prevent the application server and Connector/J from failing back if the master were to come back up.
Initialize both hosts Master1 and Master2:
Run the following command:
stop slave;
Remove all Calendar Server data from both hosts so that the databases are synchronized. For example:
davadmin db init -H localhost -t mysql -u mysql -d caldav davadmin db init -H localhost -t mysql -u mysql -d ischedule
Substitute your names for caldav and ischedule.
Caution:
These commands completely remove the calendar data.On both Master1 and Master2, run the following command:
reset slave;
On both Master1 and Master2, run the following command:
reset master
Run the following command to verify file and position on each master:
show master status; both show: mysql-bin.000001 107
Run the following commands to set each master to be a slave to the other:
On Master2:
CHANGE MASTER TO MASTER_HOST='Master1', MASTER_USER='mysql', MASTER_PASSWORD='mysql', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
On Master1:
CHANGE MASTER TO MASTER_HOST='Master2', MASTER_USER='mysql', MASTER_PASSWORD='mysql', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
Start slave connection on both hosts Master1 and Master2:
start slave;
Restart the application server to load Calendar Server.
Verify both hosts Master1 and Master2:
use caldav; select count(*) from Resources;
There should be one row after the application server starts.
Run data through Calendar Server (that is, use Calendar Server to create or change events, and so on, to cause calendar data to be stored).
Verify that data is accumulating on both Master and Slave.
For example, use the following MySQL commands to look at data in the tables:
use caldav; select * from Resources;
You can also use this command:
select count(*) from Resources;
You can also use the following status commands:
show master status; show slave status;
Stop Master1 and observe the failover.
The failover could take up to 60 seconds or more, as the connections time out.
Use this to observe the slave continuing:
select count(*) from Resources;
Stop the application server (to stop incoming client connections and data).
Bring Master1 online and allow it to sync as a slave to Master2.
Verify that Master1 synced as a slave to Master2.
On Master1:
show slave status
Make sure that there are no errors and note the slave position, for example, mysql-bin.000001 1827176.
On Master2:
show master status mysql-bin.000001 1827176
Verify this position with the one that you noted on Master1.
Verify that Master2 is still synced with Master1.
On Master2:
show slave status
Make sure that there are no errors and note the slave position, for example, mysql-bin.000002 107.
On Master1:
show master status mysql-bin.000002 107
Verify this position with the one that you noted on Master2.
Verify Row Count on both Master1 and Master2 by comparing the count from each machine:
select count(*) from Resources;
Start the application server and start incoming client data.
Verify That Master1 is in action again, and that Master2 is following.
On Master1:
show master status;
Verify that the position is increasing as master.
On Master2:
show master status;
Verify that the position is not increasing.
On both Master1 and Master2:
select count(*) from Resources;