Prepare the RPM repository server

Install the software to create a RPM repository server (on an Ubuntu web server):

shell> sudo apt-get install createrepo

Create the directory structures and initialize the repositories:

shell> sudo mkdir -p /var/www/html/repos/centos/7/os/x86_64
shell> sudo createrepo /var/www/html/repos/centos/7/os/x86_64/

Copy the MySQL Enterprise RPM packages to the repository:

shell> sudo cd /var/www/html/repos/centos/7/os/x86_64/
shell> sudo unzip -v /download/V790254-01.zip   # MySQL Enterprise Server 5.7.16
Archive:  /download/V790254-01.zip
   Length  Method      Size Cmpr    Date    Time   CRC-32   Name
---------  ------  -------- ---- ---------- ----- --------  ----
 23979568  Stored  23979568   0% 2016-09-29 16:56 ae6693b1  mysql-commercial-client-5.7.16-1.1.el7.x86_64.rpm
 45327096  Stored  45327096   0% 2016-09-29 16:56 bb83c965 …

The pt-online-schema-change tool has been a workhorse for years, allowing MySQL DBAs to alter tables with minimal impact to application workload, and before MySQL had native online alter capabilities. Although consistently grateful for the tool’s existence, I never liked the messiness of having to use and clean up triggers, and most DBAs have a horror story or two to tell about using any of the online alter methods.

When Github’s online schema transmogrifer (gh-ost; https://github.com/github/gh-ost) was released, then, I jumped on the chance to test it out.

Following are my testing notes. I tried out the flags available to run gh-ost from different locations in a replication setup, both functionally and under load. The load applied was via mysqlslap, using a …

MySQL Cluster uses a distributed architecture where we have one primary
replica and one or more backup replicas of all data in the cluster.
In previous versions we have not supported reading from the backup
replicas. The reason for this comes from the transaction algorithm in
the NDB storage engine. When the transaction have committed we have
unlocked the rows in the primary replica, but the locks in the backup
replica is unlocked in the complete phase.

The impact of this is that we could potentially perform an update
of a row and then immediately read it and not see our own write. To
avoid this problem we have avoided to read the backup replica.

If it is important to read the backup replica then we need to ensure
that transactions are not reported back to the user until the complete
phase.

When we decided to support read from …

This blog post discusses the ramifications of STRICT mode in MySQL 5.7.

In short

By default, MySQL 5.7 is much “stricter” than older versions of MySQL. That can make your application fail. To temporarily fix this, change the

SQL_MODE

 to

NO_ENGINE_SUBSTITUTION

 (same as in MySQL 5.6):

mysql> set global SQL_MODE="NO_ENGINE_SUBSTITUTION";

MySQL 5.7, dates and default values

The default

SQL_MODE

 in MySQL 5.7 is:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

That makes MySQL operate in “strict” mode for transactional tables.

“Strict mode …

The Theory

Generated Columns is a feature released on MySQL 5.7. They can be used during CREATE TABLE or ALTER TABLE statements. It is a way of storing data without actually sending it through the INSERT or UPDATE clauses in SQL. The database resolves what the data will be.

There are two types of Generated Columns: Virtual and Stored. They work with:

• mathematical expressions (product_price * quantity)
• built-in functions (RIGHT(), CONCAT(), FROM_UNIXTIME(), JSON_EXTRACT())
• literals (“2”, “new”, 0)

Besides that, they can be indexed but they don’t allow …

In this blog post, we’ll explain how to update the signing key for Percona Debian and Ubuntu packages.

Some of the users might have already noticed following warning on Ubuntu 16.04 (Xenial Xerus):

W: http://repo.percona.com/apt/dists/xenial/InRelease: Signature by key 430BDF5C56E7C94E848EE60C1C4CBDCDCD2EFD2A uses weak digest algorithm (SHA1)

when running apt-get update.

Percona .deb packages are signed with a key that uses an algorithm now considered weak. Starting with the next release, Debian and Ubuntu packages are signed with a new key that uses the much stronger SHA-512 algorithm. All future package release will also contain the new algorithm.

You’ll need to do one of the …

This blog discusses the gradual end of MyISAM in MySQL.

The story that started 20 years ago is coming to its end. I’m talking about the old MyISAM storage engine that was the only storage provided by MySQL in 1995, and was available in MySQL for 20+ years. Actually, part of my job as a MySQL consultant for 10+ years was to discover MyISAM tables and advise customers how to convert those to InnoDB.

(Check your MySQL installation, you may still have MyISAM tables).

MySQL 5.7 still used MyISAM storage for the system tables in the MySQL schema.

In MySQL 8.0 (DMR version as of writing), the MyISAM storage engine is still available. But in a very limited scope:

• After …

First of all I want to say thanks to Thomas that sent me feedback on part1 (see his comment). And indeed all three cases were able to defeat the datamasking.

I rewrote then the rules to also take those cases in consideration:

ProxySQL> DELETE FROM mysql_query_rules where rule_id  INSERT INTO mysql_query_rules 
          (rule_id,active,username,match_pattern,replace_pattern,apply)  
          VALUES (1,1,'devel','`cc_num`',"cc_num",0);
ProxySQL> INSERT INTO mysql_query_rules 
          (rule_id,active,username,match_pattern,replace_pattern,apply)  
           VALUES (2,1,'devel','^[sS][eE][lL][eE][cC][tT] (.*)cc_num([ ,\n])(.*)',
           "SELECT \1CONCAT(LEFT(cc_num,2),REPEAT('X',10)) cc_num\2\3",1);

However the current recursive implementation of ProxySQL (using flagIN & flagOUT) is not an option to face the case where a …

MySQL 5.7 improved as compare to previous releases in terms of transnational capabilities, performance with high load, high Availability, Security and it’s defaults.

Check my blog post : MySQL 5.7 features

This blog post will describe End to End implementation of MySQL on Linux distributions Which will cover MySQL Installation, configuration and administration in production environment with proper configuration. So you can start using your application by implementing following setup and in future you can change it if requires.

• Database Installation
• MySQL Variables and Configuration
• …

Download Slides

The second MySQL tutorial session at Oracle Open World was “MySQL Group Replication in a Nutshell” by MySQL Community Manager Frederic Descamps. This is succinctly described as:

“Multi-master update anywhere replication for MySQL with built-in conflict detection and resolution, automatic distributed recovery, and group membership.”

MySQL Group Replication (GR) is a virtually synchronous replication solution which is an integral component of MySQL InnoDB Cluster announced at the MySQL keynote. You can download a labs version of MySQL InnoDB cluster which includes three components.

• MySQL Router …