openworld 2014 - schema management: versioning and automation with puppet and mysql utilities
TRANSCRIPT
Schema Management: Versioning and Automation with Puppet and MySQL Utilities
Oracle Open World 2014 : MySQL Central Frédéric Descamps
Who am I ?● Frédéric Descamps «lefred»● @lefred● http://about.me/lefred● within Percona since 2011● managing MySQL since 3.23 (as far as I remember)
● devops believer
We live in a devops world \o/
Oracle Open World 2014 : MySQL Central
-3-
devops ?
we can say that devops is bringing together people and practices from development
and operationsOracle Open World 2014 : MySQL Central
devops ?
we can say that devops is bringing together people and practices from development
and operations
I am a happy dev !I am a happy op !
Oracle Open World 2014 : MySQL Central
CAMS● devops is about CAMS
– Culture
– Automation
– Measurement
– Sharing
@botchagalupe @damonedwards http://www.opscode.com/blog/2010/07/16/what-devops-means-to-me/
Oracle Open World 2014 : MySQL Central
This talk is about automation● What techniques can I use ?
– Infrastructure as Code
– Continuous Improvement
Oracle Open World 2014 : MySQL Central
WE are already between devs and ops
Oracle Open World 2014 : MySQL Central
WE are already between devs and ops
Oracle Open World 2014 : MySQL Central
I am a happy DBA !
so... AUTOMATION● Can we automate DBAs job ?● Should we ??● Even if we have only one database ?
Oracle Open World 2014 : MySQL Central
● Everything needs to be reproducible
Oracle Open World 2014 : MySQL Central
What do we «usually» automate ?● binaries installation (which version of MySQL)● configuration (my.cnf ?)● cron jobs● monitoring● databases (schemas)● grants● replication ?
Oracle Open World 2014 : MySQL Central
Infrastructure as code● So to automate all these tasks a multiplicity of projects
appeared on the web:– puppet
– chef
– cfengine
– ansible
– saltstack
– ...
Oracle Open World 2014 : MySQL Central
let's focus on Puppet● we will install MySQL on a new node
or or
Oracle Open World 2014 : MySQL Central
let's focus on Puppet● we will install MySQL on a new node
5.5 or 5.6 or 10.0 or 5.7 ?
Oracle Open World 2014 : MySQL Central
let's focus on Puppet● Puppet recipes will take care of the OS
or
Oracle Open World 2014 : MySQL Central
let's focus on Puppet● Puppet recipes will take care of the OS
or
Oracle Open World 2014 : MySQL Central
case $::osfamily { 'RedHat': { $require = Yumrepo['mysqlrepo'] } 'Debian': { $require = Apt::Source['mysqlrepo'] }}
Example in hiera
classes:
mysql
mysql::server
mysql::mysql_root_password: «fred»
mysql::mysql_distro: «community»
mysql::mysql_version: «5.6»
mysql::mysql_serverid: «2»
mysql::mysql_bind_interface: «eth1»
mysql::ensure: «running»
Oracle Open World 2014 : MySQL Central
puppet (continue)● And we can automate many things like schema creation,
grants:
mysql::db { $graphite_db_name: user => $graphite_db_user,
password => $graphite_db_password,
host => $graphite_db_host,
grant => ['SELECT', 'UPDATE'],
}
Oracle Open World 2014 : MySQL Central
puppet: MySQL modules● You can find many MySQL modules for puppet:
– https://github.com/olindata/puppetlabs-mysql
– https://github.com/lefred/puppet-lefred-mysql
Oracle Open World 2014 : MySQL Central
and now ?● can we go further ?● what could we automate ?● schemas !
– using mysqldump ?
– using frameworks ?
Oracle Open World 2014 : MySQL Central
but... automation !● we want this automated● easy to test multiple times in development, staging, ...● to use in production● and if possible to rollback easily if needed !
v1 v2
Oracle Open World 2014 : MySQL Central
Database Schema Migration Tools● There are some existing tools:
– Liquibase
– Flyway
– c5-db-migration
– dbdeploy
– MyBatis
– Autopatch
– Sqitch
Oracle Open World 2014 : MySQL Central
Database Schema Migration Tools
Most of them are...
Integrated with a framework or an ORM
Oracle Open World 2014 : MySQL Central
Database Schema Migration Tools
Most of them are...
for JAVA applications
Oracle Open World 2014 : MySQL Central
Database Schema Migration Tools
Most of them use...
XML definitions
Oracle Open World 2014 : MySQL Central
XML Definitions● What is easier to read and maintain for us, DBAs ?
Oracle Open World 2014 : MySQL Central
<?xml version="1.0" encoding="UTF8"?> <databaseChangeLogxmlns="http://www.liquibase.org/xml/ns/dbchangelog"xmlns:xsi="http://www.w3.org/2001/XMLSchemainstance"xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangeloghttp://www.liquibase.org/xml/ns/dbchangelog/dbchangelog2.0.xsd"> <changeSet id="1" author="lefred"><createTable tableName="department"><column name="id" type="int"><constraints primaryKey="true" nullable="false"/></column><column name="name" type="varchar(50)"><constraints nullable="false"/></column><column name="active" type="boolean" defaultValueBoolean="true"/></createTable></changeSet> </databaseChangeLog>
XML Definitions● or this ?
Oracle Open World 2014 : MySQL Central
CREATE TABLE department (id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL
);
Database Schema Migration Tools
most of them are...
blocking schema changes
Oracle Open World 2014 : MySQL Central
Database Schema Migration Tools
and finally...
not designed to be used via a configuration management system
Oracle Open World 2014 : MySQL Central
so are we blocked ? NO !● Oracle's MySQL-Utilities● Percona Toolkit● a standalone Python script● a new Puppet type and provider
Oracle Open World 2014 : MySQL Central
Oracle's MySQL-Utilities● toolkit that was generally distributed via MySQL Workbench● Dec 7th 2010 : v 1.0.0● Aug 20th 2014: v.1.5.1 RC● it contains a package of tools used for maintenance and
administration of MySQL servers● they are written in Python● the utilities are command line commands and a Python
library
Oracle Open World 2014 : MySQL Central
Oracle's MySQL-Utilities (2)● Utilities can help for
– database operations: provision a slave, copy a db, diff tables, synchronize tables
– general operations: find disk space used, table structure, duplicate users, work on indexes, find database objects matching patterns
– HA operations: add a slave, setup failover, find slaves, check replication
– server operations: make a temp copy of the server, use secure connection
– «specialized operations»: record login events, copy/move audit log, show failed queries, show connections
Oracle Open World 2014 : MySQL Central
What are the interresting tools for us ● mysqldbcompare — compares two databases and
identify differences (objects, row counts, data concistency) ● mysqldiff — identifies differences among database
objects● And all those options can be integrated into a Python script
using the library
Oracle Open World 2014 : MySQL Central
Percona-Toolkit● collection of advanced command-line tools● Aug 6th 2014: v2.2.10● They can do a lot of useful tasks like table checksum and
synchronization, delay slave on version where it wasn't implemented yet, digest the queries
● But for us, the most important:
ptonlineschemachange
Oracle Open World 2014 : MySQL Central
pt-online-schema-change● this tool allows to alter a table's structure without blocking
reads or writes● the tool works on a copy of the table you wish to alter● switches them at the end of the process ● and delete the original one
online-migration.py● standalone Python script● command line only no GUI sorry ;-)
● uses Oracle's MySQL-Utilities library● uses pt-online-schema-change● uses normal SQL statements that all DBAs understand● can be integrated in Puppet
Oracle Open World 2014 : MySQL Central
what can it do ?● The script is able to
– return the schema version of a database
– list all versions and their status
– migrate up
– migrate down (rollback a migration)
– checksum the schema
– return the difference between expected schema and installed schema
Oracle Open World 2014 : MySQL Central
and in Puppet ?
node percona1 { include mysql include mysql::server include mysql::utilities include mysql::percona_toolkit include onlinemigration
Class['percona::repository'] > Class['percona::server']
mysql_schema { "world": ensure => present, require => [ Package["onlinemigration"],
Service['mysql'] ], version => 2, cwd => "/root/om", } ...}
Oracle Open World 2014 : MySQL Central
and in Puppet ?
node percona1 { include mysql include mysql::server include mysql::utilities include mysql::percona_toolkit include onlinemigration
Class['percona::repository'] > Class['percona::server']
mysql_schema { "world": ensure => present, require => [ Package["onlinemigration"],
Service['mysql'] ], version => 2, cwd => "/root/om", } ...}
Oracle Open World 2014 : MySQL Central
...info: Mysql_schema[world](provider=mysql_schema): performing the check, last_version is 1info: Mysql_schema[world](provider=mysql_schema): we need to migrate to another version (2)info: Mysql_schema[world](provider=mysql_schema): performing the migration...
online-migration.py internals● uses a dedicated table : online_migration.migration_sys– it contains some meta data like db, version, apply date, status
– created by: onlinemigration.py init_sysdb
– first entry is version 0 and is created by the «init» command
● uses a folder with meta-data:– XXXXup.meta: meta data required to upgrade to version XXXX, like version,
checksum and comment
– XXXXup.mig: contains the required changes/statements
– XXXXdown.mig: contains the automated reverse statements
online-migration.py: features● versioning and status
$ onlinemigration.py status worldMigration of schema world :+++++| VERSION | APPLIED | STATUS | COMMENT |+++++| 0000 | 20140922 09:17:16 | ok | Initial file || 0001 | none | pending | none || 0002 | none | pending | none || 0003 | none | pending | add nice column to cit |+++++
online-migration.py: features (2)● migration up$ onlinemigration.py up world to 3INFO : You want to migrate up to version 0003INFO : Ok this version is pendingINFO : Preparing migration to version 0001INFO : Applied changes match the requested schemaINFO : Preparing migration to version 0002INFO : Applied changes match the requested schemaINFO : Preparing migration to version 0003INFO : Applied changes match the requested schema
$ onlinemigration.py status worldMigration of schema world :+++++| VERSION | APPLIED | STATUS | COMMENT |+++++| 0000 | 20140922 09:17:16 | ok | Initial file || 0001 | 20140922 09:17:46 | ok | none || 0002 | 20140922 09:18:24 | ok | none || 0003 | 20140922 09:18:36 | ok | add nice column to cit |+++++
online-migration.py: features (3)● checksum
$ onlinemigration.py status worldMigration of schema world :+++++| VERSION | APPLIED | STATUS | COMMENT |+++++| 0000 | 20140922 09:17:16 | ok | Initial file || 0001 | 20140922 09:17:46 | checksum problem | none |+++++
$ onlinemigration.py checksum worldworld's current schema checksum = 3239e7245d98d27681020e36d0085676
online-migration.py: features (4)● migration down (rollback)
$ onlinemigration.py down world 2INFO : You want to migrate down 2 version(s)INFO : rollback from 0003 to 0002INFO : rollback from 0002 to 0001
$ onlinemigration.py status worldMigration of schema world :+++++| VERSION | APPLIED | STATUS | COMMENT |+++++| 0000 | 20140922 09:17:16 | ok | Initial file || 0001 | 20140922 09:17:46 | ok | none || 0002 | 20140922 09:18:24 | rollback | none || 0003 | 20140922 09:18:36 | rollback | add nice column to cit || 0002 | none | pending | none || 0003 | none | pending | add nice column to cit |+++++
online-migration.py: features (5)● schema diff
$ onlinemigration.py diff worldWARNING : Schema of world doesn't have expected checksum(4478d85870969436400bac023f2b2b7c)
TABLE `City` `CountryCode` varchar(10) DEFAULT NULL,+ `CountryCode` char(3) NOT NULL DEFAULT '',
todo● Make the code more robust (handling errors)● Write support for store procedures, functions,...● Support online DDL (ALGORITHM=INPLACE)
● Add configuration file
Resources● Oracle MySQL — http://dev.mysql.com/downloads/mysql/
● MySQL-Utilities — http://dev.mysql.com/downloads/utilities/
● Percona-Toolkit - http://www.percona.com/doc/percona-toolkit/2.2/index.html
● pt-online-schema-change — http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html
● puppet — http://puppetlabs.com
● online-migration.py — https://github.com/lefred/online-migration
Oracle Open World 2014 : MySQL Central
Thank you
Questions ?
Oracle Open World 2014 : MySQL Central