Why store /var/lib/mysql when you can just create SQL dumps for backup purposes?

Posted on

Question :

Tools like mariabackup offer the possibility to create full and incremental file system backups of /var/lib/mysql but I don’t quite understand what additional safety layer this would give me if I already create regular SQL dumps.

Is it sufficient to keep daily SQL dumps (created using mysqldump) for backup purposes? What would be advantage of additionally having (incremental and full) file system backups of /var/lib/mysql? What worst case scenarios are there without file system backups of the database directory?

Answer :

For large databases, recovery is faster when the data dir is backed up as files. When an SQL dump is restored, all the data is put back by using INSERT statements, which involves parsing the queries, converting data to the storage format, updating indexes etc.

When restoring the raw database files, one simply needs to copy the files and start the server.

Leave a Reply

Your email address will not be published. Required fields are marked *