As we know, MySQL workbench is excellent GUI tool for managing MySQL DB servers, creating ERDs (Data Modelling) and for sql development. But with this GUI tool, we are getting some command-line utilities too like mysqldiskusage, mysqlindexcheck, mysqlfailover, mysqldiff, mysqldbcompare etc.,
Here, I’m describing mysqldiskusage utility, which is not only displays mysql db usage but also displays usage of binary log, slow query log, error log, general query log, relay log, and InnoDB tablespaces.
mysqlslap is native mysql utility to check client load in MySQL server. It’s introduced in MySQl 5.1. It seems very useful as it has lots of options to check load of your server by running different kind of queries with specific iterations, specific concurrency. Output will give you how much max/min/avg time has been taken by the queries.
I have used sql-bench utility before, but looks like this one is more easy for benchmarking on MySQL server.
“Server log tables” is one of the finest feature in MySQL 5.1 version but It looks like people are not using this feature regularly or perhaps they are happy with files only. Here, I’m explaining how we can enable that feature, use it and how it will be helpful for filtering the required statements in slow-log/general-logs.
Generally, for enabling slow logs or general logs, we have to set below parameters in my.cnf.
For slow logs:
slow_query_log = 1/ON
log_slow_queries Read more [...]
I just recently used Event scheduler which was the major feature in MySQL 5.1 version. Its very much similar to the linux crontab functionality. MySQL Event is nothing but a bunch of statements which we can run on specific schedule. When you will create any event, its just a database object like table, view or stored procedure.You can also say that its like a trigger but trigger will be fired/executed on specific action and event will be fired/executed on specific time.
Some limitations of partitioning in MySQL 5.1 on one page.
All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.These rules also apply to existing nonpartitioned tables that you wish to partition.
Partitioning applies to all data and indexes of a table; you cannot partition only the data and not the indexes, or vice versa, nor can you partition only a portion of the table.
Prior to MySQL 5.1.43, it was not possible to Read more [...]