Pre-Requisites

Install Requirements

note

This is specific to Self Hosted deployments. For Sath hosted connectors, these requirements are already in place.

• You need to install Helm version 3 or above.
• You can use Kubernetes cluster (1.26 or above) to install the connector

Connection Requirements

Below is needed to connect to MySQL

• MySQL Service Account User name
• MySQL Service Account Password
• MySQL URL

Create a Service Account

note

Below step assumes that you are already logged into MySQL with a user with root (sa in some systems) or sufficient privileges

1. Create the service account:

• Run the following SQL command to create a new user:

 CREATE USER 'idhub_service'@'%' IDENTIFIED BY 'strong_password_here';

info

• Replace idhub_service with the user name of the service account
• Replace strong_password_here with a secure password.
• '%' in the host column means that idhub_service can connect from any IP address. You could instead specify:

CREATE USER 'idhub_service'@'192.168.1.100' IDENTIFIED BY 'strong_password_here';

When creating a user, MySQL distinguishes users not only by their usernames but also by the specific hosts they are connecting from. This is why MySQL users are often defined as 'username'@'host'.

Key Points about host

• % (Wildcard): The % symbol is a wildcard that allows the user to connect from any host or IP address. For example, 'idhub_service'@'%' means the idhub_service user can connect from any IP address.

• Specific Host: You can restrict access to a user by specifying a particular hostname or IP address. For example:

  • 'idhub_service'@'localhost' means the user can only connect from the same machine where the MySQL server is running (the localhost or 127.0.0.1).
  • 'idhub_service'@'192.168.1.100' means the user can only connect from the host with IP 192.168.1.100.

• DNS and Hostnames: Instead of using an IP address, you can specify a hostname, like 'idhub_service'@'mydomain.com'. In this case, MySQL will resolve the hostname to an IP address when checking the connection.

• Security Implications: Allowing connections from '%' (any host) can be risky if the MySQL server is exposed to the internet, as it potentially allows anyone with the username and password to attempt to connect. For tighter security, it's common to restrict access by specifying a particular IP range or limiting connections to specific hosts

note

Keep a note of the Service account user name and it’s password that you created, as you would need that for connecting to IDHub.

---

2. Verify the Service Account

Verify whether the user has been successfully created, run the following command

SELECT user, host
FROM mysql.user
WHERE user = 'idhub_service';

This query will return any user named idhub_service along with the corresponding host from which they are allowed to connect.

EXAMPLE OUTPUT:

+---------------+------+

| user | host |

+---------------+------+

| idhub_service | % |

+---------------+------+

---

3. Configure MySQL for remote access (if needed):

• Edit the MySQL configuration file (my.cnf or my.ini).
• Ensure the bind-address is set to allow remote connections:

bind-address = 0.0.0.0

• Restart the MySQL service to apply changes.

info

For restarting the MySQL service, the process can vary depending on the operating system you're using.

1. For Linux (systemd-based distributions like Ubuntu, CentOS 7+, Fedora):
   • Open a terminal.
   • Use the systemctl command to restart MySQL
     • sudo systemctl restart mysql
   • To verify the service status:
     • sudo systemctl status mysql
   • If you encounter any issues, check the system logs:
     • sudo journalctl -u mysql
2. For Windows
   • Using the Command Prompt (Run as Administrator):
   • Stop the service:
     • mysqld stop
   • Start the Service
     • mysqld start
3. For MacOS (MySQL installed via Manually )
   • Stop the server
     • sudo /usr/local/mysql/support-files/mysql.server stop
   • Start the server
     • sudo /usr/local/mysql/support-files/mysql.server start
4. For Docker installations (If you're running MySQL in a Docker container)
   • Find your MySQL container ID
     • docker ps
   • Restart the container
     • docker restart <container_id>

---

4. Configure firewall (if applicable):

• Ensure your firewall allows incoming connections on MySQL's port (default 3306).

---

5. Prepare JDBC connection string:

• Construct the URL:

 jdbc:mysql://[hostname]:[port]/[database_name]

• Replace [hostname], [port], and [database_name] with your specific details.
• Keep a note of the URL as you would need that for connecting to IDHub.

EXAMPLE:

jdbc:mysql://mysql.iamsath.com:3306/test

Permissions

Minimum Permissions

Below we have discussed the minimum permissions that you need to set for the service account.

Permission Name	Permission Description
CREATE USER	Necessary for creating new user accounts in the MySQL database.
ALTER USER	Required for modifying existing user accounts, such as account attributes.
GRANT OPTION	Allows the service account to grant and revoke privileges to/from other users.
CREATE ROLE	Enables creation of roles, which can be used to manage groups of privileges
SELECT on mysql.user, mysql.db, mysql.tables_priv, mysql.columns_priv, mysql.procs_priv	These permissions are necessary for the service account to view existing user accounts, their privileges, and other relevant information

EXAMPLE:

Here's an example of granting these minimum permissions:

GRANT CREATE USER, ALTER USER ON *.* TO 'idhub_service'@'%' WITH GRANT OPTION;
GRANT CREATE ROLE ON *.* TO 'idhub_service'@'%';
GRANT SELECT ON mysql.user TO 'idhub_service'@'%';
GRANT SELECT ON mysql.db TO 'idhub_service'@'%';
GRANT SELECT ON mysql.tables_priv TO 'idhub_service'@'%';
GRANT SELECT ON mysql.columns_priv TO 'idhub_service'@'%';
GRANT SELECT ON mysql.procs_priv TO 'idhub_service'@'%';

Apply the Changes

• Run the following command to apply the privilege changes:

FLUSH PRIVILEGES;