The latest from the MySQL community

Fairly recently, we’ve upgraded to MySQL 8; it’s been a relatively smooth transition, however, some minor differences needed to be handled. One of them is the behavior of trailing spaces.

Trailing spaces are a (not in a good way) surprising, but also widely covered argument. This article gives a short overview, and relates it to how this affects people upgrading to MySQL 8.0.

Contents:

• Premises/Requirements
• Behavior in different contexts
  • …

WHM and cPanel is no doubt the most popular hosting control panel for Linux based environments. It supports a number of database backends - MySQL, MariaDB and PostgreSQL as the application datastore. WHM only supports standalone database setups and you can either have it deployed locally (default configuration) or remotely, by integrating with an external database server. The latter would be better if you want to have better load distribution, as WHM/cPanel handles a number of processes and applications like HTTP(S), FTP, DNS, MySQL and such.

In this blog post, we are going to show you how to integrate an external MySQL replication setup into WHM seamlessly, to improve the database availability and offload the WHM/cPanel hosting server. Hosting providers who run MySQL locally on the WHM server would know how demanding MySQL is in terms of resource utilization (depending on the number of accounts it hosts and the server specs).

…

During our work in support, we see this again and again: “I try to connect to MySQL and am getting a 1045 error”, and most times it comes accompanied with “…but I am sure my user and password are OK”.  So we decided it was worth showing other reasons this error may occur.

MySQL 1045 error Access Denied triggers in the following cases: 1) Connecting to wrong host:

[engineer@percona]# mysql -u root -psekret
mysql: [Warning] Using a password on the command line interface can be insecure. 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

If not specifying the host to connect (with -h flag), MySQL client will try to connect to the localhost instance while you may be trying to connect to another host/port instance.

Fix: Double check if you are trying to connect to localhost, or be sure to specify host and port if it’s not localhost:

 …

This tutorial is showing how manual fix of GTID inconsistency but it can be VERY RISKY to mess up the InnoDB Cluster.   It purely serves as a tutorial how to troubleshoot and identify potential issues.

Environment
MySQL InnoDB Cluster being setup on Single Machine with 3 Nodes
Node 1 : Port 3306
Node 2 : Port 3316
Node 3 : Port 3326





The InnoDB Cluster Member Status is as follows





Retrieving the GTID executed on each member "SELECT @@gtid_executed;"






The 3 sets of GTIDs are the same and the InnoDB Cluster of 3 nodes is running properly.

Let to simulate a breakdown of Server (Kill the Instance with PORT 3326)
1.  Change the persisted variables group_replication_start_on_boot = off first
  …

Most databases grow in size over time. The growth is not always fast enough to impact the performance of the database, but there are definitely cases where that happens. When it does, we often wonder what could be done to reduce that impact and how can we ensure smooth database operations when dealing with data on a large scale.

First of all, let’s try to define what does a “large data volume” mean? For MySQL or MariaDB it is uncompressed InnoDB. InnoDB works in a way that it strongly benefits from available memory - mainly the InnoDB buffer pool. As long as the data fits there, disk access is minimized to handling writes only - reads are served out of the memory. What happens when the data outgrows memory? More and more data has to be …

Introduction In this article, we are going to test the MySQL 8 implementation of custom SQL CHECK constraints. Although the CHECK clause is a standard SQL feature, prior to MySQL 8.0.16, the clause was parsed and ignored, so this functionality could only be emulated via BEFORE INSERT and UPDATE triggers. Custom SQL CHECK constraints As I explained in this article, custom SQL CHECK constraints are very useful to ensure non-nullability constraints for JPA entity subclass-specific attributes when using the SINGLE TABLE JPA inheritance strategy. To understand the problem, consider we have the... Read More

The post MySQL 8 support for custom SQL CHECK constraints appeared first on Vlad Mihalcea.

There could be various requirements at application end which need realtime slaves. Let me introduce a solution to a problem we used to face how can we monitor whether our slaves are real time or they are lagging in Milliseconds. Unfortunately there is no built in feature in MySQL to get Replication Lag in MilliSeconds.
Perhaps there is a tool provisioned in pt-toolkit named as pt-heartbeat. It generates heartbeat events on master and monitoring system can monitor time difference on slave to calculate lag.
How to deploy pt-heartbeat in your environment (Assuming OS as UBUNTU xx.xx):

Step 1: …

The CTO has issued the decree: “We are moving to the cloud!”  Great, so now what do we do? When it comes to migrating from on-premise to the cloud, there are many factors to consider and decisions that need to be made.  First (and probably most important) on that list: managed DBaaS or setup and manage our own infrastructure?

Type of Cloud

Managed DBaaS options are great but come with some limitations.  The main two questions that should be considered here are around staff/experience and current architecture/database design.  In cases where there is a very limited database (or operations) team, a DBaaS is a great choice. Much of the operational infrastructure is already in place with general best practices in place.  However, a big tradeoff comes around flexibility. In cases where the current infrastructure is complex (for reasons right or wrong), a DBaaS is definitely the wrong choice as you will be …

We bring you the best MySQL tutorial to learn all Basic to Advanced concepts step by step. This post covers all MySQL building blocks such as DDL, DML, DCL, and TCL. DDLs are commands to create, drop, alter, truncate, rename, comment databases, and tables. Also, DMLs are select, insert, update, delete commands to manipulate data. Moreover, you’ll also find the DCLs on this page such as grant and invoke to manage rights and permissions. After that, there are TCLs to control the transactions. So, let’s start by telling you that the father of MySQL is Michael Widenius, who named it

The post MySQL Tutorial – Learn Step by Step appeared first on Learn Programming and Software Testing.

What are things you must consider before using Galera Cluster Streaming Replication ? 

In Streaming Replication, the node breaks the transaction into fragments, then certifies and replicates them on the slaves while the transaction is still in progress. Once certified, the fragment can no longer be aborted by conflicting transactions. Additionally, Streaming Replication allows the node to process transaction write-sets greater than 2Gb. So, How do you decide whether go with Galera Cluster Streaming Replication or not ? We caution our customers before choosing Galera Cluster Streaming Replication considering several limitations and costs attached to it (explained below the limitations of Galera Cluster Streaming Replication in this post). Even you have decided to proceed with Galera Cluster Streaming Replication, We recommend you to enable it only for a session-level and then only on specific transactions.

…