MariaDB Cookbook

Learn how to use the database that’s growing in popularity as a drop-in replacement for MySQL. The MariaDB Cookbook is overflowing with handy recipes and code examples to help you become an expert simply and speedily.

MariaDB Cookbook

Cookbook
Daniel Bartholomew

Learn how to use the database that’s growing in popularity as a drop-in replacement for MySQL. The MariaDB Cookbook is overflowing with handy recipes and code examples to help you become an expert simply and speedily.
$29.99
$49.99
RRP $29.99
RRP $49.99
eBook
Print + eBook
$12.99 p/month

Get Access

Get Unlimited Access to every Packt eBook and Video course

Enjoy full and instant access to over 3000 books and videos – you’ll find everything you need to stay ahead of the curve and make sure you can always get the job done.

Book Details

ISBN 139781783284399
Paperback282 pages

About This Book

  • Enable performance-enhancing optimizations
  • Connect to different databases and file formats
  • Filled with clear step-by-step instructions that can be run on a live database

Who This Book Is For

This book is for anyone who wants to learn more about databases in general or MariaDB in particular. Some familiarity with SQL databases is assumed, but the recipes are approachable to almost anyone with basic database skills.

Table of Contents

Chapter 1: 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
Chapter 2: 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
Chapter 3: 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
Chapter 4: 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
Chapter 5: 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
Chapter 6: 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
Chapter 7: Replication with MariaDB Galera Cluster
Introduction
Installing MariaDB Galera Cluster
Dropping a node from MariaDB Galera Cluster
Shutting down MariaDB Galera Cluster
Chapter 8: 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
Chapter 9: 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
Chapter 10: 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
Chapter 11: 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
Chapter 12: 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
Chapter 13: 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

What You Will Learn

  • Enable various MariaDB optimizations
  • Link MariaDB to a Cassandra cluster
  • Enable and use the TokuDB storage engine
  • Read from and write to various data formats including XML and CSV
  • Search through your data with Sphinx
  • Connect to other databases with ODBC
  • Tune MariaDB for the best performance

In Detail

MariaDB is a new database that has become very popular. It is easy to install and use, even on personal systems. It is evolving and expanding to meet the database needs of today, tomorrow, and into the future. MariaDB is a drop in replacement for MySQL it also has a lot of new features and performance enhancements that differentiate it from MySQL. It is also powerful enough to be the database of choice for some of the biggest and most popular websites in the world.

This practical guide unlocks the advanced features of MariaDB's capabilities, including new storage engines, performance enhancing optimizations, and other advanced abilities. If you're looking to backend your enterprise, cloud, or embedded or mobile apps with a fast, free, open source, and familiar SQL database, MariaDB is perfect for you.

We begin with installing MariaDB and configuring it to unlock powerful optimizations that can speed up certain queries exponentially. We will then move on to using the extra storage engines included with MariaDB, such as TokuDB and the CONNECT engine. With those mastered, it's then time to branch out from single to multi-server installations, so we will dive into MariaDB's clustering and replication features, learning how to efficiently search and index our data, work with NoSQL-style data, and connect MariaDB with a Cassandra cluster. It's a dangerous world out there, so last of all, we wrap things up with recipes on securing MariaDB.

This is your ideal guide for getting the most out of MariaDB.

Authors

Table of Contents

Chapter 1: 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
Chapter 2: 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
Chapter 3: 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
Chapter 4: 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
Chapter 5: 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
Chapter 6: 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
Chapter 7: Replication with MariaDB Galera Cluster
Introduction
Installing MariaDB Galera Cluster
Dropping a node from MariaDB Galera Cluster
Shutting down MariaDB Galera Cluster
Chapter 8: 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
Chapter 9: 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
Chapter 10: 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
Chapter 11: 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
Chapter 12: 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
Chapter 13: 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

Book Details

ISBN 139781783284399
Paperback282 pages
Read More