<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 15. Replication</title><link rel="stylesheet" href="mysql-html.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="prev" href="ha-overview.html" title="Chapter 14. High Availability, Scalability, and DRBD"><link rel="next" href="mysql-cluster.html" title="Chapter 16. MySQL Cluster"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Chapter 15. Replication</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="ha-overview.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="mysql-cluster.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="replication"></a>Chapter 15. Replication</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="replication.html#replication-configuration">15.1. Replication Configuration</a></span></dt><dd><dl><dt><span class="section"><a href="replication.html#replication-howto">15.1.1. How to Set Up Replication</a></span></dt><dt><span class="section"><a href="replication.html#replication-options">15.1.2. Replication Startup Options and Variables</a></span></dt><dt><span class="section"><a href="replication.html#replication-administration">15.1.3. Common Replication Administration Tasks</a></span></dt></dl></dd><dt><span class="section"><a href="replication.html#replication-solutions">15.2. Replication Solutions</a></span></dt><dd><dl><dt><span class="section"><a href="replication.html#replication-solutions-backups">15.2.1. Using Replication for Backups</a></span></dt><dt><span class="section"><a href="replication.html#replication-solutions-diffengines">15.2.2. Using Replication with Different Master and Slave Storage Engines</a></span></dt><dt><span class="section"><a href="replication.html#replication-solutions-scaleout">15.2.3. Using Replication for Scale-Out</a></span></dt><dt><span class="section"><a href="replication.html#replication-solutions-partitioning">15.2.4. Replicating Different Databases to Different Slaves</a></span></dt><dt><span class="section"><a href="replication.html#replication-solutions-performance">15.2.5. Improving Replication Performance</a></span></dt><dt><span class="section"><a href="replication.html#replication-solutions-switch">15.2.6. Switching Masters During Failover</a></span></dt><dt><span class="section"><a href="replication.html#replication-solutions-ssl">15.2.7. Setting Up Replication Using SSL</a></span></dt></dl></dd><dt><span class="section"><a href="replication.html#replication-notes">15.3. Replication Notes and Tips</a></span></dt><dd><dl><dt><span class="section"><a href="replication.html#replication-features">15.3.1. Replication Features and Issues</a></span></dt><dt><span class="section"><a href="replication.html#replication-compatibility">15.3.2. Replication Compatibility Between MySQL Versions</a></span></dt><dt><span class="section"><a href="replication.html#replication-upgrade">15.3.3. Upgrading a Replication Setup</a></span></dt><dt><span class="section"><a href="replication.html#replication-faq">15.3.4. Replication FAQ</a></span></dt><dt><span class="section"><a href="replication.html#replication-problems">15.3.5. Troubleshooting Replication</a></span></dt><dt><span class="section"><a href="replication.html#replication-bugs">15.3.6. How to Report Replication Bugs or Problems</a></span></dt></dl></dd><dt><span class="section"><a href="replication.html#replication-implementation">15.4. Replication Implementation Overview</a></span></dt><dd><dl><dt><span class="section"><a href="replication.html#replication-implementation-details">15.4.1. Replication Implementation Details</a></span></dt><dt><span class="section"><a href="replication.html#slave-logs">15.4.2. Replication Relay and Status Files</a></span></dt><dt><span class="section"><a href="replication.html#replication-rules">15.4.3. How Servers Evaluate Replication Rules</a></span></dt></dl></dd></dl></div><a class="indexterm" name="id2927901"></a><a class="indexterm" name="id2927910"></a><a class="indexterm" name="id2927922"></a><a class="indexterm" name="id2927935"></a><p>
Replication enables data from one MySQL database server (called the
master) to be replicated to one or more MySQL database servers
(slaves). Replication is asynchronous - your replication slaves do
not need to be connected permanently to receive updates from the
master, which means that updates can occur over long-distance
connections and even temporary solutions such as a dial-up service.
Depending on the configuration, you can replicate all databases,
selected databases and even selected tables within a database.
</p><p>
The target uses for replication in MySQL include:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Scale-out solutions - spreading the load among multiple slaves
to improve performance. In this environment, all writes and
updates must take place on the master server. Reads, however,
may take place on one or more slaves. This model can improve the
performance of writes (since the master is dedicated to
updates), while dramatically increasing read speed across an
increasing number of slaves.
</p></li><li><p>
Data security - because data is replicated to the slave, and the
slave can pause the replication process, it is possible to run
backup services on the slave without corrupting the
corresponding master data.
</p></li><li><p>
Analytics - live data can be created on the master, while the
analysis of the information can take place on the slave without
affecting the performance of the master.
</p></li><li><p>
Long-distance data distribution - if a branch office would like
to work with a copy of your main data, you can use replication
to create a local copy of the data for their use without
requiring permanent access to the master.
</p></li></ul></div><p>
Replication in MySQL features support for one-way, asynchronous
replication, in which one server acts as the master, while one or
more other servers act as slaves. This is in contrast to the
<span class="emphasis"><em>synchronous</em></span> replication which is a
characteristic of MySQL Cluster (see
<a href="mysql-cluster.html" title="Chapter 16. MySQL Cluster">Chapter 16, <i>MySQL Cluster</i></a>).
</p><p>
There are a number of solutions available for setting up replication
between two servers, but the best method to use depends on the
presence of data and the engine types you are using. For more
information on the available options, see
<a href="replication.html#replication-howto" title="15.1.1. How to Set Up Replication">Section 15.1.1, “How to Set Up Replication”</a>.
</p><p>
Replication is controlled through a number of different options and
variables. These control the core operation of the replication,
timeouts and the databases and filters that can be applied on
databases and tables. For more information on the available options,
see <a href="replication.html#replication-options" title="15.1.2. Replication Startup Options and Variables">Section 15.1.2, “Replication Startup Options and Variables”</a>.
</p><p>
You can use replication to solve a number of different problems,
including problems with performance, supporting the backup of
different databases and for use as part of a larger solution to
alleviate system failures. For information on how to address these
issues, see <a href="replication.html#replication-solutions" title="15.2. Replication Solutions">Section 15.2, “Replication Solutions”</a>.
</p><p>
For notes and tips on how different data types and statements are
treated during replication, including details of replication
features, version compatibility, upgrades, and problems and their
resolution, including an FAQ, see
<a href="replication.html#replication-notes" title="15.3. Replication Notes and Tips">Section 15.3, “Replication Notes and Tips”</a>.
</p><p>
Detailed information on the implementation of replication, how
replication works, the process and contents of the binary log,
background threads and the rules used to decide how statements are
recorded and replication, see
<a href="replication.html#replication-implementation" title="15.4. Replication Implementation Overview">Section 15.4, “Replication Implementation Overview”</a>.
</p><p class="mnmas"><b>MySQL Enterprise</b>
The MySQL Enterprise Monitor provides numerous advisors that give
immediate feedback about replication-related problems. For more
information see <a href="http://www.mysql.com/products/enterprise/advisors.html" target="_top">http://www.mysql.com/products/enterprise/advisors.html</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="replication-configuration"></a>15.1. Replication Configuration</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="replication.html#replication-howto">15.1.1. How to Set Up Replication</a></span></dt><dt><span class="section"><a href="replication.html#replication-options">15.1.2. Replication Startup Options and Variables</a></span></dt><dt><span class="section"><a href="replication.html#replication-administration">15.1.3. Common Replication Administration Tasks</a></span></dt></dl></div><p>
Replication between servers in MySQL works through the use of the
binary logging mechanism. The MySQL instance operating as the master
(the source of the database changes) writes updates and changes to
the database to the binary log. The information in the binary log is
stored in different logging formats according to the database
changes being recorded. Slaves are configured to read the binary log
from the master and to execute the events in the binary log on the
slave's local database.
</p><p>
The Master is dumb in this scenario. Once binary logging has been
enabled, all statements are recorded in the binary log. Each slave
will receive a copy of the entire contents of the binary log. It is
the responsibility of the slave to decide which statements in the
binary log should be executed; you cannot configure the master to
log only certain events. If you do not specify otherwise, all events
in the master binary log are executed on the slave. If required, you
can configure the slave to only process events that apply to
particular databases or tables.
</p><p>
Slaves keep a record of the binary log file and position within the
log file that they have read and processed from the master. This
means that multiple slaves can be connected to the master and
executing different parts of the same binary log. Because the slaves
control this process, individual slaves can be connected and
disconnected from the server without affecting the master's
operation. Also, because each slave remembers the position within
the binary log, it is possible for slaves to be disconnected,
reconnect and then 'catch up' by continuing from the recorded
position.
</p><p>
Both the master and each slave must be configured with a unique id
(using the <code class="literal">server-id</code> option). In addition, the
slave must be configured with information about the master host
name, log file name and position within that file. These details can
be controlled from within a MySQL session using the <code class="literal">CHANGE
MASTER</code> statement. The details are stored within the
<code class="filename">master.info</code> file.
</p><p>
In this section the setup and configuration required for a
replication environment is described, including step-by-step
instructions for creating a new replication environment. The major
components of this section are:
</p><div class="itemizedlist"><ul type="disc"><li><p>
For a guide to setting up two or more servers for replication
see <a href="replication.html#replication-howto" title="15.1.1. How to Set Up Replication">Section 15.1.1, “How to Set Up Replication”</a>. This section deals with
the setup of the systems and provides methods for copying data
between the master and slaves.
</p></li><li><p>
Detailed information on the different configuration options and
variables that apply to replication is provided in
<a href="replication.html#replication-options" title="15.1.2. Replication Startup Options and Variables">Section 15.1.2, “Replication Startup Options and Variables”</a>.
</p></li><li><p>
Once started, the replication process should require little
administration or monitoring. However, for advice on common
tasks that you may want to executed, see
<a href="replication.html#replication-administration" title="15.1.3. Common Replication Administration Tasks">Section 15.1.3, “Common Replication Administration Tasks”</a>.
</p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-howto"></a>15.1.1. How to Set Up Replication</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="replication.html#replication-howto-repuser">15.1.1.1. Creating a User for Replication</a></span></dt><dt><span class="section"><a href="replication.html#replication-howto-masterbaseconfig">15.1.1.2. Setting the Replication Master Configuration</a></span></dt><dt><span class="section"><a href="replication.html#replication-howto-slavebaseconfig">15.1.1.3. Setting the Replication Slave Configuration</a></span></dt><dt><span class="section"><a href="replication.html#replication-howto-masterstatus">15.1.1.4. Obtaining the Master Replication Information</a></span></dt><dt><span class="section"><a href="replication.html#replication-howto-mysqldump">15.1.1.5. Creating a Data Snapshot Using <code class="literal">mysqldump</code></a></span></dt><dt><span class="section"><a href="replication.html#replication-howto-rawdata">15.1.1.6. Creating a Data Snapshot Using Raw Data Files</a></span></dt><dt><span class="section"><a href="replication.html#replication-howto-newservers">15.1.1.7. Setting Up Replication with New Master and Slaves</a></span></dt><dt><span class="section"><a href="replication.html#replication-howto-existingdata">15.1.1.8. Setting Up Replication with Existing Data</a></span></dt><dt><span class="section"><a href="replication.html#replication-howto-additionalslaves">15.1.1.9. Introducing Additional Slaves to an Existing Replication Environment</a></span></dt><dt><span class="section"><a href="replication.html#replication-howto-slaveinit">15.1.1.10. Setting the Master Configuration on the Slave</a></span></dt></dl></div><p>
This section describes how to set up complete replication of a
MySQL server. There are a number of different methods for setting
up replication, and the exact method that you use will depend on
how you are setting up replication, and whether you already have
data within your master database.
</p><p>
There are some generic tasks which may be required for all
replication setups:
</p><div class="itemizedlist"><ul type="disc"><li><p>
You may want to create a separate user that will be used by
your slaves to authenticate with the master to read the binary
log for replication. The step is optional. See
<a href="replication.html#replication-howto-repuser" title="15.1.1.1. Creating a User for Replication">Section 15.1.1.1, “Creating a User for Replication”</a>.
</p></li><li><p>
You must configure the master to support the binary log and
configure a unique ID. See
<a href="replication.html#replication-howto-masterbaseconfig" title="15.1.1.2. Setting the Replication Master Configuration">Section 15.1.1.2, “Setting the Replication Master Configuration”</a>.
</p></li><li><p>
You must configure a unique ID for each slave that you want to
connect to the Master. See
<a href="replication.html#replication-howto-slavebaseconfig" title="15.1.1.3. Setting the Replication Slave Configuration">Section 15.1.1.3, “Setting the Replication Slave Configuration”</a>.
</p></li><li><p>
Before starting a data snapshot or the replication process,
you should record the position of the binary log on the
master. You will need this information when configuring the
slave so that the slave knows where within the binary log to
start executing events. See
<a href="replication.html#replication-howto-masterstatus" title="15.1.1.4. Obtaining the Master Replication Information">Section 15.1.1.4, “Obtaining the Master Replication Information”</a>.
</p></li><li><p>
If you already have data on your Master and you want to
synchronize your slave with this base data, then you will need
to create a data snapshot of your database. You can create a
snapshot using <code class="literal">mysqldump</code> (see
<a href="replication.html#replication-howto-mysqldump" title="15.1.1.5. Creating a Data Snapshot Using mysqldump">Section 15.1.1.5, “Creating a Data Snapshot Using <code class="literal">mysqldump</code>”</a>) or by copying
the data files directly (see
<a href="replication.html#replication-howto-rawdata" title="15.1.1.6. Creating a Data Snapshot Using Raw Data Files">Section 15.1.1.6, “Creating a Data Snapshot Using Raw Data Files”</a>).
</p></li><li><p>
You will need to configure the slave with the Master settings,
such as the hostname, login credentials and binary log name
and positions. See
<a href="replication.html#replication-howto-slaveinit" title="15.1.1.10. Setting the Master Configuration on the Slave">Section 15.1.1.10, “Setting the Master Configuration on the Slave”</a>.
</p></li></ul></div><p>
Once you have configured the basic options, you will need to
follow the instructions for your replication setup. A number of
alternatives are provided:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If you are setting up a new MySQL master and one or more
slaves, then you need only set up the configuration, as you
have no data to exchange. For guidance on setting up
replication in this situation, see
<a href="replication.html#replication-howto-newservers" title="15.1.1.7. Setting Up Replication with New Master and Slaves">Section 15.1.1.7, “Setting Up Replication with New Master and Slaves”</a>.
</p></li><li><p>
If you are already running a MySQL server, and therefore
already have data that will need to be transferred to your
slaves before replication starts, have not previously
configured the binary log and are able to shut down your MySQL
server for a short period during the process, see
<a href="replication.html#replication-howto-existingdata" title="15.1.1.8. Setting Up Replication with Existing Data">Section 15.1.1.8, “Setting Up Replication with Existing Data”</a>.
</p></li><li><p>
If you are setting up additional slaves to an existing
replication environment then you can set up the slaves without
affecting the master. See
<a href="replication.html#replication-howto-additionalslaves" title="15.1.1.9. Introducing Additional Slaves to an Existing Replication Environment">Section 15.1.1.9, “Introducing Additional Slaves to an Existing Replication Environment”</a>.
</p></li></ul></div><p>
If you want to administer a MySQL replication setup, we suggest
that you read this entire chapter through and try all statements
mentioned in <a href="sql-syntax.html#replication-master-sql" title="12.6.1. SQL Statements for Controlling Master Servers">Section 12.6.1, “SQL Statements for Controlling Master Servers”</a>, and
<a href="sql-syntax.html#replication-slave-sql" title="12.6.2. SQL Statements for Controlling Slave Servers">Section 12.6.2, “SQL Statements for Controlling Slave Servers”</a>. You should also
familiarize yourself with the replication startup options
described in <a href="replication.html#replication-options" title="15.1.2. Replication Startup Options and Variables">Section 15.1.2, “Replication Startup Options and Variables”</a>.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Note that certain steps within the setup process require the
<code class="literal">SUPER</code> privilege. If you do not have this
privilege then enabling replication may not be possible.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-howto-repuser"></a>15.1.1.1. Creating a User for Replication</h4></div></div></div><p>
Each Slave must connect to the Master using a standard username
and password. The user that you use for this operation can be
any user, providing they have been granted the
<code class="literal">REPLICATION SLAVE</code> privilege.
</p><p>
You do not need to create a specific user for replication.
However, you should be aware that the username and password will
be stored in plain text within the
<code class="literal">master.info</code> file. Therefore you may want to
create a user that only has privileges for the replication
process.
</p><p>
To create a user or grant an existing user the privileges
required for replication use the <code class="literal">GRANT</code>
statement. If you create a user solely for the purposes of
replication then that user only needs the <code class="literal">REPLICATION
SLAVE</code> privilege. For example, to create a user,
<code class="literal">repl</code>, that allows all hosts within the domain
<code class="literal">mydomain.com</code> to connect for replication:
</p><pre class="programlisting">mysql> GRANT REPLICATION SLAVE ON *.*
-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';</pre><p>
See <a href="sql-syntax.html#grant" title="12.5.1.3. GRANT Syntax">Section 12.5.1.3, “<code class="literal">GRANT</code> Syntax”</a>, for more information on the
<code class="literal">GRANT</code> statement.
</p><p>
You may wish to create a different user for each slave, or use
the same user for each slave that needs to connect. As long as
each user that you want to use for the replication process has
the <code class="literal">REPLICATION SLAVE</code> privilege you can
create as many users as you require.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-howto-masterbaseconfig"></a>15.1.1.2. Setting the Replication Master Configuration</h4></div></div></div><p>
For replication to work you <span class="emphasis"><em>must</em></span> enable
binary logging on the master. If binary logging is not enabled,
replication will not be possible as it is the binary log that is
used to exchange data between the master and slaves.
</p><p>
Each server within a replication group must have a unique
<code class="literal">server-id</code>. The server-id is used to identify
individual servers within the group, and must be positive
integer between 1 and (2<sup>32</sup>)-1). How
you organize and select the numbers is entirely up to you.
</p><p>
To configure both these options you will need to shut down your
MySQL server and edit the configuration of the
<code class="filename">my.cnf</code> or <code class="filename">my.ini</code> file.
</p><p>
You will need to add the following options to the configuration
file within the <code class="literal">[mysqld]</code> section. If these
options already exist, but are commented out, uncomment the
options and alter them according to your needs. For example, to
enable binary logging, using a log filename prefix of mysql-bin,
and setting a server ID of 1:
</p><pre class="programlisting">[mysqld]
log-bin=mysql-bin
server-id=1
</pre><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
For the greatest possible durability and consistency in a
replication setup using <code class="literal">InnoDB</code> with
transactions, you should use
<code class="literal">innodb_flush_log_at_trx_commit=1</code> and
<code class="literal">sync_binlog=1</code> in the master
<code class="filename">my.cnf</code> file.
</p></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Ensure that the <code class="literal">skip-networking</code> option has
not been enabled on your replication master. If networking has
been disabled, then your slave will not able to communicate
with the master and replication will fail.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-howto-slavebaseconfig"></a>15.1.1.3. Setting the Replication Slave Configuration</h4></div></div></div><p>
The only option you must configure on the slave is to set the
unique server ID. If this option is not already set, or the
current value conflicts with the value that you have chosen for
the master server, then you should shut down your slave server,
and edit the configuration to specify the server id. For
example:
</p><pre class="programlisting">[mysqld]
server-id=2
</pre><p>
If you are setting up multiple slaves, each one must have a
unique <code class="literal">server-id</code> value that differs from that
of the master and from each of the other slaves. Think of
<code class="literal">server-id</code> values as something similar to IP
addresses: These IDs uniquely identify each server instance in
the community of replication partners.
</p><p>
If you do not specify a <code class="literal">server-id</code> value, it
is set to 1 if you have not defined
<code class="literal">master-host</code>; otherwise it is set to 2. Note
that in the case of <code class="literal">server-id</code> omission, a
master refuses connections from all slaves, and a slave refuses
to connect to a master. Thus, omitting
<code class="literal">server-id</code> is good only for backup with a
binary log.
</p><p>
You do not have to enable binary logging on the slave for
replication to be enabled. However, if you enable binary logging
on the slave then you can use the binary log for data backups
and crash recovery on the slave, and also use the slave as part
of a more complex replication topology.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-howto-masterstatus"></a>15.1.1.4. Obtaining the Master Replication Information</h4></div></div></div><p>
To configure replication on the slave you must determine the
masters current point within the master binary log. You will
need this information so that when the slave starts the
replication process, it is able to start processing events from
the binary log at the correct point.
</p><p>
If you have existing data on your master that you want to
synchronize on your slaves before starting the replication
process, then you must stop processing statements on the master,
obtain the current position, and then dump the data, before
allowing the master to continue executing statements. If you do
not stop the execution of statements then the data dump, the
master status information that you use will not match and you
will end up with inconsistent or corrupted databases on the
slaves.
</p><p>
To get the master status information, follow these steps:
</p><div class="orderedlist"><ol type="1"><li><p>
Start the command line client and flush all tables and block
write statements by executing the <code class="literal">FLUSH TABLES WITH
READ LOCK</code> statement:
</p><pre class="programlisting">mysql> FLUSH TABLES WITH READ LOCK;</pre><p>
For <code class="literal">InnoDB</code> tables, note that
<code class="literal">FLUSH TABLES WITH READ LOCK</code> also blocks
<code class="literal">COMMIT</code> operations.
</p><div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Warning</h3><p>
Leave the client from which you issued the <code class="literal">FLUSH
TABLES</code> statement running so that the read lock
remains in effect. If you exit the client, the lock is
released.
</p></div></li><li><p>
Use the <code class="literal">SHOW MASTER STATUS</code> statement to
determine the current binary log name and offset on the
master:
</p><pre class="programlisting">mysql > <strong class="userinput"><code>SHOW MASTER STATUS;</code></strong>
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73 | test | manual,mysql |
+---------------+----------+--------------+------------------+
</pre><p>
The <code class="literal">File</code> column shows the name of the log
and <code class="literal">Position</code> shows the offset within the
file. In this example, the binary log file is
<code class="literal">mysql-bin.003</code> and the offset is 73.
Record these values. You need them later when you are
setting up the slave. They represent the replication
coordinates at which the slave should begin processing new
updates from the master.
</p><p>
If the master has been running previously without binary
logging enabled, the log name and position values displayed
by <code class="literal">SHOW MASTER STATUS</code> or
<span><strong class="command">mysqldump --master-data</strong></span> will be empty. In
that case, the values that you need to use later when
specifying the slave's log file and position are the empty
string (<code class="literal">''</code>) and <code class="literal">4</code>.
</p></li></ol></div><p>
You now have the information you need to enable the slave to
start reading from the binary log in the correct place to start
replication.
</p><p>
If you have existing data that needs be to synchronized with the
slave before you start replication, leave the client running so
that the lock remains in place and then proceed to
<a href="replication.html#replication-howto-mysqldump" title="15.1.1.5. Creating a Data Snapshot Using mysqldump">Section 15.1.1.5, “Creating a Data Snapshot Using <code class="literal">mysqldump</code>”</a>, or
<a href="replication.html#replication-howto-rawdata" title="15.1.1.6. Creating a Data Snapshot Using Raw Data Files">Section 15.1.1.6, “Creating a Data Snapshot Using Raw Data Files”</a>.
</p><p>
If you are setting up a brand new master and slave replication
group, then you can exit the client and release the locks.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-howto-mysqldump"></a>15.1.1.5. Creating a Data Snapshot Using <code class="literal">mysqldump</code></h4></div></div></div><p>
One way to create a snapshot of the data in an existing master
database is to use the <code class="literal">mysqldump</code> tool. Once
the data dump has been completed, you then import this data into
the slave before starting the replication process.
</p><p>
To obtain a snapshot of the data using
<code class="literal">mysqldump</code>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If you haven't already locked the tables on the server to
prevent queries that update data from executing:
</p><p>
Start the command line client and flush all tables and block
write statements by executing the <code class="literal">FLUSH TABLES WITH
READ LOCK</code> statement:
</p><pre class="programlisting">mysql> FLUSH TABLES WITH READ LOCK;</pre><p>
Remember to use <code class="literal">SHOW MASTER STATUS</code> and
record the binary log details for use when starting up the
slave. The point in time of your snapshot and the binary log
position must match. See
<a href="replication.html#replication-howto-masterstatus" title="15.1.1.4. Obtaining the Master Replication Information">Section 15.1.1.4, “Obtaining the Master Replication Information”</a>.
</p></li><li><p>
In another session, use <code class="literal">mysqldump</code> to
create a dump either of all the databases you want to
replicate, or by selecting specific databases individually.
For example:
</p><pre class="programlisting">shell> mysqldump --all-databases --lock-all-tables >dbdump.db</pre></li><li><p>
An alternative to using a bare dump, is to use the
<code class="literal">--master-data</code> option, which will
automatically append the <code class="literal">CHANGE MASTER</code>
statement required on the slave to start the replication
process.
</p><pre class="programlisting">shell> mysqldump --all-databases --master-data >dbdump.db</pre></li></ul></div><p>
When choosing databases to include in the dump, remember that
you will need to filter out databases on each slave that you do
not want to include in the replication process.
</p><p>
You will need either to copy the dump file to the slave, or to
use the file from the master when connecting remotely to the
slave to import the data.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-howto-rawdata"></a>15.1.1.6. Creating a Data Snapshot Using Raw Data Files</h4></div></div></div><p>
If your database is particularly large then copying the raw data
files may be more efficient than using
<code class="literal">mysqldump</code> and importing the file on each
slave.
</p><p>
However, using this method with tables in storage engines with
complex caching or logging algorithms may not give you a perfect
“<span class="quote">in time</span>” snapshot as cache information and logging
updates may not have been applied, even if you have acquired a
global read lock. How the storage engine responds to this
depends on its crash recovery abilities.
</p><p>
For example, if you are using <code class="literal">InnoDB</code> tables,
you should use the <span><strong class="command"><code class="literal">InnoDB</code> Hot
Backup</strong></span> tool to obtain a consistent snapshot. This tool
records the log name and offset corresponding to the snapshot to
be later used on the slave. <span><strong class="command">Hot Backup</strong></span> is a
non-free (commercial) tool that is not included in the standard
MySQL distribution. See the <span><strong class="command"><code class="literal">InnoDB</code>
Hot Backup</strong></span> home page at
<a href="http://www.innodb.com/hot-backup" target="_top">http://www.innodb.com/hot-backup</a> for detailed
information.
</p><p>
Otherwise, you can obtain a reliable binary snapshot of
<code class="literal">InnoDB</code> tables only after shutting down the
MySQL Server.
</p><p>
To create a raw data snapshot of <code class="literal">MyISAM</code>
tables you can use standard copy tools such as
<span><strong class="command">cp</strong></span> or <span><strong class="command">copy</strong></span>, a remote copy
tool such as <span><strong class="command">scp</strong></span> or <span><strong class="command">rsync</strong></span>
an archiving tool such as <span><strong class="command">zip</strong></span> or
<span><strong class="command">tar</strong></span>, or a file system snapshot tool such as
<span><strong class="command">dump</strong></span>, providing that your MySQL data files
exist on a single filesystem. If you are only replicating
certain databases then make sure you only copy those files that
related to those tables. (For <code class="literal">InnoDB</code>, all
tables in all databases are stored in a single file unless you
have the <code class="option">innodb_file_per_table</code> option enabled.)
</p><p>
You may want to specifically exclude the following files from
your archive:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Files relating to the <code class="literal">mysql</code> database.
</p></li><li><p>
The <code class="filename">master.info</code> file.
</p></li><li><p>
The master's binary log files.
</p></li><li><p>
Any relay log files.
</p></li></ul></div><p>
To get the most consistent results with a raw data snapshot you
should shut down the server during the process, as below:
</p><div class="orderedlist"><ol type="1"><li><p>
Acquire a read lock and get the master's status. See
<a href="replication.html#replication-howto-masterstatus" title="15.1.1.4. Obtaining the Master Replication Information">Section 15.1.1.4, “Obtaining the Master Replication Information”</a>.
</p></li><li><p>
In a separate session, shut down the MySQL server:
</p><pre class="programlisting">shell> mysqladmin shutdown
</pre></li><li><p>
Take a copy of the MySQL data files. Examples are shown
below for common solutions - you need to choose only one of
these solutions:
</p><pre class="programlisting">shell> tar cf <em class="replaceable"><code>/tmp/db.tar</code></em> <em class="replaceable"><code>./data</code></em>
shell> zip -r <em class="replaceable"><code>/tmp/db.zip</code></em> <em class="replaceable"><code>./data</code></em>
shell> rsync --recursive <em class="replaceable"><code>./data</code></em> <em class="replaceable"><code>/tmp/dbdata</code></em>
</pre></li><li><p>
Start up the MySQL instance on the master.
</p></li></ol></div><p>
If you are not using <code class="literal">InnoDB</code> tables, you can
get a snapshot of the system from a master without shutting down
the server as described in the following steps:
</p><div class="orderedlist"><ol type="1"><li><p>
Acquire a read lock and get the master's status. See
<a href="replication.html#replication-howto-masterstatus" title="15.1.1.4. Obtaining the Master Replication Information">Section 15.1.1.4, “Obtaining the Master Replication Information”</a>.
</p></li><li><p>
Take a copy of the MySQL data files. Examples are shown
below for common solutions - you need to choose only one of
these solutions:
</p><pre class="programlisting">shell> tar cf <em class="replaceable"><code>/tmp/db.tar</code></em> <em class="replaceable"><code>./data</code></em>
shell> zip -r <em class="replaceable"><code>/tmp/db.zip</code></em> <em class="replaceable"><code>./data</code></em>
shell> rsync --recursive <em class="replaceable"><code>./data</code></em> <em class="replaceable"><code>/tmp/dbdata</code></em>
</pre></li><li><p>
In the client where you acquired the read lock, free the
lock:
</p><pre class="programlisting">mysql> UNLOCK TABLES;
</pre></li></ol></div><p>
Once you have created the archive or copy of the database, you
will need to copy the files to each slave before starting the
slave replication process.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-howto-newservers"></a>15.1.1.7. Setting Up Replication with New Master and Slaves</h4></div></div></div><p>
Setting up replication with a new Master and Slaves (i.e. with
no existing data) is the easiest and most straightforward method
for setting up replication.
</p><p>
You can also use this method if you are setting up new servers
and have an existing dump of the databases that you want to load
into your replication configuration. By loading the data onto a
new master, the data will be automatically replicated to the
slaves.
</p><p>
To set up replication between a new master and slave:
</p><div class="orderedlist"><ol type="1"><li><p>
Configure the MySQL master with the necessary configuration
properties. See
<a href="replication.html#replication-howto-masterbaseconfig" title="15.1.1.2. Setting the Replication Master Configuration">Section 15.1.1.2, “Setting the Replication Master Configuration”</a>.
</p></li><li><p>
Start up the MySQL master.
</p></li><li><p>
Setup a user, see
<a href="replication.html#replication-howto-repuser" title="15.1.1.1. Creating a User for Replication">Section 15.1.1.1, “Creating a User for Replication”</a>.
</p></li><li><p>
Obtain the master status information. See
<a href="replication.html#replication-howto-masterstatus" title="15.1.1.4. Obtaining the Master Replication Information">Section 15.1.1.4, “Obtaining the Master Replication Information”</a>.
</p></li><li><p>
Free the read lock:
</p><pre class="programlisting">mysql> UNLOCK TABLES;</pre></li><li><p>
On the slave, edit the MySQL configuration. See
<a href="replication.html#replication-howto-slavebaseconfig" title="15.1.1.3. Setting the Replication Slave Configuration">Section 15.1.1.3, “Setting the Replication Slave Configuration”</a>.
</p></li><li><p>
Start up the MySQL slave.
</p></li><li><p>
Execute the <code class="literal">CHANGE MASTER</code> command to set
the master replication server configuration.
</p></li></ol></div><p>
Because there is no data to load or exchange on a new server
configuration you do not need to copy or import any information.
</p><p>
If you are setting up a new replication environment using the
data from an existing database server, you will now need to run
the dump file on the master. The database updates will
automatically be propagated to the slaves:
</p><pre class="programlisting">shell> mysql -h master < fulldb.dump</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-howto-existingdata"></a>15.1.1.8. Setting Up Replication with Existing Data</h4></div></div></div><p>
When setting up replication with existing data, you will need to
decide how best to get the data from the master to the slave
before starting the replication service.
</p><p>
The basic process for setting up replication with existing data
is as follows:
</p><div class="orderedlist"><ol type="1"><li><p>
If you have not already configured the
<code class="option">server-id</code> and binary logging, you will need
to shut down your master to configure these options. See
<a href="replication.html#replication-howto-masterbaseconfig" title="15.1.1.2. Setting the Replication Master Configuration">Section 15.1.1.2, “Setting the Replication Master Configuration”</a>.
</p><p>
If you have to shut down your master database, then this is
a good opportunity to take a snapshot of the database. You
should obtain the master status (see
<a href="replication.html#replication-howto-masterstatus" title="15.1.1.4. Obtaining the Master Replication Information">Section 15.1.1.4, “Obtaining the Master Replication Information”</a>) before
taking the database down, updating the configuration and
taking a snapshot. For information on how to create a
snapshot using raw data files, see
<a href="replication.html#replication-howto-rawdata" title="15.1.1.6. Creating a Data Snapshot Using Raw Data Files">Section 15.1.1.6, “Creating a Data Snapshot Using Raw Data Files”</a>.
</p></li><li><p>
If your server is already correctly configured, obtain the
master status (see
<a href="replication.html#replication-howto-masterstatus" title="15.1.1.4. Obtaining the Master Replication Information">Section 15.1.1.4, “Obtaining the Master Replication Information”</a>) and then
use <code class="literal">mysqldump</code> to take a snapshot (see
<a href="replication.html#replication-howto-mysqldump" title="15.1.1.5. Creating a Data Snapshot Using mysqldump">Section 15.1.1.5, “Creating a Data Snapshot Using <code class="literal">mysqldump</code>”</a>) or take a raw
snapshot of the live database using the guide in
<a href="replication.html#replication-howto-rawdata" title="15.1.1.6. Creating a Data Snapshot Using Raw Data Files">Section 15.1.1.6, “Creating a Data Snapshot Using Raw Data Files”</a>.
</p></li><li><p>
With the MySQL master running, create a user to be used by
the slave when connecting to the master during replication.
See <a href="replication.html#replication-howto-repuser" title="15.1.1.1. Creating a User for Replication">Section 15.1.1.1, “Creating a User for Replication”</a>.
</p></li><li><p>
Update the configuration of the slave, see
<a href="replication.html#replication-howto-slavebaseconfig" title="15.1.1.3. Setting the Replication Slave Configuration">Section 15.1.1.3, “Setting the Replication Slave Configuration”</a>.
</p></li><li><p>
The next step depends on how you created the snapshot of
data on the master.
</p><p>
If you used <span><strong class="command">mysqldump</strong></span>:
</p><div class="orderedlist"><ol type="a"><li><p>
Startup the slave, skipping replication by using the
<code class="literal">--skip-slave</code> option.
</p></li><li><p>
Import the dump file:
</p><pre class="programlisting">shell> mysql < fulldb.dump</pre></li></ol></div><p>
If you created a snapshot using the raw data files:
</p><div class="orderedlist"><ol type="a"><li><p>
Extract the data files into your slave data directory.
For example:
</p><pre class="programlisting">shell> tar xvf dbdump.tar</pre><p>
You may need to set permissions and ownership on the
files to match the configuration of your slave.
</p></li><li><p>
Startup the slave, skipping replication by using the
<code class="literal">--skip-slave</code> option.
</p></li></ol></div></li><li><p>
Configure the slave with the master status information. This
will tell the slave the binary log file and position within
the file where replication needs to start, and configure the
login credentials and hostname of the master. For more
information on the statement required, see
<a href="replication.html#replication-howto-slaveinit" title="15.1.1.10. Setting the Master Configuration on the Slave">Section 15.1.1.10, “Setting the Master Configuration on the Slave”</a>.
</p></li><li><p>
Start the slave threads:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>START SLAVE;</code></strong>
</pre></li></ol></div><p>
After you have performed this procedure, the slave should
connect to the master and catch up on any updates that have
occurred since the snapshot was taken.
</p><p>
If you have forgotten to set the <code class="literal">server-id</code>
option for the master, slaves cannot connect to it.
</p><p>
If you have forgotten to set the <code class="literal">server-id</code>
option for the slave, you get the following error in the slave's
error log:
</p><pre class="programlisting">Warning: You should set server-id to a non-0 value if master_host
is set; we will force server id to 2, but this MySQL server will
not act as a slave.
</pre><p>
You also find error messages in the slave's error log if it is
not able to replicate for any other reason.
</p><p>
Once a slave is replicating, you can find in its data directory
one file named <code class="filename">master.info</code> and another
named <code class="filename">relay-log.info</code>. The slave uses these
two files to keep track of how much of the master's binary log
it has processed. Do <span class="emphasis"><em>not</em></span> remove or edit
these files unless you know exactly what you are doing and fully
understand the implications. Even in that case, it is preferred
that you use the <code class="literal">CHANGE MASTER TO</code> statement
to change replication parameters. The slave will use the values
specified in the statement to update the status files
automatically.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
The content of <code class="filename">master.info</code> overrides some
of the server options specified on the command line or in
<code class="filename">my.cnf</code>. See
<a href="replication.html#replication-options" title="15.1.2. Replication Startup Options and Variables">Section 15.1.2, “Replication Startup Options and Variables”</a>, for more details.
</p></div><p>
Once you have a snapshot of the master, you can use it to set up
other slaves by following the slave portion of the procedure
just described. You do not need to take another snapshot of the
master; you can use the same one for each slave.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-howto-additionalslaves"></a>15.1.1.9. Introducing Additional Slaves to an Existing Replication Environment</h4></div></div></div><p>
If you want to add another slave to the existing replication
configuration then you can do so without stopping the master.
Instead, you duplicate the settings on the slaves.
</p><p>
To duplicate the slave:
</p><div class="orderedlist"><ol type="1"><li><p>
Shut down the existing slave:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqladmin shutdown</code></strong>
</pre></li><li><p>
Copy the data directory from the existing slave to the new
slave. You can do this by creating an archive using
<span><strong class="command">tar</strong></span> or <code class="literal">WinZip</code>, or by
performing a direct copy using a tool such as
<span><strong class="command">cp</strong></span> or <span><strong class="command">rsync</strong></span>. Ensure
you also copy the log files and relay log files.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
A common problem that is encountered when adding new
replication slaves is that the new slave fails with a
series of warning and error messages like these:
</p><pre class="programlisting">071118 16:44:10 [Warning] Neither --relay-log nor --relay-log-index were used; so
replication may break when this MySQL server acts as a slave and has his hostname
changed!! Please use '--relay-log=<em class="replaceable"><code>new_slave_hostname</code></em>-relay-bin' to avoid this problem.
<span class="errortext">071118 16:44:10 [ERROR] Failed to open the relay log './<em class="replaceable"><code>old_slave_hostname</code></em>-relay-bin.003525'
(relay_log_pos 22940879)</span>
071118 16:44:10 <span class="errortext">[ERROR] Could not find target log during relay log initialization</span>
071118 16:44:10 <span class="errortext">[ERROR] Failed to initialize the master info structure</span>
</pre><p>
This is due to the fact that, if the
<code class="option">--relay-log</code> option is not specified, the
relay log files contain the hostname as part of their
filenames. (This is also true of the relay log index file
if the <code class="option">--relay-log-index</code> option is not
used. See <a href="replication.html#replication-options" title="15.1.2. Replication Startup Options and Variables">Section 15.1.2, “Replication Startup Options and Variables”</a>, for more
information about these options.)
</p><p>
To avoid this problem, use the same value for
<code class="option">--relay-log</code> on the new slave that was
used on the existing slave. (If this option was not set
explicitly on the existing slave, use
<code class="literal"><em class="replaceable"><code>existing_slave_hostname</code></em>-relay-bin</code>.)
If this is not feasible, then copy the existing
slave's relay log index file to the new slave and set
the <code class="option">--relay-log-index</code> option on the new
slave to match what was used on the existing slave. (If
this option was not set explicitly on the existing slave,
use
<code class="literal"><em class="replaceable"><code>existing_slave_hostname</code></em>-relay-bin.index</code>.)
Alternatively — if you have already tried to start
the new slave (after following the remaining steps in this
section) and have encountered errors like those described
previously — then perform the following steps:
</p><div class="orderedlist"><ol type="a"><li><p>
If you have not already done so, issue a
<code class="literal">STOP SLAVE</code> on the new slave.
</p><p>
If you have already started the existing slave
again, issue a <code class="literal">STOP SLAVE</code> on the
existing slave as well.
</p></li><li><p>
Copy the contents of the existing slave's relay
log index file into the the new slave's relay
log index file, making sure to overwrite any content
already in the file.
</p></li><li><p>
Proceed with the remaining steps in this section.
</p></li></ol></div><p>
</p></div></li><li><p>
Copy the <code class="filename">master.info</code> and
<code class="filename">relay.info</code> files from the existing
slave to the new slave. These files hold the current log
positions.
</p></li><li><p>
Start the existing slave.
</p></li><li><p>
On the new slave, edit the configuration and the give the
new slave a new unique <code class="literal">server-id</code>.
</p></li><li><p>
Start the new slave; the <code class="filename">master.info</code>
file options will be used to start the replication process.
</p></li></ol></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-howto-slaveinit"></a>15.1.1.10. Setting the Master Configuration on the Slave</h4></div></div></div><p>
To set up the slave to communicate with the master for
replication, you must tell the slave the necessary connection
information. To do this, execute the following statement on the
slave, replacing the option values with the actual values
relevant to your system:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CHANGE MASTER TO</code></strong>
-> <strong class="userinput"><code>MASTER_HOST='<em class="replaceable"><code>master_host_name</code></em>',</code></strong>
-> <strong class="userinput"><code>MASTER_USER='<em class="replaceable"><code>replication_user_name</code></em>',</code></strong>
-> <strong class="userinput"><code>MASTER_PASSWORD='<em class="replaceable"><code>replication_password</code></em>',</code></strong>
-> <strong class="userinput"><code>MASTER_LOG_FILE='<em class="replaceable"><code>recorded_log_file_name</code></em>',</code></strong>
-> <strong class="userinput"><code>MASTER_LOG_POS=<em class="replaceable"><code>recorded_log_position</code></em>;</code></strong>
</pre><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Replication cannot use Unix socket files. You must be able to
connect to the master MySQL server using TCP/IP.
</p></div><p>
The following table shows the maximum allowable length for the
string-valued options:
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><code class="literal">MASTER_HOST</code></td><td>60</td></tr><tr><td><code class="literal">MASTER_USER</code></td><td>16</td></tr><tr><td><code class="literal">MASTER_PASSWORD</code></td><td>32</td></tr><tr><td><code class="literal">MASTER_LOG_FILE</code></td><td>255</td></tr></tbody></table></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-options"></a>15.1.2. Replication Startup Options and Variables</h3></div></div></div><p>
This section describes the options that you can use on slave
replication servers. You can specify these options either on the
command line or in an option file.
</p><p>
On the master and each slave, you must use the
<code class="literal">server-id</code> option to establish a unique
replication ID. For each server, you should pick a unique positive
integer in the range from 1 to 2<sup>32</sup>
– 1, and each ID must be different from every other ID.
Example: <code class="literal">server-id=3</code>
</p><p>
Options that you can use on the master server for controlling
binary logging are described in <a href="server-administration.html#binary-log" title="5.10.3. The Binary Log">Section 5.10.3, “The Binary Log”</a>.
</p><p>
Some slave server replication options are handled in a special
way, in the sense that each is ignored if a
<code class="filename">master.info</code> file exists when the slave starts
and contains a value for the option. The following options are
handled this way:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="option">--master-host</code>
</p></li><li><p>
<code class="option">--master-user</code>
</p></li><li><p>
<code class="option">--master-password</code>
</p></li><li><p>
<code class="option">--master-port</code>
</p></li><li><p>
<code class="option">--master-connect-retry</code>
</p></li><li><p>
<code class="option">--master-ssl</code>
</p></li><li><p>
<code class="option">--master-ssl-ca</code>
</p></li><li><p>
<code class="option">--master-ssl-capath</code>
</p></li><li><p>
<code class="option">--master-ssl-cert</code>
</p></li><li><p>
<code class="option">--master-ssl-cipher</code>
</p></li><li><p>
<code class="option">--master-ssl-key</code>
</p></li></ul></div><p>
The <code class="filename">master.info</code> file format in MySQL
5.0 includes values corresponding to the SSL options.
In addition, the file format includes as its first line the number
of lines in the file. (See <a href="replication.html#slave-logs" title="15.4.2. Replication Relay and Status Files">Section 15.4.2, “Replication Relay and Status Files”</a>.) If you
upgrade an older server (before MySQL 4.1.1) to a newer version,
the new server upgrades the <code class="filename">master.info</code> file
to the new format automatically when it starts. However, if you
downgrade a newer server to an older version, you should remove
the first line manually before starting the older server for the
first time.
</p><p>
If no <code class="filename">master.info</code> file exists when the slave
server starts, it uses the values for those options that are
specified in option files or on the command line. This occurs when
you start the server as a replication slave for the very first
time, or when you have run <code class="literal">RESET SLAVE</code> and then
have shut down and restarted the slave.
</p><p>
If the <code class="filename">master.info</code> file exists when the slave
server starts, the server uses its contents and ignores any
options that correspond to the values listed in the file. Thus, if
you start the slave server with different values of the startup
options that correspond to values in the
<code class="filename">master.info</code> file, the different values have
no effect, because the server continues to use the
<code class="filename">master.info</code> file. To use different values,
you must either restart after removing the
<code class="filename">master.info</code> file or (preferably) use the
<code class="literal">CHANGE MASTER TO</code> statement to reset the values
while the slave is running.
</p><p>
Suppose that you specify this option in your
<code class="filename">my.cnf</code> file:
</p><pre class="programlisting">[mysqld]
master-host=<em class="replaceable"><code>some_host</code></em>
</pre><p>
The first time you start the server as a replication slave, it
reads and uses that option from the <code class="filename">my.cnf</code>
file. The server then records the value in the
<code class="filename">master.info</code> file. The next time you start the
server, it reads the master host value from the
<code class="filename">master.info</code> file only and ignores the value
in the option file. If you modify the <code class="filename">my.cnf</code>
file to specify a different master host of
<em class="replaceable"><code>some_other_host</code></em>, the change still has
no effect. You should use <code class="literal">CHANGE MASTER TO</code>
instead.
</p><p class="mnmas"><b>MySQL Enterprise</b>
For expert advice regarding master startup options subscribe to
the MySQL Enterprise Monitor. For more information see
<a href="http://www.mysql.com/products/enterprise/advisors.html" target="_top">http://www.mysql.com/products/enterprise/advisors.html</a>.
</p><p>
Because the server gives an existing
<code class="filename">master.info</code> file precedence over the startup
options just described, you might prefer not to use startup
options for these values at all, and instead specify them by using
the <code class="literal">CHANGE MASTER TO</code> statement. See
<a href="sql-syntax.html#change-master-to" title="12.6.2.1. CHANGE MASTER TO Syntax">Section 12.6.2.1, “<code class="literal">CHANGE MASTER TO</code> Syntax”</a>.
</p><p>
This example shows a more extensive use of startup options to
configure a slave server:
</p><pre class="programlisting">[mysqld]
server-id=2
master-host=db-master.mycompany.com
master-port=3306
master-user=pertinax
master-password=freitag
master-connect-retry=60
report-host=db-slave.mycompany.com
</pre><p>
The following list describes startup options for controlling
replication. Many of these options can be reset while the server
is running by using the <code class="literal">CHANGE MASTER TO</code>
statement. Others, such as the <code class="option">--replicate-*</code>
options, can be set only when the slave server starts.
</p><div class="itemizedlist"><ul type="disc"><li><p><a name="option_mysqld_log-slave-updates"></a>
<a class="indexterm" name="id2931024"></a>
<a class="indexterm" name="id2931037"></a>
<code class="option">--log-slave-updates</code>
</p><p>
Normally, a slave does not log to its own binary log any
updates that are received from a master server. This option
tells the slave to log the updates performed by its SQL thread
to its own binary log. For this option to have any effect, the
slave must also be started with the <code class="option">--log-bin</code>
option to enable binary logging.
<code class="option">--log-slave-updates</code> is used when you want to
chain replication servers. For example, you might want to set
up replication servers using this arrangement:
</p><pre class="programlisting">A -> B -> C
</pre><p>
Here, A serves as the master for the slave B, and B serves as
the master for the slave C. For this to work, B must be both a
master <span class="emphasis"><em>and</em></span> a slave. You must start both A
and B with <code class="option">--log-bin</code> to enable binary
logging, and B with the <code class="option">--log-slave-updates</code>
option so that updates received from A are logged by B to its
binary log.
</p></li><li><p>
<a class="indexterm" name="id2931106"></a>
<a class="indexterm" name="id2931118"></a>
<code class="option">--log-warnings[=<em class="replaceable"><code>level</code></em>]</code>
</p><p>
This option causes a server to print more messages to the
error log about what it is doing. With respect to replication,
the server generates warnings that it succeeded in
reconnecting after a network/connection failure, and informs
you as to how each slave thread started. This option is
enabled by default; to disable it, use
<code class="option">--skip-log-warnings</code>. Aborted connections are
not logged to the error log unless the value is greater than
1.
</p></li><li><p><a name="option_mysqld_master-connect-retry"></a>
<a class="indexterm" name="id2931169"></a>
<a class="indexterm" name="id2931182"></a>
<code class="option">--master-connect-retry=<em class="replaceable"><code>seconds</code></em></code>
</p><p>
The number of seconds that the slave thread sleeps before
trying to reconnect to the master in case the master goes down
or the connection is lost. The value in the
<code class="filename">master.info</code> file takes precedence if it
can be read. If not set, the default is 60. Connection retries
are not invoked until the slave times out reading data from
the master according to the value of
<code class="option">--slave-net-timeout</code>. The number of
reconnection attempts is limited by the
<code class="option">--master-retry-count</code> option.
</p></li><li><p><a name="option_mysqld_master-host"></a>
<a class="indexterm" name="id2931236"></a>
<a class="indexterm" name="id2931248"></a>
<code class="option">--master-host=<em class="replaceable"><code>host_name</code></em></code>
</p><p>
The hostname or IP number of the master replication server.
The value in <code class="filename">master.info</code> takes precedence
if it can be read. If no master host is specified, the slave
thread does not start.
</p></li><li><p><a name="option_mysqld_master-info-file"></a>
<a class="indexterm" name="id2931289"></a>
<a class="indexterm" name="id2931301"></a>
<code class="option">--master-info-file=<em class="replaceable"><code>file_name</code></em></code>
</p><p>
The name to use for the file in which the slave records
information about the master. The default name is
<code class="filename">master.info</code> in the data directory.
</p></li><li><p><a name="option_mysqld_master-password"></a>
<a class="indexterm" name="id2931341"></a>
<a class="indexterm" name="id2931354"></a>
<code class="option">--master-password=<em class="replaceable"><code>password</code></em></code>
</p><p>
The password of the account that the slave thread uses for
authentication when it connects to the master. The value in
the <code class="filename">master.info</code> file takes precedence if
it can be read. If not set, an empty password is assumed.
</p></li><li><p><a name="option_mysqld_master-port"></a>
<a class="indexterm" name="id2931395"></a>
<a class="indexterm" name="id2931407"></a>
<code class="option">--master-port=<em class="replaceable"><code>port_number</code></em></code>
</p><p>
The TCP/IP port number that the master is listening on. The
value in the <code class="filename">master.info</code> file takes
precedence if it can be read. If not set, the compiled-in
setting is assumed (normally 3306).
</p></li><li><p><a name="option_mysqld_master-retry-count"></a>
<a class="indexterm" name="id2931448"></a>
<a class="indexterm" name="id2931461"></a>
<code class="option">--master-retry-count=<em class="replaceable"><code>count</code></em></code>
</p><p>
The number of times that the slave tries to connect to the
master before giving up. Reconnects are attempted at intervals
set by <code class="option">--master-connect-retry</code> and reconnects
are triggered when data reads by the slave time out according
to the <code class="option">--slave-net-timeout</code> option. The
default value is 86400.
</p></li><li><p><a name="option_mysqld_master-ssl"></a>
<a class="indexterm" name="id2931505"></a>
<a class="indexterm" name="id2931517"></a>
<a class="indexterm" name="id2931530"></a>
<a class="indexterm" name="id2931542"></a>
<a class="indexterm" name="id2931555"></a>
<a class="indexterm" name="id2931568"></a>
<a class="indexterm" name="id2931580"></a>
<a class="indexterm" name="id2931593"></a>
<a class="indexterm" name="id2931606"></a>
<a class="indexterm" name="id2931618"></a>
<a class="indexterm" name="id2931631"></a>
<a class="indexterm" name="id2931644"></a>
<code class="option">--master-ssl</code>,
<code class="option">--master-ssl-ca=<em class="replaceable"><code>file_name</code></em></code>,
<code class="option">--master-ssl-capath=<em class="replaceable"><code>directory_name</code></em></code>,
<code class="option">--master-ssl-cert=<em class="replaceable"><code>file_name</code></em></code>,
<code class="option">--master-ssl-cipher=<em class="replaceable"><code>cipher_list</code></em></code>,
<code class="option">--master-ssl-key=<em class="replaceable"><code>file_name</code></em></code>
</p><p>
These options are used for setting up a secure replication
connection to the master server using SSL. Their meanings are
the same as the corresponding <code class="option">--ssl</code>,
<code class="option">--ssl-ca</code>, <code class="option">--ssl-capath</code>,
<code class="option">--ssl-cert</code>, <code class="option">--ssl-cipher</code>,
<code class="option">--ssl-key</code> options that are described in
<a href="server-administration.html#ssl-options" title="5.8.7.3. SSL Command Options">Section 5.8.7.3, “SSL Command Options”</a>. The values in the
<code class="filename">master.info</code> file take precedence if they
can be read.
</p></li><li><p><a name="option_mysqld_master-user"></a>
<a class="indexterm" name="id2931743"></a>
<a class="indexterm" name="id2931755"></a>
<code class="option">--master-user=<em class="replaceable"><code>user_name</code></em></code>
</p><p>
The username of the account that the slave thread uses for
authentication when it connects to the master. This account
must have the <code class="literal">REPLICATION SLAVE</code> privilege.
The value in the <code class="filename">master.info</code> file takes
precedence if it can be read. If the master username is not
set, the name <code class="literal">test</code> is assumed.
</p></li><li><p><a name="option_mysqld_max-relay-log-size"></a>
<a class="indexterm" name="id2931816"></a>
<a class="indexterm" name="id2931829"></a>
<code class="option">--max-relay-log-size=<em class="replaceable"><code>size</code></em></code>
</p><p>
The size at which the server rotates relay log files
automatically. For more information, see
<a href="replication.html#slave-logs" title="15.4.2. Replication Relay and Status Files">Section 15.4.2, “Replication Relay and Status Files”</a>. The default size is 1GB.
</p></li><li><p><a name="option_mysqld_read-only"></a>
<a class="indexterm" name="id2931867"></a>
<a class="indexterm" name="id2931880"></a>
<code class="option">--read-only</code>
</p><p>
When this option is given, the server allows no updates except
from users that have the <code class="literal">SUPER</code> privilege or
(on a slave server) from updates performed by slave threads.
On a slave server, this can be useful to ensure that the slave
accepts updates only from its master server and not from
clients. As of MySQL 5.0.16, this option does not apply to
<code class="literal">TEMPORARY</code> tables.
</p></li><li><p><a name="option_mysqld_relay-log"></a>
<a class="indexterm" name="id2931927"></a>
<a class="indexterm" name="id2931940"></a>
<code class="option">--relay-log=<em class="replaceable"><code>file_name</code></em></code>
</p><p>
The basename for the relay log. The default basename is
<code class="filename"><em class="replaceable"><code>host_name</code></em>-relay-bin</code>.
The server creates relay log files in sequence by adding a
numeric suffix to the basename. You can specify the option to
create hostname-independent relay log names, or if your relay
logs tend to be big (and you don't want to decrease
<code class="literal">max_relay_log_size</code>) and you need to put
them in some area different from the data directory, or if you
want to increase speed by balancing load between disks.
</p></li><li><p><a name="option_mysqld_relay-log-index"></a>
<a class="indexterm" name="id2931994"></a>
<a class="indexterm" name="id2932006"></a>
<code class="option">--relay-log-index=<em class="replaceable"><code>file_name</code></em></code>
</p><p>
The name to use for the relay log index file. The default name
is
<code class="filename"><em class="replaceable"><code>host_name</code></em>-relay-bin.index</code>
in the data directory, where
<em class="replaceable"><code>host_name</code></em> is the name of the slave
server.
</p></li><li><p><a name="option_mysqld_relay-log-info-file"></a>
<a class="indexterm" name="id2932053"></a>
<a class="indexterm" name="id2932065"></a>
<code class="option">--relay-log-info-file=<em class="replaceable"><code>file_name</code></em></code>
</p><p>
The name to use for the file in which the slave records
information about the relay logs. The default name is
<code class="filename">relay-log.info</code> in the data directory.
</p></li><li><p><a name="option_mysqld_relay-log-purge"></a>
<a class="indexterm" name="id2932112"></a>
<a class="indexterm" name="id2932125"></a>
<code class="option">--relay-log-purge={0|1}</code>
</p><p>
Disable or enable automatic purging of relay logs as soon as
they are not needed any more. The default value is 1
(enabled). This is a global variable that can be changed
dynamically with <code class="literal">SET GLOBAL relay_log_purge =
<em class="replaceable"><code>N</code></em></code>.
</p></li><li><p><a name="option_mysqld_relay-log-space-limit"></a>
<a class="indexterm" name="id2932173"></a>
<a class="indexterm" name="id2932186"></a>
<code class="option">--relay-log-space-limit=<em class="replaceable"><code>size</code></em></code>
</p><p>
This option places an upper limit on the total size in bytes
of all relay logs on the slave. A value of 0 means “<span class="quote">no
limit.</span>” This is useful for a slave server host that has
limited disk space. When the limit is reached, the I/O thread
stops reading binary log events from the master server until
the SQL thread has caught up and deleted some unused relay
logs. Note that this limit is not absolute: There are cases
where the SQL thread needs more events before it can delete
relay logs. In that case, the I/O thread exceeds the limit
until it becomes possible for the SQL thread to delete some
relay logs, because not doing so would cause a deadlock. You
should not set <code class="option">--relay-log-space-limit</code> to
less than twice the value of
<code class="option">--max-relay-log-size</code> (or
<code class="option">--max-binlog-size</code> if
<code class="option">--max-relay-log-size</code> is 0). In that case,
there is a chance that the I/O thread waits for free space
because <code class="option">--relay-log-space-limit</code> is exceeded,
but the SQL thread has no relay log to purge and is unable to
satisfy the I/O thread. This forces the I/O thread to
temporarily ignore <code class="option">--relay-log-space-limit</code>.
</p></li><li><p><a name="option_mysqld_replicate-do-db"></a>
<a class="indexterm" name="id2932263"></a>
<a class="indexterm" name="id2932276"></a>
<code class="option">--replicate-do-db=<em class="replaceable"><code>db_name</code></em></code>
</p><p>
Tell the slave to restrict replication to statements where the
default database (that is, the one selected by
<code class="literal">USE</code>) is <em class="replaceable"><code>db_name</code></em>.
To specify more than one database, use this option multiple
times, once for each database. Note that this does not
replicate cross-database statements such as <code class="literal">UPDATE
<em class="replaceable"><code>some_db.some_table</code></em> SET
foo='bar'</code> while having selected a different database
or no database.
</p><div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Warning</h3><p>
To specify multiple databases you <span class="emphasis"><em>must</em></span>
use multiple instances of this option. Because database
names can contain commas, if you supply a comma separated
list then the list will be treated as the name of a single
database.
</p></div><p>
An example of what does not work as you might expect: If the
slave is started with <code class="option">--replicate-do-db=sales</code>
and you issue the following statements on the master, the
<code class="literal">UPDATE</code> statement is
<span class="emphasis"><em>not</em></span> replicated:
</p><pre class="programlisting">USE prices;
UPDATE sales.january SET amount=amount+1000;
</pre><p>
The main reason for this “<span class="quote">just check the default
database</span>” behavior is that it is difficult from the
statement alone to know whether it should be replicated (for
example, if you are using multiple-table
<code class="literal">DELETE</code> statements or multiple-table
<code class="literal">UPDATE</code> statements that act across multiple
databases). It is also faster to check only the default
database rather than all databases if there is no need.
</p><p>
If you need cross-database updates to work, use
<code class="option">--replicate-wild-do-table=<em class="replaceable"><code>db_name</code></em>.%</code>
instead. See <a href="replication.html#replication-rules" title="15.4.3. How Servers Evaluate Replication Rules">Section 15.4.3, “How Servers Evaluate Replication Rules”</a>.
</p></li><li><p><a name="option_mysqld_replicate-do-table"></a>
<a class="indexterm" name="id2932419"></a>
<a class="indexterm" name="id2932432"></a>
<code class="option">--replicate-do-table=<em class="replaceable"><code>db_name.tbl_name</code></em></code>
</p><p>
Tell the slave thread to restrict replication to the specified
table. To specify more than one table, use this option
multiple times, once for each table. This works for
cross-database updates, in contrast to
<code class="option">--replicate-do-db</code>. See
<a href="replication.html#replication-rules" title="15.4.3. How Servers Evaluate Replication Rules">Section 15.4.3, “How Servers Evaluate Replication Rules”</a>.
</p></li><li><p><a name="option_mysqld_replicate-ignore-db"></a>
<a class="indexterm" name="id2932476"></a>
<a class="indexterm" name="id2932489"></a>
<code class="option">--replicate-ignore-db=<em class="replaceable"><code>db_name</code></em></code>
</p><p>
Tells the slave to not replicate any statement where the
default database (that is, the one selected by
<code class="literal">USE</code>) is <em class="replaceable"><code>db_name</code></em>.
To specify more than one database to ignore, use this option
multiple times, once for each database. You should not use
this option if you are using cross-database updates and you do
not want these updates to be replicated. See
<a href="replication.html#replication-rules" title="15.4.3. How Servers Evaluate Replication Rules">Section 15.4.3, “How Servers Evaluate Replication Rules”</a>.
</p><p class="mnmas"><b>MySQL Enterprise</b>
For expert advice regarding slave startup options subscribe
to the MySQL Enterprise Monitor. For more information see
<a href="http://www.mysql.com/products/enterprise/advisors.html" target="_top">http://www.mysql.com/products/enterprise/advisors.html</a>.
</p><p>
An example of what does not work as you might expect: If the
slave is started with
<code class="option">--replicate-ignore-db=sales</code> and you issue the
following statements on the master, the
<code class="literal">UPDATE</code> statement <span class="emphasis"><em>is</em></span>
replicated:
</p><pre class="programlisting">USE prices;
UPDATE sales.january SET amount=amount+1000;
</pre><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
In the preceding example the statement is replicated because
<code class="option">--replicate-ignore-db</code> only applies to the
default database (set through the <code class="literal">USE</code>
statement). Because the <code class="literal">sales</code> database
was specified explicitly in the statement, the statement has
not been filtered.
</p></div><p>
If you need cross-database updates to work, use
<code class="option">--replicate-wild-ignore-table=<em class="replaceable"><code>db_name</code></em>.%</code>
instead. See <a href="replication.html#replication-rules" title="15.4.3. How Servers Evaluate Replication Rules">Section 15.4.3, “How Servers Evaluate Replication Rules”</a>.
</p></li><li><p><a name="option_mysqld_replicate-ignore-table"></a>
<a class="indexterm" name="id2932633"></a>
<a class="indexterm" name="id2932646"></a>
<code class="option">--replicate-ignore-table=<em class="replaceable"><code>db_name.tbl_name</code></em></code>
</p><p>
Tells the slave thread to not replicate any statement that
updates the specified table, even if any other tables might be
updated by the same statement. To specify more than one table
to ignore, use this option multiple times, once for each
table. This works for cross-database updates, in contrast to
<code class="option">--replicate-ignore-db</code>. See
<a href="replication.html#replication-rules" title="15.4.3. How Servers Evaluate Replication Rules">Section 15.4.3, “How Servers Evaluate Replication Rules”</a>.
</p></li><li><p><a name="option_mysqld_replicate-rewrite-db"></a>
<a class="indexterm" name="id2932693"></a>
<a class="indexterm" name="id2932705"></a>
<code class="option">--replicate-rewrite-db=<em class="replaceable"><code>from_name</code></em>-><em class="replaceable"><code>to_name</code></em></code>
</p><p>
Tells the slave to translate the default database (that is,
the one selected by <code class="literal">USE</code>) to
<em class="replaceable"><code>to_name</code></em> if it was
<em class="replaceable"><code>from_name</code></em> on the master. Only
statements involving tables are affected (not statements such
as <code class="literal">CREATE DATABASE</code>, <code class="literal">DROP
DATABASE</code>, and <code class="literal">ALTER DATABASE</code>),
and only if <em class="replaceable"><code>from_name</code></em> is the
default database on the master. This does not work for
cross-database updates. The database name translation is done
<span class="emphasis"><em>before</em></span> the <code class="option">--replicate-*</code>
rules are tested.
</p><p>
If you use this option on the command line and the
“<span class="quote"><code class="literal">></code></span>” character is special to
your command interpreter, quote the option value. For example:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqld --replicate-rewrite-db="<em class="replaceable"><code>olddb</code></em>-><em class="replaceable"><code>newdb</code></em>"</code></strong>
</pre></li><li><p><a name="option_mysqld_replicate-same-server-id"></a>
<a class="indexterm" name="id2932823"></a>
<a class="indexterm" name="id2932835"></a>
<code class="option">--replicate-same-server-id</code>
</p><p>
To be used on slave servers. Usually you should use the
default setting of 0, to prevent infinite loops caused by
circular replication. If set to 1, the slave does not skip
events having its own server ID. Normally, this is useful only
in rare configurations. Cannot be set to 1 if
<code class="option">--log-slave-updates</code> is used. Note that by
default the slave I/O thread does not even write binary log
events to the relay log if they have the slave's server id
(this optimization helps save disk usage). So if you want to
use <code class="option">--replicate-same-server-id</code>, be sure to
start the slave with this option before you make the slave
read its own events that you want the slave SQL thread to
execute.
</p></li><li><p><a name="option_mysqld_replicate-wild-do-table"></a>
<a class="indexterm" name="id2932885"></a>
<a class="indexterm" name="id2932898"></a>
<code class="option">--replicate-wild-do-table=<em class="replaceable"><code>db_name.tbl_name</code></em></code>
</p><p>
Tells the slave thread to restrict replication to statements
where any of the updated tables match the specified database
and table name patterns. Patterns can contain the
“<span class="quote"><code class="literal">%</code></span>” and
“<span class="quote"><code class="literal">_</code></span>” wildcard characters, which
have the same meaning as for the <code class="literal">LIKE</code>
pattern-matching operator. To specify more than one table, use
this option multiple times, once for each table. This works
for cross-database updates. See
<a href="replication.html#replication-rules" title="15.4.3. How Servers Evaluate Replication Rules">Section 15.4.3, “How Servers Evaluate Replication Rules”</a>.
</p><p>
Example: <code class="option">--replicate-wild-do-table=foo%.bar%</code>
replicates only updates that use a table where the database
name starts with <code class="literal">foo</code> and the table name
starts with <code class="literal">bar</code>.
</p><p>
If the table name pattern is <code class="literal">%</code>, it matches
any table name and the option also applies to database-level
statements (<code class="literal">CREATE DATABASE</code>, <code class="literal">DROP
DATABASE</code>, and <code class="literal">ALTER DATABASE</code>).
For example, if you use
<code class="option">--replicate-wild-do-table=foo%.%</code>,
database-level statements are replicated if the database name
matches the pattern <code class="literal">foo%</code>.
</p><p>
To include literal wildcard characters in the database or
table name patterns, escape them with a backslash. For
example, to replicate all tables of a database that is named
<code class="literal">my_own%db</code>, but not replicate tables from
the <code class="literal">my1ownAABCdb</code> database, you should
escape the “<span class="quote"><code class="literal">_</code></span>” and
“<span class="quote"><code class="literal">%</code></span>” characters like this:
<code class="option">--replicate-wild-do-table=my\_own\%db</code>. If
you're using the option on the command line, you might need to
double the backslashes or quote the option value, depending on
your command interpreter. For example, with the
<span><strong class="command">bash</strong></span> shell, you would need to type
<code class="option">--replicate-wild-do-table=my\\_own\\%db</code>.
</p></li><li><p><a name="option_mysqld_replicate-wild-ignore-table"></a>
<a class="indexterm" name="id2933078"></a>
<a class="indexterm" name="id2933091"></a>
<code class="option">--replicate-wild-ignore-table=<em class="replaceable"><code>db_name.tbl_name</code></em></code>
</p><p>
Tells the slave thread not to replicate a statement where any
table matches the given wildcard pattern. To specify more than
one table to ignore, use this option multiple times, once for
each table. This works for cross-database updates. See
<a href="replication.html#replication-rules" title="15.4.3. How Servers Evaluate Replication Rules">Section 15.4.3, “How Servers Evaluate Replication Rules”</a>.
</p><p>
Example:
<code class="option">--replicate-wild-ignore-table=foo%.bar%</code> does
not replicate updates that use a table where the database name
starts with <code class="literal">foo</code> and the table name starts
with <code class="literal">bar</code>.
</p><p>
For information about how matching works, see the description
of the <code class="option">--replicate-wild-do-table</code> option. The
rules for including literal wildcard characters in the option
value are the same as for
<code class="option">--replicate-wild-ignore-table</code> as well.
</p></li><li><p><a name="option_mysqld_report-host"></a>
<a class="indexterm" name="id2933169"></a>
<a class="indexterm" name="id2933181"></a>
<code class="option">--report-host=<em class="replaceable"><code>slave_name</code></em></code>
</p><p>
The hostname or IP number of the slave to be reported to the
master during slave registration. This value appears in the
output of <code class="literal">SHOW SLAVE HOSTS</code> on the master
server. Leave the value unset if you do not want the slave to
register itself with the master. Note that it is not
sufficient for the master to simply read the IP number of the
slave from the TCP/IP socket after the slave connects. Due to
NAT and other routing issues, that IP may not be valid for
connecting to the slave from the master or other hosts.
</p></li><li><p><a name="option_mysqld_report-password"></a>
<a class="indexterm" name="id2933228"></a>
<a class="indexterm" name="id2933241"></a>
<code class="option">--report-password=<em class="replaceable"><code>password</code></em></code>
</p><p>
The account password of the slave to be reported to the master
during slave registration. This value appears in the output of
<code class="literal">SHOW SLAVE HOSTS</code> on the master server if
the <code class="option">--show-slave-auth-info</code> option is given.
</p></li><li><p><a name="option_mysqld_report-port"></a>
<a class="indexterm" name="id2933293"></a>
<a class="indexterm" name="id2933306"></a>
<code class="option">--report-port=<em class="replaceable"><code>slave_port_num</code></em></code>
</p><p>
The TCP/IP port number for connecting to the slave, to be
reported to the master during slave registration. Set this
only if the slave is listening on a non-default port or if you
have a special tunnel from the master or other clients to the
slave. If you are not sure, do not use this option.
</p></li><li><p><a name="option_mysqld_report-user"></a>
<a class="indexterm" name="id2933343"></a>
<a class="indexterm" name="id2933355"></a>
<code class="option">--report-user=<em class="replaceable"><code>user_name</code></em></code>
</p><p>
The account username of the slave to be reported to the master
during slave registration. This value appears in the output of
<code class="literal">SHOW SLAVE HOSTS</code> on the master server if
the <code class="option">--show-slave-auth-info</code> option is given.
</p></li><li><p><a name="option_mysqld_show-slave-auth-info"></a>
<a class="indexterm" name="id2933400"></a>
<a class="indexterm" name="id2933412"></a>
<code class="option">--show-slave-auth-info</code>
</p><p>
Display slave usernames and passwords in the output of
<code class="literal">SHOW SLAVE HOSTS</code> on the master server for
slaves started with the <code class="option">--report-user</code> and
<code class="option">--report-password</code> options.
</p></li><li><p><a name="option_mysqld_skip-slave-start"></a>
<a class="indexterm" name="id2933466"></a>
<a class="indexterm" name="id2933478"></a>
<code class="option">--skip-slave-start</code>
</p><p>
Tells the slave server not to start the slave threads when the
server starts. To start the threads later, use a
<code class="literal">START SLAVE</code> statement.
</p></li><li><p>
<a class="indexterm" name="id2933520"></a>
<a class="indexterm" name="id2933533"></a>
<code class="option">--slave_compressed_protocol={0|1}</code>
</p><p>
If this option is set to 1, use compression for the
slave/master protocol if both the slave and the master support
it. The default is 0 (no compression).
</p></li><li><p><a name="option_mysqld_slave-load-tmpdir"></a>
<a class="indexterm" name="id2933565"></a>
<a class="indexterm" name="id2933578"></a>
<code class="option">--slave-load-tmpdir=<em class="replaceable"><code>file_name</code></em></code>
</p><p>
The name of the directory where the slave creates temporary
files. This option is by default equal to the value of the
<code class="literal">tmpdir</code> system variable. When the slave SQL
thread replicates a <code class="literal">LOAD DATA INFILE</code>
statement, it extracts the file to be loaded from the relay
log into temporary files, and then loads these into the table.
If the file loaded on the master is huge, the temporary files
on the slave are huge, too. Therefore, it might be advisable
to use this option to tell the slave to put temporary files in
a directory located in some filesystem that has a lot of
available space. In that case, the relay logs are huge as
well, so you might also want to use the
<code class="option">--relay-log</code> option to place the relay logs in
that filesystem.
</p><p>
The directory specified by this option should be located in a
disk-based filesystem (not a memory-based filesystem) because
the temporary files used to replicate <code class="literal">LOAD DATA
INFILE</code> must survive machine restarts. The directory
also should not be one that is cleared by the operating system
during the system startup process.
</p></li><li><p><a name="option_mysqld_slave-net-timeout"></a>
<a class="indexterm" name="id2933654"></a>
<a class="indexterm" name="id2933666"></a>
<code class="option">--slave-net-timeout=<em class="replaceable"><code>seconds</code></em></code>
</p><p>
The number of seconds to wait for more data from the master
before the slave considers the connection broken, aborts the
read, and tries to reconnect. The first retry occurs
immediately after the timeout. The interval between retries is
controlled by the <code class="option">--master-connect-retry</code>
option and the number of reconnection attempts is limited by
the <code class="option">--master-retry-count</code> option. The default
is 3600 seconds (one hour).
</p></li><li><p><a name="option_mysqld_slave_skip_errors"></a>
<a class="indexterm" name="id2933713"></a>
<a class="indexterm" name="id2933726"></a>
<code class="option">--slave-skip-errors=[<em class="replaceable"><code>err_code1</code></em>,<em class="replaceable"><code>err_code2</code></em>,...|all]</code>
</p><p>
Normally, replication stops when an error occurs on the slave.
This gives you the opportunity to resolve the inconsistency in
the data manually. This option tells the slave SQL thread to
continue replication when a statement returns any of the
errors listed in the option value.
</p><p>
Do not use this option unless you fully understand why you are
getting errors. If there are no bugs in your replication setup
and client programs, and no bugs in MySQL itself, an error
that stops replication should never occur. Indiscriminate use
of this option results in slaves becoming hopelessly out of
synchrony with the master, with you having no idea why this
has occurred.
</p><p>
For error codes, you should use the numbers provided by the
error message in your slave error log and in the output of
<code class="literal">SHOW SLAVE STATUS</code>.
<a href="error-handling.html" title="Appendix B. Errors, Error Codes, and Common Problems">Appendix B, <i>Errors, Error Codes, and Common Problems</i></a>, lists server error codes.
</p><p>
You can also (but should not) use the very non-recommended
value of <code class="literal">all</code> to cause the slave to ignore
all error messages and keeps going regardless of what happens.
Needless to say, if you use <code class="literal">all</code>, there are
no guarantees regarding the integrity of your data. Please do
not complain (or file bug reports) in this case if the slave's
data is not anywhere close to what it is on the master.
<span class="emphasis"><em>You have been warned</em></span>.
</p><p>
Examples:
</p><pre class="programlisting">--slave-skip-errors=1062,1053
--slave-skip-errors=all
</pre></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-administration"></a>15.1.3. Common Replication Administration Tasks</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="replication.html#replication-administration-status">15.1.3.1. Checking Replication Status</a></span></dt><dt><span class="section"><a href="replication.html#replication-administration-pausing">15.1.3.2. Pausing Replication on the Slave</a></span></dt></dl></div><p>
Once replication has been started it should execute without
requiring much regular administration. Depending on your
replication environment, you will want to check the replication
status of each slave either periodically, daily, or even more
frequently.
</p><p class="mnmas"><b>MySQL Enterprise</b>
For regular reports regarding the status of your slaves,
subscribe to the MySQL Network Monitoring and Advisory Service.
For more information see
<a href="http://www.mysql.com/products/enterprise/advisors.html" target="_top">http://www.mysql.com/products/enterprise/advisors.html</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-administration-status"></a>15.1.3.1. Checking Replication Status</h4></div></div></div><p>
The most common task when managing a replication process is to
ensure that replication is taking place and that there have been
no errors between the slave and the master.
</p><p>
The primary command for this is <code class="literal">SHOW SLAVE
STATUS</code> which you must execute on each slave:
</p><pre class="programlisting">mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 931
Relay_Log_File: slave1-relay-bin.000056
Relay_Log_Pos: 950
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 931
Relay_Log_Space: 1365
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.01 sec)</pre><p>
The key fields from the status report to examine are:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">Slave_IO_State</code> — indicates the
current status of the slave. See
<a href="optimization.html#slave-io-thread-states" title="6.5.5.5. Replication Slave I/O Thread States">Section 6.5.5.5, “Replication Slave I/O Thread States”</a>, and
<a href="optimization.html#slave-sql-thread-states" title="6.5.5.6. Replication Slave SQL Thread States">Section 6.5.5.6, “Replication Slave SQL Thread States”</a>, for more
information.
</p></li><li><p>
<code class="literal">Slave_IO_Running</code> — shows whether
the IO thread for the reading the master's binary log is
running.
</p></li><li><p>
<code class="literal">Slave_SQL_Running</code> — shows whether
the SQL thread for the executing events in the relay log is
running.
</p></li><li><p>
<code class="literal">Last_Error</code> — shows the last error
registered when processing the relay log. Ideally this
should be blank, indicating no errors.
</p></li><li><p>
<code class="literal">Seconds_Behind_Master</code> — shows the
number of seconds that the slave SQL thread is behind
processing the master binary log. A high number (or an
increasing one) can indicate that the slave is unable to
cope with the large number of queries from the master.
</p></li></ul></div><p>
On the master, you can check the status of slaves by examining
the list of running processes. Slaves execute the
<code class="literal">Binlog Dump</code> command:
</p><pre class="programlisting">mysql> SHOW PROCESSLIST \G;
*************************** 4. row ***************************
Id: 10
User: root
Host: slave1:58371
db: NULL
Command: Binlog Dump
Time: 777
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL</pre><p>
Because it is the slave that drives the core of the replication
process, very little information is available in this report.
</p><p>
If you have used the <code class="literal">--report-host</code> option,
then the <code class="literal">SHOW SLAVE HOSTS</code> statement will show
basic information about connected slaves:
</p><pre class="programlisting">mysql> SHOW SLAVE HOSTS;
+-----------+--------+------+-------------------+-----------+
| Server_id | Host | Port | Rpl_recovery_rank | Master_id |
+-----------+--------+------+-------------------+-----------+
| 10 | slave1 | 3306 | 0 | 1 |
+-----------+--------+------+-------------------+-----------+
1 row in set (0.00 sec)
</pre><p>
The output includes the ID of the slave server, the value of the
<code class="literal">--report-host</code> option, the connecting port,
master ID and the priority of the slave for receiving binary log
updates.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-administration-pausing"></a>15.1.3.2. Pausing Replication on the Slave</h4></div></div></div><p>
You can stop and start the replication of statements on the
slave using the <code class="literal">STOP SLAVE</code> and <code class="literal">START
SLAVE</code> commands.
</p><p>
To stop execution of the binary log from the master, use
<code class="literal">STOP SLAVE</code>:
</p><pre class="programlisting">mysql> STOP SLAVE;</pre><p>
When execution is stopped, the slave does not read the binary
log from the master (the <code class="literal">IO_THREAD</code>) and stops
processing events from the relay log that have not yet been
executed (the <code class="literal">SQL_THREAD</code>). You can pause
either the IO or SQL threads individually by specifying the
thread type. For example:
</p><pre class="programlisting">mysql> STOP SLAVE IO_THREAD;</pre><p>
Stopping the SQL thread can be useful if you want to perform a
backup or other task on a slave that only processes events from
the master. The IO thread will continue to be read from the
master, but not executed, which will make it easier for the
slave to catch up when you start slave operations again.
</p><p>
Stopping the IO thread will allow the statements in the relay
log to be executed up until the point where the relay log has
ceased to receive new events. Using this option can be useful
when you want to pause execution to allow the slave to catch up
with events from the master, when you want to perform
administration on the slave but also ensure you have the latest
updates to a specific point. This method can also be used to
pause execution on the slave while you conduct administration on
the master while ensuring that there is not a massive backlog of
events to be executed when replication is started again.
</p><p>
To start execution again, use the <code class="literal">START SLAVE</code>
statement:
</p><pre class="programlisting">mysql> START SLAVE;</pre><p>
If necessary, you can start either the
<code class="literal">IO_THREAD</code> or <code class="literal">SQL_THREAD</code>
threads individually.
</p></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="replication-solutions"></a>15.2. Replication Solutions</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="replication.html#replication-solutions-backups">15.2.1. Using Replication for Backups</a></span></dt><dt><span class="section"><a href="replication.html#replication-solutions-diffengines">15.2.2. Using Replication with Different Master and Slave Storage Engines</a></span></dt><dt><span class="section"><a href="replication.html#replication-solutions-scaleout">15.2.3. Using Replication for Scale-Out</a></span></dt><dt><span class="section"><a href="replication.html#replication-solutions-partitioning">15.2.4. Replicating Different Databases to Different Slaves</a></span></dt><dt><span class="section"><a href="replication.html#replication-solutions-performance">15.2.5. Improving Replication Performance</a></span></dt><dt><span class="section"><a href="replication.html#replication-solutions-switch">15.2.6. Switching Masters During Failover</a></span></dt><dt><span class="section"><a href="replication.html#replication-solutions-ssl">15.2.7. Setting Up Replication Using SSL</a></span></dt></dl></div><p>
Replication can be used in many different environments for a range
of purposes. In this section you will find general notes and advice
on using replication for specific solution types.
</p><p>
For information on using replication in a backup environment,
including notes on the setup, backup procedure, and files to back
up, see <a href="replication.html#replication-solutions-backups" title="15.2.1. Using Replication for Backups">Section 15.2.1, “Using Replication for Backups”</a>.
</p><p>
For advice and tips on using different storage engines on the master
and slaves, see <a href="replication.html#replication-solutions-diffengines" title="15.2.2. Using Replication with Different Master and Slave Storage Engines">Section 15.2.2, “Using Replication with Different Master and Slave Storage Engines”</a>.
</p><p>
Using replication as a scale-out solution requires some changes in
the logic and operation of applications that use the solution. See
<a href="replication.html#replication-solutions-scaleout" title="15.2.3. Using Replication for Scale-Out">Section 15.2.3, “Using Replication for Scale-Out”</a>.
</p><p>
For performance or data distribution reasons you may want to
replicate different databases to different replication slaves. See
<a href="replication.html#replication-solutions-partitioning" title="15.2.4. Replicating Different Databases to Different Slaves">Section 15.2.4, “Replicating Different Databases to Different Slaves”</a>
</p><p>
As the number of replication slaves increases, the load on the
master can increase (because of the need to replicate the binary log
to each slave) and lead to a reduction in performance of the master.
For tips on improving your replication performance, including using
a single secondary server as an replication master, see
<a href="replication.html#replication-solutions-performance" title="15.2.5. Improving Replication Performance">Section 15.2.5, “Improving Replication Performance”</a>.
</p><p>
For guidance on switching masters, or converting slaves into masters
as part of an emergency failover solution, see
<a href="replication.html#replication-solutions-switch" title="15.2.6. Switching Masters During Failover">Section 15.2.6, “Switching Masters During Failover”</a>.
</p><p>
To secure your replication communication you can encrypt the
communication channel by using SSL to exchange data. Step-by-step
instructions can be found in
<a href="replication.html#replication-solutions-ssl" title="15.2.7. Setting Up Replication Using SSL">Section 15.2.7, “Setting Up Replication Using SSL”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-solutions-backups"></a>15.2.1. Using Replication for Backups</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="replication.html#replication-solutions-backups-mysqldump">15.2.1.1. Backing Up Using <code class="literal">mysqldump</code></a></span></dt><dt><span class="section"><a href="replication.html#replication-solutions-backups-rawdata">15.2.1.2. Backing Up Raw Data</a></span></dt></dl></div><p>
You can use replication as a backup solution by replicating data
from the master to a slave, and then backing up the data slave.
Because the slave can be paused and shut down without affecting
the running operation of the master you can produce an effective
snapshot of 'live' data that would otherwise require a shutdown of
the master database.
</p><p>
How you back up the database will depend on the size of the
database and whether you are backing up only the data, or the data
and the replication slave state so that you can rebuild the slave
in the event of failure. There are therefore two choices:
</p><p>
If you are using replication as a solution to enable you to back
up the data on the master, and the size of your database is not
too large, then the <code class="literal">mysqldump</code> tool may be
suitable. See
<a href="replication.html#replication-solutions-backups-mysqldump" title="15.2.1.1. Backing Up Using mysqldump">Section 15.2.1.1, “Backing Up Using <code class="literal">mysqldump</code>”</a>.
</p><p>
For larger databases, where <code class="literal">mysqldump</code> would be
impractical or inefficient, you can back up the raw data files
instead. Using the raw data files option also means that you can
back up the binary and relay logs that will enable you to recreate
the slave in the event of a slave failure. For more information,
see <a href="replication.html#replication-solutions-backups-rawdata" title="15.2.1.2. Backing Up Raw Data">Section 15.2.1.2, “Backing Up Raw Data”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-solutions-backups-mysqldump"></a>15.2.1.1. Backing Up Using <code class="literal">mysqldump</code></h4></div></div></div><p>
Using <span><strong class="command">mysqldump</strong></span> to create a copy of the
database enables you to capture all of the data in the database
in a format that allows the information to be imported into
another instance of MySQL. Because the format of the information
is SQL statements the file can easily be distributed and applied
to running servers in the event that you need access to the data
in an emergency. However, if the size of your data set is very
large then <span><strong class="command">mysqldump</strong></span> may be impractical.
</p><p>
When using <span><strong class="command">mysqldump</strong></span> you should stop the
slave before starting the dump process to ensure that the dump
contains a consistent set of data:
</p><div class="orderedlist"><ol type="1"><li><p>
Stop the slave from processing requests. You can either stop
the slave completely using <span><strong class="command">mysqladmin</strong></span>:
</p><pre class="programlisting">shell> mysqladmin stop-slave</pre><p>
Alternatively, you can stop processing the relay log files
by stopping the replication SQL thread. Using this method
will allow the binary log data to be transferred. Within
busy replication environments this may speed up the catch-up
process when you start the slave processing again:
</p><pre class="programlisting">shell> mysql -e 'STOP SLAVE SQL_THREAD;'</pre></li><li><p>
Run <span><strong class="command">mysqldump</strong></span> to dump your databases. You
may either select databases to be dumped, or dump all
databases. For more information see
<a href="client-utility-programs.html#mysqldump" title="7.12. mysqldump — A Database Backup Program">Section 7.12, “<span><strong class="command">mysqldump</strong></span> — A Database Backup Program”</a>. For example, to dump all
databases:
</p><pre class="programlisting">shell> mysqldump --all-databases >fulldb.dump</pre></li><li><p>
Once the dump has completed, start slave operations again:
</p><pre class="programlisting">shell> mysqladmin start-slave</pre></li></ol></div><p>
In the preceding example you may want to add login credentials
(username, password) to the commands, and bundle the process up
into a script that you can run automatically each day.
</p><p>
If you use this approach, make sure you monitor the slave
replication process to ensure that the time taken to run the
backup in this way is not affecting the slaves ability to keep
up with events from the master. See
<a href="replication.html#replication-administration-status" title="15.1.3.1. Checking Replication Status">Section 15.1.3.1, “Checking Replication Status”</a>. If the
slave is unable to keep up you may want to add another server
and distribute the backup process. For an example of how to
configure this scenario, see
<a href="replication.html#replication-solutions-partitioning" title="15.2.4. Replicating Different Databases to Different Slaves">Section 15.2.4, “Replicating Different Databases to Different Slaves”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-solutions-backups-rawdata"></a>15.2.1.2. Backing Up Raw Data</h4></div></div></div><p>
To guarantee the integrity of the files that are copied, backing
up the raw data files on your MySQL replication slave should
take place while your slave server is shut down. If the MySQL
server is still running then background tasks, particularly with
storage engines with background processes such as InnoDB, may
still be updating the database files. With InnoDB, these
problems should be resolved during crash recovery, but since the
slave server can be shut down during the backup process without
affecting the execution of the master it makes sense to take
advantage of this facility.
</p><p>
To shut down the server and back up the files:
</p><div class="orderedlist"><ol type="1"><li><p>
Shut down the slave MySQL server:
</p><pre class="programlisting">shell> mysqladmin shutdown</pre></li><li><p>
Copy the data files. You can use any suitable copying or
archive utility, including <span><strong class="command">cp</strong></span>,
<span><strong class="command">tar</strong></span> or <span><strong class="command">WinZip</strong></span>:
</p><pre class="programlisting">tar cf /tmp/dbbackup.tar ./data</pre></li><li><p>
Start up the <span><strong class="command">mysqld</strong></span> process again:
</p><pre class="programlisting">shell> mysqld_safe &</pre><p>
Under Windows:
</p><pre class="programlisting">C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld"</pre></li></ol></div><p>
Normally you should back up the entire data folder for the slave
MySQL server. If you want to be able to restore the data and
operate as a slave (for example, in the event of failure of the
slave), then when you back up the slave's data, you should back
up the slave status files, <code class="filename">master.info</code> and
<code class="filename">relay.info</code>, along with the relay log files.
These files are needed to resume replication after you restore
the slave's data.
</p><p>
If you lose the relay logs but still have the
<code class="filename">relay-log.info</code> file, you can check it to
determine how far the SQL thread has executed in the master
binary logs. Then you can use <code class="literal">CHANGE MASTER
TO</code> with the <code class="literal">MASTER_LOG_FILE</code> and
<code class="literal">MASTER_LOG_POS</code> options to tell the slave to
re-read the binary logs from that point. Of course, this
requires that the binary logs still exist on the master server.
</p><p>
If your slave is subject to replicating <code class="literal">LOAD DATA
INFILE</code> statements, you should also back up any
<code class="filename">SQL_LOAD-*</code> files that exist in the
directory that the slave uses for this purpose. The slave needs
these files to resume replication of any interrupted
<code class="literal">LOAD DATA INFILE</code> operations. The directory
location is specified using the
<code class="option">--slave-load-tmpdir</code> option. If this option is
not specified, the directory location is the value of the
<code class="literal">tmpdir</code> system variable.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-solutions-diffengines"></a>15.2.2. Using Replication with Different Master and Slave Storage Engines</h3></div></div></div><p>
The replication process does not care if the source table on the
master and the replicated table on the slave use different engine
types. In fact, the system variables
<code class="literal">storage_engine</code> and
<code class="literal">table_type</code> are not replicated.
</p><p>
This provides a number of advantages in the replication process in
that you can take advantage of different engine types for
different replication scenarios. For example, in a typical
scaleout scenario (see
<a href="replication.html#replication-solutions-scaleout" title="15.2.3. Using Replication for Scale-Out">Section 15.2.3, “Using Replication for Scale-Out”</a>), you want to use
<code class="literal">InnoDB</code> tables on the master to take advantage
of the transactional functionality, but use
<code class="literal">MyISAM</code> on the slaves where transaction support
is not required because the data is only read. When using
replication in a data logging environment you may want to use the
<code class="literal">Archive</code> storage engine on the slave.
</p><p>
Setting up different engines on the master and slave depends on
how you set up the initial replication process:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If you used <code class="literal">mysqldump</code> to create the
database snapshot on your master then you could edit the dump
text to change the engine type used on each table.
</p><p>
Another alternative for <code class="literal">mysqldump</code> is to
disable engine types that you do not want to use on the slave
before using the dump to build the data on the slave. For
example, you can add the <code class="option">--skip-innodb</code> option
on your slave to disable the <code class="literal">InnoDB</code> engine.
If a specific engine does not exist, MySQL will use the
default engine type, usually <code class="literal">MyISAM</code>. If you
want to disable further engines in this way, you may want to
consider building a special binary to be used on the slave
that only supports the engines you want.
</p></li><li><p>
If you are using raw data files for the population of the
slave, you will be unable to change the initial table format.
Instead, use <code class="literal">ALTER TABLE</code> to change the
table types after the slave has been started.
</p></li><li><p>
For new master/slave replication setups where there are
currently no tables on the master, avoid specifying the engine
type when creating new tables.
</p></li></ul></div><p>
If you are already running a replication solution and want to
convert your existing tables to another engine type, follow these
steps:
</p><div class="orderedlist"><ol type="1"><li><p>
Stop the slave from running replication updates:
</p><pre class="programlisting">mysql> STOP SLAVE;</pre><p>
This will enable you to change engine types without
interruptions.
</p></li><li><p>
Execute an <code class="literal">ALTER TABLE ...
Engine='<em class="replaceable"><code>enginetype</code></em>'</code> for
each table where you want to change the engine type.
</p></li><li><p>
Start the slave replication process again:
</p><pre class="programlisting">mysql> START SLAVE;</pre></li></ol></div><p>
Although the <code class="literal">storage_engine</code> and
<code class="literal">table_type</code> variables are not replicated, be
aware that <code class="literal">CREATE TABLE</code> and <code class="literal">ALTER
TABLE</code> statements that include the engine specification
will be correctly replicated to the slave. For example, if you
have a CSV table and you execute:
</p><pre class="programlisting">mysql> ALTER TABLE csvtable Engine='MyISAM';</pre><p>
The above statement will be replicated to the slave and the engine
type on the slave will be converted to <code class="literal">MyISAM</code>,
even if you have previously changed the table type on the slave to
an engine other than CSV. If you want to retain engine differences
on the master and slave, you should be careful to use the
<code class="literal">storage_engine</code> variable on the master when
creating a new table. For example, instead of:
</p><pre class="programlisting">mysql> CREATE TABLE tablea (columna int) Engine=MyISAM;</pre><p>
Use this format:
</p><pre class="programlisting">mysql> SET storage_engine=MyISAM;
mysql> CREATE TABLE tablea (columna int);</pre><p>
When replicated, the <code class="literal">storage_engine</code> variable
will be ignored, and the <code class="literal">CREATE TABLE</code> statement
will be executed with the slave's default engine type.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-solutions-scaleout"></a>15.2.3. Using Replication for Scale-Out</h3></div></div></div><p>
You can use replication as a scale-out solution, i.e. where you
want to split up the load of database queries across multiple
database servers, within some reasonable limitations.
</p><p>
Because replication works from the distribution of one master to
one or more slaves, using replication for scaleout works best in
an environment where you have a high number of reads and low
number of writes/updates. Most websites fit into this category,
where users are browsing the website, reading articles, posts, or
viewing products. Updates only occur during session management, or
when making a purchase or adding a comment/message to a forum.
</p><p>
Replication in this situation enables you to distribute the reads
over the replication slaves, while still allowing your web servers
to communicate with the replication master when a write is
required. You can see a sample replication layout for this
scenario in <a href="replication.html#figure_replication-scaleout" title="Figure 15.1. Using replication to improve the performance during scaleout">Figure 15.1, “Using replication to improve the performance during scaleout”</a>.
</p><div class="figure"><a name="figure_replication-scaleout"></a><p class="title"><b>Figure 15.1. Using replication to improve the performance during scaleout</b></p><div class="mediaobject"><img src="images/scaleout.png" alt="Using replication to improve the performance
during scaleout"></div></div><p>
If the part of your code that is responsible for database access
has been properly abstracted/modularized, converting it to run
with a replicated setup should be very smooth and easy. Change the
implementation of your database access to send all writes to the
master, and to send reads to either the master or a slave. If your
code does not have this level of abstraction, setting up a
replicated system gives you the opportunity and motivation to
clean it up. Start by creating a wrapper library or module that
implements the following functions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">safe_writer_connect()</code>
</p></li><li><p>
<code class="literal">safe_reader_connect()</code>
</p></li><li><p>
<code class="literal">safe_reader_statement()</code>
</p></li><li><p>
<code class="literal">safe_writer_statement()</code>
</p></li></ul></div><p>
<code class="literal">safe_</code> in each function name means that the
function takes care of handling all error conditions. You can use
different names for the functions. The important thing is to have
a unified interface for connecting for reads, connecting for
writes, doing a read, and doing a write.
</p><p>
Then convert your client code to use the wrapper library. This may
be a painful and scary process at first, but it pays off in the
long run. All applications that use the approach just described
are able to take advantage of a master/slave configuration, even
one involving multiple slaves. The code is much easier to
maintain, and adding troubleshooting options is trivial. You need
modify only one or two functions; for example, to log how long
each statement took, or which statement among those issued gave
you an error.
</p><p>
If you have written a lot of code, you may want to automate the
conversion task by using the <span><strong class="command">replace</strong></span> utility
that comes with standard MySQL distributions, or write your own
conversion script. Ideally, your code uses consistent programming
style conventions. If not, then you are probably better off
rewriting it anyway, or at least going through and manually
regularizing it to use a consistent style.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-solutions-partitioning"></a>15.2.4. Replicating Different Databases to Different Slaves</h3></div></div></div><p>
There may be situations where you have a single master and want to
replicate different databases to different slaves. For example,
you may want to distribute different sales data to different
departments to help spread the load during data analysis. A sample
of this layout is shown in
<a href="replication.html#figure_replication-multi-db" title="Figure 15.2. Using replication to replicate separate DBs to multiple hosts">Figure 15.2, “Using replication to replicate separate DBs to multiple hosts”</a>.
</p><div class="figure"><a name="figure_replication-multi-db"></a><p class="title"><b>Figure 15.2. Using replication to replicate separate DBs to multiple hosts</b></p><div class="mediaobject"><img src="images/multi-db.png" alt="Using replication to replicate separate DBs
to multiple hosts"></div></div><p>
You can achieve this separation by configuring the master and
slaves as normal, and then limiting the binary log statements that
each slave processes by using the
<code class="literal">replicate-wild-do-table</code> configuration option on
each slave.
</p><p>
For example, to support the separation as shown in
<a href="replication.html#figure_replication-multi-db" title="Figure 15.2. Using replication to replicate separate DBs to multiple hosts">Figure 15.2, “Using replication to replicate separate DBs to multiple hosts”</a>, you would configure
each slave as follows before enabling replication using
<code class="literal">START SLAVE</code>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
MySQL Slave 1 should have the following configuration options:
</p><pre class="programlisting">replicate-wild-do-table=sales.%
replicate-wild-do-table=finance.%</pre></li><li><p>
MySQL Slave 2 should have the following configuration option:
</p><pre class="programlisting">replicate-wild-do-table=support.%</pre></li><li><p>
MySQL Slave 3 should have the following configuration option:
</p><pre class="programlisting">replicate-wild-do-table=service.%</pre></li></ul></div><p>
If you have data that needs to be synchronized to the slaves
before replication starts, you have a number of options:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Synchronize all the data to each slave, and delete the
databases and/or tables that you do not want to keep.
</p></li><li><p>
Use <code class="literal">mysqldump</code> to create a separate dump
file for each database and load the appropriate dump file on
each slave.
</p></li><li><p>
Use a raw data file dump and include only the specific files
and databases that you need for each slave. This option will
not work with InnoDB databases unless you use the
<code class="literal">innodb_file_per_table</code> option.
</p></li></ul></div><p>
Each slave in this configuration will transfer to the entire
binary log from the master, but will only execute the events
within the binary log that apply to the configured databases and
tables.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-solutions-performance"></a>15.2.5. Improving Replication Performance</h3></div></div></div><p>
As the number of slaves connecting to a master increases, the
load, although minimal, also increases, as each slave uses up a
client connection to the master. Also, as each slave must receive
a full copy of the master binary log, the network load on the
master may also increase and start to create a bottleneck.
</p><p>
If you are using a large number of slaves connected to one master,
and that master is also busy processing requests (for example, as
part of a scaleout solution), then you may want to improve the
performance of the replication process.
</p><p>
One way to improve the performance of the replication process is
to create a deeper replication structure that enables the master
to replicate to only one slave, and for the remaining slaves to
connect to this primary slave for their individual replication
requirements. A sample of this structure is shown in
<a href="replication.html#figure_replication-performance" title="Figure 15.3. Using an additional replication host to improve performance">Figure 15.3, “Using an additional replication host to improve performance”</a>.
</p><div class="figure"><a name="figure_replication-performance"></a><p class="title"><b>Figure 15.3. Using an additional replication host to improve performance</b></p><div class="mediaobject"><img src="images/submaster-performance.png" alt="Using an additional replication host to
improve performance"></div></div><p>
For this to work, you must configure the MySQL instances as
follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Master 1 is the primary master where all changes and updates
are written to the database. Binary logging should be enabled
on this machine.
</p></li><li><p>
Master 2 is the slave to the Master 1 that provides the
replication functionality to the remainder of the slaves in
the replication structure. Master 2 is the only machine
allowed to connect to Master 1. Master 2 also has binary
logging enabled, and the <code class="option">--log-slave-updates</code>
option so that replication instructions from Master 1 are also
written to Master 2's binary log so that they can then be
replicated to the true slaves.
</p></li><li><p>
Slave 1, Slave 2, and Slave 3 act as slaves to Master 2, and
replicate the information from Master 2, which is really the
data logged on Master 1.
</p></li></ul></div><p>
The above solution reduces the client load and the network
interface load on the primary master, which should improve the
overall performance of the primary master when used as a direct
database solution.
</p><p>
If your slaves are having trouble keeping up with the replication
process on the master then there are a number of options
available:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If possible, you should put the relay logs and the data files
on different physical drives. To do this, use the
<a href="replication.html#option_mysqld_relay-log"><code class="option">--relay-log</code></a>
option to specify the location of the relay log.
</p></li><li><p>
If the slaves are significantly slower than the master, then
you may want to divide up the responsibility for replicating
different databases to different slaves. See
<a href="replication.html#replication-solutions-partitioning" title="15.2.4. Replicating Different Databases to Different Slaves">Section 15.2.4, “Replicating Different Databases to Different Slaves”</a>.
</p></li><li><p>
If your master makes use of transactions and you are not
concerned about transaction support on your slaves, then use
<code class="literal">MyISAM</code> or another non-transactional engine.
See <a href="replication.html#replication-solutions-diffengines" title="15.2.2. Using Replication with Different Master and Slave Storage Engines">Section 15.2.2, “Using Replication with Different Master and Slave Storage Engines”</a>.
</p></li><li><p>
If your slaves are not acting as masters, and you have a
potential solution in place to ensure that you can bring up a
master in the event of failure, then you can switch off
<code class="option">--log-slave-updates</code>. This prevents 'dumb'
slaves from also logging events they have executed into their
own binary log.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-solutions-switch"></a>15.2.6. Switching Masters During Failover</h3></div></div></div><p>
There is currently no official solution for providing failover
between master and slaves in the event of a failure. With the
currently available features, you would have to set up a master
and a slave (or several slaves), and to write a script that
monitors the master to check whether it is up. Then instruct your
applications and the slaves to change master in case of failure.
</p><p>
Remember that you can tell a slave to change its master at any
time, using the <code class="literal">CHANGE MASTER TO</code> statement. The
slave will not check whether the databases on the master are
compatible with the slave, it will just start executing events
from the specified log and postition on the new master. In a
failover situation all the servers in the group are probably
executing the same events from the same binary log, so changing
the source of the events should not affect the database structure
or integrity providing you are careful.
</p><p>
Run your slaves with the <code class="option">--log-bin</code> option and
without <code class="option">--log-slave-updates</code>. In this way, the
slave is ready to become a master as soon as you issue
<code class="literal">STOP SLAVE</code>; <code class="literal">RESET MASTER</code>,
and <code class="literal">CHANGE MASTER TO</code> statement on the other
slaves. For example, assume that you have the structure shown in
<a href="replication.html#figure_replication-redundancy-before" title="Figure 15.4. Redundancy using replication, initial structure">Figure 15.4, “Redundancy using replication, initial structure”</a>.
</p><div class="figure"><a name="figure_replication-redundancy-before"></a><p class="title"><b>Figure 15.4. Redundancy using replication, initial structure</b></p><div class="mediaobject"><img src="images/redundancy-before.png" alt="Redundancy using replication, initial
structure"></div></div><p>
In this diagram, the <code class="literal">MySQL Master</code> holds the
master database, the <code class="literal">MySQL Slave</code> computers are
replication slaves, and the <code class="literal">Web Client</code> machines
are issuing database reads and writes. Web clients that issue only
reads (and would normally be connected to the slaves) are not
shown, as they do not need to switch to a new server in the event
of failure. For a more detailed example of a read/write scaleout
replication structure, see
<a href="replication.html#replication-solutions-scaleout" title="15.2.3. Using Replication for Scale-Out">Section 15.2.3, “Using Replication for Scale-Out”</a>.
</p><p>
Each MySQL Slave (<code class="literal">Slave 1</code>, <code class="literal">Slave
2</code>, and <code class="literal">Slave 3</code>) are slaves running
with <code class="option">--log-bin</code> and without
<code class="option">--log-slave-updates</code>. Because updates received by
a slave from the master are not logged in the binary log unless
<code class="option">--log-slave-updates</code> is specified, the binary log
on each slave is empty initially. If for some reason
<code class="literal">MySQL Master</code> becomes unavailable, you can pick
one of the slaves to become the new master. For example, if you
pick <code class="literal">Slave 1</code>, all <code class="literal">Web
Clients</code> should be redirected to <code class="literal">Slave
1</code>, which will log updates to its binary log.
<code class="literal">Slave 2</code> and <code class="literal">Slave 3</code> should
then replicate from <code class="literal">Slave 1</code>.
</p><p>
The reason for running the slave without
<code class="option">--log-slave-updates</code> is to prevent slaves from
receiving updates twice in case you cause one of the slaves to
become the new master. Suppose that <code class="literal">Slave 1</code> has
<code class="option">--log-slave-updates</code> enabled. Then it will write
updates that it receives from <code class="literal">Master</code> to its own
binary log. When <code class="literal">Slave 2</code> changes from
<code class="literal">Master</code> to <code class="literal">Slave 1</code> as its
master, it may receive updates from <code class="literal">Slave 1</code>
that it has already received from <code class="literal">Master</code>
</p><p>
Make sure that all slaves have processed any statements in their
relay log. On each slave, issue <code class="literal">STOP SLAVE
IO_THREAD</code>, then check the output of <code class="literal">SHOW
PROCESSLIST</code> until you see <code class="literal">Has read all relay
log</code>. When this is true for all slaves, they can be
reconfigured to the new setup. On the slave <code class="literal">Slave
1</code> being promoted to become the master, issue
<code class="literal">STOP SLAVE</code> and <code class="literal">RESET MASTER</code>.
</p><p>
On the other slaves <code class="literal">Slave 2</code> and <code class="literal">Slave
3</code>, use <code class="literal">STOP SLAVE</code> and <code class="literal">CHANGE
MASTER TO MASTER_HOST='Slave1'</code> (where
<code class="literal">'Slave1'</code> represents the real hostname of
<code class="literal">Slave 1</code>). To <code class="literal">CHANGE MASTER</code>,
add all information about how to connect to <code class="literal">Slave
1</code> from <code class="literal">Slave 2</code> or <code class="literal">Slave
3</code> (<em class="replaceable"><code>user</code></em>,
<em class="replaceable"><code>password</code></em>,
<em class="replaceable"><code>port</code></em>). In <code class="literal">CHANGE
MASTER</code>, there is no need to specify the name of
<code class="literal">Slave 1</code>'s binary log or binary log position to
read from: We know it is the first binary log and position 4,
which are the defaults for <code class="literal">CHANGE MASTER</code>.
Finally, use <code class="literal">START SLAVE</code> on <code class="literal">Slave
2</code> and <code class="literal">Slave 3</code>.
</p><p>
Once the new replication is in place, you will then need to
instruct each <code class="literal">Web Client</code> to direct their
statements to <code class="literal">Slave 1</code>. From that point on, all
updates statements sent by <code class="literal">Web Client</code> to
<code class="literal">Slave 1</code> are written to the binary log of
<code class="literal">Slave 1</code>, which then contains every update
statement sent to <code class="literal">Slave 1</code> since
<code class="literal">Master</code> died.
</p><p>
The resulting server structure is shown in
<a href="replication.html#figure_replication-redundancy-after" title="Figure 15.5. Redundancy using replication, after master failure">Figure 15.5, “Redundancy using replication, after master failure”</a>.
</p><div class="figure"><a name="figure_replication-redundancy-after"></a><p class="title"><b>Figure 15.5. Redundancy using replication, after master failure</b></p><div class="mediaobject"><img src="images/redundancy-after.png" alt="Redundancy using replication, after master
failure"></div></div><p>
When <code class="literal">Master</code> is up again, you must issue on it
the same <code class="literal">CHANGE MASTER</code> as that issued on
<code class="literal">Slave 2</code> and <code class="literal">Slave 3</code>, so that
<code class="literal">Master</code> becomes a slave of <code class="literal">S1</code>
and picks up each <code class="literal">Web Client</code> writes that it
missed while it was down.
</p><p>
To make <code class="literal">Master</code> a master again (because it is
the most powerful machine, for example), use the preceding
procedure as if <code class="literal">Slave 1</code> was unavailable and
<code class="literal">Master</code> was to be the new master. During this
procedure, do not forget to run <code class="literal">RESET MASTER</code> on
<code class="literal">Master</code> before making <code class="literal">Slave
1</code>, <code class="literal">Slave 2</code>, and <code class="literal">Slave
3</code> slaves of <code class="literal">Master</code>. Otherwise, they
may pick up old <code class="literal">Web Client</code> writes from before
the point at which <code class="literal">Master</code> became unavailable.
</p><p>
Note that there is no synchronization between the different slaves
to a master. Some slaves might be ahead of others. This means that
the concept outlined in the previous example might not work. In
practice, however, the relay logs of different slaves will most
likely not be far behind the master, so it would work, anyway (but
there is no guarantee).
</p><p>
A good way to keep your applications informed as to the location
of the master is by having a dynamic DNS entry for the master.
With <code class="literal">bind</code> you can use
<code class="filename">nsupdate</code> to dynamically update your DNS.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-solutions-ssl"></a>15.2.7. Setting Up Replication Using SSL</h3></div></div></div><p>
Setting up replication using an SSL connection is similar to
setting up a server and client using SSL. You will need to obtain
(or create) a suitable security certificate that you can use on
the master, and a similar certificate (from the same certificate
authority) on each slave.
</p><p>
To use SSL for encrypting the transfer of the binary log required
during replication you must first set up the master to support SSL
network connections. If the master does not support SSL
connections (because it has not been compiled or configured for
SSL), then replication through an SSL connection will not be
possible.
</p><p>
For more information on setting up a server and client for SSL
connectivity, see <a href="server-administration.html#secure-using-ssl" title="5.8.7.2. Using SSL Connections">Section 5.8.7.2, “Using SSL Connections”</a>.
</p><p>
To enable SSL on the master you will need to create or obtain
suitable certficates and then add the following configuration
options to the master's configuration within the
<code class="literal">mysqld</code> section:
</p><pre class="programlisting">ssl-ca=<em class="replaceable"><code>cacert.pem</code></em>
ssl-cert=<em class="replaceable"><code>server-cert.pem</code></em>
ssl-key=<em class="replaceable"><code>server-key.pem</code></em></pre><p>
The options are as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">ssl-ca</code> identifies the Certificate Authority
(CA) certificate.
</p></li><li><p>
<code class="literal">ssl-cert</code> identifies the server public key.
This can be sent to the client and authenticated against the
CA certificate that it has.
</p></li><li><p>
<code class="literal">ssl-key</code> identifies the server private key.
</p></li></ul></div><p>
On the slave, you have two options available for setting the SSL
information. You can either add the slaves certificates to the
<code class="literal">client</code> section of the slave configuration file,
or you can explicitly specify the SSL information using the
<code class="literal">CHANGE MASTER</code> statement.
</p><p>
Using the former option, add the following lines to the
<code class="literal">client</code> section of the slave configuration file:
</p><pre class="programlisting">[client]
ssl-ca=<em class="replaceable"><code>cacert.pem</code></em>
ssl-cert=<em class="replaceable"><code>server-cert.pem</code></em>
ssl-key=<em class="replaceable"><code>server-key.pem</code></em></pre><p>
Restart the slave server, using the
<code class="literal">--skip-slave</code> to prevent the slave from
connecting to the master. Use <code class="literal">CHANGE MASTER</code> to
specify the master configuration, using the
<code class="literal">master_ssl</code> option to enable SSL connectivity:
</p><pre class="programlisting">mysql> CHANGE MASTER TO \
MASTER_HOST='master_hostname', \
MASTER_USER='replicate', \
MASTER_PASSWORD='password', \
MASTER_SSL=1;</pre><p>
To specify the SSL certificate options during the <code class="literal">CHANGE
MASTER</code> command, append the SSL options:
</p><pre class="programlisting">CHANGE MASTER TO \
MASTER_HOST='master_hostname', \
MASTER_USER='replicate', \
MASTER_PASSWORD='password', \
MASTER_SSL=1, \
MASTER_SSL_CA = 'ca_file_name', \
MASTER_SSL_CAPATH = 'ca_directory_name', \
MASTER_SSL_CERT = 'cert_file_name', \
MASTER_SSL_KEY = 'key_file_name';</pre><p>
Once the master information has been updated, start the slave
replication process:
</p><pre class="programlisting">mysql> START SLAVE;</pre><p>
You can use the <code class="literal">SHOW SLAVE STATUS</code> to confirm
that SSL connection has been completed.
</p><p>
For more information on the <code class="literal">CHANGE MASTER TO</code>
syntax, see <a href="sql-syntax.html#change-master-to" title="12.6.2.1. CHANGE MASTER TO Syntax">Section 12.6.2.1, “<code class="literal">CHANGE MASTER TO</code> Syntax”</a>.
</p><p>
If you want to enforce SSL connections to be used during
replication, then create a user with the <code class="literal">REPLICATION
SLAVE</code> privilege and use the
<code class="literal">REQUIRE_SSL</code> option for that user. For example:
</p><pre class="programlisting">mysql> GRANT REPLICATION SLAVE ON *.*
-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass' REQUIRE SSL;</pre></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="replication-notes"></a>15.3. Replication Notes and Tips</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="replication.html#replication-features">15.3.1. Replication Features and Issues</a></span></dt><dt><span class="section"><a href="replication.html#replication-compatibility">15.3.2. Replication Compatibility Between MySQL Versions</a></span></dt><dt><span class="section"><a href="replication.html#replication-upgrade">15.3.3. Upgrading a Replication Setup</a></span></dt><dt><span class="section"><a href="replication.html#replication-faq">15.3.4. Replication FAQ</a></span></dt><dt><span class="section"><a href="replication.html#replication-problems">15.3.5. Troubleshooting Replication</a></span></dt><dt><span class="section"><a href="replication.html#replication-bugs">15.3.6. How to Report Replication Bugs or Problems</a></span></dt></dl></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-features"></a>15.3.1. Replication Features and Issues</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="replication.html#replication-features-autoincid">15.3.1.1. Replication and <code class="literal">AUTO_INCREMENT</code></a></span></dt><dt><span class="section"><a href="replication.html#replication-features-charset">15.3.1.2. Replication and Character Sets</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-directory">15.3.1.3. Replication <code class="literal">DIRECTORY</code> Statements</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-floatvalues">15.3.1.4. Replication with Floating-Point Values</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-flush">15.3.1.5. Replication and <code class="literal">FLUSH</code></a></span></dt><dt><span class="section"><a href="replication.html#replication-features-functions">15.3.1.6. Replication and Functions</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-load">15.3.1.7. Replication and <code class="literal">LOAD ...</code> Operations</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-mastercrash">15.3.1.8. Replication During a Master Crash</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-mastershutdown">15.3.1.9. Replication During a Master Shutdown</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-memory">15.3.1.10. Replication with <code class="literal">MEMORY</code> Tables</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-optimizer">15.3.1.11. Replication and the Query Optimizer</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-slaveerrors">15.3.1.12. Slave Errors during Replication</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-slaveshutdown">15.3.1.13. Replication during a Slave Shutdown</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-temptables">15.3.1.14. Replication and Temporary Tables</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-timeouts">15.3.1.15. Replication Retries and Timeouts</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-timezone">15.3.1.16. Replication and Time Zones</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-transactions">15.3.1.17. Replication and Transactions</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-triggers">15.3.1.18. Replication and Triggers</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-userprivs">15.3.1.19. Replication and User Privileges</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-variables">15.3.1.20. Replication and Variables</a></span></dt><dt><span class="section"><a href="replication.html#replication-features-views">15.3.1.21. Replication and Views</a></span></dt></dl></div><a class="indexterm" name="id2936720"></a><a class="indexterm" name="id2936732"></a><a class="indexterm" name="id2936742"></a><a class="indexterm" name="id2936754"></a><a class="indexterm" name="id2936763"></a><a class="indexterm" name="id2936773"></a><p>
In general, replication compatibility at the SQL level requires
that any features used be supported by both the master and the
slave servers. If you use a feature on a master server that is
available only as of a given version of MySQL, you cannot
replicate to a slave that is older than that version. Such
incompatibilities are likely to occur between series, so that, for
example, you cannot replicate from MySQL 5.0 to
4.1. However, these incompatibilities also can occur
for within-series replication. For example, the
<a href="functions.html#function_sleep"><code class="literal">SLEEP()</code></a> function is available in
MySQL 5.0.12 and up. If you use this function on the master
server, you cannot replicate to a slave server that is older than
MySQL 5.0.12.
</p><p>
If you are planning to use replication between 5.0
and a previous version of MySQL you should consult the edition of
the MySQL Reference Manual corresponding to the earlier release
series for information regarding the replication characteristics
of that series.
</p><p>
The following list provides details about what is supported and
what is not. Additional <code class="literal">InnoDB</code>-specific
information about replication is given in
<a href="storage-engines.html#innodb-and-mysql-replication" title="13.2.6.5. InnoDB and MySQL Replication">Section 13.2.6.5, “<code class="literal">InnoDB</code> and MySQL Replication”</a>.
</p><p>
Replication issues with regard to stored routines and triggers is
described in <a href="stored-procedures.html#stored-procedure-logging" title="18.4. Binary Logging of Stored Routines and Triggers">Section 18.4, “Binary Logging of Stored Routines and Triggers”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-autoincid"></a>15.3.1.1. Replication and <code class="literal">AUTO_INCREMENT</code></h4></div></div></div><p>
Replication of <code class="literal">AUTO_INCREMENT</code>,
<a href="functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a>, and
<code class="literal">TIMESTAMP</code> values is done correctly, subject
to the following exceptions.
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">INSERT DELAYED ...
VALUES(LAST_INSERT_ID())</code> inserts a different value
on the master and the slave. (<a href="http://bugs.mysql.com/20819" target="_top">Bug#20819</a>) This is fixed in
MySQL 5.1 when using row-based or mixed-format
binary logging.
</p></li><li><p>
Before MySQL 5.0.26, a stored procedure that uses
<a href="functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a> does not
replicate properly.
</p></li><li><p>
When a statement uses a stored function that inserts into an
<code class="literal">AUTO_INCREMENT</code> column, the generated
<code class="literal">AUTO_INCREMENT</code> value is not written into
the binary log, so a different value can in some cases be
inserted on the slave. This is also true of a trigger that
causes an <code class="literal">INSERT</code> into an
<code class="literal">AUTO_INCREMENT</code> column.
</p></li><li><p>
Adding an <code class="literal">AUTO_INCREMENT</code> column to a
table with <code class="literal">ALTER TABLE</code> might not produce
the same ordering of the rows on the slave and the master.
This occurs because the order in which the rows are numbered
depends on the specific storage engine used for the table
and the order in which the rows were inserted. If it is
important to have the same order on the master and slave,
the rows must be ordered before assigning an
<code class="literal">AUTO_INCREMENT</code> number. Assuming that you
want to add an <code class="literal">AUTO_INCREMENT</code> column to
the table <code class="literal">t1</code>, the following statements
produce a new table <code class="literal">t2</code> identical to
<code class="literal">t1</code> but with an
<code class="literal">AUTO_INCREMENT</code> column:
</p><pre class="programlisting">CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;
</pre><p>
This assumes that the table <code class="literal">t1</code> has
columns <code class="literal">col1</code> and <code class="literal">col2</code>.
</p><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>
To guarantee the same ordering on both master and slave,
<span class="emphasis"><em>all</em></span> columns of <code class="literal">t1</code>
must be referenced in the <code class="literal">ORDER BY</code>
clause.
</p></div></li><li><p>
The instructions just given are subject to the limitations
of <code class="literal">CREATE TABLE ... LIKE</code>: Foreign key
definitions are ignored, as are the <code class="literal">DATA
DIRECTORY</code> and <code class="literal">INDEX DIRECTORY</code>
table options. If a table definition includes any of those
characteristics, create <code class="literal">t2</code> using a
<code class="literal">CREATE TABLE</code> statement that is identical
to the one used to create <code class="literal">t1</code>, but with
the addition of the <code class="literal">AUTO_INCREMENT</code>
column.
</p></li><li><p>
Regardless of the method used to create and populate the
copy having the <code class="literal">AUTO_INCREMENT</code> column,
the final step is to drop the original table and then rename
the copy:
</p><pre class="programlisting">DROP t1;
ALTER TABLE t2 RENAME t1;
</pre><p>
See also <a href="error-handling.html#alter-table-problems" title="B.1.7.1. Problems with ALTER TABLE">Section B.1.7.1, “Problems with <code class="literal">ALTER TABLE</code>”</a>.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-charset"></a>15.3.1.2. Replication and Character Sets</h4></div></div></div><p>
The following applies to replication between MySQL servers that
use different character sets:
</p><div class="orderedlist"><ol type="1"><li><p>
If the master uses MySQL 4.1, you must
<span class="emphasis"><em>always</em></span> use the same
<span class="emphasis"><em>global</em></span> character set and collation on
the master and the slave, regardless of the MySQL version
running on the slave. (These are controlled by the
<code class="option">--character-set-server</code> and
<code class="option">--collation-server</code> options.) Otherwise, you
may get duplicate-key errors on the slave, because a key
that is unique in the master character set might not be
unique in the slave character set. Note that this is not a
cause for concern when master and slave are both MySQL 5.0
or later.
</p></li><li><p>
If the master is older than MySQL 4.1.3, the character set
of any client should never be made different from its global
value because this character set change is not known to the
slave. In other words, clients should not use <code class="literal">SET
NAMES</code>, <code class="literal">SET CHARACTER SET</code>, and
so forth. If both the master and the slave are 4.1.3 or
newer, clients can freely set session values for character
set variables because these settings are written to the
binary log and so are known to the slave. That is, clients
can use <code class="literal">SET NAMES</code> or <code class="literal">SET
CHARACTER SET</code> or can set variables such as
<code class="literal">collation_client</code> or
<code class="literal">collation_server</code>. However, clients are
prevented from changing the <span class="emphasis"><em>global</em></span>
value of these variables; as stated previously, the master
and slave must always have identical global character set
values.
</p></li><li><p>
If you have databases on the master with character sets that
differ from the global
<code class="literal">character_set_server</code> value, you should
design your <code class="literal">CREATE TABLE</code> statements so
that tables in those databases do not implicitly rely on the
database default character set (see <a href="http://bugs.mysql.com/2326" target="_top">Bug#2326</a>). A good
workaround is to state the character set and collation
explicitly in <code class="literal">CREATE TABLE</code> statements.
</p></li></ol></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-directory"></a>15.3.1.3. Replication <code class="literal">DIRECTORY</code> Statements</h4></div></div></div><p>
If a <code class="literal">DATA DIRECTORY</code> or <code class="literal">INDEX
DIRECTORY</code> table option is used in a <code class="literal">CREATE
TABLE</code> statement on the master server, the table option
is also used on the slave. This can cause problems if no
corresponding directory exists in the slave host filesystem or
if it exists but is not accessible to the slave server. MySQL
supports an <code class="literal">sql_mode</code> option called
<code class="literal">NO_DIR_IN_CREATE</code>. If the slave server is run
with this SQL mode enabled, it ignores the <code class="literal">DATA
DIRECTORY</code> and <code class="literal">INDEX DIRECTORY</code> table
options when replicating <code class="literal">CREATE TABLE</code>
statements. The result is that <code class="literal">MyISAM</code> data
and index files are created in the table's database directory.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-floatvalues"></a>15.3.1.4. Replication with Floating-Point Values</h4></div></div></div><p>
Floating-point values are approximate, so comparisons involving
them are inexact. This is true for operations that use
floating-point values explicitly, or values that are converted
to floating-point implicitly. Comparisons of floating-point
values might yield different results on master and slave servers
due to differences in computer architecture, the compiler used
to build MySQL, and so forth. See
<a href="functions.html#type-conversion" title="11.2.2. Type Conversion in Expression Evaluation">Section 11.2.2, “Type Conversion in Expression Evaluation”</a>, and
<a href="error-handling.html#problems-with-float" title="B.1.5.8. Problems with Floating-Point Comparisons">Section B.1.5.8, “Problems with Floating-Point Comparisons”</a>.
</p><p class="mnmas"><b>MySQL Enterprise</b>
For expert advice regarding replication subscribe to the MySQL
Enterprise Monitor. For more information see
<a href="http://www.mysql.com/products/enterprise/advisors.html" target="_top">http://www.mysql.com/products/enterprise/advisors.html</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-flush"></a>15.3.1.5. Replication and <code class="literal">FLUSH</code></h4></div></div></div><p>
Some forms of the <code class="literal">FLUSH</code> statement are not
logged because they could cause problems if replicated to a
slave: <code class="literal">FLUSH LOGS</code>, <code class="literal">FLUSH
MASTER</code>, <code class="literal">FLUSH SLAVE</code>, and
<code class="literal">FLUSH TABLES WITH READ LOCK</code>. For a syntax
example, see <a href="sql-syntax.html#flush" title="12.5.5.2. FLUSH Syntax">Section 12.5.5.2, “<code class="literal">FLUSH</code> Syntax”</a>. The <code class="literal">FLUSH
TABLES</code>, <code class="literal">ANALYZE TABLE</code>,
<code class="literal">OPTIMIZE TABLE</code>, and <code class="literal">REPAIR
TABLE</code> statements are written to the binary log and
thus replicated to slaves. This is not normally a problem
because these statements do not modify table data. However, this
can cause difficulties under certain circumstances. If you
replicate the privilege tables in the <code class="literal">mysql</code>
database and update those tables directly without using
<code class="literal">GRANT</code>, you must issue a <code class="literal">FLUSH
PRIVILEGES</code> on the slaves to put the new privileges
into effect. In addition, if you use <code class="literal">FLUSH
TABLES</code> when renaming a <code class="literal">MyISAM</code> table
that is part of a <code class="literal">MERGE</code> table, you must issue
<code class="literal">FLUSH TABLES</code> manually on the slaves. These
statements are written to the binary log unless you specify
<code class="literal">NO_WRITE_TO_BINLOG</code> or its alias
<code class="literal">LOCAL</code>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-functions"></a>15.3.1.6. Replication and Functions</h4></div></div></div><p>
Certain functions do not replicate well under some conditions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <a href="functions.html#function_user"><code class="literal">USER()</code></a>,
<a href="functions.html#function_current-user"><code class="literal">CURRENT_USER()</code></a>,
<a href="functions.html#function_uuid"><code class="literal">UUID()</code></a>,
<a href="functions.html#function_version"><code class="literal">VERSION()</code></a>, and
<a href="functions.html#function_load-file"><code class="literal">LOAD_FILE()</code></a> functions are
replicated without change and thus do not work reliably on
the slave.
</p></li><li><p>
As of MySQL 5.0.13, the
<a href="functions.html#function_sysdate"><code class="literal">SYSDATE()</code></a> function is no
longer equivalent to <a href="functions.html#function_now"><code class="literal">NOW()</code></a>.
Implications are that
<a href="functions.html#function_sysdate"><code class="literal">SYSDATE()</code></a> is not
replication-safe because it is not affected by <code class="literal">SET
TIMESTAMP</code> statements in the binary log and is
non-deterministic. To avoid this, you can start the server
with the <code class="option">--sysdate-is-now</code> option to cause
<a href="functions.html#function_sysdate"><code class="literal">SYSDATE()</code></a> to be an alias for
<a href="functions.html#function_now"><code class="literal">NOW()</code></a>.
</p></li><li><p>
The <code class="literal">GET_LOCK()</code>,
<code class="literal">RELEASE_LOCK()</code>,
<code class="literal">IS_FREE_LOCK()</code>, and
<code class="literal">IS_USED_LOCK()</code> functions that handle
user-level locks are replicated without the slave knowing
the concurrency context on master. Therefore, these
functions should not be used to insert into a master's table
because the content on the slave would differ. (For example,
do not issue a statement such as <code class="literal">INSERT INTO
mytable VALUES(GET_LOCK(...))</code>.)
</p></li></ul></div><p>
As a workaround for the preceding limitations, you can use the
strategy of saving the problematic function result in a user
variable and referring to the variable in a later statement. For
example, the following single-row <code class="literal">INSERT</code> is
problematic due to the reference to the
<a href="functions.html#function_uuid"><code class="literal">UUID()</code></a> function:
</p><pre class="programlisting">INSERT INTO t VALUES(UUID());
</pre><p>
To work around the problem, do this instead:
</p><pre class="programlisting">SET @my_uuid = UUID();
INSERT INTO t VALUES(@my_uuid);
</pre><p>
That sequence of statements replicates because the value of
<code class="literal">@my_uuid</code> is stored in the binary log as a
user-variable event prior to the <code class="literal">INSERT</code>
statement and is available for use in the
<code class="literal">INSERT</code>.
</p><p>
The same idea applies to multiple-row inserts, but is more
cumbersome to use. For a two-row insert, you can do this:
</p><pre class="programlisting">SET @my_uuid1 = UUID(); @my_uuid2 = UUID();
INSERT INTO t VALUES(@my_uuid1),(@my_uuid2);
</pre><p>
However, if the number of rows is large or unknown, the
workaround is difficult or impracticable. For example, you
cannot convert the following statement to one in which a given
individual user variable is associated with each row:
</p><pre class="programlisting">INSERT INTO t2 SELECT UUID(), * FROM t1;
</pre><p>
Non-delayed <code class="literal">INSERT</code> statements that refer to
<a href="functions.html#function_rand"><code class="literal">RAND()</code></a> or user-defined variables
replicate correctly. However, changing the statements to use
<code class="literal">INSERT DELAYED</code> can result in different
results on master and slave.
</p><p>
The <code class="literal">FOUND_ROWS()</code> and
<code class="literal">ROW_COUNT()</code> functions are also not replicated
reliably. A workaround is to store the result of the function
call in a user variable, and then use that in the
<code class="literal">INSERT</code> statement. For example, if you wish to
store the result in a table named <code class="literal">mytable</code>,
you might normally do so like this:
</p><pre class="programlisting">
SELECT SQL_CALC_FOUND_ROWS FROM mytable LIMIT 1;
INSERT INTO mytable VALUES( FOUND_ROWS() );
</pre><p>
However, if you are replicating <code class="literal">mytable</code>, then
you should use <code class="literal">SELECT INTO</code>, and then store
the variable in the table, like this:
</p><pre class="programlisting">
SELECT SQL_CALC_FOUND_ROWS INTO @found_rows FROM mytable LIMIT 1;
INSERT INTO mytable VALUES(@found_rows);
</pre><p>
In this way, the user variable is replicated as part of the
context, and applied on the slave correctly.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-load"></a>15.3.1.7. Replication and <code class="literal">LOAD ...</code> Operations</h4></div></div></div><p>
Using <code class="literal">LOAD TABLE FROM MASTER</code> where the master
is running MySQL 4.1 and the slave is running MySQL 5.0 may
corrupt the table data, and is not supported. (<a href="http://bugs.mysql.com/16261" target="_top">Bug#16261</a>)
</p><p>
<span class="emphasis"><em>The following applies only if either the master or the
slave is running MySQL version 5.0.3 or older</em></span>: If on
the master a <code class="literal">LOAD DATA INFILE</code> is interrupted
(integrity constraint violation, killed connection, and so on),
the slave skips the <code class="literal">LOAD DATA INFILE</code>
entirely. This means that if this command permanently inserted
or updated table records before being interrupted, these
modifications are not replicated to the slave.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-mastercrash"></a>15.3.1.8. Replication During a Master Crash</h4></div></div></div><p>
A crash on the master side can result in the master's binary log
having a final position less than the most recent position read
by the slave, due to the master's binary log file not being
flushed. This can cause the slave not to be able to replicate
when the master comes back up. Setting
<code class="literal">sync_binlog=1</code> in the master
<code class="filename">my.cnf</code> file helps to minimize this problem
because it causes the master to flush its binary log more
frequently.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-mastershutdown"></a>15.3.1.9. Replication During a Master Shutdown</h4></div></div></div><p>
It is safe to shut down a master server and restart it later.
When a slave loses its connection to the master, the slave tries
to reconnect immediately and retries periodically if that fails.
The default is to retry every 60 seconds. This may be changed
with the <code class="option">--master-connect-retry</code> option. A slave
also is able to deal with network connectivity outages. However,
the slave notices the network outage only after receiving no
data from the master for <code class="literal">slave_net_timeout</code>
seconds. If your outages are short, you may want to decrease
<code class="literal">slave_net_timeout</code>. See
<a href="server-administration.html#server-system-variables" title="5.2.3. System Variables">Section 5.2.3, “System Variables”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-memory"></a>15.3.1.10. Replication with <code class="literal">MEMORY</code> Tables</h4></div></div></div><p>
When a server shuts down and restarts, its
<code class="literal">MEMORY</code> (<code class="literal">HEAP</code>) tables
become empty. The master replicates this effect to slaves as
follows: The first time that the master uses each
<code class="literal">MEMORY</code> table after startup, it logs an event
that notifies the slaves that the table needs to be emptied by
writing a <code class="literal">DELETE</code> statement for that table to
the binary log. See <a href="storage-engines.html#memory-storage-engine" title="13.4. The MEMORY (HEAP) Storage Engine">Section 13.4, “The <code class="literal">MEMORY</code> (<code class="literal">HEAP</code>) Storage Engine”</a>, for
more information about <code class="literal">MEMORY</code> tables.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-optimizer"></a>15.3.1.11. Replication and the Query Optimizer</h4></div></div></div><p>
It is possible for the data on the master and slave to become
different if a statement is designed in such a way that the data
modification is non-deterministic; that is, left to the will of
the query optimizer. (This is in general not a good practice,
even outside of replication.) For a detailed explanation of this
issue, see <a href="error-handling.html#open-bugs" title="B.1.8.1. Open Issues in MySQL">Section B.1.8.1, “Open Issues in MySQL”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-slaveerrors"></a>15.3.1.12. Slave Errors during Replication</h4></div></div></div><p>
If a statement on a slave produces an error, the slave SQL
thread terminates, and the slave writes a message to its error
log. You should then connect to the slave manually and determine
the cause of the problem. (<code class="literal">SHOW SLAVE STATUS</code>
is useful for this.) Then fix the problem (for example, you
might need to create a non-existent table) and run
<code class="literal">START SLAVE</code>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-slaveshutdown"></a>15.3.1.13. Replication during a Slave Shutdown</h4></div></div></div><p>
Shutting down the slave (cleanly) is also safe because it keeps
track of where it left off. Unclean shutdowns might produce
problems, especially if the disk cache was not flushed to disk
before the system went down. Your system fault tolerance is
greatly increased if you have a good uninterruptible power
supply. Unclean shutdowns of the master may cause
inconsistencies between the content of tables and the binary log
in master; this can be avoided by using
<code class="literal">InnoDB</code> tables and the
<code class="option">--innodb-safe-binlog</code> option on the master. See
<a href="server-administration.html#binary-log" title="5.10.3. The Binary Log">Section 5.10.3, “The Binary Log”</a>.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
<code class="option">--innodb-safe-binlog</code> is unneeded as of MySQL
5.0.3, having been made obsolete by the introduction of XA
transaction support.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-temptables"></a>15.3.1.14. Replication and Temporary Tables</h4></div></div></div><p>
Temporary tables are replicated except in the case where you
shut down the slave server (not just the slave threads) and you
have replicated temporary tables that are used in updates that
have not yet been executed on the slave. If you shut down the
slave server, the temporary tables needed by those updates are
no longer available when the slave is restarted. To avoid this
problem, do not shut down the slave while it has temporary
tables open. Instead, use the following procedure:
</p><div class="orderedlist"><ol type="1"><li><p>
Issue a <code class="literal">STOP SLAVE</code> statement.
</p></li><li><p>
Use <code class="literal">SHOW STATUS</code> to check the value of the
<code class="literal">Slave_open_temp_tables</code> variable.
</p></li><li><p>
If the value is 0, issue a <span><strong class="command">mysqladmin
shutdown</strong></span> command to stop the slave.
</p></li><li><p>
If the value is not 0, restart the slave threads with
<code class="literal">START SLAVE</code>.
</p></li><li><p>
Repeat the procedure later until the
<code class="literal">Slave_open_temp_tables</code> variable is 0 and
you can stop the slave.
</p></li></ol></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-timeouts"></a>15.3.1.15. Replication Retries and Timeouts</h4></div></div></div><p>
In MySQL 5.0 (starting from 5.0.3), there is a
global system variable
<code class="literal">slave_transaction_retries</code>: If the replication
slave SQL thread fails to execute a transaction because of an
<code class="literal">InnoDB</code> deadlock or because it exceeded the
<code class="literal">InnoDB</code>
<code class="literal">innodb_lock_wait_timeout</code> or the NDBCluster
<code class="literal">TransactionDeadlockDetectionTimeout</code> or
<code class="literal">TransactionInactiveTimeout</code> value, the
transaction automatically retries
<code class="literal">slave_transaction_retries</code> times before
stopping with an error. The default value is 10. Starting from
MySQL 5.0.4, the total retry count can be seen in the output of
<code class="literal">SHOW STATUS</code>; see
<a href="server-administration.html#server-status-variables" title="5.2.5. Status Variables">Section 5.2.5, “Status Variables”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-timezone"></a>15.3.1.16. Replication and Time Zones</h4></div></div></div><p>
If the master uses MySQL 4.1, the same system time zone should
be set for both master and slave. Otherwise some statements will
not be replicated properly, such as statements that use the
<a href="functions.html#function_now"><code class="literal">NOW()</code></a> or
<code class="literal">FROM_UNIXTIME()</code> functions. You can set the
time zone in which MySQL server runs by using the
<code class="option">--timezone=<em class="replaceable"><code>timezone_name</code></em></code>
option of the <code class="filename">mysqld_safe</code> script or by
setting the <code class="literal">TZ</code> environment variable. Both
master and slave should also have the same default connection
time zone setting; that is, the
<code class="option">--default-time-zone</code> parameter should have the
same value for both master and slave. Note that this is not
necessary when the master is MySQL 5.0 or later.
</p><p>
<code class="literal">CONVERT_TZ(...,...,@@global.time_zone)</code> is not
properly replicated.
<code class="literal">CONVERT_TZ(...,...,@@session.time_zone)</code> is
properly replicated only if the master and slave are from MySQL
5.0.4 or newer.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-transactions"></a>15.3.1.17. Replication and Transactions</h4></div></div></div><p>
It is possible to replicate transactional tables on the master
using non-transactional tables on the slave. For example, you
can replicate an <code class="literal">InnoDB</code> master table as a
<code class="literal">MyISAM</code> slave table. However, there are issues
that you should consider before you do this:
</p><div class="itemizedlist"><ul type="disc"><li><p>
There are problems if the slave is stopped in the middle of
a <code class="literal">BEGIN</code>/<code class="literal">COMMIT</code> block
because the slave restarts at the beginning of the
<code class="literal">BEGIN</code> block.
</p></li><li><p>
When the storage engine type of the slave is
non-transactional, transactions on the master that mix
updates of transactional and non-transactional tables should
be avoided because they can cause inconsistency of the data
between the master's transactional table and the slave's
non-transactional table. That is, such transactions can lead
to master storage engine-specific behavior with the possible
effect of replication going out of synchrony. MySQL does not
issue a warning about this currently, so extra care should
be taken when replicating transactional tables from the
master to non-transactional ones on the slaves.
</p></li></ul></div><p>
Due to the non-transactional nature of <code class="literal">MyISAM</code>
tables, it is possible to have a statement that only partially
updates a table and returns an error code. This can happen, for
example, on a multiple-row insert that has one row violating a
key constraint, or if a long update statement is killed after
updating some of the rows. If that happens on the master, the
slave thread exits and waits for the database administrator to
decide what to do about it unless the error code is legitimate
and execution of the statement results in the same error code on
the slave. If this error code validation behavior is not
desirable, some or all errors can be masked out (ignored) with
the <code class="option">--slave-skip-errors</code> option.
</p><p>
If you update transactional tables from non-transactional tables
inside a <code class="literal">BEGIN</code>/<code class="literal">COMMIT</code>
sequence, updates to the binary log may be out of synchrony with
table states if the non-transactional table is updated before
the transaction commits. This occurs because the transaction is
written to the binary log only when it is committed.
</p><p>
In situations where transactions mix updates to transactional
and non-transactional tables, the order of statements in the
binary log is correct, and all needed statements are written to
the binary log even in case of a <code class="literal">ROLLBACK</code>.
However, when a second connection updates the non-transactional
table before the first connection's transaction is complete,
statements can be logged out of order, because the second
connection's update is written immediately after it is
performed, regardless of the state of the transaction being
performed by the first connection.
</p><div class="caution" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Caution</h3><p>
You should not use transactions in a replication environment
that update both transactional and non-transactional tables.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-triggers"></a>15.3.1.18. Replication and Triggers</h4></div></div></div><p>
<span class="bold"><strong>Known issue</strong></span>: In MySQL 5.0.17,
the syntax for <code class="literal">CREATE TRIGGER</code> changed to
include a <code class="literal">DEFINER</code> clause for specifying which
access privileges to check at trigger invocation time. (See
<a href="triggers.html#create-trigger" title="19.1. CREATE TRIGGER Syntax">Section 19.1, “<code class="literal">CREATE TRIGGER</code> Syntax”</a>, for more information.)
However, if you attempt to replicate from a master server older
than MySQL 5.0.17 to a slave running MySQL 5.0.17 through
5.0.19, replication of <code class="literal">CREATE TRIGGER</code>
statements fails on the slave with a <code class="literal">Definer not fully
qualified</code> error. A workaround is to create triggers on
the master using a version-specific comment embedded in each
<code class="literal">CREATE TRIGGER</code> statement:
</p><pre class="programlisting">CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER ... ;
</pre><p>
<code class="literal">CREATE TRIGGER</code> statements written this way
will replicate to newer slaves, which pick up the
<code class="literal">DEFINER</code> clause from the comment and execute
successfully.
</p><p>
This slave problem is fixed as of MySQL 5.0.20.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-userprivs"></a>15.3.1.19. Replication and User Privileges</h4></div></div></div><p>
User privileges are replicated only if the
<code class="literal">mysql</code> database is replicated. That is, the
<code class="literal">GRANT</code>, <code class="literal">REVOKE</code>,
<code class="literal">SET PASSWORD</code>, <code class="literal">CREATE USER</code>,
and <code class="literal">DROP USER</code> statements take effect on the
slave only if the replication setup includes the
<code class="literal">mysql</code> database.
</p><p>
If you're replicating all databases, but don't want statements
that affect user privileges to be replicated, set up the slave
to not replicate the <code class="literal">mysql</code> database, using
the <code class="option">--replicate-wild-ignore-table=mysql.%</code>
option. The slave will recognize that issuing privilege-related
SQL statements won't have an effect, and thus not execute those
statements.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-variables"></a>15.3.1.20. Replication and Variables</h4></div></div></div><p>
The <code class="literal">FOREIGN_KEY_CHECKS</code>,
<code class="literal">SQL_MODE</code>, <code class="literal">UNIQUE_CHECKS</code>,
and <code class="literal">SQL_AUTO_IS_NULL</code> variables are all
replicated in MySQL 5.0. The
<code class="literal">storage_engine</code> system variable (also known as
<code class="literal">table_type</code>) is not yet replicated, which is a
good thing for replication between different storage engines.
</p><p>
Starting from MySQL 5.0.3 (master and slave), replication works
even if the master and slave have different global character set
variables. Starting from MySQL 5.0.4 (master and slave),
replication works even if the master and slave have different
global time zone variables.
</p><p>
Session variables are not replicated properly when used in
statements that update tables. For example, <code class="literal">SET
MAX_JOIN_SIZE=1000</code> followed by <code class="literal">INSERT INTO
mytable VALUES(@@MAX_JOIN_SIZE)</code> will not insert the
same data on the master and the slave. This does not apply to
the common sequence of <code class="literal">SET TIME_ZONE=...</code>
followed by <code class="literal">INSERT INTO mytable
VALUES(CONVERT_TZ(...,...,@@time_zone))</code>, which
replicates correctly as of MySQL 5.0.4.
</p><p>
Update statements that refer to user-defined variables (that is,
variables of the form
<code class="literal">@<em class="replaceable"><code>var_name</code></em></code>) are
replicated correctly in MySQL 5.0. However, this is
not true for versions prior to 4.1. Note that user variable
names are case insensitive starting in MySQL 5.0. You should
take this into account when setting up replication between MySQL
5.0 and older versions.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="replication-features-views"></a>15.3.1.21. Replication and Views</h4></div></div></div><p>
Views are always replicated to slaves. Views are filtered by
their own name, not by the tables they refer to. This means that
a view can be replicated to the slave even if the view contains
a table that would normally be filtered out by
<code class="option">replication-ignore-table</code> rules. Care should
therefore be taken to ensure that views do not replicate table
data that would normally be filtered for security reasons.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-compatibility"></a>15.3.2. Replication Compatibility Between MySQL Versions</h3></div></div></div><p>
The binary log format as implemented in MySQL 5.0 is
considerably different from that used in previous versions. Major
changes were made in MySQL 5.0.3 (for improvements to handling of
character sets and <code class="literal">LOAD DATA INFILE</code>) and 5.0.4
(for improvements to handling of time zones).
</p><p>
We recommend using the most recent MySQL version available because
replication capabilities are continually being improved. We also
recommend using the same version for both the master and the
slave. We recommend upgrading masters and slaves running alpha or
beta versions to new (production) versions. Replication from a
5.0.3 master to a 5.0.2 slave will fail; from a 5.0.4 master to a
5.0.3 slave will also fail.
</p><p>
In general (but not always), slaves running MySQL
5.0.x may be used with older masters, but not the
reverse. For more information on potential issues, see
<a href="replication.html#replication-features" title="15.3.1. Replication Features and Issues">Section 15.3.1, “Replication Features and Issues”</a>.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
You <span class="emphasis"><em>cannot</em></span> replicate from a master that
uses a newer binary log format to a slave that uses an older
format (for example, from MySQL 5.0 to MySQL 4.1.) This has
significant implications for upgrading replication servers, as
described in <a href="replication.html#replication-upgrade" title="15.3.3. Upgrading a Replication Setup">Section 15.3.3, “Upgrading a Replication Setup”</a>.
</p></div><p>
The preceding information pertains to replication compatibility at
the protocol level. However, there can be other constraints, such
as SQL-level compatibility issues. For example, a 5.0
master cannot replicate to a 4.1 slave if the
replicated statements use SQL features available in
5.0 but not in 4.1. These and other
issues are discussed in <a href="replication.html#replication-features" title="15.3.1. Replication Features and Issues">Section 15.3.1, “Replication Features and Issues”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-upgrade"></a>15.3.3. Upgrading a Replication Setup</h3></div></div></div><p>
When you upgrade servers that participate in a replication setup,
the procedure for upgrading depends on the current server versions
and the version to which you are upgrading.
</p><p>
This section applies to upgrading replication from MySQL 3.23,
4.0, or 4.1 to MySQL 5.0. A 4.0 server should be
4.0.3 or newer.
</p><p>
When you upgrade a master to 5.0 from an earlier
MySQL release series, you should first ensure that all the slaves
of this master are using the same 5.0.x release. If
this is not the case, you should first upgrade the slaves. To
upgrade each slave, shut it down, upgrade it to the appropriate
5.0.x version, restart it, and restart replication.
The 5.0 slave is able to read the old relay logs
written prior to the upgrade and to execute the statements they
contain. Relay logs created by the slave after the upgrade are in
5.0 format.
</p><p>
After the slaves have been upgraded, shut down the master, upgrade
it to the same 5.0.x release as the slaves, and
restart it. The 5.0 master is able to read the old
binary logs written prior to the upgrade and to send them to the
5.0 slaves. The slaves recognize the old format and
handle it properly. Binary logs created by the master following
the upgrade are in 5.0 format. These too are
recognized by the 5.0 slaves.
</p><p>
In other words, there are no measures to take when upgrading to
MySQL 5.0, except that the slaves must be MySQL
5.0 before you can upgrade the master to
5.0. Note that downgrading from 5.0 to
older versions does not work so simply: You must ensure that any
5.0 binary logs or relay logs have been fully
processed, so that you can remove them before proceeding with the
downgrade.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-faq"></a>15.3.4. Replication FAQ</h3></div></div></div><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="replication.html#qandaitem-16-3-4-1">16.3.4.1: </a>
How do I configure a slave if the master is running and I do
not want to stop it?
</p></li><li><p><a href="replication.html#qandaitem-16-3-4-2">16.3.4.2: </a>
Does the slave need to be connected to the master all the
time?
</p></li><li><p><a href="replication.html#qandaitem-16-3-4-3">16.3.4.3: </a>
How do I know how late a slave is compared to the master? In
other words, how do I know the date of the last statement
replicated by the slave?
</p></li><li><p><a href="replication.html#qandaitem-16-3-4-4">16.3.4.4: </a>
How do I force the master to block updates until the slave
catches up?
</p></li><li><p><a href="replication.html#qandaitem-16-3-4-5">16.3.4.5: </a>
What issues should I be aware of when setting up two-way
replication?
</p></li><li><p><a href="replication.html#qandaitem-16-3-4-6">16.3.4.6: </a><a class="indexterm" name="id2939203"></a><a class="indexterm" name="id2939215"></a>
How can I use replication to improve performance of my
system?
</p></li><li><p><a href="replication.html#qandaitem-16-3-4-7">16.3.4.7: </a>
What should I do to prepare client code in my own
applications to use performance-enhancing replication?
</p></li><li><p><a href="replication.html#qandaitem-16-3-4-8">16.3.4.8: </a>
When and how much can MySQL replication improve the
performance of my system?
</p></li><li><p><a href="replication.html#qandaitem-16-3-4-9">16.3.4.9: </a>
How do I prevent GRANT and REVOKE statements from
replicating to slave machines?
</p></li><li><p><a href="replication.html#qandaitem-16-3-4-10">16.3.4.10: </a>
Does replication work on mixed operating systems (for
example, the master runs on Linux while slaves run on Mac OS
X and Windows)?
</p></li><li><p><a href="replication.html#qandaitem-16-3-4-11">16.3.4.11: </a>
Does replication work on mixed hardware architectures (for
example, the master runs on a 64-bit machine while slaves
run on 32-bit machines)?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-16-3-4-1"></a><span class="bold"><strong>16.3.4.1: </strong></span><span class="bold"><strong>
How do I configure a slave if the master is running and I do
not want to stop it?
</strong></span></p><p>
There are several possibilities. If you have taken a
snapshot backup of the master at some point and recorded the
binary log filename and offset (from the output of
<code class="literal">SHOW MASTER STATUS</code>) corresponding to the
snapshot, use the following procedure:
</p><div class="orderedlist"><ol type="1"><li><p>
Make sure that the slave is assigned a unique server ID.
</p></li><li><p>
Execute the following statement on the slave, filling in
appropriate values for each option:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CHANGE MASTER TO</code></strong>
-> <strong class="userinput"><code>MASTER_HOST='<em class="replaceable"><code>master_host_name</code></em>',</code></strong>
-> <strong class="userinput"><code>MASTER_USER='<em class="replaceable"><code>master_user_name</code></em>',</code></strong>
-> <strong class="userinput"><code>MASTER_PASSWORD='<em class="replaceable"><code>master_pass</code></em>',</code></strong>
-> <strong class="userinput"><code>MASTER_LOG_FILE='<em class="replaceable"><code>recorded_log_file_name</code></em>',</code></strong>
-> <strong class="userinput"><code>MASTER_LOG_POS=<em class="replaceable"><code>recorded_log_position</code></em>;</code></strong>
</pre></li><li><p>
Execute <code class="literal">START SLAVE</code> on the slave.
</p></li></ol></div><p>
If you do not have a backup of the master server, here is a
quick procedure for creating one. All steps should be
performed on the master host.
</p><div class="orderedlist"><ol type="1"><li><p>
Issue this statement to acquire a global read lock:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>FLUSH TABLES WITH READ LOCK;</code></strong>
</pre></li><li><p>
With the lock still in place, execute this command (or a
variation of it):
</p><pre class="programlisting">shell> <strong class="userinput"><code>tar zcf /tmp/backup.tar.gz /var/lib/mysql</code></strong>
</pre></li><li><p>
Issue this statement and record the output, which you
will need later:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW MASTER STATUS;</code></strong>
</pre></li><li><p>
Release the lock:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>UNLOCK TABLES;</code></strong>
</pre></li></ol></div><p>
An alternative to using the preceding procedure to make a
binary copy is to make an SQL dump of the master. To do
this, you can use <span><strong class="command">mysqldump --master-data</strong></span>
on your master and later load the SQL dump into your slave.
However, this is slower than making a binary copy.
</p><p>
Regardless of which of the two methods you use, afterward
follow the instructions for the case when you have a
snapshot and have recorded the log filename and offset. You
can use the same snapshot to set up several slaves. Once you
have the snapshot of the master, you can wait to set up a
slave as long as the binary logs of the master are left
intact. The two practical limitations on the length of time
you can wait are the amount of disk space available to
retain binary logs on the master and the length of time it
takes the slave to catch up.
</p><p><a name="qandaitem-16-3-4-2"></a><span class="bold"><strong>16.3.4.2: </strong></span><span class="bold"><strong>
Does the slave need to be connected to the master all the
time?
</strong></span></p><p>
No, it does not. The slave can go down or stay disconnected
for hours or even days, and then reconnect and catch up on
updates. For example, you can set up a master/slave
relationship over a dial-up link where the link is up only
sporadically and for short periods of time. The implication
of this is that, at any given time, the slave is not
guaranteed to be in synchrony with the master unless you
take some special measures.
</p><p><a name="qandaitem-16-3-4-3"></a><span class="bold"><strong>16.3.4.3: </strong></span><span class="bold"><strong>
How do I know how late a slave is compared to the master? In
other words, how do I know the date of the last statement
replicated by the slave?
</strong></span></p><p>
You can read the <code class="literal">Seconds_Behind_Master</code>
column in <code class="literal">SHOW SLAVE STATUS</code>. See
<a href="replication.html#replication-implementation-details" title="15.4.1. Replication Implementation Details">Section 15.4.1, “Replication Implementation Details”</a>.
</p><p>
When the slave SQL thread executes an event read from the
master, it modifies its own time to the event timestamp.
(This is why <code class="literal">TIMESTAMP</code> is well
replicated.) In the <code class="literal">Time</code> column in the
output of <code class="literal">SHOW PROCESSLIST</code>, the number of
seconds displayed for the slave SQL thread is the number of
seconds between the timestamp of the last replicated event
and the real time of the slave machine. You can use this to
determine the date of the last replicated event. Note that
if your slave has been disconnected from the master for one
hour, and then reconnects, you may immediately see
<code class="literal">Time</code> values like 3600 for the slave SQL
thread in <code class="literal">SHOW PROCESSLIST</code>. This is
because the slave is executing statements that are one hour
old.
</p><p><a name="qandaitem-16-3-4-4"></a><span class="bold"><strong>16.3.4.4: </strong></span><span class="bold"><strong>
How do I force the master to block updates until the slave
catches up?
</strong></span></p><p>
Use the following procedure:
</p><div class="orderedlist"><ol type="1"><li><p>
On the master, execute these statements:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>FLUSH TABLES WITH READ LOCK;</code></strong>
mysql> <strong class="userinput"><code>SHOW MASTER STATUS;</code></strong>
</pre><p>
Record the replication coordinates (the log filename and
offset) from the output of the <code class="literal">SHOW</code>
statement.
</p></li><li><p>
On the slave, issue the following statement, where the
arguments to the <code class="literal">MASTER_POS_WAIT()</code>
function are the replication coordinate values obtained
in the previous step:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT MASTER_POS_WAIT('<em class="replaceable"><code>log_name</code></em>', <em class="replaceable"><code>log_offset</code></em>);</code></strong>
</pre><p>
The <code class="literal">SELECT</code> statement blocks until the
slave reaches the specified log file and offset. At that
point, the slave is in synchrony with the master and the
statement returns.
</p></li><li><p>
On the master, issue the following statement to allow
the master to begin processing updates again:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>UNLOCK TABLES;</code></strong>
</pre></li></ol></div><p><a name="qandaitem-16-3-4-5"></a><span class="bold"><strong>16.3.4.5: </strong></span><span class="bold"><strong>
What issues should I be aware of when setting up two-way
replication?
</strong></span></p><p>
MySQL replication currently does not support any locking
protocol between master and slave to guarantee the atomicity
of a distributed (cross-server) update. In other words, it
is possible for client A to make an update to co-master 1,
and in the meantime, before it propagates to co-master 2,
client B could make an update to co-master 2 that makes the
update of client A work differently than it did on co-master
1. Thus, when the update of client A makes it to co-master
2, it produces tables that are different from what you have
on co-master 1, even after all the updates from co-master 2
have also propagated. This means that you should not chain
two servers together in a two-way replication relationship
unless you are sure that your updates can safely happen in
any order, or unless you take care of mis-ordered updates
somehow in the client code.
</p><p>
You should also realize that two-way replication actually
does not improve performance very much (if at all) as far as
updates are concerned. Each server must do the same number
of updates, just as you would have a single server do. The
only difference is that there is a little less lock
contention, because the updates originating on another
server are serialized in one slave thread. Even this benefit
might be offset by network delays.
</p><p><a name="qandaitem-16-3-4-6"></a><span class="bold"><strong>16.3.4.6: </strong></span><span class="bold"><strong><a class="indexterm" name="id2939873"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id2939888"></a></strong></span><span class="bold"><strong>
How can I use replication to improve performance of my
system?
</strong></span></p><p>
You should set up one server as the master and direct all
writes to it. Then configure as many slaves as you have the
budget and rackspace for, and distribute the reads among the
master and the slaves. You can also start the slaves with
the <code class="option">--skip-innodb</code>,
<code class="option">--skip-bdb</code>,
<code class="option">--low-priority-updates</code>, and
<code class="option">--delay-key-write=ALL</code> options to get speed
improvements on the slave end. In this case, the slave uses
non-transactional <code class="literal">MyISAM</code> tables instead
of <code class="literal">InnoDB</code> and <code class="literal">BDB</code>
tables to get more speed by eliminating transactional
overhead.
</p><p><a name="qandaitem-16-3-4-7"></a><span class="bold"><strong>16.3.4.7: </strong></span><span class="bold"><strong>
What should I do to prepare client code in my own
applications to use performance-enhancing replication?
</strong></span></p><p>
If the part of your code that is responsible for database
access has been properly abstracted/modularized, converting
it to run with a replicated setup should be very smooth and
easy. Change the implementation of your database access to
send all writes to the master, and to send reads to either
the master or a slave. If your code does not have this level
of abstraction, setting up a replicated system gives you the
opportunity and motivation to it clean up. Start by creating
a wrapper library or module that implements the following
functions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">safe_writer_connect()</code>
</p></li><li><p>
<code class="literal">safe_reader_connect()</code>
</p></li><li><p>
<code class="literal">safe_reader_statement()</code>
</p></li><li><p>
<code class="literal">safe_writer_statement()</code>
</p></li></ul></div><p>
<code class="literal">safe_</code> in each function name means that
the function takes care of handling all error conditions.
You can use different names for the functions. The important
thing is to have a unified interface for connecting for
reads, connecting for writes, doing a read, and doing a
write.
</p><p>
Then convert your client code to use the wrapper library.
This may be a painful and scary process at first, but it
pays off in the long run. All applications that use the
approach just described are able to take advantage of a
master/slave configuration, even one involving multiple
slaves. The code is much easier to maintain, and adding
troubleshooting options is trivial. You need modify only one
or two functions; for example, to log how long each
statement took, or which statement among those issued gave
you an error.
</p><p>
If you have written a lot of code, you may want to automate
the conversion task by using the <span><strong class="command">replace</strong></span>
utility that comes with standard MySQL distributions, or
write your own conversion script. Ideally, your code uses
consistent programming style conventions. If not, then you
are probably better off rewriting it anyway, or at least
going through and manually regularizing it to use a
consistent style.
</p><p><a name="qandaitem-16-3-4-8"></a><span class="bold"><strong>16.3.4.8: </strong></span><span class="bold"><strong>
When and how much can MySQL replication improve the
performance of my system?
</strong></span></p><p>
MySQL replication is most beneficial for a system that
processes frequent reads and infrequent writes. In theory,
by using a single-master/multiple-slave setup, you can scale
the system by adding more slaves until you either run out of
network bandwidth, or your update load grows to the point
that the master cannot handle it.
</p><p>
To determine how many slaves you can use before the added
benefits begin to level out, and how much you can improve
performance of your site, you need to know your query
patterns, and to determine empirically by benchmarking the
relationship between the throughput for reads (reads per
second, or <code class="literal">reads</code>) and for writes
(<code class="literal">writes</code>) on a typical master and a
typical slave. The example here shows a rather simplified
calculation of what you can get with replication for a
hypothetical system.
</p><p>
Let's say that system load consists of 10% writes and 90%
reads, and we have determined by benchmarking that
<code class="literal">reads</code> is 1200 – 2 ×
<code class="literal">writes</code>. In other words, the system can do
1,200 reads per second with no writes, the average write is
twice as slow as the average read, and the relationship is
linear. Let us suppose that the master and each slave have
the same capacity, and that we have one master and
<em class="replaceable"><code>N</code></em> slaves. Then we have for each
server (master or slave):
</p><p>
<code class="literal">reads = 1200 – 2 × writes</code>
</p><p>
<code class="literal">reads = 9 × writes /
(<em class="replaceable"><code>N</code></em> + 1)</code> (reads are
split, but writes go to all servers)
</p><p>
<code class="literal">9 × writes / (<em class="replaceable"><code>N</code></em> +
1) + 2 × writes = 1200</code>
</p><p>
<code class="literal">writes = 1200 / (2 +
9/(<em class="replaceable"><code>N</code></em>+1))</code>
</p><p>
The last equation indicates the maximum number of writes for
<em class="replaceable"><code>N</code></em> slaves, given a maximum
possible read rate of 1,200 per minute and a ratio of nine
reads per write.
</p><p>
This analysis yields the following conclusions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If <em class="replaceable"><code>N</code></em> = 0 (which means we have
no replication), our system can handle about 1200/11 =
109 writes per second.
</p></li><li><p>
If <em class="replaceable"><code>N</code></em> = 1, we get up to 184
writes per second.
</p></li><li><p>
If <em class="replaceable"><code>N</code></em> = 8, we get up to 400
writes per second.
</p></li><li><p>
If <em class="replaceable"><code>N</code></em> = 17, we get up to 480
writes per second.
</p></li><li><p>
Eventually, as <em class="replaceable"><code>N</code></em> approaches
infinity (and our budget negative infinity), we can get
very close to 600 writes per second, increasing system
throughput about 5.5 times. However, with only eight
servers, we increase it nearly four times.
</p></li></ul></div><p>
Note that these computations assume infinite network
bandwidth and neglect several other factors that could be
significant on your system. In many cases, you may not be
able to perform a computation similar to the one just shown
that accurately predicts what will happen on your system if
you add <em class="replaceable"><code>N</code></em> replication slaves.
However, answering the following questions should help you
decide whether and by how much replication will improve the
performance of your system:
</p><div class="itemizedlist"><ul type="disc"><li><p>
What is the read/write ratio on your system?
</p></li><li><p>
How much more write load can one server handle if you
reduce the reads?
</p></li><li><p>
For how many slaves do you have bandwidth available on
your network?
</p></li></ul></div><p><a name="qandaitem-16-3-4-9"></a><span class="bold"><strong>16.3.4.9: </strong></span><span class="bold"><strong>
How do I prevent GRANT and REVOKE statements from
replicating to slave machines?
</strong></span></p><p>
Start the server with the
<code class="option">--replicate-wild-ignore-table=mysql.%</code>
option.
</p><p><a name="qandaitem-16-3-4-10"></a><span class="bold"><strong>16.3.4.10: </strong></span><span class="bold"><strong>
Does replication work on mixed operating systems (for
example, the master runs on Linux while slaves run on Mac OS
X and Windows)?
</strong></span></p><p>
Yes.
</p><p><a name="qandaitem-16-3-4-11"></a><span class="bold"><strong>16.3.4.11: </strong></span><span class="bold"><strong>
Does replication work on mixed hardware architectures (for
example, the master runs on a 64-bit machine while slaves
run on 32-bit machines)?
</strong></span></p><p>
Yes.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-problems"></a>15.3.5. Troubleshooting Replication</h3></div></div></div><p>
If you have followed the instructions, and your replication setup
is not working, the first thing to do is <span class="emphasis"><em>check the error
log for messages</em></span>. Many users have lost time by not
doing this soon enough after encountering problems.
</p><p>
If you cannot tell from the error log what the problem was, try
the following techniques:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Verify that the master has binary logging enabled by issuing a
<code class="literal">SHOW MASTER STATUS</code> statement. If logging is
enabled, <code class="literal">Position</code> is non-zero. If binary
logging is not enabled, verify that you are running the master
with the <code class="option">--log-bin</code> and
<code class="option">--server-id</code> options.
</p></li><li><p>
Verify that the slave is running. Use <code class="literal">SHOW SLAVE
STATUS</code> to check whether the
<code class="literal">Slave_IO_Running</code> and
<code class="literal">Slave_SQL_Running</code> values are both
<code class="literal">Yes</code>. If not, verify the options that were
used when starting the slave server. For example,
<code class="option">--skip-slave-start</code> prevents the slave threads
from starting until you issue a <code class="literal">START SLAVE</code>
statement.
</p></li><li><p>
If the slave is running, check whether it established a
connection to the master. Use <code class="literal">SHOW
PROCESSLIST</code>, find the I/O and SQL threads and check
their <code class="literal">State</code> column to see what they
display. See
<a href="replication.html#replication-implementation-details" title="15.4.1. Replication Implementation Details">Section 15.4.1, “Replication Implementation Details”</a>. If the
I/O thread state says <code class="literal">Connecting to master</code>,
verify the privileges for the replication user on the master,
the master hostname, your DNS setup, whether the master is
actually running, and whether it is reachable from the slave.
</p></li><li><p>
If the slave was running previously but has stopped, the
reason usually is that some statement that succeeded on the
master failed on the slave. This should never happen if you
have taken a proper snapshot of the master, and never modified
the data on the slave outside of the slave thread. If the
slave stops unexpectedly, it is a bug or you have encountered
one of the known replication limitations described in
<a href="replication.html#replication-features" title="15.3.1. Replication Features and Issues">Section 15.3.1, “Replication Features and Issues”</a>. If it is a bug, see
<a href="replication.html#replication-bugs" title="15.3.6. How to Report Replication Bugs or Problems">Section 15.3.6, “How to Report Replication Bugs or Problems”</a>, for instructions on how to
report it.
</p><p class="mnmas"><b>MySQL Enterprise</b>
For immediate notification whenever a slave stops, subscribe
to the MySQL Enterprise Monitor. For more information see
<a href="http://www.mysql.com/products/enterprise/advisors.html" target="_top">http://www.mysql.com/products/enterprise/advisors.html</a>.
</p></li><li><p>
If a statement that succeeded on the master refuses to run on
the slave, try the following procedure if it is not feasible
to do a full database resynchronization by deleting the
slave's databases and copying a new snapshot from the master:
</p><div class="orderedlist"><ol type="1"><li><p>
Determine whether the affected table on the slave is
different from the master table. Try to understand how
this happened. Then make the slave's table identical to
the master's and run <code class="literal">START SLAVE</code>.
</p></li><li><p>
If the preceding step does not work or does not apply, try
to understand whether it would be safe to make the update
manually (if needed) and then ignore the next statement
from the master.
</p></li><li><p>
If you decide that you can skip the next statement from
the master, issue the following statements:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = <em class="replaceable"><code>N</code></em>;</code></strong>
mysql> <strong class="userinput"><code>START SLAVE;</code></strong>
</pre><p>
The value of <em class="replaceable"><code>N</code></em> should be 1 if
the next statement from the master does not use
<code class="literal">AUTO_INCREMENT</code> or
<a href="functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a>.
Otherwise, the value should be 2. The reason for using a
value of 2 for statements that use
<code class="literal">AUTO_INCREMENT</code> or
<a href="functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a> is that
they take two events in the binary log of the master.
</p></li><li><p>
If you are sure that the slave started out perfectly
synchronized with the master, and that no one has updated
the tables involved outside of the slave thread, then
presumably the discrepancy is the result of a bug. If you
are running the most recent version of MySQL, please
report the problem. If you are running an older version,
try upgrading to the latest production release to
determine whether the problem persists.
</p></li></ol></div></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-bugs"></a>15.3.6. How to Report Replication Bugs or Problems</h3></div></div></div><p>
When you have determined that there is no user error involved, and
replication still either does not work at all or is unstable, it
is time to send us a bug report. We need to obtain as much
information as possible from you to be able to track down the bug.
Please spend some time and effort in preparing a good bug report.
</p><p>
If you have a repeatable test case that demonstrates the bug,
please enter it into our bugs database using the instructions
given in <a href="introduction.html#bug-reports" title="1.7. How to Report Bugs or Problems">Section 1.7, “How to Report Bugs or Problems”</a>. If you have a
“<span class="quote">phantom</span>” problem (one that you cannot duplicate at
will), use the following procedure:
</p><div class="orderedlist"><ol type="1"><li><p>
Verify that no user error is involved. For example, if you
update the slave outside of the slave thread, the data goes
out of synchrony, and you can have unique key violations on
updates. In this case, the slave thread stops and waits for
you to clean up the tables manually to bring them into
synchrony. <span class="emphasis"><em>This is not a replication problem. It is
a problem of outside interference causing replication to
fail.</em></span>
</p></li><li><p>
Run the slave with the <code class="option">--log-slave-updates</code>
and <code class="option">--log-bin</code> options. These options cause
the slave to log the updates that it receives from the master
into its own binary logs.
</p></li><li><p>
Save all evidence before resetting the replication state. If
we have no information or only sketchy information, it becomes
difficult or impossible for us to track down the problem. The
evidence you should collect is:
</p><div class="itemizedlist"><ul type="disc"><li><p>
All binary logs from the master
</p></li><li><p>
All binary logs from the slave
</p></li><li><p>
The output of <code class="literal">SHOW MASTER STATUS</code> from
the master at the time you discovered the problem
</p></li><li><p>
The output of <code class="literal">SHOW SLAVE STATUS</code> from
the slave at the time you discovered the problem
</p></li><li><p>
Error logs from the master and the slave
</p></li></ul></div></li><li><p>
Use <span><strong class="command">mysqlbinlog</strong></span> to examine the binary logs.
The following should be helpful to find the problem statement.
<em class="replaceable"><code>log_pos</code></em> and
<em class="replaceable"><code>log_file</code></em> are the
<code class="literal">Master_Log_File</code> and
<code class="literal">Read_Master_Log_Pos</code> values from
<code class="literal">SHOW SLAVE STATUS</code>.
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqlbinlog -j <em class="replaceable"><code>log_pos</code></em> <em class="replaceable"><code>log_file</code></em> | head</code></strong>
</pre></li></ol></div><p>
After you have collected the evidence for the problem, try to
isolate it as a separate test case first. Then enter the problem
with as much information as possible into our bugs database using
the instructions at <a href="introduction.html#bug-reports" title="1.7. How to Report Bugs or Problems">Section 1.7, “How to Report Bugs or Problems”</a>.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="replication-implementation"></a>15.4. Replication Implementation Overview</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="replication.html#replication-implementation-details">15.4.1. Replication Implementation Details</a></span></dt><dt><span class="section"><a href="replication.html#slave-logs">15.4.2. Replication Relay and Status Files</a></span></dt><dt><span class="section"><a href="replication.html#replication-rules">15.4.3. How Servers Evaluate Replication Rules</a></span></dt></dl></div><a class="indexterm" name="id2940964"></a><p>
MySQL replication is based on the master server keeping track of all
changes to your databases (updates, deletes, and so on) in its
binary logs. Therefore, to use replication, you must enable binary
logging on the master server. See <a href="server-administration.html#binary-log" title="5.10.3. The Binary Log">Section 5.10.3, “The Binary Log”</a>.
</p><p>
Each slave server receives from the master the saved updates that
the master has recorded in its binary log, so that the slave can
execute the same updates on its copy of the data.
</p><p>
It is <span class="emphasis"><em>extremely</em></span> important to realize that the
binary log is simply a record starting from the fixed point in time
at which you enable binary logging. Any slaves that you set up need
copies of the databases on your master <span class="emphasis"><em>as they existed at
the moment you enabled binary logging on the master</em></span>. If
you start your slaves with databases that are not in the same state
as those on the master when the binary log was started, your slaves
are quite likely to fail.
</p><p>
After the slave has been set up with a copy of the master's data, it
connects to the master and waits for updates to process. If the
master fails, or the slave loses connectivity with your master, the
slave keeps trying to connect periodically until it is able to
resume listening for updates. The
<code class="option">--master-connect-retry</code> option controls the retry
interval. The default is 60 seconds.
</p><p>
Each slave keeps track of where it left off when it last read from
its master server. The master has no knowledge of how many slaves it
has or which ones are up to date at any given time.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-implementation-details"></a>15.4.1. Replication Implementation Details</h3></div></div></div><p>
MySQL replication capabilities are implemented using three threads
(one on the master server and two on the slave). When a
<code class="literal">START SLAVE</code> statement is issued on a slave
server, the slave creates an I/O thread, which connects to the
master and asks it to send the updates recorded in its binary
logs. The master creates a thread to send the binary log contents
to the slave. This thread can be identified as the <code class="literal">Binlog
Dump</code> thread in the output of <code class="literal">SHOW
PROCESSLIST</code> on the master. The slave I/O thread reads
the updates that the master <code class="literal">Binlog Dump</code> thread
sends and copies them to local files, known as <span class="emphasis"><em>relay
logs</em></span>, in the slave's data directory. The third thread
is the SQL thread, which the slave creates to read the relay logs
and to execute the updates they contain.
</p><p class="mnmas"><b>MySQL Enterprise</b>
For constant monitoring of the status of slaves subscribe to the
MySQL Enterprise Monitor. For more information see
<a href="http://www.mysql.com/products/enterprise/advisors.html" target="_top">http://www.mysql.com/products/enterprise/advisors.html</a>.
</p><p>
In the preceding description, there are three threads per
master/slave connection. A master that has multiple slaves creates
one thread for each currently-connected slave, and each slave has
its own I/O and SQL threads.
</p><p>
The slave uses two threads so that reading updates from the master
and executing them can be separated into two independent tasks.
Thus, the task of reading statements is not slowed down if
statement execution is slow. For example, if the slave server has
not been running for a while, its I/O thread can quickly fetch all
the binary log contents from the master when the slave starts,
even if the SQL thread lags far behind. If the slave stops before
the SQL thread has executed all the fetched statements, the I/O
thread has at least fetched everything so that a safe copy of the
statements is stored locally in the slave's relay logs, ready for
execution the next time that the slave starts. This enables the
master server to purge its binary logs sooner because it no longer
needs to wait for the slave to fetch their contents.
</p><p>
The <code class="literal">SHOW PROCESSLIST</code> statement provides
information that tells you what is happening on the master and on
the slave regarding replication. See
<a href="optimization.html#thread-information" title="6.5.5. Examining Thread Information">Section 6.5.5, “Examining Thread Information”</a>, for descriptions of all
replicated-related states.
</p><p>
The following example illustrates how the three threads show up in
the output from <code class="literal">SHOW PROCESSLIST</code>.
</p><p>
On the master server, the output from <code class="literal">SHOW
PROCESSLIST</code> looks like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW PROCESSLIST\G</code></strong>
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost:32931
db: NULL
Command: Binlog Dump
Time: 94
State: Has sent all binlog to slave; waiting for binlog to
be updated
Info: NULL
</pre><p>
Here, thread 2 is a <code class="literal">Binlog Dump</code> replication
thread for a connected slave. The <code class="literal">State</code>
information indicates that all outstanding updates have been sent
to the slave and that the master is waiting for more updates to
occur. If you see no <code class="literal">Binlog Dump</code> threads on a
master server, this means that replication is not running —
that is, that no slaves are currently connected.
</p><p>
On the slave server, the output from <code class="literal">SHOW
PROCESSLIST</code> looks like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW PROCESSLIST\G</code></strong>
*************************** 1. row ***************************
Id: 10
User: system user
Host:
db: NULL
Command: Connect
Time: 11
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 11
User: system user
Host:
db: NULL
Command: Connect
Time: 11
State: Has read all relay log; waiting for the slave I/O
thread to update it
Info: NULL
</pre><p>
This information indicates that thread 10 is the I/O thread that
is communicating with the master server, and thread 11 is the SQL
thread that is processing the updates stored in the relay logs. At
the time that the <code class="literal">SHOW PROCESSLIST</code> was run,
both threads were idle, waiting for further updates.
</p><p>
The value in the <code class="literal">Time</code> column can show how late
the slave is compared to the master. See
<a href="replication.html#replication-faq" title="15.3.4. Replication FAQ">Section 15.3.4, “Replication FAQ”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="slave-logs"></a>15.4.2. Replication Relay and Status Files</h3></div></div></div><p>
By default, relay logs filenames have the form
<code class="filename"><em class="replaceable"><code>host_name</code></em>-relay-bin.<em class="replaceable"><code>nnnnnn</code></em></code>,
where <em class="replaceable"><code>host_name</code></em> is the name of the
slave server host and <em class="replaceable"><code>nnnnnn</code></em> is a
sequence number. Successive relay log files are created using
successive sequence numbers, beginning with
<code class="literal">000001</code>. The slave uses an index file to track
the relay log files currently in use. The default relay log index
filename is
<code class="filename"><em class="replaceable"><code>host_name</code></em>-relay-bin.index</code>.
By default, the slave server creates relay log files in its data
directory. The default filenames can be overridden with the
<code class="option">--relay-log</code> and
<code class="option">--relay-log-index</code> server options. See
<a href="replication.html#replication-options" title="15.1.2. Replication Startup Options and Variables">Section 15.1.2, “Replication Startup Options and Variables”</a>.
</p><p>
Relay logs have the same format as binary logs and can be read
using <span><strong class="command">mysqlbinlog</strong></span>. The SQL thread automatically
deletes each relay log file as soon as it has executed all events
in the file and no longer needs it. There is no explicit mechanism
for deleting relay logs because the SQL thread takes care of doing
so. However, <code class="literal">FLUSH LOGS</code> rotates relay logs,
which influences when the SQL thread deletes them.
</p><p>
A slave server creates a new relay log file under the following
conditions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Each time the I/O thread starts.
</p></li><li><p>
When the logs are flushed; for example, with <code class="literal">FLUSH
LOGS</code> or <span><strong class="command">mysqladmin flush-logs</strong></span>.
</p></li><li><p>
When the size of the current relay log file becomes too large.
The meaning of “<span class="quote">too large</span>” is determined as
follows:
</p><div class="itemizedlist"><ul type="circle"><li><p>
If the value of <code class="literal">max_relay_log_size</code> is
greater than 0, that is the maximum relay log file size.
</p></li><li><p>
If the value of <code class="literal">max_relay_log_size</code> is
0, <code class="literal">max_binlog_size</code> determines the
maximum relay log file size.
</p></li></ul></div></li></ul></div><p>
A slave replication server creates two additional small files in
the data directory. These <span class="emphasis"><em>status files</em></span> are
named <code class="filename">master.info</code> and
<code class="filename">relay-log.info</code> by default. Their names can be
changed by using the <code class="option">--master-info-file</code> and
<code class="option">--relay-log-info-file</code> options. See
<a href="replication.html#replication-options" title="15.1.2. Replication Startup Options and Variables">Section 15.1.2, “Replication Startup Options and Variables”</a>.
</p><p>
The two status files contain information like that shown in the
output of the <code class="literal">SHOW SLAVE STATUS</code> statement,
which is discussed in <a href="sql-syntax.html#replication-slave-sql" title="12.6.2. SQL Statements for Controlling Slave Servers">Section 12.6.2, “SQL Statements for Controlling Slave Servers”</a>.
Because the status files are stored on disk, they survive a slave
server's shutdown. The next time the slave starts up, it reads the
two files to determine how far it has proceeded in reading binary
logs from the master and in processing its own relay logs.
</p><p>
The I/O thread updates the <code class="filename">master.info</code> file.
The following table shows the correspondence between the lines in
the file and the columns displayed by <code class="literal">SHOW SLAVE
STATUS</code>.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Line</strong></span></td><td><span class="bold"><strong>Description</strong></span></td></tr><tr><td>1</td><td>Number of lines in the file</td></tr><tr><td>2</td><td><code class="literal">Master_Log_File</code></td></tr><tr><td>3</td><td><code class="literal">Read_Master_Log_Pos</code></td></tr><tr><td>4</td><td><code class="literal">Master_Host</code></td></tr><tr><td>5</td><td><code class="literal">Master_User</code></td></tr><tr><td>6</td><td>Password (not shown by <code class="literal">SHOW SLAVE STATUS</code>)</td></tr><tr><td>7</td><td><code class="literal">Master_Port</code></td></tr><tr><td>8</td><td><code class="literal">Connect_Retry</code></td></tr><tr><td>9</td><td><code class="literal">Master_SSL_Allowed</code></td></tr><tr><td>10</td><td><code class="literal">Master_SSL_CA_File</code></td></tr><tr><td>11</td><td><code class="literal">Master_SSL_CA_Path</code></td></tr><tr><td>12</td><td><code class="literal">Master_SSL_Cert</code></td></tr><tr><td>13</td><td><code class="literal">Master_SSL_Cipher</code></td></tr><tr><td>14</td><td><code class="literal">Master_SSL_Key</code></td></tr></tbody></table></div><p>
The SQL thread updates the <code class="filename">relay-log.info</code>
file. The following table shows the correspondence between the
lines in the file and the columns displayed by <code class="literal">SHOW SLAVE
STATUS</code>.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Line</strong></span></td><td><span class="bold"><strong>Description</strong></span></td></tr><tr><td>1</td><td><code class="literal">Relay_Log_File</code></td></tr><tr><td>2</td><td><code class="literal">Relay_Log_Pos</code></td></tr><tr><td>3</td><td><code class="literal">Relay_Master_Log_File</code></td></tr><tr><td>4</td><td><code class="literal">Exec_Master_Log_Pos</code></td></tr></tbody></table></div><p>
The contents of the <code class="filename">relay-log.info</code> file and
the states shown by the <code class="literal">SHOW SLAVE STATES</code>
command may not match if the <code class="filename">relay-log.info</code>
file has not been flushed to disk. Ideally, you should only view
<code class="filename">relay-log.info</code> on a slave that is offline
(i.e. <code class="literal">mysqld</code> is not running). For a running
system, <code class="literal">SHOW SLAVE STATUS</code> should be used.
</p><p>
When you back up the slave's data, you should back up these two
status files as well, along with the relay log files. They are
needed to resume replication after you restore the slave's data.
If you lose the relay logs but still have the
<code class="filename">relay-log.info</code> file, you can check it to
determine how far the SQL thread has executed in the master binary
logs. Then you can use <code class="literal">CHANGE MASTER TO</code> with
the <code class="literal">MASTER_LOG_FILE</code> and
<code class="literal">MASTER_LOG_POS</code> options to tell the slave to
re-read the binary logs from that point. Of course, this requires
that the binary logs still exist on the master server.
</p><p>
If your slave is subject to replicating <code class="literal">LOAD DATA
INFILE</code> statements, you should also back up any
<code class="filename">SQL_LOAD-*</code> files that exist in the directory
that the slave uses for this purpose. The slave needs these files
to resume replication of any interrupted <code class="literal">LOAD DATA
INFILE</code> operations. The directory location is specified
using the <code class="option">--slave-load-tmpdir</code> option. If this
option is not specified, the directory location is the value of
the <code class="literal">tmpdir</code> system variable.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="replication-rules"></a>15.4.3. How Servers Evaluate Replication Rules</h3></div></div></div><p>
If a master server does not write a statement to its binary log,
the statement is not replicated. If the server does log the
statement, the statement is sent to all slaves and each slave
determines whether to execute it or ignore it.
</p><p>
On the master side, decisions about which statements to log are
based on the <code class="option">--binlog-do-db</code> and
<code class="option">--binlog-ignore-db</code> options that control binary
logging. For a description of the rules that servers use in
evaluating these options, see <a href="server-administration.html#binary-log" title="5.10.3. The Binary Log">Section 5.10.3, “The Binary Log”</a>.
</p><p>
On the slave side, decisions about whether to execute or ignore
statements received from the master are made according to the
<code class="option">--replicate-*</code> options that the slave was started
with. (See <a href="replication.html#replication-options" title="15.1.2. Replication Startup Options and Variables">Section 15.1.2, “Replication Startup Options and Variables”</a>.) The slave
evaluates these options using the following procedure, which first
checks the database-level options and then the table-level
options.
</p><p>
In the simplest case, when there are no
<code class="option">--replicate-*</code> options, the procedure yields the
result that the slave executes all statements that it receives
from the master. Otherwise, the result depends on the particular
options given. In general, to make it easier to determine what
effect an option set will have, it is recommended that you avoid
mixing “<span class="quote">do</span>” and “<span class="quote">ignore</span>” options, or
wildcard and non-wildcard options.
</p><p>
<span class="bold"><strong>Stage 1. Check the database
options.</strong></span>
</p><p>
At this stage, the slave checks whether there are any
<code class="option">--replicate-do-db</code> or
<code class="option">--replicate-ignore-db</code> options that specify
database-specific conditions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<span class="emphasis"><em>No</em></span>: Permit the statement and proceed to
the table-checking stage.
</p></li><li><p>
<span class="emphasis"><em>Yes</em></span>: Test the options using the same
rules as for the <code class="option">--binlog-do-db</code> and
<code class="option">--binlog-ignore-db</code> options to determine
whether to permit or ignore the statement. What is the result
of the test?
</p><div class="itemizedlist"><ul type="circle"><li><p>
<span class="emphasis"><em>Permit</em></span>: Do not execute the statement
immediately. Defer the decision and proceed to the
table-checking stage.
</p></li><li><p>
<span class="emphasis"><em>Ignore</em></span>: Ignore the statement and
exit.
</p></li></ul></div></li></ul></div><p>
This stage can permit a statement for further option-checking, or
cause it to be ignored. However, statements that are permitted at
this stage are not actually executed yet. Instead, they pass to
the following stage that checks the table options.
</p><p>
<span class="bold"><strong>Stage 2. Check the table options.</strong></span>
</p><p>
First, as a preliminary condition, the slave checks whether the
statement occurs within a stored function or (prior to MySQL
5.0.12) a stored procedure. If so, execute the statement and exit.
(Stored procedures are exempt from this test as of MySQL 5.0.12
because procedure logging occurs at the level of statements that
are executed within the routine rather than at the
<code class="literal">CALL</code> level.)
</p><p>
Next, the slave checks for table options and evaluates them. If
the server reaches this point, it executes all statements if there
are no table options. If there are “<span class="quote">do</span>” table
options, the statement must match one of them if it is to be
executed; otherwise, it is ignored. If there are any
“<span class="quote">ignore</span>” options, all statements are executed except
those that match any <code class="literal">ignore</code> option. The
following steps describe how this evaluation occurs in more
detail.
</p><div class="orderedlist"><ol type="1"><li><p>
Are there any <code class="option">--replicate-*-table</code> options?
</p><div class="itemizedlist"><ul type="disc"><li><p>
<span class="emphasis"><em>No</em></span>: There are no table restrictions,
so all statements match. Execute the statement and exit.
</p></li><li><p>
<span class="emphasis"><em>Yes</em></span>: There are table restrictions.
Evaluate the tables to be updated against them. There
might be multiple tables to update, so loop through the
following steps for each table looking for a matching
option (first the non-wild options, and then the wild
options). Only tables that are to be updated are compared
to the options. For example, if the statement is
<code class="literal">INSERT INTO sales SELECT * FROM prices</code>,
only <code class="literal">sales</code> is compared to the options).
If several tables are to be updated (multiple-table
statement), the first table that matches “<span class="quote">do</span>”
or “<span class="quote">ignore</span>” wins. That is, the server checks
the first table against the options. If no decision could
be made, it checks the second table against the options,
and so on.
</p></li></ul></div></li><li><p>
Are there any <code class="option">--replicate-do-table</code> options?
</p><div class="itemizedlist"><ul type="disc"><li><p>
<span class="emphasis"><em>No</em></span>: Proceed to the next step.
</p></li><li><p>
<span class="emphasis"><em>Yes</em></span>: Does the table match any of
them?
</p><div class="itemizedlist"><ul type="circle"><li><p>
<span class="emphasis"><em>No</em></span>: Proceed to the next step.
</p></li><li><p>
<span class="emphasis"><em>Yes</em></span>: Execute the statement and
exit.
</p></li></ul></div></li></ul></div></li><li><p>
Are there any <code class="option">--replicate-ignore-table</code>
options?
</p><div class="itemizedlist"><ul type="disc"><li><p>
<span class="emphasis"><em>No</em></span>: Proceed to the next step.
</p></li><li><p>
<span class="emphasis"><em>Yes</em></span>: Does the table match any of
them?
</p><div class="itemizedlist"><ul type="circle"><li><p>
<span class="emphasis"><em>No</em></span>: Proceed to the next step.
</p></li><li><p>
<span class="emphasis"><em>Yes</em></span>: Ignore the statement and
exit.
</p></li></ul></div></li></ul></div></li><li><p>
Are there any <code class="option">--replicate-wild-do-table</code>
options?
</p><div class="itemizedlist"><ul type="disc"><li><p>
<span class="emphasis"><em>No</em></span>: Proceed to the next step.
</p></li><li><p>
<span class="emphasis"><em>Yes</em></span>: Does the table match any of
them?
</p><div class="itemizedlist"><ul type="circle"><li><p>
<span class="emphasis"><em>No</em></span>: Proceed to the next step.
</p></li><li><p>
<span class="emphasis"><em>Yes</em></span>: Execute the statement and
exit.
</p></li></ul></div></li></ul></div></li><li><p>
Are there any <code class="option">--replicate-wild-ignore-table</code>
options?
</p><div class="itemizedlist"><ul type="disc"><li><p>
<span class="emphasis"><em>No</em></span>: Proceed to the next step.
</p></li><li><p>
<span class="emphasis"><em>Yes</em></span>: Does the table match any of
them?
</p><div class="itemizedlist"><ul type="circle"><li><p>
<span class="emphasis"><em>No</em></span>: Proceed to the next step.
</p></li><li><p>
<span class="emphasis"><em>Yes</em></span>: Ignore the statement and
exit.
</p></li></ul></div></li></ul></div></li><li><p>
No <code class="option">--replicate-*-table</code> option was matched. Is
there another table to test against these options?
</p><div class="itemizedlist"><ul type="disc"><li><p>
<span class="emphasis"><em>No</em></span>: We have now tested all tables to
be updated and could not match any option. Are there
<code class="option">--replicate-do-table</code> or
<code class="option">--replicate-wild-do-table</code> options?
</p><div class="itemizedlist"><ul type="circle"><li><p>
<span class="emphasis"><em>No</em></span>: There were no
“<span class="quote">do</span>” table options, so no explicit
“<span class="quote">do</span>” match is required. Execute the
statement and exit.
</p></li><li><p>
<span class="emphasis"><em>Yes</em></span>: There were “<span class="quote">do</span>”
table options, so the statement is executed only with
an explicit match to one of them. Ignore the statement
and exit.
</p></li></ul></div></li><li><p>
<span class="emphasis"><em>Yes</em></span>: Loop.
</p></li></ul></div></li></ol></div><p>
Examples:
</p><div class="itemizedlist"><ul type="disc"><li><p>
No <code class="option">--replicate-*</code> options at all
</p><p>
The slave executes all statements that it receives from the
master.
</p></li><li><p>
<code class="option">--replicate-*-db</code> options, but no table
options
</p><p>
The slave permits or ignores statements using the database
options. Then it executes all statements permitted by those
options because there are no table restrictions.
</p></li><li><p>
<code class="option">--replicate-*-table</code> options, but no database
options
</p><p>
All statements are permitted at the database-checking stage
because there are no database conditions. The slave executes
or ignores statements based on the table options.
</p></li><li><p>
A mix of database and table options
</p><p>
The slave permits or ignores statements using the database
options. Then it evaluates all statements permitted by those
options according to the table options. In some cases, this
process can yield what might seem a counterintuitive result.
Consider the following set of options:
</p><pre class="programlisting">[mysqld]
replicate-do-db = db1
replicate-do-table = db2.mytbl2
</pre><p>
Suppose that <code class="literal">db1</code> is the default database
and the slave receives this statement:
</p><pre class="programlisting">INSERT INTO mytbl1 VALUES(1,2,3);
</pre><p>
The database is <code class="literal">db1</code>, which matches the
<code class="option">--replicate-do-db</code> option at the
database-checking stage. The algorithm then proceeds to the
table-checking stage. If there were no table options, the
statement would be executed. However, because the options
include a “<span class="quote">do</span>” table option, the statement must
match if it is to be executed. The statement does not match,
so it is ignored. (The same would happen for any table in
<code class="literal">db1</code>.)
</p></li></ul></div></div></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ha-overview.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="mysql-cluster.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 14. High Availability, Scalability, and DRBD </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 16. MySQL Cluster</td></tr></table></div></body></html>
distrib
>
Mandriva
>
2008.1
>
x86_64
>
media
>
main-testing
>
by-pkgid
>
b1e2421f2416edfc24c5845fbc1c5a2e
>
files
>
108
