Additions:
Date management
now() - interval 10 day
CURDATE() + INTERVAL 1 DAY
Additions:
Configure user to monitor things with Nagios for instance:
grant usage, super on *.* to "monitoring_user"@"W.X.Y.Z" identified by "xyz";
Additions:
Configure user for Cacti monitoring:
GRANT PROCESS ON *.* TO cacti@'cactimachine' IDENTIFIED by 'cactipasswd';
GRANT SUPER ON *.* TO cacti@%'cactimachine' IDENTIFIED BY 'cactipasswd';
Additions:
Configure user for replication:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>';
FLUSH PRIVILEGES;
Additions:
Configure user for backup:
grant select, lock tables, show view on *.* to 'backup'@'localhost' identified by 'some password';
Additions:
Deletions:
Additions:
Database variable monitoring
show variables;
show gloabal variables;
show global status;
Additions:
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
Additions:
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.
Additions:
Select "
MyValue" as source, tableName.*
From tableName;
SELECT *
FROM mother_of_all_searches
WHERE particulars_id = 'X'
OR particulars_id = 'Y'
OR particulars_id = 'Z';
select table_schema 'intranet' , sum(data_length+index_length)/1024/1024 'db size in MB'
from tables
group by table_schema;
Deletions:
Select "
MyValue" as source, tableName.* from tableName;
SELECT * FROM mother_of_all_searches WHERE particulars_id = 'X' OR particulars_id = 'Y' OR particulars_id = 'Z';
select table_schema 'intranet' , sum(data_length+index_length)/1024/1024 'db size in MB' from tables group by table_schema;
Deletions:
Additions:
%%(mysql)
Additions:
Get the size of your databases
%%(
MySQL)
select table_schema 'intranet' , sum(data_length+index_length)/1024/1024 'db size in MB' from tables group by table_schema;
Additions:
Get mysql version
select version();
Additions:
Get a specific number of charactere from a select statement
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';
Deletions:
Get a specific number of charactere from a select statement
Additions:
mysqldump:
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;
Deletions:
mysqldump:
Additions:
mysqldump:
1 insert per line: use the option: --skip-extended-insert
ITTips
User management:
show grants: displays information about users
http://dev.mysql.com/doc/refman/5.0/en/show-grants.html∞