Does anyone know if you can encrypt the database backup using AutoMYSQLBackup on Ubuntu 14.04?

March 13, 2018 1k views
MySQL Ubuntu

Does anyone know if you can encrypt the database backup using AutoMYSQLBackup on Ubuntu 14.04? I've tried adding the parameter to encrypt but it doesn't work.

If not do you have a recommendation for how to do it?

Any help is much appreciated.

10 comments
  • I've tried adding the parameter to encrypt but it doesn't work.

    Can you clarify which part is not working?

    Also, what guide are you following?

  • Thanks for the questions.

    I've tried adding the following parameters to the /etc/default/automysqlbackup config file:

    #Do you wish to encrypt your backups using openssl?
    CONFIG_encrypt='no'
    
    #Choose a password to encrypt the backups.
    CONFIG_encrypt_password='password0123'
    

    When I do nothing changes in the backup files when I manually run "automysqlbackup"

    Here's a link to the guide I was using:

    https://www.tecmint.com/mysql-mariadb-postgresql-database-backup-using-automysqlbackup-autopostgresqlbackup/

  • I also tried modifying the config file like so:

    #Do you wish to encrypt your backups using openssl?
    encrypt='no'
    
    #Choose a password to encrypt the backups.
    encrypt_password='password0123'
    

    Still didn't work

  • Sorry if this is a silly question, but.

    1. Did you uncomment those two lines? (i.e. remove the trailing # symbol).
    2. Did you change CONFIG_encrypt='no' to CONFIG_encrypt='yes'?

    Those two lines should look something like this

    # Do you wish to encrypt your backups using openssl?
    CONFIG_encrypt='yes'
    # Choose a password to encrypt the backups.
    CONFIG_encrypt_password='$YOUR_PASSWORD'
    

    replacing $YOUR_PASSWORD with whatever password you want to use.

  • Yes I did it as you posted, which setting the values to

    # Do you wish to encrypt your backups using openssl?
    CONFIG_encrypt='yes'
    # Choose a password to encrypt the backups.
    CONFIG_encrypt_password='$YOUR_PASSWORD'
    

    That said I had to add those parameters to the config file. They were not there by default to only be uncommented

  • Additionally, all the parameters in my config file do not start with "CONFIG_". I'm not sure if that makes a difference? Or if that is just a different between the Ubuntu and another version of linux?

  • That said I had to add those parameters to the config file. They were not there by default to only be uncommented

    That's fine. Did you use yes? If so, does that work?

  • Additionally, all the parameters in my config file do not start with "CONFIG_". I'm not sure if that makes a difference? Or if that is just a different between the Ubuntu and another version of linux?

    Yes it makes a difference. I wonder if the version that you are using is newer than the version from the tutorial.

    Can you share the config file, and let us know which version you are using.

  • Here's the whole config file:

    # By default, the Debian version of automysqlbackup will use:
    # mysqldump --defaults-file=/etc/mysql/debian.cnf
    # but you might want to overwrite with a specific user & pass.
    # To do this, simply edit bellow.
    
    # Username to access the MySQL server e.g. dbuser
    #USERNAME=`grep user /etc/mysql/debian.cnf | tail -n 1 | cut -d"=" -f2 | awk '{print $1}'`
    
    # Username to access the MySQL server e.g. password
    #PASSWORD=`grep password /etc/mysql/debian.cnf | tail -n 1 | cut -d"=" -f2 | awk '{print $1}'`
    
    # Host name (or IP address) of MySQL server e.g localhost
    DBHOST=localhost
    
    # List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
    # Note that it's absolutely normal that the db named "mysql" is not in this
    # list, as it's added later by the script. See the MDBNAMES directives below
    # in this file (advanced options).
    # This is ONLY a convenient default, if you don't like it, don't complain
    # and write your own.
    # The following is a quick hack that will find the names of the databases by
    # reading the mysql folder content. Feel free to replace by something else.
    # DBNAMES=`find /var/lib/mysql -mindepth 1 -maxdepth 1 -type d | cut -d'/' -f5 | grep -v ^mysql\$ | tr \\\r\\\n ,\ `
    # This one does a list of dbs using a MySQL statement.
    DBNAMES=`mysql --defaults-file=/etc/mysql/debian.cnf --execute="SHOW DATABASES" | awk '{print $1}' | grep -v ^Database$ | grep -v ^mysql$ | grep -v ^performance_schema$ | grep -v ^information_schema$ | tr \\\r\\\n ,\ `
    
    # Backup directory location e.g /backups
    # Folders inside this one will be created (daily, weekly, etc.), and the
    # subfolders will be database names. Note that backups will be owned by
    # root, with Unix rights 0600.
    BACKUPDIR="/var/lib/automysqlbackup"
    
    # Mail setup
    # What would you like to be mailed to you?
    # - log   : send only log file
    # - files : send log file and sql files as attachments (see docs)
    # - stdout : will simply output the log to the screen if run manually.
    # - quiet : Only send logs if an error occurs to the MAILADDR.
    MAILCONTENT="log"
    
    # Set the maximum allowed email size in k. (4000 = approx 5MB email [see
    # docs])
    MAXATTSIZE="4000"
    
    # Email Address to send mail to? (user@domain.com)
    #MAILADDR=""
    
    # ============================================================
    # === ADVANCED OPTIONS ( Read the doc's below for details )===
    #=============================================================
    
    # List of DBBNAMES for Monthly Backups.
    MDBNAMES=""
    
    # List of DBNAMES to EXLUCDE if DBNAMES are set to all (must be in " quotes)
    DBEXCLUDE=""
    
    # Include CREATE DATABASE in backup?
    CREATE_DATABASE=yes
    
    # Separate backup directory and file for each DB? (yes or no)
    SEPDIR=yes
    
    # Which day do you want weekly backups? (1 to 7 where 1 is Monday)
    DOWEEKLY=6
    
    # Choose Compression type. (gzip or bzip2)
    COMP=gzip
    
    # Compress communications between backup server and MySQL server?
    COMMCOMP=no
    
    # Additionally keep a copy of the most recent backup in a seperate
    # directory.
    LATEST=no
    
    #  The maximum size of the buffer for client/server communication. e.g. 16MB
    #  (maximum is 1GB)
    MAX_ALLOWED_PACKET=
    
    #  For connections to localhost. Sometimes the Unix socket file must be
    #  specified.
    SOCKET=
    
    # Command to run before backups (uncomment to use)
    #PREBACKUP="/etc/mysql-backup-pre"
    
    # Command run after backups (uncomment to use)
    #POSTBACKUP="/etc/mysql-backup-post"
    
    # Backup of stored procedures and routines (comment to remove)
    ROUTINES=yes
    
    # Encrypt the backup using openssl?
    encrypt="yes"
    
    # Encryption Password
    encrypt_password="testpassword"
    
    
  • When I check the version of Automysqlbackup it says:

    Version 2.6+debian.3-1

2 Answers

Thanks. I think that is the problem. I just installed this on Ubuntu 14.04 and it looks like it is using version 2.6. However in the tutorial they are installing version 3.0 automysqlbackup-v3.0_rc6.tar.gz.

I also checked Ubuntu 16.04 and 17.10 and they are all using this old version. It is possible that this feature was not added until a later version.

In order to get this to work, I would uninstall the version that you installed with apt apt-get remove automysqlbackup and install this manually with the following steps.

mkdir /opt/automysqlbackup

cd /opt/automysqlbackup

wget http://ufpr.dl.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%203.0/automysqlbackup-v3.0_rc6.tar.gz

tar zxf automysqlbackup-v3.0_rc6.tar.gz

./install.sh

Once you have ran the install script, you should see the up to date file located in /etc/automysqlbackup/automysqlbackup.conf and be able to change the settings for encryption. They should look something like this:

# Do you wish to encrypt your backups using openssl?
CONFIG_encrypt='yes'
# Choose a password to encrypt the backups.
CONFIG_encrypt_password='$YOUR_PASSWORD'

Be sure to replace $YOUR_PASSWORD with whatever password you want to use.

  • Thanks a ton. That worked perfectly. One last question, is there a easy way to decrypt the file on Windows after I download it from the Linux server?

Have another answer? Share your knowledge.