MariaDB Cookbook电子书

MariaDB Cookbook

Table of Contents

MariaDB Cookbook

Credits

About the Author

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Getting Started with MariaDB

Introduction

Installing MariaDB on Windows

How to do it...

How it works...

There's more...

HeidiSQL

The Feedback plugin

See also

Installing MariaDB on Linux

Getting ready

How to do it...

How it works...

See also

Installing MariaDB on Mac OS X

Getting ready

How to do it…

How it works...

Enabling the Feedback plugin

Getting ready

How to do it...

How it works...

There's more...

See also

Switching between InnoDB and XtraDB

How to do it...

How it works...

See also

Creating a backup user

How to do it…

How it works...

Making backups with XtraBackup

Getting ready

How to do it...

How it works...

There's more...

Restoring from a backup

XtraBackup and NFS

Making backups with mysqldump

Getting ready

How to do it…

How it works...

There's more...

--add-drop-database

--add-drop-table

--add-locks

Checking and optimizing tables automatically with mysqlcheck and cron

How to do it…

How it works...

There's more…

Security

Using progress reporting in the mysql client

How to do it…

How it works...

There's more…

Disabling progress reporting

Progress reporting in mytop

2. Diving Deep into MariaDB

Introduction

Importing the data exported by mysqldump

How to do it...

How it works...

There's more...

See also

Using SHOW EXPLAIN with running queries

Getting ready

How to do it...

How it works...

There's more...

See also

Using LIMIT ROWS EXAMINED

Getting ready

How to do it...

How it works...

There's more...

Using LIMIT with LIMIT ROWS EXAMINED

Warning of incomplete results

Using INSTALL SONAME

How to do it...

How it works...

There's more...

Plugin names versus filenames

INSTALL SONAME versus INSTALL PLUGIN

See also

Producing HTML output

Getting ready

How to do it...

How it works...

There's more...

See also

Producing XML output

Getting ready

How to do it...

How it works...

See also

Migrating a table from MyISAM to Aria

Getting ready

How to do it...

How it works...

See also

Migrating a table from MyISAM or Aria to InnoDB or XtraDB

Getting ready

How to do it...

How it works...

There's more...

See also

3. Optimizing and Tuning MariaDB

Introduction

Using SHOW STATUS to check if a feature is being used

How to do it...

How it works...

There's more...

See also

Controlling MariaDB optimizer strategies

How to do it...

How it works...

There's more...

See also

Using extended keys with InnoDB and XtraDB

How to do it...

How it works...

There's more...

See also

Configuring the Aria two-step deadlock detection

How to do it...

How it works...

There's more...

See also

Configuring the MyISAM segmented key cache

How to do it...

How it works...

There's more...

Setting the number of segments to 1

Determining the optimal number of segments

Other key cache variables

See also

Configuring threadpool

How to do it...

How it works...

There's more...

The thread_pool_stall_limit, thread_pool_max_threads, and extra_port variables

The thread_pool_idle_timeout variable

Pool-of-threads differences on Windows and Linux

See also

Configuring the Aria pagecache

How to do it...

How it works...

There's more...

See also

Optimizing queries with the subquery cache

Getting ready

How to do it...

How it works...

There's more...

See also

Optimizing semijoin subqueries

How to do it...

How it works...

There's more...

See also

Creating an index

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a full-text index

Getting ready

How to do it...

How it works...

There's more...

See also

Removing an index

Getting ready

How to do it...

How it works...

There's more...

See also

Using JOINs

Getting ready

How to do it...

How it works...

There's more...

See also

Using microseconds in the DATETIME columns

How to do it...

How it works...

There's more...

See also

Updating DATETIME and TIMESTAMP columns automatically

How to do it...

How it works...

There's more...

See also

4. The TokuDB Storage Engine

Introduction

Installing TokuDB

How to do it...

How it works...

There's more...

Making TokuDB the default storage engine

Creating a TokuDB-specific configuration file

See also

Configuring TokuDB

Getting ready

How to do it...

How it works...

There's more...

See also

Creating TokuDB tables

Getting ready

How to do it...

How it works...

See also

Migrating to TokuDB

Getting ready

How to do it...

How it works...

There's more...

See also

Adding indexes to TokuDB tables

Getting ready

How to do it...

How it works...

There's more...

See also

Modifying the compression of a TokuDB table

Getting ready

How to do it...

How it works...

There's more...

5. The CONNECT Storage Engine

Introduction

Installing the CONNECT storage engine

How to do it...

How it works...

See also

Creating and dropping CONNECT tables

Getting ready

How to do it...

How it works...

There's more...

OPTION_LIST options

Dropping CONNECT tables

Files and CONNECT

See also

Reading and writing CSV data using CONNECT

Getting ready

How to do it...

How it works...

There's more...

CSV header lines

Changing the number and order of columns read using flags

See also

Reading and writing XML data using CONNECT

Getting ready

How to do it...

How it works...

There's more...

Inserting XML data

Tree versus HTML-like data structures

Tags and tag attributes

See also

Accessing MariaDB tables using CONNECT

Getting ready

How to do it...

How it works...

There's more...

See also

Using the XCOL table type

How to do it...

How it works...

There's more...

See also

Using the PIVOT table type

How to do it...

How it works...

There's more...

See also

Using the OCCUR table type

How to do it...

How it works...

There's more...

See also

Using the WMI table type

Getting ready

How to do it...

How it works...

There's more...

Performance

Other information

See also

Using the MAC address table type

Getting ready

How to do it...

How it works...

There's more...

See also

6. Replication in MariaDB

Introduction

Setting up replication

Getting ready

How to do it...

How it works...

There's more...

Common causes of replication failures

Binary logs versus relay logs

Safer replication

See also

Using global transaction IDs

Getting ready

How to do it...

How it works...

There's more...

See also

Using multisource replication

Getting ready

How to do it...

How it works...

There's more...

Other ignore options

Additional files

See also

Enhancing the binlog with row event annotations

Getting ready

How to do it...

How it works...

There's more...

See also

Configuring binlog event checksums

Getting ready

How to do it...

How it works...

There's more...

See also

Selectively skipping the replication of binlog events

Getting ready

How to do it...

How it works...

There's more...

See also

7. Replication with MariaDB Galera Cluster

Introduction

Installing MariaDB Galera Cluster

Getting ready

How to do it...

How it works...

There's more...

Configuring MariaDB Galera Cluster

See also

Dropping a node from MariaDB Galera Cluster

Getting ready

How to do it...

How it works...

There's more...

See also

Shutting down MariaDB Galera Cluster

Getting ready

How to do it...

How it works...

See also

8. Performance and Usage Statistics

Introduction

Installing the Audit Plugin

Getting ready

How to do it...

How it works...

There's more...

See also

Using the Audit Plugin

Getting ready

How to do it...

How it works...

There's more...

See also

Using engine-independent table statistics

How to do it...

How it works...

There's more...

See also

Using extended statistics

How to do it...

How it works...

There's more...

See also

Enabling the performance schema

How to do it...

How it works...

There's more...

See also

Using the performance schema

Getting ready

How to do it...

How it works...

There's more...

See also

9. Searching Data Using Sphinx

Introduction

Installing SphinxSE in MariaDB

How to do it...

How it works...

There's more...

SphinxSE versus Sphinx

Getting SphinxSE's status

See also

Installing the Sphinx daemon on Linux

How to do it...

How it works...

See also

Installing the Sphinx daemon on Windows

How to do it...

How it works...

There's more...

See also

Configuring the Sphinx daemon

Getting ready

How to do it...

How it works...

There's more...

The Sphinx daemon and MariaDB on different hosts

Sphinx queries

See also

Searching with the Sphinx daemon and SphinxSE

Getting ready

How to do it...

How it works...

There's more...

See also

10. Exploring Dynamic and Virtual Columns in MariaDB

Introduction

Creating tables with dynamic columns

How to do it...

How it works...

There's more...

See also

Inserting, updating, and deleting dynamic column data

Getting ready

How to do it...

How it works...

There's more...

Nesting dynamic columns

See also

Reading data from a dynamic column

Getting ready

How to do it...

How it works...

There's more...

Reading nested dynamic columns

See also

Using virtual columns

How to do it...

How it works...

There's more...

See also

11. NoSQL with HandlerSocket

Introduction

Installing and configuring HandlerSocket

How to do it...

How it works...

There's more...

See also

Installing the libhsclient library

Getting ready

How to do it...

How it works...

Installing the HandlerSocket PERL client libraries

Getting ready

How to do it...

How it works...

Reading data using HandlerSocket and PERL

Getting ready

How to do it...

How it works...

There's more...

See also

Inserting data using HandlerSocket and PERL

Getting ready

How to do it...

How it works...

See also

Updating and deleting data using HandlerSocket and PERL

Getting ready

How to do it...

How it works...

See also

Installing the HandlerSocket Python client libraries

Getting ready

How to do it...

How it works...

See also

Reading data using HandlerSocket and Python

Getting ready

How to do it...

How it works...

See also

Inserting data using HandlerSocket and Python

Getting ready

How to do it...

How it works...

See also

Updating and deleting data using HandlerSocket and Python

Getting ready

How to do it...

How it works...

See also

Installing the HandlerSocket Ruby client libraries

Getting ready

How to do it...

How it works...

There's more...

See also

Reading data using HandlerSocket and Ruby

Getting ready

How to do it...

How it works...

See also

Inserting data using HandlerSocket and Ruby

Getting ready

How to do it...

How it works...

See also

Updating and deleting data using HandlerSocket and Ruby

Getting ready

How to do it...

How it works...

See also

Using HandlerSocket directly with Telnet

Getting ready

How to do it...

How it works...

See also

12. NoSQL with the Cassandra Storage Engine

Introduction

Installing the Cassandra storage engine

How to do it...

How it works...

There's more...

See also

Mapping data between MariaDB and Cassandra

Getting ready

How to do it...

How it works...

There's more...

See also

Using INSERT, UPDATE, and DELETE with the Cassandra storage engine

Getting ready

How to do it...

How it works...

There's more...

See also

Using SELECT with the Cassandra storage engine

Getting ready

How to do it...

How it works...

There's more...

See also

13. MariaDB Security

Introduction

Securing MariaDB with mysql_secure_installation

How to do it...

How it works...

There's more...

See also

Securing MariaDB files on Linux

Getting ready

How to do it...

How it works...

There's more...

Securing MariaDB files on Windows

How to do it...

How it works...

There's more...

Checking for users with insecure passwords

How to do it...

How it works...

See also

Encrypting connections with SSL

Getting ready

How to do it...

How it works...

There's more...

See also

Using roles to control user permissions

How to do it...

How it works...

See also

Authenticating using the PAM authentication plugin

Getting ready

How to do it...

How it works...

There's more...

See also

Index