At times, when playing with different InnoDB Clusters for testing (I usually deploy all Group Replication instances on the same host on different ports) I find myself stopping the group and doing operations on every instance (e.g. a static reconfiguration). Or I may need to shutdown all instances at once. Scripting is the usual approach, but in addition, MySQL Shell offers a very nice (and powerful) way to integrate custom scripts into the Shell itself to manage an InnoDB Cluster. This is the purpose of MySQL Shell extensions, to create new custom reports and functions and have the flexibility to manage one or more instances at once. I found particularly practical the new plugin feature, introduced in MySQL Shell 8.0.17, that can aggregate reports and functions under the same umbrella: the …
MySQL Support Blogs
This is an aggregated feed of various blogs of Oracle support staff who work and support MySQL customers and users. There is an RSS feed to which you can subscribe.
When data and concurrency grow, together with queries complexity,
a standard configuration does not fit anymore. This content walks
through the ways MySQL has to adapt to an ever-increasing amount
of data in context of tuning, under the heading
—Scaling up through partitioning and how partitioning can help manage/archive data
—How to relocate binlog, undo, redo, tablespaces, or disk temporary tables and more on different mount points to take advantage of multiple storages and tackle I/O bottlenecks
—All improvements to parallel slave replication
—A quick OS approach, to verify swapping and affinity tuning take the most out of the machine.
All of this with different approaches to monitor the instance to spot what parts of the system and what queries need to be considered, mainly using:
If you need to do bulk importing into InnoDB Cluster, it is
certainly possible to do so by using any of:
- mysqldump logical dump
- tab separated dump, exported with SELECT..INTO OUTFILE statement or by using mysqldump with --tab option. And import with LOAD DATA..INFILE
Unfortunately both imports will add load to instances and to channels interconnecting instances: data imported on the primary instance needs to be replicated to the rest of instances. And the bigger the data to import, the higher the load (and this could end up affecting performance). The import operation could be batched to reduce load, and Group Replication allows at least to throttle workload with …
Oracle announced this week at Oracle OpenWorld that it has introduced a new cloud offer called Oracle Cloud Free Tier. As the name suggest, it allows you to run a few limited instances in Oracle Cloud for free. I will in this blog show how you can use the free tier to setup a MySQL test instance.
MySQL Connector/Python 8 made the C Extension the default for the
platform/Python version combinations supporting it. One thing
that was missing from the C Extension implementation (unless you
_mysql_connector module) was support for
prepared statements. That has been taken care of with the release
of version 8.0.17.
The two main advantages of using prepared statements are security and performance. The security comes in as you can pass query parameters and have them applied server-side, so you are sure they are quoted and escaped correctly taking the data type into consideration. The performance benefit happens, when you execute the same query (except for the parameters) several times as MySQL will prepare it …
sys schema was created to make it easier to use
the Performance Schema. This included several functions, for
example to convert the picoseconds used by the Performance Schema
into human readable strings. In MySQL 8.0.16, three of these
functions have been implemented as native functions in MySQL
MySQL Server has since version 5.6 supported connection attributes for the clients. This has allowed a client to provide information such as which program or connector the client is, the client version, the license, etc. The database administrator can use this information for example to verify whether all clients have been upgraded, which client is executing a particular query, and so forth.
In MySQL 8.0.16 this feature has been included for the X DevAPI in the MySQL connectors as well, including MySQL Connector/Python which I will cover in this blog. First though, let’s take a look at how the attributes are exposed in MySQL Server.
MySQL 8.0.16 was released last week and includes many great
features including support for
CHECK constraints and
upgrades without the need of
mysql_upgrade. As usual
there are also several contributions from the community. These
are the ones, I would like to highlight in this blog to say
“thank you for the contributions”.
In my blog yesterday, I wrote about the new
reporting framework in MySQL Shell. It is part of the 8.0.16
release. I also noted that it includes the possibility to create
your own custom reports and use those with the
\watch commands. This blog will explore how you
can create a report and register it, so it automatically is
available when you start MySQL Shell.
One disadvantage of …
MySQL stores several files on disk. Even in MySQL 8 where the data dictionary is stored in InnoDB tables, there are still all the tablespace files. Different file system behave differently, and one particular challenge is case sensitivity. On Microsoft Windows, the case does not matter, on Linux the case is important, and on macOS the case of the file names is preserved but the operating system by default makes it look like it is case insensitive.
Which convention that is the correct depends on your personal
preference and use case. Between case sensitivity and case
insensitivity, it basically boils down to whether
should be the same identifier or three different ones. …
When you write stored procedures in MySQL, you sometimes need to
generate queries on the fly, for example as you process the
result of another query. This is supported using prepared statements. This blog explores how
you can take advantage of the
sys schema to simplify
the use of dynamic queries.
Executing a query using the sys.execute_prepared_stmt() …
A great way to install MySQL when you need to do quick tests is to use a sandbox tool. This allows you to perform all the installation steps with a single command making the whole process very simple, and it allows for automation of the test. Giuseppe Maxia (also known as the Data Charmer, @datacharmer on Twitter) has for many years maintained sandbox tools for MySQL, first with MySQL Sandbox and now with dbdeployer.
One of the most recent features of dbdeployer is the support for MySQL NDB Cluster. In this blog, I will take this feature and test it. First, I will …
I few months ago, I wrote about using the Django framework with MySQL 8. There are also other Python frameworks that are worth considering. In this blog, I will look at using SQLAlchemy with MySQL 8.
In order for you to be able to use MySQL 8 with SQLAlchemy, you need three pieces of software: MySQL Server, MySQL Connector/Python, and SQLAlchemy. I will go through the installations, then I will look at a code example.
MySQL changed the timestamp format in the log files in MySQL 5.7.
Since then, I have a few times seen questions about the new
format, and how to change the time zone that is used. Latest in a
comment to my blog about
8.0.14, where the question was what T and Z in the timestamp
2019-01-31T07:24:06.100447Z) means. In
this blog, I will discuss the timestamp format and show you how
you can change the time zone used.
The X DevAPI allows you to work with JSON documents and SQL
tables at the same time. Furthermore, the CRUD style API is more
intuitive than SQL statements for some programmers. Either way,
the X DevAPI allows you to mix JSON documents, SQL tables, CRUD
methods, and SQL statements to give you the best of all worlds.
In MySQL Connector/Python, the X DevAPI is implemented in the
This blog will look at how MySQL Connector/Python handles
expressions, and how you in version 8.0.14 and later need to use
mysqlx.expr() method to explicitly
When things go horrible wrong and a process crashes, one of the most powerful things to investigate the cause of the crash is a core dump. As the amount of memory allocated to processes such as MySQL has increased – in some cases approaching 1TiB of memory – enabling core dumps can cause problems of their own. MySQL Server 8.0.14 and later supports an option to reduce the size of the core dump which will be discussed in this blog.
Typically the largest single user of memory for MySQL is the InnoDB buffer pool. This is used to cache the data and indexes for tables using the InnoDB storage engine (the default). It is rarely important to know what is stored in the InnoDB buffer pool when investigating a crash, so a good …
Content reproduced on this page is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.