In a MySQL 5.7 master-slave setup that uses the default semisynchronous replication setting for rpl_semi_sync_master_wait_point, a crash of the master and failover to the slave is considered to be lossless. However, when the crashed master comes back, you may find that it has transactions that are not present in the current master (which was previously a slave). This behavior may be puzzling, given that semisynchronous replication is supposed to be lossless, but this is actually an expected behavior in MySQL. Why exactly this happens is explained in full detail in the …

Recently, I have worked on one of this case where customer wanted to convert master-slave to master-master replication. They wanted to write on one master and keep another master as a hotspare. Many people are using this kind of environment with MySQL replication.

I would like to explain here in very simple steps that how we can do that. You can also follow the same steps if you want to promote only slave as a master in master-slave replication. Sometimes, this process will be helpful if you have master-slave replication and you want to rebuild or upgrade both the servers one by one. In that case, you have to shift traffic from one server to the other, take down the first server, rebuild it and then reverse the process.

I would also like to mention here that  master-master (active-active) replication is quite risky and it can lead to data inconsistency between the servers if writes are enable on both masters at the same time. …

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 should the master fail, but this is very painful to do in practice. The cluster has to stop taking ANY writes while it waits for …

(Note: when I’m talking about MySQL I usually assume InnoDB storage engine. Any other case I explicitly tell this is MyISAM or Memory etc.)

I’ve heared an interesting aproach of using Master-slave replication in MySQL.

Thesis

So the theory was that since updates by primary keys are fast and by secondary keys are slow the slave has to be queried for the primary key and then run the updates by the fetched primary keys. To make this in context and more understandable:

Original query

UPDATE table_for_test SET value_to_change = 123 WHERE cond_column_1 = 987 AND cond_column_2 > 765;

This query get splitted to two different query. First query has to be run on the slave to fetch the primary keys:

SELECT pr_id_col FROM table_for_test WHERE cond_column_1 = 987 AND cond_column_2 > 765;

When we have the values we can go to the master and update the necessary records. …

Why is it so important to close connections to databases if there’s no explicit need to keep them open (which usually the case)?

Symptoms

• Icinga reports high usage of allowed connections (>90%) on master MySQL server.
• No running queries or any specific in processlist. All the connections are sleeping from the same servers.
• The number of connections was 20 times more than the usual

Root cause

There was tremendous amount of queries doing “copying to tmp table” on the slaves. The connections were opened to both master and slaves and waiting for the slaves to answer the query the connections to master was kept opened. So the sleeping connections was piled up to the limit of max connections.

Lesson

1. When troubleshooting always check slaves too.
2. Close unused connections especially for masters.
3. Keep your query optimized and …

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’;
my $pass = ‘password’;
my $dbh_slave = DBI->connect($cxn_string_dc, $user, $pass); …

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.