Pre-requisites for Monitoring the MySQL Server
To run the MySQL tests, the eG agent should be configured with the credentials of a user who has server-wide process and select privileges. If such a user does not pre-exist, then, in the user table of the mysql database of the target MySQL server, you need to manually create a user account with the aforesaid privileges. To create and configure such a user, follow the procedure discussed below:
-
To create a new user account, you must connect to the MySQL server as the MySQL root user. For that, first login to the MySQL host, and at the command prompt, issue the following command:
mysql -u root
If you have assigned a password to the root account, you will also need to supply a - password or -p option, as shown below:
mysql -u root -pegurkha
-
After successfully logging into the MySQL server, issue the following statement to access the mysql database, which holds the user table:
use mysql
-
The MySQL monitoring model uses both internal agent and external agent to run the tests and collect metrics related to the availability, connectivity, responsiveness, query performance, tables, etc., of the target MySQL server. To enable these agents to connect to the target MySQL server and collect metrics, a user who is going to execute the tests should be created and granted with required permissions. For this purpose, administrators need to create a user using IP address of the internal agent host and another user using IP address of the external agent host. For example, to create a user using IP address of the internal agent host, run the following command from the MySQL prompt.
CREATE USER ‘<username>’@’<IP_address_of_eG_internal_agent>’ IDENTIFIED BY ‘<password>’;
GRANT PROCESS,SELECT ON *.* TO ‘<username>’@’<IP_address_of_eG_internal_agent>’;
For example, to ensure that user john (with password john) is able to connect to the MySQL server (being monitored) from the eG internal agent host, 192.168.8.91, the following command is to be issued:
CREATE USER ‘john’@’192.168.8.91’ IDENTIFIED BY ‘john’;
GRANT PROCESS,SELECT ON . TO ‘john’@’192.168.8.91’;
Note:
- The CREATE and GRANT commands are case-sensitive; therefore, take care while specifying the user name, password, and privileges.
- Only the IP address of the eG agent's host can be provided as part of the CREATE command's syntax; the host name of the eG agent cannot be provided instead.
-
To ensure that the external agent is able to execute the MySQL Network test, make sure that you create a user with the same credentials (i.e., name and password) and privileges as above and map that user to the IP address of the external agent. Let's say, in the example above, to enable the external agent at IP address 192.168.8.92 to run the MySQL Network test, your command should be:
CREATE USER ‘john’@’192.168.8.92’ IDENTIFIED BY ‘john’;
GRANT PROCESS,SELECT ON *.* TO ‘john’@’192.168.8.92’;
-
Once the above-mentioned commands execute successfully, the user table will be updated with two records for the user account that was newly created - one mapped to the internal/remote agent’s IP address and another mapped to the external agent’s IP address.
-
For the MySQL Replication Status, MySQL Replication Slave Status, MySQL Bin Log, MySQL Replication Slave tests to run, the user configured for that tests should additionally possess the following privileges. To configure the above-mentioned user with the specific privileges, issue the commands given below:
GRANT REPLICATION CLIENT on *.* to '<username>'@'<IP_address_of_eG_agent>';
GRANT REPLICATION SLAVE on *.* to '<username>'@'<IP_address_of_eG_agent>';
For the MySQL Root Blocker test to run, the user configured for that test should additionally possess the following privilege. To configure the above-mentioned user with the specific privileges, issue the command given below:
GRANT EXECUTE on *.* to '<username>'@'<IP_address_of_eG_agent>';
-
If the target MySQL server is hosted on a cloud infrastructure, administrators need to create a user using the IP address of the remote agent that collects metrics from the target MySQL server. For example, to ensure that user john (with password john) is able to connect to the target MySQL server hosted on the cloud from the eG remote agent host, 192.168.8.93, the following command is to be issued:
CREATE USER ‘john’@’192.168.8.93’ IDENTIFIED BY ‘john’;
GRANT PROCESS,SELECT ON *.* TO ‘john’@’192.168.8.93’;
Note:
If the MySQL Network test is executed using an external agent instead of the remote agent that communicates with the target MySQL server hosted on the cloud, then the administrators should create a user with the same credentials (i.e., name and password) and privileges as the remote agent. This user has to be mapped to the IP address of the external agent as mentioned above in the example.
-
How to limit the number of connections for a user?
To restrict client use of MySQL server resources is to set the global max_user_connections system variable to a nonzero value. This limits the number of simultaneous connections that can be made by any given account, but places no limits on what a client can do once connected. To limit the number of connections for a user follow the steps given below:
-
To configure the user 'john' with limited number of connections, run the following command from the MySQL prompt:
ALTER USER 'user'@'localhost' IDENTIFIED BY 'password' WITH MAX_USER_CONNECTIONS n;
For on-premises MySQL server, run the following commands from the MySQL prompt. In our example, to limit the number of connections that can be established by the user (john) created using the IP address of the internal agent, run the following command:
ALTER USER 'john'@'192.168.8.91' IDENTIFIED BY 'john' WITH MAX_USER_CONNECTIONS 3;
Likewise, to limit the number of connections that can be established to the user (john) created using the IP address of the external agent (as shown in example), run the following command:
ALTER USER 'john'@'192.168.8.92' IDENTIFIED BY 'john' WITH MAX_USER_CONNECTIONS 3;
To limit the number of connections that can be established by the user created using the IP address of the remote agent (as shown in example) that monitors the MySQL server hosted on the cloud, run the following command:
ALTER USER 'john'@'192.168.8.93' IDENTIFIED BY 'john' WITH MAX_USER_CONNECTIONS 3;
If the MySQL Network test is executed using an external agent instead of the remote agent that communicates with the target MySQL server hosted on the cloud, then, administrators should run the above mentioned command using the details of the the user created using the IP address of the external agent.
-
If the number of connections to the database exceeds the connection limit, new connections fail and return an error. That means, If you try to establish more number of connections for the john user than the limited count 3, an error message will be recorded in the error log files available on the MySQL instance and eG agent.
-
Once the pre-requisites are set, manage the MySQL server using the procedure explained in the Managing the MySQL Server
