One of the things we have been working on for MySQL 8, is speeding up scans that read multiple records from a table or an index. Such scans benefit from reading the records in batches, so that they don’t get the overhead of latching data pages and navigating in the B-tree for every single record that is read.…

Starting with MySQL release 8.0.0, the source code is now commented using the Doxygen documentation generation tool.

This project is long overdue … it has been a very well-worn issue, or an ever recurring wish, for the last 10 years.

Well, not any more.…

This blog post discusses how you can protect your e-commerce database from a high traffic disaster.

Databases power today’s e-commerce. Whether it’s listing items on your site, contacting your distributor for inventory, tracking shipments, payments, or customer data, your database must be up, running, tuned and available for your business to be successful.

There is no time that this is more important than high-volume traffic days. There are specific events that occur throughout the year (such as Black Friday, Cyber Monday, or Singles Day) that you know are going to put extra strain on your database environment. But these are the specific times that your database can’t go down – these are the days that can make or break your year!

So what can you do to guarantee that your database environment is up to the …

This is an unstable release, please don’t use in production.

It was rumored that the new MySQL version would be 8 and not as 5.8 as a lot of people thought, and it appears the rumors were true.

Below are some of the features that caught my eye at first glance:

Roles

Although password expiration was implemented 5.7, the newer version bring a set of collective privileges as a Role. No need to have to copy paste that massive GRANT command you had when creating new users.

UTF-8 as default Charset

This is not yet the default charset coming with the server, but utf8mb4 will be the main charset instead of latin1, and the default collation will change from latin1_swedish_ci to utf8mb4_800_ci_ai. The plan is to do that before General Availability.

Invisible Indexes

Giving an …

How InnoDB initializes AUTO_INCREMENT counters is actually not a bug, but a documented mechanism. There were some complaints and even people who lost data over this. To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column. SELECT MAX(ai_col) FROM table_name FOR

On the MySQL Bugs website there are some tide stats available. These show rate of bug creation.

I've put them in a graph:

I made these with this IPython Notebook. There are more detailed graphs per version in the notebook.

Update: The version in the notebook now uses the same range for the Y axis and has a marker for the GA dates of each release.

A question which would come sometimes to mind when starting with MySQL is whether I should use DATETIME or TIMESTAMP data type since both appear to store same date and time component.


Similarities between datetime and timestamp:
1. Values contain both date and time parts.
2. Format of retrieval and display is "YYYY-MM-DD HH:MM:SS".
3. Can include a trailing fractional seconds part in up to microseconds (6 digits) precision.
4. With the fractional part included, the format for these values is "YYYY-MM-DD HH:MM:SS[.fraction]".
5. Both the TIMESTAMP and (as of MySQL 5.6.5) DATETIME offer automatic initialization and updating to the current …

In this post, we’ll look at MySQL CDC, streaming binary logs and asynchronous triggers.

What is Change Data Capture and why do we need it?

Change Data Capture (CDC) tracks data changes (usually close to realtime). In MySQL, the easiest and probably most efficient way to track data changes is to use binary logs. However, other approaches exist. For example:

• General log or Audit Log Plugin (which logs all queries, not just the changes)
• MySQL triggers (not recommended, as it can slow down the application — more below)

One of the first implementations of CDC for …

This blog post discusses ProxySQL and MHA integration, and how they work together.

MHA (Master High Availability Manager and tools for MySQL) is almost fully integrated with the ProxySQL process. This means you can count on the MHA standard feature to manage failover, and ProxySQL to manage the traffic and shift from one server to another.

This is one of the main differences between MHA and VIP, and MHA and ProxySQL: with MHA/ProxySQL, there is no need to move IPs or re-define DNS.

The following is an example of an MHA configuration file for use with ProxySQL:

server default]
    user=mha
    password=mha
    ssh_user=root
    repl_password=replica
    manager_log=/tmp/mha.log
    manager_workdir=/tmp
    remote_workdir=/tmp
    master_binlog_dir=/opt/mysql_instances/mha1/logs …

Coinciding with the new native data dictionary in MySQL 8.0, we have made a number of useful enhancements to our INFORMATION_SCHEMA subsystem design in MySQL 8.0. In this post I will first go through our legacy implementation as it has stood since MySQL 5.1, and then cover what’s changed.…