Log In
Or create an account -> 
Imperial Library
  • Home
  • About
  • News
  • Upload
  • Forum
  • Help
  • Login/SignUp

Index
Title Page Copyright and Credits
MySQL 8 Administrator's Guide
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 - the MySQL server program
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
  • ← Prev
  • Back
  • Next →
  • ← Prev
  • Back
  • Next →

Chief Librarian: Las Zenow <zenow@riseup.net>
Fork the source code from gitlab
.

This is a mirror of the Tor onion service:
http://kx5thpx2olielkihfyo4jgjqfb7zx7wxr3sd4xzt26ochei4m6f7tayd.onion