1712730.pdf

Cover 1 Copyright and Credits 3 Packt Upsell 4 Contributors 5 Table of Contents 8 Preface 24 Chapter 1: An Introduction to MYSQL 8 29 Overview of MySQL 30 MySQL as a relational database management system 30 License requirements of MySQL8 31 Reliability and scalability 31 Platform compatibility 31 Releases 31 Core features in MySQL 32 Structured database 32 Database storage engines and types 33 Overview of InnoDB 35 Overview of MyISAM 36 Overview of memory 36 Overview of archive 36 Overview of BLACKHOLE as a storage engine 37 Overview of CSV 37 Overview of merge 37 Overview of federated 38 Overview of the NDB cluster 38 Improved features in MySQL 8 39 Transactional data dictionary 41 Roles 41 InnoDB auto increment 42 Invisible indexes 42 Improving descending indexes 43 The SET PERSIST variant 43 Expanded GIS support 43 Default character set 43 Extended bit-wise operations 44 InnoDB Memcached 44 NOWAIT and SKIP LOCKED 44 JSON 45 Cloud 45 Resource management 45 Benefits of using MySQL 8 46 Security 46 Scalability 46 An open source relational database management system 47 High performance 47 High availability 47 Cross-platform capabilities 47 Limitations of MySQL 8 48 Number of tables or databases 48 Table size 48 Joins 49 Windows platform 49 Table column count 49 Row size 49 InnoDB storage engine 50 Limitations of InnoDB storage engine 50 Restrictions 50 Data dictionary 51 Limitations of group replication in MySQL8 52 Limitations of partitioning 52 Constructs prohibition 52 Operators 53 Tables 53 Use cases of MySQL 53 Social media 55 Government 55 Media and entertainment 55 Fraud detection 56 Business mapping 56 E-commerce 56 Summary 57 Chapter 2: Installing and Upgrading MySQL 8 58 The MySQL 8 installation process 58 General installation guide 59 Downloading MySQL 8 60 Verifying the package integrity 60 Using MD5 checksums 60 Using cryptographic signatures 61 Installing MySQL 8 on Microsoft Windows 62 Windows-specific considerations 63 MySQL 8 installation layout 63 Choosing the right installation package 64 The MySQL 8 installer 65 Initial setup information 66 Installation workflow 68 InnoDB cluster sandbox test setup 68 Server configuration 69 MySQL installer product catalog and dashboard 71 MySQL installer console 72 MySQL 8 installation using a ZIP file 72 Installing MySQL 8 on Linux 76 Installation using the Yum repository 76 Installation using the RPM package 78 Installation using the Debian package 79 Post-installation setup for MySQL 8 80 Data directory initialization 80 Securing the initial MySQL account 81 Starting and troubleshooting MySQL 8 services 82 Executing commands to test the server 84 Upgrading MySQL 8 84 Upgrading methods 84 In-place upgrade of MySQL 84 Logical upgrade for MySQL 8 86 Upgrading prerequisites for MySQL 5.7 86 MySQL 8 downgrading 87 Downgrading methods 88 Logical downgrade 88 Manual changes required before downgrading 89 Summary 91 Chapter 3: MySQL 8 – Using Programs and Utilities 92 Overview of MySQL 8 programs 93 MySQL programs in brief 94 Startup programs 94 Installation/upgradation programs 94 Client programs 95 Administrative and utilities programs 96 Environment variables 96 MySQL GUI tool 97 MySQL 8 command-line programs 97 Executing programs from the command line 97 Executing MySQL programs 97 Connecting to the MySQL server 98 Specifying options for programs 99 Options on the command line 100 Modifying program options 101 Modifying options with files 101 group 102 opt_name 103 opt_name=value 103 Include directives 103 Command-line options affecting option file handling 103 Setting program variables with options 103 Setting environment variables 104 Server and server-startup programs 105 mysqld - the MySQL server program 105 Options 105 mysqld_safe - MySQL server startup script 106 mysql.server - MySQL Server startup script 108 mysqld_multi - managing multiple MySQL servers 109 Installation programs 109 comp_err - compiling the MySQL error msg file 109 mysql_secure_installation - improving MySQL installation security 110 mysql_ssl_rsa_setup - creating SSL/RSA files 110 mysql_tzinfo_to_sql - loading the timezone tables 111 mysql_upgrade - checking and upgrading MySQL tables 112 MySQL 8 client programs 112 mysql - the command-line tool 113 mysql options 113 mysql commands 117 help [arg], \h [arg],\? [arg], ? [arg] 117 charset charset_name, \C charset_name 117 clear, \c 117 connect [db_name host_name], \r [db_name host_name] 118 edit, \e 118 exit, \q 118 prompt [str], \R [str] 118 quit, \q 118 status, \s 118 use db_name, \u db_name 118 mysql logging 118 mysql server-side help 119 Executing sql from text files 119 mysqladmin - client for administering a MySQL server 120 mysqlcheck - a table maintenance program 120 mysqldump - a database backup program 121 Performance and scalability 121 mysqlimport - a data import program 122 mysqlpump - a database backup program 122 mysqlsh - the MySQL Shell 123 mysqlshow - showing database, table, and column information 124 mysqlslap - load emulation client 126 MySQL 8 administrative programs 127 ibdsdi - InnoDB tablespace SDI extraction utility 127 innochecksum - offline InnoDB file checksum utility 128 myisam_ftdump - displaying full-text index utility 128 myisamchk - MyISAM table-maintenance utility 129 myisamlog - displaying MyISAM log file content 129 myisampack - generating compressed, read-only MyISAM tables 130 mysql_config_editor - MySQL configuration utility 131 mysqlbinlog - utility for processing binary log files 131 mysqldumpslow - summarizing slow query log files. 132 MySQL 8 environment variables 132 MySQL GUI tools 134 MySQL Workbench 134 MySQL Notifier 135 MySQL Notifier usage 135 Summary 135 Chapter 4: MySQL 8 Data Types 136 Overview of MySQL 8 data types 137 Numeric data types 137 Integer types 138 Fixed point types 139 Floating point types 140 Problems with floating point values 143 Bit value type 144 Bit value literals 145 Practical uses of BIT 146 Type attributes 147 Overflow handling 148 Date and time data types 148 DATE, DATETIME, and TIMESTAMP types 150 MySQL DATETIME functions 153 TIME type 154 Time functions 156 YEAR type 156 Migrating YEAR(2) to YEAR(4) 157 String data types 158 CHAR and VARCHAR data types 159 BINARY and VARBINARY data types 161 BLOB and TEXT data types 162 ENUM data type 163 SET data type 165 JSON data type 166 Partial updates of JSON values 168 Storage requirements for data types 168 Choosing the right data type for column 170 Summary 171 Chapter 5: MySQL 8 Database Management 172 MySQL 8 server administration 173 Server options and different types of variables 173 Server SQL modes 174 Setting the SQL mode 174 The available SQL modes 174 Combination SQL modes 177 Strict SQL mode 178 The IGNORE keyword 179 IPv6 support 180 Server side help 180 The server shutdown process 181 Data directory 182 The system database 182 Data dictionary tables 182 Grant system tables 183 Object information system tables 183 Log system tables 183 The server-side help system tables 183 Time zone system tables 184 Replication system tables 184 Optimizer system tables 184 Other miscellaneous system tables 185 Running multiple instances on a single machine 185 Setting up multiple data directories 186 Running multiple MySQL instances on Windows 186 Components and plugin management 188 MySQL 8 server plugins 188 Installing the plugins 189 Activate plugin 189 Uninstall plugin 190 Getting information about the installed plugins 190 Roles and permissions 190 Caching techniques 191 Globalization 191 Character sets 192 Character set support 192 Adding the character set 194 Configuring the character sets 196 Language selection 196 Time zone settings for MySQL8 197 Locale support 198 MySQL 8 server logs 199 The error log 200 Component configuration 201 Default error log destination configuration 202 Default error log destination on Windows 202 Default error log destination on Unix and Unix-Like systems 203 The general query log 203 The binary log 204 The slow query log 206 The DDL log 207 Server log maintenance 207 Summary 208 Chapter 6: MySQL 8 Storage Engines 209 Overview of storage engines 209 MySQL storage engine architecture 210 Several types of storage engine 211 Overview of the InnoDB storage engine 211 Custom storage engine 211 Several types of storage engines 212 Pluggable storage engine architecture 213 The common database server layer 214 Setting the storage engine 215 The MyISAM storage engine 215 The MEMORY storage engine 217 The CSV storage engine 219 The ARCHIVE storage engine 220 The BLACKHOLE storage engine 221 The MERGE storage engine 222 The FEDERATED storage engine 222 The EXAMPLE storage engine 223 The InnoDB storage engine 224 ACID model 225 Multiversioning 225 Architecture 226 Locking and transaction model 227 Configuration 228 Tablespaces 229 Tables and indexes 229 INFORMATION_SCHEMA tables 230 Memcached plugin 231 Creating a custom storage engine 232 Creating storage engine source files 233 Adding engine-specific variables and parameters 233 Creating the handlerton 234 Handling handler installation 235 Defining filename extensions 236 Creating tables 236 Opening a table 238 Implementing basic table scanning 238 Closing a table 239 Reference for advanced custom storage engine 240 Summary 240 Chapter 7: Indexing in MySQL 8 241 An overview on indexing 242 Uses of indexes in MySQL 8 243 SQL commands related to indexes 244 Creating an INDEX command 244 Spatial index characteristics 245 Non-spatial index characteristics 245 Drop index command 248 SPATIAL index creation and optimization 249 InnoDB and MyISAM index statistics collection 250 Column-level indexing 251 Column indexes 251 Index prefixes 251 FULLTEXT indexes 252 Spatial Indexes 252 Indexes in the MEMORY storage engine 253 Multiple-column indexes 253 B-Tree index 254 Hash index 256 Index extension 257 Using an optimizer for indexes 259 Invisible and descending indexes 260 Invisible index 261 Descending index 263 Summary 265 Chapter 8: Replication in MySQL 8 266 Overview of replication 267 What is MySQL replication? 267 Advantages of MySQL replication 269 Configuring replication 271 Binary log file based replication 271 Replication master configuration 272 REPLICATION SLAVE configuration 275 Adding slaves to replication 276 Global transaction identifiers based replication 277 MySQL multi-source replication 281 Replication administration tasks 282 Implementing replication 285 Replication formats 285 Statement-based versus row-based replication 286 Replication implementation details 287 Replication channels 289 Replication relay and status logs 291 Evaluating replication filtering rules 292 Group replication 293 Primary-secondary replication versus group replication 294 Group replication configuration 295 Group replication use cases 298 Replication solutions 299 Summary 300 Chapter 9: Partitioning in MySQL 8 301 Overview of partitioning 302 Types of partitioning 303 Partitioning management 304 Partition selection and pruning 304 Restrictions and limitations in partitioning 304 Types of partitioning 305 RANGE partitioning 306 LIST partitioning 308 COLUMNS partitioning 311 RANGE COLUMN partitioning 311 LIST COLUMN partitioning 313 HASH partitioning 314 LINEAR HASH partitioning 314 KEY partitioning 315 Subpartitioning 316 Handling NULL in partitioning 316 Partition management 317 RANGE and LIST partition management 317 HASH and KEY partition management 319 Partition maintenance 319 Obtain partition information 321 Partition selection and pruning 321 Partition pruning 322 Partition selection 325 Restrictions and limitations in partitioning 327 Partitioning keys, primary keys, and unique keys 328 Partitioning limitations relating to storage engines 330 Partitioning limitations relating to functions 331 Summary 332 Chapter 10: MySQL 8 – Scalability and High Availability 333 Overview of scalability and high availability in MySQL 8 333 MySQL replication 334 MySQL cluster 335 Oracle MySQL cloud service 335 MySQL with the Solaris cluster 335 Scaling MySQL 8 336 Scaling using cluster 337 Client node 338 Application node 338 Management node 338 Data node 339 Data storage and management of disk-based and in-memory data 339 Automatic and user-defined partitioning of tables or sharding of tables 339 Synchronous data replication between data nodes 339 Data retrieval and transactions 340 Automatic fail over 340 Automatic re-synchronization for self-healing after failure 340 Scaling using memcached in MySQL 8 340 NoSQL APIs 342 Scaling using replication 342 Single server dependancy 343 Performance 343 Backup and recovery 344 Load distribution 344 Asynchronous data replication 344 Geographical data distribution 344 GTID replication 344 ZFS replication 346 Challenges in scaling MySQL 8 346 Business type and flexibility 346 Understand server workload 347 Read-write operation limit 347 Maintenance 348 Master server failure 349 Synchronization 349 Database security 349 Cross node transaction 349 Growing team for development 350 Manage change request 350 Scale-up and scale-out 350 Achieving high availability 351 Purpose of high availability 351 Data availability 352 Security of data 352 Synchronization of data 352 Backup of the data 353 Competitive market 353 Performance 353 Updates in the system 353 Choosing the solution 353 Advantages of high availability 354 Summary 355 Chapter 11: MySQL 8 – Security 356 Overview of security for MySQL 8 356 Common security issues 357 General guidelines 357 Guidelines for a secure password 358 Guidelines for end users 358 Guidelines for administrators 359 Password and logging 360 Secure MYSQL 8 against attackers 360 Security options and variables provided by MySQL 8 361 Security guidelines for client programming 361 Access control in MySQL 8 362 Privileges provided by MySQL 8 363 Grant tables 364 Verification of access control stages 365 Stage 1 - Connection verification 365 Stage 2 - Request verification 365 Account management in MySQL 8 366 Add and remove user accounts 366 Security using roles 367 SET ROLE 368 CREATE ROLE 368 DROP ROLE 368 GRANT 369 REVOKE 369 SET DEFAULT ROLE 370 SHOW GRANTS 370 Password management 371 Encryption in MySQL 8 372 Configuring MySQL 8 to use encrypted connections 372 Server-side configuration for encrypted connections 372 Client-side configuration for encrypted connections 373 Command options for encrypted connections 374 Connect with MySQL 8 remotely from Windows with SSH 374 Security plugins 375 Authentication plugins 375 SHA-2 pluggable authentication 376 Client-side cleartext pluggable authentication 376 No-login pluggable authentication 376 Socket peer-credential pluggable authentication 377 Test pluggable authentication 378 The connection-control plugins 378 CONNECTION_CONTROL 378 Plugin installation 378 Variables related to CONNECTION-CONTROL 379 The password validation plugin 379 Install password validation plugin 380 Variables and options related to the password validation plugin 380 MySQL 8 keyring 382 Install keyring plugin 382 System variables related to keyring plugin 382 Summary 383 Chapter 12: Optimizing MySQL 8 384 Overview of MySQL 8 optimization 385 Optimizing the database 385 Optimizing the hardware 387 Optimizing MySQL 8 servers and clients 387 Optimizing disk I/O 388 Using NFS with MySQL 389 Optimizing the use of memory 389 Optimizing use of the network 393 Optimizing locking operations 395 Performance benchmarking 398 Examining thread information 398 Optimizing database structure 402 Optimizing data size 402 Table columns 403 Row format 403 Indexes 404 Joins 404 Normalization 405 Optimizing MySQL data types 405 Optimizing for many tables 406 Use of an internal temporary table in MySQL 408 Optimizing queries 409 Optimizing SQL statements 409 Optimizing indexes 411 Query execution plan 412 Optimizing tables 414 Optimization for InnoDB tables 414 Optimization for MyISAM tables 415 Optimization for MEMORY tables 415 Leveraging buffering and caching 416 InnoDB buffer pool optimization 416 MyISAM key cache 417 Summary 418 Chapter 13: Extending MySQL 8 419 An overview of extending MySQL 8 420 MySQL 8 internals 420 MySQL 8 plugin API 421 MySQL 8 services for components and plugins 421 Adding new functions to MySQL 8 422 Debugging and porting MySQL 8 422 Extending plugins and using services to call them 422 Writing plugins 423 Component and plugin services 424 The locking service 425 The keyring service 426 Adding new functions 428 Features of a user-defined function interface 428 Adding a new user-defined function 429 Adding a new native function 430 Debugging and porting 432 Debugging MySQL server 433 Debugging MySQL client 434 The DBUG package 435 Summary 436 Chapter 14: MySQL 8 Best Practices and Benchmarking 437 MySQL benchmarking and tools 438 Resource utilization 439 Stretching your benchmarking timelines 439 Replicating production settings 439 Consistency of throughput and latency 439 Sysbench can do more 440 Virtualization world 440 Concurrency 440 Hidden workloads 440 Nerves of your query 440 Benchmarks 441 Best practices for memcached 444 Resource allocation 444 Operating system architecture 444 Default configurations 445 Max object size 445 Backlog queue limit 445 Large pages support 445 Sensitive data 446 Restricting exposure 446 Failover 446 Namespaces 446 Caching mechanism 447 Memcached general statistics 447 Best practices for replication 450 Throughput in group replication 450 Infrastructure sizing 450 Constant throughput 450 Contradictory workloads 451 Write scalability 451 Best practices for data partitioning 453 Horizontal partitioning 454 Vertical partitioning 455 Pruning partitions in MySQL 455 Best practices for queries and indexing 456 Data types 456 Not null 456 Indexing 456 Search fields index 457 Data types and joins 457 Compound index 457 Shortening up primary keys 457 Indexing everything 458 Fetching all data 458 Letting the application do the job 458 Existence of data 458 Limiting yourself 458 Analyzing slow queries 459 Query cost 459 Summary 459 Chapter 15: Troubleshooting MySQL 8 461 MySQL 8 common problems 462 Most common MySQL errors 463 Access denied 464 Can't connect to [local] MySQL server 464 Lost connection to MySQL server 466 Password fails when entered incorrectly 466 Host host_name is blocked 467 Too many connections 467 Out of memory 468 Packet too large 468 The table is full 469 Can't create/write to file 469 Commands out of sync 469 Ignoring user 470 Table tbl_name doesn't exist 470 MySQL 8 server errors 470 Issues with file permissions 470 Resetting the root password 471 MySQL crashes prevention 473 Handling MySQL full disk 475 MySQL temporary files storage 475 MySQL Unix socket file 477 Time zone problems 478 MySQL 8 client errors 479 Case sensitivity in string searches 479 Problems with DATE columns 480 Problems with NULL values 481 MySQL 8 troubleshooting approach 481 Analyzing queries 482 Real-world scenario 483 Summary 485 Other Books You May Enjoy 486 Index 489 Step by step guide to monitor, manage, and secure your database engineKey FeaturesYour companion to master all the administration-related tasks in MySQL 8Ensure high performance and high availability of your MySQL solution using effective replication and backup techniquesA comprehensive guide to performing query optimization, security and a whole host of other administrative tasks in MySQL 8Book DescriptionMySQL 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.What you will learnExplore different MySQL 8 data types based on type of content and storage requirementsLearn best practices for optimal use of the features of MySQL 8Explore globalization configuration and caching techniques to improve performanceCreate a custom storage engine that reflects your system's requirementsLearn various techniques of index implementation for flash memory storagesConfigure and implement replication and learn how to use replication as solutionUnderstand how to make your MySQL 8 solution highly availableTroubleshoot common issues and identify error codes while using MySQL 8Who this book is forThis book is intended for MySQL administrators who are looking for a handy guide covering all the MySQL administration-related tasks. If you are a DBA looking to get started with MySQL administration, this book will also help you. Knowledge of the basic database concepts is required to get started with this book. Step by step guide to monitor, manage, and secure your database engine Key Features Your companion to master all the administration-related tasks in MySQL 8 Ensure high performance and high availability of your MySQL solution using effective replication and backup techniques A comprehensive guide to performing query optimization, security and a whole host of other administrative tasks in MySQL 8 Book Description 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 how you can leverage them to design a high-performance MySQL solution for your organization. This book starts with a brief introduction to the newly introduced features in MySQL 8, followed by 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. 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. What you will learn Understanding different MySQL 8 data types based on type of contents and storage requirements Best practices for optimal use of features in MySQL 8 Explore globalization configuration and caching techniques to improve performance Create custom storage engine as per system requirements Learn various ways of index implementation for flash memory storages Configure and implement replication along with approaches to use replication as solution Understand how to make your MySQL 8 solution highly available Troubleshoot common issues and identify error codes while using MySQL 8 Who this book is for This book is intended for MySQL administrators who are looking for a handy guide covering all the MySQL administration-related tasks. If you are a DBA looking to get started with MySQL administration, this book will also help you. Knowledge of the basic database concepts is required to get started with this book. Step by step guide to monitor, manage, and secure your database engine About This Book Your companion to master all the administration-related tasks in MySQL 8 Ensure high performance and high availability of your MySQL solution using effective replication and backup techniques A comprehensive guide to performing query optimization, security and a whole host of other administrative tasks in MySQL 8 Who This Book Is For This book is intended for MySQL administrators who are looking for a handy guide covering all the MySQL administration-related tasks. If you are a DBA looking to get started with MySQL administration, this book will also help you. Knowledge of the basic database concepts is required to get started with this book. What You Will Learn Understanding different MySQL 8 data types based on type of contents and storage requirements Best practices for optimal use of features in MySQL 8 Explore globalization configuration and caching techniques to improve performance Create custom storage engine as per system requirements Learn various ways of index implementation for flash memory storages Configure and implement replication along with approaches to use replication as solution Understand how to make your MySQL 8 solution highly available Troubleshoot common issues and identify error codes while using MySQL 8 In Detail 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 how you can leverage them to design a high-performance MySQL solution for your organization. This book starts with a brief introduction to the newly introduced features in MySQL 8, followed by 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. 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 an ..