Problems restoring a database?

No worries:

https://forums.mysql.com/read.php?20,675392,675404#msg-675404

Here’s the session:

[root@mysql01 tmp]# ls -altri
total 592120
134766607 -rw-r–r–.  1 root root 606322782 Jun  5 07:53 mysql-c01.sql.1556440201
134604506 drwxr-xr-x.  2 root root        37 Jun  5 07:54 .
201326721 dr-xr-x—. 14 root root      4096 Jun  8 13:03 ..
[root@mysql01 tmp]# mysql -u root -h mysql01.nix.mds.xyz -p zabbix < mysql-c01.sql.1556440201
Enter password:
ERROR 1130 (HY000): Host ‘mysql01.nix.mds.xyz’ is not allowed to connect to this MySQL server
[root@mysql01 tmp]#
[root@mysql01 tmp]#
[root@mysql01 tmp]# mysql -u root -h mysql02.nix.mds.xyz -p zabbix < mysql-c01.sql.1556440201
Enter password:
ERROR 1044 (42000) at line 951: Access denied for user ‘root’@’%.nix.mds.xyz’ to database ‘performance_schema’
[root@mysql01 tmp]# select user,host,authentication_string from mysql.user;^C
[root@mysql01 tmp]# mysql -u root -h mysql02 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1657
Server version: 5.7.25-log MySQL Community Server – (GPL), wsrep_25.17

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> select user,host,authentication_string from mysql.user;
+—————+—————+——————————————-+
| user          | host          | authentication_string                     |
+—————+—————+——————————————-+
| root          | localhost     | *725EA9257B824D03786B3160A70CE38949079EAD |
| mysql.session | localhost     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| haproxy       | %             |                                           |
| haproxy_root  | %             | *74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3 |
| root          | %.mws.mds.xyz | *725EA9257B824D03786B3160A70CE38949079EAD |
| root          | %.nix.mds.xyz | *725EA9257B824D03786B3160A70CE38949079EAD |
| zabbix        | %.mws.mds.xyz | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |
| zabbix        | %.nix.mds.xyz | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |
| zabbix        | localhost     | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |
| scm           | %             | *45E6E3C68BDF1AC7EBB5C5A3BCBD5E9437B293BE |
| rman          | %             | *819397F8B454D58DA4E9F42A88A4873756B8C96D |
| hive          | %             | *4DF1D66463C18D44E3B001A8FB1BBFBEA13E27FC |
| oozie         | %             | *2B03FE0359FAD3B80620490CE614F8622E0828CD |
| hue           | %             | *15221DE9A04689C4D312DEAC3B87DDF542AF439E |
| sentry        | %             | *78CD2A8CB7403E99F97588EDF05B3EEAC6494302 |
| amon          | %             | *DD0008B5FF196DE1F1FF14303BBA1B463CF83411 |
| nav           | %             | *1FE59D14EF0D6A47A590517437D8518EA3BFF064 |
| navms         | %             | *6F20A779B8CA430934928BE1BE2FB425DA47AC00 |
+—————+—————+——————————————-+
19 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> use zabbix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql>
mysql>
mysql> source /root/tmp/mysql-c01.sql.1556440201
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

In certain cases, you may be forced to reinitialize a database.  THIS ASSUMES YOU HAVE A WORKING BACKUP.  Do the following:

• Take a gzip backup of all the database files under /var/lib/mysqld.  (Ex )
• Remove all the database files from /var/lib/mysqld for all three nodes.
• Look for the node that has safe_to_boostrap: 1 :

  [root@mysql03 mysql]# cat grastate.dat
  # GALERA saved state
  version: 2.1
  uuid:    00000000-0000-0000-0000-000000000000
  seqno:   -1
  safe_to_bootstrap: 1
  [root@mysql03 mysql]#

• Start mysqld in safe mode to reset the pass, if need be:

  [root@mysql03 mysql]# systemctl set-environment MYSQLD_OPTS=”–wsrep-new-cluster –skip-grant-tables”

• Start mysql on the node with safe_to_bootstrap using:

  /usr/bin/mysqld_bootstrap

• Login to mysqld:

  [root@mysql03 mysql]# mysql -u root -p

  or

  [root@mysql02 mysql]# mysql -u root -h mysql-c01.nix.mds.xyz -p

• Restore the database using the steps in the first part of this document.

Regards,
BK