MySQL Replication for Moodle

Hope the earlier post for implementing Moodle was useful. To add backup to moodle would be ideal and what better than to use MySQL replication for ensuring database is getting backed up in real-time basis.

In this article we shall use two servers

  1. Master Server: – Ubuntu with Moodle, My SQL 5.5, phpmyadmin
  2. Slave Server: – Ubuntu, MySQL 5.5, phpmyadmin

Configuring the Master server –

This is the main MySQL database that Moodle will use in the production environment. This is the database in which Moodle will make entries

Master rep1

  • Selection Replication from the ‘More’ tab dropdown as shown above

Master rep2

  • Under Master replication – click on ‘configure’

Master rep3

  • Copy the 4 lines seen in the window and paste it in my.cnf file
    • sudo nano /etc/mysql/my.cnf

Master rep4

    • To allow slave server to connect to the master server, you will need to allow the IP address of the slave server in my.cnf file as shown above
    • It is observed that after entering these four lines in my.cnf of master server, Moodle application is unable to write to the database. To resolve that issue make an entry of the following line after binlog_do_db=moodle. The entry you need to make in the my.cnf file is binlog_format=ROW (below binlog_do_db=moodle)
    • Restart the mysql database and apache server by running the following commands
      • sudo service apache2 restart
      • sudo service mysql restart
    • Click on the Go button in your browser and your browser window should look like below

Master rep5

    • Click on ‘Add Slave User’

Master rep6

    • Add the details as mentioned above, you can enter a password or click on generate and then press ‘Go’

We have completed the Master Configuration

Configuring the Slave server –

This is the slave MySQL database in which the master database will be replicated in real time.

Master rep1

  • Selection Replication from the ‘More’ tab dropdown as shown above

Master rep2

  • Under Slave replication – click on ‘configure’

Master rep7

  • Make the above entries and enter the ‘server id’ details above the username section in slave server’s my.cnf in the [mysqld] section
  • Restart mysql server (sudo service mysql restart)
  • If the slave is able to connect to master it should show the below screen

Master rep8

  • Click on ‘See Slave Status Table’. The two parameters highlighted should be always ‘Yes’ in green colour indicating the slave database is replicating with the master database.

Master rep9

  • If either or both are in Red colour you should click on ‘ Control Slave’ and then ‘Full Start’

Master rep10

  • Once it is starts, click on See Slave Status Table’ and look for the ‘Slave_IO_State’ property (first line) – It should be – ‘waiting for master to send event’. This means the replication is successful.
  • Now go back to ‘Master Server’ and click on ‘More->Replication’. Under ‘Master Configuration’, click on ‘See connected Slaves’ and you should be able to see the server id of the slave server as shown below

Master rep11

You have successfully configured MySQL replication between two servers. You can have multiple slaves across various locations and repeat steps as demonstrated above.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s