Version: 5.5.5-10.0.10-MariaDB-log MariaDB Server

When I use mariadb as multi-source slave DB I met a strange problem . Creating index on a vary large partition table , Mariadb do — copy to tmp table on the slave side. It takes a long time and still not finish over 9 hours.

Primary : MySQL 5.6.16 —

add index on a partition table:

CREATE TABLE `track_raw_wap211_log` (
 `table_id` varchar(100) DEFAULT NULL,
 `page_id` varchar(100) DEFAULT NULL,
 `banner_id` varchar(100) DEFAULT NULL,
 `button_id` varchar(100) DEFAULT NULL,
 `test_id` varchar(100) DEFAULT NULL,
 `classfication` varchar(100) DEFAULT NULL,
 `request_refer` varchar(100) DEFAULT NULL,
 `request_url` text,
 `title` varchar(100) DEFAULT NULL,
 `user_id` varchar(100) DEFAULT NULL,
 `language` varchar(100) DEFAULT NULL,
 `event` varchar(100) DEFAULT NULL,
 `event_desc` varchar(100) DEFAULT NULL,
 `event_type` varchar(100) DEFAULT NULL,
 `log_version` varchar(100) DEFAULT …

One of my students requested an option to the bash shell. It was interesting to hear that he wanted me to instal the zsh in my Fedora image. There’s only one book that I’m aware of that’s been published on the Z Shell, and it is From Bash to Z Shell.

This post shows how to add the zsh to my Fedora image because I already release a new one for the term without the zsh shell. You use the yum utility as the root user to install the zsh library:

yum …

and without creating a table to pass the state around (really just an excuse to use the named locks feature).

DELIMITER //
DROP FUNCTION IF EXISTS SET_BREAKPOINT//
CREATE FUNCTION SET_BREAKPOINT()
RETURNS tinyint
NO SQL
BEGIN
        -- Acquire lock 1
        -- Wait until lock 2 is taken to signal that we may continue
        DO GET_LOCK(CONCAT('lock_1_', CONNECTION_ID()), -1);
        REPEAT
                DO 1;
        UNTIL IS_USED_LOCK(CONCAT('lock_2_', CONNECTION_ID())) END REPEAT;
        DO RELEASE_LOCK(CONCAT('lock_1_', CONNECTION_ID()));

        -- Acquire lock 3 to acknowledge message to continue.
        -- Wait for lock 2 to be released as signal of receipt.
        DO GET_LOCK(CONCAT('lock_3_', CONNECTION_ID()), -1);
        REPEAT
                DO 1;
        UNTIL IS_FREE_LOCK(CONCAT('lock_2_', CONNECTION_ID())) END REPEAT;
        DO RELEASE_LOCK(CONCAT('lock_3_', CONNECTION_ID()));

        RETURN 1;
END//

DROP FUNCTION IF EXISTS …

After TokuDB was announced as a new storage engine for MySQL , it made me very curious, but I didn’t tried it out until now.

I try to check it from different aspects and I’ll be blog it step by step. I don’t do any serious benchmarking, just play with it, and see if it could be fit into Kinja’s MySQL ecosystem.

I use one of our development servers as a TokuDB playground. Sadly that hardware is not the same as the database masters nor as the slaves, so performance tests couldn’t be made on that piece of metal but many other ways are open to do this.

I’ve installed the tokudb plugin from the Percona repository. The setup was quite easy and fast, the documentation is nice.

I decided to leave all the MyISAM tables as – is but convert all the InnoDB tables to TokuDB. To achive this, I’ve did the …

If you couldn’t have the chance to attend my session “GTID Replication – Implementation and Troubleshooting” at Percona Live MySQL & Expo Conference in Santa Clara April 13-16, 2015, the slides of my presentation are now available.
The talk was mainly about the new feature in MySQL 5.6 “GTID”, what is the concept, benefits, GTID replication implementation and troubleshooting and how to perform the migration from classic replication to GTID replication in both MySQL 5.6 and 5.7.
If you have any question, feel free to contact me

Wed, 2015-04-22 08:05martinbrampton

When you want to connect a client to a database server through an insecure network, there are two main choices: use SSL or use an SSH tunnel. Although SSL often may seem to be the best option, SSH tunnels are in fact easier to implement and can be very effective. Traffic through an SSH tunnel is encrypted with all of the security of the SSH protocol, which has a strong track record against attacks.

There are various ways to implement an SSH tunnel. This article suggests a simple approach which is adequate in many situations. For the examples here, let’s assume that there is a database server running on a host named, server.example.com, with an IP address of 1.2.3.4. Suppose further that the client is on a host named, client.example.com, with an IP address of 5.6.7.8. We’ll also suppose that there are tightly configured iptables …

Article about bug report #68814 related to testing count(*) explain plan.

Or sales table huge enough to play with.

mysql> select count(*) from sales;
+----------+
| count(*) |
+----------+
|  2500003 |
+----------+
1 row in set (0.56 sec)

First with regular count(*) without where clause:

mysql> explain select count(*) from sales\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: index
possible_keys: NULL
          key: sales_cust_idx
      key_len: 4
          ref: NULL
         rows: 2489938
        Extra: Using index
1 row in set (0.00 sec)

Estimated rows -> rows: 2489938

Then with {where sales_id > 0}:

mysql> explain select count(*) from sales where sales_id > 0\G
*************************** 1. row …

I’ve written previously about use cases where having accounts which cannot be used to establish client connections are useful. There are various hacks to accomplish this with legacy versions (insert invalid password hash into mysql.user table, etc.), and we introduced the mysql_no_login authentication plugin for this very purpose. Now as of MySQL 5.7.6, account locking gets native support through the ACCOUNT LOCK clause of CREATE USER and ALTER USER commands. This post revisits the use cases which drove this feature and the implementation details.

Use Cases

Security …

Last week I presented my talk, "How to Analyze and Tune SQL Queries for Better Performance" both at Percona Live in Santa Clara and at airbnb Tech Talks in San Francisco.  The slides are available on slideshare. A video recording from the airbnb talk should eventually be available the airbnb Tech Talks page.

The slides for my MySQL Security Essentials presentation at Percona Live 2015 MySQL Conference and Expo are now available.

In this presentation I discuss just how insecure legacy versions of MySQL are and what are the essential requirements for securing your installation on disk, via network and with user privileges. I provide recommendations for how to manage application access for your most important data asset.

Download PDF Presentation

This presentation describes the key security improvements in MySQL 5.6 and MySQL 5.7 as well as additional features provided in MariaDB 10.0 …