Note : This is only testing purpose simulation for restoring mysql database from backup
Getting below error while restoring mysql database from backup file
ERROR 3185 (HY000) at line 57: Can't find master key from keyring, please check in the
server log if a keyring is loaded and initialized successfully.
Root cause of this error is Encryption is enabled when we were taking backup from source database and and the target there was no encryption feature enabled
There will be two choice to restore the database successfully
Option 1. Configure and enable Keyring feature and restore the database from backup.
Option 2. Replace Encryption='Y' to Encryption='N' in backup dump file but this can not be simulate exactly as in source. This is not feasible solution
C:\Users>mysql -h localhost -u root -pMysql_4U
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> create database TESTDB;
Query OK, 1 row affected (0.02 sec)
mysql> exit
Bye
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3185 (HY000) at line 57: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
C:\Users>
C:\Users>mysql -h localhost -u root -pMysql_4U
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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 PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';
Empty set (0.01 sec)
******** Here there is no Keyring plugin configured ***********
mysql>
C:\ProgramData\MySQL\MySQL Server 8.0>mkdir Plugin
C:\ProgramData\MySQL\MySQL Server 8.0>cd Plugin
C:\ProgramData\MySQL\MySQL Server 8.0\Plugin>copy "C:\Program Files\MySQL\MySQL Server 8.0\lib\plugin\keyring_file.dll" "C:\ProgramData\MySQL\MySQL Server 8.0\Plugin"
1 file(s) copied.
C:\ProgramData\MySQL\MySQL Server 8.0\Plugin>
C:\ProgramData\MySQL\MySQL Server 8.0\Plugin>
C:\ProgramData\MySQL\MySQL Server 8.0\Plugin>dir
Volume in drive C is OS
Volume Serial Number is 78C0-1043
Directory of C:\ProgramData\MySQL\MySQL Server 8.0\Plugin
05/28/2023 12:41 AM <DIR> .
05/28/2023 12:39 AM <DIR> ..
12/17/2021 05:21 PM 91,648 keyring_file.dll ==> Keyring_file copied
1 File(s) 91,648 bytes
2 Dir(s) 6,630,662,144 bytes free
C:\ProgramData\MySQL\MySQL Server 8.0\Plugin>
C:\ProgramData\MySQL\MySQL Server 8.0>start notepad my.ini
****** Open my.ini file add pink color line in [mysqld] block ****
early-plugin-load=keyring_file.dll
keyring_file_data=C:\ProgramData\MySQL\MySQL Server 8.0\Plugin\keyring_file
****** Install Keyring Plugin ****
