In the theoretical part of this series, we have seen
the basics of monitoring. In that article, though, we have barely
mentioned the new tools available in MySQL 5.7 and MariaDB 10.
Let’s start from something that has the potential of dramatically
changing replication as we know it.
Crash-safe tables and Global transaction identifiers in MySQL 5.6
and 5.7Global transaction identifiers (GTID) is a feature that
has been in my wish list for long time, since the times I was
working with the MySQL team. By the time I left Oracle, this
feature was not even in the plans.
When MySQL 5.6 was first disclosed, the biggest improvement for
replication was the introduction of crash-safe tables (see
Status persistence in …
The latest MySQL Sandbox, version 3.0.66 is out. It has a few
new features (as always, when I find myself doing the same thing
many times, I script it) and improved support for latest releases
of MySQL. You can now install, among other versions, MySQL 5.7.8
and MariaDB 10.1.x
Some notable additions in this release are in the scripts that
are created and customized for each sandbox. There are many of them and when one more arrives, it's
easy to overlook it. So, here are the new arrivals.
./show_binlog
When I am troubleshooting replication behavior, I often need to
inspect the latest binary log. The sandbox has a shortcut that
gives me the right version of mysqlbinlog for the
deployment:
MySQL 5.7 comes with many changes. Some of them are better
explained than others.
I wanted to see how many changes I could get by comparing SHOW
VARIABLES in MySQL 5.6 and 5.7.
The most notable ones are:
- binlog_format: the default is now ROW. This variable affects the format of the binary log, whether you use it as a backup complement or for replication, the change means bigger binary logs and possibly side effects.
- …
Why Multi source replication is required?
MySQL has limitation of not allowing to replicate multiple DB’s from different master host on a single slave. MySQL replication had a limitation, fixed with this new release, that said that one slave could have only one master. That is a limiting factor when we are designing our replication environment. There were some “hacks” to make it work, but now there is an official way.
What is multisource replication?
MySQL Multi-Source Replication enables a replication slave to
receive transactions from multiple sources simultaneously.
Multi-source replication can be used to back up multiple servers
to a single server, to merge table shards, and consolidate data
from multiple servers to a single server. Multi-source
replication does not implement any conflict detection or
resolution when applying the transactions, and those tasks are
left to the …
Hi,
I think most of the DBAs experience this error in their weekly, monthly todo list. Each one follows different approach, I would like to share some tips here:
- You should first execute “Show Slave Status;” and check the output, analyze it. Whether SQL thread has stopped or I/O thread has stopped and check respective error , It should be either SQL error or I/O error, based on that take next step.
I/O thread has stopped:
- Check that you can reach the master host from slave hostby
using
ping
ortraceroute
/tracert
to reach the host. - Connect to Master host and check whether “Replication thread” is connected.
- There are multiple thread state involved, you need to take action based on thread state – …
I’ve had a look at a preview release of MySQL 5.7.8, some time
before it became available to the general public (perks and
duties of an Oracle ACE) and I found a few interesting
things among the release notes and the tarball itself:
- There is a new tool named mysqlpump, which is intended as a replacement for mysqldump, with parallel processing, compression, progress watch, the long awaited ability of excluding databases or tables, and more.
- The json functionality has been fished out from the labs and added to the main distribution.
Replication is the process that transfers data from an active
master to a slave server, which reproduces the data stream to
achieve, as best as possible, a faithful copy of the data in the
master.
To check replication health, you may start with
sampling the service, i.e. committing some
Sentinel value in the master and retrieving it
from the slave.
Sentinel data: Tap tap… Is this thing on?
If you want to make sure that replication is working, the easiest
test is using replication itself to see if data is being copied
across from the master to the slaves. The method is easy:
- Make sure that the data you want to see is NOT in the master or in the slave. If you skip this step, you may think that replication is working, while in fact it may not.
- Either create a table …
Both MySQL and MariaDB have been busy, each introducing new
features, sometimes creating the same feature, often with
different syntax.
This is sometimes good for users, who have a wide choice. And
sometimes it is bad, as once you are used to the deployment and
syntax of one flavor, it is hard to switch to a different one.
This problem is enhanced if you are dealing with an application,
MySQL
Sandbox, that needs to work well with all flavors.
The latest releases of MySQL Sandbox (3.0.51 to 3.0.55) have been necessary to solve minor and major troublesome points with MySQL 5.7.8 and MariaDB 10.1.
The current version (3.0.55) can install all the newest releases, including replication with MySQL 5.7.8 which suffers from a compatibility bug (better explored in a separate article). …
[Read more]As part of the release of MySQL-5.7.7, based on community feedback, improvements have been made with respect to replication defaults, which will make MySQL replication safer and easier to use.
This blog is intended to provide information about these default changes, and it briefly explains the advantages of having them.
1) Enable Simplified GTID recovery by default:
binlog_gtid_simple_recovery=TRUE by default.
https://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html#sysvar_binlog_gtid_simple_recovery
This variable controls how binary log files are iterated during the search for GTIDs when MySQL starts or restarts.
Setting this option to TRUE gives improved recovery performance. Because of this option server startup and binary log purge …
[Read more]
Slave election is a popular HA architecture, first MySQL
MariaDB toolkit to manage switchover and failover in a correct
way was introduce by Yoshinori
Matsunobu into MHA.
Failover and switchover in asynchronous clusters require
caution:
- The CAP theorem need to be satisfy. Getting strong consistency,
require the slave election to reject transactions ending up in
the old master when electing the candidate master.
- Slave election need to take care that all events on the old
master are applied to the candidate master before switching
roles.
- Should be instrumented to found a good candidate master and
make sure it's setup to take the master role.
- Need topology detection, a master role can't be pre defined, as
the role …