Thu, 2015-04-23 20:17geoff_montee_g

User accounts in MariaDB have traditionally been completely separate from operating system accounts. However, MariaDB has included a PAM authentication plugin since version 5.2.10. With this plugin, DBAs can configure MariaDB user accounts to authenticate via PAM, allowing users to use their Linux username and password to log into the MariaDB server.

However, even when using the PAM authentication plugin, the user account still needs to exist in MariaDB, and the account needs to have privileges. Creating these MariaDB accounts and making sure the privileges are correct can be a lot of work. To decrease the amount of work involved, some users would like to be able to map a Linux user to a different MariaDB user. For example, let's say that "alice" and "bob" are both DBAs. It would be nice if each of them …

Last time I was checked, how can TokuDB be used as a drop in replacement of InnoDB. The first impressions were jolly good; way less disk space usage, and the TokuDB host can be a part of the current replication cluster.

So far so good.

Well, actually not everything is that nice, because there is a very big part of infrastructure is built on the top of xtrabackup.

So let’s see what can we do backing up TokuDB.

The first and most clean way should be the mysqldump utility, but sadly this is not really useful for us, the restore process could be take too many time, because a lot of indexes has to be rebuilt.

So we need to take binary backups. Sadly TokuDB currently offers only tokudbhotbackup for hot backup which is a part of the enterprise feature set. Sadly that seems not opensource (yet!), but I hope Percona will change that soon.

Until that the following things are sure: xtrabackup cannot …

Download PDF Presentation

In this presentation on High Performance SQL and NoSQL to the New York Web Performance Meetup I use the simple analogy of applying the same techniques used in Web Performance Optimization (WPO) to your data path (SQL or NoSQL) performance.

This presentation provides examples for 5 of the most common WPO optimizations and how to apply this to your applications access to data:

1. Gzip assets
2. Make fewer HTTP requests
3. Add expires headers
4. Use a CDN
5. Optimized Images

This presentation was recorded. A video link …

The link to the slides of my talks can be found at the end of this post but first, let me share some thoughts about PLMCE.

Talking with people, I was surprised to be criticized of presenting only the good sides of my solution without giving credit to the good side of the alternative solutions.  More than surprised, I was also a little shocked as I want to be perceived as objective as possible.  Let me try to fix that:

• I am not a GTID and log-slaves detractor, I am a simplicity lover.
• I actually like GTIDs and I have some use-cases for them; one of  them is described in the MariaDB Knowledge Base (search for last_gtid in that page).
• But I think that using GTIDs in the slave protocol is complex and that a …

I’m pleased to announce that Connector/J has a new home.

Just as several other MySQL products, Connector/J source code management moved to Git and, pretty much as expected, to GitHub. Our reasoning is nothing else than listening to our users demands and trying to follow best trends and practices. There was nothing significantly wrong with Bazaar and Launchpad, as they served us well for the last seven years. It was just time to move on.

Rest assured, all will work as before, no complications no hassles. Our public GitHub repository will expose Connector/J source code as it is in the latest generally available (GA) release, as it has been for the last years.

From now on you will find …

As of MySQL 5.6.24, MySQL Enterprise Edition includes MySQL Enterprise Firewall, an application-level firewall (it runs within the mysql database process) that enables database administrators to permit or deny SQL statement execution based on matching against whitelists of accepted statement patterns. This helps harden MySQL Server against attacks such as SQL injection or attempts to exploit applications by using them outside of their legitimate query workload characteristics.

Each MySQL account registered with the firewall has its own whitelist of statement patterns (a tokenized representation of a SQL statement), enabling protection to be tailored per account. For a given account, the firewall can operate in recording or protecting mode, for training in the accepted statement …

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 …