Download to read offline
![UsersUsers mysql_user { "backup@10.10.10.1": ensure => 'present', password_hash => mysql_password(‘secret’), } mysql_grant { "backup@10.10.10.1/*.*": ensure => 'present', privileges => [ 'ALL' ], table => '*.*', user => "backup@10.10.10.1", }](https://image.slidesharecdn.com/mysqlandpuppetsittinginatree-170926162923/75/Automating-MySQL-operations-with-Puppet-40-2048.jpg)
![Replication userReplication user mysql_user { 'replica@%': ensure => 'present', password_hash => mysql_password('percona17'), } mysql_grant { 'replica@%/*.*': ensure => 'present', privileges => ['REPLICATION SLAVE'], table => '*.*', user => 'slave_user@%', }](https://image.slidesharecdn.com/mysqlandpuppetsittinginatree-170926162923/75/Automating-MySQL-operations-with-Puppet-46-2048.jpg)
![Replication OptionsReplication Options class { '::mysql::server': override_options => { 'mysqld' => { 'server-id' => 1, 'binlog-ignore-db' => [‘mysql’, ‘performance_schema’], 'log-bin' => 'binary-log.', 'binlog-format' => 'ROW', }, }, } CHANGE MASTER TO master_host="$mysql_master", master_port=3306, master_user='replica', master_password='percona17', master_use_gtid=current_pos; START SLAVE; Chose if you want to run in a Puppet exec, a more Intelligent script, … (MariaDB based example:)](https://image.slidesharecdn.com/mysqlandpuppetsittinginatree-170926162923/75/Automating-MySQL-operations-with-Puppet-47-2048.jpg)
![Configuring HAConfiguring HA ● Example using corosync/pacemaker (puppet-Example using corosync/pacemaker (puppet- corosync)corosync) cs_primitive { ‘p_mysql’: primitive_class => 'ocf', primitive_type => 'mysql', provided_by => 'heartbeat', Promotable => true, operations => [ { 'monitor' => { 'interval' => '10s', 'timeout' => '30s' } }, { 'monitor' => { 'interval' => '5s', 'timeout' => '30s', 'role' => 'Master', } }, ] } cs_colocation { 'mysql_master': Primitives => [ 'mysql_vip', 'p_mysql:Master' ], } cs_primitive { 'mysql_vip': primitive_class => 'ocf', primitive_type => 'IPaddr2', provided_by => 'heartbeat', Parameters => { 'ip' => '172.21.17.100', 'cidr_netmask' => '24' }, Operations => { 'monitor' => { 'interval' => '10s' } }, }](https://image.slidesharecdn.com/mysqlandpuppetsittinginatree-170926162923/75/Automating-MySQL-operations-with-Puppet-48-2048.jpg)
Uploaded byKris Buytaert
Automating MySQL operations with Puppet
This document summarizes a presentation about automating MySQL operations with Puppet. It discusses: - Why automation is important for consistency, security, and disaster recovery. Manual changes can introduce bugs and inconsistencies. - Puppet is an open source configuration management tool that can be used to automate MySQL configuration, users, backups, replication, and high availability clustering with tools like Corosync/Pacemaker. - Puppet modules define the desired state and Puppet ensures the actual state matches by making necessary changes. This provides auditability and change tracking through version control of Puppet code.
Automating MySQL operations with Puppet
- 1. Automating MySQL operationswith Puppet KrisBuytaert & Julien Pivotto @krisbuytaert @roidelapluie
- 2. Kris BuytaertKris Buytaert ●I used to be a Dev,I used to be a Dev, ● Then Became an OpThen Became an Op ● Chief Trolling Officer and Open SourceChief Trolling Officer and Open Source Consultant @Consultant @inuits.euinuits.eu ● Everything is an effing DNS ProblemEverything is an effing DNS Problem ● Building Clouds since before the bookstoreBuilding Clouds since before the bookstore ● Some books, some papers, some blogsSome books, some papers, some blogs ● Evangelizing devopsEvangelizing devops ● Organiser of #devopsdays, #cfgmgmtcamp,Organiser of #devopsdays, #cfgmgmtcamp, #loadays, ….#loadays, …. ● Part of the travelling geek circusPart of the travelling geek circus
- 3. Julien PivottoJulien Pivotto ●I am a sysadminI am a sysadmin ● I develop sometimesI develop sometimes ● Chief Traveling Officer and Open SourceChief Traveling Officer and Open Source Consultant @Consultant @inuits.euinuits.eu ● Born almost at cloud age :)Born almost at cloud age :) ● Monitoring, automation, databases, all mixedMonitoring, automation, databases, all mixed
- 4. What's this devopsWhat'sthis devops thing anyhow ?thing anyhow ?
- 5. C(L)AMSC(L)AMS ● CultureCulture ● (Lean)(Lean) ●AutomationAutomation ● MeasurementMeasurement ● SharingSharing Damon Edwards and John WillisDamon Edwards and John Willis Gene KimGene Kim
- 6. A global movementto improve the quality of softwareA global movement to improve the quality of software delivery leveraging Open Source experience, starteddelivery leveraging Open Source experience, started in Gent in 2009in Gent in 2009
- 7. Because the olddays:Because the old days: ● ““Put this Code Live, here's a tarball” NOW!Put this Code Live, here's a tarball” NOW! ● What dependencies ?What dependencies ? ● No machines available ?No machines available ? ● What database ?What database ? ● Security ?Security ? ● High Availability ?High Availability ? ● Scalability ?Scalability ? ● My computer can't install this ?My computer can't install this ?
- 8. 10 days intooperations10 days into operations
- 9. DevSalesDBASecQAMarDevSalesDBASecQAMar ketingMonitoringBusOpsketingMonitoringBusOps ● Nope DevOpsdidn’t forget anyoneNope DevOps didn’t forget anyone ● Actually DevOps does not make sense withoutActually DevOps does not make sense without everyoneeveryone ● Also devops != everyone is dba/tester/dev/op...Also devops != everyone is dba/tester/dev/op... ● devops != a job titledevops != a job title ● devops != a team namedevops != a team name
- 10. Why automate ?Whyautomate ?
- 11. Common ProblemsCommon Problems ●Many manual changes to systems ● Many undocumented changes ● Emergency Administration only ● Disaster Recovery site is a Disaster ● Time to deliver a box is to slow ● All boxen are different ● Computers don’t work hard enough for us
- 12. More ProblemsMore Problems ●How long does it take to reinstall a machine from 0 ● To the exact same point as before ? ● With different Hardware ? In a different cloud ? ● What about your (customer/personal data ) ● Did I just redirect prod frontend to dev backend?
- 13. Automation is notjustAutomation is not just about scaleabout scale ● ““I don’t automate coz I just have X servers” isI don’t automate coz I just have X servers” is no excuseno excuse ● You don’t need zillions of apps/servers toYou don’t need zillions of apps/servers to benefit from automationbenefit from automation ● It starts from one serverIt starts from one server ● Especially in the database world where we lookEspecially in the database world where we look for performance, fine tuning, and consistencyfor performance, fine tuning, and consistency
- 14. Security ?Security ? ●Monitoring that your platform hasn't changed.Monitoring that your platform hasn't changed. – Why is selinux disabled ?Why is selinux disabled ? – Who added / dropped that firewall ?Who added / dropped that firewall ? – What did this originally look like ?What did this originally look like ? – Is this file really what Bernd meant it to be ?Is this file really what Bernd meant it to be ?
- 15. #monitoringsucks#monitoringsucks ● Monitoring isout of sync with realityMonitoring is out of sync with reality ● Managed manuallyManaged manually ● Can't keep up..Can't keep up..
- 16. ChallengesChallenges ● ReproducabilityReproducability ● SpeedSpeed ●AuditingAuditing ● Keeping stuff in syncKeeping stuff in sync – MonitoringMonitoring – SecuritySecurity – BackupBackup
- 17. Facts!Facts! ● Data Backupis only a part ● Sysadmin backup needs to be done also ● Manual Installations = bad ● Bad installations = unusable infrastructure ● Bad installations = unproductive users ● Bad installations = manual efforts ● Manual efforts = no time ● No time = no updates no patches no security ● Manual work = high costs
- 18. The 10The 10thth floortestfloor test ● Grab a random machine (don’t take a backup before) ● Throw it out a 10th floor window ● Can you recover it in 10 minutes ?
- 19. Imagesprawl ANDImagesprawl AND SnowflakesSnowflakes ●Image Sprawl :Image Sprawl : – Copy vm 3xCopy vm 3x – Modify 2xModify 2x – Copy 21xCopy 21x – How the Heck did we get here ?How the Heck did we get here ? ● SnowFlakes :SnowFlakes : – Don't touch this box it might breakDon't touch this box it might break – Look how nice it is !Look how nice it is !
- 20. You never deployYounever deploy something “just” oncesomething “just” once ● Local test … experiment,Local test … experiment, – Vagrant box / local containersVagrant box / local containers ● Integration PlatformIntegration Platform – Same codebase,, different environmentSame codebase,, different environment ● Dev/ UAT/ Prod / DR …Dev/ UAT/ Prod / DR … ● Or your customer just forgot to renew the leaseOr your customer just forgot to renew the lease on his VPS. #toldyousoon his VPS. #toldyouso
- 21. What's different inthe cloud ?What's different in the cloud ? ● ScaleScale ● VelocityVelocity ● ChangeChange
- 22. Your machines asCattleYour machines as Cattle
- 23. Treat your peopleas petsTreat your people as pets
- 24. Configuration MgmtConfiguration Mgmt ●Configure 1000 nodes,Configure 1000 nodes, ● Modify 15000 files,Modify 15000 files, ● Think :Think : •Cfengine,Puppet, Chef, SaltCfengine,Puppet, Chef, Salt ● Put configs under version controlPut configs under version control ● Please don't roll your own ...Please don't roll your own ...
- 25. Infrastructure as CodeInfrastructureas Code ● Treat configuration automation as codeTreat configuration automation as code ● Development best practicesDevelopment best practices ● Model your infrastructureModel your infrastructure ● Version your cookbooks / manifestsVersion your cookbooks / manifests ● Test your cookbooks/ manifestsTest your cookbooks/ manifests ● Dev/ test /uat / prod for your infraDev/ test /uat / prod for your infra ● Model your infrastructureModel your infrastructure ● A working service = automated ( Application Code + InfrastructureA working service = automated ( Application Code + Infrastructure Code + Security + Monitoring )Code + Security + Monitoring ) ● IAC -ne scripting (or translating bash to yaml)IAC -ne scripting (or translating bash to yaml)
- 26. AuditabilityAuditability ● git loggitlog ● git blamegit blame ● Review,Review, ● authorizationauthorization
- 27.
- 28. Other benefits ofcodeOther benefits of code ● Code can be testedCode can be tested ● You can attach it to a bugtrackerYou can attach it to a bugtracker ● You can deploy, redeploy, …You can deploy, redeploy, … ● You can analyse it without getting access to theYou can analyse it without getting access to the infrainfra ● You can revert changesYou can revert changes
- 29. PuppetPuppet ● Open SourceConfig ManagementOpen Source Config Management ToolTool EcosystemEcosystem ● 10y old ; very mature10y old ; very mature ● Ruby, Clojure, Jruby, C++, ...Ruby, Clojure, Jruby, C++, ... ● Large communityLarge community ● Lots of open source modules (cose shared byLots of open source modules (cose shared by other people to automate things)other people to automate things) ● Puppet is also the name of the company behind itPuppet is also the name of the company behind it
- 30. Puppet in shortPuppetin short ● You define the desired state of your infraYou define the desired state of your infra ● Puppet looks at the current state and takesPuppet looks at the current state and takes actions to get to the final stateactions to get to the final state ● You have the report of what has beenYou have the report of what has been changed/errored/…changed/errored/… ● It is declarativeIt is declarative
- 31. How Puppet WorksHowPuppet Works ● Puppet works by default on a server/agentPuppet works by default on a server/agent modelmodel ● Agents fetch their “catalog” (= final desiredAgents fetch their “catalog” (= final desired state) from the master and apply that locallystate) from the master and apply that locally ● Then they report back to the serverThen they report back to the server ● The catalog is computed depending on “facts”The catalog is computed depending on “facts” the machine sends to the server, user inputthe machine sends to the server, user input (parameters) and modules (source code)(parameters) and modules (source code)
- 32. Puppet ModulesPuppet Modules ●Puppet modules are reusable set of PuppetPuppet modules are reusable set of Puppet manifestsmanifests ● Contains resources, templates, files, …Contains resources, templates, files, … ● e.g. Puppet-apache, puppet-postgresql,e.g. Puppet-apache, puppet-postgresql, puppet-corosyncpuppet-corosync ● Thousands of them are published on github andThousands of them are published on github and Puppet ForgePuppet Forge
- 33. Roles and ProfilesRolesand Profiles ● A profile is a puppet module that explains howA profile is a puppet module that explains how to install a serviceto install a service ● A role is a Puppet manifest that includes profilesA role is a Puppet manifest that includes profiles ● A server should get one and only one roleA server should get one and only one role class profile_mysql’ { class { '::mysql::server': } class { '::mysql::xtrabackup': } } class role::mysql_server { include profile_mysql include profile_base }
- 34. Defining a ServiceDefininga Service ● profile that :profile that : – Configures service using a standardConfigures service using a standard module call with hiera based parametersmodule call with hiera based parameters – Configures BackupConfigures Backup – Configures logrotationConfigures logrotation – Configures logshippingConfigures logshipping – Exports Monitoring NeedsExports Monitoring Needs
- 35. MySQL+PuppetMySQL+Puppet ● There usedto be:There used to be: – Puppet-perconaPuppet-percona – Puppet-mariadbPuppet-mariadb – Puppetlabs-mysqlPuppetlabs-mysql ● Now community has been built aroundNow community has been built around puppetlabs-mysql (4 millions download onpuppetlabs-mysql (4 millions download on puppet forge – 215 contributors)puppet forge – 215 contributors) ● Same story with other pieces of softwareSame story with other pieces of software (apache, ntp, postgresql, iptables...)(apache, ntp, postgresql, iptables...)
- 36. What is inthe module?What is in the module? ● Mysql server configMysql server config ● Mysql db creationMysql db creation ● Mysql users creationMysql users creation ● Root passwordRoot password ● Mysql bindings (c, java, perl, php…)Mysql bindings (c, java, perl, php…) ● BackupsBackups
- 37. Setting up mysqlSettingup mysql (or mariadb depending on distro default)(or mariadb depending on distro default) class { '::mysql::server': }
- 38. You probably wantthisYou probably want this class { '::mysql::server': service_manage => false, } And let your HA service taking care of that
- 39. OptionsOptions class { '::mysql::server': override_options =>{ 'mysqld' => { 'server-id' => $::mysql_server_id, 'bind-address' => '0.0.0.0', 'log-error' => '/var/log/mysqld.log', 'pid-file' => '/var/run/mysqld/mysqld.pid', 'innodb_file_per_table' => true, }, }, }
- 40. UsersUsers mysql_user { "backup@10.10.10.1": ensure=> 'present', password_hash => mysql_password(‘secret’), } mysql_grant { "backup@10.10.10.1/*.*": ensure => 'present', privileges => [ 'ALL' ], table => '*.*', user => "backup@10.10.10.1", }
- 41. But its Puppetlike 2011But its Puppet like 2011 ● Nowadays in infra as code you split data andNowadays in infra as code you split data and codecode ● Hiera : yaml as source of dataHiera : yaml as source of data ● Other backends than yaml availableOther backends than yaml available – VaultVault ● Merges data from env-specific, os-specific,Merges data from env-specific, os-specific, datacenter-specific key value stores...datacenter-specific key value stores...
- 42. --- mysql::server::db: "dev": user: "dev" password: "devpass" host:"127.0.0.1" grant: - "ALL" create_resources(mysql::db, hiera('mysql::server::db', {})) In Puppet: In Hiera:
- 43.
- 44. Exported resourcesExported resources ●You can export resources from nodesYou can export resources from nodes ● And fetch them in another nodeAnd fetch them in another node ● Exporting:Exporting: ● Collecting:Collecting: Mysql::Db <<| tag == “$tag” |>> @@::mysql::db { $name: user => $user, password => $password, dbname => $dbname, host => $host, grant => $grant, tag => $tag, }
- 45. Downsides &Downsides & WorkaroundsWorkarounds ●Exported resources are “slow”, puppet needsExported resources are “slow”, puppet needs to run on the app server to export resources toto run on the app server to export resources to the mysql serverthe mysql server ● Requires a puppetserver (with puppetdb)Requires a puppetserver (with puppetdb) ● Workaround 1: Accept that and make your appWorkaround 1: Accept that and make your app resilient to DB not readyresilient to DB not ready ● Workaround 2: Design your app in hiera as yamlWorkaround 2: Design your app in hiera as yaml and pick the info from there in both app serverand pick the info from there in both app server and database serverand database server
- 46. Replication userReplication user mysql_user{ 'replica@%': ensure => 'present', password_hash => mysql_password('percona17'), } mysql_grant { 'replica@%/*.*': ensure => 'present', privileges => ['REPLICATION SLAVE'], table => '*.*', user => 'slave_user@%', }
- 47. Replication OptionsReplication Options class{ '::mysql::server': override_options => { 'mysqld' => { 'server-id' => 1, 'binlog-ignore-db' => [‘mysql’, ‘performance_schema’], 'log-bin' => 'binary-log.', 'binlog-format' => 'ROW', }, }, } CHANGE MASTER TO master_host="$mysql_master", master_port=3306, master_user='replica', master_password='percona17', master_use_gtid=current_pos; START SLAVE; Chose if you want to run in a Puppet exec, a more Intelligent script, … (MariaDB based example:)
- 48. Configuring HAConfiguring HA ●Example using corosync/pacemaker (puppet-Example using corosync/pacemaker (puppet- corosync)corosync) cs_primitive { ‘p_mysql’: primitive_class => 'ocf', primitive_type => 'mysql', provided_by => 'heartbeat', Promotable => true, operations => [ { 'monitor' => { 'interval' => '10s', 'timeout' => '30s' } }, { 'monitor' => { 'interval' => '5s', 'timeout' => '30s', 'role' => 'Master', } }, ] } cs_colocation { 'mysql_master': Primitives => [ 'mysql_vip', 'p_mysql:Master' ], } cs_primitive { 'mysql_vip': primitive_class => 'ocf', primitive_type => 'IPaddr2', provided_by => 'heartbeat', Parameters => { 'ip' => '172.21.17.100', 'cidr_netmask' => '24' }, Operations => { 'monitor' => { 'interval' => '10s' } }, }
- 49. ConclusionConclusion ● IAC solvesa lot of problemsIAC solves a lot of problems – Improves SecurityImproves Security – Creates Monitoring LoveCreates Monitoring Love – Creates SpeedCreates Speed – Creates ConsistencyCreates Consistency ● But it still is code, and needs to be treated likeBut it still is code, and needs to be treated like code !code !
- 50. More of thisMoreof this @cfgmgmtcamp@cfgmgmtcamp Gent , 5 & 6 februaryGent , 5 & 6 february Right after FosdemRight after Fosdem
- 51. ContactContact Kris Buytaert kris.buytaert@inuits.euKrisBuytaert kris.buytaert@inuits.eu Julien Pivotto roidelapluie@inuits.euJulien Pivotto roidelapluie@inuits.eu Further ReadingFurther Reading @krisbuytaert@krisbuytaert http://www.krisbuytaert.be/blog/http://www.krisbuytaert.be/blog/ https://roidelapluie.behttps://roidelapluie.be https://inuits.eu/https://inuits.eu/ InuitsInuits Essensteenweg 31Essensteenweg 31 BrasschaatBrasschaat BelgiumBelgium 891.514.231891.514.231 +32 475 961221+32 475 961221