Tag: partitioning

common_schema 1.2: security, partition management, processes, QueryScript goodies

MySQL

common_schema 1.2 is released! This version comes shortly after 1.1, yet contains quite a few interesting goodies:

  • Account blocking
  • Security audit
  • RANGE partition management
  • Slave status
  • Better blocking and idle transaction management
  • QueryScript goodies:
    • echo, report
    • while-otherwise statement; foreach-otherwise statement
    • Better variable scope handling
    • Complete support for variable expansion
    • Transaction support within QueryScript
  • More summary info and SQL statements in processlist-related views

A closer look at these follows:

Account blocking

A new view called sql_accounts, inspired by oak-block-account (also see here and here) provides with the means of blocking use accounts (and releasing them, of course) without revoking their privileges. It offers the SQL statements to block an account (by modifying its password in a symmetric way) and to release an account (by modifying its password back to normal). It really works like a charm. Together with killall() and sql_accounts this gives the administrator great control over accounts.

Security audit

Imported from openark kit, and implemented via QueryScript, the security_audit() procedure will audit your accounts, passwords and general settings to find problems, pitfalls and security hazards. I will write more on this later.

RANGE partition management

The sql_range_partitions view manages your RANGE and RANGE COLUMNS partitioned tables by providing with the SQL statements to drop oldest partitions and to create the next (in sequence) partitions. See my earlier post. Continue reading » “common_schema 1.2: security, partition management, processes, QueryScript goodies”

Your magical RANGE partitioning maintenance query

MySQL

If you use RANGE (or RANGE COLUMNS) partitioning, and in particular when partitioning by date/time, then your are subject to the following questions: how and when do you create the “next” partition? How and when do you drop your older partitions?

Many people use in-house scripting to do that, and Giuseppe Maxia wrote Partition Helper. But I would like to take you one step forward, and provide with a query (based on views) which automagically understands which new partition you want to create, and provides you with the statement to do so. It looks somewhat like this (a demo follows later on):

mysql> SELECT * FROM sql_range_partitions \G
*************************** 1. row ***************************
            table_schema: test
              table_name: city
sql_drop_first_partition: alter table `test`.`city` drop partition `p3`
  sql_add_next_partition: alter table `test`.`city` add partition (partition `p_20160101000000` values less than (736329) /* 2016-01-01 00:00:00 */ )
*************************** 2. row ***************************
            table_schema: test
              table_name: quarterly_report_status
sql_drop_first_partition: alter table `test`.`quarterly_report_status` drop partition `p3`
  sql_add_next_partition: alter table `test`.`quarterly_report_status` reorganize partition `p_maxvalue` into (partition `p_20110401000000` values less than (1301608800) /* 2011-04-01 00:00:00 */ , partition p_maxvalue values less than MAXVALUE)

A closer look at why this is magic

This query just gave you the DROP PARTITION and ADD PARTITION for all tables in your databases that use a RANGE partitioning scheme. But, consider: Continue reading » “Your magical RANGE partitioning maintenance query”