- 384 pages
- English
- ePUB (mobile friendly)
- Available on iOS & Android
eBook - ePub
Mastering MariaDB
About this book
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.
Trusted by 375,005 students
Access to over 1 million titles for a fair monthly price.
Study more efficiently using our study tools.
Information
Mastering MariaDB
Table of Contents
Mastering MariaDB
Credits
About the Author
About the Reviewers
www.PacktPub.com
Support files, eBooks, discount offers, and more
Why subscribe?
Free access for Packt account holders
Preface
What this book covers
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Downloading the example code
Errata
Piracy
Questions
1. Understanding the Essentials of MariaDB
The MariaDB architecture
The command-line client
Storage engines
XtraDB and InnoDB
TokuDB
MyISAM and Aria
Other engines
Logs
MariaDB caches
InnoDB data structures
Authentication and security
The information_schema database
The performance_schema database
Compatibility with MySQL and other DBMS
MariaDB resources
Summary
2. Debugging
Understanding error conditions in MariaDB
The SQLSTATE value
The error number
The error message
The custom errors
The SHOW WARNINGS and SHOW ERRORS statements
The diagnostics area
The GET DIAGNOSTICS statement
The error log
The error log format
A troubleshooting example with the error log
System logs
The general query log
The file format of the general query log
The general_log table
Debugging examples with the general query log
Maintenance of the server logs
Flushing logs
Rotating the file-based logs
Rotating the table-based logs
The SQL_ERROR_LOG plugin
Tips on debugging stored programs
Debugging stored programs using the SQL_ERROR_LOG plugin
Summary
3. Optimizing Queries
The slow query log
The file format of the slow query log
The slow_log table
Explaining the pt-query-digest command from Percona Toolkit
Introducing indexes
Table statistics
Storage engines and indexes
Working with the EXPLAIN statement
Understanding the output of EXPLAIN
Simple SELECT statements
Internal temporary tables or files
The UNION queries
Simple index access methods
Index optimizations of the JOIN clause
Optimization of subqueries
Summary
4. Transactions and Locks
The InnoDB locks
The lock modes
Lock types
Diagnosing locks
Locks used by various SQL statements
Reads consistency
The non-repeatable reads
Phantom rows
Consistent reads
Locking reads
Deadlocks
Transactions
The transactions life cycle
Transactions isolation levels
The READ UNCOMMITTED isolation level
The READ COMMITTED isolation level
The REPEATABLE READ isolation level
The SERIALIZABLE isolation level
Transactions access modes
Metadata locks
Summary
5. Users and Connections
User accounts
Setting permissions using roles
Connecting MariaDB through Secure Socket Layer
Authentication plugins
Activating the pool of threads
Monitoring the pool of threads
Configuring the threadpool implementation
Configuring the pool of threads on Unix
Configuring the pool of threads on Windows
Tuning the configuration variables
Unblocking a blocked pool of threads
Monitoring connections
States of the process
Aborting connections
Summary
6. Caches
InnoDB caches
InnoDB pages
The InnoDB buffer pool
Old and new pages
Buffer pool instances
Dirty pages
The read ahead optimization
Diagnosing the buffer pool performance
Dumping and loading the buffer pool
The InnoDB change buffer
Explaining the doublewrite buffer
MyISAM key cache
LRU and the midpoint insertion strategy
Key cache instances
Segmented key cache
Preloading indexes into the cache
Aria page cache
The query cache explained
Configuring the query cache
Information on the status of the query cache
Explaining the subquery cache
Alternative query caching methods
The table open cache
Per-session buffers
Summary
7. InnoDB Compressed Tables
An overview of the InnoDB compression
InnoDB compression requirements
Explaining the file-per-table mode
A brief on InnoDB file formats
Creating InnoDB compressed tables
Explaining the implementation of the InnoDB compression
Monitoring the InnoDB compression performance
The INNODB_CMPMEM table
The INNODB_CMP_PER_INDEX table
The INNODB_CMP table
Other compression solutions
Summary
8. Backup and Disaster Recovery
Types of backups
Logical and physical backups
Hot and cold backups
Complete and incremental backups
Backups and replication
Steps to be followed before performing backups
Creating a dump file with mysqldump
Delimited text backups
The --tab option of the mysqldump command
Loading a dump file with the mysqlimport command
Creating a text-delimited file with the SELECT … INTO OUTFILE command
Dumping a table definition with the SHOW CREATE TABLE command
Loading a dump file with the LOAD DATA INFILE statement
Separator options and clauses
An example to create and restore dump files
Performing a backup using a CONNECT or CSV engine
Physical backups
Which files should be copied?
Table files
Logfiles
Configuration files
Hot physical backups
Filesystem snapshots
Incremental physical backups with the rsync command
Copying files when the server is running
Using the binary log for incremental backups
Percona XtraBackup
Performing backups
Complete backups
Partial backups
Preparing backups
Preparing complete backups
Preparing partial backups
Restoring backups
Restoring complete backups
Restoring partial backups
Securing backups
Repairing tables
Recovering InnoDB tables
Checking tables
Transaction logs
Forcing data recovery
Repairing non-InnoDB tables
The CHECK TABLE statement
The REPAIR TABLE statement
Repairing CSV tables
Repairing tables with the myisamchk and aria_chk tools
MyISAM and Aria autorecovery
Summary
9. Replication
...Table of contents
- Mastering MariaDB
Frequently asked questions
Yes, you can cancel anytime from the Subscription tab in your account settings on the Perlego website. Your subscription will stay active until the end of your current billing period. Learn how to cancel your subscription
No, books cannot be downloaded as external files, such as PDFs, for use outside of Perlego. However, you can download books within the Perlego app for offline reading on mobile or tablet. Learn how to download books offline
Perlego offers two plans: Essential and Complete
- Essential is ideal for learners and professionals who enjoy exploring a wide range of subjects. Access the Essential Library with 800,000+ trusted titles and best-sellers across business, personal growth, and the humanities. Includes unlimited reading time and Standard Read Aloud voice.
- Complete: Perfect for advanced learners and researchers needing full, unrestricted access. Unlock 1.4M+ books across hundreds of subjects, including academic and specialized titles. The Complete Plan also includes advanced features like Premium Read Aloud and Research Assistant.
We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 990+ topics, we’ve got you covered! Learn about our mission
Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more about Read Aloud
Yes! You can use the Perlego app on both iOS and Android devices to read anytime, anywhere — even offline. Perfect for commutes or when you’re on the go.
Please note we cannot support devices running on iOS 13 and Android 7 or earlier. Learn more about using the app
Please note we cannot support devices running on iOS 13 and Android 7 or earlier. Learn more about using the app
Yes, you can access Mastering MariaDB by Federico Razzoli in PDF and/or ePUB format, as well as other popular books in Computer Science & Databases. We have over one million books available in our catalogue for you to explore.