TL; DR

On a new TABLE:

CREATE TABLE child (ID INT PRIMARY KEY, parent_ID INT, FOREIGN KEY my_fk (parent_id) REFERENCES parent(ID));

On an existing TABLE:

ALTER TABLE child ADD FOREIGN KEY my_fk (parent_id) REFERENCES parent(ID);

MySQL has the ability to enforce a record that exists on a parent table when you are adding/modifying data or validate that a record doesn’t exist when you are deleting data from your child table, leaving your database inconsistent. This is called Foreign Key.

As seen above, you can either create your table with an FK since the beginning or modify/alter your table to add a new constrain after table creation time. Apart from syntax to refer to a field on the parent table, you can control what will be the behavior when you UPDATE or DELETE a record on the PARENT table that has a reference to in on the child table. This is controlled by …

We’re very excited to open up our ProxySQL Total Training online course to the public!

Get yourself, or your team, trained by our Professional ProxySQL Trainers who have built and actively maintain ProxySQL in our upcoming total Training course.

Designed for DBAs, application developers and IT professionals in the industry, the course will focus on real world implementation and hands-on labs that will ensure you acquire the needed skills to deploy efficient, scalable and highly available solutions with ProxySQL!

Immerse yourself in the world of ProxySQL with our two-day ProxySQL Total Training.

ProxySQL Total Training topics include:

• ProxySQL use cases and real world examples
• How to install, upgrade and configure ProxySQL with zero-downtime
• Query routing, query caching and query fire-walling
• Advanced performance …

In my day to day tasks as a DB Engineer at Mydbops we have been using multiple MySQL tools for multiple use cases to ensure an optimal performance and availability for servers managed by our Remote DBA Team.

A tool like pt-online-schema can be used for any DDL changes ( Overview to DDL algorithm ), if any tool which needs to scheduled for longer period we tend to use screen or cron.

Some of the problems we face when we demonise the process or use screen for running processes.

• The daemon process gets killed when the server reboot happens. …

You got yourself a Kubernetes cluster and are now testing our Percona Kubernetes Operator for Percona XtraDB Cluster. Everything is working great and you decided that you want to increase the number of Percona XtraDB Cluster (PXC) pods from the default 3, to let’s say, 5 pods.

It’s just a matter of running the following command:

kubectl patch pxc cluster1 --type='json' -p='[{"op": "replace", "path": "/spec/pxc/size", "value": 5 }]'

Good, you run the command without issues and now you will have 5 pxc pods! Right? Let’s check out how the pods are being replicated:

kubectl get pods | grep pxc
cluster1-pxc-0                                     1/1     Running   0          25m
cluster1-pxc-1                                     1/1     Running   0          23m
cluster1-pxc-2                                     1/1 …

ProxySQL Firewall Overview

ProxySQL’s flexible query rules engine has many uses, from Read/Write splitting, sharding and even creating firewall blacklist. This allows ProxySQL to be loved by both Performance and Security-minded engineers.

Starting in ProxySQL 2.0.9, ProxySQL has another Security feature: the Firewall Whitelist.

Modeled on MySQL Enterprise Firewall, this allows a security-conscious administrator to tune access to only allow certain queries.

Imagine a situation where your webapp gets hacked, which exposes your user’s database credentials.

If your webapp connects directly to the database, the malicious user can do what they want to your data with the same permissions your webapp has.

So perhaps they can’t just DROP TABLE because you’ve smartly removed DDL permissions …

Following on from our recent newsletter and the message from Eero Teerikorpi, Founder and CEO, in this blog I will set out the outline and plan for the work we are currently undertaking to make a number of changes within the terminology used in our product and communications.

Tags:  MySQL terminology replica primary source target tungsten clustering …

In any MySQL replication or migration, generally, we’ll skip the mysql database from the dump file. Since it has the current server’s settings, users, etc. So we should not create a mess on the target server with this source mysql database dump. But one downside of this approach is we’ll not get the users on the target MySQL server. Maybe you tried some approaches that are mentioned here. But whenever I don’t the credentials list during the migration and don’t have time to explore/install any tools likept-show-grants, then I’ll use this trick.

DISCLAIMER You should try this approach when you don’t any user credentials on the source DB. Take a backup of the MySQL database on the target DB side. Do this with …

Ever wanted to extend MySQL and add some feature you think it is missing?  With MySQL plugins, you can do exactly that.  One thing that has bothered me for several years is that you cannot easily retrieve system metrics from within MySQL.  Whether I am connecting via a remote connection or looking to add features to monitoring without the need for another interface with the server, I have wanted to retrieve system metrics without leaving the MySQL interface.

So, I started a Proof of Concept for this.  My goal was to get metrics such as RAM (total, used, free), system load, CPU utilization, disk utilization for the file system containing the datadir, and more.  My objective was to do this as efficiently within MySQL as possible.  For this, I chose to utilize standard C libraries in as few lines of code as possible without having to scrape system files or run commands to get the data.  The …

Why another article on this, Marco?

MySQL deadlocks is a topic covered many times, including here at Percona. I suggest you review the reference section at the end of this post for articles on how to identify deadlocks and from where they are generated.

So why another article?

The answer is that messages we receive like the following are still very common:

User (John): “Marco, our MySQL is having problems”
Marco: “Ok John what problems? Can you be a bit more specific?”
John: “Our log scraper is collecting that MySQL has a lot of errors”
Marco: “Ok can you share the MySQL log so I can review it?”
John: “Errors are in the application log, will share one application log”

Marco reviews the log and in it he finds:

“ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction”

Marco’s reaction is: “Oh …

Oracle MySQL is in need of a columnar storage engine for analytics workloads.  A columnar engine (or column store) stores data vertically, that is, it stores all the data associated with a column together, instead of the traditional RDBMS storage method of storing entire rows together, either in a index organized manner, like InnoDB, or in a heap, like MyISAM.  

Columnar storage has the benefit of reducing IO when only a subset of the row is accessed in a query, because only the data for the accessed rows must be read from disk (or cache) instead of having to read entire rows.  Most columnar stores do not support indexes, but WARP does.

WARP is open source

You can find the WARP source code release on GitHub.  Binaries can be provided upon request.  Simply open an issue for your desired Linux distribution, and I will make them available as soon as I can.

…