0

This is the content of my Databases directory:

total 201M
drwxr-xr-x 23 systemd-coredump systemd-coredump 4.0K May 27 02:07 .
drwxr-xr-x  9 root             root             4.0K Jun  1 10:20 ..
drwx------  2 systemd-coredump systemd-coredump 4.0K May 12 16:31 Accounts
drwx------  2 systemd-coredump systemd-coredump 4.0K May 12 16:31 Aggregates
-rw-rw----  1 systemd-coredump systemd-coredump  17M May 31 07:03 aria_log.00000001
-rw-rw----  1 systemd-coredump systemd-coredump   52 May 31 07:03 aria_log_control
drwx------  2 systemd-coredump systemd-coredump 4.0K May 12 16:31 Blog
drwx------  2 systemd-coredump systemd-coredump 4.0K May 12 16:31 Configuration
drwx------  2 systemd-coredump systemd-coredump 4.0K May 26 10:07 Contacts
drwx------  2 systemd-coredump systemd-coredump 4.0K May 26 10:11 Contents
-rw-r--r--  1 systemd-coredump systemd-coredump  509 Feb 26 10:11 db.sultanimmubin.ir.conf
-rw-rw----  1 systemd-coredump systemd-coredump    9 May 21 02:05 ddl_recovery-backup.log
-rw-rw----  1 systemd-coredump systemd-coredump    9 May 27 02:06 ddl_recovery.log
-rw-r--r--  1 systemd-coredump systemd-coredump 1.2K Feb 26 10:11 docker-compose.yml
drwx------  2 systemd-coredump systemd-coredump 4.0K Apr 12 14:54 Entities
drwx------  2 systemd-coredump systemd-coredump 4.0K May 12 16:31 Forms
drwx------  2 systemd-coredump systemd-coredump 4.0K Apr 12 14:51 Galleries
drwx------  2 systemd-coredump systemd-coredump 4.0K May 26 10:06 Geo
drwx------  2 systemd-coredump systemd-coredump 4.0K May 12 16:31 Globalization
-rw-rw----  1 systemd-coredump systemd-coredump 7.6K May 27 02:01 ib_buffer_pool
-rw-rw----  1 systemd-coredump systemd-coredump  76M May 27 02:01 ibdata1
-rw-rw----  1 systemd-coredump systemd-coredump  96M May 31 07:05 ib_logfile0
-rw-rw----  1 systemd-coredump systemd-coredump  12M May 27 02:07 ibtmp1
drwx------  2 systemd-coredump systemd-coredump 4.0K May 26 10:08 Media
-rw-rw----  1 systemd-coredump systemd-coredump    0 Feb 26 09:57 multi-master.info
drwx------  2 systemd-coredump systemd-coredump 4.0K Feb 26 09:57 mysql
-rw-r--r--  1 systemd-coredump systemd-coredump   15 Feb 26 09:57 mysql_upgrade_info
drwx------  2 systemd-coredump systemd-coredump 4.0K May 12 16:31 Navigation
drwx------  2 systemd-coredump systemd-coredump 4.0K Feb 26 09:56 performance_schema
drwx------  2 systemd-coredump systemd-coredump 4.0K May 12 16:31 Seo
drwx------  2 systemd-coredump systemd-coredump 4.0K May 12 16:31 Social
drwx------  2 systemd-coredump systemd-coredump  12K Feb 26 09:56 sys
drwx------  2 systemd-coredump systemd-coredump 4.0K May 12 16:35 Taxonomy

As you can see, any directory that starts with uppercase is my own database. I don't care about logs at all as long as they are already inserted into the corresponding tables. And I don't care about MariaDB's infrastructure at all.

I want to backup files using a file-based backup plan. It's a script that uses azcopy from Microsoft Azure to put directories and files into the cloud.

Yet for a simple server with less than a thousand records, it consumes 200 MBs.

What files should I backup in the cloud to be able to restore later, other than my database directories?

Update The reason I'm asking for this is because of cloud costs. We have a program that backs up many files to the cloud, and for the database, it backs up the whole Databases directory. This causes our bill to increase dramatically. Because we are a web design agency and we have hundreds of customers and if we calculate, we upload tens of Gigabytes of data each night for database backup.

Improve this question
8
  • The ib_logfile0 is used during crash recovery to ensure data integrity. Without it, you're saying you're ok with potential data loss / corruption?
    – J.D.
    Commented Jun 1, 2023 at 12:09
  • Do not lose ibdata1 -- that's where all your data is.
    – Rick James
    Commented Jun 1, 2023 at 17:44
  • @RickJames, do you mean for a simple database MariaDB store 76 MBs of data? That seems extremely inefficient.
    – Saeed Neamati
    Commented Jun 2, 2023 at 6:02
  • 2
    In general, it is not a good idea to use file-based backup for databases, MySQL or not. It only works reliably if 1) you have a full snapshot of the data directory and 2) you suspend all database updates while the backup is being taken. I suggest you use the database backup tools for your DBMS.
    – mustaccio
    Commented Jun 2, 2023 at 13:32
  • 2
    @SaeedNeamati "And if in theory all of the data is in those database files, and they are not dependent upon anything else, then why not?" - To mustaccio's point, an operation may be actively running, and even updates the state of one file but not the other dependent files, during the same time you take a file-based backup, leaving you with an inconsistent set of the files in the backup.
    – J.D.
    Commented Jun 2, 2023 at 17:34

1 Answer 1

Reset to default
1

Note in the official documentation that there's no pick and choose on the suitable files to backup.

Note also that backups of files require a) mariadb syncronisation and b) an atomic snapshot of the filesystem.

For a few 1000 rows it seems like a logical backup would be smaller:

 mariadb-dump --flush-privileges --all-databases --routines --events --single-transaction | gzip -c > dump.sql.gz

And the other thing about backups, test a restore, people ask for more restores than backups.

Improve this answer
2
  • For a databases directory that is 228 MBs, this resulted in a file that is only 1.2 MBs. This is amazing. How can I restore that file on another instance of MariaDB to ensure that it works correctly?
    – Saeed Neamati
    Commented Jun 7, 2023 at 4:52
  • zcat dump.sql.gz | mariadb. The mariadb can be appended with host/socket authentication to the other instance. The other instance can also be started with --skip-grant-tables as the dump will populate the entire instance.
    – danblack
    Commented Jun 7, 2023 at 5:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.