Create a MySQL InnoDB Cluster using MySQL 8.0 has always been very easy. Certainly thanks to MySQL Shell and server enhancements like SET PERSIST and RESTART statement (see this post).

The most complicated part to deal with was the existing and none existing data. In fact GTID sets must be compatible.

Let me explain that with some examples:

Example 1 – empty servers

If you have empty servers with GTID enabled, manually creating credentials to connect to each MySQL instances will generate GTIDs that will prevent nodes to …

Mydbops continues with its commitment to the open source community comprising of Database Administrators. After the successful conduct of three meet-ups in the past, we are now stepping ahead on to the 4th edition of Mydbops Database meet-up.

First time, we are going to the new venue, thanks to the kind people of Zenefits Technologies India Pvt.Ltd., It is a real pleasure to be hosted by the like-minded organisation in the city of Bangalore, scheduled on Saturday, 3rd of August, 2019 at Zenefits Office.

In the past three editions, the focus was on the latest and hands-on topics by the selected speakers from the DBA World. This focus gets sharper this time as well. The topics for the 4 th Mydbops Database Meetup are:

InnoDB scalability improvements in MySQL 8.0 – Mr. Karthik P R, Founder / CEO of Mydbops, a MIT Masters …

Two important MySQL 8.0 Shell Utilities we use regularly are the upgrade checker utility and  JSON import utility . The upgrade checker utility simplifies the pre-upgrade compatibility audit (whether MySQL server instances are ready for upgrade), We have blogged about MySQL upgrade checker utility here . The upgrade checker utility does not support checking MySQL Server instances at a version earlier than MySQL 5.7. From MySQL Shell 8.0.16, the upgrade checker utility can check the configuration file (my.cnf or my.ini) for the server instance. The utility checks for any system variables that are defined in the configuration file but have been removed in the target MySQL Server release, and also for any system variables that are …

What is new with MySQL 8.0 Delayed Replication ?

Delayed Replication – You can deliberately execute transactions later than the master by a specific duration of time , Why you do that and for what ? Consider this, Accidentally someone did a wrong UPDATE / DELETE in the master and the transaction is committed, Now how can DBA rollback the database system to the last known good condition ? This is when we benefit from MySQL delayed slave replication investment. The default replication delay in MySQL is “0” seconds, To delay the slave by N seconds use the CHANGE MASTER TO MASTER_DELAY = N, The transactions received from the master is not executed until N seconds later than it’s commit on the immediate master. We have blogged here how to setup …

MySQL has introduced the concept of functional index in MySQL 8.0.13. It is one of the much needed feature for query optimisation , we have seen about histogram in my last blog. Let us explore the functional index and its use cases.

For the below explanation, I have used a production scenario which has 16 core cpu, 32GB RAM and with MySQL version 8.0.16(Latest at the time of writing).

MySQL do support indexing on columns or prefixes of column values (length).

Example: 

mysql>show create table app_user\G
*************************** 1. row ***************************
Table: app_user
Create Table: CREATE TABLE `app_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ad_id` int(11) DEFAULT NULL,
`source` varchar(32) DEFAULT NULL,
`medium` varchar(32) DEFAULT NULL,
`campaign` varchar(32) DEFAULT NULL,
`timestamp` …

Quickly configure replication using DBdeployer [SandBox]
We might have different scenarios when we need a quick setup of replication either between the same version of MySQL (Like 8.0 --> 8.0) or between the different version of MySQL (Like 5.7 --> 8.0) to perform some testings. 

Here in this blog post, I will explain how we can create our replication lab setup quickly using the virtual machine and DBdeployer tool. 

Let's see, how to create replication between the same version and different version of MySQL using DBdeployer step by step. 

Create CentOS VM Please find my …

MySQL supports different types of Indexes. They depend on the storage engine and on the type of data. This is the list of supported indexes:

• B-trees (the most common)
• R-trees (for spatial data)
• Hashes (for Memory engine)
• Inverted (for InnoDB Full Text)

Best Practices

My recommendation are valid for InnoDB storage engine. I won’t talk about MyISAM. There are some best practices to follow when designing your tables. These are the 3 most important:

MySQL 8.0 introduces many new features. We will have a look at the exciting histogram  feature in MySQL 8.0

Histogram:

What is Histogram?

In General, a histogram is an accurate representation of the distribution of numerical data. In MySQL, It will be useful to collect data distribution for a specific column.

What problem it solves?

In general DB Optimizer gives us the best execution plan, But the stats make the execution plan better and better .The data distribution of values in columns can make good impact in optimiser in case of column with less distinct values.

We will see an example of how it helps optimizer in some cases. 

I have used a production case. MySQL version is 8.0.15 installed in ubuntu 18.04 (32GB RAM,8 core) with optimal configuration. Let us try to optimise a …

MySQL 8.0 : Error Logging
Here I would like to explain, How MySQL 8.0 server can be configured to log diagnosis messages inside the error log file. 
Generally, the error log contains:

• A record for mysqld service startup and shutdown.
• Diagnostic messages such as errors, warnings, and notes during mysqld server startup, running or shut down. 
• A stack trace if mysqld exits abnormally. 
• mysqld_safe writes once it finds mysqld exists abnormally and mysqld_safe invoke/start mysqld. 

Error Log Component Configuration MySQL 8.0 uses the MySQL error log component architecture that performs log …

Do you know that it’s possible to get information from the CPUs of your MySQL Server from SQL ?

If you enable the status for the  INNODB_METRICS table in INFORMATION_SCHEMA, you will be able to query CPU information.

First, check if those status are enabled:

MySQL> SELECT name, subsystem, status 
FROM INFORMATION_SCHEMA.INNODB_METRICS where NAME like 'cpu%';
+---------------+-----------+----------+
| name          | subsystem | status   |
+---------------+-----------+----------+
| cpu_utime_abs | cpu       | disabled |
| cpu_stime_abs | cpu       | disabled |
| cpu_utime_pct | cpu       | disabled |
| cpu_stime_pct | cpu       | disabled |
| cpu_n         | cpu       | disabled |
+---------------+-----------+----------+
5 rows in set (0.00 sec)