This page describes how to set up the replication of a MySql server database.

[[PageOutline]]

= 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;
}}}

= Start/Stop the slave =

To start the slave:
{{{
mysql prompt> START SLAVE;
}}}

To stop it:
{{{
mysql prompt> STOP SLAVE;
}}}

To check it status:
{{{
mysql prompt> SHOW SLAVE STATUS\G
}}}
The displayed message looks like:
{{{
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: ippdb01
                Master_User: ipp
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysqld-bin.032506
        Read_Master_Log_Pos: 13793409
             Relay_Log_File: mysqld-relay-bin.002900
              Relay_Log_Pos: 13793547
      Relay_Master_Log_File: mysqld-bin.032506
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: czardb,gpc1,ippadmin,ipptopsps,megacam,ssp,uip
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 13793409
            Relay_Log_Space: 13793547
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)
}}}

= Create a slave from another one =
*) I assume that we want to create a mysql slave on host slave02 from a (running/functioning) slave named slave01

*) Make sure that slave01 and slave02 both run the exact same version of mysql

*) Stop the mysql server on slave01
{{{
shell@slave01> mysqladmin -u root -p shutdown now
}}}

*) RSync the contents of /var/lib/mysql on slave01 to /var/lib/mysql.slave01 on slave02
{{{
shell@slave02> rsync -av root@slave01:/var/lib/mysql /var/lib/mysql.slave01
}}}

*) Stop the mysql server on slave02 if it is running, backup /etc/mysql/my.cnf on slave02 and copy /etc/mysql/my.cnf from slave01 there
{{{
shell@slave02> mysqladmin -u root -p shutdown now
shell@slave02> cd /etc/mysql
shell@slave02> mv my.cnf my.cnf.before_rsync
shell@slave02> scp root@slave01:/etc/mysql/my.cnf .
}}}

*) Edit /etc/mysql/my.cnf on slave02 and change the server-id.
The server-id value has to be a number, I usually use YYYYMMDD<RUNNING NUMBER>, e.g. for the 5th slave install on 2025 Nov 6th (busy day), I would use:
{{{
server-id = 2025110605
}}}

*) If you want to be super-safe (and if you have space), make a copy of the rsync on slave02
{{{
shell> cp -a /var/lib/mysql.slave01 /var/lib/mysql.slave01.sos
}}}

*) Make mysql.slave01 the new mysql data directory
{{{
shell@slave02> mv /var/lib/mysql /var/lib/mysql.before_rsync.delete_me_when_working
shell@slave02> mv /var/lib/mysql.slave01 /var/lib/mysql
}}}

*) Start the mysql server on slave02
{{{
shell@slave02> /etc/init.d/mysql zap
shell@slave02> /etc/init.d/mysql start
}}}
then check the slave status
{{{
mysql@slave02> SHOW SLAVE STATUS\G
}}}

In case of error, check the mysql error logs on slave02 or the master (e.g. create a user for the replication on the master if it does not exist...)

*) Start the mysql server on slave 01 and check its status (it should work without any issue).
{{{
shell@slave01> /etc/init.d/mysql zap
shell@slave01> /etc/init.d/mysql start
}}}
= 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             ||