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 …[Read more]
2 Older Entries »
Even if you lost all data from a production server, physical backups (data files snapshot created with an offline copy or with Percona XtraBackup) could show the same internal database structure corruption as in production data. Backups in a simple plain text format allow you to avoid such corruptions and migrate between database formats (e.g., during a software upgrade and downgrade), or even help with migration from completely different database solution.
Unfortunately, the restore speed for logical backups is usually bad, and for a big database it could require days …[Read more]
But the real magic happens on the backend. This is the ecosystem that really powers your website. One writer has articulated this point very nicely as follows:
The technology and programming that “power” a site—what your end user doesn’t see but what makes the site run—is called the back end. Consisting of the server, the database, and the server-side applications, it’s the behind-the-scenes functionality—the brain of a site. …[Read more]
It’s not uncommon to promote a server from slave to master. One
of the key things to protect your data integrity is to make sure
that the promoted slave is permanently disconnected from its old
master. If not, it may get writes from the old master, which can
cause all kinds of data corruption. MySQL provides the handy
RESET SLAVE command. But as we’ll see, its behavior
has changed along with the MySQL versions and it’s easy to shoot
yourself in the foot if you use it incorrectly. So how do you
safely disconnect a replication slave?
- For MySQL 5.0 and 5.1, run
CHANGE MASTER TO MASTER_HOST=''and then
- For MySQL 5.5 and 5.6, run
STOP SLAVEand then
RESET SLAVE ALL.
- For all versions, ban
master-passwordsettings in my.cnf, …
Read the original article at Why does MySQL replication fail?
When considering active-active multi-master, you must consider it’s foundation technology. Although MySQL replication is straightforward to setup, it can fail in a myriad of ways. Most of those are known and well understood. We can solve them only if we use the technology in the standard way. Click through to the end for multi-master solutions [...]
For more articles like these go to Sean Hull's Scalable Startups
Related posts:[Read more]
In this post I describe what happens when a slave's Foreign Key setup is different from that of the master. I'm in particular interested in a setup where the slave has a subset of the master's foreign keys, or no foreign keys at all. I wish to observe whether integrity holds.
Making the changes
Which foreign keys do we have and how do we drop them? If you want to do this by hand, well, good luck! Fortunately, common_schema provides with quite a few handy views and routines to assist us. Consider viewing the existing foreign keys on sakila:
master> SELECT create_statement FROM common_schema.sql_foreign_keys WHERE TABLE_SCHEMA='sakila'; …[Read more]
In a recent rant on Why I think SQL_MODE is useless…, I wanted to counteract this statement with why we MUST all use SQL_MODE, even with the inherit flaws.
The fundamental principle of a database is to restore and retrieve data. When I can insert data into the database and then I select this data it is different, this is fundamentally wrong. This is a loss of essential data integrity, something a database should NEVER do.
SQL_MODE solves the problem of “silent truncation” in most instances, and produces an all important error. As pointed out, the SQL_MODE has several limitations, however the benefits do out way the risks. Quality control on source code can reduce the limitations, but no amount of coding can stop the CRUD that comes out of the database without some SQL_MODE settings.
I would ask two more …[Read more]
With the recent cyber attacks and breaches with data from large organizations including Sony, is your MySQL data safe? What are the best practices for securing and administering your MySQL environment? In this presentation we will cover the essential steps for better MySQL security. We will also cover the different installation and administration tasks necessary to ensure your data is managed.[Read more]
Replication as most people know it, has mostly been SQL statement propagation from master to slave. This is known as "statement-based" replication. But there is also another kind of replication that is available, "the row-based replication" and that has quite a lot of benefits. In this post I intend on highlighting the advantages and disadvantages of both the types of replication to help you choose the best one. I also follow up with my own recommendation.
The following are my slides that I presented at ODTUG Kaleidoscope 2010. This presentation talks about the MySQL defaults including a non-transactional state, silent data truncations, date management, and transaction isolation options. These are all critical for data integrity and consistency. I also cover in-depth topics including SQL_MODE, character sets and collations.
2 Older Entries »