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
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE ', table_name, ' DISCARD TABLESPACE;
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
ALTER TABLE ', table_name, ' IMPORT TABLESPACE;
SET FOREIGN_KEY_CHECKS=1;
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 target.example.com --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!
Another option that you have which has worked great for me is using concat to build the DISCARD and IMPORT statements.
SELECT concat("ALTER TABLE demo.", table_name , " DISCARD TABLESPACE;") AS command FROM information_schema.tables WHERE table_schema = 'demo';
SELECT concat("ALTER TABLE demo.", table_name , " IMPORT TABLESPACE;") AS command FROM information_schema.tables WHERE table_schema = 'demo';
Then you can just copy the resulting commands from the results pane unquoted and then paste them right back in to execute.