maria-discuss team mailing list archive

[Gordan Bobic <gordan.bobic@xxxxxxxxx>]

On Tue, Aug 2, 2022 at 2:47 PM Cédric Counotte
<cedric.counotte@xxxxxxxxxx> wrote:
>
> I will partially respond to your questions:
>
> -skip-lock-tables, avoids locking the table when dumping them.
>         This means if you are on production servers, the dump won't freeze/slow your activity, however the dump may be incomplete or not reflecting an atomic state.
>
> -single-transaction
>         This as far as I know is only the way the INSERT commands will be written to the dump. With multiple inserts or a single one.

Not quite. --skip-lock-tables dumps the data without locking the
tables. You are telling MariaDB that you don't care if the tables are
mutually consistent during the dump.
The --single-transaction option means that it will grab a brief
metadata lock and start a transaction, and then dump everything from
within that transaction, thus ensuring that the dump is coherent and
internally consistent, while still avoiding long table locking.

> As for mariabackup, I don't think the prepare command needs access to the actual source folder, as everything has been copied. The prepare seems to replace the last bin log, but you might want to seek for someone else confirmation.
>
> FWIW, I stopped using mariabackup for backup (using it to start replication only) because restoring a single DB (we have 300+) or a single table seems highly cumbersome because you'll have to restore the entire backup to a working MariaDB server, and then dump/import the tables/DB you need onto your production server. Had to do it once, never again. So now I'm using plain mysqldump for which I easily control the output and how I'll be able to retrieve a DB or single table!

Unfortunately, mysqldump/mariadbdump are completely useless for big
databases. Anything more than about 50GB is just too painfully slow.
There are usually better ways to achieve this with well thought out
infrastructures unless your database sizes are very small.

> ** mariabackup
>
> We have a setup that stores the binary logs under /var/log/mysql (set up with the log_bin configuration option) and the databases themselves under /var/lib/mysql
>
> Suppose I call mariabackup to make a backup of the database as follows (some options obmitted):
>
>   mariabackup --backup --target-dir=my_backup_dir
>
> Could you tell me if calling
>
>   mariabackup --prepare  --target-dir=my_backup_dir
>
> needs access to the binary logs stored under /var/log/mysql or if mariabackup made a copy of all that it needs in my_backup_dir?

No, it only needs access to the what is in the backup directory. What
--prepare does is applies the redo log changes it buffered up during
the backup to make the backup set fully consistent.
There are no circumstances under which mariabackup will need access to
binary replication logs.