MySQL Replication is a feature of the MySQL server that allows you to replicate data from one MySQL database server (called the master) to one or more MySQL database servers (slaves). MySQL Replication has been supported in MySQL for a very long time and is an extremely flexible and powerful technology. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

In this article, by Alex Davies, author of High Availability MySQL Cookbook, we will cover:

• Designing a replication setup
• Configuring a replication master
• Configuring a replication slave without synchronizing data
• Configuring a replication slave and migrating data with a simple SQL dump
• Using LVM to reduce downtime on master when bringing a slave online
• Replication safety tricks

Installing and Managing Multi Master Replication Manager(MMM)for MySQL High Availability is covered seperately.

Replication is asynchronous, that is, the process of replication is not immediate and there is no guarantee that slaves have the same contents as the master (this is in contrast to MySQL Cluster).

Designing a replication setup

There are many ways to architect a MySQL Replication setup, with the number of options increasing enormously with the number of machines. In this recipe, we will look at the most common topologies and discuss the advantages and disadvantages of each, in order to show you how to select the appropriate design for each individual setup.

Getting ready

MySQL replication is simple. A server involved in a replication setup has one of following two roles:

• Master: Master MySQL servers write all transactions that change data to a binary log
• Slave: Slave MySQL servers connect to a master (on start) and download the transactions from the master’s binary log, thereby applying them to the local server

Slaves can themselves act as masters; the transactions that they apply from their master can be added in turn to their log as if they were made directly against the slave.

Binary logs are binary files that contain details of every transaction that the MySQL server has executed. Running the server with the binary log enabled makes performance about 1 percent slower.

The MySQL master creates binary logs in the forms name.000001, name.000002, and so on. Once a binary log reaches a defined size, it starts a new one. After a certain period of time, MySQL removes old logs.

The exact steps for setting up both slaves and masters are covered in later recipes, but for the rest of this recipe it is important to understand that slaves contact masters to retrieve newer bits of the binary log, and to apply these changes to their local database.

How to do it…

There are several common architectures that MySQL replication can be used with. We will briefly mention and discuss benefits and problems with the most common designs, although we will explore in detail only designs that achieve high availability.

Master and slave

A single master with one or more slaves is the simplest possible setup. A master with one slave connected from the local network, and one slave connected via a VPN over the Internet, is shown in the following diagram:

A setup such as this—with vastly different network connections from the different slaves to the master—will result in the two slaves having slightly different data. It is likely that the locally attached slave may be more up to date, because the latency involved in data transfers over the Internet (and any possible restriction on bandwidth) may slow down the replication process.

This Master-Slave setup has the following common uses and advantages:

• A local slave for backups, ensuring that there is no massive increase in load during a backup period.
• A remote location—due to the asynchronous nature of MySQL replication, there is no great problem if the link between the master and the slave goes down (the slave will catch up when reconnected), and there is no significant performance hit at the master because of the slave.
• It is possible to run slightly different structures (such as different indexes) and focus a small number of extremely expensive queries at a dedicated slave in order to avoid slowing down the master.
• This is an extremely simple setup to configure and manage.

A Master-Slave setup unfortunately has the following disadvantages:

• No automatic redundancy. It is common in setups such as this to use lower specification hardware for the slaves, which means that it may be impossible to “promote” a slave to a master in the case of an master failure.
• Write queries cannot be committed on the slave node. This means write transactions will have to be sent over the VPN to the master (with associated latency, bandwidth, and availability problems).
• Replication is equivalent to a RAID 1 setup, which is not an enormously efficient use of disk space (In the previous example diagram, each piece of data is written three times).
• Each slave does put a slight load on the master as it downloads its binary log. The number of slaves thus can’t increase infinitely.

Multi-master (active / active)

Multi-master replication involves two MySQL servers, both configured as replication masters and slaves. This means that a transaction executed on one is picked up by the other, and vice versa, as shown in the following diagram:

A SQL client connecting to the master on the left will execute a query, which will end up in that master’s binary log. The master on the right will pick this query up and execute it. The same process, in reverse, occurs when a query is executed on the master on the right. While this looks like a fantastic solution, there are problems with this design:

• It is very easy for the data on the servers to become inconsistent due to the non-deterministic nature of some queries and “race conditions” where conflicting queries are executed at the same time on each node
  Recent versions of MySQL include various tricks to minimize the likelihood of these problems, but they are still almost inevitable in most real-world setups.
• It is extremely difficult to discover if this inconsistency exists, until it gets so bad that the replication breaks (because a replicated query can’t be executed on the other node).

This design is only mentioned here for completeness; it is often strongly recommended not to use it. Either use the next design, or if more than one “active” node is required, use one of the other high-availability techniques that are available but not covered in this article.

Active / passive master

Active / passive master replication involves two MySQL servers configured as per active / active master replication, but with some form of “write barrier” around a “passive” node in order to ensure that only one node is able to execute queries that change the data at any one point of time. This design is demonstrated in the following diagram:

In the preceding diagram, the master on the left is the active master and the master on the right is passive. By running the example we discussed in active / active replication, if a SQL query is executed on the master on the left, it is injected into the binary log. This query is then picked up and executed by the master on the right. If a SQL query is executed on the node on the right, the “write barrier” prevents the query from being executed.

This barrier could consist of one of the following with various degrees of enforcement:

• A virtual IP address shared between the nodes (with one node having its ownership at any point of time)
• Permissions set on the “passive” node to prevent all connections except those for the replication user
• An application configured only to connect to the active node and no untrustworthy user
• In many ways, the simplest and easiest, by configuring all slave nodes as “read-only” on startup with the following my.cnf entry on the slave:

  [mysqld]
  read_only

When set as read-only, the node will reject all UPDATE and DELETE queries, and thus can’t allow a standard database user (such as an application or mistaken user) to change data although changes that are received by the master are executed as normal.

To promote a slave node to a master node, either remove the read_only parameter from my.cnf or to set the parameter for the currently running MySQL Server only, execute the following query:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

The server will now respond to all queries normally. This trick is covered in more detail later in this article in the Replication safety tricks recipe.

The effect is that there is one node that is asynchronously up to date with the other node (this node may or may not be used for read-only queries). In the event of the failure of the master, the passive node can be quickly “promoted” to the active master using the following procedure:

1. Install a write barrier around the previously active node to prevent all new MODIFY queries.
2. Wait for the passive node to catch up with any queries left in the master’s binary log.
3. Remove the write barrier around the passive (which now becomes the active) node.

Clearly, if the active node has failed, step 2 may not be possible (and, depending on the type of write barrier, step 1 may be difficult). In this case, the passive node is promoted to master node and some transactions are lost. If this is not acceptable, you need a synchronous high-availability technique such as shared storage and MySQL Cluster.

How it works…

In this section, we will cover how MySQL replication works in slightly more detail. MySQL supports two forms of replication: statement-based and row-based replication. There is also a hybrid mode (“mixed”) that is used by default.

Mixed-mode replication

Mixed-mode replication (the default) will use statement-based replication for almost everything, but it will switch to row-based replication for certain events. Full documentation on every event that will cause this can be found at http://dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html, but the most common events that cause row-based replication to be used are as follows:

• When a query updates a MySQL Cluster table
• When a query includes the UUID() function
• When two or more tables with AUTO_INCREMENT columns are modified
• When any INSERT DELAYED query is executed

There is no need to configure anything to get mixed-mode replication to work, because it is enabled as soon as you turn on binary logging (by adding the log-bin parameter to the [mysqld] section in my.cnf, which we shall cover in the later recipes in this article).

Statement-based replication

Statement-based replication quite literally keeps a record of every statement executed on the master that the slaves then executed. With this form of replication, which has been around from MySQL 3.1 version, binary logs can be converted to text for inspection with the mysqlbinlog command.

The following command runs the output of mysqlbinlog (which is verbose, and includes many comments and details on the environment) using grep command to look for a SQL query executed on the node:

[root@node1 mysql]# mysqlbinlog /var/lib/mysql/node1.000001 
| grep -in "create database"
23:create database world

Unfortunately, statement-based replication has problems with queries that can cause different results when executed at the same time on the same dataset, such as DELETE or UPDATE with a LIMIT and noORDER BY clause. Go to http://dev.mysql.com/doc/refman/5.4/en/replication-sbr-rbr.html for a complete list of limitations.

To force MySQL to use statement-based replication, modify the binlog-format my.cnf parameter and restart MySQL:

[mysqld]
binlog-format=mixed

Row-based replication

As a consequence of the limitations inherent to a statement-based replication system, row-based replication was added in MySQL 5.1.5.

If you enable MySQL Cluster on a SQL node, row-based replication will be used by default. MySQL Cluster is not compatible with statement-based replication.

Row-based replication literally replicates the actual changes (“events”) to data in a database. Rather than a slave executing an entire single transaction, it simply executes the required queries to achieve the same results (this can, in some cases, increase performance). For example, if a DELETE query includes a delete subquery, the log would only contain details of the events—that is, the rows actually deleted.

Even with row-based replication, some queries (such as OPTIMIZE TABLE, ALTER TABLE, and ANALYZE TABLE) must be stored as queries and executed on the slaves as queries.

While it is not possible to inspect the actual queries within the binary log, it is possible to run mysqlbinlogwith the –base64-output=DECODE-ROWS and –verbose parameters in order to see which rows are being updated.

There are some unresolved issues with row-based replication and concurrent large bulk INSERT andSELECT queries. However, with other uses of a database, it is likely that row-based replication is more deterministic and less likely to cause problems.

Row-based replication is required for replication involving MySQL Clusters, as covered in the Master andslave section. To configure row-based replication explicitly, modify the binlog-format parameter in my.cnfand restart MySQL:

[mysqld]
binlog-format=row

To check which replication mode a MySQL server is running in, SELECT the value of the binlog_formatsystem variable:

mysql> SHOW VARIABLES LIKE ‘binlog_format’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| binlog_format | MIXED |
+—————+——-+
1 row in set (0.00 sec)

Configuring a replication master

In this recipe, we will configure a master. Once configured, a replication master can have as many slaves as required connecting to it and retrieving its binary log.

Getting ready

The process of setting up a master is as follows:

• Configure a replication user account, with restricted permissions, for slaves to use when they log in
• Configure the master to start recording information into a binary log (using row-based, statement-based or hybrid-based replication modes)

How to do it…

We will firstly cover the parameters that must be set in the [mysqld] section in /etc/my.cnf on the master node.

1. Configuring a node IDEvery server involved in a replication agreement with any other server must have a unique ID, set inmy.cnf with the server-id parameter:

   server-id = 1

2. Configuring a binary logThe master must be told to store a binary log. The parameter log-bin will do this, but it is a good idea to pass a name for this logfile.If you fail to do so, it can cause confusion—particularly if machine hostnames change.For a machine with a hostname, the following will place logfiles in the form <MySQL datadir> node1-bin.xxxxxx. For example, /var/lib/mysql/node1-bin.000001:

   log-bin=node1

   It is possible to pass a full path to this logfile. This is extremely desirable if you have a dedicated block device to store the binary logs, as on IO intensive systems this may reduce the overhead of binary logging:

   log-bin=/mnt/disk2/node1

   In addition to the log files, a file prefix.index (for example node1.index) is created in the same directory that tells you which is the most recent binary log file.

   You must restart the mysql server after changing this setting:

   [root@node1 tmp]# service mysql restart
   Shutting down MySQL. [ OK ]
   Starting MySQL. [ OK ]

3. Configuring a replication user accountEach slave that connects to a master in a replication setup (for the purposes of reading the binary log on the master) must have an account to log in. This account must be granted the dedicated permissionREPLICATION SLAVE. It is common practice to also grant this account REPLICATION CLIENT, which allows monitoring of the replication setup.If possible, grant this permission only to a specific host—in this example, node 1 (10.0.0.1):mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
   ‘replication’@’10.0.0.1’ IDENTIFIED BY ‘password’;Query OK, 0 rows affected (0.00 sec)

To update the permissions table, flush the privileges:

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.01 sec)

How it works…

The process of replication is a little more complicated than we earlier alluded to. In this section, we will explain the details behind MySQL replication in slightly more detail. When a transaction is sent to a MySQL server running with binary logging enabled, the transaction is executed normally, and just prior to completing the transaction the server records the change serially in the binary log. Once this is done, it tells the storage engine that the transaction is ready to be committed.

Even if statements are concurrent during execution, they are recorded serially in the binary log.

The slave runs two threads to handle its role as a slave.

Firstly, the slave runs a I/O thread that opens a standard client connection to the master (using the replication user account) and starts a non-SQL BINARYLOG DUMP command, which causes the master to start a “Binlog dump thread” and allows the slave to read log entries from the master’s binary log. The slave compares each entry with the latest entry that it already has on the disk. If it fails to find new entries, it sleeps and waits for the master to send a new entry signal. If there are new entries, it records these in the “relay log” on the slave.

A second thread, the “SQL slave thread”, reads from the relay log and replays the queries, which completes the process that started with a query being sent to the master and ends with the query being committed to the slave server database.

When the slave thread executes a query, it does not, by default, record the query in its own binary log—this can be changed with the my.cnf parameter log-slave-updates.

There’s more…

The following is a checklist of things to consider when setting up a master:

Disk space

Binary logs take up disk space, and if you have them stored in the same partition as the MySQL data because you are running out of space, it will prevent changes to your database. There are a couple of ways to approach this problem, outlined here.

Only logging some databases

You may well have some databases that you do not wish to replicate. Unless you are using binary logs for some other purpose (such as, for backups), you can stop the MySQL server from logging these queries with the my.cnf parameter binlog-ignore-db.

When using row-based replication, this works as expected (all queries made against this database are ignored and not logged). When using statement-based replication, things are a little more complicated—the effect of this parameter is to not log any statement where the default database (that is, the one selected by USE) is db_name:

binlog-ignore-db = mysql

To specify more than one database you wish to ignore, use this option multiple times. Do not use commas.

If you only want to replicate a specific database, you can use the inverse parameter binlog-do-db.

Limiting individual binary log size

Binary logs can become unmanageable, and the default maximum size for a binary log is 1G. This is tunable with the my.cnf parameter max_binlog_size:

max_binlog_size=200M

Rotating binary logs

It is good practice to automatically delete old binary logs. You can rotate binary logs older than two days with the my.cnf parameter expire_log_days.

The default value for this parameter is 0, which means “no automatic removal”.

expire_logs_days = 2

Removals occur when the MySQL server is started and when the logs are flushed (note that the logs are flushed when the current logfile reaches max_binlog_size, providing a further reason to set this parameter to a relatively small value).

Performance

In addition to the recommendation of storing your binary log on a block device separate from your MySQL data directory, there are some additional tricks you can use to increase performance while logging:

Binary log caching

The my.cnf parameter named binlog_cache_size sets the size of the cache that is used to hold SQL statements before they are inserted into the binary log during a transaction. Big, multi-statement transactions can benefit from an increased value (the default is 32M):

binlog_cache_size = 64M

This buffer is allocated per connection on the first UPDATE or INSERT query.

Configuring a replication slave without syncing data

In this recipe, we will see how to configure a replication slave and initiate the replication process. This recipe assumes that a master server is already configured, with a replication user account configured for the slave.

How to do it…

In this recipe, we show how to configure a slave server without showing how to sync data, which is shown in the next recipe. This recipe would be perfect if you have two freshly installed MySQL servers for example, or if you have a slave, which is a clone of a master that is not being updated, using virtualization.

The first step is to verify that the two servers have different server ID parameters in my.cnf by executing the following command in the mysql client on both servers:

mysql> SHOW VARIABLES LIKE “server_id”;

+—————+——-+
| Variable_name | Value |
+—————+——-+
| server_id | 5 |
+—————+——-+

1 row in set (0.00 sec)

If two servers in a replication agreement have the same server ID, replication will fail.

If the two servers do have the same server ID, modify the server-id parameter in my.cnf in order to ensure that both nodes have a server ID explicitly set, and that the two IDs are different.

The second step is to verify the master status on the master. In the MySQL Client, execute the following SQL query:

mysql> SHOW MASTER STATUS;

+————–+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+————–+———-+————–+——————+
| node1.000003 | 107 | | |
+————–+———-+————–+——————+

1 row in set (0.00 sec)

Take note of the filename (node1.000003) and position (107). On the master, we have already configured a replication user account (replication) and the corresponding password.

The next step is to tell the slave where the master is, what user account to use in order to log in, what logfile to start reading from, and what position to jump to. This is all encased in a CHANGE MASTER TOquery. Jump onto the slave, enter the MySQL Client, and execute the following query:

mysql> CHANGE MASTER TO master_host = ‘10.0.0.1’, master_
user=’replication’, master_password=’password’, master_log_
file=’node1.000003′, master_log_pos=107;

Query OK, 0 rows affected (0.01 sec)

Now start the slave threads on the new slave:

mysql> START SLAVE;

Query OK, 0 rows affected (0.00 sec)

And check that the slave has come up using:

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.1
Master_User: replication
…
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Congratulations! Your replication agreement is now working.

At this point, do something on the master (such as creating a table, inserting a row, and so on) and ensure that it appears in the slave. If it does not, review the output of SHOW SLAVE STATUS and ensure that the slave thread is running, and that there is no error displayed.

Configuring a replication slave, migrating data with a simple SQL dump

In this recipe, we will show how to configure a replication slave while coping with a master that both has data on it and potentially has that data changing, while minimizing the time for which the master must be “locked” from updates

In the common case of adding a slave to a master that already has data in it, the simplest technique is to use the mysqldump binary provided by MySQL to inject the data from master to slave, and to reset the slave at the same time.

For this one-line command on the master to work, the following requirements must be met:

• Any existing slave process must be stopped on the slave (STOP SLAVE)
• A user account must exist that is able to create databases and tables, and insert rows when connecting to the slave from the master
• It must be acceptable to lock the tables on the master for the duration of the operation

This technique copies all data from the master to the slave, including all of the mysql database—with the exclusion of the users table. It is likely that all of the other tables are identical if the software versions are the same.
Ensure you have replicate-ignore-db = mysql in /etc/my.cnf on the slave, if you don’t wish to replicate the mysql database, or remove the –ignore-table parameter using the next command (recommended).

How to do it…

The command to execute on the master, in full, is as follows:

[root@node1 mysql]# mysqldump –delete-master-logs –ignore-table=mysql.
user –master-data –lock-all-tables –all-databases -u root
–password=” –hex-blob | mysql -h 10.0.0.2 -u root –password=”

We can explain this command in chunks as follows:

• mysqldump: Binary for producing SQL statements from a MySQL database. This means, “take my database and produce a file that contains SQL statements, which would build an identical database if executed”.
• –delete-master-logs: Deletes all logs on the master from the moment you start the backup (these are not needed; the slave only requires logs after this point).
• –master-data: Includes a CHANGE MASTER TO command within the dump.
• –lock-all-tables: Locks all tables on the master during the period of the backup, in order to ensure that every transaction before the time of the backup is logged, and every transaction after the backup is in the binary log. If you have a lot of data, this may involve a very long lock on all tables. Look at the next recipe for a solution.
• –all-databases: Backs up all databases on the master.
• –hex-blob: If you have BLOBs (binary objects) in your database, this option ensures they are stored in a way that allows them to be imported correctly (that is, stored as hexadecimal characters).
• The pipe command (|): Takes output from the command on the left and passes it to the command on the right.
• mysql –h 10.0.0.2: Connects to the slave.
• -u root –-password=’x’: Uses these details.

Once this command has finished execution, run START SLAVE as shown in the preceding section to start your slave up.

How it works…

The CHANGE MASTER TO command is only one of several ways to point a slave at a master (it is the most recommended way). Internally, this command creates a file master.info in the MySQL data directory (/var/lib/mysql by default), which keeps the details. This file is updated every time this CHANGE MASTER TO query is executed, and it is this file that the server uses when it begins to see the latest logfile that the slave was reading from, and at what position, when the server shuts down.

Using LVM to reduce downtime on a master when bringing a slave online

It is possible to use the Logical Volume Manager (LVM) which comes with most Linux distributions, including Redhat / CentOS, to take a read-only snapshot of the block device that the MySQL Data directory is residing on, and use this to synchronize a slave with only a very short period of table locks on the master.

In many cases of 24×7 use of a database, this is essential and it can be useful when you do not want to wait for a scheduled outage interval every time a slave needs re-synchronizing.

Getting ready

For the purpose of this recipe, we will require that you already have the MySQL data directory residing on a volume group with enough free space to hold the data that will change during the time the backup is running (perhaps, 10-20% for a very busy server).

In order to check if you have space in a volume group, use the vgs command:

[root@node2 mysql]# vgs
VG #PV #LV #SN Attr VSize VFree
system 1 2 0 wz–n- 14.62G 4.88G

In this case, the volume group system has free space of 4.88G. If you do not have any or enough space, refer to Operating System documentation to find out how to modify volume groups, or the manual pages for pvcreate and vgextend.

Once the MySQL data directory is on a filesystem residing on a logical volume and there is sufficient space in the volume group, the following process must be carried out:

• Lock the tables in the master
• Take a snapshot (very quick)
• Record the binary log name and position on the master
• Unlock the tables
• Synchronize the data directory on the master with the slave
• Update the slave to point at the correct log name and position
• Start the slave
• Remove the snapshot on the master

We will now do this in an example with a master on node1 (10.0.0.1) and slave on node2 (10.0.0.2). The master has a logical volume mysql in volume group system. We assume that SSH is possible between the nodes, and in this example we set it to use a key.

How to do it…

Firstly, open two sessions to the master (when a LOCK TABLES query is run in a MySQL Client, it is ended if the client connection is closed). Secondly, prepare the lvcreate command in a second window to minimize downtime. Then, in quick succession, run the following four commands:

Lock all tables in window 1:

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

In a busy server, this may take some time. Wait for the command to complete before moving on.

Create a snapshot volume in window 2, passing a new name (mysql_snap), and pass the size that will be devoted to keeping the data that changes during the course of the backup, and the path to the logical volume that the MySQL data directory resides on:

[root@node1 lib]# lvcreate –name=mysql_snap –snapshot –size=200M \
/dev/system/mysql
Rounding up size to full physical extent 224.00 MB
Logical volume “mysql_snap” created

Return to window 1, and check the master log position:

mysql> SHOW MASTER STATUS;
+————–+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+————–+———-+————–+——————+
| node1.000012 | 997 | | |
+————–+———-+————–+——————+
1 row in set (0.00 sec)

Only after the lvcreate command in window 2 gets completed, unlock the tables:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

The next step is to move the data on this snapshot to the slave. On the master, mount the snapshot:

[root@node1 lib]# mkdir /mnt/mysql-snap
[root@node1 lib]# mount /dev/system/mysql_snap /mnt/mysql-snap/

On the slave, stop the running MySQL server and rsync the data over:

[root@node2 mysql]# rsync -e ssh -avz node1:/mnt/mysql-snap /var/lib/
mysql/
root@node1’s password:
receiving file list … done
mysql-snap/
mysql-snap/ib_logfile0
mysql-snap/ib_logfile1

mysql-snap/ibdata1
…
mysql-snap/world/db.opt
sent 1794 bytes received 382879 bytes 85482.89 bytes/sec
total size is 22699298 speedup is 59.01

Ensure the permissions are set correctly on the new data, and start the MySQL slave server:

[root@node2 mysql]# chown -R mysql:mysql /var/lib/mysql
[root@node2 mysql]# service mysql start
Starting MySQL. [ OK ]

Now carry out the CHANGE MASTER TO command in the Setting up slave with master having same datasection of this recipe to tell the slave where the master is, by using the position and logfile name recorded in the output from window 1 (that is, log name node1.000012 and Position 997).

Replication safety tricks

MySQL replication in anything but an extremely simple setup with one master handling every single “write”. A guarantee of no writes being made to other nodes is highly prone to a couple of failures. In this recipe, we look at the most common causes of replication failure that can be prevented with some useful tricks.

This section shows how to solve auto increment problems in multi-master setups, and also how to prevent the data on MySQL servers, which you wish should remain read-only, from being changed (a common cause of a broken replication link). Auto-increment is the single largest cause of problems.

It is not difficult to see that it is not possible to have more than one server handling asynchronous writes when auto-increments are involved (if there are two servers, both will give out the next free auto-increment value, and then they will die when the slave thread attempts to insert a second row with the same value).

Getting ready

This recipe assumes that you already have replication working, using the recipes discussed earlier in this article.

How to do it…

In a master-master replication agreement, the servers may insert a row at almost the same time and give out the same auto-increment value. This is often a primary key, thus causing the replication agreement to break, because it is impossible to insert two different rows with the same primary key. To fix this problem, there are two extremely useful my.cnf values:

1. auto_increment_increment that controls the difference between successiveAUTO_INCREMENTvalues.
2. auto_increment_offset that determines the first AUTO_INCREMENT value given out for a new auto-increment column.

By selecting a unique auto_increment_offset value and an auto_increment_increment value greater than the maximum number of nodes you ever want in order to handle a write query, you can eliminate this problem. For example, in the case of a three-node cluster, set:

• Node1 to have auto_increment_increment of 3 and
  auto_increment_offset of 1
• Node2 to have auto_increment_increment of 3 and
  auto_increment_offset of 2
• Node3 to have auto_increment_increment of 3 and
  auto_increment_offset of 3

Node1 will use value 1 initially, and then values 4, 7, and 10. Node2 will give out value 2, then values 5, 8, and 11. Node3 will give out value 3, then 6, 9, and 12. In this way, the nodes are able to successfully insert nodes asynchronously and without conflict.

These mysqld parameters’ values can be set in the [mysqld] section of my.cnf, or within the server without restart:

[node A] mysql> set auto_increment_increment = 10;
[node A] mysql> set auto_increment_offset = 1;

There’s more…

A MySQL server can be started or set to read-only mode using a my.cnf parameter or a SET command. This can be extremely useful to ensure that a helpful user does not come along and accidentally inserts or updates a row on a slave, which can (and often does) break replication when a query that comes from the master can’t be executed successfully due to the slightly different state on the slave. This can be damaging in terms of time to correct (generally, the slave must be re-synchronized).

When in read-only mode, all queries that modify data on the server are ignored unless they meet one of the following two conditions:

1. They are executed by a user with SUPER privileges (including the default root user).
2. They are executed by the a replication slave thread.

To put the server in the read-only mode, simply add the following line to the [mysqld] section in /etc/my.cnf:

read-only

This variable can also be modified at runtime within a mysql client:

mysql> show variables like “read_only”;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| read_only | OFF |
+—————+——-+
1 row in set (0.00 sec)
mysql> SET GLOBAL read_only=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like “read_only”;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| read_only | ON |
+—————+——-+
1 row in set (0.00 sec)

Summary

Having covered setting up MySQL replication for high availability, in the next article, we will cover installing and managing Multi Master Replication Manager(MMM) for MySQL High Availability.

---

If you have read this article you may be interested to view :

• Installing and Managing Multi Master Replication Manager(MMM) for MySQL High Availability
• MySQL Cluster Management : Part 1
• MySQL Cluster Management : Part 2