Mysqldump: Error: ‘Access denied

Hello everybody!

I have the following problem with a backup script.

#!/bin/bash

Bash script for creating backups of Nextcloud.

Version 2.0.0

Usage:

- With backup directory specified in the script: ./NextcloudBackup.sh

- With backup directory specified by parameter: ./NextcloudBackup.sh (e.g. ./NextcloudBackup.sh /media/hdd/nextcloud_backup)

The script is based on an installation of Nextcloud using nginx and MariaDB, see Nextcloud auf Ubuntu Server 18.04 LTS mit nginx, MariaDB, PHP, Let's Encrypt, Redis und Fail2ban » DecaTec

IMPORTANT

You have to customize this script (directories, users, etc.) for your actual environment.

All entries which need to be customized are tagged with “TODO”.

Variables

backupMainDir=$1

if [ -z “$backupMainDir” ]; then
# TODO: The directory where you store the Nextcloud backups (when not specified by args)
backupMainDir=’/media/hdd/nextcloud_backup’
fi

currentDate=$(date +"%Y%m%d_%H%M%S")

The actual directory of the current backup - this is a subdirectory of the main directory above with a timestamp

backupdir="${backupMainDir}/${currentDate}/"

TODO: The directory of your Nextcloud installation (this is a directory under your web root)

nextcloudFileDir=’/var/www/nextcloud’

TODO: The directory of your Nextcloud data directory (outside the Nextcloud file directory)

If your data directory is located under Nextcloud’s file directory (somewhere in the web root), the data directory should not be a separate part of the backup

nextcloudDataDir=’/var/nextcloud_data’

TODO: The directory of your Nextcloud’s local external storage.

Uncomment if you use local external storage.

#nextcloudLocalExternalDataDir=’/var/nextcloud_external_data’

TODO: The service name of the web server. Used to start/stop web server (e.g. 'systemctl start ')

webserverServiceName=‘apache2’

TODO: Your web server user

webserverUser=‘www-data’

TODO: The name of the database system (one of: mysql, mariadb, postgresql)

databaseSystem=‘mariadb’

TODO: Your Nextcloud database name

nextcloudDatabase=‘nextcloud_db’

TODO: Your Nextcloud database user

dbUser=‘nextcloud_db_user’

TODO: The password of the Nextcloud database user

dbPassword=‘mYpAsSw0rd’

TODO: The maximum number of backups to keep (when set to 0, all backups are kept)

maxNrOfBackups=0

TODO: Ignore updater’s backup directory in the data directory to save space

Set to true to ignore the backup directory

ignoreUpdaterBackups=false

File names for backup files

If you prefer other file names, you’ll also have to change the NextcloudRestore.sh script.

fileNameBackupFileDir=‘nextcloud-filedir.tar.gz’
fileNameBackupDataDir=‘nextcloud-datadir.tar.gz’

TODO: Uncomment if you use local external storage

#fileNameBackupExternalDataDir=‘nextcloud-external-datadir.tar.gz’

fileNameBackupDb=‘nextcloud-db.sql’

Function for error messages

errorecho() { cat <<< “$@” 1>&2; }

function DisableMaintenanceMode() {
echo “Switching off maintenance mode…”
sudo -u “${webserverUser}” php ${nextcloudFileDir}/occ maintenance:mode --off
echo “Done”
echo
}

Capture CTRL+C

trap CtrlC INT

function CtrlC() {
read -p "Backup cancelled. Keep maintenance mode? [y/n] " -n 1 -r
echo

if ! [[ $REPLY =~ ^[Yy]$ ]]
then
	DisableMaintenanceMode
else
	echo "Maintenance mode still enabled."
fi

echo "Starting web server..."
systemctl start "${webserverServiceName}"
echo "Done"
echo

exit 1

}

Print information

echo “Backup directory: ${backupMainDir}”

Check for root

if [ “$(id -u)” != “0” ]
then
errorecho “ERROR: This script has to be run as root!”
exit 1
fi

Check if backup dir already exists

if [ ! -d “${backupdir}” ]
then
mkdir -p “${backupdir}”
else
errorecho “ERROR: The backup directory ${backupdir} already exists!”
exit 1
fi

Set maintenance mode

echo “Set maintenance mode for Nextcloud…”
sudo -u “${webserverUser}” php ${nextcloudFileDir}/occ maintenance:mode --on
echo “Done”
echo

Stop web server

echo “Stopping web server…”
systemctl stop “${webserverServiceName}”
echo “Done”
echo

Backup file directory

echo “Creating backup of Nextcloud file directory…”
tar -cpzf “${backupdir}/${fileNameBackupFileDir}” -C “${nextcloudFileDir}” .
echo “Done”
echo

Backup data directory

echo “Creating backup of Nextcloud data directory…”

if [ “$ignoreUpdaterBackups” = true ] ; then
echo “Ignoring updater backup directory”
tar -cpzf “${backupdir}/${fileNameBackupDataDir}” --exclude=“updater-/backups/” -C “${nextcloudDataDir}” .
else
tar -cpzf “${backupdir}/${fileNameBackupDataDir}” -C “${nextcloudDataDir}” .
fi

echo “Done”
echo

Backup local external storage.

Uncomment if you use local external storage

#echo “Creating backup of Nextcloud local external storage directory…”
#tar -cpzf “${backupdir}/${fileNameBackupExternalDataDir}” -C “${nextcloudLocalExternalDataDir}” .
#echo “Done”
#echo

Backup DB

if [ “${databaseSystem,}” = “mysql” ] || [ “${databaseSystem,}” = “mariadb” ]; then
echo “Backup Nextcloud database (MySQL/MariaDB)…”

if ! [ -x "$(command -v mysqldump)" ]; then
	errorecho "ERROR: MySQL/MariaDB not installed (command mysqldump not found)."
	errorecho "ERROR: No backup of database possible!"
else
	mysqldump --single-transaction -h localhost -u "${dbUser}" -p"${dbPassword}" "${nextcloudDatabase}" > "${backupdir}/${fileNameBackupDb}"
fi

echo "Done"
echo

elif [ “${databaseSystem,}” = “postgresql” ] || [ “${databaseSystem,}” = “pgsql” ]; then
echo “Backup Nextcloud database (PostgreSQL)…”

if ! [ -x "$(command -v pg_dump)" ]; then
	errorecho "ERROR: PostgreSQL not installed (command pg_dump not found)."
	errorecho "ERROR: No backup of database possible!"
else
	PGPASSWORD="${dbPassword}" pg_dump "${nextcloudDatabase}" -h localhost -U "${dbUser}" -f "${backupdir}/${fileNameBackupDb}"
fi

echo "Done"
echo

fi

Start web server

echo “Starting web server…”
systemctl start “${webserverServiceName}”
echo “Done”
echo

Disable maintenance mode

DisableMaintenanceMode

Delete old backups

if [ ${maxNrOfBackups} != 0 ]
then
nrOfBackups=$(ls -l ${backupMainDir} | grep -c ^d)

if [[ ${nrOfBackups} > ${maxNrOfBackups} ]]
then
	echo "Removing old backups..."
	ls -t ${backupMainDir} | tail -$(( nrOfBackups - maxNrOfBackups )) | while read -r dirToRemove; do
		echo "${dirToRemove}"
		rm -r "${backupMainDir}/${dirToRemove:?}"
		echo "Done"
		echo
	done
fi

fi

echo
echo “DONE!”
echo “Backup created: ${backupdir}”

After executing the script, the following summary appears:
root@nc1clon102:/skripts# ./nc-backup.sh
Backup directory: /mnt/CloudBackup
Set maintenance mode for Nextcloud…
Maintenance mode enabled
Done

Stopping web server…
Done

Creating backup of Nextcloud file directory…
Done

Creating backup of Nextcloud data directory…
Ignoring updater backup directory
Done

Backup Nextcloud database (MySQL/MariaDB)…
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces
Done

Starting web server…
Done

Switching off maintenance mode…
Maintenance mode disabled
Done

Removing old backups…
20210303_192619
Done

DONE!
Backup created: /mnt/CloudBackup/20210307_162045/

The problem, however, is that my data does not seem to be backed up because there is 100GB in the cloud as a test, but the backup only finished after 20 seconds.
If I then delete some PDF documents, Excel spreadsheets and images in the cloud and restore the backup, the PDF documents, Excel spreadsheets and some images can no longer be displayed.
I hope I have expressed myself clearly to some extent.

As can be seen with the backup script, there also seems to be no access to the database.
mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces
However, I don’t know how to restore access.

I hope one of you can help me with this because as a beginner I am unfortunately not so fit and still have to learn a lot.
So please be patient with me :slight_smile:

Best regards
Majestic

There is a crosspost, it’s in the German subforum and got some answers:

closing this here.