Backup process does not backup databases - Leaves sql empty

[llamprec]

I am trying to run a full backup of users to move them to a new DirectAdmin installation on a new server.

The backup runs and completes but then I receive an email saying there there was an issue. The subject of the mail is.

New Message: An error occurred during the backup​

there is a link in the email that opens a message within DA showing me the following.:

Error while backing up database send_mail
Error while backing up database send_mail: The sql file is 0 bytes in size: /home/admin/admin_backups/send/backup/send_mail.sql
mysqldump error output: mysqldump: Got error: 1045: Access denied for user 'da_admin'@'localhost' (using password: YES) when trying to connect

If I connect to the server in a terminal, and run the mysql -u da_admin -p, I can connect to the database without any hassles. So I am confused as to why I am seeing an error saying that Access was denied for the user da_admin.

As a result of this error, looking at the backed up files, all the .sql files are showing as size zero, the databases have not been backed up. This is the same for multiple users.

Is there anyone that has any ideas why I can connect on the commandline but get an error when run via DirectAdmin backup process.

Thanks
Lawrence

 

[Richard G]

Error while backing up database send_mail

Do you have a user called "send"? Interesting.

If I connect to the server in a terminal, and run the mysql -u da_admin -p, I can connect to the database without any hassles. So I am confused as to why I am seeing an error saying that Access was denied for the user da_admin.

That is a very important thing to know, because this shows that da_admin login is working.
Since I had little time yesterday, it didn't come to my mind, but I'm just thinking, if I'm correct, then DA itself is making use of a certain file in the configuration directory.

So if you use the terminal, which password do you use? The one from the setup.txt?
Can you verify that this password is exactly the same as in:
/usr/local/directadmin/conf/my.cnf
that file should look like this:

[client]
user=da_admin
password=thatmysqlpassword

Normally there is a second file there called mysql.conf which this content:

user=da_admin
passwd=thatmysqlpassword

so basically the same file, but without the "client" tag.

Can you check and verify both file extists and contain the password you are using when you are able to login via terminal.

If it's not the same, change the passwords to the pass you use, restart directadmin and try to create the backup again.

 

[warg]

To be honest it's a bit worrying how many backup process issues we faced in this forum since less than a year :/. I would expect this to be better monitored by Directadmin team like security issues e. g. if bruteforce protection does work.

 

[llamprec]

Richard,

Thanks for your response. I have checked and the follwoing files exist and all the passwords are the same.

[root@ns3 ~]# cat /usr/local/directadmin/conf/my.cnf
[client]
user=da_admin
password=XXXXXXXXX

[root@ns3 ~]# cat .my.cnf
[client]
user=da_admin
password=XXXXXXXXX\socket=/var/lib/mysql/mysql.sock

[root@ns3 ~]# cat /usr/local/directadmin/conf/mysql.conf
user=da_admin
passwd=XXXXXXXXX

Although, there is one more file that I see than what you suggested. The file .my.cnf in the root home dir.

Lawrence

 

[Richard G]

The file .my.cnf in the root home dir.

That's used for automatic login via termain. So you only have to do "mysql" without the -u and -p to be able to login.
Normally you don't need this one.

But since you said that via terminal it wouldn't give any issues, the password here should be the same as in the other 2 files, then we're sure. Because the content of that file will overrule the commandline.

Maybe best is to remove or rename that file (maybe to mysql.bak or something) so you can again try via commandline:
mysql -uda_admin -p
with the correct password.

However, if all passwords are the same and this test also does not give issues, then I'm out of ideas as to why the login works via terminal, but not via backup, as both normally work via localhost.

Maybe my friends @bdacus01 or @Active8 have some more insight in this. Maybe there is no @localhost permission for da_admin but I'm not sure how to check.

 

[Richard G]

I found this command, which you can use once logged in to mysql.

SHOW GRANTS FOR 'da_admin'@'localhost';

but since you can login that will probably show you that all rights *.* are granted.

 

[Active8]

I am trying to run a full backup of users

Running backup as Administrator or as "Reseller" ? , I prefer personal the "Reseller" approach .
Can you confirm that the backups as Reseller running fine ?

 

[llamprec]

Guys,
I have checked the grants for the user ad_admin and they are set correctly.

mysql> SHOW GRANTS FOR 'da_admin'@'localhost';
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for da_admin@localhost |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'da_admin'@'localhost' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

I ama little confused with teh comments about logging in without a password. I do not think that this is relevant with regards this issue.
I thought that I was running the backup as the admin with DirectAdmin.

Lawrence

 

[Richard G]

I thought that I was running the backup as the admin with DirectAdmin.

Yes and no. You might be logged in as admin, but the automated backup process uses the da_admin user to create and restore backups. Not the account user.

I ama little confused with teh comments about logging in without a password.

Who said anything about logging in without a password? I only said the .my.cnf in your /root folder would/could overrule commandline given options, and with this file you don't need a password when using terminal either. So that was just an explanation.

It might be that changing da_admin credentials in the database migh fix things.

Just to be sure.
1.) What version of mariaDB/Mysql was running on the old and now on the new server
2.) What's the content of /etc/my.cnf on current server
3.) What's the content of /etc/my.cnf.d/server.cnf on current server

 

[llamprec]

Richard

Thanks for the clarity on the password question.

As for your questions, here are the answers.
On the old server

On the new server

Old server

new server

Lastly, this is teh same for the old and new server.

Thanks again for the great support.
Lawrence

 

[Richard G]

Thanks again for the great support.

Thank you for the compliment. However I havent given great support yet as I wasn't able to provide any solution to the problem.

Mysql 5.7 hmmz.... the setting local-infile = 1 wonders me because for security reasons this is always set to 0. However, if I'm correct, that should not make any difference for the backups. So as far as I know could not cause this issue.

However, we might have find the cause of the issue:
On the new server, if you look into the my.cnf you will see this line:
socket=/usr/local/mysql/data/mysql.sock
and in the .my.cnf which you had in root, is pointing to /var/lib/myql.sock

Such difference could well be the cause of the problem. If DA is instructed by mysql to use a non existing mysql socket (because wrong place) then da_admin wouldn't be allowed to login and so not be able to create anything bigger than 0 bytes.

Just to be sure, could you change this setting in your /etc/my.cnf file to:
socket=/var/lib/mysql/mysql.sock
then restart mysql and try again.

If I'm not mistaken, that might well be the solution to the issue.

I just see the login from caiway, are you also Dutch? Because Active8 and I are too.
However, your name souns English so could also be you're just living in the Netherlands. I'm always a bit inquisitive/nosy.

 

[factor]

database send_mail

Was this DB created in the DA gui? Or did you create it with PMA or some manual way ?

in options.conf
what is

mysql_backup=
mysql_backup_dir=

first should be yes
second is the location of where to store backups.

What happens if you just run? does it backup all the dbs or fail?

cd /usr/local/directadmin/custombuild
./build set mysql_backup yes
./build mysql_backup

 

[factor]

socket=/usr/local/mysql/data/mysql.sock
and in the .my.cnf which you had in root, is pointing to /var/lib/myql.sock

Such difference could well be the cause of the problem.

yes for sure
run

find / -name mysql.sock

to find it on the system.

 

[Richard G]

to find it on the system.

Probably not needed as he can connect via terminal, which made use of the .my.cnf in /root which points to the correct socket otherwise login that way wouldn't be possible either.

So I think there is no need for searching. Just change and restart mysql.

 

[llamprec]

Thank you for the compliment. However I havent given great support yet as I wasn't able to provide any solution to the problem.

Richard, I am very happy that you are trying, that is half the battle, the fixing is the cream on the top. So much appreciated.

However, we might have find the cause of the issue:
On the new server, if you look into the my.cnf you will see this line:
socket=/usr/local/mysql/data/mysql.sock
and in the .my.cnf which you had in root, is pointing to /var/lib/myql.sock

Looking at this comment, I am not sure this is the issue because this setting is only on the new server and not on the old server. It is on the old server that the .sql files are empty.

I just see the login from caiway, are you also Dutch? Because Active8 and I are too.
However, your name souns English so could also be you're just living in the Netherlands. I'm always a bit inquisitive/nosy.

Haha haha that is permitted mate, no, I am a South African with a Swedish passport living in Aalsmeer in the Netherlands. Long story but interesting non the less.

Was this DB created in the DA gui? Or did you create it with PMA or some manual way ?

Yes this database was created within DA.

first should be yes
second is the location of where to store backups.

@bdacus01

Following your instruction, I get the following. Using the da_admin password located in the .my.cnf file I can connect to mysql without hassles, but there is an issue connecting using the DA process.

As a test I ran the find command and below is what I see on the old server.

So I do not think that any change is needed.
Hope this helps you guys a little.
Lawrence

 

[llamprec]

As an exercise, on the new server I ran the following commands and all ran successfully.

Lawrence

 

[Richard G]

I am not sure this is the issue because this setting is only on the new server and not on the old server.

Ooooh... I thought you were having issues on the new server, my mistake.
However, I'm still very confused now, because I found the difference on the new server.
So if the mysql.sock on the new server is also on /var/lib/mysql and not in /usr/local/mysql/data I would still advise to change that to prevent future issues. Still wondering how that got in there though.

Still wondering. Just to be sure DA does not look at another place for some odd reason, could you add the line on your old server in /etc/my.cnf and try again with the DA command?
So add this:

[client]
socket=/var/lib/mysql/mysql.sock

then restart mysql and see if that changes anything.

 

[Richard G]

I am a South African with a Swedish passport living in Aalsmeer in the Netherlands. Long story but interesting non the less.

Oh that seems nice, 3 different country's. LoL. But that's nice. Now you say it, I think a recovered a memory leak on my side. I have that some times at my age.
You might have mentioned to @bdacus01 and me in another thread that you were South African, but I forgot all about it. Unless it was somebody else.

 

[factor]

Lets make sure you dont have any bad my.cnf

General MariaDB and MySQL | Directadmin Docs

DirectAdmin Knowledge Base

docs.directadmin.com

find / -name my.cnf

also maybe this

Backup restore recovery | Directadmin Docs

DirectAdmin Knowledge Base

docs.directadmin.com

Version 1.50.0 | Directadmin Docs

DirectAdmin Knowledge Base

www.directadmin.com

 

[llamprec]

Ooooh... I thought you were having issues on the new server, my mistake.
However, I'm still very confused now, because I found the difference on the new server.
So if the mysql.sock on the new server is also on /var/lib/mysql and not in /usr/local/mysql/data I would still advise to change that to prevent future issues. Still wondering how that got in there though.

I will do this for sure.

Still wondering. Just to be sure DA does not look at another place for some odd reason, could you add the line on your old server in /etc/my.cnf and try again with the DA command?
So add this:

[client]
socket=/var/lib/mysql/mysql.sock

then restart mysql and see if that changes anything.

Tried this and same thing.

@bdacus01
I will carry out what you suggest and provide feedback.

also maybe this
Backup restore recovery | Directadmin Docs

I have not done the same thing but something similar. I used musqldump to export all the databases into a .sql file. gzipped the file and sftp the file to my new box. I first created all the users and the empty databases, I then used mysql -u da_admin -p <database_name> < <database_name>.sql

Di this for each and every database I have, I then went into the new DA and checked and it seemed that all the databases had been inserted as expected. On Tuesday when I get into the office, I will go about and test the dirreent sites to see if the upload worked or not. Will keep you posted.
Lawrence