Book Image

MySQL 8 Administrator???s Guide

By : Chintan Mehta, Ankit K Bhavsar, Hetal Oza, Subhash Shah
Book Image

MySQL 8 Administrator???s Guide

By: Chintan Mehta, Ankit K Bhavsar, Hetal Oza, Subhash Shah

Overview of this book

MySQL is one of the most popular and widely used relational databases in the world today. The recently released version 8.0 brings along some major advancements in the way your MySQL solution can be administered. This handbook will be your companion to understand the newly introduced features in MySQL and show you how you can leverage them to design a high-performance MySQL solution for your organization. This book starts with a brief introduction to the new features in MySQL 8, and then quickly jumping onto the crucial administration topics that you will find useful in your day-to-day work. Topics such as migrating to MySQL 8, MySQL benchmarking, achieving high performance by implementing the indexing techniques, and optimizing your queries are covered in this book. You will also learn how to perform replication, scale your MySQL solution and implement effective security techniques. There is also a special section on the common and not so common troubleshooting techniques for effective MySQL administration is also covered in this book. By the end of this highly practical book, you will have all the knowledge you need to tackle any problem you might encounter while administering your MySQL solution.

Related Content you might be interested in

Current Title:

Small book image
MySQL 8 Administrator???s Guide
Chintan Mehta | Ankit K Bhavsar | Hetal Oza | Subhash Shah
Feb, 2018 | 510 pages
Small book image
MySQL 8 for Big Data
Chintan Mehta | Shabbir Challawala | Jaydip Lakhatariya | Kandarp Patel
Oct, 2017 | 296 pages
Small book image
MySQL 8 Cookbook
Karthik Appigatla
Jan, 2018 | 446 pages
Small book image
Advanced MySQL 8
Eric Vanier | Tejaswi Malepati | Birju Shah
Jan, 2019 | 286 pages
Table of Contents (17 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Get in touch
Free Chapter
1
An Introduction to MYSQL 8
An Introduction to MYSQL 8
Overview of MySQL
Core features in MySQL
Improved features in MySQL 8
Benefits of using MySQL 8
Limitations of MySQL 8
Use cases of MySQL
Summary
2
Installing and Upgrading MySQL 8
Installing and Upgrading MySQL 8
The MySQL 8 installation process
Post-installation setup for MySQL 8
Upgrading MySQL 8
MySQL 8 downgrading
Summary
3
MySQL 8 – Using Programs and Utilities
MySQL 8 – Using Programs and Utilities
Overview of MySQL 8 programs
MySQL 8 command-line programs
MySQL 8 client programs
MySQL 8 administrative programs
MySQL 8 environment variables
MySQL GUI tools
Summary
4
MySQL 8 Data Types
MySQL 8 Data Types
Overview of MySQL 8 data types
Numeric data types
Date and time data types
String data types
JSON data type
Storage requirements for data types
Choosing the right data type for column
Summary
5
MySQL 8 Database Management
MySQL 8 Database Management
MySQL 8 server administration
Data directory
The system database
Running multiple instances on a single machine
Components and plugin management
Roles and permissions
Caching techniques
Globalization
MySQL 8 server logs
Summary
6
MySQL 8 Storage Engines
MySQL 8 Storage Engines
Overview of storage engines
Several types of storage engines
The InnoDB storage engine
Creating a custom storage engine
Summary
7
Indexing in MySQL 8
Indexing in MySQL 8
An overview on indexing
Column-level indexing
B-Tree index
Hash index
Index extension
Using an optimizer for indexes
Invisible and descending indexes
Summary
8
Replication in MySQL 8
Replication in MySQL 8
Overview of replication
Configuring replication
Implementing replication
Group replication
Replication solutions
Summary
9
Partitioning in MySQL 8
Partitioning in MySQL 8
Overview of partitioning
Types of partitioning
Partition management
Partition selection and pruning
Restrictions and limitations in partitioning
Summary
10
MySQL 8 – Scalability and High Availability
MySQL 8 – Scalability and High Availability
Overview of scalability and high availability in MySQL 8
Scaling MySQL 8
Challenges in scaling MySQL 8
Achieving high availability
Summary
11
MySQL 8 – Security
MySQL 8 – Security
Overview of security for MySQL 8
Common security issues
Access control in MySQL 8
Account management in MySQL 8
Encryption in MySQL 8
Security plugins
Summary
12
Optimizing MySQL 8
Optimizing MySQL 8
Overview of MySQL 8 optimization
Optimizing MySQL 8 servers and clients
Optimizing database structure
Optimizing queries
Optimizing tables
Leveraging buffering and caching
Summary
13
Extending MySQL 8
Extending MySQL 8
An overview of extending MySQL 8
Extending plugins and using services to call them
Adding new functions
Debugging and porting
Summary
14
MySQL 8 Best Practices and Benchmarking
MySQL 8 Best Practices and Benchmarking
MySQL benchmarking and tools
Best practices for memcached
Best practices for replication
Best practices for data partitioning
Best practices for queries and indexing
Summary
15
Troubleshooting MySQL 8
Troubleshooting MySQL 8
MySQL 8 common problems
MySQL 8 server errors
MySQL 8 client errors
MySQL 8 troubleshooting approach
Real-world scenario
Summary
16
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

Roles and permissions

To put it simply, a role is a collection of privileges. To create a role in MySQL 8, you must have the global CREATE ROLE or CREATE USER privilege. MySQL 8 provides various privileges to attach to roles and users. Refer to https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html for more details on the available privileges.

Now, let's take an example to understand the role creation and privileges assignment. Assume we have a hr_employee table already created in the current database and we want to give access of this table to the hrdepartment role. This dilemma can be resolved by making use of the following code:

CREATE ROLE hrdepartment;
grant all on hr_employee to hrdepartment;

The above code will help us to create the hrdepartment role and grant all the necessary access to it. This topic will be covered in detailed in Chapter 11, Security.

...
Previous Section
End of Section 7
Next Section