To configure MySQL performance monitoring using G8Keeper, you will need to have DB user that has required permission to collect database statistics. Before we proceed any further, it is important to note that MySQL performance monitoring works only on databases with MySQL version greater than or equal to 5.7 and performance_schema is set to 1 in DB parameters. Also, the server where g8keeper-agent is running needs to have mysql client installed.
This user needs permissions on some of the MySQL system schemas. Here are the steps to setup the user.
Note:You need to be comfortable with the unix (linux) command line to perform following steps.
All the commands used below can be accessed in text format here
1. Login to your server that has access to your database.
2. On the terminal, run the MySQL command-line program, and log in as an administrator by typing the following command
> mysql -u root -p -hhostname
Note: If the database is running on the localhost, then you do not need the -h option, else you need to provide server address to access the DB host.
3. This will prompt you for the password, please enter password for root account.
4. Next step is to create a new user in database.
mysql> CREATE USER 'g8keeper'@'%' IDENTIFIED BY 'g8k33p3r112';
Note: replace password by a reasonably complex password
If DB is installed on the same host where g8keeper-agent is running then you can replace % by localhost
mysql> CREATE USER 'g8keeper'@'localhost' IDENTIFIED BY 'g8k33p3r112';
This user needs read only access on the databases.
To give the user access to these database from any host, type and run the following commands:
mysql> grant SELECT, PROCESS, EXECUTE on *.* to 'g8keeper'@'%' identified by 'g8k33p3r112';
Note:
- ‘%’ is used in above command to allow access from any host. However, if you are running the command on localhost and the user will be accessing the database from same host then you can replace ‘%’ by localhost
mysql> grant SELECT, PROCESS, EXECUTE on *.* to 'g8keeper'@'localhost' identified by 'g8k33p3r112';
In case there is a specific IP from where the user will access the database you can replace ‘localhost’ with that IP address or hostname
5. After you have given the required permission, we need to verify that the permissions have been granted. To verify the same run following command (depending on how you have created user)
mysql> show grants for 'g8keeper'@'localhost';
or
mysql> show grants for 'g8keeper'@'%';
It should show something like this
6. Once the permission is granted we need to just flush privileges so that the changes can take effect and we are done
mysql> flush privileges;
mysql> quit
With this we are all set.
All the commands used above can be accessed in text format here