Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 CHANGE MASTER TO 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 CHANGE MASTER TO 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 CHANGE MASTER TO 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 Starting Replication.”