Comments - Full Backup and Restore with Mariabackup

Is it really necessary to stop the MariaDB Server process before restoring files into the datadir, even if that directory is not empty (expecting to only add new files inside it, and not override any existing file of course)?

I mean, the MariaDB Server process seems to scan the datadir contents only on start.
If new files are copied into it while the process is running, they don’t seem to be taken in account before the process is restarted.

Not needing to stop the server (and rather just restart it at end) should be a big advantage in terms of server unavailability time.

And my own tests running that way didn’t raise any problems doing that.

But using only MyISAM tables, I admit.

It should be noted that parent directory mariadb if not exists results in Error: cannot mkdir 2: /var/mariadb/backup/. Therefore mkdir /var/mariadb command should be used before mariabackup --backup.

Mariadb 10.3 - Mariabackup fails on restore. Even taking ownership of all files with the SYSTEM account just does not work. But a complete system backup with Macrium Reflect and then stop the MySQL service, extract the data files from the backup and place in the data directory, and restart the MySQL service works a treat. The set of files in the Macrium reflect backup abart from the various database directories are: - aria_log.00000001 - 'machine name'.err - aria_log_control - ib_buffer_pool - ib_logfile0 - ib_logfile1 - ibdata1 - ibtmp1 - multi-master.info - my.ini - 'machine name'.pid This is not the same as what is produced by mariabackup.

If I made a db backup by 'mariabackup --backup' command, may I use the backup image to restore to another mariadb version using 'mariabackup --copy-back' command?

We should execute the command, 'mariabackup --prepare', after performing 'mariabackup --backup'. Must the command 'mariabackup --prepare' be executed right after finishing 'mariabackup --backup'? Or I may wait till the time of db restoration?

Hi! This is a very good question. I encourage you to verify the time needed in your setup to do the prepare step and based on your SLA decide when to do it (at backup step or at recovery step).

Please take a look at this presentation where Oli Sennhauser explain why he suggest to do the prepare step just after the backup step. https://www.youtube.com/watch?v=dQRuK7y2zuc

Does the prepare step touch all the files inside the backup directory, not the files on /var/lib/mysql directory? If we perform 'prepare' right after backup, it helps to save the time to 'prepare' the backup files for restoration. Is it the reason why we should perform 'prepare' immediately right after the backup? If the 'prepare' time is insignificant, is it no difference whether we do the 'prepare' step right after the backup, or just before db restoration?

it ony touched the backup directory. Note, that you can't always do the prepare on the same machine, apparently when you do not backup into a directory. You can backup to "stream" , i.e stdout, and ship that stream to another box, or elsewhere, to a cloud. Second, maybe you do not want to run an IO and CPU-expensive "prepare", on the box where a server is running.

Having said that, yes, maybe it is more convenient to run prepare on the same box right after backup, if the points above are not a concern.

Please read carefully the documentation and take a moment to look the Oli's mariabackup presentation. You will find all the answer you are looking for.

hi there, I get another error, namelly that some tables are not 'reachable' anymore: <pre> MariaDB [etst]> select * from blablatable; ERROR 1146 (42S02): Table 'test.blablatable' doesn't exist </pre>

These tables exist definitly on server1 (the one that is backed up). Any idea what might be going op here?

mariadb-10.5.6-winx64, I try to restore db by mariabackup, but it doesn't work: it restores all files/directories, but the services 'mariadb' cannot be start.. then, I copy/pase backed files/Dirs to madiadb_dir\data and replace all, it works! Maybe windows file explorer is better backup tool than mariabackup..

mysql Ver 15.1 Distrib 10.3.12-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 mariabackup based on MariaDB server 10.3.12-MariaDB debian-linux-gnu (x86_64)

Kept running into errors similar to the following: https://dba.stackexchange.com/questions/192742/os-error-13-when-running-mariabackup-as-non-mysql-user?rq=1

For me a "successful" backup required sudo -u mysql mariabackup .....

Hello William,

The OS user running MariaBackup needs to have permission to write to the target directory. For example any OS user can run it specifying the target directory as a directory under their home directory.

I have a question about restoring from a mariabackup backup into a mariadb instance running in a docker container.

You say that you have to stop the mariadb instance before doing the above restore commands. However, if you stop mariadb (mysqladmin shutdown), the docker container stops. Do you see the problem? If the container is stopped, then there is no place to run the mariabackup restore commands from above.

(Pardon my new-ness to all of this. I am an experienced DBA - the container / mariadb world is new to me). Thanks.

Normally the database files itself are not in the container but instead somewhere else.

So the procedure would be: - Stop the mariadb instance - Run the above commands on the main machine or in another container that has access to the databases directory - Restart the mariadb instance

Good Day,

Would like to know if and how I can restore the backup to a new database. Original Database: DB1 New Database: DB2

Also, the error "original data directory /var/lib/mysql is not empty", does this mean I must remove all the files in that directory or I should specify another directory to restore to.

Regards,

Ralph