Common Schema: a framework for MySQL server administration
Presented at the 2012 Percona Live: MySQL Conference & Expo by Roland Bouman
Slides are at http://form.percona.com/PLMCE2012Slid...
From the official conference description at http://www.percona.com/live/mysql-con...
The common_schema project provides a set of thoroughly documented MySQL views, stored procedures and functions which simplify otherwise complex shell and client scripting tasks. The common_schema is delivered as a self-contained MySQL schema, which is easily installed simply by running a SQL script: there is no need to configure nor to compile; no special plugins are required, and no changes to your configuration. Because common_schema is implemented in SQL, it is independent of any specific operating system, installed packages or dependencies.
The common_schema is compatible with all MySQL 5.1 servers, and more specialized editions are available for servers running the InnoDB
plugin and Percona server. All common_schema editions are available on google code under a permissive BSD license.
During this 1 hour talk, the following topics will be covered.
Obtaining and installing common schema
Overview: What's inside the common schema
Monitoring
track changes in status variables over time
gain insights on running queries using different views on the processlist
Security
what privileges have been granted, and to which users
Given a set of privileges, find matching users
Schema Analysis:
find out which tables lack primary keys, or have redundant keys.
generate DDL: use the common schema
Query and Schema object analysis
which schema objects are used by any particular query, view, or
stored routine
Function library
Advanced date and time functions, checksums and more
Advanced SQL Scripting
Advanced dynamic SQL: using eval() and exec()
Looping over queries, lists, intervals or waiting for conditions:
using foreach() and repeat_exec()
Contributing to common_schema
found a bug? have a suggestion? want to contribute code? Here's how...
For selected topics, example usages will be demonstrated.
After the session there will be room for questions.