Section 8.4. Configuring the Servers

8.4. Configuring the Servers

Once the replication user is set up on both servers, you will
need to add some lines to the MySQL configuration file on the master and on the slave server. Depending on the type of
operating system, the configuration file will probably be
called either my.cnf or my.ini.
On Unix types of systems, the configuration file is usually located in the
/etc directory. On Windows systems, it's usually located in
c:\ or in c:\Windows. If the file doesn't exist on
your system, you can create it. Using a plain text editor (e.g., vi or
Notepad.exe)—one that won't add binary formatting—add
the following lines to the configuration file of the master under the
[mysqld] group heading:

[mysqld]
server-id = 1
log-bin = /var/log/mysql/bin.log
...

The server identification number is an arbitrary number used to
identify the master server in the binary log and in communications with
slave servers. Almost any whole number from 1 to 4294967295 is fine. Don't
use 0, as that causes problems. If you don't assign a server number, the
default server identification number of 1 will be used. The default is all
right for the master, but a different one should be assigned to each
slave. To keep log entries straight and avoid confusion in communications
between servers, it is very important that each slave have a unique
number.

In the configuration file excerpt shown here, the line containing
the log-bin option instructs MySQL to perform
binary logging to the path and file given. The actual path
and filename is mostly up to you. Just be sure that the directory exists
and that the user mysql is the owner, or at least has
permission to write to the directory. By default, if a path is not given,
the server's data directory is assumed as the path for log files. To leave
the defaults in place, give log-bin without the equals
sign and without the file pathname. This example shows the default
pathname. If you set the log file name to something else, keep the suffix
.log as shown here. It will be replaced automatically
with an index number (e.g., .000001) as new log files
are created when the server is restarted or the logs are flushed.

These two options are all that is required on the master. They can
be put in the configuration file or given from the command line when
starting the mysqld daemon each time. On the command
line, add the required double dashes before each option and omit the
spaces around the equals signs.

For InnoDB tables, you may want to add the following lines to the master's
configuration file:

innodb_flush_log_at_trx_commit = 1
sync-binlog = 1

These lines resolve problems that can occur with transactions and
binary logging.

For the slave server, we will need to add several options to the
slave's configuration file, reflecting the greater complexity and number
of threads on the slave. You will have to provide a server identification
number, information on connecting to the master server, and more log
options. Add lines similar to the following to the slave's configuration
file:

[mysqld]
server-id = 2

log-bin = /var/log/mysql/bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log

relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index

slave-load-tmpdir = /var/log/mysql/
skip-slave-start
...

At the top, you can see the server identification number is set to
2. The next stanzas set the logs and related index files. If these files
don't exist when the slave is started, it will automatically create
them.

The second stanza starts binary logging like on the master server,
but this time on the slave. This is the log that can be used to allow the
master and the slave to reverse roles as mentioned earlier. The binary log index file (log-bin.index)
records the name of the current binary log file to use. The
log-error option establishes an error log. Any problems
with replication will be recorded in this log.

The third stanza defines the relay log that records each entry in
the master server's binary log, along with related files mentioned
earlier. The relay-log-info-file option names the file
that records the most recent position in the master's binary log that the
slave recorded for later execution (not the most recent statement actually
executed by the slave), while the relay log index file in turn records the
name of the current relay log file to use for replication.

The slave-load-tmpdir option is necessary only if
you expect the LOAD DATA INFILE statement to
be executed on the server. This SQL statement is used to import data in
bulk into the databases. The slave-load-tmpdir option
specifies the temporary directory for those files. If you don't specify
the option, the value of the tmpdir variable will be
used. This relates to replication because the slave will log LOAD
DATA INFILE activities to the log files with the prefix
SQL_LOAD- in this directory. For security, you may not
want those logs to be placed in a directory such as
/tmp.

The last option, skip-slave-start, prevents the
slave from replicating until you are ready. The order and spacing of
options, incidentally, are a matter of personal style.

To set variables on the slave related to its connection with the
master (e.g., the master's host address), it is recommended that you use
the [click here] statement to set the values on the
slave. You could provide the values in the configuration file. However,
the slave will read the file only the first time you start up the slave
for replication. Because the values are stored in the master.info file, MySQL just relies
on that file during subsequent startups and ignores these options in the
main MySQL configuration file. The only time it adjusts the
master.info file contents is when you tell it to
explicitly through a [click here] statement. You could
edit the master.info file and
other replication information files directly, but you might cause more
problems in doing so. It's best to use the [click here]
statement to make changes. Here is an example:

CHANGE MASTER TO MASTER_HOST = 'master_host';
CHANGE MASTER TO MASTER_PORT = 3306;
CHANGE MASTER TO MASTER_USER = 'replicant';
CHANGE MASTER TO MASTER_PASSWORD = 'my_pwd';

This set of SQL statements provides information about the master
server. The first statement gives
the hostname (or the IP address) of the master. The next one provides the
port for the connection. Port 3306 is the default port for MySQL, but
another could be used for performance or security considerations. The next
two lines set the username and password for logging into the master
server. After you run these SQL statements, their values are stored in the
master.info file and you shouldn't need to rerun the
statements upon subsequent startups.

At this point, the servers should be configured properly. Next, you
will need to get the slave's data current by making a backup on the master
server and copying it manually to the slave. This is described in the
following section. If the master and slave are new servers and the master
has no data yet, you can skip the next section and proceed to Section 8.6."