我们将使用Puppet安装MySQL并配置Master/Master复制。

本文是KVM，Katelo和Puppet系列测试环境项目的一部分。

测试环境

我们安装了两个要配置的CentOS 7服务器，如下所示：

db1.hl.local（10.11.1.17）–将配置为MySQL主数据库
db2.hl.local（10.11.1.18）–将配置为MySQL主数据库

SELinux设置为强制模式。

用Puppet配置

Puppet Master 在Katello服务器上运行。

Puppet模块

我们使用puppetlabs-mysql Puppet模块来配置服务器。

请参阅模块文档以获取支持的功能和可用的配置选项。

Katello存储库

MySQL存储库由Katello提供（我们在此处配置了它们）。

配置防火墙

必须确保MySQL服务器可以相互通信。
以下要求适用于两个MySQL主服务器：

firewall { '007 allow MySQL':
  dport  => [3306],
  source => '10.11.1.0/24',
  proto  => tcp,
  action => accept,
}

这也将允许Apache连接到数据库。

在db1.hl.local上配置MySQL Master

此处确实没有什么突破性的内容，但请注意自动递增偏移量。
这有助于防止以下情况：两个查询同时在两个服务器db1和db2的同一数据库和同一表中插入数据，并且不同的条目以相同的id结 tail。

class { 'mysql::server':
  package_name       => 'mysql-community-server',
  service_name       => 'mysqld',
  root_password      => 'PleaseChangeMe',
  create_root_my_cnf => true,
  manage_config_file => true,
  config_file        => '/etc/my.cnf',
  purge_conf_dir     => true,
  restart            => true,
  override_options   => {
    mysqld => {
      bind-address     => '0.0.0.0',
      datadir          => '/var/lib/mysql',
      log-error        => '/var/log/mysqld.log',
      pid-file         => '/var/run/mysqld/mysqld.pid',
      wait_timeout     => '600',
      interactive_timeout => '600',
      server-id        => '1',
      log-bin          => 'mysql-bin',
      relay-log        => 'mysql-relay-log',
      auto-increment-offset    => '1',
      auto-increment-increment => '2',
    },
    mysqld_safe => {
      log-error => '/var/log/mysqld.log',
    },
  },
  remove_default_accounts => true,
}->
## MySQL admin user who can connect remotely
mysql_user { 'Hyman@theitroad%':
  ensure        => 'present',
  password_hash => mysql_password('PleaseChangeMe'),
}->
mysql_grant { 'Hyman@theitroad%/*.*':
  ensure     => 'present',
  options    => ['GRANT'],
  privileges => ['ALL'],
  table      => '*.*',
  user       => 'Hyman@theitroad%',
}->
## MySQL user for replication
mysql_user { 'Hyman@theitroad%':
  ensure        => 'present',
  password_hash => mysql_password('PleaseChangeMe'),
}->
mysql_grant { 'Hyman@theitroad%/*.*':
  ensure     => 'present',
  privileges => ['REPLICATION SLAVE'],
  table      => '*.*',
  user       => 'Hyman@theitroad%',
}

在db2.hl.local上配置MySQL Master

第二台服务器的配置几乎与第一台服务器相同，但有两个例外：server-id和auto-increment-offset。

class { 'mysql::server':
  package_name       => 'mysql-community-server',
  service_name       => 'mysqld',
  root_password      => 'PleaseChangeMe',
  create_root_my_cnf => true,
  manage_config_file => true,
  config_file        => '/etc/my.cnf',
  purge_conf_dir     => true,
  restart            => true,
  override_options   => {
    mysqld => {
      bind-address     => '0.0.0.0',
      datadir          => '/var/lib/mysql',
      log-error        => '/var/log/mysqld.log',
      pid-file         => '/var/run/mysqld/mysqld.pid',
      wait_timeout     => '600',
      interactive_timeout => '600',
      server-id        => '2',
      log-bin          => 'mysql-bin',
      relay-log        => 'mysql-relay-log',
      auto-increment-offset    => '2',
      auto-increment-increment => '2',
    },
    mysqld_safe => {
      log-error => '/var/log/mysqld.log',
    },
  },
  remove_default_accounts => true,
}->
## MySQL admin user who can connect remotely
mysql_user { 'Hyman@theitroad%':
  ensure        => 'present',
  password_hash => mysql_password('PleaseChangeMe'),
}->
mysql_grant { 'Hyman@theitroad%/*.*':
  ensure     => 'present',
  options    => ['GRANT'],
  privileges => ['ALL'],
  table      => '*.*',
  user       => 'Hyman@theitroad%',
}->
## MySQL user for replication
mysql_user { 'Hyman@theitroad%':
  ensure        => 'present',
  password_hash => mysql_password('PleaseChangeMe'),
}->
mysql_grant { 'Hyman@theitroad%/*.*':
  ensure     => 'present',
  privileges => ['REPLICATION SLAVE'],
  table      => '*.*',
  user       => 'Hyman@theitroad%',
}

配置主/主复制

简单的部分已经完成，我们应该在此阶段配置我们的MySQL节点。

我们还没有创建任何数据库，因此，目前我们没有太多要在两个服务器之间同步的信息。

让我们继续并将手动配置MySQL复制所需的步骤放入Bash脚本'start_mysql_repl.sh'中。
请注意，该脚本是使MySQL复制正常工作的一种快速而肮脏的方法，但这不是正确的方法。

理想情况下，我们应该使用带有参数的Puppet模板，以便我们可以通过将参数哈希传递给函数来为其提供值，而不必对主机名，用户名等进行硬编码。

#!/bin/bash
## Author: igi at www.theitroad.com
# Configure MySQL Replication with Puppet
## Variables below must match with the ones 
# defined in the Puppet manifest
#master1_host="db1.hl.local";
master2_host="db2.hl.local";
repl_user="dbrepl";
repl_pass="PleaseChangeMe";
db_user="dbadmin";
db_pass="PleaseChangeMe";
master1_status="/tmp/master1.status";
master2_status="/tmp/master2.status";
if ! [ -f "/root/.replication1.done" ];then
  mysql -h"$master1_host" -u"$db_user" -p"$db_pass" -ANe "SHOW MASTER STATUS;"|awk '{print  " " }' >"$master1_status" && \
  log_file=$(cut -d" " -f1 "$master1_status") && \
  log_pos=$(cut -d" " -f2 "$master1_status") && \
  mysql -h"$master2_host" -u"$db_user" -p"$db_pass" < <-EOSQL & 
    STOP SLAVE;
    CHANGE MASTER TO MASTER_HOST='$master1_host', \
    MASTER_USER='$repl_user', MASTER_PASSWORD='$repl_pass', \
    MASTER_LOG_FILE='$log_file', MASTER_LOG_POS=$log_pos;
    START SLAVE;
EOSQL
  sleep 5;
  rm -f "$master1_status";
 
  master2_status=$(mysql -h"$master2_host" -u"$db_user" -p"$db_pass" -e "SHOW SLAVE STATUS\G;"|grep -c "Waiting for master");
  if [ "$master2_status" -eq "1" ];then
    touch /root/.replication1.done;
    echo "OK - master2 was configured as a slave for master1";
  else
    echo "ERROR - failed to configure master2 as a slave for master1";
    exit 1;
  fi
else
  echo "Note - master2 is already configured as a slave for master1";
fi
if ! [ -f "/root/.replication2.done" ];then
  mysql -h"$master2_host" -u"$db_user" -p"$db_pass" -ANe "SHOW MASTER STATUS;"|awk '{print  " " }' >"$master2_status" && \
  log_file=$(cut -d" " -f1 "$master2_status") && \
  log_pos=$(cut -d" " -f2 "$master2_status") && \
  mysql -h"$master1_host" -u"$db_user" -p"$db_pass" < <-EOSQL & 
    STOP SLAVE;
    CHANGE MASTER TO MASTER_HOST='$master2_host', \
    MASTER_USER='$repl_user', MASTER_PASSWORD='$repl_pass', \
    MASTER_LOG_FILE='$log_file', MASTER_LOG_POS=$log_pos;
    START SLAVE;
EOSQL
  sleep 5;
  rm -f "$master2_status";
  
  master1_status=$(mysql -h"$master1_host" -u"$db_user" -p"$db_pass" -e "SHOW SLAVE STATUS\G;"|grep -c "Waiting for master");
  if [ "$master1_status" -eq "1" ];then
    touch /root/.replication2.done;
    echo "OK - master1 was configured as a slave for master2";
  else
    echo "ERROR - failed to configure master1 as a slave for master2";
    exit 1;
  fi
else
  echo "Note - master1 is already configured as a slave for master2";
fi

注意：EOSQL前面的'<''<'之间没有空格。
WordPress有时会通过格式化来做有趣的事情。

该脚本将主控主机db1.hl.local配置为主控主机db2.hl.local的从属服务器。

该脚本还将主机db2.hl.local配置为主机db1.hl.local的从机。

将以下Puppet配置应用于服务器db1.hl.local（不得将其应用于第二台服务器）。

请注意我们如何部署脚本，配置复制然后创建数据库。
该数据库的名称为“教程 ”，主要是因为我们将其用于WordPress。

file { '/root/start_mysql_repl.sh':
  ensure => 'file',
  source => 'puppet:///homelab_files/start_mysql_repl.sh',
  owner  => '0',
  group  => '0',
  mode   => '0700',
  notify => Exec['configure_replication'],
}
exec { 'configure_replication':
  command     => '/root/start_mysql_repl.sh',
  path        => '/usr/bin:/usr/sbin:/bin:/sbin',
  provider    => shell,
  unless      => ['test -f /root/.replication1.done', 'test -f /root/.replication2.done'],
  notify      => Exec['create_database'],
}
## We want to create the database after 
## the replication has been established
exec { 'create_database':
  command     => 'mysql --defaults-file=/root/.my.cnf -e "DROP DATABASE IF EXISTS blog; CREATE DATABASE blog; GRANT ALL PRIVILEGES ON blog.* TO \'dbuser1\'@\'10.11.1.%\' IDENTIFIED BY \'PleaseChangeMe\'; FLUSH PRIVILEGES;"',
  path        => '/usr/bin:/usr/sbin:/bin:/sbin',
  provider    => shell,
  refreshonly => true,
  notify      => Exec['import_database'],
}
## We want to import the database from a dump file
exec { 'import_database':
  command  => 'mysql --defaults-file=/root/.my.cnf blog < blog.sql',
  path     => '/usr/bin:/usr/sbin:/bin:/sbin',
  provider => shell,
  onlyif   => ['test -f /root/blog.sql'],
  refreshonly => true,
}
file { '/root/blog.sql':
  ensure => file,
  source => 'puppet:///homelab_files/blog.sql',
  owner  => '0',
  group  => '0',
  mode   => '0600',
}

数据库导入部分将还原我们的WordPress数据库的内容。
由于导入是在建立复制后执行的，因此该数据库在两个MySQL主数据库上均可用。