Homelab<\/h2>\n
We have two CentOS 7 servers installed which we want to configure as follows:<\/p>\n
db1.hl.local (10.11.1.17) \u2013 will be configured as a MySQL master
\ndb2.hl.local (10.11.1.18) \u2013 will be configured as a MySQL master<\/p>\n
SELinux set to enforcing mode.<\/p>\n
See the image below to identify the homelab part this article applies to.<\/p>\n
![\"\"](\"https:\/\/www.lisenet.com\/wp-content\/uploads\/2018\/04\/lisenet-homelab-diagram_mysql.png\")
<\/p>\n
Configuration with Puppet<\/h2>\n
Puppet master runs on the Katello<\/a> server.<\/p>\n We use puppetlabs-mysql<\/a> Puppet module to configure the server.<\/p>\n Please see the module documentation for features supported and configuration options available.<\/p>\n MySQL repository is provided by Katello (we configured them here<\/a>).<\/p>\n It is essential to ensure that MySQL servers can talk to each other. The following needs applying to both MySQL masters:<\/p>\n firewall { ‘007 allow MySQL’: This will also allow Apache connections to the database.<\/p>\n Nothing groundbreaking here really, but note the auto-increment-offset. This is to help prevent the situation where two queries insert data at the same time in the same database and the same table on both servers db1 and db2, and different entries end up with the same id.<\/p>\n class { ‘mysql::server’: Configuration of the second server is almost identical to the first one with two exceptions: server-id and auto-increment-offset.<\/p>\n class { ‘mysql::server’: The easy part is complete, and we should have our MySQL nodes provisioned at this stage.<\/p>\n We don\u2019t have any databases created yet, therefore at this point there isn\u2019t much we want to sync between the two servers.<\/p>\n Let us go ahead and put the steps required to configure MySQL replication manually into a Bash script start_mysql_repl.sh. Note that the script is a quick and dirty way of getting MySQL replication working, but it\u2019s not the right approach.<\/p>\n Ideally we should use a Puppet template with parameters, so that we can provide values for them by passing a parameter hash to a function and wouldn\u2019t have to hardcode hostnames, usernames etc.<\/p>\n #!\/bin\/bash if ! [ -f “\/root\/.replication1.done” ];then Note: there are no spaces between in front of EOSQL. WordPress does funny things with formatting sometimes.<\/p>\n The script configures master host db1.hl.local as a slave for master host db2.hl.local.<\/p>\n The script also configures master host db2.hl.local as a slave for master host db1.hl.local.<\/p>\n Apply the following Puppet configuration to the server db1.hl.local (it must not be applied to the second server).<\/p>\n Note how we deploy the script, configure replication and then create a database. The name of the database is “blog”, mostly because of the fact that we’ll be using it for WordPress.<\/p>\n file { ‘\/root\/start_mysql_repl.sh’: The database import part restores the content of our WordPress database. Because the import is performed after the replication has been established, the database is available on both MySQL masters.<\/p>\nPuppet Modules<\/h3>\n
Katello Repositories<\/h3>\n
Configure Firewall<\/h3>\n
\ndport => [3306],
\nsource => ‘10.11.1.0\/24’,
\nproto => tcp,
\naction => accept,
\n}<\/p>\nConfigure MySQL Master on db1.hl.local<\/h3>\n
\npackage_name => ‘mysql-community-server’,
\nservice_name => ‘mysqld’,
\nroot_password => ‘PleaseChangeMe’,
\ncreate_root_my_cnf => true,
\nmanage_config_file => true,
\nconfig_file => ‘\/etc\/my.cnf’,
\npurge_conf_dir => true,
\nrestart => true,
\noverride_options => {
\nmysqld => {
\nbind-address => ‘0.0.0.0’,
\ndatadir => ‘\/var\/lib\/mysql’,
\nlog-error => ‘\/var\/log\/mysqld.log’,
\npid-file => ‘\/var\/run\/mysqld\/mysqld.pid’,
\nwait_timeout => ‘600’,
\ninteractive_timeout => ‘600’,
\nserver-id => ‘1’,
\nlog-bin => ‘mysql-bin’,
\nrelay-log => ‘mysql-relay-log’,
\nauto-increment-offset => ‘1’,
\nauto-increment-increment => ‘2’,
\n},
\nmysqld_safe => {
\nlog-error => ‘\/var\/log\/mysqld.log’,
\n},
\n},
\nremove_default_accounts => true,
\n}->
\n## MySQL admin user who can connect remotely<\/em>
\nmysql_user { ‘[email protected]<\/a>%’:
\nensure => ‘present’,
\npassword_hash => mysql_password(‘PleaseChangeMe’),
\n}->
\nmysql_grant { ‘[email protected]<\/a>%\/*.*’:
\nensure => ‘present’,
\noptions => [‘GRANT’],
\nprivileges => [‘ALL’],
\ntable => ‘*.*’,
\nuser => ‘[email protected]<\/a>%’,
\n}->
\n## MySQL user for replication<\/em>
\nmysql_user { ‘[email protected]<\/a>%’:
\nensure => ‘present’,
\npassword_hash => mysql_password(‘PleaseChangeMe’),
\n}->
\nmysql_grant { ‘[email protected]<\/a>%\/*.*’:
\nensure => ‘present’,
\nprivileges => [‘REPLICATION SLAVE’],
\ntable => ‘*.*’,
\nuser => ‘[email protected]<\/a>%’,
\n}<\/p>\nConfigure MySQL Master on db2.hl.local<\/h3>\n
\npackage_name => ‘mysql-community-server’,
\nservice_name => ‘mysqld’,
\nroot_password => ‘PleaseChangeMe’,
\ncreate_root_my_cnf => true,
\nmanage_config_file => true,
\nconfig_file => ‘\/etc\/my.cnf’,
\npurge_conf_dir => true,
\nrestart => true,
\noverride_options => {
\nmysqld => {
\nbind-address => ‘0.0.0.0’,
\ndatadir => ‘\/var\/lib\/mysql’,
\nlog-error => ‘\/var\/log\/mysqld.log’,
\npid-file => ‘\/var\/run\/mysqld\/mysqld.pid’,
\nwait_timeout => ‘600’,
\ninteractive_timeout => ‘600’,
\nserver-id => ‘2’,
\nlog-bin => ‘mysql-bin’,
\nrelay-log => ‘mysql-relay-log’,
\nauto-increment-offset => ‘2’,
\nauto-increment-increment => ‘2’,
\n},
\nmysqld_safe => {
\nlog-error => ‘\/var\/log\/mysqld.log’,
\n},
\n},
\nremove_default_accounts => true,
\n}->
\n## MySQL admin user who can connect remotely<\/em>
\nmysql_user { ‘[email protected]<\/a>%’:
\nensure => ‘present’,
\npassword_hash => mysql_password(‘PleaseChangeMe’),
\n}->
\nmysql_grant { ‘[email protected]<\/a>%\/*.*’:
\nensure => ‘present’,
\noptions => [‘GRANT’],
\nprivileges => [‘ALL’],
\ntable => ‘*.*’,
\nuser => ‘[email protected]<\/a>%’,
\n}->
\n## MySQL user for replication<\/em>
\nmysql_user { ‘[email protected]<\/a>%’:
\nensure => ‘present’,
\npassword_hash => mysql_password(‘PleaseChangeMe’),
\n}->
\nmysql_grant { ‘[email protected]<\/a>%\/*.*’:
\nensure => ‘present’,
\nprivileges => [‘REPLICATION SLAVE’],
\ntable => ‘*.*’,
\nuser => ‘[email protected]<\/a>%’,
\n}<\/p>\nConfigure Master\/Master Replication<\/h3>\n
\n#
\n# Author: Tomas at www.lisenet.com
\n# Configure MySQL Replication with Puppet
\n#
\n# Variables below must match with the ones
\n# defined in the Puppet manifest
\n#
\nmaster1_host=”db1.hl.local”;
\nmaster2_host=”db2.hl.local”;
\nrepl_user=”dbrepl”;
\nrepl_pass=”PleaseChangeMe”;
\ndb_user=”dbadmin”;
\ndb_pass=”PleaseChangeMe”;
\nmaster1_status=”\/tmp\/master1.status”;
\nmaster2_status=”\/tmp\/master2.status”;<\/p>\n
\nmysql -h”$master1_host” -u”$db_user” -p”$db_pass” -ANe “SHOW MASTER STATUS;”|awk ” >”$master1_status” &&
\nlog_file=$(cut -d” ” -f1 “$master1_status”) &&
\nlog_pos=$(cut -d” ” -f2 “$master1_status”) &&
\nmysql -h”$master2_host” -u”$db_user” -p”$db_pass” “$master2_status” &&
\nlog_file=$(cut -d” ” -f1 “$master2_status”) &&
\nlog_pos=$(cut -d” ” -f2 “$master2_status”) &&
\nmysql -h”$master1_host” -u”$db_user” -p”$db_pass”<\/p>\n
\nensure => ‘file’,
\nsource => ‘puppet:\/\/\/homelab_files\/start_mysql_repl.sh’,
\nowner => ‘0’,
\ngroup => ‘0’,
\nmode => ‘0700’,
\nnotify => Exec[‘configure_replication’],
\n}
\nexec { ‘configure_replication’:
\ncommand => ‘\/root\/start_mysql_repl.sh’,
\npath => ‘\/usr\/bin:\/usr\/sbin:\/bin:\/sbin’,
\nprovider => shell,
\nunless => [‘test -f \/root\/.replication1.done’, ‘test -f \/root\/.replication2.done’],
\nnotify => Exec[‘create_database’],
\n}
\n## We want to create the database after
\n## the replication has been established<\/em>
\nexec { ‘create_database’:
\ncommand => ‘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;”‘,
\npath => ‘\/usr\/bin:\/usr\/sbin:\/bin:\/sbin’,
\nprovider => shell,
\nrefreshonly => true,
\nnotify => Exec[‘import_database’],
\n}
\n## We want to import the database from a dump file<\/em>
\nexec { ‘import_database’:
\ncommand => ‘mysql –defaults-file=\/root\/.my.cnf blog ‘\/usr\/bin:\/usr\/sbin:\/bin:\/sbin’,
\nprovider => shell,
\nonlyif => [‘test -f \/root\/blog.sql’],
\nrefreshonly => true,
\n}
\nfile { ‘\/root\/blog.sql’:
\nensure => file,
\nsource => ‘puppet:\/\/\/homelab_files\/blog.sql’,
\nowner => ‘0’,
\ngroup => ‘0’,
\nmode => ‘0600’,
\n}<\/p>\n