High Availability and Scalability

19.1 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. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database. The target 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. Replication in MySQL features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster.   Advantages:

1. It makes backing up the database easier and safer.
2. Gives more performance with load balancing. we can split the load between two servers by directing reads to the slave server and writes to the master server.
3. We can replicate from one storage engine to another storage engine.
4. We can use this slave server for data analysis, reports etc....instead on impacting the production server we can do this in slave server and even we can shutdown the slave server.

  Disadvantages:

1. The main disadvantage of replication is there is no guaranty of data synchronization between two servers.
2. No automatic fail over technique in case if master fails. This causes a little down time.
3. User defined variables and temporary tables may not work.
4. If replication is set with multiple slaves it may cause some load on master server to updated the statement to all the slaves.

  Replication Formats: Replication is of three formats:

1. STATEMENT BASED
2. ROW BASED
3. MIXED-FORMAT           (Combination of statement based and row based)

  Statement Based: All statements propagate from master to slave and execute the statements on slave.   Advantages of Statement-Based Replication: 

1.    Less data written to log files. This results less storage space for log files.
2. Log files contains all the changes made to the database. so they can be used to track the database changes.

    Disadvantages of Statement-Based Replication:

1.    Queries using function like NOW(), RAND(), CURDATE(), UUID() etc are unsafe.
2. DELETE and UPDATE queries using a LIMIT clause and without using ORDER BY clause are       unsafe.
3. Queries requires more number of locks on tables.

  Row Based: Row-based binary logging logs changes in individual table rows.   Advantages of Row-Based Replication:

1. All queries can be replicated safely.
2. Queries requires less number of locks on tables.

  Disadvantages of Row-Based Replication:

1. Large data written to log files. This results large storage space for log files.

This would take a little longer time when statements like BLOB and TEXT values are             encountered.

2. We cannot track the logs to find the changes committed to the database.

  Mixed Based: When the mixed format is in effect, statement-based logging is used by default, but automatically switches to row-based logging in particular cases   Note:   MIXED-FORMAT replication was introduced from MySQL Version 5.1.8.   19.1.1  Replication Configuration: Replication between servers in MySQL is based on the binary logging mechanism. The MySQL instance operating as the master writes updates and changes as “events” to the binary log. The information in the binary log is stored in different logging formats according to the database changes being recorded. Slaves are configured to read the binary log from the master and to execute the events in the binary log on the slave's local database. Once binary logging has been enabled, all statements are recorded in the binary log. Each slave receives a copy of the entire contents of the binary log. It is the responsibility of the slave to decide which statements in the binary log should be executed; you cannot configure the master to log only certain events. If you do not specify otherwise, all events in the master binary log are executed on the slave. If required, you can configure the slave to process only events that apply to particular databases or tables. Each slave keeps a record of the binary log coordinates: The file name and position within the file that it has read and processed from the master. This means that multiple slaves can be connected to the master and executing different parts of the same binary log. Because the slaves control this process, individual slaves can be connected and disconnected from the server without affecting the master's operation. Also, because each slave remembers the position within the binary log, it is possible for slaves to be disconnected, reconnect and then “catch up” by continuing from the recorded position. Both the master and each slave must be configured with a unique ID. In addition, each slave must be configured with information about the master host name, log file name, and position within that file. These details can be controlled from within a MySQL session using the CHANGE MASTER TO statement on the slave. The details are stored within the slave's master.info file.   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 straight forward 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 in slave:

[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).

Adding a Slave to an existing Slave:  

1. If server_id and binary logging are not enabled on the Slave 1 you need to enable it and also you need to enable log-slave-updates parameter which writes the events from relaylog to binlog and restart the server. (see step 1).
2. Create a user in Slave 1 giving privileges to Slave 2 to connect with REPLICATION SLAVE (see step 2).
3. On Slave 1 Creating a Data Snapshot Using mysqldump (Refer above)
4. Obtain master status in Slave 1 by using SHOW MASTER STATUS (see Step 3).
5. Update the configuration of the Slave 2 (see Step 4).
6. Import the dump file in Slave 2:

[shell]# mysql < dbdump.sql

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

  Replication Scenarios: Bi-directional Replication:     In bi-directional replication clients can retrieve and update the data in both the servers. One problem in a multimaster replication is the conflict that can happen with self-generated keys. The AUTO_INCREMENT feature is quite convenient, but in a replication environment it will be disruptive. If node A and node B both insert an auto-incrementing key on the same table, conflicts arise immediately and replication stops. To over come this issue we need to set below 2 parameters in both servers. Master 1: auto_increment_offset    = 1; auto_increment_increment = 2;   Master 2: auto_increment_offset    = 2; auto_increment_increment = 2; In the first master the auto increment value starts from 1 and increments by 2, so it continues in odd number series. In the second master the auto increment value starts from 2 and increments by 2, so it continues in even number series. As the two servers generate different auto increment values you can over come this issue.   Circular Replication:   In circular replication clients can retrieve and update the data in all the servers. You need to set auto_increment_offset ans auto_increment_increment variables as below.   Master 1: auto_increment_offset    = 1; auto_increment_increment = 3;   Master 2: auto_increment_offset    = 2; auto_increment_increment = 3;   Master 3: auto_increment_offset    = 3; auto_increment_increment = 3;   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   19.2 Replication Solutions

Using Replication for Backups:

Replication server can be used for consistent backups. These backups can be used to configure another slave to this server or else to dump the database into other server. IF you are taking backups using mysqldump utility, you need to stop sql thread (or you can stop both threads) so that the mysql server stops udpating the datafiles. [shell]# mysqladmin stop-slave   (OR) [shell]# mysql -e 'STOP SLAVE SQL_THREAD;' [shell]# mysqldump --all-databases > fulldb.dump [shell]# mysqladmin start-slave   Replication servers can also be used to take physical backups (raw backups).  To take physical backups MySQL Server should be safely shutdowned. You can shutdown the slave MySQL Server and after taking backups you can start the server and replication as well from the same position where it stopped.   [shell]# /etc/init.d/mysql stop [shell]# tar -cvzf dbbackup.tar.gz /var/lib/mysql [shell]# /etc/init.d/mysql start Normally you should back up the entire data directory for the slave MySQL server. If you want to be able to restore the data and operate as a slave (for example, in the event of failure of the slave), then in addition to the slave's data, you should also back up the slave status files, master.info and relay-log.info, along with the relay log files. These files are needed to resume replication after you restore the slave's data. If you lose the relay logs but still have the relay-log.info file, you can check it to determine how far the SQL thread has executed in the master binary logs. Then you can use CHANGE MASTER TO with the MASTER_LOG_FILE and MASTER_LOG_POS options to tell the slave to re-read the binary logs from that point. This requires that the binary logs still exist on the master server.

 

Using Replication with Different Master and Slave Storage Engines:

It does not matter for the replication process whether the source table on the master and the replicated table on the slave use different engine types. This provides a number of benefits in the replication process in that you can take advantage of different engine types for different replication scenarios. For example, in a typical scale-out scenario, you want to use InnoDB tables on the master to take advantage of the transactional functionality, but use MyISAM on the slaves where transaction support is not required because the data is only read. When using replication in a data-logging environment you may want to use the Archive storage engine on the slave.

• If you used mysqldump to create the database snapshot on your master, you could edit the dump file text to change the engine type used on each table.
• Another alternative for mysqldump is to disable engine types that you do not want to use on the slave before using the dump to build the data on the slave. For example, you can add the --skip-innodb option on your slave to disable the InnoDB engine. If a specific engine does not exist for a table to be created, MySQL will use the default engine type, usually MyISAM. (This requires that the NO_ENGINE_SUBSTITUTION SQL mode is not enabled.) If you want to disable additional engines in this way, you may want to consider building a special binary to be used on the slave that only supports the engines you want.
• If you are using raw data files (a binary backup) to set up the slave, you will be unable to change the initial table format. Instead, use ALTER TABLE to change the table types after the slave has been started.
• If you are already running a replication solution and want to convert your existing tables to another engine type, follow these steps:

mysql> STOP SLAVE; mysql> ALTER TABLE <table_name> ENGINE='engine_type' mysql> START SLAVE;

 

Improving Replication Performance:

As the number of slaves connecting to a master increases, the load, although minimal, also increases, as each slave uses a client connection to the master. Also, as each slave must receive a full copy of the master binary log, the network load on the master may also increase and create a bottleneck. If you are using a large number of slaves connected to one master, and that master is also busy processing requests, then you may want to improve the performance of the replication process. One way to improve the performance of the replication process is to create a deeper replication structure that enables the master to replicate to only one slave, and for the remaining slaves to connect to this primary slave for their individual replication requirements.     For this to work, you must configure the MySQL instances as follows:

• Master 1 is the primary master where all changes and updates are written to the database. Binary logging should be enabled on this machine.
• Master 2 is the slave to the Master 1 that provides the replication functionality to the remainder of the slaves in the replication structure. Master 2 is the only machine permitted to connect to Master 1. Master 2 also has binary logging enabled, and the --log-slave-updates option so that replication instructions from Master 1 are also written to Master 2's binary log so that they can then be replicated to the true slaves.
• Slave 1, Slave 2, and Slave 3 act as slaves to Master 2, and replicate the information from Master 2, which actually consists of the upgrades logged on Master 1.

The above solution reduces the client load and the network interface load on the primary master, which should improve the overall performance of the primary master when used as a direct database solution. If your slaves are having trouble keeping up with the replication process on the master, there are a number of options available:

• If possible, put the relay logs and the data files on different physical drives. To do this, use the --relay-log option to specify the location of the relay log.
• If the slaves are significantly slower than the master, you may want to divide up the responsibility for replicating different databases to different slaves.
• If your master makes use of transactions and you are not concerned about transaction support on your slaves, use MyISAM or another nontransactional engine on the slaves.

 

Switching Masters During Failover:

There is currently no official solution for providing failover between master and slaves in the event of a failure. With the currently available features, you would have to set up a master and a slave (or several slaves), and to write a script that monitors the master to check whether it is up. Then instruct your applications and the slaves to change master in case of failure. Remember that you can tell a slave to change its master at any time, using the CHANGE MASTER TO statement. The slave will not check whether the databases on the master are compatible with the slave, it will just start reading and executing events from the specified binary log coordinates on the new master. In a failover situation, all the servers in the group are typically executing the same events from the same binary log file, so changing the source of the events should not affect the database structure or integrity Run your slaves with the --log-bin option and without --log-slave-updates. In this way, the slave is ready to become a master as soon as you issue STOP SLAVE; RESET MASTER, and CHANGE MASTER TO statement on the other slaves.   Troubleshooting Replication: If you encounter any issues with replication setup the first thing to do is check the error log for messages. If you cannot tell from the error log what the problem was, try the following techniques:

• Verify that the master has binary logging enabled by issuing a SHOW MASTER STATUS statement. If logging is enabled, Position is nonzero. If binary logging is not enabled, verify that you are running the master with the --log-bin option.
• Verify that the master and slave both were started with the --server-id option and that the ID value is unique on each server.
• Verify that the slave is running. Use SHOW SLAVE STATUS to check whether the Slave_IO_Running and Slave_SQL_Running values are both Yes. If not, verify the options that were used when starting the slave server. For example, --skip-slave-start prevents the slave threads from starting until you issue a START SLAVE statement.
• If the slave is running, check whether it established a connection to the master. Use SHOW PROCESSLIST, find the I/O and SQL threads and check their State column to see what they display. If the I/O thread state says Connecting to master, Verify the privileges for the user being used for replication on the master.
• If a statement that succeeded on the master refuses to run on the slave, try the following procedure if it is not feasible to do a full database resynchronization by deleting the slave's databases and copying a new snapshot from the master:

1. Determine whether the affected table on the slave is different from the master table. Try to understand how this happened. Then make the slave's table identical to the master's and run START SLAVE.
2. If the preceding step does not work or does not apply, try to understand whether it would be safe to make the update manually (if needed) and then ignore the next statement from the master.
3. If you decide that the slave can skip the next statement from the master, issue the following statements:

mysql> SET GLOBAL sql_slave_skip_counter = N; mysql> START SLAVE;   Replication Issues:

1. Do not use RESET MASTER command om master server while replication is running.

As the replication is based on binary logs,  RESET MASTER command would flush all binary logs and start from the first binary log. For example if the current binary log is "binlog.000024", it will flush it and start from the first binary log as "binlog.000001". And the Slave_SQL_Thread would searching for "binlog.000024" file and the replication would fail.

2. Another major issue with the replication is network. If the network fails between the two servers the slave tries to connect to the master based on the below variables and it gives up.

  master-retry-count This variables defines the number of times the slave tries to connect to the master before giving   up. The reconnect interval is set by master-connect-retry variable.   Master-connect-retry This variables defines the number of seconds the slave thread sleep before trying to connect to        the    master. The default value of this variable is 60 seconds.

3. If the disk space where the binary logs are stored is full we may get the below error:

[ERROR] /usr/sbin/mysqld: Disk is full writing '/var/bin/mysql/mysql-bin.000034' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs . In the above,  error code 28 defines disk full error. In this case you can use PURGE BINARY LOGS command. This command flushes all the binary logs but keeps the present binary log file with the same name.  So that the replication may not fail.

4. Usually we will be getting 1045 error (access denied for user 'root'@'ipaddress') while we setting up replication or changing master user and host using CHANGE MASTER command.

We will get this error if the user doesn't have REPLICATION SLAVE privilege on the specified host.  Assigning the required privilege may solve the issue.

5. Also we may get errors like 1062(duplicate entry), 1146 (table doesn't exists).

If there is a unique key on a table and a row exists in slave sever and doesn't exists in master server then we may get 1062 error. If any DML command if the table doesn't exists on the slave server we may get 1146 error. Creating the table on the slave server solves the issue. To over come the errors of these types and if you know the error numbers we can give the error numbers in the variable as slave-skip-errors = 1062, 1146 We can also overcome all the error by giving the value 'ALL' for the above variable. But not recommended.   Check Out Our Related Courses Sql Tutorials Oracle Pl SQL Tutorials Top 50 Sql Server Interview Questions SQL Server T-SQL Training In New York