WaZaRWiki: Database variable monitoring

Database variable monitoring

SHOW variables;

SHOW gloabal variables;

SHOW global status;

User management:

Displays information about users http://dev.mysql.com/doc/refman/5.0/en/show-grants.html∞

SHOW grants

Configure user for replication:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>';

FLUSH PRIVILEGES;

Configure user for monitoring replication:

GRANT replication client on *.* to 'username'@'localhost' identified BY 'XYZ';

Configure user for backup:

GRANT SELECT, LOCK TABLES, SHOW view on *.* to 'backup'@'localhost' identified BY 'some password';

Configure user for Cacti monitoring:

GRANT PROCESS ON *.* TO cacti@'cactimachine' IDENTIFIED BY 'cactipasswd';

GRANT SUPER ON *.* TO cacti@%'cactimachine' IDENTIFIED BY 'cactipasswd';

Configure user to monitor things with Nagios for instance:

GRANT USAGE, super on *.* to "monitoring_user"@"W.X.Y.Z" identified BY "xyz";

mysqldump:

1 insert per line: use the option: --skip-extended-insert

Add a specific column in a select statement filled with the same value:

SELECT "MyValue" as source, tableName.* 

FROM tableName;

Source: will be the column title and it will be filled with "MyValue" for all the results

Get a specific number of charactere from a select statement

SELECT SUBSTRING(mother_of_all_results.source FROM 3 FOR 10) AS username, mother_of_all_results.*

FROM mother_of_all_results;

Create a table from another one

CREATE TABLE mother_of_all_results AS

SELECT * 

FROM mother_of_all_searches 

WHERE  particulars_id = 'X' 

OR  particulars_id = 'Y' 

OR  particulars_id = 'Z';

Get mysql version

SELECT version();

Get the size of your databases

SELECT table_schema 'intranet' , sum(data_length+index_length)/1024/1024 'db size in MB'  

FROM tables 

GROUP BY table_schema;

Tuning

key_buffer
max_allowed_packet
thread_stack
read_rnd_buffer_size

key_buffer is only allocated once, when the DB starts
max_allowed_packet (size of the packet created for each query), thread_stack, read_rnd_buffer_size are allocated for every connection to your DB server.
So from max_allowed_packet, thread_stack and read_rnd_buffer_size you can get the max number of connection to your db.
To do so, you need to divide the total amount of RAM you have by the sum of those 3 figures.

Disk usage patterns

This link is a really good overview of the type of files that are handled by MySQL: http://yoshinorimatsunobu.blogspot.com/2009/05/tables-on-ssd-redobinlogsystem.html∞

Random i/o oriented:

Table files (*.ibd)
UNDO segments (ibdata)

Sequential write oriented:

REDO log files (ib_logfile*)
Binary log files (binlog.XXXXXX)
Doublewrite buffer (ibdata)
Insert buffer (ibdata)
Slow query logs, error logs, general query logs, etc

Date management

NOW() - INTERVAL 10 DAY

CURDATE() + INTERVAL 1 DAY

WaZaRWiki : MySQLTips