Backup failing and erroring out on execution after finding the databases

[EasyG0ing1]

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? (user@domain.com)
#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

[conradhagemans]

I have the same. Did you ever solve this?

[conradhagemans]

Found it! Change this line in the config file: CONFIG_mysql_dump_encrypted_login='yes'

[solaceten]

I fixed it by moving to another similar but updated script here: https://github.com/belgotux/automysqlbackup