Setting Up Replication | Mastering MySQL 4

A number of scenarios exist for setting up a master and slave relationship. You'll see some of these in the examples later in this chapter. The steps that follow are the basic ones to get replication started.

Perform the following steps on the master:

Set up a replication user, with the REPLICATION SLAVE permission:

GRANT REPLICATION_SLAVE ON *.* TO replication_user IDENTIFIED BY  'replication_password';

Make a copy of the tables and data. If the database has been used a while and binary logging is already in place (see Chapter 10, "Basic Administration," for details on binary logging), take note of the offset immediately after the backup. (See the "Replication Commands" section later in the chapter for more information.) The LOAD DATA FROM MASTER operation on the slave can take the place of this step. LOAD DATA FROM MASTER currently only works with MyISAM tables, and is best used with small datasets or with situations where data on the master can be locked for the duration of the operation. Version 4.1 should solve some of these deficiencies.
Add the code below to the configuration file (my.cnf or my.ini). log-bin indicates that the master will use binary update logging, and server-id is a unique number to differentiate each of the master and slave machines. By convention, the master is usually set to 1, and the slaves from 2 upward:
```
[mysqld] log-bin server-id=1 
```

Perform the following steps on the slave/s:

Add the following to the configuration file (my.cnf or my.ini). The master_hostname is the hostname of the master, the master_user and master_password values are the username and password, respectively, set up on the master for replication (with the replication slave privilege). The master_TCP/IP_port is the port number the master listens on (which is only needed if the port is nonstandard), and the unique number is a number starting from 2 up to 2^32-1:
```
[mysqld] master-host=master_hostname master-user=replication_user master-password=replication_password master-port=master_TCP/IP_port server-id=unique_number 
```
Copy the data taken from the master onto the slave (if you're not running LOAD DATA FROM MASTER).
Start the slave server.
If you haven't yet got the data, use LOAD DATA FROM MASTER to access it.

Now, with both servers running, you should start to see replication occurring.

Replication Options

Table 12.1 describes the various replication options available to the master, and Table 12.2 describes the various replication options available to the slave.

Table 12.1: Master Configuration File Options
Option	Description
log-bin=filename	Activates binary logging. This option must be present on the master for replication to occur. The filename is optional. To clear the log, run RESET MASTER, and do not forget to run RESET SLAVE on all slaves. By default the binary log will be called hostname.xxx, with xxx being a number starting at 001 and incrementing by one each time the log is rotated.
log-bin-index=filename	Specifies the name of the binary log index file (which lists the binary log files in order, so the slave will always know the active one). The default is hostname.index.
sql-bin-update-same	If set, setting SQL_LOG_BIN to either 1 or 0 will automatically set SQL_ LOG_UPDATE to the same value, and vice versa. SQL_LOG_UPDATE should not be needed any more, so this option is unlikely to be used.
binlog-do-db=database_name	Only logs updates to the binary log from the database_name database. Allother databases are ignored. You can restrict databases from the slave as well.
binlog-ignore-db= database_name	Logs all updates to the binary log except from the database_name database. You can also set the database to ignore on the slave.

Table 12.2: Slave Configuration File Options
Option	Description
master-host=host	Specifies the hostname or IP address of the master to which to connect. Has to be set for replication to begin. Once replication has begun, the master.info data will determine this, and you'll need to use a CHANGE MASTER TO statement to change it.
master-user=username	Specifies the username the slave will connect to the master with. The user should have REPLICATION SLAVE permission on the master. Defaults to test. Once replication has begun, the master.info data will determine this, and you'll need to use a CHANGE MASTER TO statement to change it.
master-password=password	Specifies the password with which the slave will connect to the master. Defaults to an empty string. Once replication has begun, the master.info data will determine this, and you'll need to use a CHANGE MASTER TO statement to change it.
master-port=portnumber	Specifies the port the master listens on (defaults to the value of MYSQL_ PORT, usually 3306). Once replication has begun, the master.info data will determine this, and you'll need to use a CHANGE MASTER TO statement to change it.
master-connect-retry= seconds	If the connection between the master and slave goes down, MySQL will wait this many seconds before trying to reconnect (default 60).
master-ssl	Specifies that replication take place using Secure Sockets Layer (SSL).
master-ssl-key=key_name	If SSL is set to be used (the master-ssl option), this specifies the master SSL key filename.
master-ssl-cert= certificate_name	If SSL is set to be used (the master-ssl option), this specifies the master SSL certificate name.
master-info-file=filename	Specifies the master information file (default master.info in the data directory), which keeps track of the point in the binary logs that the slave is at in the replication process.
report-host	Specifies the hostname or IP address that the slave will announce itself as to the master (for use during a SHOW SLAVE HOSTS statement). Not set by default. Other methods of determining the host are not reliable; hence the need for this option.
report-port	Specifies the port for connecting to the slave reported to the master. You should only need this if the slave is on a nondefault port, or connection takes place in a nonstandard way.
replicate-do-table= db_name.table_name	Ensures that the slave only replicates the specified table name, from the specified database. You can use this option multiple times to replicate multiple tables.
replicate-ignore-table= db_name.table_name	Tells the slave not to replicate a statement that updates the specified table (even if other tables are also updated by the same statement). You can specify multiple instances of this option.
replicate-wild-do-table= db_name.table_name	Tells the slave to replicate statements only where they match the specified table (similar to the replicate_do_table option), but where the matchtakes into account wildcards. For example, where the table name isdb%.tb%, the match will apply to any database beginning with the letters db, and any table beginning with the letters tb.
replicate-wild-ignore-table=db_ name.table_name	Tells the slave not to replicate a statement that updates the specified table, even if other tables are also updated by the same statement, similar to the replicate-ignore-table option, except that wildcards are taken into account. For example, where the table name is db%.tb% replication will not be performed where the database begins with the letters db, and the table begins with the letters tb). You can specify multiple instances of this option.
replicate-ignore-db= database_name	Tells the slave not to replicate any statement when the current database is database_name. You can use this option multiple times to specify multiple databases to ignore.
replicate-do-db= database_name	Tells the slave thread to replicate a statement only when the database is database_name. You can use this option multiple times to replicate multiple databases.
log-slave-updates	Tells the slave to log replicated updates to the binary log. Not set by default. If you plan to use the slave as a master to another slave, you'll needto set this option.
replicate-rewrite-db= master_database-> slave_database	If the database on your slave has a different name to that on the master, you'll need to map the relationship with this option.
slave-skip-errors= [err_code1,err_ code2,... \| all]	When replication encounters an error, it will stop (since an error usually means the data is inconsistent, and manual steps are needed). This option tells MySQL to continue replicating if the error is one of the listed errors. Error codes are supplied as a number (the same number given in the error log) and separated by a comma. You can also use the all option to cater for any possible errors. You should not normally use this option, as mistaken use of it can lead to your data getting out of sync with the master, with no realistic way of getting it back in sync besides recopying the master data.
skip-slave-start	With this option set, replication will not begin when the server starts. You can manually begin it with the SLAVE START command.
slave_compressed_protocol=#	If set to 1, then MySQL uses compression to transfer the data between slave and master if both servers support this.
slave_net_timeout=#	Determines the number of seconds to wait for more data from the master before a read is aborted.

Replication Commands

You should be familiar with both the slave and the master replication commands. The following are the slave replication commands:

SLAVE START and SLAVE STOP start and stop the replication process, respectively.
SHOW SLAVE STATUS returns information about the slave, including the important fact whether the slave is connected to the master (Slave_IO_Running), replication is running (Slave_SQL_Running), what binary log is being used (Master_Log_File and Relay_Master_Log_ File), and what position is current in the binary log (Read_Master_Log_Pos and Exec_ master_log_pos).
The CHANGE MASTER TO statement is an important one for keeping the replication in sync or starting it off at the right place. The MASTER_LOG_FILE refers to the binary log on the master from which the slave must start replicating, and the MASTER_LOG_POS is the position in that file. (You'll see examples of this later in the chapter.) This statement is also used when the master fails, and you need to change the master to which the slave connects. The full set of CHANGE MASTER TO options is:
```
CHANGE MASTER TO MASTER_HOST = 'master_hostname',     MASTER_USER='replication_username',     MASTER_PASSWORD=''replication_user_password',    MASTER_PORT='master_port',    MASTER_LOG_FILE='master_binary_logfile',    MASTER_LOG_POS='master_binary_log_position'
```
The RESET SLAVE statement causes the slave to forget its position in the master logs.
LOAD DATA FROM MASTER takes a copy of the data on the master and brings it onto the slave. Currently, this is not useful for large datasets or for situations where the master cannot be unavailable for long, as it acquires a global read lock when copying the data. It also updates the value of MASTER_LOG_FILE and MASTER_LOG_POS. Currently it only works with MyISAM tables. This statement is likely to become the standard way of preparing the slave in future, so be sure to read your latest documentation.
The SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n statement causes the slave to skip the next n statements from the master's binary log.

The following are the master replication commands:

The SET SQL_LOG_BIN = n statement either deactivates binary update logging (if set to 0) or reactivates it (if set to 1). You need the SUPER privilege to run this statement.
RESET MASTER removes all binary logs and starts numbering again at 001.
SHOW MASTER STATUS shows the current binary log, the position in the binary log, and whether any databases are being ignored for binary logging.
PURGE MASTER LOGS TO binary_log_filename removes all logs prior to the specified binary log. Be sure that no slaves still need the binary logs you're about to remove. See the example later in the "Removing Old Binary Logs from the Master and Starting" section.
SHOW MASTER LOGS shows the list of binary log files available. You'd usually use this before purging the logs.
SHOW SLAVE HOSTS returns a list of slaves registered with the master (note that by default a slave does not register itself but requires the report-host configuration option to be set).
The SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ] statement reads statements from the binary logs.

Replication Complexities

The following are a few crucial issues you need to keep in mind when setting up and configuring replication:

FLUSH statements are not replicated, which will affect you if you update the permission tables directly on the master and then use FLUSH to activate the changes. The changes will not take effect on the slaves until you run a FLUSH statement there as well.
Make sure your masters and slaves have the same character set.
The RAND() function does not work properly when passed a random expression as an argument. You can safely use something like UNIX_TIMESTAMP().
Queries that update data and use user variables are not replicated safely (although this is set to change. Check your latest documentation).
Replication usually works with different MySQL versions, even between version 3.23.x and version 4.0.x, but there have been exceptions (none of 4.0.0, 4.0.1, or 4.0.2 worked with each other), so check the latest documentation to be sure. Instead, use the latest stable versions throughout if possible.