Mysql Reptile

Oussama Hammami, 2011-03-21

Switzernet

Reptile est un script écrit en Ruby dont le but est de contrôler la réplication MySQL.

Ci-dessous la procédure d’installation

Slave

# aptitude install ruby1.8-dev ruby1.8 ri1.8 rdoc1.8 irb1.8 libreadline-ruby1.8 libruby1.8 libopenssl-ruby sqlite3 libsqlite3-ruby1.8

# ln -s /usr/bin/ruby1.8 /usr/bin/ruby

# ln -s /usr/bin/ri1.8 /usr/bin/ri

# ln -s /usr/bin/rdoc1.8 /usr/bin/rdoc

# ln -s /usr/bin/irb1.8 /usr/bin/irb

# ruby -v

# install libmysql-ruby libmysqlclient-dev

# aptitude install gems

# wget "http://production.cf.rubygems.org/rubygems/rubygems-1.3.7.tgz"     --output-document="/tmp/rubygems.tgz"

# tar --directory="/tmp" -xzf "/tmp/rubygems.tgz"

# cd /tmp/rubygems-*

# ruby setup.rb

# cd bin/

# ./gem -v

# ./gem  install reptile

# cp /usr/lib/ruby/gems/1.8/gems/reptile-0.1.2/reptile.yml.sample /etc/reptile.yml

# vi /etc/reptile.yml

Master

mysql> INSERT INTO mysql.user (Host,User,Password) VALUES(' SLAVE IP ','switzer_reptile',PASSWORD('PASSWORD'));

mysql> FLUSH PRIVILEGES;

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT, SUPER ON *.* TO 'switzer_reptile'@' SLAVE IP ' IDENTIFIED BY 'PASSWORD';

mysql> GRANT SELECT, REPLICATION CLIENT ON *.* TO 'switzer_reptile'@' SLAVE IP ' IDENTIFIED BY 'PASSWORD';

mysql> GRANT SELECT, INSERT, UPDATE, ALTER ON replication_monitor.* TO 'switzer_reptile'@' SLAVE IP ' IDENTIFIED BY 'PASSWORD';

mysql> FLUSH PRIVILEGES;

Slave

mysql> INSERT INTO mysql.user (Host,User,Password) VALUES('localhost','switzer_reptile',PASSWORD('PASSWORD'));

mysql> FLUSH PRIVILEGES;

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT, SUPER ON *.* TO 'switzer_reptile'@"localhost" IDENTIFIED BY 'PASSWORD';

mysql> GRANT SELECT, REPLICATION CLIENT ON *.* TO 'switzer_reptile'@"localhost" IDENTIFIED BY 'PASSWORD';

mysql> GRANT SELECT, INSERT, UPDATE, ALTER ON replication_monitor.* TO 'switzer_reptile'@"localhost" IDENTIFIED BY 'PASSWORD';

mysql> FLUSH PRIVILEGES;

/etc/reptile.yml

config:

  heartbeart_threshold: 10

  delay_threshold_secs: 360

  row_difference_threshold: 10

# You can also set username/passwords here,

# so you don't have to repeat them.

#

users:

 ro_user:

   username: switzer_reptile

   password: PASSWORD

 heartbeat_user:

   username: switzer_reptile

   password: PASSWORD

 replication_user:

   username: switzer_reptile

   password: PASSWORD

porta-sip:

  master:

    password: PASSWORD

    username: switzer_reptile

    adapter:  mysql

    database: porta-sip

    host:     master.switzernet.com

    port:     3306

#    socket:   /tmp/mysql.sock

  slave:

    password: PASSWORD

    username: switzer_reptile

    adapter:  mysql

    database: porta-sip

    host:     localhost

    port:     3306

#   socket:   /var/run/mysqld/mysqld.sock

Master & Slave

mysql> CREATE DATABASE replication_monitor;

mysql> CREATE TABLE replication_monitor.heartbeats (

    unix_time INTEGER NOT NULL,

    db_time TIMESTAMP NOT NULL,

    INDEX time_idx(unix_time)

  );

 Activer la réplication de la base de données replication_monitor (dans le fichier /etc/mysql/my.cnf).

Exemple

db2:~# replication_status -h

Usage: replication_status [path_to_config_file]

    -h, --help                       Displays this help info

        --status                     Displays the slave status

        --diff                       Checks the row count difference between master and slave

        --report                     Prints a report

        --heartbeat                  Checks the heartbeat timestamp difference between master and slave

        --stop-slaves                Stops all slaves

        --start-slaves               Starts all slaves

-l, --log-level [LEVEL]          Specify log level (debug,info,warn,error,fatal)

db2:~# replication_status --diff

INFO: Checking row counts.

INFO: Replication Row Count Deltas for porta-sip on master.switzernet.com @ Mon Mar 21 11:17:14 +0100 2011

INFO: There is 1 delta

INFO:   location table: 1

db2:~# replication_status --report

INFO: Generating report

INFO: Checking slave status.

INFO: 'porta-sip' is 'running'

INFO: Checking row counts.

INFO: Replication counts A-OK for porta-sip on master.switzernet.com @ Mon Mar 21 11:18:18 +0100 2011

INFO: Checking heartbeats.

INFO: The slave delay for 'porta-sip' is 2 seconds

Daily Replication Report for 03/21/11

                       Checking slave status

                       Checking table row counts

The row count difference threshold is 10 rows

                       Checking replication heartbeat

The heartbeat latency threshold is 360 seconds

Reference

https://github.com/nstielau/reptile#readme

http://reptile.rubyforge.org/doc/

http://stackoverflow.com/questions/3608287/installing-mysql2-gem-for-rails-3

http://www.ubuntugeek.com/how-to-install-ruby-on-rails-in-ubuntu-810-intrepid.html