MySQL replication provides the ability to automatically and continuously replicate databases between two servers over a network. The benefits of this capability are fairly obvious; if there is a failure of some sort on one server, a ready-made copy of the database is already running on a connected server.
Replication is accomplished through a master-slave
relationship between two servers.
A master server can have more than one slave, and a slave can serve as a master to its own slave, thus forming a master-slave chain. This article deals with the process of setting up MySQL replication over a network using TCP/IP between a single master-slave pair.
Understanding MySQL ReplicationMySQL Replication works via two threads on the slave, the I/O thread and the SQL thread. The I/O thread receives events – updates that occur on the master – from the binary logs of the master server. The I/O thread writes these updates to a relay log. The SQL thread then reads the relay log and executes the events as updates to the slave database. The two threads work independently of one another so that one can be disabled without effecting the other. One advantage of this is that the SQL thread can be stopped while a backup is made of the slave database. New events from the master will still be written to the relay log, and will be executed when the SQL thread is resumed.
In order to establish the required connection and the transfer of data described above, each slave must have an account on its master. The slave periodically connects to the master and locates the position in the master's binary log where it left off the last time it connected. The exchange of data resumes at this point.
It is advisable to set up replication only on servers using the same versions of MySQL. MySQL replication has been an ongoing and changing area of development, and there are some compatibility issues between different versions of MySQL.
Setting up MySQL ReplicationHere is an outline of the steps needed to establish MySQL replication on a master and slave over a TCP/IP network.
You may choose not to replicate all databases from one server to another. To exclude a database, set the following in the master server's option file:
The MySQL database is one you may not want to replicate, because it would place all the user information from the master to the slave.
Free eBookSubscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:
What visitors say...
"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.Read more Testimonials
I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.
Return to top
The name Oracle is a trademark of Oracle Corporation.