As part of my ongoing focus on MySQL 8 user and password management, I’ve covered how the new dual passwords feature can reduce the overall DBA workload and streamline the management process (see MySQL 8: Dual Passwords). This wasn’t the only change to user/password management in MySQL 8; one of the more security-focused changes was the implementation of temporary account locking, first introduced in MySQL 8.0.19. With this feature, database administrators can now configure user accounts so that too many consecutive login failures can temporarily lock the account.

The account locking feature only applies to the failure of a client to provide a correct password during the connection attempt. It does not apply to failure to connect for other reasons (network issues, unknown user account, etc.). In the case of dual passwords, either of the account …

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

This is the tenth episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

In this episode, we’ll learn how to connect to our MySQL Database Service instance using the Oracle Cloud Infrastructure Cloud Shell.
It provides a pre-authenticated Oracle Cloud Infrastructure CLI and preinstalled developer tools for easily managing Oracle Cloud resources.

The post Discovering MySQL Database Service – Episode 10 – Connect to MySQL Database …

Connect PlanetScale to JetBrains DataGrip to use database branching, deploy requests, and non-blocking schema changes

Read the full story

It is a common use case to synchronize data in two tables inside MySQL servers. This blog post describes one specific case: how to synchronize data between two different tables on the same MySQL server. This could be useful, for example, if you test DML query performance and do not want to affect production data. After few experiments, tables get out of sync and you may need to update the test one to continue working on improving your queries. There are other use cases when you may need to synchronize the content of the two different tables on the same server, and this blog will show you how to do it.

Table Content Synchronization

The industry-standard tool for table content synchronization – pt-table-sync – is designed to synchronize data between different MySQL servers and does not support bulk synchronization between two …

An interesting outcome of teaching SQL is discovering what skills new users require. One that I continuously rediscover is how to build a test case for various elements of SQL. This is a small article on querying with filters in the WHERE clause.

There are several of the exercises in Alan Beaulieu’s Learning SQL, 3rd Edition that would benefit from example setup. For example, Chapter 4 provides a snapshot of the payment table but doesn’t provide any instructions.

You can create an exercise_4_2 table with the following SQL statement if you plan to change the data:

CREATE TABLE exercise_4_2 AS
SELECT payment_id
,      customer_id
,      amount
,      payment_date
FROM   payment
WHERE  payment_id BETWEEN 101 AND 120;

Alternatively, you can create an exercise_4_2 view with the following SQL statement if you plan to only query the data:

CREATE VIEW exercise_4_2 AS
SELECT payment_id
, …

[…] I am connecting following Andrews advice: Stackoverflow Link Also, Monitoring and Managing […]

 Want to learn MySQL? Have an hour or so?  Go to Oracle's Learning Explorer and sign up for the FREE MySQL Explorer learning path.  It begins with an overview of MySQL, discusses the client/server model, and then leads you through the use of MySQL.  You do not need previous MySQL or database experience.

MySQL is the most-used opensource database and over the years I have has requests for introductory materials. Well, now I can send them to the MySQL Explorer for some web based training. And you can earn the explorer badge when you pass the end of class quiz.

…

In a previous post, A Horizontal Scalability Mindset for MySQL, I discussed the concerns around growing individual MySQL instances too large and some basic strategies:

• Optimizing/minimizing size with proper data types
• Removing unused/duplicate indexes
• Keeping your Primary Keys small
• Pruning data

Finally, if those methods have been exhausted, I touched on horizontal sharding as the approach to keep individual instances at a reasonable size. When discussing my thoughts across our internal teams, there was lots of feedback that we needed to dive into the sharding topic in more detail. This post aims to give more theory and considerations around sharding along with a lightweight ProxySQL sample implementation.

What is Sharding?

Sharding is a word that is frequently used but …

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

This is the ninth episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

In this episode, we’ll learn how to connect to our MySQL Database Service instance using MySQL Workbench – a unified visual tool for database architects, developers, and DBAs that provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more.

The post Discovering MySQL Database …

I ranted about hiring interviews, and the canned questions that people have to answer. One of the interviews we do is a systems design interview, where we want to see how (senior) people use components and patterns to design a system for reliability and scale-out.

A sample question (based on a Twitter thread

in German):

It is 2010, and the company has a database structure where a fixed number front end machines form a cell. Reads and writes are already split: Writes go to the primary of a replication tree, and are being replicated to the read instance of the database in each cell. Reads go to the database instance that is a fixed part of the cell.

Read and write handles are split in the application. Clients write to a primary MySQL database, which then replicates to a database instance that is fixed part of a cell. Clients …