Replication demo

Set Up: assume 2 Windows boxes. (should work on other platforms)

Machine 1 - Master

IP: master.ip
Has MySQL 5.1

Machine 2 - Slave

IP: slave.ip
Has MySQL 5.1

0. MySQL setup:

Alan R says: For macs, I've used http://www.mamp.info/en/index.html as an easy way to install/control MySQL.

On each machine you will need to create a 'purls' database in MySQL, and a username and password for a MySQL user that can modify it. It is also necessary to know what port MySQL is listening on.

When creating the database do not specify a Unicode collation as that will make the 1.4 PURLZ server fail. Instead use an 8-bit collation.

Installing PURL using MySQL as the backend on Machine 1:

1. Download PURL 1.4 from http://purlz.org/project/purl/downloads/

2. Install specifying MySQL as your backend. Imp(ortant?) note: You would have a create a database schema'purls' on your MySQL using command line. The PURL installation would not do that for you. In other words, the PURL installation expects you to have a schema called 'purls'. This is will be used as the backend for storing the URLs.

3. Go to the folder where you installed PURL. By default it is: C:\Program Files\PURLZ-Server-1.4

4. Navigate to the bin folder, click on startup.bat to start the server. Imp(ortant?) note: Default username password for the PURL server is: admin/password

5. Create a backup of purls on Machine 1: On the command line, execute the following command (subtituting correct username and password per #0 above):

   mysqldump --user=root --password=...  --opt purls > c:\purls_backup.sql

6. On Machine 1: Log on to MySQL

   mysql -u root -p

7. On Machine 1:

   Run the command
   grant replication slave, replication client on *.* to 'replicator'@'slave.ip' identified by 'password';

8. On Machine 1:

   Configure the master:
   Add the following lines to my.ini file in C:\Program Files\MySQL\MySQL 5.1 folder
   Add the lines in the section [mysqld] after the line that specifies the port

   server-id=1
   log-bin="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/bin.log"
   log-error="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/error.log"
   log-bin-index="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/log-bin.index"
   binlog-do-db=purls

9. Restart the MySQL service on the master

10. On Machine 2 (slave): Create a new empty database 'purls'

11. On Machine 2: Restore the purls database: On the command prompt run the command

   mysql -u root -p ... < C:\path_to_purls_backup\purls_backup.sql

12. On Machine 2: Configure the slave: Add the following lines to my.ini file in C:\Program Files\MySQL\MySQL 5.1 folder (add the lines in the section [mysqld] after the line that specifies the port)

   server-id=2
   master-host=171.65.32.120
   master-port=3306
   master-user=replicator
   master-password=password
   replicate-do-db=purls
   log-bin="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/bin.log"
   log-bin-index="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/log-bin.index"
   log-error="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/error.log"
   relay-log="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/relay.log"
   relay-log-info-file="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/relay-log.info"
   relay-log-index="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/relay-log.index"

13. Restart the MySQL service on the slave

14. On the slave, log into MySQL

    i) start slave;
    ii)show slave status\G;

Make sure slave I/O running is yes Make sure slave_SQL_running is yes

Testing the replication:

Changes to be made to ConfigRDBMS.xml 
Change to slave ip: 171.65.32.239
Change user to user name of the mysql database on slave
Change password to the user of the mysql database on slave

Acknowledgments

The replication mechanism and instructions were provided by the National Center for Biomedical Ontology.