Database Operations at Groupon using Ansible Mani Subramanian Sr. - - PowerPoint PPT Presentation

▶
database operations at groupon using ansible
SMART_READER_LITE
LIVE PREVIEW

Database Operations at Groupon using Ansible Mani Subramanian Sr. - - PowerPoint PPT Presentation

Mar 27, 2024 985 likes •1.3k views

Database Operations at Groupon using Ansible Mani Subramanian Sr. Manager Global Database Services Groupon manidba@groupon.com About me Worked as an Oracle DBA for 15+ years Branched out to MySQL since 2011 Branched out to

slide-1
SLIDE 1

Mani Subramanian

  • Sr. Manager

Global Database Services Groupon manidba@groupon.com

Database Operations at Groupon using Ansible

slide-2
SLIDE 2
  • Worked as an Oracle DBA for 15+ years
  • Branched out to MySQL since 2011
  • Branched out to PostgreSQL since 2014
  • Now - Managing Global Databases Services in Groupon

About me

slide-3
SLIDE 3
  • Supports Production Operations
  • Managing databases at scale both MySQL and PostgreSQL
  • DaaS (Database as Service)
  • Develop Tools and scripts for internal purpose
  • Teams worldwide.

Global Database Services

slide-4
SLIDE 4
  • Provisioning new MySQL DB instances (Demo)
  • Setting up MySQL replication (Demo)
  • Provisioning new PostgreSQL DB instances (Demo)
  • Setting up Streaming replication (Demo)
  • Creating new users and grants.
  • Database failover using MHA (Demo)
  • Destroy DB instances.
  • Backup
  • Validate the Backup restore
  • Future potential developments

Agenda

slide-5
SLIDE 5
  • FreeBSD Operating System
  • ZFS FileSystem
  • MySQL - Percona 5.6
  • PostgreSQL - 9.4
  • CARP - Common Address Redundancy Protocol (VIP)
  • Ansible 2.4
  • Runit
  • MHA for MySQL failover
  • ZFS snapshots (FS level consistent backup)
  • Xtrabackup (MySQL)
  • Pg_basebackup(PostgreSQL)

Environment

slide-6
SLIDE 6
  • What is Ansible?

○ Radically simple IT automation engine.

  • Why Ansible?

○ Automate tasks (sequence of tasks) ○ Save time and be more productive ○ Reduce mistakes or errors.

  • How does it work?

○ SSH Keys are your friends ○ No additional agents ○ Uses simple language (YAML in the form of Ansible playbooks)

Ansible

slide-7
SLIDE 7
slide-8
SLIDE 8

Ansible

slide-9
SLIDE 9
slide-10
SLIDE 10
  • Roles

○ Tasks ○ Handlers ○ Defaults ○ Vars ○ Files ○ Templates ○ Meta Documentation : http://docs.ansible.com/ansible/latest/user_guide/

Simple Ansible Terms

slide-11
SLIDE 11

[gds_snc1_test_db001] gds-snc1-test-db001m1.snc1 gds-snc1-test-db001s1.snc1 [gds_snc1_test_db002] gds-snc1-test-db002m1.snc1 gds-snc1-test-db002s1.snc1 [gds_snc1_test_db003] gds-snc1-test-db003m1.snc1 gds-snc1-test-db003s1.snc1

Ansible inventory file

slide-12
SLIDE 12

Ansible usage - Example1

slide-13
SLIDE 13

Ansible usage - Example2

slide-14
SLIDE 14

Ansible usage - Example3

slide-15
SLIDE 15

Ansible usage - Example4

slide-16
SLIDE 16

ansible-playbook plays/mysql/create-instance.yml -e "node=gds-snc1-test- db001m1.snc1 instance=gds_sandbox_demo inst_type=master" <<< DEMO >>> ansible-playbook plays/mysql/create-instance.yml -e "node=gds-snc1-test- db001s1.snc1 instance=gds_sandbox_demo inst_type=slave" <<< DEMO >>>

DB Instances Provisioning

slide-17
SLIDE 17

YAML file on Instance Provisioning

slide-18
SLIDE 18

plays/mysql/create-instance.yml

slide-19
SLIDE 19

check-params.yml

slide-20
SLIDE 20

Configure MHA ansible-playbook plays/mysql/create-admin.yml -e "node=gds-snc1-test- db001m1.snc1 instance=gds_sandbox_demo" ansible-playbook plays/mysql/create-admin.yml -e "node=gds-snc1-test- db001s1.snc1 instance=gds_sandbox_demo" <<< DEMO >>>

Configuring MHA using Ansible playbook

slide-21
SLIDE 21

ansible-playbook plays/mysql/clone-instance.yml -e "master_server=gds-snc1- test-db001m1.snc1 slave_server=gds-snc1-test-db001s1.snc1 instance=gds_sandbox_demo" <<< DEMO >>>

Cloning the Slave box(es)

slide-22
SLIDE 22

ansible-playbook plays/mysql/destroy-instance.yml -e "node=gds-snc1-test- db001s1.snc1 instance=gds_sandbox_demo" <<< DEMO >>> It does

  • Checks for connections
  • Removes monitoring
  • Stops the service
  • Deletes instances
  • Removes snapshots
  • Removes ZFS filesystem

Destroying DB instances

slide-23
SLIDE 23

It is a life saver in multiple scenarios like

  • When User drops tables/wipes data mistakenly.
  • Rollback faster on any planned data changes.
  • Repeated load test on same data set is possible.
  • Shipping ZFS filesystem to different box/data center for Research/Recovery

purpose. << Demo >>

ZFS Snapshots

slide-24
SLIDE 24

sandbox-ro-vip.snc1-J1: host: sandbox-ro-vip.snc1 instance_name:

  • sandbox_demo

pool: us retention: 31d-1m-2y target: mysql template: daas_mysql_v2 zfs_fs: snc1-prod-sandbox

Yaml file to schedule backup

slide-25
SLIDE 25

Run the key generation play: ansible-playbook -i inventory/percona-demo plays/test-gen-ssh-keys.yml Run the key installation play: ansible-playbook -i inventory/percona-demo plays/test-install-keys.yml Installing Backup Jobs: ansible-playbook -i inventory/percona-demo plays/test-install-jobs.yml

Scheduling backups

slide-26
SLIDE 26
  • Ansible play creates instance and also pushes monitoring scripts to the

hosts.

  • Check-Mk agent executes to collect data for monitoring alerts
  • More details http://mathias-kettner.com/check_mk.html

Monitoring

slide-27
SLIDE 27
  • Backfill and bulk DML using ansible
  • Operations using CMDB
  • Self service on DB instance provisioning and whitelisting app servers
  • Schema change using ansible

Future potential development

slide-28
SLIDE 28
slide-29
SLIDE 29