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

details_hoverNOTE

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

SRCERADBNAME with the name of the source ESMC 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

TARGETERADBNAME with the name of the target ESMC database (after migration)

TARGETERALOGIN with the login name for the new ESMC database user on the target database server

TARGETERAPASSWD with the password of the new ESMC 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 ESMC Server/MDM services.

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

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

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

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

details_hoverNOTE

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 TARGETERADBNAME < BACKUPFILE

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

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

Recommended characters for TARGETERALOGIN:

Lower-case ASCII letters, numbers and underscore "_"

Recommended characters for TARGETERAPASSWD:

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 ESMC database user on the target MySQL server:

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

details_hoverNOTE

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

7.Delete the contents of the 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. If you have the new ESMC Server or MDM already installed, connect it to the database.