In my previous post, we saw the usage of MySQL Group Replication (MGR) in single-primary mode. We know that Oracle does not recommends using MGR in multi-primary mode, but there is so much in the documentation and in presentations about MGR behavior in multi-primary, that I feel I should really give it a try, and especially compare this technology with the already existing multiple master solution introduced in 5.7: multi-source replication.
In this blog, we’ll look at how to manually build Percona Server for MySQL RPM packages. Several customers and other people from the open source community have asked us how they could make their own Percona Server for MySQL RPM binaries from scratch. This request is often made by companies that want to add custom patches to our release. To do this, you need to make some modifications to the percona-server.spec file in the source tree, and some preparation is necessary.
In this blog post, we’ll look at how to set up ProxySQL for high availability. During the last few months, we’ve had a lot of opportunities to present and discuss a very powerful tool that will become more and more used in the architectures supporting MySQL: ProxySQL.
MySQL user management mostly done by database administrator and usually done via command line. But did you know that there is another way to do this task via GUI. On this tutorial I am going to show you how to add new MySQL/MariaDb using MySQL Workbench. MySQL Workbench is a powerful tool to manage MySQL server. We can easily connect and manage our server that installed locally or on a remote server.
In this blog post, I’ll discuss how to use the InnoDB buffer pool pre-load feature in MySQL 5.7 Starting MySQL 5.6, you can configure MySQL to save the contents of your InnoDB buffer pool and load it on startup. Starting in MySQL 5.7, this is the default behavior. Without any special effort, MySQL saves and restores a portion of buffer pool in the default configuration. We made a similar feature available in Percona Server 5.5 – so the concept has been around for quite a while.
MySQL Test Run or MTR for short, is a MySQL test program. It was developed to ensure that the MySQL server’s operation is as expected whether it be in terms of testing the functionality of new features or integrity of the old. There are suites of existing tests which are to be run whenever a change is introduced in any of the components of the server to see if there are any side effects as a direct result of the change.
This blog post discusses how better MySQL 8.0 benchmarks can improve MySQL in general. Like many in MySQL community, I’m very excited about what MySQL 8.0 offers. There are a lot of great features and architecture improvements. Also like many in the MySQL community, I would like to see MySQL 8.0 perform better. Better performance is what we always want (and expect) from new database software releases.
I was going through the MySQL Slow Query logs, as I often do, and came across a query that was consistently doing full table scans. If you know anything about relational database systems, the phrase, "full table scan," probably makes you squirm a little bit - they're generally bad for performance. So, I started investigating the problem only to be completely stumped. The query looked like it should have been using an index.
Clients with an existing application sometimes ask me to fix bugs, improve efficiency by speeding up the application, or add a new feature to some existing software. The first stage of this is researching the original code – so-called reverse engineering. With SQL databases, it is not always immediately obvious which SQL queries MySQL executed – especially if these queries were generated by a framework or some kind of external library.
Using tools like HeidiSQL for Windows, Sequel Pro for macOS, or the cross-platform MySQL Workbench, you can connect securely to your database over SSH, bypassing those cumbersome and potentially insecure steps. This brief tutorial will show you how to connect to a remote database using MySQL Workbench.
This blog post elaborates on the architecture and design of the transactional data dictionary that will be part of MySQL 8.0. Some descriptions of architecture will be implemented in later versions. See MySQL 8.0 Data Dictionary: Background and motivation.
In this blog, we’ll discuss things to watch out for during a MySQL downgrade. Previously, I wrote the blog MySQL upgrade best practices. Besides upgrading your MySQL version, sometimes you need to downgrade. When it comes to downgrading MySQL, there are two types of downgrade methods supported:
The future MySQL 8.0 will (probably) have a great new feature: support for index sort order on disk (i.e., indexes can be physically sorted in descending order). In the MySQL 8.0 Labs release (new optimizer preview), when you create an index you can specify the order “asc” or “desc”, and it will be supported (for B-Tree indexes). That can be especially helpful for queries like “SELECT … ORDER BY event_date DESC, name ASC LIMIT 10″ (ORDER BY clause with ASC and DESC sort).
So it is time to give some justifications for these constraints, which come from the SQL standard (except the exclusion of ORDER BY, LIMIT and DISTINCT, which is MySQL-specific, though also present in several well-known RDBMSs). Consider this example, producing numbers from 1 to 10:
By default, MySQL 5.7 is much “stricter” than older versions of MySQL. That can make your application fail. To temporarily fix this, change the SQL_MODE to NO_ENGINE_SUBSTITUTION (same as in MySQL 5.6):
In this second part of MySQL/MariaDB beginner series, we will explain how to limit the number of rows returned by a SELECT query, and how to order the result set based on a given condition. Additionally, we will learn how to group the records and perform basic mathematical manipulation on numeric fields. All of this will help us to create a SQL script that we can use to produce useful reports.
Starting with the 8.0 optimizer labs release the MySQL server now supports descending indexes. As I will detail in this post, this new feature can be used to eliminate the need for sorting results, and lead to performance improvements in a number of queries. Up until this release, all indexes were created in ascending order. While the syntax itself is parsed, the meta data is not preserved. For example in MySQL 5.7:
To err is human, or so the saying goes. Unfortunately, in the world of IT, innocuous mistakes made early on can lead to really daunting problems down the road. While you can never eliminate human error or bad decisions, there are steps that you can take to minimize the likelihood of finding yourself in a pickle due to a hasty decision arrived at in the spur of the moment or a mistake made early on in design. In today’s article, we’ll go over a few of the most common DBA mistakes to avoid. Some of these relate specifically to MySQL, while others may be applicable to any RDBMS.
In this blog post, I’ll look at how to use the loose_ option prefix in my.cnf in MySQL. mysqld throws errors at startup – and refuses to start up – if a non-existent options are defined in the my.cnf file.
What makes the data dictionary so interesting, despite its scarce visibility, is the effect that it has on performance. Up to MySQL 5.7, searching the information_schema was an onerous operation, potentially crippling the system. In MySQL 8.0, the same operations are 100 times faster. This would be reason enough to be excited, as I know many users who have had terrible problems with the inefficiency of information_schema.