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.<\/p>\n\n\n\n
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<\/a>. <\/p>\n\n\n\n Let’s begin the execution.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n Consider reviewing the mysqldump.conf that comes with the setup. It has very well explained details of the configuration options for the mysqldump provider.<\/p>\n\n\n\n References<\/p>\n\n\n\nInstallations for Holland Backup Framework on Ubuntu<\/strong><\/h1>\n\n\n\n\n\n\n\n
root@nitty-witty:~# . \/etc\/os-release\nroot@nitty-witty:~# cat \/etc\/os-release\nNAME=\"Ubuntu\"\nVERSION=\"18.04.5 LTS (Bionic Beaver)\"\nID=ubuntu\nID_LIKE=debian\nPRETTY_NAME=\"Ubuntu 18.04.5 LTS\"\nVERSION_ID=\"18.04\"\nHOME_URL=\"https:\/\/www.ubuntu.com\/\"\nSUPPORT_URL=\"https:\/\/help.ubuntu.com\/\"\nBUG_REPORT_URL=\"https:\/\/bugs.launchpad.net\/ubuntu\/\"\nPRIVACY_POLICY_URL=\"https:\/\/www.ubuntu.com\/legal\/terms-and-policies\/privacy-policy\"\nVERSION_CODENAME=bionic\nUBUNTU_CODENAME=bionic\nroot@nitty-witty:~# echo \"deb https:\/\/download.opensuse.org\/repositories\/home:\/holland-backup\/x${NAME}_${VERSION_ID}\/ .\/\" >> \/etc\/apt\/sources.list\n\nroot@nitty-witty:~# wget -qO - https:\/\/download.opensuse.org\/repositories\/home:\/holland-backup\/x${NAME}_${VERSION_ID}\/Release.key |apt-key add -\nOK\n\nroot@nitty-witty:~# apt-get update\nHit:1 http:\/\/repo.mysql.com\/apt\/ubuntu bionic InRelease\nGet:2 http:\/\/ap-south-1.ec2.archive.ubuntu.com\/ubuntu bionic InRelease [242 kB]\nGet:3 http:\/\/security.ubuntu.com\/ubuntu bionic-security InRelease [88.7 kB]\nGet:4 https:\/\/download.opensuse.org\/repositories\/home:\/holland-backup\/xUbuntu_18.04 .\/ InRelease [1552 B]\nGet:5 https:\/\/download.opensuse.org\/repositories\/home:\/holland-backup\/xUbuntu_18.04 .\/ Packages [685 B]<\/pre>\n\n\n\nInstall Holland Backup Framework<\/h1>\n\n\n\n
root@nitty-witty:~# apt-get install holland\nReading package lists\u2026 Done\nBuilding dependency tree\nReading state information\u2026 Done\nThe following additional packages will be installed:\nlibpq5 python3-bson python3-bson-ext python3-gridfs python3-psycopg2 python3-pymongo python3-pymongo-ext python3-pymysql\nSuggested packages:\npython-psycopg2-doc python-pymongo-doc python-pymysql-doc\nThe following NEW packages will be installed:\nholland libpq5 python3-bson python3-bson-ext python3-gridfs python3-psycopg2 python3-pymongo python3-pymongo-ext python3-pymysql\n0 upgraded, 9 newly installed, 0 to remove and 21 not upgraded.\nNeed to get 617 kB of archives.\nAfter this operation, 3842 kB of additional disk space will be used.\nDo you want to continue? [Y\/n] Y\nGet:1 http:\/\/ap-south-1.ec2.archive.ubuntu.com\/ubuntu bionic-updates\/main amd64 libpq5 amd64 10.16-0ubuntu0.18.04.1 [108 kB]\nGet:2 https:\/\/download.opensuse.org\/repositories\/home:\/holland-backup\/xUbuntu_18.04 .\/ holland 1.2.2-2 [93.9 kB]\nGet:3 http:\/\/ap-south-1.ec2.archive.ubuntu.com\/ubuntu bionic\/main amd64 python3-psycopg2 amd64 2.7.4-1 [152 kB]\nGet:4 http:\/\/ap-south-1.ec2.archive.ubuntu.com\/ubuntu bionic\/main amd64 python3-bson amd64 3.6.1+dfsg1-1 [31.8 kB]\nGet:5 http:\/\/ap-south-1.ec2.archive.ubuntu.com\/ubuntu bionic\/main amd64 python3-pymongo amd64 3.6.1+dfsg1-1 [125 kB]\nGet:6 http:\/\/ap-south-1.ec2.archive.ubuntu.com\/ubuntu bionic\/universe amd64 python3-pymysql all 0.8.0-1 [60.1 kB]\nGet: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]\nGet:8 http:\/\/ap-south-1.ec2.archive.ubuntu.com\/ubuntu bionic\/main amd64 python3-gridfs all 3.6.1+dfsg1-1 [15.0 kB]\nGet: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]\nFetched 617 kB in 2s (259 kB\/s)\nSelecting previously unselected package libpq5:amd64.\n(Reading database \u2026 57365 files and directories currently installed.)\nPreparing to unpack \u2026\/0-libpq5_10.16-0ubuntu0.18.04.1_amd64.deb \u2026\nUnpacking libpq5:amd64 (10.16-0ubuntu0.18.04.1) \u2026\nSelecting previously unselected package python3-psycopg2.\nPreparing to unpack \u2026\/1-python3-psycopg2_2.7.4-1_amd64.deb \u2026\nUnpacking python3-psycopg2 (2.7.4-1) \u2026\nSelecting previously unselected package python3-bson.\nPreparing to unpack \u2026\/2-python3-bson_3.6.1+dfsg1-1_amd64.deb \nUnpacking python3-bson (3.6.1+dfsg1-1) \u2026\nSelecting previously unselected package python3-pymongo.\nPreparing to unpack \u2026\/3-python3-pymongo_3.6.1+dfsg1-1_amd64.deb \u2026\nUnpacking python3-pymongo (3.6.1+dfsg1-1) \u2026\nSelecting previously unselected package python3-pymysql.\nPreparing to unpack \u2026\/4-python3-pymysql_0.8.0-1_all.deb \u2026\nUnpacking python3-pymysql (0.8.0-1) \u2026\nSelecting previously unselected package holland.\nPreparing to unpack \u2026\/5-holland_1.2.2-2_all.deb \u2026\nUnpacking holland (1.2.2-2) \u2026\nSelecting previously unselected package python3-bson-ext.\nPreparing to unpack \u2026\/6-python3-bson-ext_3.6.1+dfsg1-1_amd64.deb \u2026\nUnpacking python3-bson-ext (3.6.1+dfsg1-1) \u2026\nSelecting previously unselected package python3-gridfs.\nPreparing to unpack \u2026\/7-python3-gridfs_3.6.1+dfsg1-1_all.deb\nUnpacking python3-gridfs (3.6.1+dfsg1-1) \u2026\nSelecting previously unselected package python3-pymongo-ext.\nPreparing to unpack \u2026\/8-python3-pymongo-ext_3.6.1+dfsg1-1_amd64.deb \u2026\nUnpacking python3-pymongo-ext (3.6.1+dfsg1-1) \u2026\nSetting up python3-gridfs (3.6.1+dfsg1-1) \u2026\nSetting up libpq5:amd64 (10.16-0ubuntu0.18.04.1) \u2026\nSetting up python3-pymysql (0.8.0-1) \u2026\nSetting up python3-bson (3.6.1+dfsg1-1) \u2026\nSetting up python3-bson-ext (3.6.1+dfsg1-1) \u2026\nSetting up python3-psycopg2 (2.7.4-1) \u2026\nSetting up python3-pymongo (3.6.1+dfsg1-1) \u2026\nSetting up python3-pymongo-ext (3.6.1+dfsg1-1) \u2026\nSetting up holland (1.2.2-2) \u2026\nProcessing triggers for man-db (2.8.3-2ubuntu0.1) \u2026\nProcessing triggers for libc-bin (2.27-3ubuntu1.4) \u2026\n<\/pre>\n\n\n\n
Verifying Setup and directory structure:<\/h3>\n\n\n\n
root@nitty-witty:~# holland --version\n1.2.2\nroot@nitty-witty:~# ls -lhtrR \/etc\/holland\/\n\/etc\/holland\/:\ntotal 12K\n-rw-r--r-- 1 root root 890 Sep 1 2020 holland.conf\ndrwxr-xr-x 2 root root 4.0K Mar 19 06:33 backupsets\ndrwxr-xr-x 2 root root 4.0K Mar 19 06:33 providers\n\n\/etc\/holland\/backupsets:\ntotal 4.0K\n-rw-r--r-- 1 root root 1.5K Sep 1 2020 default.conf\n\n\/etc\/holland\/providers:\ntotal 56K\n-rw-r--r-- 1 root root 322 Sep 1 2020 xtrabackup.conf\n-rw-r--r-- 1 root root 108 Sep 1 2020 tar.conf\n-rw-r--r-- 1 root root 1.6K Sep 1 2020 sqlite.conf\n-rw-r--r-- 1 root root 136 Sep 1 2020 random.conf\n-rw-r--r-- 1 root root 160 Sep 1 2020 pgdump.conf\n-rw-r--r-- 1 root root 2.5K Sep 1 2020 mysqlhotcopy.conf\n-rw-r--r-- 1 root root 4.9K Sep 1 2020 mysqldump.conf\n-rw-r--r-- 1 root root 1.2K Sep 1 2020 mysqldump-lvm.conf\n-rw-r--r-- 1 root root 1.8K Sep 1 2020 mysql-lvm.conf\n-rw-r--r-- 1 root root 1.3K Sep 1 2020 mongodump.conf\n-rw-r--r-- 1 root root 330 Sep 1 2020 mariabackup.conf\n-rw-r--r-- 1 root root 2.7K Sep 1 2020 maatkit.conf\n-rw-r--r-- 1 root root 99 Sep 1 2020 example.conf\nroot@nitty-witty:~#<\/pre>\n\n\n\n
Holland backupset configuration for mysqldump<\/h3>\n\n\n\n
root@nitty-witty:~# cat \/etc\/holland\/backupsets\/mysqldump.conf\n[holland:backup]\nbackups-to-keep = 7\nplugin = mysqldump\npurge-failed-backup=yes\npurge-policy=after-backup\n#before-backup-command\n#after-backup-command\n#failed-backup-command<\/pre>\n\n\n\n
Holland provider configuration for mysqldump<\/h3>\n\n\n\n
root@nitty-witty:~# cat \/etc\/holland\/providers\/mysqldump.conf | grep -v ^# | grep -v ^$\n[mysqldump]\nlock-method = auto-detect\ndump-routines = yes\ndump-events = yes\nstop-slave = no\nbin-log-position = yes\nflush-logs = no\nfile-per-database = no\nadditional-options = \"\"\n[compression]\nmethod = gzip\ninline = yes\nlevel = 1\n[mysql:client]\ndefaults-extra-file = \/root\/.my.cnf,~\/.my.cnf,\nroot@nitty-witty:~#<\/pre>\n\n\n\n
Backup user credentials<\/h3>\n\n\n\n
root@nitty-witty:~# cat .my.cnf
[client]
user=backup_user
password=XxX<\/pre>\n\n\n\nDry running mysqldump backup using holland framework<\/h3>\n\n\n\n
root@nitty-witty:~# holland -d backup mysqldump --dry-run\nAdding plugin directory: '\/usr\/share\/holland\/plugins'\nHolland 1.2.2 started with pid 6648\n--- Starting dry run ---\nCreating backup path \/backup\/mysqldump\/20210319_064814\nUnknown parameter 'purge-failed-backup' in section 'holland:backup'\nWriting out config to \/backup\/mysqldump\/20210319_064814\/backup.conf\nEstimating size of mysqldump backup\nEstimated Backup Size: 2.54MB\nMissing backup.conf from last backup\nStarting backup[mysqldump\/20210319_064814] via plugin mysqldump\nRunning in dry-run mode.\nConnected to MySQL\nUsing mysqldump executable: \/usr\/bin\/mysqldump\nExecuting: \/usr\/bin\/mysqldump --no-defaults --version\nmysqldump version 5.7.33\nUsing mysqldump option --flush-privileges\nUsing mysqldump option --routines\nUsing mysqldump option --events\nUsing mysqldump option --max-allowed-packet=128M\nUsing mysqldump option --master-data=2\nUsing gzip compression level 1 with args\nExecuting: \/bin\/gzip -1\nDry 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\nBackup completed in 0.07 seconds\n--- Ending dry run ---<\/pre>\n\n\n\n
Manually execute holland backup using mysqldump<\/h3>\n\n\n\n
root@nitty-witty:~# holland -d backup mysqldump\nAdding plugin directory: '\/usr\/share\/holland\/plugins'\nHolland 1.2.2 started with pid 6653\n--- Starting backup run ---\nSet advisory lock on \/etc\/holland\/backupsets\/mysqldump.conf\nCreating backup path \/backup\/mysqldump\/20210319_064819\nUnknown parameter 'purge-failed-backup' in section 'holland:backup'\nWriting out config to \/backup\/mysqldump\/20210319_064819\/backup.conf\nEstimating size of mysqldump backup\nEstimated Backup Size: 2.54MB\nMissing backup.conf from last backup\nStarting backup[mysqldump\/20210319_064819] via plugin mysqldump\nConnected to MySQL\nUsing mysqldump executable: \/usr\/bin\/mysqldump\nExecuting: \/usr\/bin\/mysqldump --no-defaults --version\nmysqldump version 5.7.33\nUsing mysqldump option --flush-privileges\nUsing mysqldump option --routines\nUsing mysqldump option --events\nUsing mysqldump option --max-allowed-packet=128M\nUsing mysqldump option --master-data=2\nUsing gzip compression level 1 with args\nExecuting: \/bin\/gzip -1\nExecuting: \/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\nDebug: Determining size of directory \/backup\/mysqldump\/20210319_064819\/backup_data\nFinal on-disk backup size 232.19KB\n8.94% of estimated size 2.54MB\nWriting out config to \/backup\/mysqldump\/20210319_064819\/backup.conf\nBackup completed in 0.17 seconds\nNo backups purged\nReleased lock \/etc\/holland\/backupsets\/mysqldump.conf\n--- Ending backup run ---<\/pre>\n\n\n\n
Review backup directory<\/h3>\n\n\n\n
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#<\/pre>\n\n\n\nSetup a daily cronjob for backup<\/h3>\n\n\n\n
root@nitty-witty:~# crontab -l
0 0 * * * holland -d backup mysqldump 1>holland_backup_cron.log 2>&1
root@nitty-witty:~#<\/pre>\n\n\n\nMySQL Backup setup steps for Using Xtrabackup<\/h1>\n\n\n\n
. \/etc\/os-release\ncat \/etc\/os-release\necho \"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}<\/em>${VERSION_ID}\/Release.key apt-key add -\napt-get update\napt-get install holland\nholland --version\nwget https:\/\/repo.percona.com\/apt\/percona-release_latest.$(lsb_release -sc)_all.deb\nsudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb\npercona-release enable-only tools release\napt-get update\nsudo apt-get install percona-xtrabackup-24 qpress\nwhich xtrabackup\nxtrabackup --version\nvi \/etc\/holland\/holland.conf\nvi \/etc\/holland\/backupsets\/xtrabackup.conf\nvi \/etc\/holland\/providers\/xtrabackup.conf\nholland -d backup xtrabackup --dry-run\nholland -d backup xtrabackup<\/pre>\n\n\n\n