ITTips
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
There are no comments on this page. [Add comment]