Moodle MySQL Replication using PHPMyAdmin

Let’s assume you have Moodle up and running and you are backing up your MySQL database on a regular basis, well what happens if your database server goes down, gets corrupt or your disk array melts?  This is when you find out that your backup is only ever as good as when it was last taken.

Most organisations take nightly SQL dumps so if the database server fails they can simply restore from a complete backup.  However, what happens if your database fails at say 4pm in the afternoon and your last automated backup was 11:00pm the night before?  Well potentially you have lost over a full days worth of data. Now in a busy college or school environment that could be a lot of assignment hand-ins, or tutor marks and feedback.  Not to mention forum posts, comments, messages, quiz attempts and pretty much everything else.

So what would you tell your staff and students if you lost a full day of their work?  You can’t tell tutors to mark their assignments again, or tell students to re-submit assignments, re-take quizzes and remember what they said in a forum.  So the solution is “Replication“.

So grab a cup of coffee (or tea if you’re British) and follow this tutorial to get replication up and running in less than an hour.  You will need basic MySQL skills to set this up and you need PHPMyAdmin installed on both of your database servers.  (This is a long tutorial with many steps but I needed to make sure I didn’t miss any out, as it won’t work if you skip them)

OK ready? Let’s go…

1. You need 2 MySQL servers, each running a copy of PHPMyAdmin
2. Your primary Moodle Database is referred to as the “master”, the replicated server will be referred to as the “slave”
3. Take an SQL dump of your master database and restore it on your slave database
4. Now, on your master database log in to PHPMyAdmin and click the Replication Tab

   

5. Now click the link to configure this as the master server
6. Select “Ignore all databases, replicate:”
7. Select your Moodle database from the available list (see below)

   

8. Copy and paste the code this screen provides into the very bottom of your MySQL config file (my.ini on Windows) AND add a line that says binlog_format=ROW (this fixes an error when running an external DB enrollment sync with replication)
9. Restart the MySQL services on the master server, leave PHPMyAdmin Open though
10. Once the service has restarted, click on “Go” on the PHPMyAdmin screen.
11. You will be redirected to the Replication screen which now looks like this

    

12. Lastly, we need to create a replication user so click on the link that says “Add slave replication user”
13. Create a user and password, set the host to “Any” and click “Go”
14. On the privileges screen ensure the new user has both replication permissions checked and click “Go”

    

15. That’s all we need to do on the master server, now lets move over to the slave
16. From within PHPMyAdmin on the slave, click the replication tab
17. Then click the link to configure this server as slave replication
18. Copy the line of code that shows your new server id and paste this entire line into the MySQL config file on your slave database server
19. Stop and start your MySQL service on the slave server
20. Now in PHPMyAdmin enter the username of the replication user you created in step 13
21. Enter the password and the host (the hostname of the master server or its IP Address)
22. If your default port is not 3306 then change it, chances are it uses the default port
23. Click “Go”

    

24. It then takes you back to the replication screen and appears as though it’s not configured but it requires a refresh
25. So refresh the page and you will see it’s configured but not running

    

26. Click “Control slave” then click “Full Start”
27. PHPMyAdmin now sits there with a Loading window, after a while this will  time out, if this happens or indeed after 5 minutes nothing happens then reload the replication page again (refresh it).
28. When the page reloads (either automatically or manually forced by you, you will see no error warnings and a message that says “Server is configured as slave”
29. Now to check it’s working, click on the link that says “See slave status table”

    

30. If everything is working then you will see a message against Slave_IO_State that reads “Waiting for master to send event”

Well done!  You have successfully configured your Moodle server to replicate your database.
So what happens now?  Well you can now have some fun with it.

Try creating a new course on your live Moodle then watch how it magically appears in your mdl_course table on your slave database.  Each time a change is made on the master, it’s replicated by the slave.  Trust me, the novelty of doing this never fades.  I keep trying to catch it out.

So when disaster strikes!
If your master database should fail you no longer need to panic and begin restoring huge databases. Instead just change your Moodle config file to point to the host or IP of the slave and you are up and running with no data loss.  This then gives you the time to fix your master server.

I hope you find this article useful.  And I hope with it you can sleep better at night knowing you have this fail safe in place.
I’ve had database meltdowns without replication, and the agony of this is far greater than the agony of sitting through this tutorial.

Thanks for reading and good luck.

Update 5th July 2013 : I have added an extra step at point 8, you must add the line binlog_format=ROW to your MySQL config file and restart.  I found an error when running the database enrollment sync script when replicating, this line fixes that error.