Single schema backup to be restored in different host

Hi team, I have created a procedure for restoring a single database(schema) backup from one server to another. The steps I have outlined are as follows:

##On Source
1 - Take an xtrabackup backup of the database:
xtrabackup --backup --databases=demo --target-dir=/mnt/backups/xtrabackup --datadir=/var/lib/mysql

2 - Prepare it
xtrabackup --prepare --export --target-dir=/mnt/backups/xtrabackup

3 - Take a no_data mysqldump
mysqldump --no-data demo > demo-dump-defs.sql

4 - Move all demo backup files to target server

##On Target
5 - Create database demo
create database demo;

6 - Import --no_data dump
cat /mnt/backups/demo-dump-defs.sql |mysql -A demo

7 - In database demo

8 - Copy xtrabackup database backup to /var/lib/mysql/demo
cp /mnt/backups/xtrabackup/demo/* /var/lib/mysql/demo
chown mysql:mysql /var/lib/mysql/demo

9 - Import the tablespace

Given that this is a repetitive task, I’m curious if there is an easier way to accomplish it, even if it doesn’t involve using xtrabackup.

Thanks in advance!

Hello, you could use mydumper here. For example to backup multiple db’s using regex option:

mydumper -u$user -p $pw  --threads 4 --regex '^(mydb1|mydb2*)' --compress --triggers --routines --events --trx-consistency-only --outputdir /storage/backup/

Then restore as follows to overwrite existing data on the target:

for x in {'mydb1','mydb2'}; do myloader -o -v 3 -u $user -p $pw -h --threads 4 --source-db $x --database $x --directory /storage/backup;  done 2>&1 | tee -a /storage/backup/myloader.log

Hi Ivan, I appreciate your response. I was wondering if you could provide some insights on whether mydumper/myloader or xtrabackup would be more efficient for our task of refreshing databases between production and staging environments. Our main concern is minimizing the time required to complete this process since it is critical for troubleshooting purposes. Thank you.

Hi, that will depend on many factors such as your DB size, storage and network speed, etc. I advise you to try both approaches to compare. If the size is big, xtrabackup will be faster for sure. However the mydumper approach is simpler as requires less steps.

Thank you for responding. Could you provide more information based on your experience regarding whether xtrabackup or mydumper generate locks at the table or database level? The backups are supposed to be run in a production environment, and we need to ensure that they have minimal impact, or at least clarify the situation. Thanks!