Slaves can be used for:

1. Horizontal read scalability — take the load off a master database by spreading reads to a replicated slave.
2. Disaster recovery — some disasters, such as a hardware failure, can be solved by having a slave ready to propagate to a master. This technique also works to make offline changes to a master/slave pair without having database downtime (see below).
3. Consistent Backups — without disrupting production usage, a slave can be used to take a consistent backup by stopping the database and copying the database files. This is a free and uncomplicated way to get consistent backups (innodb hot backup is not free, and using a snapshotting tool (such as LVM’s snapshot capability) can be complex. Not everyone wants to manage snapshots.)
   

Be careful when using a slave for more than one purpose. Using a slave for more than one purpose can be done, but carefully. For …

Problem

A problem we occasionally see is Relay Log corruption, which is most frequently caused by network errors. At this point in time, the replication IO thread does not perform checksumming on incoming data (currently scheduled for MySQL 6.x). In the mean time, we have a relatively easy workaround: encrypt the replication connection. Because of the nature of encrypted connections, they have to checksum each packet.

Solution 1: Replication over SSH Tunnel

This is the easiest to setup. You simply need to do the following on the Slave:

shell> ssh -f user@master.server -L 4306:master.server:3306 -N

This sets up the tunnel. slave.server:4306 is now a tunnelled link to master.server:3306. So now, you just need to alter the Slave to go through the tunnel:

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO …

There’s a change of behaviour in MySQL 5.1.31 for Row Based Replication, if you have InnoDB transactions that also write to a MyISAM (or other non-transactional engine) table. It’s a side effect of fixing Bug #40116. Take this simple example:

Transaction 1: INSERT INTO myisam_tbl (item, val) VALUES (1, 0);
Transaction 1: INSERT INTO innodb_tbl (item, val) VALUES (1, -1), (2, -1);
Transaction 1: START TRANSACTION;
Transaction 1: UPDATE myisam_tbl SET val=val+1 WHERE item=1;
Transaction 1: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=1;
Transaction 2: START TRANSACTION;
Transaction 2: UPDATE myisam_tbl SET val=val+1 WHERE item=1;
Transaction 2: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=2;
Transaction 2: COMMIT;
Transaction 1: COMMIT;

After this, the Master innodb_tbl would look like this:

 Our latest article over at Database Journal is hot off the presses.  Advanced MySQL Replication – Improving Performance discusses some of the best ways to improve the performance of your MySQL slave setup.  If the slave is constantly getting further and further behind the master database, we discuss a number of techniques which may help you bring it under control.

Last night I gave a presentation on Tungsten Replicator to the SFO MySQL MeetUp. It was really fun--lots of excellent questions and ideas from an audience that knows MySQL in and out. Here are slides from the talk from our trusty S3 document repository.

There were many things to think about on the BART ride home to Berkeley but here are a couple that really struck me, in part because I think we can do something about them.

First, database replication needs to expose as much monitoring data as possible. The kind of data you get or can infer from SHOW SLAVE STATUS is just the beginning. This came through really strongly from people like Erin who are using load-balancers and other automated techniques for distributing load to replicas using decision criteria like availability and latency of slaves. We have …

One of my favorite MySQL configurations for high availability is master-master replication, which is just like normal master-slave replication except that you can fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this is probably the best general-purpose way to get fast failover and a bunch of other benefits (non-blocking ALTER TABLE, for example).

The benefit is that you have another server with all the same data, up and running, ready to serve queries. In theory, it's a truly hot standby (stay with me -- that's not really guaranteed). You don't get this with shared storage or DRBD, although those provide stronger guarantees against data loss if mysqld crashes. And you can use the standby (passive) master for serving some SELECT queries, taking backups, etc as …

Point-in-time recovery or PITR is one of my favorite PostgreSQL features. Most database servers have a transaction log for recovery. (MySQL is one of the few exceptions; more on that shortly.) However, PostgreSQL PITR allows and even encourages users to manipulate the logs to perform some very useful tasks:

* Creating a bit-for-bit backup of a server without halting
* Restoring a server to a specific point in time
* Creating a warm standby server

The third task is especially interesting because it's so common. One of the most pronounced trends in computing is the decreasing cost of computing power through Moore's law improvements and virtualization. Standby solutions nowadays look like a good investment compared to the cost having a server down or worse …

I’ll keep this short. Let’s say you run “mysql> show slave status\G” and you see the following error:

Last_Error: Error 'Unknown or incorrect time zone: 'Etc/UTC'' on query. Default database: 'db_name'. Query: 'INSERT INTO table SET column = CONVERT_TZ('2009-01-24', '-8:00', '+00:00')'

It’s a simple fix. Exit out of MySQL and run the following command - this works on Redhat, I’ve not tried it on other flavors.

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo|mysql -u root -p

Log back into MySQL and run the following.

mysql> stop slave;
mysql> start slave;
mysql> show slave status\G;

The error should be gone and the slave should be catching up to the master now. Problem solved.

This is a simple mysql circular replication implementation on a single machine (just a proof of concept) which can easily be done on a broader scale. Just be aware of the cons of circular replication which mainly gets down to: once a node freaks out or stops for one reason or the other, it’s a bitch and you need to take care of IMMEDIATELY.

Download Sandbox from https://launchpad.net/mysql-sandbox/+download
Download MySQL from http://dev.mysql.com/downloads

Copy the downloaded software onto the your *nix box onto any folder of your preference called $BASEDIR

run:

cd /$BASEDIR

gunzip mysql_sandbox_X.X.XX.tar.gz
tar -xf mysql_sandbox_X.X.XX.tar

ln -s mysql_sandbox_X.X.XX sandbox

time /$BASEDIR/sandbox/make_replication_sandbox –circular=4 –topology=circular /$BASEDIR/mysql-5.1.30-linux-x86_64-glibc23.tar.gz


user@hostname $ time …

One very helpful use of the technique Sheeri described in Remote connections without leaving the mysql shell is making sure that replication is working properly.

According to the MySQL Reference Manual’s section on SHOW SLAVE STATUS Syntax, it shows information corresponding to the slave thread in the slave server. When replication is broken, however, or not working properly due to network issues between master and slave, this information may not be accurate. This has improved over recent releases, but it’s still not perfect.

The question, then, is: how to be 100% sure (or as close as you can get to 100%) that replication is running fine? The answer, as offered by Sheeri: use CONNECT.

Example …