Automating DB Ops with Ansible, Chef, and Puppet

1 . Automating DB Ops with Ansible, Chef, and Puppet Tyler Duzan, Product Manager Percona 

2 .Who Am I? • My name is Tyler Duzan • Formerly an operations engineer for more than 12 years focused on security and automation • Now a Product Manager at Percona • Have used Puppet, Chef, Ansible, Saltstack, and Terraform professionally in the past 2 

3 .Basics of Automation 

4 .Why We Automate • Provides a source of actionable and testable documentation of processes • Reduces likelihood of human error • Expands the scale and capability of human engineers • Reduces time to deploy and time to recover • Saves money in the long term • Improves compliance with organizational policies • Reproducibility of environments 4 

5 .When Should You Consider Automating a Task? • Repetition • Task needs to be done many times exactly the same way or with minimal variation • Any variation can be templated, or follows a pattern • Good rule: If you’ve manually done something 3 times, after the 3rd time you should consider automating that task • Compliance • Task needs to be done exactly a certain way to comply with organizational policies • Part of a process pipeline where future tasks depend on the way this task is completed • Error Reduction • Is critical to production environments 5 

6 .Some DB Tasks Worth Automating • Initial Server / Cluster Install • User / Role Management • Backups • Restore • Compliance Tasks • Server Configuration Changes 6 

7 .Security and Compliance with Automation • Automation simplifies deploying centralization within your infrastructure • Centralized logging and log shipping • Centralized user management and single-sign on for servers • Can enforce approved configuration which is policy-compliant • Provides a way to version configuration and provide controls • Change Management processes can integrate easily with automation • Handle complex/error-prone security configuration, such as custom SELinux policies 7 

8 .Types of Automation Tools • Infrastructure Automation • Cloud-Focused - Terraform - Cloud Formation - SaltCloud • Physical-Focused - Foreman - Open Crowbar - Cobbler • Container-Focused - Kubernetes Operators - DC/OS Data Frameworks 8 

9 .Types of Automation Tools • Configuration Management • Agent-Based - Chef - Puppet - SaltStack • Remote Execution - Ansible - Capistrano - Fabric 9 

10 .Types of Automation Tools • Automation Language Choice • Domain Specific Language (DSL) - Terraform - Cloud Formation - Puppet - Ansible • General Purpose Programming Language - Chef - SaltStack - Capistrano - Fabric 10 

11 .What Tool is Right for You or Your Team? • Importance of tooling • Linters, pipeline intregration, CI, how do you test your automation • Testability • Language Familiarity • Developer Focused vs Operations Focused • Team Experience with writing automation 11 

12 .Tool Overview and Examples 

13 .Ansible • Agentless, remote execution, DSL-based automation • Uses SSH to execute playbooks against nodes • Actions are executed from a controller which maintains an inventory of playbooks, modules, and nodes • Ansible playbooks are effectively like shell scripts. They run in sequential order, just like a shell script, and can take any action that could be otherwise run on the target node via ssh remote commands. • Playbooks can be idempotent, but are not guaranteed to be idempotent • Ansible and its modules are written in Python 13 

14 .Ansible: Setup MySQL Slave Example Adapted from https://github.com/ensibel/setup-mysql-slave/blob/master/main.yml --- To execute the playbook, assuming you have defined mysql_replication_user and - hosts: "{{ slave }}" mysql_replication_password in your host_vars file tasks: you can run just the following: - name: configure MySQL slave process mysql_replication: ansible-playbook main.yml -e master_host: "{{ mysql_replication_master | default(master) }}" 'master=master.example.com master_user: "{{ mysql_replication_user }}" master_password: "{{ mysql_replication_password }}" slave=slave.example.com' master_log_file: "{{ binlog_file.stdout }}" master_log_pos: "{{ binlog_position.stdout }}" mode: changemaster - name: start MySQL slave process mysql_replication: mode: startslave 14 

15 .Ansible: MySQL User Example Adapted from official Ansible examples To execute this playbook, you can run the --- following: - hosts: all ansible-playbook -i example remote_user: root user.yml tasks: - name: Create MySQL Database User mysql_user: user=example password=12345 priv=*.*:ALL state=present - name: Ensure no user named "example2" exists and delete if it does mysql_user: user=example2 state=absent 15 

16 .Chef • Agent-based, local execution, pure Ruby automation • Uses a local agent on each node to execute a collection of cookbooks containing recipes • Actions are defined in a series of cookbooks which are typically structured as Ruby gems which can be installed and managed using normal Ruby processes and tools, such as Berkshelf or Bundler • Cookbooks contain a collection of recipes, attributes, resources, and templates, can include libraries, can depend upon Ruby gems, and also can optionally include tests • Cookbooks are always idempotent and declarative, agents rerun cookbooks regularly to ensure that a given node matches its declared environment configuration • Chef is written in Ruby and Erlang, Chef cookbooks are written in Ruby 16 

17 .Chef: How the Chef-Client Works 1. Register and authenticate the node with the Chef server 2. Build the node object 3. Synchronize cookbooks and the node run list 4. Compile the resources and execute the first pass (‘compile’) 5. Converge and execute the second pass (‘converge’) 6. Handle any exceptions and report the status to the Chef server 17 

18 .Chef: Setup MySQL Server Example Adapted from example in the documentation of the ‘mysql’ community cookbook # Depends on `mysql` community cookbook yum_repository 'mysql57-community' do mysql_service 'default' do mirrorlist version '5.7' 'http://repo.mysql.com/yum/mysql-5.7- bind_address '0.0.0.0' community/el/$releasever/$basearch/' port '3306' description 'MySQL 5.7 Community Edition' data_dir '/var/lib/mysql' enabled true initial_root_password 'Ch4ng3me' gpgcheck true action [:create, :start] end end 18 

19 .Chef: MySQL User Example Adapted from old Chef cookbook chef_gem 'mysql-grantee' # Retrieve mysql application data bag items mysql_apps = search(node['mysql']['users']['d ata_bag']) # Apply permissions ruby_block 'Configure MySQL Users' do block { mysql_apply_grants(self, mysql_apps) } action :run end 19 

20 .Puppet • Agent-based, local execution, DSL-based automation • Uses a local agent on each node to execute a collection of manifests containing declarative configuration • Actions are defined in a series of manifest, which are structured as a collection of resource abstractions defined in Puppet’s DSL • Manifests are idempotent and declarative and abstracted away from platform specifics. Agents apply manifests in a transactional way. • Agents run as a daemon and regularly reapply manifests to ensure the node matches the declared environment configuration • Puppet is written in C++, Clojure, and Ruby 20 

21 .Puppet: Agent Transaction Details 1. An agent send facts from Facter to the master. 2. Puppet builds a graph of the list of resources and their interdependencies, representing the order in which they need to be configured, for every client. The master sends the appropriate catalog to each agent node. 3. The actual state of the system is then configured according to the desired state described in manifest file. If the system is already in the desired state, Puppet will not make any changes, making transactions idempotent. 4. Finally, the agent sends a report to the master, detailing what changes were made and any errors that occurred. 21 

22 .Puppet: Setup Percona Server Example Adapted from official MySQL Puppet module include '::mysql::server' $override_options = { 'mysqld' => { yumrepo { 'percona': 'data_dir' => '/var/lib/mysql', descr => 'CentOS $releasever - 'bind_address' => '0.0.0.0', Percona', 'port' => '3306' } baseurl => 'http://repo.percona.com/centos/$relea } sever/os/$basearch/', gpgkey => 'http://www.percona.com/downloads/perc ona-release/RPM-GPG-KEY-percona', enabled => 1, gpgcheck => 1, } 22 

23 .Puppet: Setup Percona Server Example class { '::mysql::server': package_name => 'Percona-Server-server-57', package_ensure => '5.7.23-23.1.el7', service_name => 'mysql', config_file => '/etc/my.cnf', root_password => 'Ch4ng3me', remove_default_accounts => true, override_options => $override_options } 23 

24 .Terraform • Has its own specific DSL called Terraform HCL, which is used to create an execution plan • Interacts with provider APIs to define infrastructure as code • Terraform configuration is idempotent and declarative • Region abstracted to allow easily reproducible infrastructure • Terraform configuration consists of providers, modules, resources, variables, and attributes. • Terraform is written in Go 24 

25 .Terraform: Deploy AWS RDS and Aurora # AWS provider resource "aws_rds_cluster" "default" { cluster_identifier = "my-test- provider "aws" { aurora" region = "us-west-2" engine = "aurora-mysql" } availability_zones = ["us-west-2a", "us-west-2b", "us-west-2c"] database_name = "testdb" data "aws_db_instance" "database" master_username = "testuser" { master_password = "Ch4ng3me" db_instance_identifier = "my- backup_retention_period = 5 test-database" preferred_backup_window = "07:00- 09:00" } } 25 

26 .Testing Your Automation 

27 .Kitchen CI • Provides an easy way to automate local or remote integration testing of your automation code • Supports drivers for docker, vagrant, aws, and other methods for deploying test systems • Uses chef-zero to compile and converge your cookbooks on the local VMs • Provides a method to do TDD with your automation • Uses ServerSpec + Ruby for running tests • Kitchen configuration is declared as YAML • Ships with ChefDK, can be extended to support Ansible and Puppet 27 

28 .Kitchen CI: Test PostgreSQL Example Adapted from the Kitchen CI Website Example --- suites: - name: client driver: run_list: name: vagrant - recipe[postgresql::client] provisioner: - name: server run_list: name: chef_zero - recipe[postgresql::server] platforms: - name: ubuntu-14.04 - name: windows-2012r2 28 

29 .ServerSpec & InSpec • Extends RSpec test framework to support integration testing of infrastructure • Provides resources which map to physical infrastructure attributes • Tests are written in simplified English DSL which defines what the expected outcome of running some automation is, and ServerSpec checks to see if it matches those expectations • Chef has created an extended version of ServerSpec with many improvements called InSpec that is to migrate to if you choose. • Both are included/integrated with Kitchen CI 29