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.
mysql>CREATE USER 'delphix_os'@'<staging_host>' IDENTIFIED BY 'delphix_user_passwd';
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
mysql>GRANT SELECT, SHUTDOWN, SUPER, RELOAD ,SHOW VIEW, EVENT, TRIGGER, REPLICATION CLIENT,REPLICATION SLAVE on *.* to 'delphix_os'@'staging-host';
mysql>GRANT SELECT, SHUTDOWN, SUPER, RELOAD ,SHOW VIEW, EVENT, TRIGGER on *.* to 'delphix_os'@'localhost';
You can also grant more permissive privileges
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
mysql> SHOW VARIABLES LIKE 'log_bin';
If binary logging is enabled, you should see the following status
+---------------+-------+
| 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.
/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.
/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.