What is XtraBackup?
XtraBackup is an open-source hot backup utility for MySQL-based database provided by percona. According their official documents, XtraBackup is the world's only open-source, free MySQL hot backup software that perform non-blocking backups for InnoDB and XtraDB database.
How to use it?
Software environment:
OS: Ubuntu server 18
Database: MySQL 8
Database name for test: dbtest
Installation steps(commands):
1: wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
2: sudo dpkg -i percona-<version_info>.deb
3: sudo apt update
4: sudo apt-get install percona-xtrabackup-80
Assume we need to backup database from server 1 and restore it on server 2, and have directory structure like follows on server 1:
~/dbbackup
~/dbbackup/base(store initial full backup)
~/dbbackup/inc1(store first incremental backup)
Full backup command:
sudo xtrabackup — backup — user=<DBUSER> — password=<DBUSERPASS> — databases='dbtest' — target-dir=dbbackup/base
Incremental backup:
sudo xtrabackup — backup — user=<DBUSER> — password=<DBUSERPASS> — databases='dbtest' — target-dir=dbbackup/inc1 — incremental-basedir=dbbackup/base
Copy files from server 1 to server 2:
sudo rsync -avz dbbackup/ user_name@<ip_address>:~/dbbackup/
Preparing a full backup on server 2(command):
xtrabackup — prepare — apply-log-only — target-dir=dbbackup/base
Preparing a incremental backup on server 2(command):
xtrabackup — prepare — target-dir=dbbackup/base — incremental-dir=dbbackup/inc1
Copy prepared database file to MySQL directory on server 2:
sudo rsync -avrp dbbackup/base/ /var/lib/mysql/
Change new files permission give MySQL full access on server 2:
sudo chown -R mysql:mysql /var/lib/mysql
Restart MySQL service on server 2:
service mysql restart
For now if you use 'show databases' command on server 2 you should see your restored database.
Useful links: