Rajiv's picture

Deploy Mysql And Mariadb Replication Using Puppet​

Puppet is an open source system management tool to centralize and automate configuration. Puppet helps to minimize the manual and repetitive tasks; thus, it saves a great deal of time.Puppet by default works in any server/agent model. The agents fetch the final desired date, i.e., “catalog” from the master to apply it locally. Once this happens, they report back to the server.The catalog computation is done depending upon the “facts” sent to the server, user inputs (parameters), and the modules (source code) by the machine.

In this blog, we’ll show "how to deploy and manage MySQL/MariaDB instances using Puppet?"

 

There are many technologies around MySQL/MariaDB like replication (master-slave, group replication for MySQL group or Galera), SQL-aware load balancers such as ProxySQL and MariaDB MaxScale, recovery tools & backup tools, and more.

 

To simplify the code and avoid reinventing the wheel, the Puppet Forge built and managed by the community provides several modules to help us. In this blog, I am going to focus on MySQL Replication.

Puppetlabs/mysql

It is the most popular and best in market Puppet module for MySQL and MariaDB. The module manages both, installation and configuration of MySQL, as well as extend Puppet to further allow the management of MySQL resources, like databases, users, and grants.

 

Puppet module officially maintained by the puppetlabs Github repository supports all major versions of Puppet Enterprise on RedHat, Ubuntu, SLES, Debian, Scientific, OracleLinux platforms, CentOS, etc.

 

A user has options to install MySQL, Percona Server, and MariaDB by customizing the package repository

 

Does the below example show how to deploy a MySQL server? On the puppet master, first install the MySQL module, then create the manifest file:

(puppet-master)$ puppet module install puppetlabs/mysql

(puppet-master)$ vim /etc/puppetlabs/code/environments/production/manifests/mysql.pp

 

Add these lines:

node "db1.local" {

  class { '::mysql::server':

    root_password => 't5[sb^D[+rt8bBYu',

    remove_default_accounts => true,

    override_options => {

      'mysqld' => {

        'log_error' => '/var/log/mysql.log',

        'innodb_buffer_pool_size' => '512M'

      }

      'mysqld_safe' => {

        'log_error' => '/var/log/mysql.log'

      }

    }

  }

}

 

Now on the Puppet agent node, run this command to apply configuration catalog:

(db1.local)$ puppet agent -t

 

On the first run, you can get this error:

Info: Certificate for db1.local has not been signed yet

 

Run these command on Puppet master to sign certificate:

(puppet-master)$ puppetserver ca sign --certname=db1.local

Successfully signed certificate request for db1.local

 

Again retry with "puppet agent -t" command and re-initiate the connection with that signed certificate.

 

The above definition installs the standard MySQL-related packages available in the distribution repository of Operating System. For example, on the Ubuntu 18.04 (Bionic), you get installed MySQL 5.7.26 packages:

(db1.local) $ dpkg --list | grep -i mysql

ii  mysql-client-5.7                5.7.26-0ubuntu0.18.04.1         amd64          MySQL database client binaries

ii  mysql-client-core-5.7        5.7.26-0ubuntu0.18.04.1         amd64          MySQL database core client binaries

ii  mysql-common                 5.8+1.0.4                                  all                MySQL database common files, e.g. /etc/mysql/my.cnf

ii  mysql-server                     5.7.26-0ubuntu0.18.04.1          all                MySQL database server (metapackage depending on the latest version)

ii  mysql-server-5.7               5.7.26-0ubuntu0.18.04.1          amd64         MySQL database server binaries and system database setup

ii  mysql-server-core-5.7       5.7.26-0ubuntu0.18.04.1          amd64         MySQL database server binaries

 

You may opt for other vendors like Oracle, MariaDB or Percona with some extra configuration on the repository. The following definition installs the MariaDB packages from the MariaDB apt repository:

$ puppet module install puppetlabs/apt

$ vim /etc/puppetlabs/code/environments/production/manifests/mariadb.pp

# include puppetlabs/apt module

include apt

 

# apt definition for MariaDB 10.3

apt::source { 'mariadb':

  location => 'http://sgp1.mirrors.digitalocean.com/mariadb/repo/10.3/ubuntu/',

  release  => $::lsbdistcodename,

  repos    => 'main',

  key      => {

    id     => 'A6E773A1812E4B8FD94024AAC0F47944DE8F6914',

    server => 'hkp://keyserver.ubuntu.com:80',

  },

  include => {

    src   => false,

    deb   => true,

  },

}

 

# MariaDB configuration

class {'::mysql::server':

  package_name     => 'mariadb-server',

  service_name     => 'mysql',

  root_password    => 't5[sb^D[+rt8bBYu',

  override_options => {

    mysqld => {

      'log-error' => '/var/log/mysql/mariadb.log',

      'pid-file'  => '/var/run/mysqld/mysqld.pid',

    },

    mysqld_safe => {

      'log-error' => '/var/log/mysql/mariadb.log',

    },

  }

}

 

# Deploy on db2.local

node "db2.local" {

Apt::Source['mariadb'] ->

Class['apt::update'] ->

Class['::mysql::server']

}

 

Take note on the key->id value, where there is a special way to retrieve the 40-character id as shown in this article:

$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

$ apt-key adv --list-public-keys --with-fingerprint --with-colons

uid:-::::1459359915::6DC53DD92B7A8C298D5E54F950371E2B8950D2F2::MariaDB Signing Key <signing-key@mariadb.org>::::::::::0:

sub:-:4096:1:C0F47944DE8F6914:1459359915::::::e::::::23:

fpr:::::::::A6E773A1812E4B8FD94024AAC0F47944DE8F6914:

 

Where the id value in the line starting with "fpr" is 'A6E773A1812E4B8FD94024AAC0F47944DE8F6914'.

After the Puppet catalog is applied, you will be able to directly access MySQL console as root without an explicit password because the module manages & configures ~/.my.cnf automatically.

If we want to reset the root password, change the root_password value in the Puppet definition, and then apply the catalog on the agent node.

 

Deployment of MySQL Replication

To deploy MySQL Replication setup, create at least two types of configuration to separate both, master and slave configuration. The master configuration has read-only disabled (to allow read/write) while the slave configuration configures with read-only enabled.

 

In this example, we will use GTID-based replication to simplify the configuration because all nodes' configuration are very similar. We want to initiate a replication link to the master right, but after the slave is up.

 

Suppose we have 3 nodes MySQL master-slave replication:

  • db1.local - master
  • db2.local - slave #1
  • db3.local - slave #2

 

To meet what we said above, we can write something like this:

# Puppet manifest for MySQL GTID-based replication MySQL 5.7 on Ubuntu 18.04 (Puppet v6.4.2)

# /etc/puppetlabs/code/environments/production/manifests/replication.pp

 

# node's configuration

class mysql {

  class {'::mysql::server':

    root_password           => 'q1w2e3!@#',

    create_root_my_cnf      => true,

    remove_default_accounts => true,

    manage_config_file      => true,

    override_options        => {

      'mysqld' => {

        'datadir'                 => '/var/lib/mysql',

        'bind_address'            => '0.0.0.0',

        'server-id'               => $mysql_server_id,

        'read_only'               => $mysql_read_only,

        'gtid-mode'               => 'ON',

        'enforce_gtid_consistency'=> 'ON',

        'log-slave-updates'       => 'ON',

        'sync_binlog'             => 1,

        'log-bin'                 => '/var/log/mysql-bin',

        'read_only'               => 'OFF',

        'binlog-format'           => 'ROW',

        'log-error'               => '/var/log/mysql/error.log',

        'report_host'             => ${fqdn},

        'innodb_buffer_pool_size' => '512M'

      },

      'mysqld_safe' => {

        'log-error'               => '/var/log/mysql/error.log'

      }

    }

  }

  

  # create slave user

  mysql_user { "${slave_user}@192.168.0.%":

      ensure        => 'present',

      password_hash => mysql_password("${slave_password}")

  }

 

  # grant privileges for slave user

  mysql_grant { "${slave_user}@192.168.0.%/*.*":

      ensure        => 'present',

      privileges    => ['REPLICATION SLAVE'],

      table         => '*.*',

      user          => "${slave_user}@192.168.0.%"

  }

 

  # /etc/hosts definition

  host {

    'db1.local': ip => '192.168.0.161';

    'db2.local': ip => '192.169.0.162';

    'db3.local': ip => '192.168.0.163';

  }

 

  # executes change master only if $master_host is defined

  if $master_host {

    exec { 'change master':

      path    => '/usr/bin:/usr/sbin:/bin',

      command => "mysql --defaults-extra-file=/root/.my.cnf -e \"CHANGE MASTER TO MASTER_HOST = '$master_host', MASTER_USER = '$slave_user', MASTER_PASSWORD = '$slave_password', MASTER_AUTO_POSITION = 1; START SLAVE;\"",

      unless  => "mysql --defaults-extra-file=/root/.my.cnf -e 'SHOW SLAVE STATUS\G' | grep 'Slave_SQL_Running: Yes'"

    }

  }

}

 

## node assignment

 

# global vars

$master_host = undef

$slave_user = 'slave'

$slave_password = 'Replicas123'

 

# master

node "db1.local" {

  $mysql_server_id = '1'

  $mysql_read_only = 'OFF'

  include mysql

}

 

# slave1

node "db2.local" {

  $mysql_server_id = '2'

  $mysql_read_only = 'ON'

  $master_host = 'db1.local'

  include mysql

}

 

# slave2

node "db3.local" {

  $mysql_server_id = '3'

  $mysql_read_only = 'ON'

  $master_host = 'db1.local'

  include mysql

}

 

Now, force the agent to apply catalog:

(all-mysql-nodes)$ puppet agent -t

 

Now, on the master (db1.local), verify all the connected slaves:

mysql> SHOW SLAVE HOSTS;

+-----------+-----------+------+-----------+--------------------------------------+

| Server_id | Host      | Port | Master_id | Slave_UUID                           |

+-----------+-----------+------+-----------+--------------------------------------+

|         3 | db3.local | 3306 |         1 | 2d0b14b6-8174-11e9-8bac-0273c38be33b |

|         2 | db2.local | 3306 |         1 | a9dfa4c7-8172-11e9-8000-0273c38be33b |

+-----------+-----------+------+-----------+--------------------------------------+

 

 

Pay extra bit of attention to the exec 'change master' :" section, where a MySQL command will execute to initiate replication link if a condition is met. All "exec" resources which are executed by Puppet must be idempotent, i.e., operations having same effect whether to run it once or 10,001 times. There are a number of conditional attributes which you may use like "unless", "onlyif" and "create." This would safeguard the correct state and prevent the Puppet from messing up with your setup. Also, you may delete/comment that section where you want to initiate the manual replication link.

MySQL Management

This module performs a number of MySQL management tasks such as:

  • configuration options (modify, apply, custom configuration)
  • database resources (database, user, grants)
  • backup (create, schedule, backup user, storage)
  • simple restore (mysqldump only)
  • plugins installation/activation

Database Resource

As you can see, above we defined two different MySQL resources - mysql_user and mysql_grant - for creating user and granting privileges for the user respectively. Also, we ca use mysql::db class to ensure a database with associated users. For example:

# make sure the database and user exist with proper grant

mysql::db { 'mynewdb':

  user          => 'mynewuser',

  password      => 'passw0rd',

  host          => '192.168.0.%',

  grant         => ['SELECT', 'UPDATE']

}

 

Note: In MySQL replication, all writes are performed on the master. Thus, make sure that the above mentioned resource gets assigned to the master. Or else, errant transaction could occur.

Backup and Restore

Most of the times, one backup host is required for the entire cluster unless a subset of data is replicated. We use the mysql::server::backup class to prepare backup resources. Suppose we have following declaration in our manifest:

# Prepare the backup script, /usr/local/sbin/mysqlbackup.sh

class { 'mysql::server::backup':

  backupuser     => 'backup',

  backuppassword => 'passw0rd',

  backupdir      => '/home/backup',

  backupdirowner => 'mysql',

  backupdirgroup => 'mysql',

  backupdirmode  => '755',

  backuprotate   => 15,

  time           => ['23','30'],   #backup starts at 11:30PM everyday

  include_routines  => true,

  include_triggers  => true,

  ignore_events     => false,

  maxallowedpacket  => '64M',

  optional_args     => ['--set-gtid-purged=OFF'] #extra argument if GTID is enabled

}

 

Puppet configures all prerequisites before running backup, creating backup user, preparing destination path, assigning ownership and permission, setting cron job and setting the backup command options, etc. to use in the provided backup script which is located at /usr/local/sbin/mysqlbackup.sh.

 

It's then up to the user either to run or schedule the script. In order to make an immediate backup, simply invoke:

$ mysqlbackup.sh

 

If we extract an actual mysqldump command, here is what it looks like:

$ mysqldump --defaults-extra-file=/tmp/backup.NYg0TR --opt --flush-logs --single-transaction --events --set-gtid-purged=OFF --all-databases

 

For those who wish to use other backup tools like  MariaDB Backup (MariaDB only), MySQL Enterprise Backup, or Percona Xtrabackup, then module provides following private classes:

  • mysql::backup::xtrabackup (Percona Xtrabackup and MariaDB Backup)
  • mysql::backup::mysqlbackup (MySQL Enterprise Backup)

 

Let's see an example of declaration with Percona Xtrabackup:

class { 'mysql::backup::xtrabackup':

  xtrabackup_package_name => 'percona-xtrabackup',

  backupuser     => 'xtrabackup',

  backuppassword => 'passw0rd',

  backupdir      => '/home/xtrabackup',

  backupdirowner => 'mysql',

  backupdirgroup => 'mysql',

  backupdirmode  => '755',

  backupcompress => true,

  backuprotate   => 15,

  include_routines  => true,

  time              => ['23','30'], #backup starts at 11:30PM

  include_triggers  => true,

  maxallowedpacket  => '64M',

  incremental_backups => true

}

 

This will schedule two backups, one full backup on every Sunday at 11:30 PM and other one as an incremental backup every day at the same time but not on Sunday. cron job output after the above manifest is applied as follows:

(db1.local)$ crontab -l

# Puppet Name: xtrabackup-weekly

30 23 * * 0 /usr/local/sbin/xtrabackup.sh --target-dir=/home/backup/mysql/xtrabackup --backup

# Puppet Name: xtrabackup-daily

30 23 * * 1-6 /usr/local/sbin/xtrabackup.sh --incremental-basedir=/home/backup/mysql/xtrabackup --target-dir=/home/backup/mysql/xtrabackup/`date +%F_%H-%M-%S` --backup

 

For more details and options for this and other classes, check out the reference here.

 

For the restoration aspect, the module supports restoration with mysqldump backup method after by importing the SQL file directly to the database using this mysql::db class, for example:

mysql::db { 'mydb':

  user     => 'myuser',

  password => 'mypass',

  host     => 'localhost',

  grant    => ['ALL PRIVILEGES'],

  sql      => '/home/backup/mysql/mydb/backup.gz',

  import_cat_cmd => 'zcat',

  import_timeout => 900

}

 

The SQL file loads once, but not on every run, unless the enforce_sql => true is used.

Configuration Options

Here in this example, we used manage_config_file => true with override_options to structure configuration lines, which later is pushed out by Puppet. Any modification in the manifest file reflects the content of the target MySQL configuration file. This module neither loads the configuration into runtime nor restart the MySQL service after pushing the changes into the configuration files.

 

To add custom MySQL configuration, place additional files into "includedir", default to /etc/mysql/conf.d. It allows you to override the settings and add additional ones, which are helpful if you don't use override_options in the mysql::server class. Making use of the Puppet template is highly recommended. Place the custom configuration file in the module template directory (default to , /etc/puppetlabs/code/environments/production/modules/mysql/templates) and add the below lines in the manifest:

# Loads /etc/puppetlabs/code/environments/production/modules/mysql/templates/my-custom-config.cnf.erb into /etc/mysql/conf.d/my-custom-config.cnf

 

file { '/etc/mysql/conf.d/my-custom-config.cnf':

  ensure  => file,

  content => template('mysql/my-custom-config.cnf.erb')

}

 

To implement version specific parameters, use this version directive, [mysqld-5.5] as it allows one config for the different versions of MySQL.

 

Stay tuned and let's know about your experience!

 

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.