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.
Two MariaDB Servers
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=masterOn 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.sqlYou 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.sqlEdit the second servers my.cnf file, and add the following lines under the [mysqld] banner:
server_id=2 super_read_only = 1Restart the second database server. On the master server, get the current position and log name with the following command:
SHOW MASTER STATUSGo 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/ignitionClick 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.