This page describes the replication of a MySql server database.

= Objectives =
The database named ''ps1sc'' is present on the host ''neverland''. We want to replicate it on the host named ''biglefts''. ''neverland'' will be the master, ''biglefts'' its slave.

= Preliminary =

It is necessary to have root access on both configurations (master and slave). 

The master MySql version is 5.1.41-3ubuntu12.6.

The slave one is 5.1.37-1ubuntu5.4-log.

Warning: two mysql installations have been deployed on biglefts. Use the one in /usr/bin, not the one in /usr/local/bin (the latter happens to be the default one).

= Dump and copy =
{{{
shell prompt> mysqldump ps1sc > ps1sc.sql
shell prompt> mysql
  mysql prompt> create database ps1sc_copy;
  mysql prompt> exit
shell prompt> cat ps1sc.sql | mysql ps1sc_copy
}}}

= Set up the master configuration for replication =
All commands are executed on the master (''neverland'')
Documentation is at http://dev.mysql.com/doc/refman/5.1/en/replication-howto-masterbaseconfig.html
* Stop the mysql server: ''sudo service mysql stop''
* Note: ''ps waux | grep mysqld'' should not show anything (except the grep part maybe)
* Edit the mysql configuration and add (or uncomment+modify) the ''[mysqld]'' section so that it contains:
{{{
log-bin=/var/log/mysql/mysql-bin.log
server-id=2010101301
}}}
* Since we are going to use InnoDB, I followed the advice in documentation and added:
{{{
innodb_flush_log_at_trx_commit=1
sync_binlog=1
}}}
* Start the mysql server: ''sudo service mysql start''

= Set up the slave configuration for replication =
All commands are executed on the slave (''biglefts'')
Documentation is at http://dev.mysql.com/doc/refman/5.1/en/replication-howto-slavebaseconfig.html

* Stop the mysql server: ''sudo service mysql stop''

* Note: ''ps waux | grep mysqld'' should not show anything (except the grep part maybe)

* Edit the mysql configuration and add (or uncomment+modify) the ''[mysqld]'' section so that it contains:
{{{
server-id=2010101302
}}}

* I also activated binary logging (see the last paragraph in the documentation)
{{{
log-bin=/var/log/mysql/mysql-bin.log
}}}

* Start the mysql server: ''sudo service mysql start''
= Create a User for Replication =
Documentation is at http://dev.mysql.com/doc/refman/5.1/en/replication-howto-repuser.html
On the master:
{{{
  mysql prompt> CREATE USER 'repl'@'biglefts.ifa.hawaii.edu' IDENTIFIED BY 'biglefts_repl';
  mysql prompt> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'biglefts.ifa.hawaii.edu';
  mysql prompt> FLUSH PRIVILEGES;
  mysql prompt> exit
}}}

+ Obtaining the Replication Master Binary Log Coordinates
Documentation is at http://dev.mysql.com/doc/refman/5.1/en/replication-howto-masterstatus.html

On the master:
{{{
  mysql prompt> FLUSH TABLES WITH READ LOCK;
  mysql prompt> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |    83959 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
}}}
In a '''different''' shell (do not disconnect from mysql client), make a dump of the database:
{{{
shell prompt> mysqldump --master-data ps1sc > ps1sc.db
}}}
Then, release the lock:
{{{
  mysql prompt> UNLOCK TABLES;
}}}

= Set up the slave =
Copy the dump to the slave (with scp, rsync...). Create the database on the slave and ingest the dump:
{{{
mysql prompt> CREATE DATABASE ps1sc;
}}}
{{{
shell prompt> cat ps1sc.db | mysql ps1sc
}}}
Set up the master configuration (on the slave):
{{{
CHANGE MASTER TO MASTER_HOST='neverland.ifa.hawaii.edu', 
                 MASTER_USER='repl', 
                 MASTER_PASSWORD='biglefts_repl', 
                 MASTER_LOG_FILE='mysql-bin.000001', 
                 MASTER_LOG_POS=83959;
}}}

= Enjoy... =
It might be necessary to tell the slave to ignore some updates:
/usr/sbin/mysqld --replicate-ignore-table=mysql.user

= MySQL Servers Replications =

|| Database         || Master  || Slave   || Replication User Name ||
|| nebulous         || ippdb00 || ippdb02 || repl_neb              ||
|| gpc1             || ippdb01 || ipp0012 || slave                 ||
|| ippRequestServer || ippc17  || ippc19  || repl_pstamp           ||

 * nebulous replicated on 20110315T121800
   Parameters: 
{{{
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysqld-bin.002221
        Position: 1068308831
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
}}}

Dump is on ippdb00:
{{{
mysqldump --master-data nebulous -u root > /export/ippdb00.0/ipp/nebulous/nebulous_dump-20110315T121800.sql
}}}