Replication

1. Creating a User for Replication

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 REPLICATION SLAVE privilege.

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 master.info file. Therefore you may want to create a user that only has privileges for the replication process.

To create a user or grant an existing user the privileges required for replication use the GRANT statement. If you create a user solely for the purposes of replication then that user only needs the REPLICATION SLAVE privilege. For example, to create a user, repl, that allows all hosts within the domain mydomain.com to connect for replication:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

2. Setting the Replication Master Configuration

For replication to work you must 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.

Each server within a replication group must have a unique server-id. The server-id is used to identify individual servers within the group, and must be positive integer between 1 and (232)-1). How you organize and select the numbers is entirely up to you.

To configure both these options you will need to shut down your MySQL server and edit the configuration of the my.cnf or my.ini file.

You will need to add the following options to the configuration file within the [mysqld] 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:

[mysqld]
log-bin=mysql-bin
server-id=1

3. Setting the Replication Slave Configuration

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:

[mysqld]
server-id=2

If you are setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from each of the other slaves. Think of server-id values as something similar to IP addresses: These IDs uniquely identify each server instance in the community of replication partners.

If you do not specify a server-id value, it is set to 1 if you have not defined master-host; otherwise it is set to 2. Note that in the case of server-id omission, a master refuses connections from all slaves, and a slave refuses to connect to a master. Thus, omitting server-id is good only for backup with a binary log.

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.

4. Obtaining the Master Replication Information

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.

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.

To get the master status information, follow these steps:

1. Start the command line client and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:

     mysql> FLUSH TABLES WITH READ LOCK;

2. Use the SHOW MASTER STATUS statement to determine the current binary log name and offset on the master:

      mysql > SHOW MASTER STATUS;
      +---------------+----------+--------------+------------------+
      | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
      +---------------+----------+--------------+------------------+
      | mysql-bin.003 | 73       | test         | manual,mysql     |
      +---------------+----------+--------------+------------------+

The File column shows the name of the log and Position shows the offset within the file. In this example, the binary log file is mysql-bin.003 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.

If the master has been running previously without binary logging enabled, the log name and position values displayed by SHOW MASTER STATUS or mysqldump –master-data 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 () and 4.

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.

5. Creating a Data Snapshot Using Raw Data Files

If your database is particularly large then copying the raw data files may be more efficient than using mysqldump and importing the file on each slave.

To create a raw data snapshot of MyISAM tables you can use standard copy tools such as cp or copy, a remote copy tool such as scp or rsync an archiving tool such as zip or tar, or a file system snapshot tool such as dump, providing that your MySQL data files exist on a single filesystem. If you are only replicating certain databases then make su

re you only copy those files that related to those tables. (For InnoDB, all tables in all databases are stored in a single file unless you have the innodb_file_per_table option enabled.)

You may want to specifically exclude the following files from your archive:

• Files relating to the mysql database.
• The master.info file.
• The master’s binary log files.
• Any relay log files.

To get the most consistent results with a raw data snapshot you should shut down the server during the process, as below:

1. Acquire a read lock and get the master’s status. 2. In a separate session, shut down the MySQL server:

      shell> mysqladmin shutdown

3. Take a copy of the MySQL data files. Examples are shown below for common solutions – you need to choose only one of these solutions:

      shell> tar cf /tmp/db.tar ./data
      shell> zip -r /tmp/db.zip ./data
      shell> rsync --recursive ./data /tmp/dbdata

4. Start up the MySQL instance on the master.

If you are not using InnoDB tables, you can get a snapshot of the system from a master without shutting down the server as described in the following steps:

1. Acquire a read lock and get the master’s status. See Section 16.1.1.4, “Obtaining the Master Replication Information”. 2. Take a copy of the MySQL data files. Examples are shown below for common solutions – you need to choose only one of these solutions:

      shell> tar cf /tmp/db.tar ./data
      shell> zip -r /tmp/db.zip ./data
      shell> rsync --recursive ./data /tmp/dbdata

3. In the client where you acquired the read lock, free the lock:

      mysql> UNLOCK TABLES;

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.

6. Setting the Master Configuration on the Slave

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:

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

7. Starting the slave threads

Start the slave using:

mysql> START SLAVE;

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. You also find error messages in the slave’s error log if it is not able to replicate for any other reason.

Once a slave is replicating, you can find in its data directory one file named master.info and another named relay-log.info. The slave uses these two files to keep track of how much of the master’s binary log it has processed. Do not 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 CHANGE MASTER TO statement to change replication parameters. The slave will use the values specified in the statement to update the status files automatically.