How-to
Seed a large Ignition historian database for replication
Short answer
Stage the backup database by closed partition, not in one 88 GB dump. The measured 4.5 GB in 40 minutes gives 6.75 GB/h, so the whole history is about 13 hours of dumping, but a weekly 2 GB partition is an 18-minute unit you can restore and verify before starting MySQL replication from that aligned state.
On this page
- How long will seeding the full 88 GB historian database take?
- Why can't replication start until both databases match?
- How do I find which partition tables to dump?
- Should I dump the whole database or stage closed partitions?
- How do I script a dump when the table name comes from a query?
- What else does the seed need besides the data tables?
- How do I verify the seed and start replication from it?
- Questions asked
Stage the seed partition by partition instead of moving the whole database in one dump. On the installation that measured it, a 4.5 GB table dumped in 40 minutes, which puts an 88 GB historian at about 13 hours of dump time alone. Closed weekly partitions of roughly 2 GB cut that into eighteen-minute units you can dump over several nights, restore, verify, and then hand to MySQL replication as the aligned baseline both servers need before changes can flow.
How long will seeding the full 88 GB historian database take? #
About 13 hours of dump time alone: the 4.5 GB test table finished in 40 minutes, which is 6.75 GB/h, and the full database at that rate is 13.0 h before a single byte is restored or moved off the server.
Run the arithmetic on the measurement you have:
4.5 GB ÷ 40 min = 0.1125 GB/min
0.1125 GB/min × 60 min/h = 6.75 GB/h
88 GB ÷ 6.75 GB/h = 13.0 h
That rate covers the dump step only. Restore on the backup server runs at its own speed, usually slower than the dump, so time one restore of the same test table before you promise a window. If a weekend holds the measured dump plus the measured restore plus margin, one consistent full-database copy is the simplest baseline. If it does not, stage the seed.
| Copy unit | Observed size | Dump time at 6.75 GB/h |
|---|---|---|
| Monthly partition | about 8 GB | about 71 min |
| Weekly partition | about 2 GB | about 18 min |
| Whole database | 88 GB | about 13 h |
The monthly and weekly sizes are what one installation observed after it changed its partition setting; they are inputs to this arithmetic, not a rule about what a partition weighs.
Why can't replication start until both databases match? #
Because replication copies changes forward from a start position, not history: MySQL's model has a source ship changes to replicas, and a replica can only apply them correctly if it already holds the source's data as it stood at that position.
That is the requirement in the MySQL Reference Manual's replication documentation: give the replica a copy of the source's data, then start replication at the position that copy corresponds to. Change capture in general has the same shape — a baseline snapshot first, incremental changes after, and the snapshot must line up with where the change stream begins.
An empty backup server with replication enabled will faithfully receive new writes and stay wrong about everything older, so pick the baseline as one of two shapes and record which one you took: a full consistent snapshot taken with the engine's backup method, or a staged set of closed partitions restored on the backup server as the state replication starts from.
How do I find which partition tables to dump? #
Read them from sqlth_partitions: SELECT pname FROM sqlth_partitions ORDER BY end_time DESC LIMIT 1; returns the partition with the newest end_time, and a current epoch seconds value times 1000 falls between the start and end bounds of whichever partition is open now.
Treat the newest row as discovery, not as a decision. Ignition's historian splits data into separate tables on a time setting and prunes old ones, and the partition that suits a static dump is a closed one: its end_time is in the past for the gateway you are backing up, so no writes are landing in its table while you copy it. The bounds are epoch milliseconds, which is why the seconds × 1000 conversion is part of the comparison. When more than one gateway stores to the same database, sort out which rows belong to yours before you trust the query, or it will hand you another gateway's table.
Partition length and the pruning that eventually deletes old partitions are configured together, so configuring Ignition pruning for old data partitions belongs in the same planning step: it sets how long the seed has before the source starts deleting what you have not copied yet.
Should I dump the whole database or stage closed partitions? #
Stage them: the total bytes do not change either way, but a weekly partition near 2 GB dumps in about 18 minutes, so an interruption costs you one unit instead of the whole run.
A closed partition is a stable unit — no historian writes are landing in it, so a plain table dump is a consistent copy of that unit without any special snapshot mode. The open partition is the opposite: it is being written while you read it, so either exclude it and let replication deliver that time range from the aligned start position, or take it with the engine's consistent backup method rather than a bare table dump. Monthly units near 71 minutes each also work; weekly units simply retry faster.
Watch the pruning horizon while the seed runs: if pruning is tight, a partition can disappear from the source before it is ever seeded, and why two Ignition historian connections share one pruning boundary covers how that boundary behaves when more than one connection writes to the same database. The decision line is the window: hold a maintenance window that fits measured dump plus measured restore plus margin, and take one full consistent copy; otherwise stage closed partitions and treat the open one as replication's first delivery.
How do I script a dump when the table name comes from a query? #
Split it into two steps in the script: query sqlth_partitions for the name, then hand that name to the database's dump tool, because plain SQL cannot use a query result as a table name outside stored procedures.
The workflow that reported this runs on a Linux server, where a scheduled cron job executes a Python script that connects with mysql.connector, reads the closed partition's name, and calls the dump tool through subprocess for that table and its associated data tables, keeping the dump where the source server is not its only holder. Keep that separation whatever the engine: metadata lookup, dump invocation, then an explicit check that the dump file exists and is non-empty before the step is called done. Validate the returned name against the expected partition name pattern before it reaches a command line — a database-returned string concatenated blindly into a shell command executes whatever it contains.
What else does the seed need besides the data tables? #
The matching sqlth_partitions rows and the associated tables the historian keeps beside the data: a data table copied alone holds values that nothing on the destination can resolve into history, because the partition record is what places it in time.
The reported script backs up the last closed partition with its associated data tables, excluding the latest created partition — and that exclusion is a recorded gap, not an omission to forget. The copy is deliberately behind the source for the open partition's range, so the replication start position has to cover that range or a later dump has to close it. Before the first staged run, list the schema and confirm which tables sit beside the data partitions, because a partition name identifies one table, not its dependencies.
How do I verify the seed and start replication from it? #
Restore every unit on the backup server, confirm the tables and row counts landed, then configure replication so its start position corresponds to the seeded state, and prove the pair with writes that arrive after you start.
- Measure one dump and one restore of a representative partition, then compute the window with the arithmetic above.
- List the closed partitions from sqlth_partitions, scoped to the gateway that owns them.
- Dump each closed partition together with its sqlth_partitions row and associated tables, and keep the dump off the source server.
- Restore each unit on the backup server and check the expected tables and row counts before starting the next.
- After the restores, check tag browsing on the destination; restores can leave stale entries, which stale historian tag browse tree entries after a restore works through.
- Configure replication from the state you seeded, following the engine's documented procedure for setting the start position.
- Write new values at the source and confirm they arrive at the replica, then compare row counts on both sides.
If a unit's dump fails or a restore comes up short, repeat that unit before advancing the baseline. A misaligned pair is the quiet failure: replication reports healthy while the second database is missing history, the same class of gap that what Ignition historian failover leaves behind in the second database walks through. Keep the whole plan in one document — initial seed, per-partition transfers, the recorded gap for the open partition, and the replication start point.
Questions asked
Can I run the dump from Ignition scripting instead of an OS script?
Keep the dump outside the gateway. A cron job or a scheduled task calling the database's dump tool runs whether the gateway is up or not, keeps the long disk read out of the process serving the plant, and is straightforward to retry. Gateway scripting can read the partition metadata, but the dump itself belongs to database tooling and the OS scheduler.
Two gateways write to one historian database. Does the partition query still work?
Yes, but scope it first. sqlth_partitions carries records for every gateway storing to that database, so ordering by end_time and taking the first row can return another gateway's partition. Filter the rows to the gateway whose data you are seeding, then apply the seconds times 1000 comparison to those rows only, and confirm the table you dump belongs to that gateway.
How often should partition dumps run after the first seed?
Match the cadence to the partition length. With weekly partitions of about 2 GB, one new partition closes each week and dumps in roughly 18 minutes, so a weekly cron run keeps the backup current for about three quarters of an hour a month. The first seed is the long job; steady state is one unit per period.
Does replication make the dumps unnecessary?
No. Replication copies changes as they happen, including the bad ones: a mistaken delete or a bad write propagates to the second server as faithfully as a good one. The staged dumps remain your independent copy taken at a point in time you chose, which is exactly what a live replication stream cannot give you.