[MySQL]: How do we set-up Replication

How to Set Up Replication: 

          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 to use depends on how you are setting up replication, and whether you already have data within your master database.

 There are some generic tasks that are common to all replication setups: 

• On the master, you must enable binary logging and configure a unique server ID. This might require a server restart.
• On each slave that you want to connect to the master, you must configure a unique server ID. This might require a server restart
• 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.
• Before creating a data snapshot or starting 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.
• If you already have data on your master and you want to use it to synchronize your slave, you will need to create a data snapshot. You can create a snapshot using mysqldump or by copying the data files directly. 

          Once you have configured the basic options, you will need to follow the instructions for your replication setup. A number of alternatives are provided: 

• If you are establishing a new MySQL master and one or more slaves, you need only set up the configuration, as you have no data to exchange.
• If you are already running a MySQL server, and therefore already have data that must be transferred to your slaves before replication starts.
• If you are adding slaves to an existing replication environment, you can set up the slaves without affecting the master.

 

Setting Up Replication with New Master and Slaves:

          The easiest and most straightforward method for setting up replication is to use new master and slave servers.

          You can also use this method if you are setting up new servers but have an existing dump of the databases from a different server that you want to load into your replication configuration. By loading the data into a new master, the data will be automatically replicated to the slaves.

Setting the Replication Master Configuration:

          On a master, you must enable binary logging and establish a unique server ID. If this has not already been done, this part of master setup requires a server restart.

          Each server within a replication group must be configured with a unique server ID. This ID is used to identify individual servers within the group, and must be a positive integer between 1 and (232)–1. How you organize and select the numbers is entirely up to you

Step 1:

          To configure the binary log and server ID options, you will need to shut down your MySQL server and edit the my.cnf or my.ini file. Add the following options to the configuration file within the [mysqld] section.

[mysqld]

log-bin=mysql-bin

server-id=1

# skip-networking

After making the changes, restart the server.

Ensure that the skip-networking option is not enabled on your master. If networking has been disabled, your slave will not able to communicate with the master and replication will fail.

Step 2:

          Each slave must connect to the master using a MySQL user name and password, so there must be a user account on the master that the slave can use to connect. Any account can be used for this operation, providing it has been granted the REPLICATION SLAVE privilege. You may wish to create a different account for each slave, or connect to the master using the same account for each slave.

          You need not create an account specifically for replication. However, you should be aware that the user name and password will be stored in plain text within the master.info file. Therefore, you may want to create a separate account that has privileges only for the replication process, to minimize the possibility of compromise to other accounts.

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’slave_hostname’ IDENTIFIED BY ‘password’; 

Step 3:

          To configure replication on the slave you must determine the master’s current coordinates within its 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. 

To obtain the master binary log coordinates, follow these steps: 

1. Start a session on the master by connecting to it with 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;

 

For InnoDB tables, note that FLUSH TABLES WITH READ LOCK also blocks COMMIT operations. 

2. In a different session on the master, use the SHOW MASTER STATUS statement to determine the current binary log file name and position: 

mysql > SHOW MASTER STATUS;

+————————+———–+——————–+————————–+

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB  |

+————————+———–+——————–+————————–+

| mysql-bin.000003 | 73       |             |                   |

+————————+———–+——————–+————————–+ 

          The File column shows the name of the log file and Position shows the position within the file. 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. 

In the first session release the read lock:

mysql> UNLOCK TABLES; 

          If the master has been running previously without binary logging enabled, the log file name and position values displayed by SHOW MASTER STATUS will be empty. 

          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.

Setting the Replication Slave Configuration:

Step 4:          On a replication slave, you must establish a unique server ID. If this has not already been done, this part of slave setup requires a server restart.

[mysqld]

server-id=2 

After making the changes, restart the server.

          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.

          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, 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 (for example, where the slave acts as a master to other slaves).

Step 5:

          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; 

The  MASTER_LOG_FILE and  MASTER_LOG_POS values should be used here which are obtained from Step 3. 

The CHANGE MASTER TO statement has other options as well. 

Step 6:

After that issue a command to start slave.

mysql> START SLAVE;

The above statement starts both sql thread and IO thread.

          When a START SLAVE 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 slave creates an SQL thread to read the relay log that is written by the slave I/O thread and execute the events contained therein.

 

To start or stop just SQL Thread use

mysql> START/STOP SLAVE SQL_THREAD;

To start or stop just SQL Thread use

mysql> START/STOP SLAVE IO_THREAD;

Step 7:

To see replication status use below command:

mysql> SHOW SLAVE STATUS\G

Setting Up Replication with Existing Master and New Slave:

          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.

1. If server_id and binary logging are not enabled on the master you need to enable it and restart the server (see step 1).
2. If the MySQL master is running, create a user to be used by the slave when connecting to the master during replication (see step 2).

Creating a Data Snapshot Using mysqldump:

3. Start a session on the server by connecting to it with 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;

          In another session, use mysqldump to create a dump either of all the databases you want to replicate, or of selected individual databases. For example:

          [shell]# mysqldump –all-databases –lock-all-tables > dbdump.sql

          Transfer the dump file to slave server.

          [shell]# scp dbdump.sql slaveip:/<path>

          password:

4. Obtain master status by using SHOW MASTER STATUS (see Step 3).
5. Update the configuration of the slave (see Step 4).
6. Import the dump file:

          [shell]# mysql < dbdump.sql

7. Configure the slave with the replication coordinates from the master (see Step 5).
8. Start the slave threads (see Step 6).

Replication Options and Variables:

server-id=<value>

          Need to use unique server id to setup a replication configuration. 

–master-info-file=file_name

          The name to use for the file in which the slave records information about the master. The default name is master.info in the data directory. 

–log-slave-updates

          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 –log-bin option to enable binary logging. –log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement: 

A -> B -> C 

–master-connect-retry=seconds

          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.If not set, the default is 60.The number of reconnection attempts is limited by the –master-retry-count option.

 –master-retry-count=count

          The number of times that the slave tries to connect to the master before giving up. Reconnects are attempted at intervals set by the –master-connect-retry option. The default value is 86400. A value of 0 means “infinite”; the slave attempts to connect forever. 

–max-relay-log-size=size

          The size at which the server rotates relay log files automatically. 

–read-only

          Cause the slave to permit no updates except from slave threads or from users having the SUPER privilege. On a slave server, this can be useful to ensure that the slave accepts updates only from its master server and not from clients. 

–relay-log=file_name

          The basename for the relay log. The default basename is host_name-relay-bin. The server writes the file in the data directory unless the basename is given with a leading absolute path name to specify a different directory. 

–relay-log-index=file_name

          The name to use for the relay log index file. The default name is host_name-relay-bin.index in the data directory 

-relay-log-info-file=file_name

          The name to use for the file in which the slave records information about the relay logs. The default name is relay-log.info in the data directory. 

–relay-log-purge={0|1}

          Disable or enable automatic purging of relay logs as soon as they are no longer needed. The default value is 1 (enabled). This is a global variable that can be changed dynamically. 

–replicate-do-db=db_name

          The effects of this option depend on whether statement-based or row-based replication is in use. 

SBR:  Tell the slave SQL thread to restrict replication to statements where the default database (that is, the one selected by USE) is db_nam. To specify more than one database, use this option multiple times, once for each database; however, doing so does not replicate cross-database statements such as UPDATE some_db.some_table SET foo=’bar’ while a different database (or no database) is selected. 

RBR: Tells the slave SQL thread to restrict replication to database db_name. Only tables belonging to db_name are changed; the current database has no effect on this. However, issuing cross update statements on the master has no effect on the slave when using row-based replication and –replicate-do-db.

 –replicate-ignore-db=db_name

          As with –replicate-do-db, the effects of this option depend on whether statement-based or row-based replication is in use. 

SBR: Tells the slave SQL thread not to replicate any statement where the default database (that is, the one selected by USE) is db_name. 

RBR: Tells the slave SQL thread not to update any tables in the database db_name. The default database has no effect. When using statement-based replication, the following example does not work as you might expect. Suppose that the slave is started with –replicate-ignore-db=sales and you issue the following statements on the master: 

USE prices;

UPDATE sales.january SET amount=amount+1000; 

          The UPDATE statement is replicated in such a case because –replicate-ignore-db applies only to the default database (determined by the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered. However, when using row-based replication, the UPDATE statement’s effects are not propagated to the slave, and the slave’s copy of the sales.january table is unchanged.

 –replicate-do-table=db_name.tbl_name

          Tells the slave SQL 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 both cross-database updates and default database updates. 

–replicate-ignore-table=db_name.tbl_name

          Tells the slave SQL thread not to 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.

 

–replicate-rewrite-db=from_name->to_name

          Tells the slave to translate the default database (that is, the one selected by USE) to to_name if it was from_name on the master. Only statements involving tables are affected and only if from_name is the default database on the master. This does not work for cross-database updates. To specify multiple rewrites, use this option multiple times.

 

–replicate-wild-do-table=db_name.tbl_name

          Tells the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns. To specify more than one table, use this option multiple times, once for each table. This works for cross-database updates. 

Example: –replicate-wild-do-table=foo%.bar% replicates only updates that use a table where the database name starts with foo and the table name starts with bar. 

–replicate-wild-ignore-table=db_name.tbl_name

          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. 

–slave-skip-errors=[err_code1,err_code2,…|all]

          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. 

Examples:

–slave-skip-errors=1062,1053

–slave-skip-errors=all

 

Ask Question