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