Start a slave db from a backup of the master Linode?
If I spin up a new Linode from a backup, is a database (InnodB) which was running on the original server consistent?
The real goal I want to achieve is to create a slave MySQL server (actually MariaDB) for an existing master which is over 100GB in size. If I can skip the mysqldump, it will spare my server some noticeable downtime, so I'd like the option of telling my new slave, "Just use your existing values of MASTER_LOG_FILE and MASTER_LOG_POS and data when you start replicating."
But will its MASTER_LOG_POS be consistent with its data?
I see from the Backup Service page that backups are "file-based," so I wonder whether this is a snapshot of an instance in time or whether a db transaction might have completed after the backup started but before it ended.
2 Replies
mjones
Linode Staff
Hi @pinneydevs! I'd always recommend using mysqldump or similar for database backups for the exact reason you imagine. A backup can start before a transaction completes and will backup the data exactly as it is with the transaction in progress. This won't happen every time, but it has a chance of happening during any backup.
One thing you could try doing is halting all transactions during your backup window, but that would be more downtime than running mysqldump in most cases.
Just chiming in here, mysqldump has an option for transactions to make sure it has a consistent backup, and to include the master log/position within the backup, so restoring the backup with this script should give you a fully consistent slave ready to go.
I believe MariaDB also has some hot backup options that allow you to take a file-system level backup which may also help achieve what you need, but I have never needed to use it.