This page describes how to set up 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;

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