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
}}}
Note: If you ingest a huge database, think of stopping the slow log! (there are good chances to see the slow log grow as much as the number of insert statements)

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              ||
|| ippadmin         || ippdb01 || ipp0012 || slave                 ||
|| ippRequestServer || ippc17  || ippc19  || repl_pstamp           ||
|| gpc1             || ippdb01 || ippdb03 || repl_gpc1             ||