Where I work, we are using MySQL in a scale-out configuration to handle our database needs.

That means, you write to a primary server, but reads generally go to a replica database further down in a replication tree.

A number of additional requirements that should not concern you as a developer make it a little bit more elaborate than a simple “primary and a number of replicas” configuration. But the gist of all that is:

• there is always a read-copy of the database very close to your application, latency wise
• there are always sufficient copies of the data around so that we can afford to run our databases on unraided local storage.

The nature of our databases is such, that we drown all data reads with sufficient memory, where ever that is possible. Our databases are Memory Engines, when it comes to reads.

I joke about that:

You, too, can be a successful database …

Geo locations are a cornerstone of modern applications. Whether you’re a food delivery business or a family photographer, knowing the closest “something” to you or your clients can be a great feature.

In our ‘Scaling and Optimization’ training class for MySQL, one of the things we discuss is column types. The spatial types are only mentioned in passing, as less than 0.5% of MySQL users know of their existence (that’s a wild guess, with no factual basis). In this post, we briefly discuss the POINT type and how it can be used to calculate distances to the closest public park.

Import the data

To start off, we need a few tables and some data. The first table will hold the mapping between the zip code and its associated latitude/longitude. GeoNames has this data under the Creative Commons v3 license, …

Let’s continue the discovery of the MySQL Database Resource when deploying on Oracle Cloud Infrastructure using Terraform.

Last week, we saw how to create custom configurations and define user variables. Today we will see how we can define a backup policy and a maintenance window.

Backup Policy

In the oci_mysql_mysql_db_system resource, we will add a new section called backup_policy like this:

backup_policy {
       is_enabled        = "true"
       retention_in_days = "3"
       window_start_time = "01:00-00:00"
       freeform_tags = {"backup_defined_by"="Terraform"}
       pitr_policy {
            is_enabled = "true"
       }
}

This part of code (you can see in a working Terraform architecture sample), enables backup, sets the retention days to 3. It also defines the starting time …

Where I work, there is an ongoing discussion about test data generation. At the moment we do not replace or change any production data for use in the test environments, and we don’t generate test data.

That is safe and legal, because production data is tokenized. That is, PII and PCI data is being replaced by placeholder tokens which can be used by applications to access the actual protected data through specially protected access services. Only a very limited circle of people is dealing with data in behind the protected services.

Using production data in test databases is also fast, because we copy data in parallel, at line speed, or we make redirect-on-write (“copy-on-write” in the age of SSD) writeable snapshots available.

Assume for a moment we want to change that and

• mask data from production when we copy it to test databases
• reduce the amount of data used in test databases, while …

The microservice architecture is not a new pattern but has become very popular lately for mainly two reasons: cloud computing and containers. That combo helped increase adoption by tackling the two main concerns on every infrastructure: Cost reduction and infrastructure management.

However, all that beauty hides a dark truth:

The hardest part of microservices is the data layer

And that is especially true when it comes to classic relational databases like MySQL. Let’s figure out why that is.

MySQL and the microservice

Following the same two pillars of microservices (cloud computing and containers), what can one do with that in the MySQL space? What do cloud computing and containers bring to the table?

Cloud computing

The magic of the cloud is that it allows you to be cost savvy by letting you easily SCALE UP/SCALE DOWN the size of your instances. No more wasted money on …

Once again, OpenLampTech has another full issue for all of your MySQL, PHP, and LAMP stack content. The publication continues to grow. Thank you all for reading.

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

In OpenLampTech issue #45, enjoy articles covering:

• Symfony’s Twig
• MySQL PIVOT functionality
• Building a niche website with WordPress
• 10 popular Linux distros
• And much much more.

Want to know how you can support OpenLampTech?

Head over to the …

In the MySQL world, I am surrounded by some of the best database consultant, it brings them immense joy about having a “Friday”, so much so that they cannot believe…

The post Providing Friday Proofs to MySQL Consultants | Scripts Functions Routines first appeared on Change Is Inevitable.

Overview JavaScript Object Notation (JSON) is a light-weight text-based file format similar to YAML or XML which simplifies data exchange. It was invented by Douglas Crockford in the early 2000s and became increasingly popular with the rise of document-based (also called NoSQL) databases. JSON supports strings, numbers, booleans, objects, and arrays as well as null values. A simple JSON example containing key-value pairs, an object "bandMembers" and an array "songs" would look like this:{ "artist": "Starlord Band", "bandMembers": { "vocals": "Steve Szczepkowski", "guitar": "Yohann Boudreault", "bass": "Yannick T.", "drums": "Vince T." }, "bandMembersCount": 4, "album": "Space Rider", "releaseDate": "2021-10-25", "songs": [ "Zero to Hero", "Space Riders with No Names", "Ghost", "Bit of Good (Bit of Bad)", "Watch me shine", "We’re Here", "The Darkness inside", "No Guts No Glory", "All for One", "Solar Skies" ], "songsCount": 10 }

MySQL has …

Learn what the MySQL JSON data type is when to use MySQL JSON and some caveats to using JSON documents in relational databases.

When you develop an application you expect success, and often success comes with growth problems.  These problems especially show themselves in the area of data storage, where being stateful is not as easy to scale as the stateless parts of the application.

There are several stages of approaching database scalability:

1. Configuration and query optimization. This step can help a lot, and I would recommend a recent book by Daniel Nichter “Efficient MySQL Performance: Best Practices and Techniques”, which goes into this topic.
2. If #1 is done and you continue to push the limits of your database, the next step is to improve the hardware: adding extra memory, improving storage throughput (regular SSD, NVMe storage layer, etc.), or increasing the size of the cloud instances (this is what I call “optimization by credit card”). This typically should help, but only to a certain limit. And there is only so much …