Learn about Centmin Mod LEMP Stack today
Become a Member

Sysadmin MariaDB time based backups

Discussion in 'System Administration' started by pamamolf, Sep 28, 2016.

  1. pamamolf

    pamamolf Premium Member Premium Member

    May 31, 2014
    Local Time:
    9:58 AM
    MariaDB 10.3.x

    I was wondering what kind of backup solutions exist for MariaDB to avoid drop existing database and import again as that on a big database will need a lot of time ?

    Looking for solution one to restore to an hour before in anyway if it is possible and for solution two to do a full backup faster than importing....

    Thanks :)
  2. cloud9

    cloud9 Active Member

    Oct 6, 2015
    Local Time:
    7:58 AM
  3. eva2000

    eva2000 Administrator Staff Member

    May 24, 2014
    Brisbane, Australia
    Local Time:
    4:58 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    dbbackup.sh scheduled cron can somewhat do it but for finer grain control you need to enable binary logging (ALOT of extra disk space consumed !) and do point in time recovery MySQL :: MySQL 5.6 Reference Manual :: 7.5 Point-in-Time (Incremental) Recovery Using the Binary Log

    you can only recovery what the binary log has recorded, so you can't go far back so still need a full backup of mysql database(s) to restore initially and then use binary log to roll back to specific point in time where difference in time is between the time the full backup was made and the time you want to roll back from.

    So if you made full backups every 24hrs and had binary logging enabled >24hrs ago. You'd restore your last full backup which is 24hrs old and then replay and recover the last 24hrs of binary logs to bring your database up to date or if you want to restore to 1hr ago, restore full backup 24hrs old and replay and recover the last 23hrs of binary logs to bring database state to 1hr ago. By default, binary log records all databases data on the server by default so you need full backups of all databases on the server and restore all databases on server before rolling back via binary logs.

    Alot of disk space will be used as you need space to store binary logs and you also need to backup the binary logs + mysql full database backups as well. Disk space depends on how long you want to keep the binary logs for too which you can configure. I'd make room for at least 6-10x times more disk space that the total dataset size of ALL mysql databases on your server. So if you have 20GB of databases, make room for 200GB of free disk space at least.

    Also recommended practice is to have backup of binary logs off server in remote location, so you'd need a 2nd server for that. So yes 2x 200GB extra disk space servers for the source data server and binary log backup server

    So read the official mysql documentation ;)
    Last edited: Sep 28, 2016
    • Informative Informative x 1