Book Image

MariaDB Cookbook

By : Daniel Bartholomew
Book Image

MariaDB Cookbook

By: Daniel Bartholomew

Overview of this book

Related Content you might be interested in

Current Title:

Small book image
MariaDB Cookbook
Daniel Bartholomew
Mar, 2014 | 282 pages
No titles found
Table of Contents (20 chapters)
MariaDB Cookbook
MariaDB Cookbook
Credits
Credits
About the Author
About the Author
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Getting Started with MariaDB
Getting Started with MariaDB
Introduction
Installing MariaDB on Windows
Installing MariaDB on Linux
Installing MariaDB on Mac OS X
Enabling the Feedback plugin
Switching between InnoDB and XtraDB
Creating a backup user
Making backups with XtraBackup
Making backups with mysqldump
Checking and optimizing tables automatically with mysqlcheck and cron
Using progress reporting in the mysql client
2
Diving Deep into MariaDB
Diving Deep into MariaDB
Introduction
Importing the data exported by mysqldump
Using SHOW EXPLAIN with running queries
Using LIMIT ROWS EXAMINED
Using INSTALL SONAME
Producing HTML output
Producing XML output
Migrating a table from MyISAM to Aria
Migrating a table from MyISAM or Aria to InnoDB or XtraDB
3
Optimizing and Tuning MariaDB
Optimizing and Tuning MariaDB
Introduction
Using SHOW STATUS to check if a feature is being used
Controlling MariaDB optimizer strategies
Using extended keys with InnoDB and XtraDB
Configuring the Aria two-step deadlock detection
Configuring the MyISAM segmented key cache
Configuring threadpool
Configuring the Aria pagecache
Optimizing queries with the subquery cache
Optimizing semijoin subqueries
Creating an index
Creating a full-text index
Removing an index
Using JOINs
Using microseconds in the DATETIME columns
Updating DATETIME and TIMESTAMP columns automatically
4
The TokuDB Storage Engine
The TokuDB Storage Engine
Introduction
Installing TokuDB
Configuring TokuDB
Creating TokuDB tables
Migrating to TokuDB
Adding indexes to TokuDB tables
Modifying the compression of a TokuDB table
5
The CONNECT Storage Engine
The CONNECT Storage Engine
Introduction
Installing the CONNECT storage engine
Creating and dropping CONNECT tables
Reading and writing CSV data using CONNECT
Reading and writing XML data using CONNECT
Accessing MariaDB tables using CONNECT
Using the XCOL table type
Using the PIVOT table type
Using the OCCUR table type
Using the WMI table type
Using the MAC address table type
6
Replication in MariaDB
Replication in MariaDB
Introduction
Setting up replication
Using global transaction IDs
Using multisource replication
Enhancing the binlog with row event annotations
Configuring binlog event checksums
Selectively skipping the replication of binlog events
7
Replication with MariaDB Galera Cluster
Replication with MariaDB Galera Cluster
Introduction
Installing MariaDB Galera Cluster
Dropping a node from MariaDB Galera Cluster
Shutting down MariaDB Galera Cluster
8
Performance and Usage Statistics
Performance and Usage Statistics
Introduction
Installing the Audit Plugin
Using the Audit Plugin
Using engine-independent table statistics
Using extended statistics
Enabling the performance schema
Using the performance schema
9
Searching Data Using Sphinx
Searching Data Using Sphinx
Introduction
Installing SphinxSE in MariaDB
Installing the Sphinx daemon on Linux
Installing the Sphinx daemon on Windows
Configuring the Sphinx daemon
Searching with the Sphinx daemon and SphinxSE
10
Exploring Dynamic and Virtual Columns in MariaDB
Exploring Dynamic and Virtual Columns in MariaDB
Introduction
Creating tables with dynamic columns
Inserting, updating, and deleting dynamic column data
Reading data from a dynamic column
Using virtual columns
11
NoSQL with HandlerSocket
NoSQL with HandlerSocket
Introduction
Installing and configuring HandlerSocket
Installing the libhsclient library
Installing the HandlerSocket PERL client libraries
Reading data using HandlerSocket and PERL
Inserting data using HandlerSocket and PERL
Updating and deleting data using HandlerSocket and PERL
Installing the HandlerSocket Python client libraries
Reading data using HandlerSocket and Python
Inserting data using HandlerSocket and Python
Updating and deleting data using HandlerSocket and Python
Installing the HandlerSocket Ruby client libraries
Reading data using HandlerSocket and Ruby
Inserting data using HandlerSocket and Ruby
Updating and deleting data using HandlerSocket and Ruby
Using HandlerSocket directly with Telnet
12
NoSQL with the Cassandra Storage Engine
NoSQL with the Cassandra Storage Engine
Introduction
Installing the Cassandra storage engine
Mapping data between MariaDB and Cassandra
Using INSERT, UPDATE, and DELETE with the Cassandra storage engine
Using SELECT with the Cassandra storage engine
13
MariaDB Security
MariaDB Security
Introduction
Securing MariaDB with mysql_secure_installation
Securing MariaDB files on Linux
Securing MariaDB files on Windows
Checking for users with insecure passwords
Encrypting connections with SSL
Using roles to control user permissions
Authenticating using the PAM authentication plugin
Index
Index

Using SHOW EXPLAIN with running queries

The SHOW EXPLAIN feature was introduced in MariaDB 10.0. It enables us to get an EXPLAIN (that is, a description of the query plan) of the query running in a given thread.

Getting ready

Import the ISFDB database as described in the Importing the data exported by mysqldump recipe of this chapter.

How to do it...

  1. Open a terminal window and launch the mysql command-line client and connect to the isfdb database.

    mysql isfdb

  2. Next, we open another terminal window and launch another instance of the mysql command-line client.

  3. Run the following command in the first window:

    ALTER TABLE title_relationships DROP KEY titles;

  4. Next, in the first window, start the following example query:

    SELECT titles.title_id AS ID, 
       titles.title_title AS Title, 
       authors.author_legalname AS Name, 
       (SELECT COUNT(DISTINCT title_relationships.review_id) 
         FROM title_relationships
         WHERE title_relationships.title_id = titles.title_id)
  AS reviews 
FROM...
Previous Section
End of Section 4
Next Section