7 12 2013

This is how I setup MySQL replication with puppet managing the configuration files/install.

The MySQL module used is – https://github.com/puppetlabs/puppetlabs-mysql

Puppet Config for the master;

       class { 'mysql':} ->

       class { 'mysql::server':
           config_hash => { 
               'root_password' => '<password>',
               'bind_address' => '0.0.0.0'
           }
       } ->

       mysql::server::config { 'basic_config':
               settings => {
               'mysqld' => {
                   'server-id'                      => '1',
                   'binlog-format'                  => 'mixed',
                   'log-bin'                        => 'mysql-bin',
                   'datadir'                        => '/var/lib/mysql',
                   'innodb_flush_log_at_trx_commit' => '1',
                   'sync_binlog'                    => '1',
                   'binlog-do-db'                   => [ 'database1', 
                                                         'database2',
                                                         'database3',
                                                         'database4',
                                                       ],
               },

           }

       }

       database_user { '<user>@<master>':
           password_hash => mysql_password('<password>')
       } ->

       database_grant { '<user>@<master>/*':
           privileges => ['REPLICATION SLAVE'] ,
       }

       mysql::db { 'database1':
           ensure   => 'present',
           user     => 'user1',
           password => '<password>',
           host     => '<host>',
           grant    => ['all'],
           require  => Class['mysql::config'],
       }

       mysql::db { 'database2':
           ensure   => 'present',
           user     => 'user2',
           password => '<password>',
           host     => '<host>',
           grant    => ['all'],
           require  => Class['mysql::config'],
       }

       mysql::db { 'database3':
           ensure   => 'present',
           user     => 'user3',
           password => '<password>',
           host     => '<host>',
           grant    => ['all'],
           require  => Class['mysql::config'],
       }

       mysql::db { 'database4':
           ensure   => 'present',
           user     => 'user4',
           password => '<password>',
           host     => '<host>',
           grant    => ['all'],
           require  => Class['mysql::config'],
       }

Puppet Config for the slave;

       class { 'mysql': } ->

       class { 'mysql::server':
           config_hash => {
               'root_password' => '<password>',
               'bind_address'  => '0.0.0.0',
           }
       } ->

       mysql::server::config { 'basic_config':
           settings => {
               'mysqld' => {
                   'server-id'         => '2',
                   'binlog-format'     => 'mixed',
                   'log-bin'           => 'mysql-bin',
                   'relay-log'         => 'mysql-relay-bin',
                   'log-slave-updates' => '1',
                   'read-only'         => '1',
                   'replicate-do-db'   => [ 'database1', 
                                            'database2',
                                            'database3',
                                            'database4',
                                          ],
               },

           }

       }

       mysql::db { 'database1':
           ensure   => 'present',
           user     => 'user1',
           password => '<password>',
           host     => '<host>',
           grant    => ['all'],
           require  => Class['mysql::config'],
       }

       mysql::db { 'database2':
           ensure   => 'present',
           user     => 'user2',
           password => '<password>',
           host     => '<host>',
           grant    => ['all'],
           require  => Class['mysql::config'],
       }

       mysql::db { 'database3':
           ensure   => 'present',
           user     => 'user3',
           password => '<password>',
           host     => '<host>',
           grant    => ['all'],
           require  => Class['mysql::config'],
       }

       mysql::db { 'database4':
           ensure   => 'present',
           user     => 'user4',
           password => '<password>',
           host     => '<host>',
           grant    => ['all'],
           require  => Class['mysql::config'],
       }

SQL Syntax (Yes, there are some manual bits..)

On Master;

   FLUSH PRIVILEGES;

   FLUSH TABLES WITH READ LOCK;

   UNLOCK TABLES;

   mysql> SHOW MASTER STATUS;
   +------------------+----------+----------------------------------------------------------------------------+------------------+
   | File             | Position | Binlog_Do_DB                                                               | Binlog_Ignore_DB |
   +------------------+----------+----------------------------------------------------------------------------+------------------+
   | mysql-bin.000270 | 71440377 | database1,database2,database3,database4                                    |                  |
   +------------------+----------+----------------------------------------------------------------------------+------------------+

Make a note of the mysql-bin file name and the postition number, then dump the databases you are going to replicate and copy them to the slave.

On Slave;

Import the SQL dumps from the master.

   SLAVE STOP;

   CHANGE MASTER TO MASTER_HOST='<master>', MASTER_USER='<user>', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='mysql-bin.000270', MASTER_LOG_POS=71440377;

   START SLAVE;

   SHOW SLAVE STATUS\G

MASTER_LOG_FILE= and MASTER_LOG_POS=; are displayed when you run “SHOW MASTER STATUS;” on the master.

When you run “SHOW SLAVE STATUS\G” make sure you see;

 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

Also check on “Seconds_Behind_Master” you will need to wait for this to drop to 0.

Once it’s on 0, create a table in a database on the master and check to see if it’s replicated to the slave.

Template Error: The template file must be given (or the template could not be opened) Error: Missing Dependency: rtld(GNU_HASH)