[ Error Solved ] while Restoring MySQL Database from backup file : ERROR 3185 (HY000) at line 25: Can't find master key from keyring

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

C:\Users>mysql -h localhost -u root -pMysql_4U -D TESTDB < D:\Software\MySQL\Backup\TESTDB_BACKUP_28MAY2023.sql

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  ****

mysql> install plugin keyring_file soname 'keyring_file.dll';

Query OK, 0 rows affected (0.03 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';

+--------------+---------------+

| PLUGIN_NAME  | PLUGIN_STATUS |

+--------------+---------------+

| keyring_file | ACTIVE        |

+--------------+---------------+

1 row in set (0.01 sec)

mysql>

********  Restart the MySQL services  *********************

In my case MySQL instance name is MySQLTEST

C:\Users>sc queryex MySQLTEST

SERVICE_NAME: MySQLTEST

        TYPE               : 10  WIN32_OWN_PROCESS

        STATE              : 4  RUNNING

                                (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)

        WIN32_EXIT_CODE    : 0  (0x0)

        SERVICE_EXIT_CODE  : 0  (0x0)

        CHECKPOINT         : 0x0

        WAIT_HINT          : 0x0

        PID                : 4852

        FLAGS              :

C:\Users>sc stop MySQLTEST

SERVICE_NAME: MySQLTEST

        TYPE               : 10  WIN32_OWN_PROCESS

        STATE              : 3  STOP_PENDING

                                (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)

        WIN32_EXIT_CODE    : 0  (0x0)

        SERVICE_EXIT_CODE  : 0  (0x0)

        CHECKPOINT         : 0x1

        WAIT_HINT          : 0x5265c00

C:\Users>sc start MySQLTEST

SERVICE_NAME: MySQLTEST

        TYPE               : 10  WIN32_OWN_PROCESS

        STATE              : 2  START_PENDING

                                (NOT_STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)

        WIN32_EXIT_CODE    : 0  (0x0)

        SERVICE_EXIT_CODE  : 0  (0x0)

        CHECKPOINT         : 0x3

        WAIT_HINT          : 0x3a98

        PID                : 19096

        FLAGS              :

********  Restore the dump file  *********************

C:\Users>mysql -h localhost -u root -pMysql_4U -D TESTDB < D:\Software\MySQL\Backup\TESTDB_BACKUP_28MAY2023.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

C:\Users>