MySQL Backup setup, automation using Holland, mysqldump on Ubuntu

This post will guide you to set up and automate the MySQL logical backups using mysqldump on Ubuntu Linux. We will set-up MySQL backup using mysqldump and automate it with Holland backup framework on Ubuntu.

Towards the end of the post, you have the steps to be executed in a single block which includes steps for Xtrabackup configuration. Though this post is for Ubuntu Linux with holland + mysqldump while we already have an old post to set-up Holland with Xtrabackup on Redhat / CentOS.

Let’s begin the execution.

Installations for Holland Backup Framework on Ubuntu

root@nitty-witty:~# . /etc/os-release
root@nitty-witty:~# cat /etc/os-release
NAME="Ubuntu"
VERSION="18.04.5 LTS (Bionic Beaver)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 18.04.5 LTS"
VERSION_ID="18.04"
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
VERSION_CODENAME=bionic
UBUNTU_CODENAME=bionic
root@nitty-witty:~# echo "deb https://download.opensuse.org/repositories/home:/holland-backup/x${NAME}_${VERSION_ID}/ ./" >> /etc/apt/sources.list

root@nitty-witty:~# wget -qO - https://download.opensuse.org/repositories/home:/holland-backup/x${NAME}_${VERSION_ID}/Release.key |apt-key add -
OK

root@nitty-witty:~# apt-get update
Hit:1 http://repo.mysql.com/apt/ubuntu bionic InRelease
Get:2 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu bionic InRelease [242 kB]
Get:3 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]
Get:4 https://download.opensuse.org/repositories/home:/holland-backup/xUbuntu_18.04 ./ InRelease [1552 B]
Get:5 https://download.opensuse.org/repositories/home:/holland-backup/xUbuntu_18.04 ./ Packages [685 B]

Install Holland Backup Framework

root@nitty-witty:~# apt-get install holland
Reading package lists… Done
Building dependency tree
Reading state information… Done
The following additional packages will be installed:
libpq5 python3-bson python3-bson-ext python3-gridfs python3-psycopg2 python3-pymongo python3-pymongo-ext python3-pymysql
Suggested packages:
python-psycopg2-doc python-pymongo-doc python-pymysql-doc
The following NEW packages will be installed:
holland libpq5 python3-bson python3-bson-ext python3-gridfs python3-psycopg2 python3-pymongo python3-pymongo-ext python3-pymysql
0 upgraded, 9 newly installed, 0 to remove and 21 not upgraded.
Need to get 617 kB of archives.
After this operation, 3842 kB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu bionic-updates/main amd64 libpq5 amd64 10.16-0ubuntu0.18.04.1 [108 kB]
Get:2 https://download.opensuse.org/repositories/home:/holland-backup/xUbuntu_18.04 ./ holland 1.2.2-2 [93.9 kB]
Get:3 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu bionic/main amd64 python3-psycopg2 amd64 2.7.4-1 [152 kB]
Get:4 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu bionic/main amd64 python3-bson amd64 3.6.1+dfsg1-1 [31.8 kB]
Get:5 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu bionic/main amd64 python3-pymongo amd64 3.6.1+dfsg1-1 [125 kB]
Get:6 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu bionic/universe amd64 python3-pymysql all 0.8.0-1 [60.1 kB]
Get:7 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu bionic/main amd64 python3-bson-ext amd64 3.6.1+dfsg1-1 [20.5 kB]
Get:8 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu bionic/main amd64 python3-gridfs all 3.6.1+dfsg1-1 [15.0 kB]
Get:9 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu bionic/main amd64 python3-pymongo-ext amd64 3.6.1+dfsg1-1 [11.0 kB]
Fetched 617 kB in 2s (259 kB/s)
Selecting previously unselected package libpq5:amd64.
(Reading database … 57365 files and directories currently installed.)
Preparing to unpack …/0-libpq5_10.16-0ubuntu0.18.04.1_amd64.deb …
Unpacking libpq5:amd64 (10.16-0ubuntu0.18.04.1) …
Selecting previously unselected package python3-psycopg2.
Preparing to unpack …/1-python3-psycopg2_2.7.4-1_amd64.deb …
Unpacking python3-psycopg2 (2.7.4-1) …
Selecting previously unselected package python3-bson.
Preparing to unpack …/2-python3-bson_3.6.1+dfsg1-1_amd64.deb 
Unpacking python3-bson (3.6.1+dfsg1-1) …
Selecting previously unselected package python3-pymongo.
Preparing to unpack …/3-python3-pymongo_3.6.1+dfsg1-1_amd64.deb …
Unpacking python3-pymongo (3.6.1+dfsg1-1) …
Selecting previously unselected package python3-pymysql.
Preparing to unpack …/4-python3-pymysql_0.8.0-1_all.deb …
Unpacking python3-pymysql (0.8.0-1) …
Selecting previously unselected package holland.
Preparing to unpack …/5-holland_1.2.2-2_all.deb …
Unpacking holland (1.2.2-2) …
Selecting previously unselected package python3-bson-ext.
Preparing to unpack …/6-python3-bson-ext_3.6.1+dfsg1-1_amd64.deb …
Unpacking python3-bson-ext (3.6.1+dfsg1-1) …
Selecting previously unselected package python3-gridfs.
Preparing to unpack …/7-python3-gridfs_3.6.1+dfsg1-1_all.deb
Unpacking python3-gridfs (3.6.1+dfsg1-1) …
Selecting previously unselected package python3-pymongo-ext.
Preparing to unpack …/8-python3-pymongo-ext_3.6.1+dfsg1-1_amd64.deb …
Unpacking python3-pymongo-ext (3.6.1+dfsg1-1) …
Setting up python3-gridfs (3.6.1+dfsg1-1) …
Setting up libpq5:amd64 (10.16-0ubuntu0.18.04.1) …
Setting up python3-pymysql (0.8.0-1) …
Setting up python3-bson (3.6.1+dfsg1-1) …
Setting up python3-bson-ext (3.6.1+dfsg1-1) …
Setting up python3-psycopg2 (2.7.4-1) …
Setting up python3-pymongo (3.6.1+dfsg1-1) …
Setting up python3-pymongo-ext (3.6.1+dfsg1-1) …
Setting up holland (1.2.2-2) …
Processing triggers for man-db (2.8.3-2ubuntu0.1) …
Processing triggers for libc-bin (2.27-3ubuntu1.4) …

Verifying Setup and directory structure:

root@nitty-witty:~# holland --version
1.2.2
root@nitty-witty:~# ls -lhtrR /etc/holland/
/etc/holland/:
total 12K
-rw-r--r-- 1 root root 890 Sep 1 2020 holland.conf
drwxr-xr-x 2 root root 4.0K Mar 19 06:33 backupsets
drwxr-xr-x 2 root root 4.0K Mar 19 06:33 providers

/etc/holland/backupsets:
total 4.0K
-rw-r--r-- 1 root root 1.5K Sep 1 2020 default.conf

/etc/holland/providers:
total 56K
-rw-r--r-- 1 root root 322 Sep 1 2020 xtrabackup.conf
-rw-r--r-- 1 root root 108 Sep 1 2020 tar.conf
-rw-r--r-- 1 root root 1.6K Sep 1 2020 sqlite.conf
-rw-r--r-- 1 root root 136 Sep 1 2020 random.conf
-rw-r--r-- 1 root root 160 Sep 1 2020 pgdump.conf
-rw-r--r-- 1 root root 2.5K Sep 1 2020 mysqlhotcopy.conf
-rw-r--r-- 1 root root 4.9K Sep 1 2020 mysqldump.conf
-rw-r--r-- 1 root root 1.2K Sep 1 2020 mysqldump-lvm.conf
-rw-r--r-- 1 root root 1.8K Sep 1 2020 mysql-lvm.conf
-rw-r--r-- 1 root root 1.3K Sep 1 2020 mongodump.conf
-rw-r--r-- 1 root root 330 Sep 1 2020 mariabackup.conf
-rw-r--r-- 1 root root 2.7K Sep 1 2020 maatkit.conf
-rw-r--r-- 1 root root 99 Sep 1 2020 example.conf
root@nitty-witty:~#

Holland backupset configuration for mysqldump

root@nitty-witty:~# cat /etc/holland/backupsets/mysqldump.conf
[holland:backup]
backups-to-keep = 7
plugin = mysqldump
purge-failed-backup=yes
purge-policy=after-backup
#before-backup-command
#after-backup-command
#failed-backup-command

The before, after, and failed backup commands can be used to perform sanity checks and alert upon failures for the backup process. There are many other options you may review on the Holland documentation.

Holland provider configuration for mysqldump

root@nitty-witty:~# cat /etc/holland/providers/mysqldump.conf | grep -v ^# | grep -v ^$
[mysqldump]
lock-method = auto-detect
dump-routines = yes
dump-events = yes
stop-slave = no
bin-log-position = yes
flush-logs = no
file-per-database = no
additional-options = ""
[compression]
method = gzip
inline = yes
level = 1
[mysql:client]
defaults-extra-file = /root/.my.cnf,~/.my.cnf,
root@nitty-witty:~#

Consider reviewing the mysqldump.conf that comes with the setup. It has very well explained details of the configuration options for the mysqldump provider.

Backup user credentials

root@nitty-witty:~# cat .my.cnf
[client]
user=backup_user
password=XxX

Dry running mysqldump backup using holland framework

root@nitty-witty:~# holland -d backup mysqldump --dry-run
Adding plugin directory: '/usr/share/holland/plugins'
Holland 1.2.2 started with pid 6648
--- Starting dry run ---
Creating backup path /backup/mysqldump/20210319_064814
Unknown parameter 'purge-failed-backup' in section 'holland:backup'
Writing out config to /backup/mysqldump/20210319_064814/backup.conf
Estimating size of mysqldump backup
Estimated Backup Size: 2.54MB
Missing backup.conf from last backup
Starting backup[mysqldump/20210319_064814] via plugin mysqldump
Running in dry-run mode.
Connected to MySQL
Using mysqldump executable: /usr/bin/mysqldump
Executing: /usr/bin/mysqldump --no-defaults --version
mysqldump version 5.7.33
Using mysqldump option --flush-privileges
Using mysqldump option --routines
Using mysqldump option --events
Using mysqldump option --max-allowed-packet=128M
Using mysqldump option --master-data=2
Using gzip compression level 1 with args
Executing: /bin/gzip -1
Dry Run: /usr/bin/mysqldump --defaults-file=/backup/mysqldump/20210319_064814/my.cnf --flush-privileges --routines --events --max-allowed-packet=128M --master-data=2 --lock-all-tables --all-databases
Backup completed in 0.07 seconds
--- Ending dry run ---

Manually execute holland backup using mysqldump

root@nitty-witty:~# holland -d backup mysqldump
Adding plugin directory: '/usr/share/holland/plugins'
Holland 1.2.2 started with pid 6653
--- Starting backup run ---
Set advisory lock on /etc/holland/backupsets/mysqldump.conf
Creating backup path /backup/mysqldump/20210319_064819
Unknown parameter 'purge-failed-backup' in section 'holland:backup'
Writing out config to /backup/mysqldump/20210319_064819/backup.conf
Estimating size of mysqldump backup
Estimated Backup Size: 2.54MB
Missing backup.conf from last backup
Starting backup[mysqldump/20210319_064819] via plugin mysqldump
Connected to MySQL
Using mysqldump executable: /usr/bin/mysqldump
Executing: /usr/bin/mysqldump --no-defaults --version
mysqldump version 5.7.33
Using mysqldump option --flush-privileges
Using mysqldump option --routines
Using mysqldump option --events
Using mysqldump option --max-allowed-packet=128M
Using mysqldump option --master-data=2
Using gzip compression level 1 with args
Executing: /bin/gzip -1
Executing: /usr/bin/mysqldump --defaults-file=/backup/mysqldump/20210319_064819/my.cnf --flush-privileges --routines --events --max-allowed-packet=128M --master-data=2 --lock-all-tables --all-databases
Debug: Determining size of directory /backup/mysqldump/20210319_064819/backup_data
Final on-disk backup size 232.19KB
8.94% of estimated size 2.54MB
Writing out config to /backup/mysqldump/20210319_064819/backup.conf
Backup completed in 0.17 seconds
No backups purged
Released lock /etc/holland/backupsets/mysqldump.conf
--- Ending backup run ---

Review backup directory

root@nitty-witty:~# ls -lhtr /backup/mysqldump/20210319_064819/
total 16K
-rw-rw---- 1 root root 41 Mar 19 06:48 my.cnf
drwxrwx--- 2 root root 4.0K Mar 19 06:48 backup_data
-rw-rw---- 1 root root 4.3K Mar 19 06:48 backup.conf
root@nitty-witty:~# cd /backup/mysqldump/newest/backup_data/
root@nitty-witty:/backup/mysqldump/newest/backup_data# ls -lhtr
total 232K
-rw-rw---- 1 root root 229K Mar 19 06:48 all_databases.sql.gz
root@nitty-witty:/backup/mysqldump/newest/backup_data#

Setup a daily cronjob for backup


root@nitty-witty:~# crontab -l
0 0 * * * holland -d backup mysqldump 1>holland_backup_cron.log 2>&1
root@nitty-witty:~#

MySQL Backup setup steps for Using Xtrabackup

. /etc/os-release
cat /etc/os-release
echo "deb https://download.opensuse.org/repositories/home:/holland-backup/x${NAME}${VERSION_ID}/ ./" >> /etc/apt/sources.list wget -qO - https://download.opensuse.org/repositories/home:/holland-backup/x${NAME}${VERSION_ID}/Release.key apt-key add -
apt-get update
apt-get install holland
holland --version
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
percona-release enable-only tools release
apt-get update
sudo apt-get install percona-xtrabackup-24 qpress
which xtrabackup
xtrabackup --version
vi /etc/holland/holland.conf
vi /etc/holland/backupsets/xtrabackup.conf
vi /etc/holland/providers/xtrabackup.conf
holland -d backup xtrabackup --dry-run
holland -d backup xtrabackup

References

https://docs.hollandbackup.org/docs/

http://www.percona.com/doc/percona-xtrabackup/index.html

Leave a Reply

Your email address will not be published. Required fields are marked *