Ideas, releases, practical guides, and perspectives from the people building with MySQL.
Learn what data integrity in DBMS means, why it’s vital for database accuracy, and how to maintain it effectively. Read on to protect your data now.
The post Data Integrity in a Database: How to Ensure Accuracy and Security appeared first on Devart Blog.
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]The ability to restore MySQL logical backups is a significant part of disaster recovery procedures. It’s a last line of defense.
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]
The website or mobile app is the storefront for participating in the modern digital era. It’s your portal for inviting users to come and survey your products and services. Much attention focuses on front-end development; this is where the HMTL5, CSS, and JavaScript are coded to develop the landing page that everyone sees when they visit your site.
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?
In short
STOP SLAVE,
CHANGE MASTER TO MASTER_HOST='' and then RESET
SLAVE.
STOP SLAVE and then
RESET SLAVE ALL.
master-user,
master-host and master-password
settings 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 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.
Presenter: Ronald Bradford
Schedule: Insight Out DB Showcase. October 2011 …
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.
I realized tonight exactly why MySQL’s default behavior of silent truncation bothers me.
It reminds me of people who use a ticketing system and close every ticket as soon as they are done working on the issue instead of actually asking the other party if they are satisfied, because closing more tickets make it look like they’re doing more work.
It reminds me of workers at fast food restaurants who hit the button to make the order disappear as if they have already served me my food, because then their throughput times are faster.
Similarly, with MySQL’s default behavior of silent truncation, it’s as if the database server is saying “the fewer database errors raised, the better.” As in the previous two examples, the metrics do not matter if the quality of service is poor — particularly when the quality of service is poor specifically *because* people are trying to meet the metrics instead of the actual goal …
[Read more]