COURSE OBJECTIVE:
Course Objectives At the end of the MySQL Database Administration course,delegates will have the knowledge to use the features available to administer,monitor and support MySQL databases and servers.
TARGET AUDIENCE:
Who will the Course Benefit? Database Administrators,Database Developers and Technical Consultants who need to setup,administer,monitor and support MySQL databases and servers.
COURSE PREREQUISITES:
Requirements A working knowledge of MySQL is required. Delegates should have knowledge of relational database concepts; have knowledge of MySQL data types; be able run Queries using SQL; join tables and run basic DDL and DML statements. This knowledge can be gained by attendance on the Introduction to MySQL course. This course is run on a Linux operating system platform,therefore,a good working knowledge of Linux/UNIX is strongly recommended. This knowledge can be gained by attendance on the Linux Introduction course.
COURSE CONTENT:
MySQL Database Administration Training Course Course Contents – DAY 1 Course Introduction • Administration and Course Materials • Course Structure and Agenda • Delegate and Trainer Introductions Session 1: THE SERVER,CLIENTS AND TOOLS • Introduction to MySQL • The versions of MySQL • MySQL Database Server Releases • MySQL Database Versions • The MySQL Server • MySQL Client Connections • MySQL Client Programs • MySQL Client Libraries • The mysql Command Line Tool • The mysqladmin Program Session 2: MYSQL WORKBENCH • Installation of MySQL Workbench • Connecting to MySQL Workbench • MySQL Workbench Screens Session 3: OBTAIN METADATA • What is Metadata? • The mysqlshow Client Program • The SHOW and DESCRIBE Commands • Obtaining data from the INFORMATION_SCHEMA • MySQL 8 INFORMATION_SCHEMA Changes • Generate Statements using the INFORMATION_SCHEMA Session 4: CONFIGURE THE SERVER • Start and Stop the MySQL Server • Status Files • An Overview of MySQL Storage Engines • MySQL Server Options and Variables • MySQL Status Variables • Configure the MySQL Enterprise Edition Auditing Plugin Session 5: SQL MODES,LOG FILES AND BINARY LOGGING • SQL Modes • The General Log • The Error Log • The Slow Query Log • The Binary Logs MySQL Database Administration Training Course Course Contents – DAY 2 Session 6: MYSQL ARCHITECTURE • Communication Protocols used to connect a Client to the Server • The SQL Parser and Storage Engine Tiers • Installing and Uninstalling Plugins • How MySQL Uses Disk Space • How MYSQL Uses Memory Session 7: THE INNODB STORAGE ENGINE • Features of the Innodb Engine • The System Tablespace • Transactions and Referential Integrity • Physical Characteristics of Innodb Tables • System Tablespace Configuration • File per Table Tablespace • Log File and Buffer Configuration • Undo Logs and Undo Tablespaces • General Tablespaces • Buffer and Lof File Configuration • Temporary Table Tablespaces • Innodb Status • InnoDB Shutdown Options Session 8: OTHER STORAGE ENGINES • Check Engine Usage • The MYISAM Engine • The Merge Engine • Other Engines: Archive,Memory,Blackhole,CSV,Spider,MyRocks • Mixing Storage Engines Session 9: TABLE MAINTENANCE • Table Maintenance Operations • Check Table • Repair Table • Analyze Table • Optimize Table • MySQL Check • Repair InnoDB Tables • MyISAM Table Maintenance and Repair Utilities MySQL Database Administration Training Course Course Contents – DAY 3 Session 10: BACKUP AND RECOVERY • The Advantages and Disadvantages of Different Backup Methods • Backup Tool Overview • What Should be Backed up? • Binary Backups of MYISAM Tables • Binary Backups of Innodb Tables • Performing Hot Backups • Performing Recovery • Import and Export Operations • Export and Import using SQL • Export from the Command Line using mysqldump • Import from the Command Line using mysqlimport • Additional Backup Tools Session 11: USER MANAGEMENT • Introduction • User Accounts • Creating Users • Renaming Users • Change a User Password • Dropping Users • Granting Privileges • The User Table • Connection Validation • Password Validation Plugins Session 12: PRIVILEGES • Types of Privileges • Granting and Revoking Privileges • Setting Resource Limits • Resource Management • Role Management • The MySQL Database • The Show Grants Command MySQL Database Administration Training Course Course Contents – DAY 4 Session 13: TRANSACTIONS AND LOCKING • Locking Concepts • Levels of Locking • Implicit Table Locking with MyISAM and InnoDB • Explicit Table Locking • Advisory Locking • Monitoring Locks • Turning Autocommit on and off • Using Transaction Blocks • Possible Causes of Lock Contention • Handling Concurrency Problems and Deadlocks Session 14: TUNING THE SERVER • Status Configuration Variables • Status Variables • System Variables • Per Client Variables • Performance Schema Overview Session 15: THE EVENT SCHEDULER • Event scheduler concepts • Event scheduler configuration • Creating,altering and dropping events • Event scheduler monitoring • Events and privileges MySQL Database Administration Training Course Course Contents – DAY 5 Session 16: OVERVIEW OF HIGH AVAILABILITY • High Availability Goals • High Availability Concepts • Design for High Availability • Definition of Availability • High Availability Terminology Session 17: CONFIGURE MASTER SLAVE REPLICATION • Replication Overview • When to Use Replication • Disadvantages of Replication • Replication Factors • Replication Architecture • Complex Replication Topologies • Replicating from InnoDB to MyISAM Tables • Testing Replication • Monitoring Replication Threads • Excluding Databases or Tables from Replication • Example: Setting Up a Master Slave Replication • Replication Using GTIDs • Run Replication from a Remote Host • Controlled Switchover Session 18: ADMINISTER A REPLICATION TOPOLOGY • MySQL Replication and Failover Utilities • Replication Files and Threads • Configure a Lagging Slave • Monitoring Replication • Troubleshooting Replication • Semi-synchronous Replication • Configure MySQL Failover Session 19: MYSQL SERVER INSTALLATION,UPGRADE DEPLOYMENT SECURITY • MySQL Distributions • Installing MySQL on Linux and UNIX • The Installation Directories • Set a Password for the mysql root user • Upgrade the MySQL Server • Deployment Security
FOLLOW ON COURSES:
Further Learning MySQL Performance & Tuning MySQL for Developers Apache Web Server