The latest from the MySQL community

Percona is glad to announce the release of Percona Server 5.6.30-76.3 on May 25, 2016. Download the latest version from the Percona web site or the Percona Software Repositories.

Based on MySQL 5.6.30, including all the bug fixes in it, Percona Server 5.6.30-76.3 is the current GA release in the Percona Server 5.6 series. Percona Server is open-source and free …

Thanks to everyone who joined us yesterday for our ClusterControl 1.3 release webinar!

Johan Andersson, CTO at Severalnines and creator of ClusterControl, walked us through the latest features of the 1.3 release and demonstrated them live as well. In addition to an overview of ClusterControl’s deployment, monitoring, management and scaling functionalities for MySQL, MariaDB, Percona Server, MongoDB and PostgreSQL, Johan focussed our attention on new features around key management, operational reports and more.

One feature-set that triggered particular interest in yesterday’s audience was the automated deployment of a production setup of NDB / MySQL Cluster: users can create a production setup of NDB/MySQL Cluster from ClusterControl and deploy management, SQL/API and data nodes - all via the ClusterControl interface.

The replay of this webinar and the slides are now available for viewing online:

…

A customer reported last month that MySQL does a full table scan (FTS) if a query was filtered by a INT value on a VARCHAR column. First I told him that this is not true any more because MySQL has fixed this behaviour long time ago. He showed me that I was wrong:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `data` (`data`)
) ENGINE=InnoDB;

EXPLAIN SELECT * FROM test WHERE data = 42\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ALL
possible_keys: data
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 522500
     filtered: 10.00
        Extra: Using where

EXPLAIN SELECT * FROM test WHERE data = …

I posted recently Lossless RBR for MySQL 8.0 about a concern I have about moving to minimal RBR in MySQL 8.0.  This seems to be the direction that Oracle is considering, but I am not sure it is a good idea as a default setting. I talked about a hypothetical new replication mode lossless RBR and also about … Continue reading The differences between IDEMPOTENT and AUTO-REPAIR mode

The post The differences between IDEMPOTENT and AUTO-REPAIR mode first appeared on Simon J Mudd's Blog.

In this blog, we’ll look at the MySQL 5.7 document store feature, and how it is implemented.

Document Store

MySQL 5.7.12 is a major new release, as it contains quite a number of new features:

1. Document store and “MongoDB” like NoSQL interface to JSON storage
2. Protocol X / X Plugin, which can be used for asynchronous queries (I will write about it as well)
3. New MySQL shell

Peter already wrote the document store overview; in this post, I will look deeper into the document store implementation. In my next post, I will demonstrate how to use document store for Internet of Things (IoT) and event logging.

Older MySQL 5.7 …

In this blog post we’ll discuss pt-online-schema-change, and how to correctly use it.

Always use pt-osc?

Altering large tables can be still a problematic DBA task, even now after we’ve improved Online DDL features in MySQL 5.6 and 5.7. Some ALTER types are still not online, or sometimes just too expensive to execute on busy production master.

So in some cases, we may want to apply an

ALTER

 first on slaves, taking them out of traffic pool one by one and bringing them back after the

ALTER

 is done. In the end, we can promote one of the already altered slaves to be new master, so that the downtime/maintenance time is greatly minimized. The ex-master can be altered later, without affecting production. Of course, this method works best when the schema change is …

Join Sveta on Thursday, May 26, 2016, at 10 am PDT (UTC-7) for her webinar Troubleshooting MySQL hardware resource usage.

MySQL does not just run on its own. It stores data on disk, and stores data and temporarily results in memory. It uses CPU resources to perform operations, and a network to communicate with its clients.

In this webinar, we’ll discuss common resource usage issues, how they affect MySQL Server performance, and methods to find out how resources are being used. We will employ both OS-level tools, and new features in Performance Schema that provide detailed information on what exactly is happening inside MySQL Server.

…

I've seen some confusion on what constitutes a keyword or a reserved word in MySQL. The manual defines them, and has a complete list. Reserved words are a special subset of keywords, and you can't use a reserved word as an identifier unless you quote it with backticks. I discourage the use of backticks to quote identifiers, because it allows you to use lots of words and characters in your identifier that you'll probably regret later. For example:

`` mysql> create tableYou will regret this!((╯°□°）╯︵ ┻━┻` int); Query OK, 0 rows affected (0.03 sec)

mysql> desc You will regret this!; +--------------------------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------------+---------+------+-----+---------+-------+ | …

This post is about setting up Master- Slave replication on MySQL database. Here we assume that, primary master server is up and running and you need to add another server as a slave to an existing server. The following step by step guide will help you to set up the replication.Step 1: Enable binary log on master server:

Binary logs are the source of information for the slave server to replicate the database from master server. The slave server downloads the binary logs and executes it as a SQL statement. In order to enable the binary logs add the following parameters to configuration file and restart the server.[mysqld]log-bin = /path/to/log/mysql-binserver-id = 1expire_logs_days = 15max_binlog_size = 1Gbinlog_format = MIXED

In order to verify whether the binary log enabled or not, just run of the following command.SHOW MASTER STATUS \G
SHOW BINARY LOGS;
SHOW VARIABLES LIKE ‘%log-bin%’;

Step 2: …

Percona is glad to announce the GA release of Percona XtraBackup 2.4.3 on May 23rd, 2016. Downloads are available from our download site and from apt and yum repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered …