Sommerferien-Zeit – für all die Daheimgebliebenen dürfte es jetzt hoffentlich etwas ruhiger zu und her gehen. Zeit für eine Weiterbildung? Es bleibt nicht mehr viel Zeit, das Jahres-Schulungs-Budget aufzubrauchen!

Vom 19. bis 23. August führt FromDual wieder einmal die Schulung MariaDB und MySQL für Fortgeschrittene in Köln durch. Siehe auch unsere Schulungstermine.

Es hat noch Plätze frei! Anmelden kann man sich direkt bei unserem Schulungs-Partner, der GFU Cyrus AG.

Diese MariaDB/MySQL Weiterbildung richtet sich …

Sometimes, the MySQL Optimizer chooses a wrong plan, and a query that should execute in less than 0.1 second ends-up running for 12 minutes !  This is not a new problem: bugs about this can be traced back to 2014, and a blog post on this subject was published in 2015.  But even if this is old news, because this problem recently came yet again to my attention, and because this is still not fixed in MySQL 5.7 and 8.0, this is a subject worth writing about.

The MySQL Optimizer

Before looking at the problematic query, we have to say a few words about the optimizer.  The Query Optimizer is the part of query execution that chooses the query plan.  A Query Execution Plan is the way MySQL chooses to execute a specific query.  It …

The usage of this clone plugin is so much helpful to bring new MySQL instance to live.

Usage can go with New Replica (as in MySQL Replication), or we can easily create New Node in MySQL InnoDB Cluster.  or we can provision a New MySQL Instance for Testing or Troubleshooting.  or we need the New Instance with data for snapshot query.

Many useful links have been posted in the recent week(s).   

Please check the URL with the details.   This CLONE feature is the **NEW** super star add-on to the MySQL today.

Usage :

https://lefred.be/content/mysql-innodb-cluster-from-scratch-even-more-easy-since-8-0-17/

https://lefred.be/content/create-an-asynchronous-mysql-replica-in-5-minutes/

https://mysqlandmore.blogspot.com/2019/07/mysql-8017-release-and-clone-plugin.html


…

Very good, practical, and precise info! However, you could use some code prettifier to highlight the commands. Currently, there seems no delimitation between the text and code.

LikeLike

This tutorial explains MySQL ORDER BY clause and its usage with both the ASC and DESC options. We’ll describe this MySQL statement with the help of simple examples. 1. ORDER BY Clause Syntax 2. ORDER BY W/O ASC or DESC 3. ORDER BY DESC 4. ORDER BY Relative Position 5. ORDER BY Two Fields ASC & DESC Let’s now go through each of the section one by one. MySQL ORDER BY Clause The MySQL SELECT command doesn’t return a sorted result set by default. Hence, to sort the output, you have to append the ORDER BY clause in the SELECT

The post MySQL ORDER BY with Simple Examples appeared first on Learn Programming and Software Testing.

MariaDB 10.4 has being declared GA, and few presentations on Galera4 in the last conferences were hold.

So, I thought, it is time to give it a try and see what is going on.

It is not a secret that I consider the Codership guys my heroes, and that I have push for Galera as solid and very flexible HA solution, for many years.

Given that my first comment is that it was a shame to have Galera4 available only in MariaDB, I would have preferred to test the MySQL vanilla version for many reasons, but mainly because the MySQL/Oracle is and remain the official and main line of the MySQL software, you like it or not, and as such I want to see how the Galera4 behave with that. Anyhow Codership state that the other versions will be out AFTER the summer, and I hope this will be true.

To test the new version given I do not have the vanilla MySQL, I decide to use the other distribution coming from Percona. At the end the test …

This tutorial explains all MySQL data types, their characteristics, and min, max as well as possible default values. We’ll describe their usages so that you can use them efficiently for creating schemas and tables. A MySQL table can have one or more fields with specific data types such as a string or date. However, there are more available in MySQL to ease up your job of collecting and storing data. It is also crucial that you understand which data type should you use and when. Here are some standard goals that define them what do they represent: 1. The data,

The post MySQL Data Types Explained appeared first on Learn Programming and Software Testing.

This tutorial explains MySQL TIMESTAMP and TIMESTAMP field characteristics such as automated initialization and updating. We’ll describe their usages with the help of simple examples. 1. TIMESTAMP Syntax 2. TIMESTAMP Simple Examples 3. Set Timezone and Use Timestamp 4. Auto Init and Update Timestamp Let’s now go through each of the section one by one. MySQL TIMESTAMP The MySQL TIMESTAMP is a transient data type that contains a mixture of date and time. It is exactly 19 characters long. The structure of a TIMESTAMP field is as follows: Syntax # MySQL Timestamp YYYY-MM-DD HH:MM:SS The TIMESTAMP value shows in UTC

The post MySQL TIMESTAMP with Simple Examples appeared first on Learn Programming and Software Testing.

Optimal Indexing for Performance – How to plan Index Ops. ? 

An index or database index is a data structure which is used to quickly locate and access the data in a database table. Indexes are created on columns which will be the Search key that contains a copy of the primary key or candidate key of the table. These values are stored in sorted order so that the corresponding data can be accessed quickly (Note that the data may or may not be stored in sorted order). They are also Data Reference Pointers holding the address of the disk block where that particular key value can be found. Indexing in database systems is similar to what we see in books. There are complex design trade-offs involving lookup performance, index size, and index-update performance. Many index designs exhibit logarithmic (O(log(N))) lookup performance and in some applications it is possible to achieve flat (O(1)) performance. Indices can …

You may have noticed that in many of my demos, I disable firewall and SELinux (I even use --initialize-insecure sometimes ). This is just to make things easier… But in fact enabling iptables and SELinux are not complicated.

Firewall

These examples are compatible with Oracle Linux, RedHat and CentOS. If you use another distro, the principle is the same.

For the firewall, we need first to allow incoming traffic to MySQL and MySQL X ports: 3306 and 33060:

# firewall-cmd --zone=public --add-port=3306/tcp --permanent
# firewall-cmd --zone=public --add-port=33060/tcp --permanent

If you don’t plan to restart the firewall, you just need to run the same commands without --permanent to make then immediately active.

Then we need to allow the Group Replication’s communication port. This is usually 33061 but it can be configured in …