MySQL 8.0 Password Rotation

MySQL 8.0 supports password rotation using dual password support. Dual password support As of MySQL 8.0.14, user accounts are permitted to have dual passwords, designated as primary and secondary passwords. Dual-password capability makes it possible to seamlessly perform credential changes in scenarios like this: A system has a large number of MySQL servers, possibly involving...

Measuring Slave Lag with Parallel Replication

The struggle to measure slave lag correctly is real. MySQL provides an estimate of the slave lag using the show slave status \G command, in the form of Seconds_Behind_Master mysql> show slave status \G .. Slave_IO_State: Waiting for master to send event Master_Log_File: mysql_binary_log.000061 Read_Master_Log_Pos: 26823921 Relay_Log_File: mysql_relay_log.000175 Relay_Log_Pos: 26811053 Relay_Master_Log_File: mysql_binary_log.000061 Slave_IO_Running: Yes Slave_SQL_Running:...

MySQL Parallel Replication: How parallel is it for your workload?

Parallel replication is a great feature of MySQL 5.7 that gets around the bottleneck of single-threaded replication. However not all workloads can take advantage of parallel replication. It would be nice to check whether your workload can benefit from parallel replication, and if so, what is the degree of parallelism that works for you. Set...

MySQL: Storing Time

Storing time in databases seems like a daunting task to many. The general principle with storing time in databases is - ALWAYS STORE TIME IN UTC. However for a lot of people this raises questions.. but my application expects time to be in US/Pacific, or my database server's time zone in PDT or most of...

Quick fix for Replication Error “but the master has purged binary logs containing GTIDs”

I ran into this error recently, and wanted to share a quick fix. This is what the problem looks like mysql> show slave status \G ..               Master_Log_File: mysql_binary_log.000382           Read_Master_Log_Pos: 341467211                Relay_Log_File: mysql_relay_log.000001        ...

LDAP Authentication for MySQL

Why LDAP? LDAP stands for Lightweight Directory Access Protocol. It is based on a client server model. A client queries LDAP server, which responds with an answer or with a pointer to where client can get more information. Most organizations have LDAP set up and configured for managing users and their credentials for internal applications....

Passwordless authentication using mysql_config_editor

In the DBA operations world there are always challenges with passwords. How do you use the password for login, automation and operation scripts in a way that does not expose the password. One of the MySQL utilities that addresses some of these questions is mysql_config_editor. mysql_config_editor enables you to store authentication credentials in a login...

Exchange Partition : An archiving strategy

As applications mature, tables tend to grow infinitely, specially the time series ones. They have tens of millions of rows and run up to a few TBs in size. Even though the tables have so much data, applications only need to access data that was recently saved. (Say within, the last year or so). A...

How Crash Recovery Works? : Part 3

In the last post we saw how the log is written during the operation of a database system. In this post we will go over the actual recovery mechanism and how the log is used. Why recovery? As we discussed in our first post on this topic, a recovery algorithm ensures that all the changes...

How Crash Recovery Works? : Part 2

Continuing where we left off in the last post, we saw why database systems use logs for recovery. Besides logs there are other supporting data structures that are needed for recovery. Broadly speaking there are three data structures that are part of a recovery mechanism (We’ll discuss the ARIES mechanism here) LogTransaction TableDirty Page Table...