Name: MySQL 8 Administrator’s Guide
Author: Chintan Mehta Ankit Bhavsar Hetal Oza Subhash Shah
ISBN: 9781788393843

微信读书书城MySQL 8 Administrator’s Guide

首页我的书架登录

翻译完成

切换

MySQL 8 Administrator’s Guide

Chintan Mehta Ankit Bhavsar Hetal Oza Subhash Shah

扉页
coverpage
+书签
Title Page
Packt Upsell
Why subscribe?
PacktPub.com
Contributors
About the authors
About the reviewers
Packt is searching for authors like you
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Conventions used
Get in touch
Reviews
An Introduction to MYSQL 8
Overview of MySQL
MySQL as a relational database management system
License requirements of MySQL8
Reliability and scalability
Platform compatibility
Releases
Core features in MySQL
Structured database
Database storage engines and types
Overview of InnoDB
Overview of MyISAM
Overview of memory
Overview of archive
Overview of BLACKHOLE as a storage engine
Overview of CSV
Overview of merge
Overview of federated
Overview of the NDB cluster
Improved features in MySQL 8
Transactional data dictionary
Roles
InnoDB auto increment
Invisible indexes
Improving descending indexes
The SET PERSIST variant
Expanded GIS support
Default character set
Extended bit-wise operations
InnoDB Memcached
NOWAIT and SKIP LOCKED
JSON
Cloud
Resource management
Benefits of using MySQL 8
Security
Scalability
An open source relational database management system
High performance
High availability
Cross-platform capabilities
Limitations of MySQL 8
Number of tables or databases
Table size
Joins
Windows platform
Table column count
Row size
InnoDB storage engine
Limitations of InnoDB storage engine
Restrictions
Data dictionary
Limitations of group replication in MySQL8
Limitations of partitioning
Constructs prohibition
Operators
Tables
Use cases of MySQL
Social media
Government
Media and entertainment
Fraud detection
Business mapping
E-commerce
Summary
Installing and Upgrading MySQL 8
The MySQL 8 installation process
General installation guide
Downloading MySQL 8
Verifying the package integrity
Using MD5 checksums
Using cryptographic signatures
Installing MySQL 8 on Microsoft Windows
Windows-specific considerations
MySQL 8 installation layout
Choosing the right installation package
The MySQL 8 installer
Initial setup information
Installation workflow
InnoDB cluster sandbox test setup
Server configuration
MySQL installer product catalog and dashboard
MySQL installer console
MySQL 8 installation using a ZIP file
Installing MySQL 8 on Linux
Installation using the Yum repository
Installation using the RPM package
Installation using the Debian package
Post-installation setup for MySQL 8
Data directory initialization
Securing the initial MySQL account
Starting and troubleshooting MySQL 8 services
Executing commands to test the server
Upgrading MySQL 8
Upgrading methods
In-place upgrade of MySQL
Logical upgrade for MySQL 8
Upgrading prerequisites for MySQL 5.7
MySQL 8 downgrading
Downgrading methods
Logical downgrade
Manual changes required before downgrading
Summary
MySQL 8 – Using Programs and Utilities
Overview of MySQL 8 programs
MySQL programs in brief
Startup programs
Installation/upgradation programs
Client programs
Administrative and utilities programs
Environment variables
MySQL GUI tool
MySQL 8 command-line programs
Executing programs from the command line
Executing MySQL programs
Connecting to the MySQL server
Specifying options for programs
Options on the command line
Modifying program options
Modifying options with files
group
opt_name
opt_name=value
Include directives
Command-line options affecting option file handling
Setting program variables with options
Setting environment variables
Server and server-startup programs
mysqld –
Options
mysqld_safe – MySQL server startup script
mysql.server – MySQL Server startup script
mysqld_multi – managing multiple MySQL servers
Installation programs
comp_err – compiling the MySQL error msg file
mysql_secure_installation – improving MySQL installation security
mysql_ssl_rsa_setup – creating SSL/RSA files
mysql_tzinfo_to_sql – loading the timezone tables
mysql_upgrade - checking and upgrading MySQL tables
MySQL 8 client programs
mysql - the command-line tool
mysql options
mysql commands
help [arg], h [arg], ? [arg], ? [arg]
charset charset_name, C charset_name
clear, c
connect [db_name host_name], r [db_name host_name]
edit, e
exit, q
prompt [str], R [str]
quit, q
status, s
use db_name, u db_name
mysql logging
mysql server-side help
Executing sql from text files
mysqladmin - client for administering a MySQL server
mysqlcheck - a table maintenance program
mysqldump - a database backup program
Performance and scalability
mysqlimport - a data import program
mysqlpump - a database backup program
mysqlsh - the MySQL Shell
mysqlshow - showing database, table, and column information
mysqlslap - load emulation client
MySQL 8 administrative programs
ibdsdi - InnoDB tablespace SDI extraction utility
innochecksum - offline InnoDB file checksum utility
myisam_ftdump - displaying full-text index utility
myisamchk - MyISAM table-maintenance utility
myisamlog - displaying MyISAM log file content
myisampack - generating compressed, read-only MyISAM tables
mysql_config_editor - MySQL configuration utility
mysqlbinlog - utility for processing binary log files
mysqldumpslow - summarizing slow query log files.
MySQL 8 environment variables
MySQL GUI tools
MySQL Workbench
MySQL Notifier
MySQL Notifier usage
Summary
MySQL 8 Data Types
Overview of MySQL 8 data types
Numeric data types
Integer types
Fixed point types
Floating point types
Problems with floating point values
Bit value type
Bit value literals
Practical uses of BIT
Type attributes
Overflow handling
Date and time data types
DATE, DATETIME, and TIMESTAMP types
MySQL DATETIME functions
TIME type
Time functions
YEAR type
Migrating YEAR(2) to YEAR(4)
String data types
CHAR and VARCHAR data types
BINARY and VARBINARY data types
BLOB and TEXT data types
ENUM data type
SET data type
JSON data type
Partial updates of JSON values
Storage requirements for data types
Choosing the right data type for column
Summary
MySQL 8 Database Management
MySQL 8 server administration
Server options and different types of variables
Server SQL modes
Setting the SQL mode
The available SQL modes
Combination SQL modes
Strict SQL mode
The IGNORE keyword
IPv6 support
Server side help
The server shutdown process
Data directory
The system database
Data dictionary tables
Grant system tables
Object information system tables
Log system tables
The server-side help system tables
Time zone system tables
Replication system tables
Optimizer system tables
Other miscellaneous system tables
Running multiple instances on a single machine
Setting up multiple data directories
Running multiple MySQL instances on Windows
Components and plugin management
MySQL 8 server plugins
Installing the plugins
Activate plugin
Uninstall plugin
Getting information about the installed plugins
Roles and permissions
Caching techniques
Globalization
Character sets
Character set support
Adding the character set
Configuring the character sets
Language selection
Time zone settings for MySQL8
Locale support
MySQL 8 server logs
The error log
Component configuration
Default error log destination configuration
Default error log destination on Windows
Default error log destination on Unix and Unix-Like systems
The general query log
The binary log
The slow query log
The DDL log
Server log maintenance
Summary
MySQL 8 Storage Engines
Overview of storage engines
MySQL storage engine architecture
Several types of storage engine
Overview of the InnoDB storage engine
Custom storage engine
Several types of storage engines
Pluggable storage engine architecture
The common database server layer
Setting the storage engine
The MyISAM storage engine
The MEMORY storage engine
The CSV storage engine
The ARCHIVE storage engine
The BLACKHOLE storage engine
The MERGE storage engine
The FEDERATED storage engine
The EXAMPLE storage engine
The InnoDB storage engine
ACID model
Multiversioning
Architecture
Locking and transaction model
Configuration
Tablespaces
Tables and indexes
INFORMATION_SCHEMA tables
Memcached plugin
Creating a custom storage engine
Creating storage engine source files
Adding engine-specific variables and parameters
Creating the handlerton
Handling handler installation
Defining filename extensions
Creating tables
Opening a table
Implementing basic table scanning
Closing a table
Reference for advanced custom storage engine
Summary
Indexing in MySQL 8
An overview on indexing
Uses of indexes in MySQL 8
SQL commands related to indexes
Creating an INDEX command
Spatial index characteristics
Non-spatial index characteristics
Drop index command
SPATIAL index creation and optimization
InnoDB and MyISAM index statistics collection
Column-level indexing
Column indexes
Index prefixes
FULLTEXT indexes
Spatial Indexes
Indexes in the MEMORY storage engine
Multiple-column indexes
B-Tree index
Hash index
Index extension
Using an optimizer for indexes
Invisible and descending indexes
Invisible index
Descending index
Summary
Replication in MySQL 8
Overview of replication
What is MySQL replication?
Advantages of MySQL replication
Configuring replication
Binary log file based replication
Replication master configuration
REPLICATION SLAVE configuration
Adding slaves to replication
Global transaction identifiers based replication
MySQL multi-source replication
Replication administration tasks
Implementing replication
Replication formats
Statement-based versus row-based replication
Replication implementation details
Replication channels
Replication relay and status logs
Evaluating replication filtering rules
Group replication
Primary-secondary replication versus group replication
Group replication configuration
Group replication use cases
Replication solutions
Summary
Partitioning in MySQL 8
Overview of partitioning
Types of partitioning
Partitioning management
Partition selection and pruning
Restrictions and limitations in partitioning
Types of partitioning
RANGE partitioning
LIST partitioning
COLUMNS partitioning
RANGE COLUMN partitioning
LIST COLUMN partitioning
HASH partitioning
LINEAR HASH partitioning
KEY partitioning
Subpartitioning
Handling NULL in partitioning
Partition management
RANGE and LIST partition management
HASH and KEY partition management
Partition maintenance
Obtain partition information
Partition selection and pruning
Partition pruning
Partition selection
Restrictions and limitations in partitioning
Partitioning keys, primary keys, and unique keys
Partitioning limitations relating to storage engines
Partitioning limitations relating to functions
Summary
MySQL 8 – Scalability and High Availability
Overview of scalability and high availability in MySQL 8
MySQL replication
MySQL cluster
Oracle MySQL cloud service
MySQL with the Solaris cluster
Scaling MySQL 8
Scaling using cluster
Client node
Application node
Management node
Data node
Data storage and management of disk-based and in-memory data
Automatic and user-defined partitioning of tables or sharding of tables
Synchronous data replication between data nodes
Data retrieval and transactions
Automatic fail over
Automatic re-synchronization for self-healing after failure
Scaling using memcached in MySQL 8
NoSQL APIs
Scaling using replication
Single server dependancy
Performance
Backup and recovery
Load distribution
Asynchronous data replication
Geographical data distribution
GTID replication
ZFS replication
Challenges in scaling MySQL 8
Business type and flexibility
Understand server workload
Read-write operation limit
Maintenance
Master server failure
Synchronization
Database security
Cross node transaction
Growing team for development
Manage change request
Scale-up and scale-out
Achieving high availability
Purpose of high availability
Data availability
Security of data
Synchronization of data
Backup of the data
Competitive market
Performance
Updates in the system
Choosing the solution
Advantages of high availability
Summary
MySQL 8 – Security
Overview of security for MySQL 8
Common security issues
General guidelines
Guidelines for a secure password
Guidelines for end users
Guidelines for administrators
Password and logging
Secure MYSQL 8 against attackers
Security options and variables provided by MySQL 8
Security guidelines for client programming
Access control in MySQL 8
Privileges provided by MySQL 8
Grant tables
Verification of access control stages
Stage 1 - Connection verification
Stage 2 - Request verification
Account management in MySQL 8
Add and remove user accounts
Security using roles
SET ROLE
CREATE ROLE
DROP ROLE
GRANT
REVOKE
SET DEFAULT ROLE
SHOW GRANTS
Password management
Encryption in MySQL 8
Configuring MySQL 8 to use encrypted connections
Server-side configuration for encrypted connections
Client-side configuration for encrypted connections
Command options for encrypted connections
Connect with MySQL 8 remotely from Windows with SSH
Security plugins
Authentication plugins
SHA-2 pluggable authentication
Client-side cleartext pluggable authentication
No-login pluggable authentication
Socket peer-credential pluggable authentication
Test pluggable authentication
The connection-control plugins
CONNECTION_CONTROL
Plugin installation
Variables related to CONNECTION-CONTROL
The password validation plugin
Install password validation plugin
Variables and options related to the password validation plugin
MySQL 8 keyring
Install keyring plugin
System variables related to keyring plugin
Summary
Optimizing MySQL 8
Overview of MySQL 8 optimization
Optimizing the database
Optimizing the hardware
Optimizing MySQL 8 servers and clients
Optimizing disk I/O
Using NFS with MySQL
Optimizing the use of memory
Optimizing use of the network
Optimizing locking operations
Performance benchmarking
Examining thread information
Optimizing database structure
Optimizing data size
Table columns
Row format
Indexes
Joins
Normalization
Optimizing MySQL data types
Optimizing for many tables
Use of an internal temporary table in MySQL
Optimizing queries
Optimizing SQL statements
Optimizing indexes
Query execution plan
Optimizing tables
Optimization for InnoDB tables
Optimization for MyISAM tables
Optimization for MEMORY tables
Leveraging buffering and caching
InnoDB buffer pool optimization
MyISAM key cache
Summary
Extending MySQL 8
An overview of extending MySQL 8
MySQL 8 internals
MySQL 8 plugin API
MySQL 8 services for components and plugins
Adding new functions to MySQL 8
Debugging and porting MySQL 8
Extending plugins and using services to call them
Writing plugins
Component and plugin services
The locking service
The keyring service
Adding new functions
Features of a user-defined function interface
Adding a new user-defined function
Adding a new native function
Debugging and porting
Debugging MySQL server
Debugging MySQL client
The DBUG package
Summary
MySQL 8 Best Practices and Benchmarking
MySQL benchmarking and tools
Resource utilization
Stretching your benchmarking timelines
Replicating production settings
Consistency of throughput and latency
Sysbench can do more
Virtualization world
Concurrency
Hidden workloads
Nerves of your query
Benchmarks
Best practices for memcached
Resource allocation
Operating system architecture
Default configurations
Max object size
Backlog queue limit
Large pages support
Sensitive data
Restricting exposure
Failover
Namespaces
Caching mechanism
Memcached general statistics
Best practices for replication
Throughput in group replication
Infrastructure sizing
Constant throughput
Contradictory workloads
Write scalability
Best practices for data partitioning
Horizontal partitioning
Vertical partitioning
Pruning partitions in MySQL
Best practices for queries and indexing
Data types
Not null
Indexing
Search fields index
Data types and joins
Compound index
Shortening up primary keys
Indexing everything
Fetching all data
Letting the application do the job
Existence of data
Limiting yourself
Analyzing slow queries
Query cost
Summary
Troubleshooting MySQL 8
MySQL 8 common problems
Most common MySQL errors
Access denied
Can t connect to [local] MySQL server
Lost connection to MySQL server
Password fails when entered incorrectly
Host host_name is blocked
Too many connections
Out of memory
Packet too large
The table is full
Can t create/write to file
Commands out of sync
Ignoring user
Table tbl_name doesn t exist
MySQL 8 server errors
Issues with file permissions
Resetting the root password
MySQL crashes prevention
Handling MySQL full disk
MySQL temporary files storage
MySQL Unix socket file
Time zone problems
MySQL 8 client errors
Case sensitivity in string searches
Problems with DATE columns
Problems with NULL values
MySQL 8 troubleshooting approach
Analyzing queries
Real-world scenario
Summary
Other Books You May Enjoy
Leave a review - let other readers know what you think

简介

版权

MySQL 8 Administrator’s Guide