Why migrating to MySQL 8.0 ?

MySQL 8.0 brings a lot of new features. These features make MySQL database much more secure (like new authentication, secure password policies and management, …) and fault tolerant (new data dictionary), more powerful (new redo log design, less contention, extreme scale out of InnoDB, …), better operation management (SQL Roles, instant add columns), many (but really many!) replication enhancements and native group replication… and finally many cool stuff like the new Document Store, the new MySQL Shell and MySQL InnoDB Cluster that you should already know if you follow this blog (see these TOP 10 for features for developers and this TOP 10 for DBAs & OPS).

Not anymore a drop in replacement !

We …

Before the implementation of WL#11652 in MySQL 8.0.13 , the MySQL server listened to incoming connection requests either on one or all configured network interfaces.

However, in case the underlying platform supports several network interfaces it might be useful to allocate more than one network interface for specific use, and therefore allow specifying bind-address for several network interfaces.…

Facebook Twitter Google+ LinkedIn

What's a good natural key? This is a very difficult question for most entities when you design your schema. In some rare cases, there seems to be an "obvious" candidate, such as a variety of ISO standards, including: ISO 639 language codesISO 3166 country codesISO 4217 currency codes But even in those cases, there might … Continue reading The Cost of Useless Surrogate Keys in Relationship Tables →

It is very important for every DBA’s to backup their data frequently so that they can recover data if any problems occur such as Hardware failure, System crashes, Human mistakes. There are several ways to backup MySQL data.

They are

1) Physical Backup

2) Logical Backup

Physical Backup:

              It is also called Raw Backup. It copies the directories and its files that has database contents.

Logical Backup:

            It is also called Text Backup. It converts all the available rows into single/multiple inserts and also contains create statements to create databases, tables, triggers and so on,

This blog is to discuss logical backups in MySQL and its available utilities.

List of utilities available for logical …

On this article, I will show you how to migrate your wordpress database from the MariaDB on CentOS to the real MySQL.

Why migrating to MySQL 8.0 ?

MySQL 8.0 brings a lot of new features. These features make MySQL database much more secure (like new authentication, secure password policies and management, …) and fault tolerant (new data dictionary), more powerful (new redo log design, less contention, extreme scale out of InnoDB, …), better operation management (SQL Roles, instant add columns), many (but really many!) replication enhancements and native group replication… and finally many cool stuff like the new Document Store, the new MySQL Shell and MySQL InnoDB Cluster that you should already know if you follow this …

When designing a highly-available MySQL architecture, having a proxy to route the traffic to the appropriate instances is crucial to achieve transparent (or almost) failovers/switchovers. ProxySQL, a popular open source, SQL-level proxy is a great choice for this, and even though an experimental clustering feature is available, we could follow a simpler active-passive approach too, based on Common Address Redundancy Protocol (CARP), which will also provide a highly available IP for the applications to connect.

Solution overview

As mentioned before, this setup relies on a shared IP configured through CARP on the two hosts that will be running ProxySQL. CARP configuration is outside the scope of this post but the steps are on available on the FreeBSD documentation.

We then need to make sure all ProxySQL configuration changes performed on the …

If you are using Galera replication, you know that schema changes may be a serious problem. With its current implementation, there is no way even a simple ALTER will be unobtrusive for live production traffic. It is a fact that with the default TOI alter method, Percona XtraDB Cluster (PXC) cluster suspends writes in order to execute the ALTER in the same order on all nodes.

For factual data structure changes, we have to adapt to the limitations, and either plan for a maintenance window, or use pt-online-schema-change, where interruptions should be very short. I suggest you be extra careful here, as normally you …

We already have seen how to gather overall MySQL server diagnostics information and how to trace a specific thread activity. Let’s go to more granular level this time ! What about tracing a specific statement. Has MySQL a built-in way to do that ?

The “ps_trace_statement_digest” introduced in MySQL 5.7 allow tracing a specific statement digest (As explained in the doc : The digesting process converts each SQL statement to normalized form (the statement digest) and computes a SHA-256 hash value (the digest hash value) from the normalized result.Normalization permits statements that are …

We’re in the process of upgrading our MySQL databases from v5.7 to v8.0; since one of the differences in v8.0 is that the default encoding changed from utf8 to utf8mb4, and we had the conversion in plan anyway, we anticipated it and performed it as preliminary step for the upgrade.

This post describes in depth the overall experience, including tooling and pitfalls, and related subjects.

Contents:

• Introduction
• Migration plan: overview and considerations
  • …

Real-time web/mobile application is becoming popular day by day. Services like Firebase and Pusher provides API’s and Services to develop effective real-time notification system for your mobile and web apps.

We are not going to use these Services in this post; instead, we will develop an application that pops up notification on a particular event – Say a new comment added on Post. For notification, we will use Chrome desktop notification and for real-time communication – Socket.io.

I have already covered desktop notification here and Socket.io tutorial here.

DOWNLOAD Prerequisites :

Level of the …