Migration process for MySQL Server

Prerequisites

Source and target SQL Server instances must be installed. They may be hosted on different machines.

MySQL tools must be available on at least one of the computers (mysqldump and mysql client).

Useful links

https://dev.mysql.com/doc/refman/5.6/en/copying-databases.html

https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

https://dev.mysql.com/doc/refman/5.6/en/mysql.html

Migration process


note

In the commands, configuration files or SQL statements below, please always replace:

SRCHOST with the address of the source database server

SRCROOTLOGIN with the source MySQL server root user login

SRCDBNAME with the name of the source ESET PROTECT database to back up

BACKUPFILE with the path to the file where the backup will be stored

TARGETROOTLOGIN with the target MySQL server root user login

TARGETHOST with the address of the target database server

TARGETDBNAME with the name of the target ESET PROTECT database (after migration)

TARGETLOGIN with the login name for the new ESET PROTECT database user on the target database server

TARGETPASSWD with the password of the new ESET PROTECT database user on the target database server

It is not necessary to execute the SQL statements below via the command line. If there is GUI tool available, you can use an application you already know.

1.Stop the ESET PROTECT Server/MDM services.

2.Create a full database backup of the source ESET PROTECT database (the database you plan to migrate):

mysqldump --host SRCHOST --disable-keys --extended-insert --routines -u SRCROOTLOGIN -p SRCDBNAME > BACKUPFILE

3.Prepare an empty database on the target MySQL server:

mysql --host TARGETHOST -u TARGETROOTLOGIN -p "--execute=CREATE DATABASE TARGETDBNAME /*!40100 DEFAULT CHARACTER SET utf8 */;"


note

Use the apostrophe character ' instead of " quotation marks on Linux systems.

4.Restore the database on the target MySQL server to the previously prepared empty database:

mysql --host TARGETHOST -u TARGETROOTLOGIN -p TARGETDBNAME < BACKUPFILE

5.Create an ESET PROTECT database user on the target MySQL server:

mysql --host TARGETHOST -u TARGETROOTLOGIN -p "--execute=CREATE USER TARGETLOGIN@'%' IDENTIFIED BY 'TARGETPASSWD';"

Recommended characters for TARGETLOGIN:

Lower case ASCII letters, numbers and underscore "_"

Recommended characters for TARGETPASSWD:

ASCII characters only, including upper case and lower case ASCII letters, numbers, spaces and special characters

Do not use non-ASCII characters, curly braces {} or @

Please note that if you do not follow the character recommendations above, you may have database connectivity problems or you will need to escape the special characters in the later steps during database connection string modification. Character escaping rules are not included in this document.

6.Grant proper access rights for the ESET PROTECT database user on the target MySQL server:

mysql --host TARGETHOST -u TARGETROOTLOGIN -p "--execute=GRANT ALL ON TARGETDBNAME.* TO TARGETLOGIN;"


note

Use the apostrophe character ' instead of " quotation marks on Linux systems.

7.Delete the contents of tbl_authentication_certificate table (otherwise, Agents may fail to connect to the new Server):

 mysql --host TARGETHOST -u root -p "--execute=DELETE FROM era_db.tbl_authentication_certificate where certificate_id = 1;"

8.Connect the ESET PROTECT Server or MDM to the database.