mysar - sar-like utility for MySQL
Current version is v0.5-alpha Report bugs @ http://bugs.launchpad.net/mysar/
Minimum set of parameters
mysar.pl --user user --pass passwd --variables|status
See USAGE for optional parameters and defaults.
This utiliy sole purpose is to take a snapshot of MySQL's running status in a number of tables so it they can be queried and cross reference with other utilities and external and internal logs. For example: sar or MySQL's query logs.
Options List:
--host <hostname> Connect to a remote host to perform tests
--socket <socket> Use a different socket for a local connection
--port <port> Port to use for connection (default: 3306)
--user <username> (required) Username to use for authentication
--pass <password> (required) Password to use for authentication
--variables Gather the global variables values
--status Gather the global status values
You need to specify at least one of the options
above
--varfile File with the list of variables to monitor
--statfile File with the list of status variables to
monitor
For variables/status specification you may
use '%' as wildcard
--database Specifies which schema contains the tables
'value_var' & 'value_stat'
--purge Specify the number of rows to keep in the table(s)
being updated.
Specify the host being monitored, default value is localhost
Specify the socket to use to connecto to the database. The default value is taken from the configuration files.
Which port to use to connect to the database. Default value is 3306
MANDATORY PARAMETER. Which user to connect to the database. There is no default value. Make sure that it has been granted at least PROCESS, CREATE for all schemas and ALL for the mysar schema.
GRANT USAGE, PROCESS, CREATE ON *.* TO 'mysar'@'localhost' IDENTIFIED BY 'passwd'; GRANT ALL ON mysar.* TO 'mysar'@'localhost'
User mysar should only be defined to connect from localhost, unless you're monitoring a remote server. Replace mysar and passwd with the values of your preference.
MANDATORY PARAMETER. Password to use to connect to the database. There is no default value.
ONE OF THE 2 HAS TO BE PRESENT. One of these 2 parameters needs to be present. variables gathers the result of SHOW GLOBAL VARIABLES and status from SHOW GLOBAL STATUS.
It is possible to limit the variables to gather by using a file where each line contains a reg expression like what you would use in SHOW GLOBAL VARIABLES LIKE 'reg_exp'.
Same as before with the expressions to use in SHOW GLOBAL STATUS LIKE 'reg_exp'.
Specify the schema that mysar will use to create its tables. Details on defaults are not completely defined and currently the database has to exist when running mysar.pl for the first time. Use:
CREATE DATABASE dbname;
Specify the number of days being kept in the tables that are being updated. The default is 30, specifying 0 implies no purge.
I have not tested the routine with other OS like Windows and Solaris. I currently don't see any issues on why it shouldn't run on them as long as you have a proper Perl installation. This may vary in future releases, especially Windows due to its file naming convention. In the long term Windows will be fully supported, but for now it'll be implemented towards the end of each release cycle (a resources issue).
Since there is no need for the trigger, it should run with MySQL v4.1. However, since v4.1 is an end of life'd release, keeping it backward compatible with v4.1 is not a priority (another resources issue). The utility will remain untested for earlier releases.