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