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

Book Description

Debug, secure, and back up your data for optimum server performance with MariaDB

In Detail

MariaDB is a community-driven fork of MySQL, and is a relational DBMS that can be used to build reliable, high-performance database servers. MariaDB combines MySQL characteristics such as replication, backup and disaster recovery, transactions and locks, and also includes important improvements to the performance, stability, security, and monitoring capabilities.

Mastering MariaDBstarts with an overview of the basic features and mechanisms, which includes diagnosing and solving real-life problems such as data corruption, poorly performing queries, and deadlocks. You will learn how to improve the performance of a server by identifying slow queries, and how to choose and set up a proper backup plan and recover data when disasters occur. You will learn how to share your data through several servers using replication. By the end of this book, you will be able to configure MariaDB servers, diagnose as well as troubleshoot common transactional problems, and execute database maintenance.

What You Will Learn

  • Identify inefficient queries using logs and log analysis tools
  • Design your indexes and optimize your queries to produce efficient query plans
  • Tune MariaDB and InnoDB configuration to achieve a stabilized degree of performance and reliability
  • Create and manage users, roles, and permissions
  • Perform regular backups and restore data
  • Share your data through several partitions, disks, or servers using techniques such as replication to make operations faster
  • Set up, maintain, and troubleshoot a replication environment as well as a database cluster
  • 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. Mastering MariaDB
      1. Table of Contents
      2. Mastering MariaDB
      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. Understanding the Essentials of MariaDB
        1. The MariaDB architecture
        2. The command-line client
        3. Storage engines
          1. XtraDB and InnoDB
          2. TokuDB
          3. MyISAM and Aria
          4. Other engines
        4. Logs
        5. MariaDB caches
        6. InnoDB data structures
        7. Authentication and security
        8. The information_schema database
        9. The performance_schema database
        10. Compatibility with MySQL and other DBMS
        11. MariaDB resources
        12. Summary
      9. 2. Debugging
        1. Understanding error conditions in MariaDB
          1. The SQLSTATE value
          2. The error number
          3. The error message
          4. The custom errors
          5. The SHOW WARNINGS and SHOW ERRORS statements
        2. The diagnostics area
        3. The GET DIAGNOSTICS statement
        4. The error log
          1. The error log format
          2. A troubleshooting example with the error log
        5. System logs
        6. The general query log
          1. The file format of the general query log
          2. The general_log table
          3. Debugging examples with the general query log
        7. Maintenance of the server logs
          1. Flushing logs
          2. Rotating the file-based logs
          3. Rotating the table-based logs
        8. The SQL_ERROR_LOG plugin
        9. Tips on debugging stored programs
        10. Debugging stored programs using the SQL_ERROR_LOG plugin
        11. Summary
      10. 3. Optimizing Queries
        1. The slow query log
          1. The file format of the slow query log
          2. The slow_log table
        2. Explaining the pt-query-digest command from Percona Toolkit
        3. Introducing indexes
          1. Table statistics
          2. Storage engines and indexes
        4. Working with the EXPLAIN statement
          1. Understanding the output of EXPLAIN
            1. Simple SELECT statements
            2. Internal temporary tables or files
            3. The UNION queries
            4. Simple index access methods
            5. Index optimizations of the JOIN clause
            6. Optimization of subqueries
        5. Summary
      11. 4. Transactions and Locks
        1. The InnoDB locks
          1. The lock modes
          2. Lock types
          3. Diagnosing locks
          4. Locks used by various SQL statements
        2. Reads consistency
          1. The non-repeatable reads
          2. Phantom rows
          3. Consistent reads
          4. Locking reads
        3. Deadlocks
        4. Transactions
          1. The transactions life cycle
          2. Transactions isolation levels
            1. The READ UNCOMMITTED isolation level
            2. The READ COMMITTED isolation level
            3. The REPEATABLE READ isolation level
            4. The SERIALIZABLE isolation level
          3. Transactions access modes
        5. Metadata locks
        6. Summary
      12. 5. Users and Connections
        1. User accounts
        2. Setting permissions using roles
        3. Connecting MariaDB through Secure Socket Layer
        4. Authentication plugins
          1. Activating the pool of threads
          2. Monitoring the pool of threads
          3. Configuring the threadpool implementation
            1. Configuring the pool of threads on Unix
            2. Configuring the pool of threads on Windows
          4. Tuning the configuration variables
          5. Unblocking a blocked pool of threads
        5. Monitoring connections
          1. States of the process
          2. Aborting connections
        6. Summary
      13. 6. Caches
        1. InnoDB caches
          1. InnoDB pages
          2. The InnoDB buffer pool
            1. Old and new pages
            2. Buffer pool instances
            3. Dirty pages
            4. The read ahead optimization
            5. Diagnosing the buffer pool performance
            6. Dumping and loading the buffer pool
            7. The InnoDB change buffer
            8. Explaining the doublewrite buffer
        2. MyISAM key cache
          1. LRU and the midpoint insertion strategy
          2. Key cache instances
          3. Segmented key cache
          4. Preloading indexes into the cache
        3. Aria page cache
        4. The query cache explained
          1. Configuring the query cache
          2. Information on the status of the query cache
          3. Explaining the subquery cache
          4. Alternative query caching methods
        5. The table open cache
        6. Per-session buffers
        7. Summary
      14. 7. InnoDB Compressed Tables
        1. An overview of the InnoDB compression
        2. InnoDB compression requirements
        3. Explaining the file-per-table mode
          1. A brief on InnoDB file formats
        4. Creating InnoDB compressed tables
        5. Explaining the implementation of the InnoDB compression
        6. Monitoring the InnoDB compression performance
          1. The INNODB_CMPMEM table
          2. The INNODB_CMP_PER_INDEX table
          3. The INNODB_CMP table
        7. Other compression solutions
        8. Summary
      15. 8. Backup and Disaster Recovery
        1. Types of backups
          1. Logical and physical backups
          2. Hot and cold backups
        2. Complete and incremental backups
          1. Backups and replication
          2. Steps to be followed before performing backups
        3. Creating a dump file with mysqldump
        4. Delimited text backups
          1. The --tab option of the mysqldump command
          2. Loading a dump file with the mysqlimport command
          3. Creating a text-delimited file with the SELECT … INTO OUTFILE command
          4. Dumping a table definition with the SHOW CREATE TABLE command
          5. Loading a dump file with the LOAD DATA INFILE statement
          6. Separator options and clauses
          7. An example to create and restore dump files
          8. Performing a backup using a CONNECT or CSV engine
        5. Physical backups
          1. Which files should be copied?
            1. Table files
            2. Logfiles
            3. Configuration files
          2. Hot physical backups
          3. Filesystem snapshots
          4. Incremental physical backups with the rsync command
          5. Copying files when the server is running
        6. Using the binary log for incremental backups
        7. Percona XtraBackup
          1. Performing backups
            1. Complete backups
            2. Partial backups
          2. Preparing backups
          3. Preparing complete backups
            1. Preparing partial backups
          4. Restoring backups
            1. Restoring complete backups
            2. Restoring partial backups
        8. Securing backups
        9. Repairing tables
          1. Recovering InnoDB tables
            1. Checking tables
            2. Transaction logs
            3. Forcing data recovery
          2. Repairing non-InnoDB tables
            1. The CHECK TABLE statement
            2. The REPAIR TABLE statement
            3. Repairing CSV tables
            4. Repairing tables with the myisamchk and aria_chk tools
            5. MyISAM and Aria autorecovery
        10. Summary
      16. 9. Replication
        1. An overview of replication
          1. How replication works
          2. Replication threads
            1. Parallel replication
          3. Slave logs
        2. Choosing a binary log format
          1. Statement-based binary logging
          2. Row-based binary logging
          3. The MIXED binary logging format
          4. The binary logging of stored programs
        3. Configuring replication
          1. Configuring a new replication master
          2. Configuring a new replication slave
          3. Starting a slave
          4. Checking whether a slave is running
          5. Reconfiguring an existing slave
          6. Importing the data into a master
          7. Importing the data into a slave from a master
            1. Dumping data from a master
            2. Dumping data from a slave
          8. Filtering binary log events
            1. The SET SQL_LOG_BIN statement
            2. The @@skip_replication variable
          9. Filtering the replication of events on the slaves
          10. Checksums of the binary log events
          11. Configuring parallel replication
          12. Delaying a slave
        4. Multisource replication
        5. Replication logs
          1. Rotating the binary log
          2. Rotating the relay log
          3. The slave status logs
        6. Checking the replication for errors
          1. The CHECKSUM TABLE statement
          2. The pt-table-checksum tool
          3. Files checksum
          4. Query checksum
        7. Troubleshooting
          1. A slave does not start
          2. A slave lags behind
        8. Summary
      17. 10. Table Partitioning
        1. Support for partitioning
        2. Partitioning types and expressions
          1. Partitioning expressions
          2. Indexes and primary keys
          3. Partition names
          4. Partitioning types
            1. The RANGE type
            2. The LIST type
            3. The COLUMNS keyword
            4. The HASH and KEY types
            5. The LINEAR keyword
            6. Splitting into subpartitions
        3. Administering partitioned tables
          1. Obtaining information about partitions
          2. Changing partitions' definitions
            1. Modifying RANGE and LIST partitions
            2. Modifying HASH and KEY partitions
            3. Copying data between a partition and a table
          3. Maintenance operations statements
        4. Partitions' physical files
        5. Query optimizations
          1. Partition pruning
          2. Partition selection
        6. Summary
      18. 11. Data Sharding
        1. Distributing files between multiple disks
          1. Determining the path of table files
          2. InnoDB logfiles
          3. Configuring the undo log
          4. Configuring the redo log
        2. The FEDERATEDX and CONNECT storage engines
          1. Creating a FEDERATEDX table
          2. Defining a link to a remote server
          3. Creating a MYSQL CONNECT table
            1. Sending SQL statements to a remote server
            2. Merging multiple CONNECT MYSQL tables
        3. The SPIDER storage engine
          1. Explaining the working of the SPIDER storage engine
          2. Installing the SPIDER storage engine
          3. Creating a SPIDER table
          4. Logging of queries and errors
          5. Executing arbitrary statements on remote servers
            1. Explaining the spider_direct_sql() function
            2. Explaining the spider_bg_direct_sql() function
        4. Summary
      19. 12. MariaDB Galera Cluster
        1. MariaDB Galera Cluster key concepts
          1. An overview of Galera Cluster
          2. Synchronous replication
        2. Setting up a cluster
          1. Requirements
          2. Installation
        3. Starting the nodes
          1. Determining a node URL
          2. Node provisioning
            1. State Snapshot Transfer
            2. Incremental State Transfer
          3. The split brain problem
            1. The Galera arbitrator
        4. Configuring the cluster
          1. Explaining the important Galera system variables
            1. Generic cluster settings
            2. Performance and reliability
            3. Settings affecting the behavior of State Snapshot Transfer
            4. Dealing with Galera limitations
          2. Setting the wsrep parameters
        5. Monitoring and troubleshooting
          1. Notification scripts
          2. Checking the status variables
            1. The health of a cluster
            2. Individual node health
            3. The health of a replication
            4. Network performance
        6. Load balancing
        7. Listing the limitations of Galera Cluster
        8. Galera Load Balancer
        9. Summary
      20. Index