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.…

This blog post explains benefits, features and limitations of the improved semi-sync replication in MySQL 5.7

The post Improved Semi-Sync Replication in MySQL 5.7 appeared first on Datavail.

Invisible indexes are a new feature in MySQL 8.0 that provide the ability to mark an index as unavailable for use by the Optimizer. This means that the index will still be maintained and kept up-to-date as data is modified, but no queries will be permitted to make use of the index (even if the query uses a FORCE INDEX hint).…

In this blog post, I will discuss the CVE-2016-6662 vulnerability, how to tell if it affects you, and how to prevent the vulnerability from affecting you if you have an older version of MySQL.

I’ll also list which MySQL versions include the vulnerability fixes.

As we announced in a previous post, there are certain scenarios in Percona Server (and MySQL) that can allow a remote root code execution (CVE-2016-6662).

Vulnerability approach

The website legalhackers.com contains the full, current explanation of the …

MySQL has just released MySQL 8.0 DR (and yes, DR stands for Don’t Run-it-in-production-yet) so let’s jump right in and take a look at the hottest new features coming in this new release:

1. Persistent runtime configuration changes. Love it. From now on we’ll be able to use SET PERSIST innodb_buffer_pool_size = X; instead of SET GLOBAL innodb_buffer_pool_size = X; for the runtime changes to persist during a restart. It may not make much sense if you’re using a modern database that doesn’t even have a configuration file, but for us who lived with MySQL for over 20 years, this is huge!

How does it work? In a nutshell, these changes are saved in mysqld-auto.cnf file in MySQL data directory.

2. MySQL privilege tables are now InnoDB. I think this was …

MySQL 8.0.0 was released today. It has been some time in the making, shrouded in a veil of secrecy for over one year. We knew, from listening to the gossip and looking at the few available previews, some of what was going to bring. So, for the observant users, its main features may not come as a surprise. For the rest of you, here's a quick roundup:

Notable features

• No MyISAM tables anymore! The grant tables are now InnoDB, meaning that grant operations are now atomic.
• A real data dictionary. This change is less visible than the previous one. The data dictionary tables are hidden and only a subset of the data is available through information_schema views. The reason for the hidden tables is to allow a stable interface through several versions. I am …

The MySQL Development team is happy to announce our 8.0.0 development milestone release (DMR), now available for download at dev.mysql.com.  The source code is available at GitHub. You can find the full list of changes and bug fixes in the 8.0.0 Release Notes. …