Dumping OpenEMR database... There was an error on the backup

Support
#1

OpenEMR version 7.0.0 (1) running on Debian 11
apache2 2.4.54-1~deb11u1
php 7.4.30-1+deb11u1
mariadb-server 1:10.5.15-0+deb11u1

Firefox private / Chromium incognito

In Global Settings > Logging

  • User Debugging Options - All
  • System Error Logging Options - Debug

On the webpage Admin > System > Backup

Click Create Backup produces Dumping OpenEMR database… There was an error on the backup

/var/log/apache2/error.log

mysqldump: Got error: 1045: "Access denied for user 'openemr'@'localhost' (using password: YES)" when trying to connect

When I use the details contained in sqlconf.php and do

mysqldump --opt --quote-names -u openemr -p openemr | gzip > openemr.sql.gz

openemr.sql.gz is produced after entering the password

Have been trying to compare with a test system that works as expected with no luck

#2

Hi,
Can you log into MySQL from the command line using the credentials that are in the sqlconf?

#3

Yes

$ mysql -u openemr -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 187
Server version: 10.5.15-MariaDB-0+deb11u1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
#4

This is my sqlconf.php if it helps

<?php
//  OpenEMR
//  MySQL Config

$host   = 'localhost';
$port   = '3306';
$login  = 'openemr';
$pass   = 'XXXXXXX';
$dbase  = 'openemr';


//Added ability to disable
//utf8 encoding - bm 05-2009
global $disable_utf8_flag;
$disable_utf8_flag = false;

$sqlconf = array();
global $sqlconf;
$sqlconf["host"]= $host;
$sqlconf["port"] = $port;
$sqlconf["login"] = $login;
$sqlconf["pass"] = $pass;
$sqlconf["dbase"] = $dbase;
//////////////////////////
//////////////////////////
//////////////////////////
//////DO NOT TOUCH THIS///
$config = 1; /////////////
//////////////////////////
//////////////////////////
//////////////////////////
?>
#5

Doesn’t anyone have any ideas on this?

I’m confused how mysqldump works on cli but not through the web script

#6

I do not know what is the problem. You should see it up close.
I use this script for backup. That also makes backups of the other DBs separately. It runs via cron every hour. You must have gzip installed.
You can use it by modifying your preferences.

#!/usr/bin/bash
#
####################################################################
##       Shell script to backup all MySql database with single User
##
##       MySQL Database Backup Script
##   Written By: Amol Jhod
##   URL: https://www.itsupportwale.com/blog/learn-how-to-backup-up-all-mysql-databases-using-a-single-user-with-a-simple-bash-script
##   Last Update: Apr 25, 2019
##
##   For more scripts please visit : www.itsupportwale.com
##
#####################################################################
#####################################################################
#### Caution : This script is takes backup of all databases #########
#############   on which the given user is having access. ###########
############## And Delete the backups older then BKP_DAYS ##########
#####################################################################
#####################################################################
########### You Have to Update the Below Values #####################
#####################################################################
#
#
BKP_USER="root"     # Enter the username for backup
BKP_PASS='YourRootMysqlPassword' # Enter the root password of the backup.
#
BKP_DEST="/home/backups/databases/mysql" # Enter the Backup directory,change this if you have someother location
#
## Note: Scripts will delete all backup which are older then BKP_DAYS##
#
BKP_DAYS="30"  # Enter how many days backup you want,
#
########### Use This for only local server #############################
MYSQL_HOST="localhost"
#
#
########################################################################
########### Thats Enough!! NO NEED TO CHANGE THE BELOW VALUES ##########
########################################################################
#
##################### Get Backup DATE ##################################
#
BKP_DATE=`date +"%Y-%m-%d_%H-%M-%S%p"`
#
########## Ignore these default databases shen taking backup ############
#
IGNORE_DB="information_schema mysql performance_schema"
#
########## Creating backup dir if not exist #############################
#
# [ ! -d $BKP_DEST ] && mkdir -p $BKP_DEST || :
#
################# Autodetect the linux bin path #########################
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
TAR="$(which tar)"

#
###################### Get database list ################################
#
DB_LIST="$($MYSQL -u $BKP_USER -h $MYSQL_HOST -p$BKP_PASS -Bse 'show databases')"
#
mkdir -p ${BKP_DEST}/${BKP_DATE}
        echo " =========== Fecha: ${BKP_DATE} ============" >> ${BKP_DEST}/Backup-Report.txt

    if ! mysql -u ${BKP_USER} -p${BKP_PASS} -e 'exit'; then
        echo 'Fallido! Contraseña o usuario incorrecto ' >> ${BKP_DEST}/Backup-Report.txt
        exit 1
    fi
for db in $DB_LIST
do
    skipdb=-1
    if [ "$IGNORE_DB" != "" ];
    then
        for i in $IGNORE_DB
        do
            [ "$db" == "$i" ] && skipdb=1 || :
        done
    fi

    if [ "$skipdb" == "-1" ] ; then
        NOW=`date +"%d%b%Y_%H:%M:%S%p"`
        BKP_FILENAME="$BKP_DEST/$BKP_DATE/$db.$NOW.gz"
#
################ Using MYSQLDUMP for bakup and Gzip for compression ###################
#
        $MYSQLDUMP --skip-lock-tables -u $BKP_USER -h $MYSQL_HOST -p$BKP_PASS $db | $GZIP -9 > $BKP_FILENAME

                if [ $? -eq 0 ]; then
                    touch ${BKP_DEST}/Backup-Report.txt
                    echo "Copia exitosa de ${db} on ${BKP_DATE}" >> ${BKP_DEST}/Backup-Report.txt

                else
                    touch ${BKP_DEST}/Backup-Report.txt
                    echo "Fallo la copia de ${db} on ${BKP_DATE}" >> ${BKP_DEST}/Backup-Report.txt
                    exit 1
                fi
    fi
done
#########To delete all backup files older then BKP_DAYS #################
#
#find $BKP_DEST -type d -mtime +$BKP_DAYS -delete
find $BKP_DEST -type d -mtime +$BKP_DAYS -exec rm {} \;
#
#
#### End of script ####

Regards.
Luis

1 Like
#7

That’s great, thanks!

However I’m still concerned about why this doesn’t work. I have another test server that works fine and the web script is identical

#8

Try putting the host and specifying db:

mysqldump --opt --quote-names -h "localhost" -u "openemr" -p"XXXXXXX" "openemr" | gzip > openemr.sql.gz
#9

That gives :

mysqldump: Got error: 1045: "Access denied for user 'openemr'@'localhost' (using password: YES)" when trying to connect

Edit : same after logging into root with sudo -i

#10

You are leaving spaces after -p. It should be -p’PassWord’ not -p Password . Or --password=‘PassWord’.

but try:

GRANT ALL PRIVILEGES ON openemr.* TO openemr@'localhost' IDENTIFIED BY 'PassWord';
flush privileges;
#11

Tried again and this works

mysqldump --opt --quote-names -h "localhost" -u "openemr" -p'XXXXXXX' "openemr" | gzip > openemr.sql.gz

The web script still doesn’t

#12

I’ve got the same problem with a new installation. I am seeing this message in the mysql log: [Warning] Access denied for user ‘openemr’@‘localhost’ (using password: YES)

I have checked all of the permissions and they look good.

-Phil