Backup failed at DB dump on a Galera Cluster ---> modify db.sh

Community Support Backups
1

I have a running Galera Cluster with three nodes. On the nodes with VestaCP (old but latest), the backup works including databases and a tar ball is created.

I moved on Hestia. I did not import with backup—> restore because of Galera. I created DB connection details in Hestia. And it worked. I could connect to the DBs, change DB pass, etc.

When I make backup with v-backup-user user, it breaks with ERROR 17 = DB. The Error: Error: dump DB_Name failed. This happens with users that has a DB in the account.

All other backups works on Hestia that does not have a DB, i. e. user not having DB, the backup completes and a tar ball is created.

But the same DB could be dumped by old VestaCP, as it is a Galera Cluster.

Conclusion: The DB is is not corrupted. The php VestaCP scripts can make a dump of the same replica and Hestia cannot dump. hence, the problem lies in the php scripts.

Any hints?

2

Troubleshooting further:

I can create new DBs and they are entered in db.conf. I can delete them. So, the connection is fine.

I can and have changed and updated the password of these DBs in question as well. These means that the connection details are current in the db.conf.

I have created a new test DB. The details are in db.conf and it is not excluded from backup. All other DBs are excluded. The backup fails at the point of newly created DB.

Conclusion: The issue has nothing to do with any existing DB (from Galera Cluster) of newly created DB with Hestia (that got replicated in Galera Cluster). The problem is in the Hestia scripts.

After I excluded all the DB in the account of that user, the backup completed and a tarball is created.

My question is why does Hestia does not dump the existing DB, although the connection details exists in the db.conf

3

I don’t know:

Try running first:

mysqldump --defaults-file=$mycnf --single-transaction --routines -r database.sql user_database

4

Thanks eris for the hint. I was there and bumped further, one step as I have this trouble under my nose.

Earlier, the password in .mysql.localhost had no Capital and thus failed the function completely. Now, I changed this and it can go further with get_database_values.

Following is the point where is fails:

+ mycnf=/usr/local/hestia/conf/.mysql.localhost
+ '[' '!' -e /usr/local/hestia/conf/.mysql.localhost ']'
++ grep password /usr/local/hestia/conf/.mysql.localhost
++ cut -f 2 -d \'
+ mypw=MyMariaDBRootPasswd
+ '[' MyMariaDBRootPasswd '!=' MyMariaDBRootPasswd ']'
++ mktemp
+ mysql_out=/tmp/tmp.Qa25VlzGwc
+ mysql --defaults-file=/usr/local/hestia/conf/.mysql.localhost -e 'SELECT VERSION()'
+ '[' 0 -ne 0 ']'
++ cat /tmp/tmp.Qa25VlzGwc
++ tail -n1
++ cut -f 1 -d -
+ mysql_ver=10.6.4
+ mysql_fork=mysql
++ grep MariaDB /tmp/tmp.Qa25VlzGwc
+ check_mysql_fork=10.6.4-MariaDB-1:10.6.4+maria~focal-log
+ '[' '!' -z 10.6.4-MariaDB-1:10.6.4+maria~focal-log ']'
+ mysql_fork=mariadb
+ rm -f /tmp/tmp.Qa25VlzGwc

Notice here: + ‘[’ MyMariaDBRootPasswd ‘!=’ MyMariaDBRootPasswd ']'

Eventually, it does not go further and does not enter in mysqldump --defaults-file=$mycnf because it had failed earlier.

With this hint, I think there is a problem in the password because it says it is not equal.

For the dump, there is a text password in .mysql.localhost. So my question is why can it not compare the cleartext pass? Do I need to ecnrypt it with MD5 and insert it somewhere?

NB: I have changed and entered the root pass from the server —> configure in the panel. so it updated where necessary, like under /root/.my.conf, .mysql.localhost, etc. I made sure that the root pass exist the same (with Capital letters) also in mysql.conf as well.

5

Hello Eris,

With the following command, I can dump the DB without problems:

mysqldump -u root DB_Name > database.sql

NB: Just a thought —> When the old VestaCP can dump the slave1 DB, then Hesta MUST be able to dump the same DB on slave2 because the binary execution in the Cluster from the primary component remains the same.

To me it is clear that it has nothing to do with the MariaDB. It MUST be in the Hestia scripts or the manner in which I try to get it working.

BTW, it has been a fresh install.

6

Hello everyone,

I created a new database under a new account in Hestia. I can modify the password for that database too.

But I cannot make a backup of this database. If I exclude it, the backup is completed and a tarball is made.

This means this problem is Hestia php/bash scripts specific. It could be that some specific binary is missing. As I am new to Ubuntu, I do not know if this assumption is true.

I would be happy if someone could confirm, if the above problem occurs in the latest Hestia scripts under Ubuntu 20.4. If yes, it would be hard for me to believe that there is a bug somewhere.

7

Is nothing special

If you have multiple servers running it makes sure the it uses the correct password and it should skip repopulating with new password…

Check first what the contents is on /tmp/e.mysql

I don’t have a DB cluster running and settings it up for a few tests I am not planning to do so…

8

Hello Eris,

Thanks for your response and for taking time to look into this. About pwd ‘!=’, it is now obvious. I did not check the code. I have multiple servers running. They all have same password. Further, if I can create a new database under that user, then the backup/restore should work. The contents is on /tmp/e.mysql: Oh, yes. That a point to check. I did the following:

created DB DBUser_DBTest

CREATE TABLE nastybug (
troubles varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO nastybug (troubles) VALUES (CURRENT_DATE());

v-backup-user DBUser
—> $ Error: dump DBUser_DBTest failed
—> /usr/local/hestia/log/error.log
—>—> v-backup-user ‘DBUser’ [Error 17]

cat /tmp/e.mysql
—> $ mysqldump: Couldn’t execute ‘SHOW FUNCTION STATUS WHERE Db = ‘DBUser_DBTest’’:
—> $ Cannot load from mysql.proc. The table is probably corrupted (1728)

The message in e.mysql cannot be true as it is a newly created DB, as above.

This situation occurs for all existing DBs as well as newly created DB.

For the purpose of dumping a DB, the MariaDB serves as a client i.e. slave of the Master to function like a Standalone server. If you think that I should make further test, I can disconnect the slave (where Hestia is installed) from the cluster and try to make a backup. However, I do not think that this is an area to trouble shoot further.

I have not yet executed the following:

GRANT SELECT ON mysql.proc to 'DBUser_DBTest;

Do you have further suggestions?

9

I tried on cli to dump DBUser_DBTest. I cannot. Same error mysql.proc is thrown.
Thats where I need to get into the roots.

Legend:
-A For all databases (you can also use --all-databases)
-R For all routines (stored procedures & triggers)
-E For all events
–single-transaction Without locking the tables i.e., without interrupting any connection (R/W).

Following DOES_NOT work:
mysqldump -u root -p DBUser_DBTest -A -R -e --triggers --single-transaction > backup.sql

After removing from above: -A -R -e --triggers --single-transaction, these parameters one by one, I came down to following.

Following DOES work:
mysqldump -u root -p DBUser_DBTest > backup.sql

So it appears that the Hestia’s scripts having --single-transaction shall not work!

10

Hello Eris, you captured much earlier the right point.

Now, I ended up with the following ugly solution:

# mysqldump --defaults-file=$mycnf --single-transaction --routines -r $1 $2 2> $err
mysqldump --defaults-file=$mycnf -r $1 $2 2> $err

Now backup and restore work perfect.

I do not like this ugly solution. Does anyone have better ideas?

11

Excuse my ignorance. Why is it ugly?

12

--single-transaction

Locks temporary the database and probably not possible with a cluster…

–routines

Dump stored routines (procedures and functions) from dumped databases

Should no issue…

13

The solution is ugly because I would have to modify with each update manually. With the auto update, backup will break. Further, it reduces a little bit of feature.

Now I am looking on wsrep area. It is possible that the slave has been crashed, but not fully. It is also possible that the provider node was blasted, causing hindrance in wsrep (replication), when binary data needed to update and could not. Thus, if there is node inconsistency, a crash occurs. In that case - I only assume - that dumping could be troublesome.

I am going to deepen this and make some testing further. If I find something interesting, I shall come back and inform.

14

To prevent the issue with updates you might be able to use this:

And modify v-backup-user when post install runs …

Other idea is disable backing up of database over all and run a new cronjob that backups all backups separate…

15

Oh man, thanks Marcus for your hints and lovely support. After I complete migrating to Ubuntu and Hestia, I will setup these things and incorporate your suggestions above. Seems like a solution that could work even if there is no change in the Hestia code.

I had planned to develop a bash script to upload (ssh) a backup from master and update the remote slave by unpacking the backup and restoring with cron. I never completed it, as vestacp came to a deadend. Now, it is time to do.

BTW, I already have functioning rsync of files to slaves and Galera wsrep with Maxscale works very good. But backups could be helpful. So I will modify with the hooks.

Again Marcus, thank for your hints and help.

1 Like
16

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.