Using SQL Replication with Ignition

Posted
Comments None



In the Inductive Automation videos/manuals, they mention using a database solution to replicate your data. To do that, you could use a managed database service, such as AWS RDS. Or if you want the database to be local, then I will explain one method of replicating the data between two servers. I’m not going to go into the details of how this works, this is more of a reference to get it up and running quickly.

Requirements:
Two MariaDB Servers
MariaDB Connector/J
Ignition Server

If you are going to do this to a live system, I recommend you run through it once on a practise system first, to get the hang of it. I have not tried using the included mysql connector/j in ignition with replication, only the mariadb driver. If you are currently using MariaDB, you probably have this, if not you can get it here. To install it, on the left menu, click Drivers, under the DATABASES section. Scroll to the bottom of that page, and click “Create new JDBC Driver…”. Enter the name, and description. Under class name enter
org.mariadb.jdbc.Driver
. Under choose file, select the jar file that you just downloaded. Under driver type select
 MySQL
. Under Url format write
jdbc:mariadb://host:port/database
. At the bottom, select the default translator to be
MYSQL
. Click Create New JDBC Driver. Now add a connection to the database using that driver. For reference to create a database, and add a user with permissions to that database, type the following.
CREATE database 'ignition';
CREATE USER 'ignition_db'@'localhost' IDENTIFIED BY 's3cr3t';
GRANT ALL ON ignition_db.* TO 'ignition_db'@'localhost';
There is more than one way to write that. Change localhost to the hostname or ip address of the ignition client. You could use the wildcard % to allow access from any host, but it is alot more secure to specifically identify the host name. On this primary database server, you need to open my.cnf and add the following lines, under the [mysqld] banner.
server_id=1
log-bin
log-basename=master
On Oracles MySQL Community edition, it wouldn’t recognise the log-basename item. If you are on that platform, then just exclude that line, and use whatever logname is shown in the show master status command. Now restart the server, and verify that it came up, you may need to type journalctl -xe to see what you mistyped if it doesn’t come back up. We need to add a replication user, so that the second database server can connect, in order to stay up to date.
CREATE USER 'replication'@'secondServer' IDENTIFIED BY 's3cr3t';
GRANT REPLICATION SLAVE ON ignition.* TO 'replication'@'secondServer';
If this is a live environment, to get maximum data integrity, you will have to lock the tables, then export the database, re-import it, enable the replication, then unlock the tables. Because ignition has the store and forward system, it should be possible for you to do this without losing current data, but if you make a mistake, or you are slow, you could lose some data. To lock the tables on the first server, type.
FLUSH TABLES WITH READ LOCK;
If you typed that on a live system, you should keep the ignition store and forward status page open, to ensure you don’t overfill the store and forward system. No more writes will be allowed to that database server until you type:
UNLOCK TABLES;
Assuming that you are not following this guide step by step, and you have previously added a database, and used it with ignition. You will need to export the database from the first server and import it again to the second server. If you have shell access to the first server, then export the database (called ignition) as follows. This is where the read lock is nice, because the database is frozen during the move.
mysqldump ignition > ignition.sql
You may need to add some options, if you do, read about them here. Go to the second server, and import that database.
 
mysql -u root -ppassword < ignition.sql
Edit the second servers my.cnf file, and add the following lines under the [mysqld] banner:
server_id=2
super_read_only = 1
Restart the second database server. On the master server, get the current position and log name with the following command:
SHOW MASTER STATUS
Go back to the second server enter this command, using the log file and position from the above command.
CHANGE MASTER TO
MASTER_HOST='firstServer',
MASTER_USER='replication',
MASTER_PASSWORD='s3cr3t',
MASTER_PORT=3306,
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=123456,
MASTER_CONNECT_RETRY=10;
Then type:
START SLAVE;
Go back to the first server and unlock the tables with
UNLOCK TABLES;
Now go to the ignition server, and verify the database is still connected, and working. On the first server you can use the “SHOW MASTER STATUS”, and compare the position to output from “SHOW SLAVE STATUS” on the slave server. If they match, you are replicating. On ignition, we will change the database connection to automatically fail over to the slave server. I added the option to make the slave read only, because if I don’t do that, any changes made to the slave will not be updated back to the master. If the main server goes down, we will not lose any data, and could make a script to bring up a new main server with a copy of the data from the secondary automatically. While it is down, ignition will need to rely on the store and forward system to capture all the data. On ignition, under the database driver, type the Connect URL as follows.
jdbc:mariadb:replication://firstServer:3306,secondServer:3306/ignition
Click save changes. Now go shutdown your primary database server, and use ignition to request database data. If everything works, you will still have access to all of the data. Start the primary server back up, and you should see the store and forward system move everything to the primary server.

I have done a bit of testing with this, and have noticed a few issues, so use at your own risk. The problem I see, is if you use database write queries that do not run through the store and forward system, then ignition will hang up, while waiting to hear back from the driver that the write went through. It does eventually timeout and you can keep using the application. After a few minutes, ignition does realize the primary database failed, and stops trying read queries (even though they still work from the slave). To overcome that limitation, a multiple master replication system could be used.
I think the positives outweigh the negatives, since the data is all still there to read with no service disruption if the primary database fails.

A troubleshooting note. If you see MySQL saying you need to increase your maximum allowed message size, then make sure you used the correct log name.

Author

Comments

There are currently no comments on this article.

Comment

Enter your comment below. Fields marked * are required. You must preview your comment before submitting it.





← Older Newer →