WaZaRWiki : MySQLTips

GaelReignier :: Categories :: PageIndex :: RecentChanges :: RecentlyCommented :: Login/Register :: Hosted by: eNiX
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:

Sequential write oriented:

Date management


NOW() - INTERVAL 10 DAY

CURDATE() + INTERVAL 1 DAY

There are no comments on this page. [Add comment]

Valid XHTML 1.0 Transitional :: Valid CSS :: Powered by WikkaWiki
Page was generated in 0.0966 seconds