MySQL Backup Policy

Dear community,

I’ve transferred a CE installation from sqlite3 to a MariaDB 10 database that is run on a Synology NAS. For backup purposes, I’d like make a copy of the MariaDB database daily at 3am.

Synology will temporary shut down the MariaDB server during the backup, which takes about ten seconds. For me, it is no problem at all if the the seahub website is down for a couple of seconds every night.

I foresee to have large transfers towards Seafile by desktop clients, in the order of hundreds of GBs overnight. During the migration I learned that seafile also has a database present in the MySQL.

Can the temporary shutdown of the MariaDB server during the backup lead to any data integrity problems on the storage? Or is such a backup policy safe to do?

Kind regards,

~Mart

Hello Martin,

The MariaDB database is used by Seafile and Seahub. So if you shut your database down, I don’t think you will be able to transfer anything :slight_smile:

If you want to backup you database, which is a wise thing to do, you should simply dump it using mysqldump (included with MariaDB). This should allow you to backup the database dump without interrupting your Seafile services.

I hope this will help you !

Dear Romain,

Thanks for your reply. And do you, by chance, happen know what happens with the on-going transfers when the mysql is shut down? Will they be damaged or can Seafile handle this and, e.g., instruct the client to resend the file that was at that moment being sent?

If it’s not yet present it might be a good stability feature for the back log. :slight_smile:

When possible I try to stay as close to the Synology DSM defaults as possible for easy long time maintenance. So if the client at reconnect safely continues with the current files, that’s something I don’t consider as a major problem.

For future readers: when using mysqldump, don’t forget the flag --single-transaction.

Kind regards,

~Mart

Martin,

No I didn’t try stopping the database… But I am quite confident that it would crash !

Best regards

The Manual recommends mysqldump as @RomainC suggests:

mysqldump -h [mysqlhost] -u[username] -p[password] --opt ccnet-db > /backup/databases/ccnet-db.sql.`date +"%Y-%m-%d-%H-%M-%S"`

mysqldump -h [mysqlhost] -u[username] -p[password] --opt seafile-db > /backup/databases/seafile-db.sql.`date +"%Y-%m-%d-%H-%M-%S"`

mysqldump -h [mysqlhost] -u[username] -p[password] --opt seahub-db > /backup/databases/seahub-db.sql.`date +"%Y-%m-%d-%H-%M-%S"`

For those using the Pro version, however, there is a real-time backup tool as well.

I suppose if you are willing (and able) to stop your server you could do a backup using rsync and even grab an occasional bare metal image.

There are a few previous discussions on backup that you might want to reference. Some examples are here and here.

Thanks, gentlemen! In that case I shall create a cron job running a bash script containing the three mysqldump commands mentioned.

Also leaving this link for fellow Synology users, for how to mysqldump: beatificabytes.be/schedule-a-backup-of-all-your-mysql-databases-on-synology/