Scaling a database presents challenges. As you grow, you might begin having trouble handling ever-increasing throughput or data size. You might find that query latency is getting worse. You might be pushing the limits of your hardware. When this happens, a classic option is vertically scaling your database by getting better hardware, but is there a better way? And what happens when you reach the vertical limits? This is where horizontal sharding comes in. In this article, we'll cover some common indicators that your database may be ready for horizontal sharding. We'll also look at some measures you can implement until then. Let's dig in. Hitting the limits There are lots of different limits that you can run into when you're scaling up. At the database level, you might be maxing out CPU, memory, disk space, or IOPS. Running into these limits can have real consequences for your business. Database operations like schema changes will start taking longer, …

OpenLampTech continues to publish great content for the PHP, MySQL, LAMP stack, and open-source communities. This week, I (am resharing) have another awesome text-based developer interview for you with Wendell Adriel. Thanks 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.

Visit and read Wendell’s fantastic interview here: https://openlamptech.substack.com/p/openlamptech-developer-interview-18d

If you’re not already …

This is the second blog in the series where I talk about INSTANT ADD/DROP columns feature introduced in MySQL 8.0. After the introductory Blog, this blog aims to provide more design details.

This blog post discusses the limitations of the MySQL Clone plugin.

The MySQL clone plugin significantly simplifies the process of replica provisioning. All you need to do is:

• Ensure that the source server has binary logs enabled
• Grant appropriate permissions
• Execute the CLONE INSTANCE  command on the recipient

This works extremely easily when you provision a new replica that doesn’t have any data.

Due to its simplicity, you may want to use the clone plugin instead of a backup to restore a server that survives data inconsistency or corruption. E.g., after crash.

However, if you have data on your replica, you need to consider how you will recover if the CLONE INSTANCE  command fails with an error.

CLONE INSTANCE  command, by default, works as follows:

• Checks prerequisites on the replica
• Wipes out data …

As MySQL database administrators, we are well aware of the significance of implementing a primary key within a table. Throughout our careers, most of us have encountered situations where the absence of a primary key has led to operational challenges. Primary keys play an indispensable role in sound database design by uniquely identifying individual rows and significantly enhancing data retrieval, manipulation, and overall system performance.

From the MySQL documentation:

The PRIMARY KEY clause is a critical factor affecting the performance of MySQL queries and the space usage for tables and indexes. The primary key uniquely identifies a row in a table. Every row in the table should have a primary key value, and no two rows can have the same primary key value.

It is common for tables to be inadvertently created without a primary key, often leading to regrettable consequences that we only recognize when issues …

ProxySQL is a high-performance SQL proxy, which runs as a daemon watched by a monitoring process. The process monitors the daemon and restarts it in case of a crash to minimize downtime.

The daemon accepts incoming traffic from MySQL clients and forwards it to backend MySQL servers.

The proxy is designed to run continuously without needing to be restarted. Most configurations can be done at runtime using queries similar to SQL statements in the ProxySQL admin interface. These include runtime parameters, server grouping, and traffic-related settings.

Here, we will consider ProxySQL configured for async replication. Even when a replica is broken/stopped, ProxySQL still routes connections to replicas. It can be overcome by setting the appropriate value for mysql-monitor_slave_lag_when_null

…

During a migration to MySQL HeatWave, it could be interesting to cut & paste user creation statements. In this blog you learn how to achieve this.

Another packed full newsletter coming your way this week in OpenLampTech. So much goodness in this one. Take your time reading, enjoy, and share. Thank you.

Custom WooCommerce and Shopify Solutions

Discover useful WooCommerce and Shopify custom solutions for your online store today at affordable prices!

Learn More

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 #97, there is great content on:

• Developer interview
• MySQL …

Sometimes it’s convenient to retrieve the user creation statement and to copy it to another server.

However, with the new authentication method used as default since MySQL 8.0, caching_sha2_password, this can become a nightmare as the output is binary and some bytes can be hidden or decoded differently depending of the terminal and font used.

Let’s have a look:

If we cut the create user statement and paste it into another server what will happen ?

We can see that we get the following error:

ERROR: 1827 (HY000): The password hash doesn't have the expected format.

How could we deal with that ?

The solution to be able to cut & paste the authentication string without having any issue, is to change it as a binary representation (hexadecimal) like this:

And then replace the value in the user create statement:

But there is an easier way. MySQL …

Here comes the success story of Specialized Media Dashboard, a project that comprises an open-source media monitoring system. The specialized media in question encompasses the spheres of journalism, law enforcement, and climate change.

The post Case Study: How the Engineers of SMD Found a Way to Process Millions of Database Records Faster With dbForge Studio for MySQL appeared first on Devart Blog.