Setting up MySQL Replication

[resplin]

Obsolete Pages{{Obsolete}}

The official documentation is at: http://docs.alfresco.com

DB ReplicationMySQL
Back to Replication

---

How to start replicating - the master server

1. Grant the slave permission to replicate with the REPLICATION SLAVE privilege, for example as follows: GRANT REPLICATION SLAVE ON *.* TO slave_user IDENTIFIED BY 'slave_password'
   
2. If the master is not using the binary update log, add the following lines to the my.cnf (Linux) or my.ini (Windows) configuration file on the master, and restart the server:
   

   [mysqld]
   log-bin
   server-id=1

By convention, the master is usually server-id 1, and any slaves from 2 onwards, though you can change this if you wish. If the master is already using the binary update log, either take note of the offset at the moment of the backup (the next step), or use the RESET MASTER statement to clear all binary logs and immediately begin the backup. You may want to make a copy of the binary logs before doing this, in case you need to use the binary logs to restore from backup.

1. Make a backup of the database. You will use this to start the slave server. Note the comments about the binary log above. You can also skip this step if you use the LOAD DATA FROM MASTER statement, but see the comments about locking the master below first.
   
2.   Add the following to the configuration file on the slave:
   

   master-host=master-hostname
   master-user=slave-user
   master-password=slave-password
   server-id=2

The slave user and slave password are those to which you set when you granted REPLICATION SLAVE permission on the master. The server-id must be a unique number, different to the master or any other slaves in the system. There are also two other options: master-port, used if the master is running on a non-standard port (3306 is default), and master-connect-retry, a time in seconds for the slave to attempt to reconnect if the master goes down. 60 seconds is default.

Restore the data from the master, either as you would normally restore a backup, or with the statement LOAD DATA FROM MASTER. The latter will lock the master for the duration of the operation, which could be quite lengthy, so you may not be able to spare the downtime.

More info here: http://www.databasejournal.com/features/mysql/article.php/3355201

Replication status

Check for errors in the hostname.err file in you MySQL data directory on your slave server, all connection problems should be logged here.

Use mysql> SHOW SLAVE STATUS; and mysql> SHOW MASTER STATUS; on the slave and the master to check the status of the replication. On the slave side both Slave_IO_Running: Yes and Slave_SQL_Running: Yes should be running. The Read_Master_Log_Pos: 9999 on the slave and the Position: 9999 on the master should read the same offset.