In MySQL, you can store the same data in multiple servers to provide extra bandwidth and robustness. The process known as REPLICATION is used to keep the data between the two (or more) servers in step.


One server is designated as being the MASTER server and all data updates are performed on that server. Updates are logged by the master server, and a running SLAVE server will collect the changes.

These notes are written to cover data stored on two systems, but note that you can increase the number of servers if you wish - either by running multiple slaves off a master, or by setting up a slave to the first master as a master itself, supplying updates to a second generation slave.

The binary log file format used by the master to record changes varies significantly from one release to the next, so that the slave server should always be at the same or a higher version of MySQL than the master.

A replica server provides extra bandwidth and robustness and is also useful in allowing you to take a reference backup without having to stop data entry - simply freeze the slave, do the backup, and have the slave catch up.


for this example ...
 Master server - robinson
 Slave server - friday
 MySQL root password - k645pfb
 internal account name, ids, etc, made up as we go along.


1. Check that the versions of MySQL that you propose to use on the Master and Slave will work together; ideally, use the same version on both.

2. Install the MySQL on both master and slave if not already running; no need to configure the slave as you'll be simply replicating, but do configure the master.

3. Set up an account on the master server that the slave can use to make its connection. You need to grant the account REPLICATION SLAVE privilege.

 GRANT REPLICATION SLAVE ON *.* TO
  'tgif'@'friday' IDENTIFIED BY 'codAndCh1ps';

4. Take a snapshot of the data on the master server.
 
a) Lock all the tables on the master:
 FLUSH TABLES WITH READ LOCK

b) Run
 SHOW MASTER STATUS
and note the File and Position (if blank, don't worry). LEAVE the MySQL client that you used for that running. This will prevent updates while you're copying to your mirror.

c) Take a dump of the data on the Master server:
 mysqldump --master-data -uroot -pk645pfb > mirror.sql
 
5. Check that the Master server has an ID set and is running with the log-bin option. This should be in the my.cnf file - for example
 [mysqld]
 log-bin=mysql-bin
 server-id=1
Server ids (Master and Slave) must all be unique positive integers.

If necessary, stop and restart the server

6. Stop the slave server and configure its my.cnf file - for example
 [mysqld]
 server-id=2
Restart the slave server

7. Load the data onto the slave server; transfer the mirror.sql file created in step 4 (c) across, then run
 mysql -uroot -pk645pfb < mirror.sql
Flush or restart the slave server if you've transferred different account information over!

8. Tell the slave server to act as a slave server:
 CHANGE MASTER TO
  MASTER_HOST='robinson',
  MASTER_USER='tgif',
  MASTER_PASSWORD='codAndCh1ps',
  MASTER_LOG_FILE='[recorded name or empty if none]',
  MASTER_LOG_POS=[recorded number or 4 if none];

9. Start the slave threads
 START SLAVE;

That should be it ... your slave will contact your master for any updates since the backup copy was taken.


Some alternatives:

a) If you want to use LOAD TABLE FROM MASTER or LOAD DATA FROM MASTER commands later, you need to give your slave SUPER and RELOAD global privileges, and also SELECT privilege over any tables that you want to load.

b) The data can also be transferred, more efficiently, in binary. In summary, you use tar, zip or similar software to squish the data directory in MySQL's data directory (extra steps needed if you're using InnoDb as well) and transfer those files - data files, log files, .frm definition files - and unpack them.
  
c) If you're going to be running different user accounts / passwords etc. on the slave server, or it's a significantly more recent version of MySQL, then do NOT transfer the database called mysql.

d) If you're copying to a slave that has previously functioned as a slave, restart it with the --skip-slave-start option in order to avoid it contacting its old master right away.

e) If you configure your slave server as a master too, then it can take over for data changes in the event of a prolonged failure of the master, and can feed data through to second layer slaves. With a properly configured network with homogeneous systems, the old master can be brought back in later as a slave to automatically catch up.


The Master server keeps a binary log file of database updates for passing to the slaves as required.

The Slave server keeps data files called master.info and relay-log.info to keep track of how it's doing in processing the master log. It maintains a connection to the Master so that updates are virtually instant.

If a slave server looses connection to the master, it will retry connecting periodically (default 60 seconds, --master-connect-retry option to change), and when it gets back in touch it will catch up.

Master servers do not maintain a list of known slaves, let alone a note of whether or not they're up to date.

You are STRONGLY advised not to edit any of these files directly, but rather to use commands such as CHANGE MASTER TO ..


See also MySQL - our FAQ

Please note that articles in this section of our web site were current and correct to the best of our ability when published, but by the nature of our business may go out of date quite quickly. The quoting of a price, contract term or any other information in this area of our website is NOT an offer to supply now on those terms - please check back via our main web site

MySQL - Designing an SQL Database System
  [59] - ()
  [361] - ()
  [375] - ()
  [494] - ()
  [515] - ()
  [666] - ()
  [918] - ()
  [937] - ()
  [945] - ()
  [1423] - ()
  [1575] - ()
  [1771] - ()
  [2053] - ()
  [2085] - ()
  [2204] - ()
  [2749] - ()
  [3270] - ()
  [3361] - ()
  [3494] - ()
  [4426] - ()

Replication in MySQL
  [2210] - ()

resource index - MySQL
Solutions centre home page

You'll find shorter technical items at The Horse's Mouth and delegate's questions answered at the Opentalk forum.

At Well House Consultants, we provide training courses on subjects such as Ruby, Lua, Perl, Python, Linux, C, C++, Tcl/Tk, Tomcat, PHP and MySQL. We're asked (and answer) many questions, and answers to those which are of general interest are published in this area of our site.