The MySQL UNIQUE constraint is often used in a column definition in which we need each value for that column to be distinct from the others. Perhaps it is an email column for an on-line registration form and we want to ensure that users cannot register twice for an account using the same email. Whatever the case may be, UNIQUE is there to help us ensure this type of data integrity or business requirement. What if the target table already exists and you determine you need to add a UNIQUE constraint to an existing column? In this post, I will cover 2 ways you can implement a UNIQUE constraint on existing columns using the phpMyAdmin web interface…

Photo by Kaleidico on …

In this post I describe the various steps that allowed me to reach 3.6 million queries per second on a single VM instance using MySQL 8.0 with the help of Dynimize.

It's not every day that you get to break a record. So when I discovered that you can now rent by the hour massive instances within Google Compute Cloud that support 224 virtual cores based on AMD EPYC 2 Rome processors, I had to jump at the opportunity to see what kind low hanging fruit might be out there. Low and behold I found it! Oracle's performance record for MySQL on a single server stands at 2.1M QPS without using Unix sockets, and 2.25M QPS with Unix sockets. Seeing that they published this 3 years ago on Broadwell based …

Description

Join this virtual meetup featuring WePay and Oracle MySQL! They will review three great talks on MySQL Compatibility Check, Running MySQL on Kubernetes and Scalable Bookkeeping.

Talk 1: MySQL Compatibility Check

Talk 2: Running MySQL on Kubernetes

Talk 3: Scalable Bookkeeping

Date & Time 

Oct 1, 2020 10:00 AM in Pacific Time (US and Canada)

Register here

All opinions expressed in this blog are those of Dave Stokes who is actually amazed to find anyone else agreeing with him

A question to the internal #DBA channel at work: »Is it possible to change a column type from BIGINT to VARCHAR ? Will the numbers be converted into a string version of the number or will be it a byte-wise transition that will screw the values?«

Further asking yielded more information: »The use-case is to have strings, to have UUIDs.«

So we have two questions to answer:

• Is ALTER TABLE t CHANGE COLUMN c lossy?
• INTEGER AUTO_INCREMENT vs. UUID

Is ALTER TABLE t CHANGE COLUMN c lossy?

ALTER TABLE is not lossy. We can test.

mysql> create table kris ( id integer not null primary key auto_increment);
Query OK, 0 rows affected (0.16 sec)

mysql> insert into kris values (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into kris select NULL from kris;
Query OK, 1 …

MySQL uses connection and config parameters from a number of possible sources. The easiest way to find out where it is looking for config files is to run

$ mysql --help | grep cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /Users/kkoehntopp/homebrew/etc/my.cnf ~/.my.cnf

As can be seen, my version of the MySQL client checks in this order

• /etc/my.cnf
• /etc/mysql/my.cnf
• /Users/kkoehntopp/homebrew/etc/my/cnf
• ~/.my.cnf

The cnf file is a file in dot-ini syntax, so you have [groups] and each group contains lines with key = value pairs. Which groups are read?

$ mysql --help | grep "groups are"
The following groups are read: mysql client

So in my case, I would create a /Users/kkoehntopp/.my.cnf looking like this:

[client] …

Percona Monitoring and Management (PMM) is an effective tool in tracking stats of the running MySQL servers. Especially, the timelines capability helps users to get the picture of how the given stats changes over tenure of the workload. PMM official packages are not yet available on ARM but part of the PMM (importantly the stats collector aka exporter) could be compiled on ARM that would facilitate reporting stats of the MySQL instance running on ARM to PMM-Server there-by allowing it to track MySQL on ARM.

Background

Few days back Agustin from Percona tried compiling PMM Client on ARM. You can read more about it here. I just extended the process to also compile mysqld_exporter that is needed to connect and collect MySQL stats.

Compiling mysqld-exporter

Steps assume …

During the MDS webinar on how to deploy WordPress on OCI using MDS (slides & video), I briefly explained how to deploy the full architecture on OCI using Resource Manager and Stacks.

The Stack for that architecture is now available on my github: https://github.com/lefred/oci-wordpress-mds/releases/tag/0.0.1

To deploy it, it’s very easy. In OCI’s Dashboard, go on “Resource Manager” and then choose “Stacks“:

Create a new stack and just drop the …

Today we will see how to use MySQL Database Service aka MDS with WordPress.

To achieve this easy task, we will use the architecture we already deployed in this article.

We have then two Compute Instances on OCI, 1 running WordPress (Apache and PHP) and one running MySQL 8.0.

The Plan

This is how we will proceed to migrate to MDS with minimal maintenance time, we will:

1. create a MDS instance
2. verify if the database is ready to act as replication source
3. dump the MySQL instance running on OCI for being migrated to MDS.
4. load the dump in MDS
5. create a user dedicated to the replication
6. create a replication channel on MDS (from OCI to MDS)
7. modify WordPress config to point to MDS

Create a MDS …

This is just a note to help out anyone that might want to use the mysql_config_editor command in their automation tools. 

the mysql_config_editor does not take a password argument so automation tools that might have before set your password in the .my.cnf file trying to use mysql_config_editor fails. 

It is possible and quite simple though with the expect tool. 

 yum -y install expect  

it works for apt-get also. 

So in this example, I will show a simple bash script version. 

1st.. my login path does not work... 

mysql --login-path=local

ERROR 1045 (28000): Access denied for user

Set this with expect 

You would execute this via your bash script.  

expect <<EOD

spawn mysql_config_editor set --login-path=local …

Iterating schemas over time is not an uncommon thing. Often requirements emerge only after you have data, and then directed action is possible. Consequently, working on existing data, and structuring and cleaning it up is a common task.

In todays example we work with a log table that logged state transitions of things in freeform VARCHAR fields. After some time the log table grew quite sizeable, and the log strings are repeated rather often, contributing to the overall size of the table considerably.

We are starting with this table:

CREATE TABLE `log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_id` int NOT NULL,
  `change_time` datetime NOT NULL,
  `old_state` varchar(64) NOT NULL,
  `new_state` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

That is, our log table has an id field to allow individual row addressing, and then logs the state change of a …