Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying MariaDB Cookbook
  • Table Of Contents Toc
  • Feedback & Rating feedback
MariaDB Cookbook

MariaDB Cookbook

By : Daniel Bartholomew
4.2 (9)
Buy this Book
close
close
MariaDB Cookbook

MariaDB Cookbook

4.2 (9)
By: Daniel Bartholomew

Overview of this book

Table of Contents (20 chapters)
close
close
close
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
chevron up
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 engine-independent table statistics

MariaDB includes a facility to gather statistics on all tables, no matter what storage engine those tables use. The MariaDB optimizer can use these statistics to better calculate the optimum query plans.

How to do it...

  1. Connect to MariaDB using the mysql command-line client with a user that has the SUPER privilege.

  2. Run the following command:

    SET GLOBAL use_stat_tables=complementary;

  3. Force an update of the table statistics for a table with the following command (change table_name to the name of an existing table):

    ANALYZE TABLE table_name;

  4. View the collected table, index, and column statistics with the following commands:

    SELECT * FROM mysql.table_stats;
SELECT * FROM mysql.index_stats;
SELECT * FROM mysql.column_stats;

How it works...

How MariaDB uses the engine-independent table statistics is controlled by the use_stat_tables variable. There are three valid values: never means that MariaDB will not use the statistics, complementary means that MariaDB...

Unlock full access

Continue reading for free

A Packt free trial gives you instant online access to our library of over 7000 practical eBooks and videos, constantly updated with the latest in tech
Start a 7-day FREE trial
End of Section 5
Next Section
bookmark search playlist font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY