售 价:¥
温馨提示:数字商品不支持退换货,不提供源文件,不支持导出打印
为你推荐
Title Page
Copyright and Credits
MySQL 8 Cookbook
Dedication
Packt Upsell
Why subscribe?
PacktPub.com
Contributors
About the author
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
Conventions used
Sections
Getting ready
How to do it…
How it works…
There's more…
See also
Get in touch
Reviews
MySQL 8 - Installing and Upgrading
Introduction
Installing MySQL using YUM/APT
How to do it...
Using YUM repositories
Using APT repositories
Installing MySQL 8.0 using RPM or DEB files
How to do it...
Using the RPM bundle
Using the APT bundle
Installing MySQL on Linux using Generic Binaries
How to do it...
There's more...
Starting or Stopping MySQL 8
How to do it...
Starting the MySQL 8.0 server
Stopping the MySQL 8.0 server
Checking the status of the MySQL 8.0 server
Uninstalling MySQL 8
How to do it...
On YUM-based systems
On APT-based systems
Uninstalling Binaries
Managing the MySQL Server with systemd
How to do it...
Downgrading from MySQL 8.0
How to do it...
In-place Downgrades
Using YUM repositories
Using APT Repositories
Using the RPM or APT bundle
Using Generic Binaries
Logical Downgrades
Using YUM Repositories
Using APT Repositories
Using RPM or APT bundles
Using Generic Binaries
Upgrading to MySQL 8.0
Getting ready
How to do it...
In-place upgrades
YUM-based systems
APT-based systems
Using RPM or APT bundles
Using Generic Binaries
Logical Upgrades
Installing MySQL utilities
How to do it...
On YUM-based systems
On APT-based systems
Using MySQL
Introduction
Connecting to MySQL using the command-line client
Getting ready
How to do it...
See also
Creating databases
How to do it...
See also
Creating tables
How to do it...
Cloning table structure
See also
Inserting, updating, and deleting rows
How to do it...
Inserting
Updating
Deleting
REPLACE, INSERT, ON DUPLICATE KEY UPDATE
Truncating tables
Loading sample data
How to do it...
Selecting data
How to do it...
Selecting columns
Count
Filter based on condition
Operators
Simple pattern matching
Regular expressions
Limiting results
Using the table alias
Sorting results
How to do it...
Grouping results (aggregate functions)
How to do it...
COUNT
SUM
AVERAGE
DISTINCT
Filtering using HAVING
See also
Creating users
How to do it...
See also
Granting and revoking access to users
How to do it...
Granting privileges
Checking grants
Revoking grants
Modifying the mysql.user table
Setting password expiry for users
Locking users
Creating roles for users
Selecting data into a file and table
How to do it...
Saving as a file
Saving as a table
Loading data into a table
How to do it...
Joining tables
How to do it...
Identifying Duplicates using SELF JOIN
Using SUB queries
Finding mismatched rows between tables
Stored procedures
How to do it...
There's more...
See also
Functions
How to do it...
Inbuilt functions
See also
Triggers
How to do it...
See also
Views
How to do it...
Events
How to do it...
Access control
See also
Getting information about databases and tables
How to do it...
TABLES
COLUMNS
FILES
INNODB_SYS_TABLESPACES
INNODB_TABLESTATS
PROCESSLIST
See also
Using MySQL (Advanced)
Introduction
Using JSON
How to do it...
Insert JSON
Retrieve JSON
JSON functions
Pretty view
Searching
Modifying
Removing
Other functions
See also
Common table expressions (CTE)
How to do it...
Non-recursive CTE
Recursive CTE
Generated columns
How to do it...
Window functions
How to do it...
Row number
Partition results
Named windows
First, last, and nth values
Configuring MySQL
Introduction
Using config file
How to do it...
Using global and session variables
How to do it...
Using parameters with startup script
How to do it...
Configuring the parameters
How to do it...
data directory
innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_log_file_size
Changing the data directory
How to do it...
Transactions
Introduction
Performing transactions
How to do it...
Autocommit
Using savepoints
How to do it...
Isolation levels
How to do it...
Read uncommitted
Read committed
Repeatable read
Serializable
Locking
How to do it...
Locking queue
Binary Logging
Introduction
Using binary logging
How to do it...
Enabling binary logs
Disabling binary logs for a session
Move to the next log
Expire binary logs
Binary log format
How to do it...
See also
Extracting statements from a binary log
Getting ready
How to do it...
Observations
Extracting based on time and position
Extracting based on the database
Extracting a row event display
Rewriting a database name
Disabling a binary log for recovery
Displaying events in a binary log file
Ignoring databases to write to a binary log
How to do it...
Example 1
Example 2
Relocating binary logs
How to do it...
Backups
Introduction
Taking backups using mysqldump
How to do it...
Full backup of all databases
Point-in-time recovery
Dumping master binary coordinates
Specific databases and tables
Ignore tables
Specific rows
Backup from a remote server
Backup to rebuild another server with a different schema
Only schema and no data
Only data and no schema
Backup for merging data with other server
REPLACE with new data
IGNORE data
Taking backups using mysqlpump
How to do it...
Parallel processing
Exclude/include database objects using regex
Backup users
Compressed backups
Faster reload
Taking backups using mydumper
How to do it...
Installation
Full backup
Consistent backup
Backup of a single table
Backup of specific databases using regex
Taking backup of a big table using mydumper
Non-blocking backup
Compressed backups
Backing up only data
Taking backups using flat files
How to do it...
Taking backups using XtraBackup
How to do it...
Installation
On CentOS/Red Hat/Fedora
On Debian/Ubuntu
Locking instances for backup
How to do it...
Binary log backup
How to do it...
Restoring Data
Introduction
Recovering from mysqldump and mysqlpump
How to do it...
There's more...
Recovering from mydumper using myloader
How to do it...
Recovering full database
Recover a single database
Recovering a single table
Recovering from flat file backup
How to do it...
Performing point-in-time recovery
How to do it...
mysqldump or mysqlpump
mydumper
Replication
Introduction
Setting up replication
How to do it...
Setting up master-master replication
How to do it...
Setting up multi-source replication
How to do it...
Setting up replication filters
How to do it...
Replicate a database only
Replicate specific tables
Ignore a database
Ignore specific tables
See also
Switching slave from master-slave to chain replication
How to do it...
Switching the slave from chain replication to master-slave
How to do it...
Setting up delayed replication
How to do it...
Setting up GTID replication
How to do it...
Setting up semi-synchronous replication
How to do it...
Table Maintenance
Introduction
Installing Percona Toolkit
How to do it...
On Debian/Ubuntu
On CentOS/Red Hat/Fedora
Altering tables
How to do it...
Moving tables across databases
How to do it...
Altering tables using an online schema change tool
How it works...
How to do it...
Archiving tables
How to do it...
Purging data
Archiving data
Copying data
See also
Cloning tables
How to do it...
Partitioning tables
How to do it...
RANGE partitioning
Removing partitioning
RANGE COLUMNS partitioning
LIST and LIST COLUMNS partitioning
HASH and LINEAR HASH partitioning
KEY and LINEAR KEY partitioning
Subpartitioning
Partition pruning and selection
How to do it...
Partition pruning
Partition selection
Partition management
How to do it...
ADD partitions
Reorganizing partitions
DROP partitions
TRUNCATE partitions
Managing HASH and KEY partitions
Other operations
Partition information
How to do it...
Using SHOW CREATE TABLE
Using SHOW TABLE STATUS
Using EXPLAIN
Querying the INFORMATION_SCHEMA.PARTITIONS table
Efficiently managing time to live and soft delete rows
How it works...
How to do it...
Managing Tablespace
Introduction
Changing the number or size of InnoDB redo log files
How to do it...
Resizing the InnoDB system tablespace
How to do it...
Increasing the InnoDB system tablespace
Shrinking the InnoDB system tablespace
Creating file-per-table tablespaces outside the data directory
How to do it...
Copying file-per-table tablespaces to another instance
How to do it...
Copy full table
Copying individual partitions of a table
See also
Managing UNDO tablespace
How to do it...
Managing general tablespace
How to do it...
Create a general tablespace
Adding tables to a general tablespace
Moving non-partitioned tables between tablespaces
Managing partitioned tables in a general tablespace
Dropping general tablespace
Compressing InnoDB tables
How to do it...
Enabling Compression for file_per_table Tables
Disabling Compression for file_per_table Tables
Enabling Compression for General Tablespace
Managing Logs
Introduction
Managing the error log
How to do it...
Configuring the error log
Rotating the error log
Using the system log for logging
Error logging in JSON format
Managing the general query log and slow query log
How to do it...
General query log
Slow query log
Selecting query log output destinations
Managing the binary logs
How to do it...
Performance Tuning
Introduction
The explain plan
How to do it...
Using EXPLAIN
Using EXPLAIN JSON
Using EXPLAIN for connection
Benchmarking queries and the server
How to do it...
Adding indexes
Primary key (clustered index) and secondary indexes
How to do it...
Add index
UNIQUE index
Prefix index
Drop index
Index on generated columns
Invisible index
How to do it...
Descending index
How to do it...
Analyzing slow queries using pt-query-digest
How to do it...
Slow query log
General query log
Process list
Binary log
TCP dump
See also
Optimizing datatypes
How to do it...
Removing duplicate and redundant indexes
How to do it...
pt-duplicate-key-checker
mysqlindexcheck
Checking index usage
How to do it...
Controlling the query optimizer
How to do it...
optimizer_search_depth
How to know that the query is spending time in evaluating plans?
optimizer_switch
Optimizer hints
Adjusting the optimizer cost model
Using index hints
How to do it...
Indexing for JSON using generated columns
How to do it...
Using resource groups
How to do it...
Alter and drop resource group
Using performance_schema
How to do it...
Enable/disable performance_schema
Enable/disable consumers and instruments
performance_schema tables
Using the sys schema
How to do it...
Statement by type (INSERT and SELECT) from each host
Statement by type from each user
Redundant indexes
Unused indexes
Statements executed from each host
Table statistics
Table statistics with buffer
Statement analysis
Security
Introduction
Securing installation
How to do it...
The FILE privilege
Restricting networks and users
How to do it...
Password-less authentication using mysql_config_editor
How to do it...
Resetting the root password
How to do it...
Using init-file
Using --skip-grant-tables
Setting up encrypted connections using X509
How to do it...
Setting up SSL replication
How to do it...
买过这本书的人还买过
读了这本书的人还在读
同类图书排行榜