Back Up MySQL with Percona Xtrabackup

Prerequisites

  • A newly created Alibaba Cloud Ubuntu 16.04 instance.
  • A root password is set up to your instance.

Install MariaDB

apt-get install mariadb-server mariadb-client -y
Enter current password for root (enter for none):
Set root password? [Y/n]: N
Remove anonymous users? [Y/n]: Y
Disallow root login remotely? [Y/n]: Y
Remove test database and access to it? [Y/n]: Y
Reload privilege tables now? [Y/n]: Y

Install Percona XtraBackup

wget https://repo.percona.com/apt/percona-release_latest.xenial_all.deb
dpkg -i percona-release_latest.xenial_all.deb
apt-get update -y
apt-get install percona-xtrabackup-24 -y

Configure MariaDB

mysql -u root -p
MariaDB [(none)]> CREATE USER 'bkuser'@'localhost' IDENTIFIED BY 'password';
MariaDB [(none)]> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkuser'@'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> EXIT;
mkdir -p /opt/backup/mysql

Perform Full Backup with Innobackupex

innobackupex --user=bkuser --password=password /opt/backup/mysql/fullbackup --no-timestamp
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --tmpdir=/tmp 
xtrabackup: recognized client arguments: --datadir=/var/lib/mysql --tmpdir=/tmp
encryption: using gcrypt 1.6.5
181112 15:46:02 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
181112 15:46:02 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'bkuser' (using password: YES).
181112 15:46:02 version_check Connected to MySQL server
181112 15:46:02 version_check Executing a version check against the server...
181112 15:46:02 version_check Done.
181112 15:46:02 Connecting to MySQL server host: localhost, user: bkuser, password: set, port: not set, socket: not set
Using server version 10.0.36-MariaDB-0ubuntu0.16.04.1
innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
.
.
181112 15:46:06 Finished backing up non-InnoDB tables and files
181112 15:46:06 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '137240123'
xtrabackup: Stopping log copying thread.
.181112 15:46:06 >> log scanned up to (137240123)
181112 15:46:06 Executing UNLOCK TABLES
181112 15:46:06 All tables unlocked
181112 15:46:06 Backup created in directory '/opt/backup/mysql/fullbackup/'
181112 15:46:06 [00] Writing /opt/backup/mysql/fullbackup/backup-my.cnf
181112 15:46:06 [00] ...done
181112 15:46:06 [00] Writing /opt/backup/mysql/fullbackup/xtrabackup_info
181112 15:46:06 [00] ...done
xtrabackup: Transaction log of lsn (137240123) to (137240123) was copied.
181112 15:46:06 completed OK!
innobackupex --apply-log /opt/backup/mysql/fullbackup
InnoDB: Doing recovery: scanned up to log sequence number 137241109 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.19 started; log sequence number 137241109
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 137241128
181112 15:49:37 completed OK!
cat /opt/backup/mysql/fullbackup/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 137240123
last_lsn = 137240123
compact = 0
recover_binlog_info = 0

Incremental Backups with Innobackupex

innobackupex --user=bkuser --password=password /opt/backup/mysql/fullbackup-base --no-timestamp
innobackupex --user=bkuser --password=password --incremental /opt/backup/mysql/increament_bk --incremental-basedir=/opt/backup/mysql/fullbackup-base
181112 15:51:51 Finished backing up non-InnoDB tables and files
181112 15:51:51 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '137240123'
xtrabackup: Stopping log copying thread.
.181112 15:51:51 >> log scanned up to (137240123)
181112 15:51:51 Executing UNLOCK TABLES
181112 15:51:51 All tables unlocked
181112 15:51:51 Backup created in directory '/opt/backup/mysql/increament_bk/
181112 15:51:51 [00] Writing /opt/backup/mysql/increament_bk/backup-my.cnf
181112 15:51:51 [00] ...done
181112 15:51:51 [00] Writing /opt/backup/mysql/increament_bk/xtrabackup_info
181112 15:51:51 [00] ...done
xtrabackup: Transaction log of lsn (137240123) to (137240123) was copied.
181112 15:51:51 completed OK!
cat /opt/backup/mysql/increament_bk/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 137240123
to_lsn = 137240123
last_lsn = 137240123
compact = 0
recover_binlog_info = 0
innobackupex --apply-log --redo-only /opt/backup/mysql/fullbackup-base
innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=/opt/backup/mysql/increament_bk/
innobackupex --apply-log /opt/backup/mysql/fullbackup-base

Restore Full Backup with Innobackupex

mv /var/lib/mysql/* /mnt/
innobackupex --copy-back /opt/backup/mysql/fullbackup-base
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --tmpdir=/tmp 
xtrabackup: recognized client arguments: --datadir=/var/lib/mysql --tmpdir=/tmp
encryption: using gcrypt 1.6.5
181112 15:04:15 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
.
.
.
181112 15:04:19 [01] Copying ./mysql/servers.MYI to /var/lib/mysql/mysql/servers.MYI
181112 15:04:19 [01] ...done
181112 15:04:19 [01] Copying ./mysql/time_zone_name.frm to /var/lib/mysql/mysql/time_zone_name.frm
181112 15:04:19 [01] ...done
181112 15:04:19 [01] Copying ./mysql/column_stats.frm to /var/lib/mysql/mysql/column_stats.frm
181112 15:04:19 [01] ...done
181112 15:04:19 [01] Copying ./mysql/gtid_slave_pos.frm to /var/lib/mysql/mysql/gtid_slave_pos.frm
181112 15:04:19 [01] ...done
181112 15:04:19 [01] Copying ./mysql/slow_log.frm to /var/lib/mysql/mysql/slow_log.frm
181112 15:04:19 [01] ...done
181112 15:04:19 [01] Copying ./mysql/user.MYD to /var/lib/mysql/mysql/user.MYD
181112 15:04:19 [01] ...done
181112 15:04:19 [01] Copying ./mysql/general_log.CSV to /var/lib/mysql/mysql/general_log.CSV
181112 15:04:19 [01] ...done
181112 15:04:19 [01] Copying ./mysql/time_zone_transition.MYD to /var/lib/mysql/mysql/time_zone_transition.MYD
181112 15:04:19 [01] ...done
181112 15:04:19 completed OK!
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Original Source

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

4.97K Followers

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com