Showing entries 1 to 10 of 12
2 Older Entries »
Displaying posts with tag: data integrity (reset)
The curse of MySQL warnings

MySQL warnings are an anti-pattern when it comes to maintaining data integrity. When the information retrieved from a database does not match what was entered, and this is not identified immediately, this can be permanently lost.

MySQL by default for several decades until the most recent versions enabled you to insert incorrect data, or insert data that was then truncated, or other patterns that resulted in failed data integrity. Very few applications considered handling warnings as errors, and there is a generation of software products that have never informed the developers that warnings were occurring.

The most simplest example is:

CREATE SCHEMA IF NOT EXISTS warnings;
USE warnings;

CREATE TABLE short_name(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  PRIMARY KEY(id)
);

INSERT INTO short_name (name) VALUES ('This name is too long and will get truncated');
ERROR 1406 (22001): Data too long for …
[Read more]
Re-Slaving a Crashed MySQL Master Server in Semisynchronous Replication Setup

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]
How to Restore MySQL Logical Backup at Maximum Speed

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]
Complete Megalist: 25 Helpful Tools For Back-End Developers

 

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]
Disconnecting a replication slave is easier with MySQL 5.5+ (RESET SLAVE vs. RESET SLAVE ALL)

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

  • For MySQL 5.0 and 5.1, run STOP SLAVE, CHANGE MASTER TO MASTER_HOST='' and then RESET SLAVE.
  • For MySQL 5.5 and 5.6, run STOP SLAVE and then RESET SLAVE ALL.
  • For all versions, ban master-user, master-host and master-password settings in my.cnf, …
[Read more]
Why does MySQL replication fail?

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:

  1. 5 Ways to fortify MySQL replication
  2. Easy …
[Read more]
Why SQL_MODE is essential even when not perfect

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]
Better MySQL Security and Administration


Download PDF Presentation

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 …

[Read more]
Statement-based vs Row-based Replication

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.

MySQL Idiosyncrasies That Bite

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.

MySQL Idiosyncrasies That Bite View more presentations from Ronald Bradford.

Showing entries 1 to 10 of 12
2 Older Entries »