Update 2016-01-30: restarting the IO_THREAD might be considered useful in some situations (avoiding MDEV-9138). Look for "in contrast, if the IO thread was also stopped first" in MDEV-6589 for more information.
In a previous post, I listed some sequences of commands that you should not run on a MariaDB slave that is lagging and which is using the GTID protocol. Those are the following (do not
In the spirit of sharing war stories and avoiding others to do the same mistakes as I did, here are some sequences of commands that you should avoid to run on a MariaDB slave that is lagging and which is using the GTID protocol. Remember, do not run those because...
So, those bad commands are the following:
"STOP SLAVE; START SLAVE UNTIL ...;", or "STOP SLAVE; START SLAVE;" (to remove an
During the PerconaLive conference in Amsterdam, I attended a
session where I heard a good piece of advice about using GTID. It
amounts to: look at SHOW SLAVE STATUS output, and if you see more
than one line in the Executed_Gtid_Set field, this tells you
immediately if someone has written on a slave database.
This is good advice. Let's dissect it. Here is what a regular
slave looks like, when nobody has messed up with it:
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
[...]
Master_Server_Id: 1
Master_UUID: 00013454-1111-1111-1111-111111111111
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more …[Read more]
We remember when we first started auditing MySQL servers, there
were very few tools available. In one of our early big
gigs, we were battling serious performance issues for a client.
At the time, tuning-primer.sh was about the only tool
available that could be used to diagnose performance bottlenecks.
Fortunately, with a lot of manual interpolation of the raw
data it presented, we were able to find the issue with the server
and suggest how to resolve them. For that we are very
thankful. It was a first step in analyzing MySQL status
variables, minimizing the number of formulas to learn and
calculate by hand. Obviously doing it by hand takes
forever!
Now fast-forward to today. Unfortunately, not much has changed. Many DBAs and developers are still using open source tools such as tuning-primer, mysqltuner.pl, mysqlreport, and so on. Don’t get the wrong; those tools have …
[Read more]For companies that live and die by their databases, a one-shot backup isn’t really the perfect solution. Typically for such companies (think Yahoo! or Google), database access is a near-constant process, and database content changes continually, often on a second-by-second basis. Data replication, which involves continual data transfer between two (or more) servers to maintain a replica of the original database, is a better backup solution for these situations.
MySQL supports two (or three, depending on how you look at it) different methods of replicating databases from master to slave. All of these methods use the binary log; however, they differ in the type of data that is written to the master’s binary log.
Replication in MySQL is the dynamic process of synchronizing data between a primary (master) database server and one or more secondary (slave) database servers in near-real time. Using this process, it’s possible to create copies of one or more databases so that even if the primary server fails, data can still be recovered from one of the secondary servers.
Replication is essential for many applications, and the lack of replication support was a major drawback to MySQL compared to other relational database management systems (RDBMSs). MySQL 3.23 was the first version to introduce replication support, and support has improved continually in subsequent versions. However, MySQL is still best suited for one-way replication, where you have one master and one or more slaves.
TIP
As much as possible, try to use the same version of MySQL for both the master and slave server(s). A version …
[Read more]Ever get called out for a MySQL issue only to realize that there was no issue? It was a false alarm from the monitor. We sure have and it’s frustrating, especially at 3:00 or 4:00 in the morning!
Many DBAs work in an environment where there is some sort of first level support that gets assigned tickets first. Unfortunately, many of the times these groups are, shall we say, less than skilled in MySQL. As a result, they quickly escalate the ticket onto the primary on-call DBA, even when there is really nothing wrong.
Much of the time, there are multiple types of MySQL topology in these environments: standalone, galera cluster, replication, etc. Writing large runbooks with detailed test cases can be a daunting process and one that will cause many first-level support engineers to give up and simply escalate the issue anyway.
In an effort to avoid undue call outs, we developed a simple bash …
[Read more]I am attending Percona Live Amsterdam 2015 on September 21-23, 2015.
I will be on stage three times:
My first talk is a topic that has ben among my favorites for long time: I published an article about it in 2001, and several …
[Read more]In order to simplify the configuration of MySQL for standalone nodes, clusters, and replication configurations, we decided it would be easiest to maintain a common my.cnf file. We have to admit, the idea wasn’t ours; we picked the idea up from www.fromdual.com and thought it was such a great idea, we decided to implement it as well.
Below is our version of a standardized my.cnf implementing
several of our best practices. We hope it will be of
benefit to you.
######################################################################################################## # my.cnf (Configuration file for MySQL) # # Provided by Itchy Ninja Software to implement general best practices for MySQL regardless of server # type. We chose a single file instead of maintaining multiple versions of the configuration file. # Based upon http://www.fromdual.com/mysql-configuration-file-sample # # Sections are included for Percona XTRADB Cluster …[Read more]
http://blog.booking.com/abstracting_binlog_servers_and_mysql_master_promotion_wo_reconfiguring_slaves.html
Follow the link above to read my latest article on the Booking.com Developer Blog. It is about Binlog Servers and how to promote a slave as the new master without reconfiguring all slaves.
This is also a good opportunity to remind you of my next talks:
I’ll be giving a talk about Binlog