sixhop/automysqlbackup

A fork and further development of AutoMySQLBackup from sourceforge. http://sourceforge.net/projects/automysqlbackup/

AutoMySQLBackup

A fork and further development of AutoMySQLBackup from sourceforge. http://sourceforge.net/projects/automysqlbackup/

You can find a short german description below.

Information

Creating backups means copy the data in a way where it can be restored again. To backup a mysql database server in most cases it is needed to create a dump from the database and each table. If the data, mysql stores in its data directory is simply copied, restoring the data in the mysql database will not be possible. This command line tool enables you to create and maintian mysql backups. You can backups of innodb and myisam tables.

Automysqldumper uses mysqldump for creating the sql backup. By default databases are backed up in separate gzipped files. To restore a database you can use:

zcat daily_andi_wiki_2016-12-24_03h59m_Saturday.sql.gz | mysql -u root -p

Change the name of the file to your needs. After this simple step you get back the data into your database.

To setup this script have a look at the automysqlbackup.conf file. In there you have several options to configure the mysql backup script to your needs.

Adjustments

You can find some original files from the sourceforge package. Some files are adjusted to my needs:

• support for MySQL 5.6 and MySQL 5.7
• support for login path (since MySQL 5.6.x a secure way to save your mysql credentials was implemented)
• adjusted the use of --ssl because it became depreated in MySQL 5.7. The parameter --ssl-mode=REQUIRED is used instead.

Add login path

Per default this script uses the login path automysqldump.

mysql_config_editor set --login-path=automysqldump --host=localhost --user=root --password

After that command give your mysql root password and you're done. If you want to do your backup with another user, simply change the username.

MariaDB

MariaDB does not ship mysql_config_editor with their software. The blog post MySQL 5.6: Security through Complacency? argues that mysql_config_editor is no more secure than a plain text file since the credentials can be printed with a modified version of mysql_config_editor.

To use backup MariaDB databases, specify CONFIG_mysql_dump_username and CONFIG_mysql_dump_password like you would with the SourceForge version.

Get Involved

Backup your mysql server with ease by using this adjusted script. If you encounter any errors feel free to drop an issue. :) (English or German)

For more Information check out Webagentur sixhop.net.

Auch genutzt in meinem Onlineshop für Verlobungsringe und Trauringe

Deutsche Version

Mit diesem Skript können Sie von Ihren Datenbanken auf Ihrem MySQL Server Backups erstellen. Jede MySQL Datenbank mit allen Tables wird dabei standardmäßig in einer eigenen Datei gespeichert, sodass Sie auch einzelne Datenbanken wiederherstellen können.

Mit Hilfe der Anpassung für die Benutzung von login path zur Authentifizierung haben sie die Möglichkeit Ihr Passwort so zu speichern, dass Ihre Daten für den Login nicht bei jedem Aufruf des Backupskripts auf der Konsole und damit auch in der Prozessliste auftaucht. Nach der erfolgreichen Konfiguration reicht der Befehl automysqlbackup auf der Konsole.

automysqlbackup's Issues

integer expression expected on Ubuntu 19.10

Hi, on a new Ubuntu 19.10 install I get the following error

/usr/local/bin/automysqlbackup: line 506: [: 8019 01910: integer expression expected]

Any ideas on why is this happening?

BACKUPFILES variable not available in POSTBACKUP command

None of the variables of automysqlbackup are available in a script called via the POSTBACKUP hook.
It would be nice to have BACKUPFILES available to a POSTBACKUP command to post-process the backup files created. Other variables might be useful, too.
Maybe it takes just a trick in calling the POSTBACKUP command?

CONFIG_mysql_dump_encrypted_login cannot be specified via optional config file

Specifying CONFIG_mysql_dump_encrypted_login='yes' in an optional config file that is loaded when executing automysqlbackup with option -c is ignored by the script.
The reason is the following execution flow:

main:
  -> method_backup():
     -> load_default_config():
        -> mysql_commands():
           -> source $CONFIG_configfile
           -> if [ "${CONFIG_mysql_dump_encrypted_login}" = "yes" ]; then configure --login-path; fi
     -> source $CONFIG_configfile
     -> source $opt_config_file

The optional config file is sourced after the mysql_commands function has been invoked which decides whether to use login-path based login or not based on the configuration parameter CONFIG_mysql_dump_encrypted_login.

Also, it looks sub-optimal that the default config file is sourced twice, once in mysql_commands() and again in method_backup()

Latest file name are not cleaned

Hey there, I used AutoMySQLBackup with those config lines enabled:

# Store an additional copy of the latest backup to a standard
# location so it can be downloaded by third party scripts.
CONFIG_mysql_dump_latest='yes'

# Remove all date and time information from the filenames in the latest folder.
# Runs, if activated, once after the backups are completed. Practically it just finds all files in the latest folder
# and removes the date and time information from the filenames (if present).
CONFIG_mysql_dump_latest_clean_filenames='yes'

and it results in

$ ls -lahrt |rg 2021.* -o
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.txt.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz
2021-01-25_10h08m_Monday.sql.gz

am i missing something?

mariadb v10 still needs -V

[EDIT: this posted before I was finished testing originally, feel free to delete until I test further to see if problem was elsewhere]

MariaDB v10 still uses the --ssl flag instead of --ssl-mode=REQUIRED, but nodot_ver for it returns a number greater than 56. This causes the wrong flag to be added if CONFIG_mysql_dump_usessl is true.

Improve text in the help

Hi

as a new user I have no idea what means this: "-b Use backup method."

• please explain it in the readme and in the short help.
  I don't understand which backup methods are available to choose from, or if this is a switch and when I don't enable this option what will happen, it will not do the backup or..?

• you should mention in the README file what permisions are required for the account performing the backups, because when people don't want enter plain root password in the config file and they want create separate database account just for the backups

thx

retry X times on failures

Hi, should be a good feature the possibility to add a retry option customizable with a parameter like CONFIG_retries_on_failure=3 , in this way if a backup fails for a network partitioning or whatever reason, it will retry to re-execute the task max ${CONFIG_retries_on_failure} times

Backup failing and erroring out on execution after finding the databases

Hello,

I got everything working and my config file setup, but when I run the command without any options, this is what happens:

~/Downloads/AutoMySQLBackup-3.0.7 $/usr/local/bin/automysqlbackup
checking directory "/Volumes/Storage/autoMySQLBackup/daily" ... exists.
checking directory "/Volumes/Storage/autoMySQLBackup/weekly" ... exists.
checking directory "/Volumes/Storage/autoMySQLBackup/monthly" ... exists.
checking directory "/Volumes/Storage/autoMySQLBackup/latest" ... exists.
checking directory "/Volumes/Storage/autoMySQLBackup/tmp" ... exists.
checking directory "/Volumes/Storage/autoMySQLBackup/fullschema" ... exists.
checking directory "/Volumes/Storage/autoMySQLBackup/status" ... exists.

# Testing for installed programs
WARNING: Turning off multicore support, since pigz isn't there.
mysql ... found.
mysqldump ... found.

# Parsing databases ... done.
======================================================================
AutoMySQLBackup version 3.0
http://sourceforge.net/projects/automysqlbackup/

Backup of Database Server - localhost
Databases - //List of all my databases was here
======================================================================
======================================================================
Dump full schema.

Rotating 4 month backups for

======================================================================

======================================================================
Dump status.

Rotating 4 month backups for

======================================================================

Backup Start Time Tue Mar 21 13:04:42 PDT 2023
======================================================================
Daily Backup ...

Daily Backup of Database ( DatabaseName )
Rotating 6 day backups for DatabaseName
----------------------------------------------------------------------
//This repeats for every database that I have in mysql
----------------------------------------------------------------------

Backup End Time Tue Mar 21 13:05:14 PDT 2023
======================================================================
Total disk space used for backup storage...
Size - Location
595M /Volumes/Storage/autoMySQLBackup

======================================================================

###### WARNING ######
Errors reported during AutoMySQLBackup execution.. Backup failed
Error log below..
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1142: SELECT, LOCK TABLES command denied to user 'michael'@'localhost' for table 'accounts' when using LOCK TABLES
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
michael on MBP (c for commands)
~/Downloads/AutoMySQLBackup-3.0.

And here is my config file:

#version=3.0_rc2
# DONT'T REMOVE THE PREVIOUS VERSION LINE!
#
# Uncomment to change the default values (shown after =)
# WARNING:
# This is not true for UMASK, CONFIG_prebackup and CONFIG_postbackup!!!
#
# Default values are stored in the script itself. Declarations in
# /etc/automysqlbackup/automysqlbackup.conf will overwrite them. The
# declarations in here will supersede all other.

# Edit $PATH if mysql and mysqldump are not located in /usr/local/bin:/usr/bin:/bin:/usr/local/mysql/bin
#PATH=${PATH}:FULL_PATH_TO_YOUR_DIR_CONTAINING_MYSQL:FULL_PATH_TO_YOUR_DIR_CONTAINING_MYSQLDUMP

# Basic Settings

# use encrypted authentication credentials
# yes: through login path
# no: through username and password
# #CONFIG_mysql_dump_encrypted_login='no'
CONFIG_mysql_dump_encrypted_login='no'

# since mysql 5.6.x connections can be stored securely
# add your connection with
# mysql_config_editor set --login-path=automysqldump --host=localhost --user=root --password
# automysqldump is using the login-path "automysqldump" as default
#CONFIG_mysql_dump_login_path='automysqldump'
CONFIG_mysql_dump_login_path='automysqldump'

# Path to the mysql login configuration file.
# Sometimes if the script is running in a cronjob, the mysql login configuration file cannot be located. 
# I.e. set it to '/root/.mylogin.cnf' or '/home/username/.mylogin.cnf'
#CONFIG_mysql_dump_login_path_file=''
CONFIG_mysql_dump_login_path_file='/Users/michael/MySQL/my.cnf'

# Username to access the MySQL server e.g. dbuser
#CONFIG_mysql_dump_username='root'
CONFIG_mysql_dump_username='michael'

# Password to access the MySQL server e.g. password
#CONFIG_mysql_dump_password=''
CONFIG_mysql_dump_password='<Redacted>'

# Host name (or IP address) of MySQL server e.g localhost
#CONFIG_mysql_dump_host='localhost'
CONFIG_mysql_dump_host='localhost'

# "Friendly" host name of MySQL server to be used in email log
# if unset or empty (default) will use CONFIG_mysql_dump_host instead
#CONFIG_mysql_dump_host_friendly=''

# Backup directory location e.g /backups
# CONFIG_backup_dir='/var/backup/db'
CONFIG_backup_dir='/Volumes/Storage/autoMySQLBackup'


# This is practically a moot point, since there is a fallback to the compression
# functions without multicore support in the case that the multicore versions aren't
# present in the system. Of course, if you have the latter installed, but don't want
# to use them, just choose no here.
# pigz -> gzip
# pbzip2 -> bzip2
#CONFIG_multicore='yes'

# Number of threads (= occupied cores) you want to use. You should - for the sake
# of the stability of your system - not choose more than (#number of cores - 1).
# Especially if the script is run in background by cron and the rest of your system
# has already heavy load, setting this too high, might crash your system. Assuming
# all systems have at least some sort of HyperThreading, the default is 2 threads.
# If you wish to let pigz and pbzip2 autodetect or use their standards, set it to
# 'auto'.
#CONFIG_multicore_threads=2
CONFIG_multicore_threads=8

# Databases to backup

# List of databases for Daily/Weekly Backup e.g. ( 'DB1' 'DB2' 'DB3' ... )
# set to (), i.e. empty, if you want to backup all databases
#CONFIG_db_names=()
CONFIG_db_names=()

# You can use
#declare -a MDBNAMES=( "${DBNAMES[@]}" 'added entry1' 'added entry2' ... )
# INSTEAD to copy the contents of $DBNAMES and add further entries (optional).

# List of databases for Monthly Backups.
# set to (), i.e. empty, if you want to backup all databases
#CONFIG_db_month_names=()
CONFIG_db_month_names=()

# List of DBNAMES to EXLUCDE if DBNAMES is empty, i.e. ().
#CONFIG_db_exclude=( 'information_schema' )

# List of tables to exclude, in the form db_name.table_name
# You may use wildcards for the table names, i.e. 'mydb.a*' selects all tables starting with an 'a'.
# However we only offer the wildcard '*', matching everything that could appear, which translates to the
# '%' wildcard in mysql.
#CONFIG_table_exclude=()


# Advanced Settings

# Rotation Settings

# Which day do you want monthly backups? (01 to 31)
# If the chosen day is greater than the last day of the month, it will be done
# on the last day of the month.
# Set to 0 to disable monthly backups.
#CONFIG_do_monthly="01"
CONFIG_do_monthly="01"

# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
# Set to 0 to disable weekly backups.
#CONFIG_do_weekly="5"
CONFIG_do_weekly="5"

# Set rotation of daily backups. VALUE*24hours
# If you want to keep only today's backups, you could choose 1, i.e. everything older than 24hours will be removed.
#CONFIG_rotation_daily=6
CONFIG_rotation_daily=6

# Set rotation for weekly backups. VALUE*24hours
#CONFIG_rotation_weekly=35
CONFIG_rotation_weekly=35

# Set rotation for monthly backups. VALUE*24hours
#CONFIG_rotation_monthly=150
CONFIG_rotation_monthly=150


# Server Connection Settings

# Set the port for the mysql connection
#CONFIG_mysql_dump_port=3306
CONFIG_mysql_dump_port=3306

# Compress communications between backup server and MySQL server?
#CONFIG_mysql_dump_commcomp='no'

# Use ssl encryption with mysqldump?
#CONFIG_mysql_dump_usessl='yes'
CONFIG_mysql_dump_usessl='no'

# For connections to localhost. Sometimes the Unix socket file must be specified.
#CONFIG_mysql_dump_socket=''

# The maximum size of the buffer for client/server communication. e.g. 16MB (maximum is 1GB)
#CONFIG_mysql_dump_max_allowed_packet=''
CONFIG_mysql_dump_max_allowed_packet='1GB'

# The maximum size of the buffer for client/server communication. e.g. 16MB (maximum is 1GB)
#CONFIG_mysql_dump_max_allowed_packet=''

# This option sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with
# transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time
# when BEGIN was issued without blocking any applications.
#
# When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For
# example, any MyISAM or MEMORY tables dumped while using this option may still change state.
#
# While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and
# binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE,
# DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of
# them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table
# contents to obtain incorrect contents or fail.
#CONFIG_mysql_dump_single_transaction='no'

# http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_master-data
# --master-data[=value] 
# Use this option to dump a master replication server to produce a dump file that can be used to set up another
# server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates
# the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates
# from which the slave should start replicating after you load the dump file into the slave.
#
# If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only;
# it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment
# and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.
#
# This option requires the RELOAD privilege and the binary log must be enabled. 
#
# The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless
# --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the
# beginning of the dump (see the description for --single-transaction). In all cases, any action on logs happens at
# the exact moment of the dump.
# ==================================================================================================================
# possible values are 1 and 2, which correspond with the values from mysqldump
# VARIABLE=    , i.e. no value, turns it off (default)
#
#CONFIG_mysql_dump_master_data=

# Included stored routines (procedures and functions) for the dumped databases in the output. Use of this option
# requires the SELECT privilege for the mysql.proc table. The output generated by using --routines contains
# CREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. However, these statements do not
# include attributes such as the routine creation and modification timestamps. This means that when the routines
# are reloaded, they will be created with the timestamps equal to the reload time.
#
# If you require routines to be re-created with their original timestamp attributes, do not use --routines. Instead,
# dump and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges
# for the mysql database. 
#
# This option was added in MySQL 5.0.13. Before that, stored routines are not dumped. Routine DEFINER values are not
# dumped until MySQL 5.0.20. This means that before 5.0.20, when routines are reloaded, they will be created with the
# definer set to the reloading user. If you require routines to be re-created with their original definer, dump and
# load the contents of the mysql.proc table directly as described earlier.
#
CONFIG_mysql_dump_full_schema='yes'

# Backup status of table(s) in textfile. This is very helpful when restoring backups, since it gives an idea, what changed
# in the meantime.
#CONFIG_mysql_dump_dbstatus='yes'
CONFIG_mysql_dump_dbstatus='yes'

# Backup dump settings

# Include CREATE DATABASE in backup?
#CONFIG_mysql_dump_create_database='no'
CONFIG_mysql_dump_create_database='yes'

# Separate backup directory and file for each DB? (yes or no)
#CONFIG_mysql_dump_use_separate_dirs='yes'
CONFIG_mysql_dump_use_separate_dirs='yes'

# Choose Compression type. (gzip or bzip2)
#CONFIG_mysql_dump_compression='gzip'

# Store an additional copy of the latest backup to a standard
# location so it can be downloaded by third party scripts.
#CONFIG_mysql_dump_latest='no'

# Remove all date and time information from the filenames in the latest folder.
# Runs, if activated, once after the backups are completed. Practically it just finds all files in the latest folder
# and removes the date and time information from the filenames (if present).
CONFIG_mysql_dump_latest_clean_filenames='no'

# Create differential backups. Master backups are created weekly at #$CONFIG_do_weekly weekday. Between master backups,
# diff is used to create differential backups relative to the latest master backup. In the Manifest file, you find the
# following structure
# $filename 	md5sum	$md5sum	diff_id	$diff_id	rel_id	$rel_id
# where each field is separated by the tabular character '\t'. The entries with $ at the beginning mean the actual values,
# while the others are just for readability. The diff_id is the id of the differential or master backup which is also in
# the filename after the last _ and before the suffixes begin, i.e. .diff, .sql and extensions. It is used to relate
# differential backups to master backups. The master backups have 0 as $rel_id and are thereby identifiable. Differential
# backups have the id of the corresponding master backup as $rel_id.
#
# To ensure that master backups are kept long enough, the value of $CONFIG_rotation_daily is set to a minimum of 21 days.
#
#CONFIG_mysql_dump_differential='no'
CONFIG_mysql_dump_differential='no'


# Notification 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.
#CONFIG_mailcontent='stdout'

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
#CONFIG_mail_maxattsize=4000

# Allow packing of files with tar and splitting it in pieces of CONFIG_mail_maxattsize.
#CONFIG_mail_splitandtar='yes'

# Use uuencode instead of mutt. WARNING: Not all email clients work well with uuencoded attachments.
#CONFIG_mail_use_uuencoded_attachments='no'

# Email Address to send mail to? ([email protected])
#CONFIG_mail_address='root'


# Encryption

# Do you wish to encrypt your backups using openssl?
#CONFIG_encrypt='no'
CONFIG_encrypt='no'

# Choose a password to encrypt the backups.
#CONFIG_encrypt_password='password0123'

# Other

# Backup local files, i.e. maybe you would like to backup your my.cnf (mysql server configuration), etc.
# These files will be tar'ed, depending on your compression option CONFIG_mysql_dump_compression compressed and
# depending on the option CONFIG_encrypt encrypted.
#
# Note: This could also have been accomplished with CONFIG_prebackup or CONFIG_postbackup.
#CONFIG_backup_local_files=()
CONFIG_backup_local_files=()

# Command to run before backups (uncomment to use)
#CONFIG_prebackup="/etc/mysql-backup-pre"

# Command run after backups (uncomment to use)
#CONFIG_postbackup="/etc/mysql-backup-post"

# Uncomment to activate! This will give folders rwx------
# and files rw------- permissions.
#umask 0077

# dry-run, i.e. show what you are gonna do without actually doing it
# inactive: =0 or commented out
# active: uncommented AND =1
#CONFIG_dryrun=1

Is there anything I can do to fix this error? I did run the command as specified in the config file, here is the snapshot of how that went:

~ $mysql_config_editor set --login-path=automysqldump --host=localhost --user=michael --password
Enter password:
michael on MBP (c for commands)
~ $

What am I doing wrong?

Thank you,

Mike Sims

Database Backups weight lower than should be

Hi,

I just installed the script and its working wonderfully, however, I notice that the backups weight are much lower than espected, in fact, when I try to import those backups, they crashed. I have no error in the error log but when I launch the executable with bash -x I get:

• echo 'Errors reported during AutoMySQLBackup execution.. Backup failed'
  Errors reported during AutoMySQLBackup execution.. Backup failed
• echo 'Error log below..'
  Error log below..
• cat /var/AllBackup/sitesDatabases//ERRORS_127.0.0.1-042440251.log

When I go to read that error log file, it does not exist by such name. Any inisghts about what could be happening?

Daily rotation not working

Hi,

I've set the "CONFIG_rotation_daily" to "1", but backups older than 24 hours are not being deleted.
I installed this on a server with very little space left, so when this feature wasn't working, the hard drive was full within 3 days.

Small incompatibilities with MacOS sed/du

I appreciate the enhancements in this fork but couldn't use as-is it due to two very small differences between the GNU sed and du commands. Here are my suggested changes to make it work on MacOS:

Line 103 insert:

# OS dependent options (showstoppers on MacOS)
  if [[ $OSTYPE == darwin* ]]; then
    OS_sed_option='-E'
    OS_du_extra_option=''
  else
    OS_sed_option='-r'
    OS_du_extra_option='--si'
  fi

Line 105 change from:

     NODOT_VER=`echo $VERSION | sed -r 's/\.//g'`

to:

     NODOT_VER=`echo $VERSION | sed $OS_sed_option 's/\.//g'`

Line 1997 change from:

	echo `du -hs --si "${CONFIG_backup_dir}"`

to

	echo `du -hs $OS_du_extra_option "${CONFIG_backup_dir}"`

With those changes I was able to use the script on MacOS.

MariaDB doesn't honor --login-path

Per the docs, MariaDB doesn't support the --login-path option. It would be nice if the README.md made note of this.

Segmentation fault (core dumped) $MYSQL "${mysql_opt[@]}" --batch --skip-column-names -e "show databases"

Hi guys,
first of all thank you to @BigAndini and all other contributors for this AutoMySQLBackup fork and further development!

On a server I recently set up I have the following error every time we run automysqlbackup:

/usr/local/bin/automysqlbackup: line 1080: 120421 Segmentation fault      (core dumped) $MYSQL "${mysql_opt[@]}" --batch --skip-column-names -e "show databases"

I'm sure that the error happens in the parse_databases function:

https://github.com/sixhop/AutoMySQLBackup/blob/master/automysqlbackup#L1189

With our configuration $MYSQL is mysql --login-path=client and ${mysql_opt[@]} is empty.
If I run the following command directly in the server bash shell I have no error:

mysql --login-path=client --batch --skip-column-names -e "show databases"

I have no idea how to debug it.
My server is an Ubuntu Server 20.04 with MySQL 8.0.25.

Do you have any clue?

Ubuntu 18 (OpenSSL 1.1.1) Warning "deprecated key derivation used"

WARNING

Errors reported during AutoMySQLBackup execution.. Backup failed
Error log below..
*** WARNING : deprecated key derivation used.
Using -iter or -pbkdf2 would be better.

I was able to bypass this by adding -pbkdf2 -iter 1000 to the openssl encryption call:
This:
openssl enc -aes-256-cbc -e -in ${1} -out ${1}.enc -pass pass:"${CONFIG_encrypt_password}"

Become this: _
openssl enc -aes-256-cbc -pbkdf2 -iter 1000 -e -in ${1} -out ${1}.enc -pass pass:"${CONFIG_encrypt_password}"_

Hourly Backups?

How can I configure hourly backups for a specific DB?

I have daily and monthly configured for several DBs - I just want one specific DB to backup more regularly and I'm fine with the hourly backup over-writing the previous one.

integer expression expected on Ubuntu 20.04.1 LTS (MySQL 8 not working)

I recently upgraded my dev enviroment to Ubuntu 20.04.1 LTS with MySQL 8.0.20. After this upgrade the script stopped working with the following error. (I'm using login-path method)

/usr/local/bin/automysqlbackup: line 506: [: 8020 02004: integer expression expected

After some investigation I found out this is from the version of MySQL and Ubuntu.

Line 116 and 117 in automysqlbackup

VERSION=`mysql -V | grep -oE "[0-9]+\.[0-9]+\.[0-9]+"`
NODOT_VER=`echo $VERSION | sed -${sed_Regex} 's/\.//g'`

MySQL output
Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu)
Rexexp as 8020 02004 which matches the error message in line 506.

My temporary solution

If i change the regexp in line 116 I get MySQL 8 working by removing the Ubuntu 02004 part

VERSION=`mysql -V | grep -oE "[1-9]+\.[0-9]+\.[0-9]+"`

After this change the backup works again!

Not sure this is a good solution though? It's a fix for me but you probably have a better solution that knows the rest of the script and where there can be more problems.

Whitespace and a syntax error

First of all, I'm really glad you forked this from sourceforge and that there is now a place again for maintaining this really useful script. I'll be submitting a couple of PRs to fix two minor issues that I had proposed before on the original code as well:

• Remove trailing spaces
• Fix a syntax error in a if statement

Hope you can review them and consider merging. I will also open another issue with a feature request that I will be explaining there separately.

mysqlshow: command not found

Hi,

I fire the script on a synology nas with latest DSM operating system and it seems that mysqlshow command is not part of the mariadb package.

Is there any chance you would check for mysqlshow or mariadb-show existence, as it seems to be a breaking change from latest mariadb versions:

https://mariadb.com/kb/en/mysqlshow/

[Feature Request] Option to export structure for each database individually

Currently the script generates daily, weekly and monthly schema exports for all databases at once in the fullschema subdirectory. We do require schema files for each database individually and would like to implement that in a PR if we can agree with the maintainers on the approach to take.

What's our use case?

We are hosting Drupal sites with multiple sites on each host and each site has their own mysql db. As those databases contain session and caching tables that are not required for the backups, we do exclude them with the CONFIG_table_exclude configuration settings.

In cases where we have to restore a backup for an individual Drupal database, we first drop the database and then source the backup file which will then recreate all the tables except those that got excluded. But we can't use the fullschema either, because that would have an impact on other databases on the same host which we don't want at that time.

If we had a schema file matching each of the dumps, we could first source that schema file followed by sourcing the dump and then would get exactly what we were looking for.

Proposed solution:

There should be a new config option (defaults to "no") which could be set to "yes" if one was looking for individual db schema files. If that was turned on, in process_dbs() we could simply execute a second command which also dump the schema and appends .schema to the filename.

What do you think?

Reporting a vulnerability

Hello!

I hope you are doing well!

We are a security research team. Our tool automatically detected a vulnerability in this repository. We want to disclose it responsibly. GitHub has a feature called Private vulnerability reporting, which enables security research to privately disclose a vulnerability. Unfortunately, it is not enabled for this repository.

Can you enable it, so that we can report it?

Thanks in advance!

PS: you can read about how to enable private vulnerability reporting here: https://docs.github.com/en/code-security/security-advisories/repository-security-advisories/configuring-private-vulnerability-reporting-for-a-repository

Backup files for slave setup so not have correct binlog file/pos

When doing backups of a master server that are going to be used for slave restore and replication (eg. CONFIG_mysql_dump_master_data=2), and where 1 or more of the dbs on the master server are large, there is the possibility that the binlog file/pos values may be different between the dbs due to --single-transaction happening on a per db basis and time passes between the 1st db being done and the others. Here is an example:

• Backups are set to start at 3am
• CONFIG_mysql_dump_single_transaction='yes'
• CONFIG_mysql_dump_master_data=2

After backup, and when checking data/time of backup files and binlog file/pos:

Aug 21 05:18 db1.sql.gz (large) - mysql-bin.009304/200746006
Aug 21 05:18 db2.sql.gz (small) - mysq-bin.006306/978513887
Aug 21 05:18 db3.sql.gz (small) - mysq-bin.006306/978513887

This makes sense because binlog file/pos is taken at start of backup and time passes between the 1st backup and the other 2. Is there a way to use automysqlbackup for slave restores where the dbs are large and keep same binlog file/pos across all dbs?

The only other option is to do manual backups with 'flush tables with read lock' enabled.

Regards, Robby

unknown option '--ssl'.

I recently upgraded OS and server now running Mysql 8.0

I get the error:

mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [ERROR] unknown option '--ssl'.

So I turn off SSL in the config... is there a better way?

WARNING: Forcing protocol to TCP due to option specification. Please explicitly state intended protocol.

I just started getting this warning message since upgrading slackware current. I ran bash -x and bash -v and can't find why this is triggered.

backup MariaDB DB

Hi, I would like to try the script for my mariadb. I have read the details below
'To use backup MariaDB databases, specify CONFIG_mysql_dump_username and CONFIG_mysql_dump_password like you would with the SourceForge version.'
Because mariadb doesn't come with 'mysql_config_editor ' you advise Add login path by add username and password in config file, am i right?
When u mentioned '...with the SourceForge version'... meaning that I cannot use your script here?
Please advise me further. Thank you

How can I use --ssl-mode instead -ssl ?

mysql: [Warning] Using a password on the command line interface can be insecure.
WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.

I check # Use ssl encryption with mysqldump?
CONFIG_mysql_dump_usessl='yes'

Error: The mysql server is empty, i.e. no databases found. Check if something is wrong. Exiting.

testing on mariadb 10.1 and failed
i have set mysql server with root and root mysql password and get this error:

automysqlbackup]# /usr/local/bin/automysqlbackup /etc/automysqlbackup/automysqlbackup.conf
checking directory "/var/backup/db/daily" ... exists.
checking directory "/var/backup/db/weekly" ... exists.
checking directory "/var/backup/db/monthly" ... exists.
checking directory "/var/backup/db/latest" ... exists.
checking directory "/var/backup/db/tmp" ... exists.
checking directory "/var/backup/db/fullschema" ... exists.
checking directory "/var/backup/db/status" ... exists.

Testing for installed programs

pigz ... found.
mysql ... found.
mysqldump ... found.

Parsing databases ... Note: Parsed config file /etc/automysqlbackup/automysqlbackup.conf.

Error: The mysql server is empty, i.e. no databases found. Check if something is wrong. Exiting.

WARNING

Errors reported during AutoMySQLBackup execution.. Backup failed
Error log below..
mysql: unknown variable 'ssl-mode=REQUIRED'

How to make weekly and monthly backups work with manual install

I've installed AutoMySQLBackup manually by putting the following bash script in my /etc/cron.hourly folder.

#!/bin/sh

/opt/automysqlbackup/automysqlbackup /opt/automysqlbackup/automysqlbackup.conf

chown root.root /opt/automysqlbackup/backup/db* -R
find /opt/automysqlbackup/backup/db* -type f -exec chmod 400 {} \;
find /opt/automysqlbackup/backup/db* -type d -exec chmod 700 {} \;

Will weekly and monthly backups also work this way? If not, what can I do to make them work with a fully manual install?

I'm guessing they willl not since the program doesn't seem to know what kind of backup it's doing (everything gets stored under the /daily folder).

max_execution_time is not available in MySQL 5.6

When trying to backup a DB using the latest version (after commit f61b83f), we get the following errors :

ERROR 1193 (HY000) at line 1: Unknown system variable 'max_execution_time'
ERROR 1193 (HY000) at line 1: Unknown system variable 'max_execution_time'

max_execution_time should not be used on mysql 5.6. This variable had been introduced in MySQL 5.7.8

[Bug] ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

I got the error after I finished (though the backup was successful)

Run command sudo /usr/local/bin/automysqlbackup:

Log /var/log/Backup_Post_Pre_log:

My environment:
NAME="Amazon Linux AMI"
ID="amzn"
ID_LIKE="rhel fedora"
PRETTY_NAME="Amazon Linux AMI 2018.03"
Mysql5.6

Copying to "latest" with encryption results in "No such file or directory" error

If automysqlbackup is configured to create "latest" copies of the backup files and encryption is enabled then the filename generated contains a duplicate suffix which results in a "No such file or directory" error.

For example:

Backup Start Time Thu Jul 12 13:33:02 PDT 2018
======================================================================
Daily Backup ...

Daily Backup of Database ( foo_db )
Rotating 6 day backups for foo_db
Successfully encrypted archive as /var/backup/db/daily/foo_db/daily_foo_db_2018-07-12_13h32m_Thursday.sql.gz.enc

cp: cannot stat '/var/backup/db/daily/foo_db/daily_foo_db_2018-07-12_13h32m_Thursday.sql.gz.gz.enc': No such file or directory

CONFIG_mysql_dump_latest='yes'
CONFIG_mysql_dump_latest_clean_filenames='yes'
CONFIG_encrypt='yes'

AutoMySQLBackup/automysqlbackup

Line 336 in be8283a

cp -al "${1}${suffix}.enc" "${CONFIG_backup_dir}"/latest/

Can you support mydumper ？

mydumper Strong performance

Speed up imports

Can you add on mysqldump to speed up big imports?

--extended-insert: it makes sure that it is not one INSERT per line, meaning a single statement can have dozens of rows.
--quick: useful when dumping large tables, by default MySQL reads the whole table in memory then dumps into a file, that way the data is streamed without consuming much memory.

No mails

I see tmp folder created
But nothing more. Backups being done, but no mail sent. Silently.
Mail sending itself tested and it works.
mutt and mail binaries in place.

Bad filedescriptor loop -Centos 8 and Mysql 8

Trying to get automysql running with a Mysql 8 database.

On first run it appeared to be stalled:

automysqlbackup /etc/automysqlbackup/automysqlbackup3.mysql.conf

# Checking for permissions to write to folders:
base folder /home/MySQL-Backup/backups ... exists ... ok.
backup folder /home/MySQL-Backup/backups/mysql-db ... exists ... writable? yes. Proceeding.

NOTE: here is where it hangs..... rest appears after the Control-C

^Cchecking directory "/home/MySQL-Backup/backups/mysql-db/daily" ... exists.
checking directory "/home/MySQL-Backup/backups/mysql-db/weekly" ... exists.
checking directory "/home/MySQL-Backup/backups/mysql-db/monthly" ... exists.
checking directory "/home/MySQL-Backup/backups/mysql-db/latest" ... exists.
checking directory "/home/MySQL-Backup/backups/mysql-db/tmp" ... exists.
checking directory "/home/MySQL-Backup/backups/mysql-db/fullschema" ... exists.
checking directory "/home/MySQL-Backup/backups/mysql-db/status" ... exists.

# Testing for installed programs
pigz ... found.
mysql ... found.
mysqldump ... found.

# Parsing databases ... done.
======================================================================
AutoMySQLBackup version 3.0
http://sourceforge.net/projects/automysqlbackup/

Backup of Database Server - localhost
Databases - drupal8,redacted1,information_schema,redacted2,redacted3,redacted4,redacted5,mysql,performance_schema,phpmyadmin,redacted6,redacted7
Databases (monthly) - drupal8,redacted1,information_schema,redacted2,redacted3,redacted4,redacted5,mysql,performance_schema,phpmyadmin,redacted6,redacted7

======================================================================
======================================================================
Dump full schema.


###### WARNING ######
Errors reported during AutoMySQLBackup execution.. Backup failed
Error log below..
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.

======================================================================

======================================================================
Dump status.

mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Rotating 4 month backups for

======================================================================

Backup Start Time Mon Nov  2 14:30:31 GMT 2020
======================================================================
Daily Backup ...

Daily Backup of Database ( drupal8 )
mysqldump: [Warning] Using a password on the command line interface can be insecure.
^C/usr/local/bin/automysqlbackup: line 835: 6: Bad file descriptor
/usr/local/bin/automysqlbackup: line 836: 7: Bad file descriptor
Skipping normal output methods, since the program exited before any log files could be created.
dbdump with parameters "drupal8,redacted1,information_schema,redacted2,redacted3,redacted4,redacted5,mysql,performance_schema,phpmyadmin,redacted6,redacted7" "/home/MySQL-Backup/backups/mysql-db/daily/drupal8/daily_drupal8_2020-11-02_14h28m_Monday.sql.gz" failed!

Backup End Time Mon Nov  2 14:31:39 GMT 2020
======================================================================
Total disk space used for backup storage...
Size - Location
136K /home/MySQL-Backup/backups/mysql-db

======================================================================
/usr/local/bin/automysqlbackup: line 835: 6: Bad file descriptor
/usr/local/bin/automysqlbackup: line 836: 7: Bad file descriptor
Skipping normal output methods, since the program exited before any log files could be created.
[root@host MySQL-Backup]# ^C

Second run:

# automysqlbackup /etc/automysqlbackup/automysqlbackup3.mysql.conf

# Checking for permissions to write to folders:
base folder /home/MySQL-Backup/backups ... exists ... ok.
backup folder /home/MySQL-Backup/backups/mysql-db ... exists ... writable? yes. Proceeding.

Here it hangs.....

^Cchecking directory "/home/MySQL-Backup/backups/mysql-db/daily" ... exists.
checking directory "/home/MySQL-Backup/backups/mysql-db/weekly" ... exists.
checking directory "/home/MySQL-Backup/backups/mysql-db/monthly" ... exists.
checking directory "/home/MySQL-Backup/backups/mysql-db/latest" ... exists.
checking directory "/home/MySQL-Backup/backups/mysql-db/tmp" ... exists.
checking directory "/home/MySQL-Backup/backups/mysql-db/fullschema" ... exists.
checking directory "/home/MySQL-Backup/backups/mysql-db/status" ... exists.

# Testing for installed programs
pigz ... found.
mysql ... found.
mysqldump ... found.

# Parsing databases ...
###### WARNING ######
Errors reported during AutoMySQLBackup execution.. Backup failed
Error log below..
mysql: [Warning] Using a password on the command line interface can be insecure.
^C/usr/local/bin/automysqlbackup: line 835: 6: Bad file descriptor
/usr/local/bin/automysqlbackup: line 836: 7: Bad file descriptor
Skipping normal output methods, since the program exited before any log files could be created.
^C/usr/local/bin/automysqlbackup: line 835: 6: Bad file descriptor
/usr/local/bin/automysqlbackup: line 836: 7: Bad file descriptor
Skipping normal output methods, since the program exited before any log files could be created.
^C/usr/local/bin/automysqlbackup: line 835: 6: Bad file descriptor
/usr/local/bin/automysqlbackup: line 836: 7: Bad file descriptor
Skipping normal output methods, since the program exited before any log files could be created.

Stuck in loop above - have to kill the two automysqlbackup pids with -9

OS is Centos 8 and Mysql 8.

Any ideas on how to fix this?

Here is the config file:

CONFIG_mysql_dump_username='root'
CONFIG_mysql_dump_password='redacted'
CONFIG_backup_dir='/home/MySQL-Backup/backups/mysql-db'
CONFIG_db_exclude=( "redacted8" "redacted9" "performance_schema" "information_schema" )
CONFIG_do_weekly=6
CONFIG_mysql_dump_usessl='no'

Supplied more than one argument, ignoring ALL arguments - using default and global config file only.

Hi,
For some reason, the login info my freshly created config file does not seem to be used in good manner. I get this error:

Parsing databases ... Note: Supplied more than one argument, ignoring ALL arguments - using default and global config file only.
Error: The mysql server is empty, i.e. no databases found. Check if something is wrong. Exiting.

###### WARNING ######
Errors reported during AutoMySQLBackup execution.. Backup failed
Error log below..
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Paths are being parsed okay, it's just the part where mysql is being called. All is run on Debian 12.
Below my config. Any ideas? Thanks in advance!

#version=3.0_rc2
# DONT'T REMOVE THE PREVIOUS VERSION LINE!
#
# Uncomment to change the default values (shown after =)
# WARNING:
# This is not true for UMASK, CONFIG_prebackup and CONFIG_postbackup!!!
#
# Default values are stored in the script itself. Declarations in
# /etc/automysqlbackup/automysqlbackup.conf will overwrite them. The
# declarations in here will supersede all other.

# Edit $PATH if mysql and mysqldump are not located in /usr/local/bin:/usr/bin:/bin:/usr/local/mysql/bin
#PATH=${PATH}:FULL_PATH_TO_YOUR_DIR_CONTAINING_MYSQL:FULL_PATH_TO_YOUR_DIR_CONTAINING_MYSQLDUMP

# Basic Settings

# use encrypted authentication credentials
# yes: through login path
# no: through username and password
#CONFIG_mysql_dump_encrypted_login='no'

# since mysql 5.6.x connections can be stored securely
# add your connection with
# mysql_config_editor set --login-path=automysqldump --host=localhost --user=root --password
# automysqldump is using the login-path "automysqldump" as default
#CONFIG_mysql_dump_login_path='automysqldump'

# Path to the mysql login configuration file.
# Sometimes if the script is running in a cronjob, the mysql login configuration file cannot be located.
# I.e. set it to '/root/.mylogin.cnf' or '/home/username/.mylogin.cnf'
#CONFIG_mysql_dump_login_path_file='/etc/automysqlbackup/myserver.conf'

# Username to access the MySQL server e.g. dbuser
CONFIG_mysql_dump_username='root'

# Password to access the MySQL server e.g. password
CONFIG_mysql_dump_password='<redacted>'

# Host name (or IP address) of MySQL server e.g localhost
#CONFIG_mysql_dump_host='localhost'

# "Friendly" host name of MySQL server to be used in email log
# if unset or empty (default) will use CONFIG_mysql_dump_host instead
CONFIG_mysql_dump_host_friendly='statler'

# Backup directory location e.g /backups
CONFIG_backup_dir='/var/backups/mysql'

# This is practically a moot point, since there is a fallback to the compression
# functions without multicore support in the case that the multicore versions aren't
# present in the system. Of course, if you have the latter installed, but don't want
# to use them, just choose no here.
# pigz -> gzip
# pbzip2 -> bzip2
# xz -T -> xz
#CONFIG_multicore='yes'

# Number of threads (= occupied cores) you want to use. You should - for the sake
# of the stability of your system - not choose more than (#number of cores - 1).
# Especially if the script is run in background by cron and the rest of your system
# has already heavy load, setting this too high, might crash your system. Assuming
# all systems have at least some sort of HyperThreading, the default is 2 threads.
# If you wish to let pigz and pbzip2 autodetect or use their standards, set it to
# 'auto'.
#CONFIG_multicore_threads=2

# Databases to backup

# List of databases for Daily/Weekly Backup e.g. ( 'DB1' 'DB2' 'DB3' ... )
# set to (), i.e. empty, if you want to backup all databases
#CONFIG_db_names=()
# You can use
#declare -a MDBNAMES=( "${DBNAMES[@]}" 'added entry1' 'added entry2' ... )
# INSTEAD to copy the contents of $DBNAMES and add further entries (optional).

# List of databases for Monthly Backups.
# set to (), i.e. empty, if you want to backup all databases
#CONFIG_db_month_names=()

# List of DBNAMES to EXLUCDE if DBNAMES is empty, i.e. ().
CONFIG_db_exclude=( 'performance_schema' 'information_schema' )

# List of DBNAMES patterns to EXLUCDE if DBNAMES is empty, i.e. ().
CONFIG_db_exclude_pattern=()

# List of tables to exclude, in the form db_name.table_name
# You may use wildcards for the table names, i.e. 'mydb.a*' selects all tables starting with an 'a'.
# However we only offer the wildcard '*', matching everything that could appear, which translates to the
# '%' wildcard in mysql.
#CONFIG_table_exclude=()


# Advanced Settings

# Rotation Settings

# Which day do you want monthly backups? (01 to 31)
# If the chosen day is greater than the last day of the month, it will be done
# on the last day of the month.
# Set to 0 to disable monthly backups.
#CONFIG_do_monthly='01'

# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
# Set to 0 to disable weekly backups.
#CONFIG_do_weekly='5'

# Set rotation of daily backups. VALUE*24hours
# If you want to keep only today's backups, you could choose 1, i.e. everything older than 24hours will be removed.
#CONFIG_rotation_daily=6

# Set rotation for weekly backups. VALUE*24hours
#CONFIG_rotation_weekly=35

# Set rotation for monthly backups. VALUE*24hours
#CONFIG_rotation_monthly=150


# Server Connection Settings

# Set the port for the mysql connection
#CONFIG_mysql_dump_port=3306

# Compress communications between backup server and MySQL server?
#CONFIG_mysql_dump_commcomp='no'

# Use ssl encryption with mysqldump?
#CONFIG_mysql_dump_usessl='no'

# For connections to localhost. Sometimes the Unix socket file must be specified.
#CONFIG_mysql_dump_socket=''

# The maximum size of the buffer for client/server communication. e.g. 16MB (maximum is 1GB)
#CONFIG_mysql_dump_max_allowed_packet=''

# This option sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with
# transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time
# when BEGIN was issued without blocking any applications.
#
# When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For
# example, any MyISAM or MEMORY tables dumped while using this option may still change state.
#
# While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and
# binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE,
# DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of
# them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table
# contents to obtain incorrect contents or fail.
#CONFIG_mysql_dump_single_transaction='no'

# http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_master-data
# --master-data[=value]
# Use this option to dump a master replication server to produce a dump file that can be used to set up another
# server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates
# the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates
# from which the slave should start replicating after you load the dump file into the slave.
#
# If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only;
# it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment
# and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.
#
# This option requires the RELOAD privilege and the binary log must be enabled.
#
# The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless
# --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the
# beginning of the dump (see the description for --single-transaction). In all cases, any action on logs happens at
# the exact moment of the dump.
# ==================================================================================================================
# possible values are 1 and 2, which correspond with the values from mysqldump
# VARIABLE=    , i.e. no value, turns it off (default)
#
#CONFIG_mysql_dump_master_data=

# Included stored routines (procedures and functions) for the dumped databases in the output. Use of this option
# requires the SELECT privilege for the mysql.proc table. The output generated by using --routines contains
# CREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. However, these statements do not
# include attributes such as the routine creation and modification timestamps. This means that when the routines
# are reloaded, they will be created with the timestamps equal to the reload time.
#
# If you require routines to be re-created with their original timestamp attributes, do not use --routines. Instead,
# dump and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges
# for the mysql database.
#
# This option was added in MySQL 5.0.13. Before that, stored routines are not dumped. Routine DEFINER values are not
# dumped until MySQL 5.0.20. This means that before 5.0.20, when routines are reloaded, they will be created with the
# definer set to the reloading user. If you require routines to be re-created with their original definer, dump and
# load the contents of the mysql.proc table directly as described earlier.
#
#CONFIG_mysql_dump_full_schema='yes'

# Backup status of table(s) in textfile. This is very helpful when restoring backups, since it gives an idea, what changed
# in the meantime.
#CONFIG_mysql_dump_dbstatus='yes'

# Backup dump settings

# Include CREATE EVENT in backup?
#CONFIG_mysql_dump_create_event='yes'

# Include CREATE DATABASE in backup?
#CONFIG_mysql_dump_create_database='no'

# Include DROP DATABASE in backup?
#CONFIG_mysql_dump_add_drop_database='no'

# Separate backup directory and file for each DB? (yes or no)
#CONFIG_mysql_dump_use_separate_dirs='yes'

# Choose Compression type. (gzip, bzip2 or xz)
#CONFIG_mysql_dump_compression='gzip'

# Use hex-blob for backup?
#CONFIG_mysql_dump_hex_blob='no'

# Store an additional copy of the latest backup to a standard
# location so it can be downloaded by third party scripts.
#CONFIG_mysql_dump_latest='no'

# Remove all date and time information from the filenames in the latest folder.
# Runs, if activated, once after the backups are completed. Practically it just finds all files in the latest folder
# and removes the date and time information from the filenames (if present).
#CONFIG_mysql_dump_latest_clean_filenames='no'

# Create differential backups. Master backups are created weekly at #$CONFIG_do_weekly weekday. Between master backups,
# diff is used to create differential backups relative to the latest master backup. In the Manifest file, you find the
# following structure
# $filename 	md5sum	$md5sum	diff_id	$diff_id	rel_id	$rel_id
# where each field is separated by the tabular character '\t'. The entries with $ at the beginning mean the actual values,
# while the others are just for readability. The diff_id is the id of the differential or master backup which is also in
# the filename after the last _ and before the suffixes begin, i.e. .diff, .sql and extensions. It is used to relate
# differential backups to master backups. The master backups have 0 as $rel_id and are thereby identifiable. Differential
# backups have the id of the corresponding master backup as $rel_id.
#
# To ensure that master backups are kept long enough, the value of $CONFIG_rotation_daily is set to a minimum of 21 days.
#
#CONFIG_mysql_dump_differential='no'


# Notification 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.
#CONFIG_mailcontent='stdout'

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
#CONFIG_mail_maxattsize=4000

# Allow packing of files with tar and splitting it in pieces of CONFIG_mail_maxattsize.
#CONFIG_mail_splitandtar='yes'

# Use uuencode instead of mutt. WARNING: Not all email clients work well with uuencoded attachments.
#CONFIG_mail_use_uuencoded_attachments='no'

# Email Address to send mail to? ([email protected])
#CONFIG_mail_address='root'


# Encryption

# Do you wish to encrypt your backups using openssl?
#CONFIG_encrypt='no'

# Choose a password to encrypt the backups.
#CONFIG_encrypt_password='password0123'

# Other

# Backup local files, i.e. maybe you would like to backup your my.cnf (mysql server configuration), etc.
# These files will be tar'ed, depending on your compression option CONFIG_mysql_dump_compression compressed and
# depending on the option CONFIG_encrypt encrypted.
#
# Note: This could also have been accomplished with CONFIG_prebackup or CONFIG_postbackup.
#CONFIG_backup_local_files=()

# Command to run before backups (uncomment to use)
#CONFIG_prebackup='/etc/mysql-backup-pre'

# Command run after backups (uncomment to use)
#CONFIG_postbackup='/etc/mysql-backup-post'

# Uncomment to activate! This will give folders rwx------
# and files rw------- permissions.
#umask 0077

# dry-run, i.e. show what you are gonna do without actually doing it
# inactive: =0 or commented out
# active: uncommented AND =1
#CONFIG_dryrun=1

install.sh fails md5sum preflight check

Hi,

When trying to run install.sh from AutoMySQLBackup-Master I get a md5sum failure for the automysqlbackup file.

inside the install.sh it lists the md5 as 33a3a48a418ccfa978b023cd5a8e2d0b however the hash i get for the bundled automysqlbackup file is 31e3b0fe937af7ba3c4015543cd2a848

Thanks

Custom backup dir doesn't work, dumps still in default /var/lib

Ubuntu 22.04 LTS
No reaction to any changes to
CONFIG_backup_dir='/var/backups/db'

All dumps (daily) manually executed (works) saved in
/var/lib/automysqlbackup/daily

md5sum failed :(

Attempted to run installer after cloning SSH repo, received an "md5sum failed :(" message for each file in the check. Worked around the issue by regenerating the md5sums of each file in the check (not ideal) and replacing them in the install.sh file. Running CentOS 6.8 (Final).

Missing text in log when creating main backup folder

Hi,
when creating main backup folder ($CONFIG_backup_dir) there is missing text in the log:

user@server ~ $ sudo /usr/local/bin/automysqlbackupcron
creating ... success.
checking directory "/var/backups/dball/daily" ... creating ... success.
checking directory "/var/backups/dball/weekly" ... creating ... success.

I guess the first line is for the main backup folder and it should looks like the other lines.

thx

weekly/monthly rotating does not work?

I moved last month from an old sf.net version to latest from github/sixshop. I also took latest conf file; weekly

CONFIG_do_monthly="01"
CONFIG_do_weekly="7"

I would expect that every Sunday a daily backup is rotated into weekly, every first day of the month in into monthly. Nothing appeared on July 1st....

Did I overlook something? It did work on the old version.

md5sum not available by default on macOS

Hi,

md5sum isn't available on macOS so install.sh fails for me:

### Checking archive files for existence, readability and integrity.

automysqlbackup ... exists and is readable ... md5sum failed :(

but md5 -r would work and openssl md5 is another option. See this comment which might help.

I'd like to install this on macOS High Sierra (10.13).

Single brackets in line 473

/usr/local/bin/automysqlbackup: line 473: [: : integer expression expected

- if [ "$NODOT_VER" -ge 56 ]; then
+ if [[ "$NODOT_VER" -ge 56 ]]; then

tables in dump are missing

Hello.
After backup runs, half of tables in dump are missing.

line 873: 6: Bad file descriptor

I just downloaded the newest version and I got these error messages:

/usr/local/bin/automysqlbackup: line 873: 6: Bad file descriptor
/usr/local/bin/automysqlbackup: line 874: 7: Bad file descriptor