Environment requirements for replication with bin log as an ingestion mechanism

Given below are the pre-requisites for MySQL virtualization when using Replication with binlog mode.

Source environment requirements

Source environment is where the source MySQL databases are running.

Connectivity

Delphix staging user must be able to connect to source environment from staging and take a backup of the source database(s) using the mysqldump utility.

Source DB user

A Source DB user with the following permissions.
- Can connect to the source database from staging host as well as locally. This user is required to take backup of the source database.

CODE


mysql>CREATE USER 'delphix_os'@'<staging_host>' IDENTIFIED BY 'delphix_user_passwd';

CODE


mysql>CREATE USER 'delphix_os'@'localhost' IDENTIFIED BY 'delphix_user_passwd';

Has at the minimum, the following permissions on the source database(s).

SELECT, SHUTDOWN, SUPER, RELOAD ,SHOW VIEW, EVENT, TRIGGER, REPLICATION CLIENT,REPLICATION SLAVE

CODE

mysql>GRANT SELECT, SHUTDOWN, SUPER, RELOAD ,SHOW VIEW, EVENT, TRIGGER, REPLICATION CLIENT,REPLICATION SLAVE on *.* to 'delphix_os'@'staging-host';

CODE

mysql>GRANT SELECT, SHUTDOWN, SUPER, RELOAD ,SHOW VIEW, EVENT, TRIGGER on *.* to 'delphix_os'@'localhost';

You can also grant more permissive privileges

CODE

mysql>GRANT ALL PRIVILEGES ON *.* TO 'user'@'%';

Note

Remember, this is the user that Delphix uses to manage the Staging database. So, it is recommended that you create a dedicated source db user for Delphix with the privileges mentioned above.

Binary logging

In order to set up replication, binary logging should be enabled on the source database. You can check the status of binary logging as follows

CODE


mysql> SHOW VARIABLES LIKE 'log_bin';

If binary logging is enabled, you should see the following status

CODE

    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | log_bin       | ON    |
    +---------------+-------+

Server-Id

The source database must have a non zero server-id and the server-id value should be different than the server-id value on the source.

Staging environment requirements

Staging OS user

This is a typical Delphix OS staging user. Key requirements for this user are given below.

Please refer to Delphix Docs for more detailed requirements.

A Delphix OS user with the elevated permissions to run ps, mount, umount, mkdir, rmdir commands without requiring a password. Below is an example where delphix_os will be used for the mounting purpose.

CODE

/etc/sudoers
Defaults:delphix_os !requiretty
delphix_os ALL=NOPASSWD: \ 
/bin/mount, /bin/umount, /bin/mkdir, /bin/rmdir, /bin/ps

Delphix OS user should be in the same primary and secondary groups as mysql user ( or the MySQL binary owner )
Delphix OS user must have execute access on all files within MySQL installation folder - Min permission level 775 recommended.

Storage

Staging Host must have enough storage space to hold the source backup file.
Empty folder on staging host to hold delphix toolkit [ approximate 2GB free space ]

MySQL version & configuration

MySQL Binary version must match the version on the source database(s)
[Recommended] As every organization's MySQL configuration is different, User can place a starter my.cnf file to be present in Delphix Toolkit Directory when creating a staging database. Delphix will use this my.cnf file and modify it as per the configuration provided during the dsource creation process. This is recommended to reduce the possibility of errors while restoring the backup from the source database. However, having the my.cnf in the toolkit directory is not mandatory. Connector has the ability to create my.cnf file on its own.

Target environment requirements

Target OS user

This is a typical Delphix OS target user. Key requirements for this user are given below.

Please refer to Delphix Docs for more detailed requirements.

A Delphix OS user with the elevated permissions to run ps, mount, umount, mkdir, rmdir commands without requiring a password. Below is an example where delphix_os will be used for the mounting purpose.

CODE


/etc/sudoers

Defaults:delphix_os !requiretty
delphix_os ALL=NOPASSWD: \ 
/bin/mount, /bin/umount, /bin/mkdir, /bin/rmdir, /bin/ps

Delphix OS user should be in the same primary and secondary groups as mysql user ( or the MySQL binary owner )
Delphix OS user must have execute access on all files within MySQL installation folder - Min permission level 775 recommended.

Done, what's next?

All the pre-requisites are now taken care of, next step is to install the connector.