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

The master MySql version is 5.1.41-3ubuntu12.6. The slave one is 5.1.37-1ubuntu5.4-log. Warning: two mysql installation are present on biglefts. Use the one in /usr/bin, not the one in /usr/local/bin

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