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
-
Then, at the MySQL prompt, issue the following command to create a user:
CREATE USER ‘<username>’@’<IP_address_of_eG_agent>’ IDENTIFIED BY ‘<password>’;
GRANT PROCESS,SELECT ON . TO ‘<username>’@’<IP_address_of_eG_agent>’;
For instance, to ensure that user john (with password john) is able to connect to the MySQL server (being monitored) from the eG 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. For instance, 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>';
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:
-
Create a user by issuing the following command:
CREATE USER 'egdb'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'egin123@';, where egdb is the username and egin123@ is the password.
-
Now to ensure that the above user is created on the database, run the below command:
select host, user from mysql.user;, this will show the list of users as in Figure 1.
-
Alternatively, to check the list of users, go to Administration and click on the Users and Privileges under Management option. This will open the the list of users on the database and their privileges, as shown in Figure 2.
-
To configure the above-mentioned user with all the privileges, issue the command below:
GRANT ALL PRIVILEGES ON database1.* TO 'egdb'@'localhost' WITH GRANT OPTION;
Figure 3 : Granting all privileges to user
-
Now, to configure the above-mentioned user with limited number of connections run the following command:
ALTER USER 'egdb'@'localhost' IDENTIFIED BY 'egin123@' WITH MAX_USER_CONNECTIONS 3;
Figure 4 : To limit the number of connections
-
To check the number of current connections established by this user on the database, issue the following command:
show processlist;, this will display all the user connections currently established on the database.
Figure 5 : List of connections established on the database
-
Now, connect to the database server using egdb user. For that, go to Database tab and click Connect to database option, see Figure 6. (or) go to home and establish a new connection as egdb user,refer Figure 7.
Figure 6 : Establishing new connection through database tab
-
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 egdb user than the limited count 3, an error message (see Figure 8) will be shown.
-
Once the pre-requisites are set, manage the MySQL server using the procedure explained in the Managing the MySQL Server
