Jump to Navigation

MySQL: Setting up replication

This is a simple guide to setting up replication between MySQL servers.

Assumptions

  • You are using MySQL 5.5 or later;
  • The master server is currently not being used and is not yet configured to use binlogs;
  • The slave server contains a perfect copy of the master server databases.

Procedure

On the master side, make sure the following statements are included in the /etc/my.cnf file:

# Insert within the [mysqld] container:
# server-id can be anything, as long as it is unique within your replication structure
server-id=1
# You can optionally specify a logfile name here (see below)
log-bin

Restart mysql after changing the my.cnf file.

On the master side, create a mysql user with REPLICATION SLAVE permissions:

# Use a sufficiently capable MySQL user; root will usually do. 
# Specify a real password for production use. You can use any valid user name here.
CREATE USER repl IDENTIFIED by 'repl';
GRANT REPLICATION SLAVE ON *.* TO repl;

On the slave side, make sure the following statements are included in the /etc/my.cnf file:

# Insert within the [mysqld] container:
# server-id can be anything, as long as it is unique within your replication structure
server-id=2

Restart mysql after changing the my.cnf file.

Check that you can connect from the slave to the master, for example by trying:

# On the slave; use the host name of the master here.
telnet master 3306
# If you get a Connected to message, all is well; if it just hangs, you may have an issue

If connection fails, you may have a firewall problem, or perhaps skip-networking is set on the master or even bind-address=127.0.0.1. Resolve that first.

On the slave side, specify the connection to the master and start replicating:

# Use a sufficiently capable MySQL user; root will usually do. 
# Specify the correct hostname and port of the master,
# as well as the user you created above with its password.
CHANGE MASTER TO MASTER_HOST='master',MASTER_PORT=3306,
MASTER_USER='repl',MASTER_PASSWORD='repl';
START SLAVE

Relay host

If you have a server that is both a slave and a master for other slaves, use the following in the my.sql:

# Insert within the [mysqld] container:
# server-id can be anything, as long as it is unique within your replication structure
server-id=2
# You can optionally specify a logfile name here (see below)
log-bin
log-slave-updates

Using GTIDs

Global transaction IDs make it easier to administrate your replication structure. They are introduced in MySQL 5.6.

Let the slave stop replicating first:

# Use a sufficiently capable MySQL user; root will usually do. 
STOP SLAVE;

To use GTIDs, add the following on the master side (besides the things outlined above):

 # Insert within the [mysqld] container:
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates

Restart mysql after changing the my.cnf file.

On the slave side, add:

# Insert within the [mysqld] container:
log-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates

Restart mysql after changing the my.cnf file. The slave will now automatically start.

On a relay host, you just have to add:

# Insert within the [mysqld] container:
gtid_mode=ON
enforce_gtid_consistency

Again, restart mysql after changing the my.cnf file.

Replication type

The default replication type is statement. This records the MySQL statements executed on masters and executes the same statements on slaves. This is not optimal, as it is possible to construct statements that will not have the same result on different servers.

There are two alternatives: row or mixed. Row based replication records which rows were changed by an SQL statement on the master and sends that to the slaves. Mixed replication tries to use statement based replication where possible and falls back on row based when needed.

Always use mixed instead of statement. Use row if you mostly use complicated queries that change only a few rows at a time; use mixed if you have simple queries that change lots of rows at once.

Setting on master or relay servers:

# Insert within the [mysqld] container:
# Select the replication type (default: statement; recommended: mixed or row)
binlog-format=mixed

Specifying file names

There are several additional settings in the my.sql you can use on a master or slave. All relative file names are placed in the data directory (often /var/lib/mysql).

Settings on master or relay servers:

# Insert within the [mysqld] container:
# Base name of the binary log files (default: hostname-bin)
log-bin=name
# Name of the binary log index file (default: hostname-bin.index)
log-bin-index=name

Settings on slave or relay servers:

# Insert within the [mysqld] container:
# The base name of the relay log files (default: hostname-relay-bin)
relay-log=name
# The name of the relay log index file (default: hostname-relay-bin.index)
relay-log-index=name
# The name of the master info file (default: master.info)
# Alternatively, use the option master-info-repository to put the data into the mysql database
master-info-file=name
# The name of the relay log info file (default: relay-log.info)
# Alternatively, use the option relay-log-info-repository to put the data into the mysql database
relay-log-info-file=name
# Location of the temporary directory for load file statements (default: $TMPDIR)
slave-load-temp-dir=dirname
Tags:


Technical_article | by Dr. Radut