Database Backups not working after upgrading Server to Ubuntu 22.04.5 LTS

[xdazedx]

Hi, I upgraded my Ubuntu server to 22.04.5 LTS, and finally got it working, except my old script from knight-of-ni post
viewtopic.php?f=32&t=23815 that faithfully worked for 10 years,
and numerous upgrades finally stopped working (and yes, the Backup saved me a couple times in the most recent years).

The backups produce a file with nothing in it, and it looks like my error is that it wants a root password, and I tried putting it in my script but it didn't work. "Got error: 1045: "Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect" Mysql backup script completed successfully!

I checked my auth.log

Code: Select all

May 27 15:23:00 CCTV sshd[93016]: Accepted publickey for dazed from 192.168.69.18 port 54929 ssh2: RSA SHA256:xxxxxxxxxxxxxxxxxxxxxxxxxxx

I checked my syslog where I think I see the problem but cant figure out how to fix.

Code: Select all

May 27 15:23:01 CCTV mariadbd[863]: 2025-05-27 15:23:01 2793 [Warning] Access denied for user 'user'@'localhost' (using password: YES)

MySql database

Code: Select all

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zm                 |
+--------------------+
5 rows in set (0.001 sec)

This was the script knight-of-ni shared, and used to make backup of all databases. I didn't use/need any password before, but now it seems to want one, and like I said I tried inserting a pass into the script to no avail. When I run sudo mysql -p I can login with pass. I've been searching, tried adding all privileges in mysql (and flushed;), and even changed the password....Any help would be appreciated.

Code: Select all

#!/bin/bash

# Path to the tools this scripts needs
MYSQLDUMP="/usr/bin/mysqldump"
GZIP="/bin/gzip"
FIND="/usr/bin/find"

# Check to see if this script has access to all the commands it needs
for CMD in $MYSQLDUMP $GZIP $FIND; do
  type $CMD &> /dev/null

  if [ $? -ne 0 ]; then
    echo
    echo "ERROR: The script cannot find the required command \"${CMD}\"."
    echo
    exit 1
  fi
done

# Enter SQL database login credentials
USER="root"
PASS="mypass"

# Specify which databases to backup
DB="--all-databases"

# Specify where the databases will be saved to
BKUP_PATH="/path/to/your/backup/folder"

# Format for the timestamp
timestamp="$(date +%Y-%m-%d_%a)"

# Run the backup and compress it
error=0
${MYSQLDUMP} -u${USER} -p${PASS} --events --single-transaction --quick ${DB} | ${GZIP} -9 > ${BKUP_PATH}/Nightly_dB_Backup_${timestamp}.sql.gz

if [ "$?" -ne 0 ]; then
	echo
	echo "WARNING: Mysqldump returned an error!"
	echo
	error=1
fi

# Now find & delete old backups we no longer need
${FIND} ${BKUP_PATH} -maxdepth 1 -type f -mtime +30 -delete

if [ "$?" -ne 0 ]; then
	echo
	echo "WARNING: Find returned an error!"
	echo
	error=1
fi

if [ "$error" -eq 0 ]; then
	echo "Mysql backup script completed successfully!"
fi

[iconnor]

Are there strange characters in your password? Maybe need to put it in quotes Or backslash escape them. Try a simple password first.
Also, as this is ubuntu, if the script is being run as root, you can use --defaults-file=/etc/mysql/debian.cnf instead of a user/pass combo.

[adam.robertson]

1. make sure your root user has global permissions for all databases, and make sure you have a password set for that root user. phpMyadmin simplifies user permissions and such if the command line is a problem.
2. Also try not to use too many special characters in your password like "@" or "#" as that can throw bash scripts off if not formatted properly.
3. try this change in the first part of your script:

Code: Select all

#!/bin/bash   
#SET THE PATH   
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin   
# Enter SQL database login credentials
HOST='localhost'
USER="root"
PASS="mypass"

# Specify which databases to backup
#ALL DATABASES
 DB=( `echo "show databases" |  mysql --user=$USER --password=$PASS --host=$HOST  | tail -n+3 `)
#for a single database use this instead:
#DB=(  'single_database_name' )

#...the rest of your script...