Mysql Replication

Mysql Replication Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous - slaves need not be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database. Uses for replication in MySQL include: Scale-out solutions - spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves. Data security - because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data. Analytics - live data can be created on the master, while the analysis of the information can take place on the slave without affecting the performance of the master. Long-distance data distribution - if a branch office would like to work with a copy of your main data, you can use replication to create a local copy of the data for their use without requiring permanent access to the master.

Setting the Replication Master Configuration: On a replication master, you must enable binary logging and establish a unique server ID. Binary logging must be enabled on the master because the binary log is the basis for sending data changes from the master to its slaves. If binary logging is not enabled, replication will not be possible.  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.  To configure the binary log and server ID options, you will need to shut down your MySQL server and edit themy.cnf or my.ini file. Add the following options to the configuration file within the [mysqld] section. For example, to enable binary logging using a log file name prefix of mysql-bin, and configure a server ID of 1, use these lines: [mysqld] log-bin=mysql-bin server-id=1 After making the changes, restart the server.

Setting the Replication Slave Configuration: On a replication slave, you must establish a unique server ID. [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.

Creating a User for Replication: 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. mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

Obtaining the Replication Master Binary Log Coordinates: 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.  If you have existing data on your master that you want to synchronize on your slaves before starting the replication process, you must stop processing statements on the master, and then obtain its current binary log coordinates and dump its data, before permitting the master to continue executing statements. If you do not stop the execution of statements, the data dump and the master status information that you use will not match and you will end up with inconsistent or corrupted databases on the slaves. 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;         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       | test         | manual,mysql     | +------------------+----------+--------------+------------------+                 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. If the master has been running previously without binary logging enabled, the log file 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.

Creating a Data Snapshot Using mysqldump: One way to create a snapshot of the data in an existing master database is to use the mysqldump tool. Once the data dump has been completed, you then import this data into the slave before starting the replication process. To obtain a snapshot of the data using mysqldump: 1.       If you have not already locked the tables on the server to prevent statements that update data from executing: 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; Remember to use SHOW MASTER STATUS and record the binary log details for use when starting up the slave. The point in time of your snapshot and the binary log position must match. 2.       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.db An alternative to using a bare dump, is to use the --master-data option, which automatically appends theCHANGE MASTER TO statement required on the slave to start the replication process. shell> mysqldump --all-databases --master-data >dbdump.db 3.       In the client where you acquired the read lock, release the lock: mysql> UNLOCK TABLES; Alternatively, exit the first session to release the read lock.

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. To set up replication between a new master and slave:        1. Configure the MySQL master with the necessary configuration properties.        2. Start up the MySQL master.        3. Set up a user.        4. Obtain the master status information.        5. On the master, release the read lock: mysql> UNLOCK TABLES;        6. On the slave, edit the MySQL configuration.        7. Start up the MySQL slave.        8. Execute a CHANGE MASTER TO statement to set the master replication server configuration.  Perform the slave setup steps on each slave. Because there is no data to load or exchange on a new server configuration you do not need to copy or import any information. If you are setting up a new replication environment using the data from a different existing database server, you will now need to run the dump file generated from that server on the new master. The database updates will automatically be propagated to the slaves: shell> mysql -h master < fulldb.dump

Setting Up Replication with Existing Data: 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. The basic process for setting up replication with existing data is as follows: 1.       With the MySQL master running, create a user to be used by the slave when connecting to the master during replication.  2.       If you have not already configured the server-id and enabled binary logging on the master server, you will need to shut it down to configure these options. If you have to shut down your master server, this is a good opportunity to take a snapshot of its databases. You should obtain the master status before taking down the master, updating the configuration and taking a snapshot. 3.       If your master server is already correctly configured, obtain its status  and then use mysqldump to take a snapshot  or take a raw snapshot of the live server. 4.       Update the configuration of the slave. 5.       The next step depends on how you created the snapshot of data on the master. If you used mysqldump: a.       Start the slave, using the --skip-slave-start option so that replication does not start. b.      Import the dump file: shell> mysql < fulldb.dump If you created a snapshot using the raw data files: Extract the data files into your slave data directory. For example: shell> tar xvf dbdump.tar You may need to set permissions and ownership on the files so that the slave server can access and modify them. Start the slave, using the --skip-slave-start option so that replication does not start. 6.       Configure the slave with the replication coordinates from the master. This tells the slave the binary log file and position within the file where replication needs to start. Also, configure the slave with the login credentials and host name of the master.  7.       Start the slave threads: 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.

Introducing Additional Slaves to an Existing Replication Environment: To add another slave to an existing replication configuration, you can do so without stopping the master. Instead, set up the new slave by making a copy of an existing slave, except that you configure the new slave with a differentserver-id value. To duplicate an existing slave: 1.       Shut down the existing slave: shell> mysqladmin shutdown 2.       Copy the data directory from the existing slave to the new slave. You can do this by creating an archive using taror WinZip, or by performing a direct copy using a tool such as cp or rsync. Ensure that you also copy the log files and relay log files. 3.       Copy the master.info and relay-log.info files from the existing slave to the new slave if they were not located in the data directory. These files hold the current log coordinates for the master's binary log and the slave's relay log. 4.       Start the existing slave. 5.       On the new slave, edit the configuration and give the new slave a unique server-id not used by the master or any of the existing slaves. 6.       Start the new slave. The slave will use the information in its master.info file to start the replication process.

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;

Checking Replication Status: The most common task when managing a replication process is to ensure that replication is taking place and that there have been no errors between the slave and the master. The primary statement for this is SHOW SLAVE STATUS, which you must execute on each slave: mysql> SHOW SLAVE STATUS\G *************************** 1. row ***************************              Slave_IO_State: Waiting for master to send event                 Master_Host: master1                 Master_User: root                 Master_Port: 3306               Connect_Retry: 60             Master_Log_File: mysql-bin.000004         Read_Master_Log_Pos: 931              Relay_Log_File: slave1-relay-bin.000056               Relay_Log_Pos: 950       Relay_Master_Log_File: mysql-bin.000004            Slave_IO_Running: Yes           Slave_SQL_Running: Yes             Replicate_Do_DB:         Replicate_Ignore_DB:          Replicate_Do_Table:      Replicate_Ignore_Table:     Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:                  Last_Errno: 0                  Last_Error:                Skip_Counter: 0         Exec_Master_Log_Pos: 931             Relay_Log_Space: 1365             Until_Condition: None              Until_Log_File:               Until_Log_Pos: 0          Master_SSL_Allowed: No          Master_SSL_CA_File:          Master_SSL_CA_Path:             Master_SSL_Cert:           Master_SSL_Cipher:              Master_SSL_Key:       Seconds_Behind_Master: 0 The key fields from the status report to examine are: 1.       Slave_IO_State: The current status of the slave. 2.       Slave_IO_Running: Whether the I/O thread for reading the master's binary log is running. Normally, you want this to be Yes unless you have not yet started replication or have explicitly stopped it with STOP SLAVE. 3.       Slave_SQL_Running: Whether the SQL thread for executing events in the relay log is running. As with the I/O thread, this should normally be Yes. 4.       Last_Error: The last error registered when processing the relay log. Ideally this should be blank, indicating no error. 5.       Seconds_Behind_Master: The number of seconds that the slave SQL thread is behind processing the master binary log. A high number (or an increasing one) can indicate that the slave is unable to handle events from the master in a timely fashion. 6.       Master_Log_file, Read_Master_Log_Pos: Coordinates in the master binary log indicating how far the slave I/O thread has read events from that log. 7.       Relay_Master_Log_File, Exec_Master_Log_Pos: Coordinates in the master binary log indicating how far the slave SQL thread has executed events received from that log. 8.       Relay_Log_File, Relay_Log_Pos: Coordinates in the slave relay log indicating how far the slave SQL thread has executed the relay log. These correspond to the preceding coordinates, but are expressed in slave relay log coordinates rather than master binary log coordinates. On the master, you can check the status of connected slaves using SHOW PROCESSLIST to examine the list of running processes. Slave connections have Binlog Dump in the Command field: mysql> SHOW PROCESSLIST \G; *************************** 4. row ***************************      Id: 10    User: root    Host: slave1:58371      db: NULL Command: Binlog Dump    Time: 777   State: Has sent all binlog to slave; waiting for binlog to be updated    Info: NULL Because it is the slave that drives the replication process, very little information is available in this report. The SHOW SLAVE HOSTS statement on the master shows basic information about the slaves. The output includes the ID of the slave serve Pausing Replication on the Slave: You can stop and start the replication of statements on the slave using the STOP SLAVE and START SLAVEstatements. To stop processing of the binary log from the master, use STOP SLAVE: mysql> STOP SLAVE; When replication is stopped, the slave I/O thread stops reading events from the master binary log and writing them to the relay log, and the SQL thread stops reading events from the relay log and executing them. You can pause the I/O or SQL thread individually by specifying the thread type: mysql> STOP SLAVE IO_THREAD; mysql> STOP SLAVE SQL_THREAD; To start execution again, use the START SLAVE statement: mysql> START SLAVE; To start a particular thread, specify the thread type: mysql> START SLAVE IO_THREAD; mysql> START SLAVE SQL_THREAD; r, the value of the --report-host option, the connecting port, and master ID: mysql> SHOW SLAVE HOSTS; +-----------+--------+------+-------------------+-----------+ | Server_id | Host   | Port | Rpl_recovery_rank | Master_id | +-----------+--------+------+-------------------+-----------+ |        10 | slave1 | 3306 |                 0 |         1 | +-----------+--------+------+-------------------+-----------+ 1 row in set (0.00 sec)

Pausing Replication on the Slave: You can stop and start the replication of statements on the slave using the STOP SLAVE and START SLAVEstatements. To stop processing of the binary log from the master, use STOP SLAVE: mysql> STOP SLAVE; When replication is stopped, the slave I/O thread stops reading events from the master binary log and writing them to the relay log, and the SQL thread stops reading events from the relay log and executing them. You can pause the I/O or SQL thread individually by specifying the thread type: mysql> STOP SLAVE IO_THREAD; mysql> STOP SLAVE SQL_THREAD; To start execution again, use the START SLAVE statement: mysql> START SLAVE; To start a particular thread, specify the thread type: mysql> START SLAVE IO_THREAD; mysql> START SLAVE SQL_THREAD;