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
chevron up
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 the PIVOT table type

The PIVOT table type is very useful to sort and sum the columns in a table. It's similar to GROUP BY but with a more understandable layout. This sort of task is often used to sort and sum columns of data in a desktop spreadsheet program.

How to do it...

  1. Launch the mysql command-line client application and connect to the test database on our MariaDB server. If the test database does not exist, create it first.

  2. Run the following CREATE TABLE statement to create an expenses table:

    CREATE TABLE expenses ( 
  who varchar(64), 
  day varchar(10), 
  what varchar(64), 
  amount varchar(10) 
);

  3. Add some data to the table by executing the following command lines:

    INSERT expenses VALUES 
  ("Daniel","2013-09-01","Clothing",42.50), 
  ("Amy","2013-09-02","Food",5.22), 
  ("Daniel","2013-09-01","Clothing",27.75), 
  ("Daniel","2013-09-03","Food",10.27), 
  ("Amy","2013-09-03","Gas",42.84), 
  ("Amy","2013-09-01","Food",15.01), 
  ("Amy","2013-09-01","Clothing",11.00), 
  ("Daniel...

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 9
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