Kumar Y V Ravi and others / Кумар И В Рави и другие - Mastering MySQL Administrati
|
||||||||||||||||||||||||||||||||||||||||||
|
Home » Books and magazines » Computer literature » Database management system |
| DL-List and Torrent activity | |
| Size: 18 MB | Registered: 8 months 7 days | Completed: 2 times | |
|
Seeder not seen: 5 months 3 days |
|
|
|
| Author | Message | |||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Microsoft SQL Server ® Gender: Longevity: 9 years Posts: 677
|
Mastering MySQL Administration: High Availability, Security, Performance, and Efficiency / Осваиваем администрирование MySQL: Высокая доступность, безопасность, производительность и результативность
Год издания: 2024 Автор: Kumar Y V Ravi and others / Кумар И В Рави и другие Издательство: Apress Media LLC ISBN: 979-8-8688-0252-2 Язык: Английский Формат: PDF Качество: Издательский макет или текст (eBook) Интерактивное оглавление: Да Количество страниц: 750 Описание: This book is your one-stop resource on MySQL database installation and server management for administrators. It covers installation, upgrades, monitoring, high availability, disaster recovery, security, and performance and troubleshooting. You will become fluent in MySQL 8.2, the latest version of the highly scalable and robust relational database system. With a hands-on approach, the book offers step-by-step guidance on installing, upgrading, and establishing robust high availability and disaster recovery capabilities for MySQL databases. It also covers high availability with InnoDB and NDB clusters, MySQL routers and enterprise MySQL tools, along with robust security design and performance techniques. Throughout, the authors punctuate concepts with examples taken from their experience with large-scale implementations at companies such as Meta and American Airlines, anchoring this practical guide to MySQL 8.2 administration in the real world. What YouWill Learn Understand MySQL architecture and best practices for administration of MySQL server Configure high availability, replication, disaster recovery with InnoDB and NDB engines Back up and restore with MySQL utilities and tools, and configure the database for zero data loss Troubleshoot with steps for real-world critical errors and detailed solutions Who This Book Is For Technical professionals, database administrators, developers, and engineers seeking to optimize MySQL databases for scale, security, and performance Эта книга - ваш универсальный ресурс по установке базы данных MySQL и управлению сервером для администраторов. В ней рассказывается об установке, обновлениях, мониторинге, обеспечении высокой доступности, аварийном восстановлении, безопасности, производительности и устранении неполадок. Вы будете свободно владеть MySQL 8.2, последней версией высокомасштабируемой и надежной системы реляционных баз данных. Благодаря практическому подходу, в книге предлагается пошаговое руководство по установке, обновлению и созданию надежных возможностей обеспечения высокой доступности и аварийного восстановления баз данных MySQL. Он также обеспечивает высокую доступность с помощью кластеров InnoDB и NDB, маршрутизаторов MySQL и корпоративных инструментов MySQL, а также надежные методы обеспечения безопасности и производительности. Повсюду авторы дополняют концепции примерами, взятыми из их опыта крупномасштабных внедрений в таких компаниях, как Meta и American Airlines, закрепляя это практическое руководство по администрированию MySQL 8.2 в реальном мире. Чему вы научитесь Понимание архитектуры MySQL и лучших практик администрирования сервера MySQL Настройте высокую доступность, репликацию и аварийное восстановление с помощью модулей InnoDB и NDB NDB engine Создавайте резервные копии и восстанавливайте их с помощью утилит и инструментов MySQL, а также настраивайте базу данных таким образом, чтобы избежать потери данных Пошаговое руководство по устранению реальных критических ошибок и подробные решения Для кого предназначена эта книга Технические специалисты, администраторы баз данных, разработчики и инженеры, стремящиеся оптимизировать базы данных MySQL с точки зрения масштабирования, безопасности и производительности. ОглавлениеAbout the Authors ....................................................................................................xvAbout the Technical Reviewer ................................................................................xix Acknowledgments ..................................................................................................xxi Foreword ...............................................................................................................xxv Chapter 1: MySQL Installation and Upgrade ............................................................. 1 Introduction ................................................................................................................................... 1 Installing MySQL 8.0 ..................................................................................................................... 1 Installing MySQL 8.0 on Linux Using the YUM Repository ............................................................. 3 Installing MySQL 8.0 on Linux Using Binary Distribution ............................................................ 15 Installing MySQL 8.0 on Linux Using Binary Distribution – Commercial Edition ......................... 28 Installing MySQL 8.0 on Microsoft Windows ............................................................................... 41 Installing MySQL 8.0 on Docker .................................................................................................. 70 Upgrading MySQL 5.7 to MySQL 8.0.34 ...................................................................................... 79 Overview ..................................................................................................................................... 79 Assumptions ............................................................................................................................... 80 Prerequisites ............................................................................................................................... 80 Upgrade Steps ............................................................................................................................ 83 Downgrading MySQL ................................................................................................................... 98 MySQL 8.2 ................................................................................................................................... 98 What Is New in MySQL 8.2 .................................................................................................... 98 What Is Deprecated in MySQL 8.2 ......................................................................................... 99 Summary .................................................................................................................................. 100 Chapter 2: MySQL Utilities .................................................................................... 101 Introduction ............................................................................................................................... 101 MySQL ....................................................................................................................................... 102 MySQL Dump ............................................................................................................................ 103 How to Back Up a Single Database ........................................................................................... 104 How to Back Up Multiple Databases ......................................................................................... 104 How to Back Up All the Databases ............................................................................................ 104 Useful command line options with MySQL Dump ................................................................ 105 How to Restore Single MySQL Database .................................................................................. 105 How to Restore All MySQL Databases ....................................................................................... 106 MySQL Pump ............................................................................................................................. 106 MySQL Backup .......................................................................................................................... 107 MySQL Check ............................................................................................................................ 111 MySQL Binlog ............................................................................................................................ 112 MySQL Safe ............................................................................................................................... 113 MySQL Dump Slow .................................................................................................................... 114 MySQL Show ............................................................................................................................. 114 MySQL Secure Installation ........................................................................................................ 116 MySQL Import ........................................................................................................................... 117 MySQL Config ........................................................................................................................... 119 MySQL Config Editor ................................................................................................................. 120 MySQL Slap ............................................................................................................................... 121 MySQL Router ........................................................................................................................... 122 MySQL Shell .............................................................................................................................. 123 MySQL Workbench .................................................................................................................... 126 Summary .................................................................................................................................. 128 Chapter 3: MySQL Server Administration ............................................................. 129 Introduction ............................................................................................................................... 129 Configuring the MySQL Server .................................................................................................. 129 MySQL Data Directory ............................................................................................................... 132 Startup and Shutdown of MySQL Server .................................................................................. 139 Connection Management .......................................................................................................... 140 Storage Engines ........................................................................................................................ 145 Multiple Instance Management ................................................................................................ 171 Summary .................................................................................................................................. 188 Chapter 4: MySQL Tablespace Management and Partitioning .............................. 189 Introduction ............................................................................................................................... 189 Tablespaces .............................................................................................................................. 189 Tablespace Management – Resizing a System Tablespace ...................................................... 190 Tablespace Management – Moving an Undo Tablespace ......................................................... 194 Tablespace Management – Dropping an Undo Tablespace ....................................................... 196 Tablespace Management – Resizing a Temporary Tablespace ................................................. 199 Tablespace Management – File-per- Table Tablespaces ........................................................... 203 Types of Partitioning in MySQL ................................................................................................. 209 RANGE Partitioning .................................................................................................................... 209 LIST Partitioning ........................................................................................................................ 215 COLUMNS Partitioning .............................................................................................................. 218 HASH Partitioning ...................................................................................................................... 220 KEY Partitioning ........................................................................................................................ 223 SUBPARTITION ........................................................................................................................... 224 Summary .................................................................................................................................. 227 Chapter 5: MySQL High Availability, Replication, and Scalability ......................... 229 Introduction ............................................................................................................................... 229 High-Level Overview ................................................................................................................. 230 Binlog Replication ..................................................................................................................... 231 GTID-Based Replication ............................................................................................................ 254 GTID = source_id:transaction_id ......................................................................................... 254 MySQL Scalability ..................................................................................................................... 264 Summary .................................................................................................................................. 272 Chapter 6: MySQL InnoDB Cluster and ClusterSet ................................................ 273 Introduction ............................................................................................................................... 273 High-Level Overview ................................................................................................................. 273 Configuring InnoDB Clusterset .................................................................................................. 275 InnoDB Cluster Connection Routing Using MySQL Router ........................................................ 328 Scenario 1: InnoDB Clusterset – Role Switch of Clusters ......................................................... 345 Scenario 2: Role Switch of Instances Within the Clusters ......................................................... 356 Scenario 3: Test Failure of an Instance in Secondary Role Within the Clusters ........................ 360 Scenario 4: Test Failure of an Instance in Primary Role Within the DR Cluster ......................... 368 InnoDB Clusterset Scenarios – Recover InnoDB Clusterset from a Major Outage .................... 376 Summary .................................................................................................................................. 395 Chapter 7: MySQL NDB Cluster ............................................................................. 397 Introduction ............................................................................................................................... 397 When to Use NDB ...................................................................................................................... 397 NDB Cluster vs. InnoDB Cluster Comparison ............................................................................ 398 NDB Cluster Components .......................................................................................................... 398 Management Nodes .................................................................................................................. 398 Data Nodes ................................................................................................................................ 398 SQL Nodes ................................................................................................................................. 399 Fragmented Replicas ................................................................................................................ 399 NDB Cluster Installation ............................................................................................................ 400 High-Level Architecture ....................................................................................................... 400 Prerequisites ....................................................................................................................... 400 Download the Software ....................................................................................................... 401 Downloading the rpm to the Server .................................................................................... 404 Install the rpm Packages ..................................................................................................... 406 NDB Cluster Configuration ........................................................................................................ 408 Configuring Management Nodes ......................................................................................... 408 Configuring Data Nodes and SQL Nodes ............................................................................. 410 Configuring SQL API Nodes .................................................................................................. 410 NDB Cluster Initiation ................................................................................................................ 411 Initiate the Management Node ............................................................................................ 411 Initiate the Data Nodes ........................................................................................................ 411 Data Node 01 ....................................................................................................................... 411 Data Node 02 ....................................................................................................................... 411 Initiate the SQL API Nodes ................................................................................................... 412 SQL API Node 01 .................................................................................................................. 412 SQL API Node 02 .................................................................................................................. 412 Monitor the NDB Cluster from Management Node .............................................................. 413 Restart Node 02 ................................................................................................................... 414 NDB Cluster Validation .............................................................................................................. 415 Create Sample Tables and Data ........................................................................................... 415 NDB Cluster Restart .................................................................................................................. 418 NDB Cluster Graceful Restart .............................................................................................. 418 NDB Cluster Force Restart ................................................................................................... 419 NDB Cluster Data Node Crash ............................................................................................. 421 NDB Cluster Replication ............................................................................................................ 423 Requirements for NDB Cluster Replication .......................................................................... 424 Preparing the NDB Cluster for Replication .......................................................................... 424 Prepare Source and Create Replica User ............................................................................ 425 Prepare Target and Create Replication User ........................................................................ 427 Summary .................................................................................................................................. 429 Chapter 8: MySQL Logical Backup ........................................................................ 431 Introduction ............................................................................................................................... 431 Using mysqldump ..................................................................................................................... 431 Using MyDumper ....................................................................................................................... 442 Using Percona Xtrabackup ........................................................................................................ 456 Summary .................................................................................................................................. 482 Chapter 9: MySQL Enterprise Backup and Recovery ............................................ 483 Introduction ............................................................................................................................... 483 Installing MySQL Enterprise Backup ......................................................................................... 486 Configuring MySQL Enterprise Backup ..................................................................................... 488 Perform a Full Instance Backup ................................................................................................ 491 Perform an Incremental Backup ............................................................................................... 494 Perform an Encrypted Backup .................................................................................................. 497 Perform a Backup of a Replica Server ...................................................................................... 499 Perform a Restore of Complete MySQL Server from Backup .................................................... 503 Perform a Restore of an Incremental Backup ........................................................................... 510 Perform a Backup to a Cloud Storage ....................................................................................... 525 Summary .................................................................................................................................. 532 Chapter 10: MySQL Security ................................................................................. 533 Introduction ............................................................................................................................... 533 Best Practices for Securing MySQL Database .......................................................................... 534 Use of Strong Passwords .......................................................................................................... 534 Upgrade MySQL Software to the Latest Version ....................................................................... 534 Role-Based Access Control (RBAC) ........................................................................................... 534 Operating System Hardening .................................................................................................... 534 Audit Logging ............................................................................................................................ 535 Robust Monitoring ..................................................................................................................... 535 Secure Data Through Encryption .............................................................................................. 535 Secure Backup and Recovery Strategy ..................................................................................... 535 Firewall Configuration ............................................................................................................... 535 Securing MySQL Installation ..................................................................................................... 535 Change Default Root Password ................................................................................................. 536 Remove Anonymous Users ........................................................................................................ 536 Remove the Test Database ........................................................................................................ 537 Set Password Complexity ......................................................................................................... 537 Restricting Privileges of User Accounts .................................................................................... 537 Disable Remote Access for Root User ....................................................................................... 539 Securing Using CLI .................................................................................................................... 539 Changing the Default Port in MySQL ......................................................................................... 541 How to Change the Ports .......................................................................................................... 542 Changing the Port Numbers ...................................................................................................... 542 How to Run MySQL As a Non-root User .................................................................................... 543 Change Default datadir and Run As Non-root User ............................................................. 543 Update Config to Change the User ...................................................................................... 544 Data-at-Rest Encryption (DARE/TDE) ........................................................................................ 544 Prerequisites of Encryption ....................................................................................................... 545 Pre-checks for Enabling Encryption .......................................................................................... 546 Enable Encryption ..................................................................................................................... 547 Post Enable Encryption ............................................................................................................. 547 General Tablespace Encryption ................................................................................................. 548 Verify Encrypted Tablespaces ................................................................................................... 548 File-per-Table Tablespace Encryption ....................................................................................... 548 Redo Log Encryption ................................................................................................................. 549 Undo Log Encryption ................................................................................................................. 549 Master Key Rotation .................................................................................................................. 549 Database Encryption in Transit (SSL/TLS) ................................................................................. 550 Secure MySQL Backups ............................................................................................................ 554 Use Encryption Along with mysqldump .................................................................................... 555 Enterprise Firewall .................................................................................................................... 556 Installing the Firewall Plug-in ................................................................................................... 556 Configuring the Firewall Plug-in ............................................................................................... 557 Understanding Different Operational Modes ............................................................................. 557 Enable Protection Mode ............................................................................................................ 558 Enterprise Audit ........................................................................................................................ 559 Make Plug-in Persistent ...................................................................................................... 560 Enable Auditing for All the Users ......................................................................................... 561 Validate Audit Logs .............................................................................................................. 562 Role-Based Access Control ....................................................................................................... 564 How Can We Implement RBAC in MySQL ? ............................................................................... 564 Authentication with MySQL ....................................................................................................... 564 Native Authentication ................................................................................................................ 565 LDAP Authentication .................................................................................................................. 566 Prerequisite for LDAP Authentication .................................................................................. 566 Enable LDAP with Simple Bind ............................................................................................ 566 Enable LDAP with SASL Plug-in ........................................................................................... 567 PAM Authentication ................................................................................................................... 569 Validate If Any LDAP Settings Are Used ............................................................................... 570 Configure PAM to Use LDAP ................................................................................................ 570 Install auth_pam Plug-in ..................................................................................................... 570 Validate Login ...................................................................................................................... 571 Windows Authentication ........................................................................................................... 572 Summary .................................................................................................................................. 573 Chapter 11: MySQL Performance Tuning .............................................................. 575 Introduction ............................................................................................................................... 575 Design Database for Optimum Performance ............................................................................ 576 Server Requirements ................................................................................................................ 576 CPU ........................................................................................................................................... 576 Memory ..................................................................................................................................... 577 Disk Storage .............................................................................................................................. 577 Database Settings ..................................................................................................................... 577 innodb_dedicated_server .................................................................................................... 577 Innodb_buffer_pool_size ..................................................................................................... 578 Innodb_buffer_pool_instances ............................................................................................ 579 Innodb_log_file_size ........................................................................................................... 579 innodb_log_files_in_group ................................................................................................. 579 Innodb_redo_log_capacity .................................................................................................. 580 innodb_log_buffer_size ....................................................................................................... 580 Innodb_flush_log_at_trx_commit ....................................................................................... 581 innodb_flush_log_at_timeout ............................................................................................. 581 innodb_file_per_table ......................................................................................................... 582 Innodb_doublewrite ............................................................................................................ 583 Innodb_flush_method ......................................................................................................... 583 sort_buffer_size .................................................................................................................. 584 join_buffer_size ................................................................................................................... 584 read_buffer_size ................................................................................................................. 584 log_queries_not_using_indexes ......................................................................................... 585 Best Practices for Performance Optimization ........................................................................... 585 Database Config Changes ................................................................................................... 585 Analyze Performance Bottlenecks ............................................................................................ 587 Monitor OS Resources ......................................................................................................... 587 Slow Query Log ................................................................................................................... 588 Performance Schema .......................................................................................................... 588 Performance Tuning Tools ......................................................................................................... 593 Analyze Table ....................................................................................................................... 593 Optimize Table Statement .................................................................................................... 594 Information Schema Table Stats View ................................................................................. 596 Check Table Statement ........................................................................................................ 598 Checksum Table Statement ................................................................................................. 598 MySQL Indexes ......................................................................................................................... 599 Index Structures ........................................................................................................................ 599 Index Types ............................................................................................................................... 600 Non-unique (Regular) Index ...................................................................................................... 601 Unique Index ............................................................................................................................. 603 Primary Key Index ..................................................................................................................... 607 Compound Index ....................................................................................................................... 610 Hash Index ................................................................................................................................ 615 Invisible Indexes ....................................................................................................................... 617 Rebuild Indexes ......................................................................................................................... 623 Index Considerations and Syntax .............................................................................................. 625 Summary .................................................................................................................................. 626 Chapter 12: MySQL Enterprise Monitor ................................................................ 627 Introduction ............................................................................................................................... 627 Installation Prerequisites for MySQL Enterprise Monitor (MEM) ............................................... 629 Installing MySQL Enterprise Monitor ......................................................................................... 633 Monitoring a MySQL Instance ................................................................................................... 652 Summary .................................................................................................................................. 659 Chapter 13: Monitoring MySQL Using Oracle Enterprise Manager Cloud Control 13c ........................................................................................................... 661 Introduction ............................................................................................................................... 661 Prerequisite Checks .................................................................................................................. 662 Oracle Enterprise Manager Cloud Control Version ............................................................... 662 EM Support for MySQL Version ............................................................................................ 663 Supported Platforms ............................................................................................................ 663 OS User for Management Agent ................................................................................................ 664 Monitoring User for MySQL Instance ................................................................................... 664 Update Hosts File ................................................................................................................ 665 Open Firewall Ports ............................................................................................................. 666 Agent Install Directory ......................................................................................................... 667 Installing the MySQL Database Plug-in ..................................................................................... 667 Checking the Availability of Plug-in in OEM Cloud Control 13c ........................................... 667 Viewing Information About Plug-in ...................................................................................... 668 Downloading Plug-ins in Online Mode ................................................................................. 670 Deploying Plug-in on OMS ................................................................................................... 673 Deploying Plug-ins on Oracle Management Agent Host ........................................................... 683 Adding MySQL Targets .............................................................................................................. 694 Adding Targets Using Autodiscovery ................................................................................... 694 Promote the Discovered Targets ............................................................................................... 697 Monitoring the Targets .............................................................................................................. 700 Monitor Newly Added Management Agent .......................................................................... 700 Monitor Newly Added Management Agent Host ........................................................................ 701 Monitor Newly Added MySQL Target ......................................................................................... 701 Validate Targets from Enterprise Repository Database ............................................................. 702 Summary .................................................................................................................................. 703 Chapter 14: MySQL Troubleshooting ..................................................................... 705 Introduction ............................................................................................................................... 705 Scenario 1: Access denied for user root ................................................................................... 705 Scenario 2: MySQL too many connections ................................................................................ 706 Scenario 3: MySQL Host host_name is blocked ........................................................................ 707 Scenario 4: [InnoDB] Unable to lock ./ibdata1 error: 11 ............................................................ 708 Scenario 5: Fatal error: Please read “Security” section of the manual to find out how to run mysqld as ............................................................................................ 709 Scenario 6: MySQL packet too large ......................................................................................... 710 Scenario 7: MySQL standby replication stopped due to Error_code: 1032 ............................... 711 Scenario 8: [Repl] Replica I/O for channel: Error connecting to source ‘[email protected]:3306’ ........................................................................................................712 Scenario 9: MySQL Error: Out of memory ................................................................................. 712 Scenario 10: MySQL Error: Unable to connect to database ....................................................... 713 Scenario 11: MySQL Backup Error: MAIN: [ERROR] unknown variable ‘defaults-file=/etc/my.cnf’ ........................................................................................................ 713 Scenario 12: MySQL Backup Error: MAIN ERROR: The backup directory does already exist and is not empty. Remove or clear it and retry. ................................................... 714 Scenario 13: InnoDB Cluster Error: ERROR: New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster. ................................................................... 714 Scenario 14: InnoDB Cluster Error: ERROR: The following tables do not have a Primary Key or equivalent column: ........................................................................................... 715 Scenario 15: InnoDB Cluster Error: ERROR: RuntimeError: Cannot add an instance with the same server UUID ....................................................................................................... 716 Scenario 16: InnoDB Cluster Error: ERROR: The instance mysql-c:3306 does not belong to the cluster. ERROR: MYSQLSH 51104: Metadata for instance mysql-c:3306 not found ............................................................................................................ 717 Scenario 17: InnoDB Cluster Error: Dba.getCluster: This function is not available through a session to a standalone instance (metadata exists, instance belongs to that metadata, but GR is not active) (MYSQLSH 51314). Unable to get cluster status post reconnecting ........ 718 Scenario 18: Binlog location is full ............................................................................................ 718 Summary .................................................................................................................................. 720 Index ..................................................................................................................... 721
 |
|||||||||||||||||||
![[Profile]](./templates/default/images/lang/en/icon_profile.gif "View user's profile"){kind=icon} ![[PM]](./templates/default/images/lang/en/icon_pm.gif "Send private message"){kind=icon} |
||||||||||||||||||||
![[Quote]](./templates/default/images/lang/en/icon_quote.gif "Reply with quote"){kind=icon}
|
|
Home » Books and magazines » Computer literature » Database management system |
Current time is: 24-Feb 14:17
All times are UTC + 2
You cannot post new topics in this forum
You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum |