Standard MySQL is configurable such that a single master server can be clustered with a number of read-only slave servers. To enable this master-slave replication, master’s transaction logs are communicated to the slaves (log shipping). Log shipping is a form of asynchronous replication. Under this configuration, the data on the slave always remains behind the master, a condition referred to as slave lag or replication lag. The extent of the slave lag depends on workload, network bandwidth and network latency. Database reads can be served out of the slaves, assuming the application has been designed to tolerate the slave lag and requisite staleness of data (eventual consistency), which can at times be variable and opaque. MySQL master-slave replication offers the possibility of promoting a slave to become the new master

  [Read more...]

About a month ago I needed to compare tens of thousands of tables in hundreds of databases between a few different servers. The obvious choice was, mk-table-checksum! The only problem was, that the tool needs to know the minimum and maximum value of the column by which each table is to be subdivided into chunks and checksummed. This select min(col), max(col) from table locks all write operations on the table and on a big table it meant downtime.

Looking at the source it was clear we could make mk-table-checksum run the select min(col), max(col) from table on the read-only slave and use the values to checksum the master.

It was subtle code changes in function:
get_range_statistics adding

my $cxn_string_dc = “DBI:mysql:;host=slavehost;port=3306;mysql_read_default_group=client”;
my $user = ‘user’;

  [Read more...]

Replication enables data from one MySQL server to be replicated on one or more other MySQL servers. Replication is mostly used as scale-out solution. In such a solution, all writes and updates take place on the master server, while reads take place on one or more slaves. This model is actually known as master-slave replication and this is the kind of replication that I will be setting up in this post.