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
|
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 */;" |
|
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;" |
|
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;" |