You are previewing MariaDB Cookbook.
O'Reilly logo
MariaDB Cookbook

Book Description

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.

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.

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
  • Downloading the example code for this book. You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

    Table of Contents

    1. MariaDB Cookbook
      1. Table of Contents
      2. MariaDB Cookbook
      3. Credits
      4. About the Author
      5. About the Reviewers
      6. www.PacktPub.com
        1. Support files, eBooks, discount offers, and more
          1. Why subscribe?
          2. Free access for Packt account holders
      7. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Conventions
        5. Reader feedback
        6. Customer support
          1. Downloading the example code
          2. Errata
          3. Piracy
          4. Questions
      8. 1. Getting Started with MariaDB
        1. Introduction
        2. Installing MariaDB on Windows
          1. How to do it...
          2. How it works...
          3. There's more...
            1. HeidiSQL
            2. The Feedback plugin
          4. See also
        3. Installing MariaDB on Linux
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        4. Installing MariaDB on Mac OS X
          1. Getting ready
          2. How to do it…
          3. How it works...
        5. Enabling the Feedback plugin
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        6. Switching between InnoDB and XtraDB
          1. How to do it...
          2. How it works...
          3. See also
        7. Creating a backup user
          1. How to do it…
          2. How it works...
        8. Making backups with XtraBackup
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Restoring from a backup
            2. XtraBackup and NFS
        9. Making backups with mysqldump
          1. Getting ready
          2. How to do it…
          3. How it works...
          4. There's more...
            1. --add-drop-database
            2. --add-drop-table
            3. --add-locks
        10. Checking and optimizing tables automatically with mysqlcheck and cron
          1. How to do it…
          2. How it works...
          3. There's more…
            1. Security
        11. Using progress reporting in the mysql client
          1. How to do it…
          2. How it works...
          3. There's more…
            1. Disabling progress reporting
            2. Progress reporting in mytop
      9. 2. Diving Deep into MariaDB
        1. Introduction
        2. Importing the data exported by mysqldump
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        3. Using SHOW EXPLAIN with running queries
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        4. Using LIMIT ROWS EXAMINED
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Using LIMIT with LIMIT ROWS EXAMINED
            2. Warning of incomplete results
        5. Using INSTALL SONAME
          1. How to do it...
          2. How it works...
          3. There's more...
            1. Plugin names versus filenames
            2. INSTALL SONAME versus INSTALL PLUGIN
          4. See also
        6. Producing HTML output
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        7. Producing XML output
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        8. Migrating a table from MyISAM to Aria
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        9. Migrating a table from MyISAM or Aria to InnoDB or XtraDB
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
      10. 3. Optimizing and Tuning MariaDB
        1. Introduction
        2. Using SHOW STATUS to check if a feature is being used
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        3. Controlling MariaDB optimizer strategies
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        4. Using extended keys with InnoDB and XtraDB
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        5. Configuring the Aria two-step deadlock detection
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        6. Configuring the MyISAM segmented key cache
          1. How to do it...
          2. How it works...
          3. There's more...
            1. Setting the number of segments to 1
            2. Determining the optimal number of segments
            3. Other key cache variables
          4. See also
        7. Configuring threadpool
          1. How to do it...
          2. How it works...
          3. There's more...
            1. The thread_pool_stall_limit, thread_pool_max_threads, and extra_port variables
            2. The thread_pool_idle_timeout variable
            3. Pool-of-threads differences on Windows and Linux
          4. See also
        8. Configuring the Aria pagecache
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        9. Optimizing queries with the subquery cache
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        10. Optimizing semijoin subqueries
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        11. Creating an index
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        12. Creating a full-text index
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        13. Removing an index
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        14. Using JOINs
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        15. Using microseconds in the DATETIME columns
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        16. Updating DATETIME and TIMESTAMP columns automatically
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
      11. 4. The TokuDB Storage Engine
        1. Introduction
        2. Installing TokuDB
          1. How to do it...
          2. How it works...
          3. There's more...
            1. Making TokuDB the default storage engine
            2. Creating a TokuDB-specific configuration file
          4. See also
        3. Configuring TokuDB
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        4. Creating TokuDB tables
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        5. Migrating to TokuDB
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        6. Adding indexes to TokuDB tables
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        7. Modifying the compression of a TokuDB table
          1. Getting ready
          2. How to do it...
            1. How it works...
          3. There's more...
      12. 5. The CONNECT Storage Engine
        1. Introduction
        2. Installing the CONNECT storage engine
          1. How to do it...
          2. How it works...
          3. See also
        3. Creating and dropping CONNECT tables
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. OPTION_LIST options
            2. Dropping CONNECT tables
            3. Files and CONNECT
          5. See also
        4. Reading and writing CSV data using CONNECT
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. CSV header lines
            2. Changing the number and order of columns read using flags
          5. See also
        5. Reading and writing XML data using CONNECT
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Inserting XML data
            2. Tree versus HTML-like data structures
            3. Tags and tag attributes
          5. See also
        6. Accessing MariaDB tables using CONNECT
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        7. Using the XCOL table type
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        8. Using the PIVOT table type
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        9. Using the OCCUR table type
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        10. Using the WMI table type
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Performance
            2. Other information
          5. See also
        11. Using the MAC address table type
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
      13. 6. Replication in MariaDB
        1. Introduction
        2. Setting up replication
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Common causes of replication failures
            2. Binary logs versus relay logs
            3. Safer replication
          5. See also
        3. Using global transaction IDs
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        4. Using multisource replication
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Other ignore options
            2. Additional files
          5. See also
        5. Enhancing the binlog with row event annotations
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        6. Configuring binlog event checksums
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        7. Selectively skipping the replication of binlog events
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
      14. 7. Replication with MariaDB Galera Cluster
        1. Introduction
        2. Installing MariaDB Galera Cluster
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Configuring MariaDB Galera Cluster
          5. See also
        3. Dropping a node from MariaDB Galera Cluster
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        4. Shutting down MariaDB Galera Cluster
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
      15. 8. Performance and Usage Statistics
        1. Introduction
        2. Installing the Audit Plugin
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        3. Using the Audit Plugin
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        4. Using engine-independent table statistics
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        5. Using extended statistics
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        6. Enabling the performance schema
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        7. Using the performance schema
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
      16. 9. Searching Data Using Sphinx
        1. Introduction
        2. Installing SphinxSE in MariaDB
          1. How to do it...
          2. How it works...
          3. There's more...
            1. SphinxSE versus Sphinx
            2. Getting SphinxSE's status
          4. See also
        3. Installing the Sphinx daemon on Linux
          1. How to do it...
          2. How it works...
          3. See also
        4. Installing the Sphinx daemon on Windows
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        5. Configuring the Sphinx daemon
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. The Sphinx daemon and MariaDB on different hosts
            2. Sphinx queries
          5. See also
        6. Searching with the Sphinx daemon and SphinxSE
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
      17. 10. Exploring Dynamic and Virtual Columns in MariaDB
        1. Introduction
        2. Creating tables with dynamic columns
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        3. Inserting, updating, and deleting dynamic column data
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Nesting dynamic columns
          5. See also
        4. Reading data from a dynamic column
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Reading nested dynamic columns
          5. See also
        5. Using virtual columns
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
      18. 11. NoSQL with HandlerSocket
        1. Introduction
        2. Installing and configuring HandlerSocket
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        3. Installing the libhsclient library
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Installing the HandlerSocket PERL client libraries
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Reading data using HandlerSocket and PERL
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        6. Inserting data using HandlerSocket and PERL
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        7. Updating and deleting data using HandlerSocket and PERL
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        8. Installing the HandlerSocket Python client libraries
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        9. Reading data using HandlerSocket and Python
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        10. Inserting data using HandlerSocket and Python
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        11. Updating and deleting data using HandlerSocket and Python
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        12. Installing the HandlerSocket Ruby client libraries
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        13. Reading data using HandlerSocket and Ruby
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        14. Inserting data using HandlerSocket and Ruby
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        15. Updating and deleting data using HandlerSocket and Ruby
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        16. Using HandlerSocket directly with Telnet
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
      19. 12. NoSQL with the Cassandra Storage Engine
        1. Introduction
        2. Installing the Cassandra storage engine
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        3. Mapping data between MariaDB and Cassandra
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        4. Using INSERT, UPDATE, and DELETE with the Cassandra storage engine
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        5. Using SELECT with the Cassandra storage engine
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
      20. 13. MariaDB Security
        1. Introduction
        2. Securing MariaDB with mysql_secure_installation
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        3. Securing MariaDB files on Linux
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        4. Securing MariaDB files on Windows
          1. How to do it...
          2. How it works...
          3. There's more...
        5. Checking for users with insecure passwords
          1. How to do it...
          2. How it works...
          3. See also
        6. Encrypting connections with SSL
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        7. Using roles to control user permissions
          1. How to do it...
          2. How it works...
          3. See also
        8. Authenticating using the PAM authentication plugin
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
      21. Index