mysql import from backup not work

Hi,
last week i have to reinstall my server.
the iscponfig is install like this.
https://www.howtoforge.com/ispconfig-autoinstall-debian-ubuntu/
htf_report_2022-08-29.txt ist attached.

i backup everything and most things (Mail and website) work again.
in the ispconfig i used the Resync Tool.

i createt backups from the mysql like this:

Code:

mysqldump -u root -p --events --all-databases --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats > ~/db_backup/mysql-dump_$(date +"%Y-%m-%d_%s").sql
for i in $(cat ~/db_backup/databases-list); do mysqldump -u root -p --databases $i --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats > ~/db_backup/mysql-dump-$i-$(date +"%Y-%m-%d_%s").sql; done
mysql mysql -u root -p -e 'show tables like "db%"' | grep -v Tables_in | xargs mysqldump mysql -u root -p > ~/db_backup/mysql-dump-mysql-db-$(date +"%Y-%m-%d_%s").sql
mysql mysql -u root -p -e 'show tables like "user%"' | grep -v Tables_in | xargs mysqldump mysql -u root -p > ~/db_backup/mysql-dump-mysql-user-$(date +"%Y-%m-%d_%s").sql

today is the mysql my problem.

first i go to phpmyadmin an logged in as root and try to restore the mysql-user.
changed to mysql > users > sql and pasted my users.

Code:

LOCK TABLES `user` WRITE;
INSERT INTO `user` VALUES ('localhost','c1web1sql2','*secret','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'','','N','N','',0.000000),('localhost','c1web1sql1','*secret','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'','','N','N','',0.000000),
[...]
UNLOCK TABLES;

hit ok and get

Code:

#1356 - View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

ok i see users is only a view and not a table !? where can i import my users?

i go to ispc and changed the password of the db user so i think isp will recreate him.
i create a new db but nothing happend.
tried to login as db user. not work.

Code:

mysqli::real_connect(): (HY000/1045): Access denied for user 'dbuc17_nextcloud'@'localhost' (using password: YES)

sounds right i dont see him in mysql.users "view".
i create a new user and new db and this works.

so how can i restore my old db users and dbs ?
was my backup wrong ?

 

thanks for your reply.
and sorry for my late answer.

i looks like this.

i think its right. is it ?

and the sync sync the database.


if i try to login as user dbuc17_nextcloud i get an error.


an if i look as root i can see that the user not exist.


if i create a new user with a new db it work.
user dbuc17_test with db dbc17_test3 for example.
if i change the user for the db (dbc17_nc) to the new user i can login as new user and can see the db.
if i change back i cant login with the original user.

greetings from germany.

 

i delete him and create new.
i also create with another password.
i resync the db

but the error is the same.

 

after deleting the user in ISPConfig i don't found him in mysql > view > user.
where should i look also?

 

yes the db user dbuc17_nextcloud is gone in MySQL and ISPConfig.

 

okay. create him new.
same name but new pw. generatet vom ispconfig.
edit datebase and use the new user.

try to login into mysql
error.

 

you are right he is not created.
password is right in this file.
if i create another new user he will be created.

/var/log/ispconfig/cron.log

Code:

Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [plugins.inc:155] - Calling function 'check_phpini_changes' from plugin 'webserver_plugin' raised by action 'server_plugins_loaded'.
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [server:177] - Found 3 changes, starting update process.
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [plugins.inc:118] - Calling function 'db_user_insert' from plugin 'mysql_clientdb_plugin' raised by event 'database_user_insert'.
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [modules.inc:240] - Processed datalog_id 6941
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [plugins.inc:118] - Calling function 'db_user_update' from plugin 'mysql_clientdb_plugin' raised by event 'database_user_update'.
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [modules.inc:240] - Processed datalog_id 6942
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [plugins.inc:118] - Calling function 'db_update' from plugin 'mysql_clientdb_plugin' raised by event 'database_update'.
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [mysql clientdb plugin.inc:107] - Calling GRANT for dbc17_nc with access rw and hosts localhost
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [mysql clientdb plugin.inc:113] - GRANT for user dbuc17_nextcloud at host localhost
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [mysql clientdb plugin.inc:151] - CREATE USER 'dbuc17_nextcloud'@'localhost'
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [mysql clientdb plugin.inc:170] - PASSWORD SET FOR 'dbuc17_nextcloud'@'localhost' success? no
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [mysql clientdb plugin.inc:178] - GRANT ALL PRIVILEGES ON `dbc17_nc`.* TO 'dbuc17_nextcloud'@'localhost' success? no
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [mysql clientdb plugin.inc:107] - Calling GRANT for dbc17_nc with access rw and hosts localhost
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [mysql clientdb plugin.inc:113] - GRANT for user dbuc17_nextcloud at host localhost
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [mysql clientdb plugin.inc:151] - CREATE USER 'dbuc17_nextcloud'@'localhost'
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [mysql clientdb plugin.inc:170] - PASSWORD SET FOR 'dbuc17_nextcloud'@'localhost' success? no
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [mysql clientdb plugin.inc:178] - GRANT ALL PRIVILEGES ON `dbc17_nc`.* TO 'dbuc17_nextcloud'@'localhost' success? no
Thu 6 Oct 11:01:01 CEST 2022 06.10.2022-11:01 - DEBUG [modules.inc:240] - Processed datalog_id 6943

is it possible that the username is "burned" ?!

 

I don't know if it's a different version.
both installations were installed with the "perfect server" howto.

Code:

Fehler
SQL-Befehl: Kopieren

CREATE USER 'dbuc17_nextcloud'@'localhost';
MySQL meldet: Dokumentation

#1396 - Operation CREATE USER schlug fehl für 'dbuc17_nextcloud'@'localhost'