MySQL Database Admin for Experienced Database Administrators
StayAhead Training
Duration: 3 Days
£1,195
(VAT will be affed to this price)
Detailed information
| Duration: | 3 Days |
|---|---|
| Price: | £1,195 (VAT will be affed to this price) |
| Type: | Training |
| Method: | In a classroom |
| Accreditation: | See course outline for exam details. |
| Prepares for: | Examinations The MySQL Database Administration for Experienced Database Administrators course covers the topics required for the MySQL 5 Database Administrator Certified Professional Exam, Part I (1Z0-873) and MySQL 5 Database Administrator Certified Professional Exam, Part II (1Z0-874). Before taking any exam, ensure you have the recommended experience. The Oracle website lists all exam requirements and these are updated regularly. Exams are not included as part of the course. |
| Geared towards: | MySQL Database Administration for Experienced Database Administrators Course Overview This MySQL Database Administration for Experienced Database Administrators course is designed for Database Administrators who already have substantial experience in the administration of another relational database management system such as Oracle or Microsoft SQL Server. This course is NOT suitable for delegates who only have experience with Microsoft Access. The course provides practical experience in setting up and maintaining a MySQL database server, including backing up, recovery, configuration and optimization. |
| Requirements: | Requirements Substantial experience of a relational database management system such as Oracle or Microsoft SQL Server is required. This course is NOT suitable for delegates who only have experience with Microsoft Access. |
| Internship: | none |
| Students per class: | 10 |
Do you need further information?
Contact the person in charge , free and at no obligation, for information on how to enroll, enrollment limit/availability and more.
Course program
Skills Gained
The delegates will practise:
Starting, Stopping and Configuring MySQL
Security-Related Configuration
MySQL Database Creation
Using Client Programs for MySQL DBA Work
Setting up Character Set Support
Locking
Using Storage Engines, including the MyISAM and InnoDB engines
Table Maintenance
Obtaining Data from the Information_Schema Database
Backup and Recovery, including Exporting (mysqldump) and Importing (mysqlimport
Using Stored Procedures and Triggers for Database Administration Tasks
User Management
Securing the Server
Upgrade-Related Security Issues
Optimizing Queries
Optimizing Schemas
Optimizing the Server
The Event Scheduler
Partitioned Tables
Interpreting Error Messages
Optimizing the MySQL Environment
Scaling MySQL, including Replication
Who will the Course Benefit?
Anyone with substantial experience of an alternative relational database management system who now needs to administer, monitor and support MySQL databases and servers.
MySQL Database Administration for Experienced Database Administrators Training Course
Course Contents - DAY 1
Course Introduction
Administration and Course Materials
Course Structure and Agenda
Delegate and Trainer Introductions
Session 1: CLIENT PROGRAMS FOR DBA WORK
Introduction
Client Program Limitations
mysql
MySQL Admin
Exercises: Using Client Programs
Session 2: THE INFORMATION_SCHEMA DATABASE
What is Metadata?
The mysqlshow Utility
The SHOW and DESCRIBE Commands
The Information_Schema Database
Exercises: Obtaining Information about MySQL
Session 3: MYSQL ADMINISTRATOR
Installation
Connecting
Server Information
Service Control
User Administration
Privileges
Health
Backup and Restore
Catalogs
Exercises: Using the MySQL Administrator Tool
Session 4: INSTALLING, CONFIGURING, STARTING AND STOPPING
MySQL Distributions
Installing on Windows
Installing on Linux and UNIX
Starting and Stopping on Windows
Starting and Stopping on UNIX/Linux
Configuration
Log and Status Files
The Default SQL Mode
Time Zone Tables
Some Security Issues
Upgrading
Exercises: Installing, configuring, stopping and starting
Session 5: INTERPRETING ERROR AND DIAGNOSTIC INFORMATION
MySQL Error Messages
The SHOW Statement
SQL Modes
The PERROR Utility
The Log
The Error Log
The Slow Query Log
Exercises: Interpreting Error and Diagnostic Information
Session 6: MYSQL ARCHITECTURE
Client/Server Overview
Communication Protocols
The SQL Parser and Storage Engine Tiers
How MySQL Uses Disk Space
How MYSQL Uses Memory
Exercises: Examining the Architecture
Session 7: TABLES, DATA TYPES AND CHARACTER SET SUPPORT
Table Properties
Creating Tables
Altering Tables
Dropping Tables
Emptying Tables
Obtaining Table Metadata
Column Attributes
Bit Data Type
Numeric Data Types
Character String Data Types
Binary String Data Types
Enum and Set Data Types
Temporal Data Types
Auto_Increment
Handling Missing or Invalid Data Values
Performance Issues with Character sets
Choosing Data Types for Character Columns
Exercises: Creating and Maintaining Tables
--------------------------------------------------------------------------------
MySQL Database Administration for Experienced Database Administrators Training Course
Course Contents - DAY 2
Session 8: LOCKING
Locking Concepts
Explicit Table Locking
Advisory Locking
Preventing Locking Problems
Exercises: Locking
Session 9: STORAGE ENGINES
Introduction
The MYISAM Engine
Locking with MYISAM Tables
The Merge Engine
Other Engines: Archive, Memory, Federated, Blackhole, NDBCluster
Exercises Using Storage Engines
Session 10: THE INNODB ENGINE
Introduction
Features of Innodb
Transactions
Referential Integrity
Physical Characteristics of Innodb Tables
Tablespace Configuration
Log File and Buffer Configuration
Innodb Status
Exercises Using the InnoDB Engine
Session 11: TABLE MAINTENANCE
Table Maintenance Operations
Check Table
Repair Table
Analyze Table
Optimize Table
MySQL Check
MYISAMCHK
Repairing Innodb Tables
Enabling MYISAM AutoRepair
Exercises: Maintaining Tables
Session 12: BACKUP AND RECOVERY
Planning and Implementing a Backup and Recovery Strategy
Defining a Disaster Recovery Plan
Testing a Backup and Recovery Plan
The Advantages and Disadvantages of Different Methods
Binary Backups of MYISAM Tables
Binary Backups of Innodb Tables
Recovery
Import and Export Operations
Exporting Using SQL
Importing Using SQL
Exporting from the Command Line using mysqldump
Importing from the Command Line using mysqlimport
Exercises: Backing up and Recovery
Session 13: USER MANAGEMENT
Introduction
User Accounts
Creating Users
Renaming Users
Changing Passwords
Dropping Users
Granting Privileges
The User Table
Connection Validation
Exercises: Creating, Managing and Dropping Users
Session 14: PRIVILEGES
Introduction
Types of Privileges
Revoking Privileges
Resource Limits
The MySQL Database
The Show Grants Command
Exercises: Granting and Revoking Privileges
--------------------------------------------------------------------------------
MySQL Database Administration for Experienced Database Administrators Training Course
Course Contents - DAY 3
Session 15: USER VARIABLES AND PREPARED STATEMENTS
User Variables
Prepared Statements
Exercises: User Variables and Prepared Statements
Session 16: STORED ROUTINES FOR ADMINISTRATION
Types of Stored Routines
Benefits of Stored Routines
Stored Routines Features
Stored Routine Maintenance
Stored Routine Privileges and Execution Security
Exercises: Creating and Using Stored Routine
Session 17: TRIGGERS
DML Triggers
The Create Trigger Statement
Managing Triggers
Exercises: Creating and Using Triggers
Session 18: SECURING THE SERVER
Security Issues
Operating System Security
Filesystem Security
Log Files and Security
Network Security
Upgrade-related Security Issues
Upgrading the Privilege Tables
Security-Related SQL_Mode Values
Exercises: Securing the Server
Session 19: OPTIMIZING QUERIES
Optimization Overview
Optimization Process
Planning a Routine Monitoring Regime
Setting Suitable Goals
Identifying Candidates for Query Analysis
Using Explain to Analyze Queries
Meaning of Explain Output
Using Explain Extended
Exercises: Explaining and Optimizing Queries
Session 20: OPTIMIZATION AND INDEXES
Indexes for Performance
Creating and Dropping Indexes
Obtaining Index Metadata
Indexing Principles
Indexing and Joins
MyIsam Index Caching
Exercises: Using Indexes for Optimization
Session 21: OPTIMIZING SCHEMAS
Normalisation
General Table Optimizations
Myisam Specific Optimizations
Innodb Specific Optimizations
Other Engine Specific Optimizations
Exercises: Optimizing Schemas
Session 22: OPTIMIZING THE SERVER
Measuring Server Load
System Factors
Server Parameters
Query Optimizer Performance
The Query Cache
Exercises: Optimizing the Server
--------------------------------------------------------------------------------
MySQL Database Administration for Experienced Database Administrators Training Course
Course Contents - DAY 3 ( continued)
Session 23: OPTIMIZING THE ENVIRONMENT
Choosing the Platform
Hardware Configurations
Disk Issues on Linux
Symbolic Links
Optimizing the Operating System
Exercises: Optimizing the Environment
Session 24: THE EVENT SCHEDULER
Event scheduler concepts
Event scheduler configuration
Creating, altering and dropping events
Event scheduler monitoring
Events and privileges
Exercises: Using the event scheduler
Session 25: PARTITIONED TABLES
Partitioned tables concepts
Range partitioning
Hash partitioning
Key partitioning
List partitioning
Composite partitioning or subpartitioning
Maintenance of partitioned tables
Exercises: Using partitioned tables
Session 26: SCALING MYSQL
Using Multiple Servers
Replication
Exercises: Using Replication
The delegates will practise:
Starting, Stopping and Configuring MySQL
Security-Related Configuration
MySQL Database Creation
Using Client Programs for MySQL DBA Work
Setting up Character Set Support
Locking
Using Storage Engines, including the MyISAM and InnoDB engines
Table Maintenance
Obtaining Data from the Information_Schema Database
Backup and Recovery, including Exporting (mysqldump) and Importing (mysqlimport
Using Stored Procedures and Triggers for Database Administration Tasks
User Management
Securing the Server
Upgrade-Related Security Issues
Optimizing Queries
Optimizing Schemas
Optimizing the Server
The Event Scheduler
Partitioned Tables
Interpreting Error Messages
Optimizing the MySQL Environment
Scaling MySQL, including Replication
Who will the Course Benefit?
Anyone with substantial experience of an alternative relational database management system who now needs to administer, monitor and support MySQL databases and servers.
MySQL Database Administration for Experienced Database Administrators Training Course
Course Contents - DAY 1
Course Introduction
Administration and Course Materials
Course Structure and Agenda
Delegate and Trainer Introductions
Session 1: CLIENT PROGRAMS FOR DBA WORK
Introduction
Client Program Limitations
mysql
MySQL Admin
Exercises: Using Client Programs
Session 2: THE INFORMATION_SCHEMA DATABASE
What is Metadata?
The mysqlshow Utility
The SHOW and DESCRIBE Commands
The Information_Schema Database
Exercises: Obtaining Information about MySQL
Session 3: MYSQL ADMINISTRATOR
Installation
Connecting
Server Information
Service Control
User Administration
Privileges
Health
Backup and Restore
Catalogs
Exercises: Using the MySQL Administrator Tool
Session 4: INSTALLING, CONFIGURING, STARTING AND STOPPING
MySQL Distributions
Installing on Windows
Installing on Linux and UNIX
Starting and Stopping on Windows
Starting and Stopping on UNIX/Linux
Configuration
Log and Status Files
The Default SQL Mode
Time Zone Tables
Some Security Issues
Upgrading
Exercises: Installing, configuring, stopping and starting
Session 5: INTERPRETING ERROR AND DIAGNOSTIC INFORMATION
MySQL Error Messages
The SHOW Statement
SQL Modes
The PERROR Utility
The Log
The Error Log
The Slow Query Log
Exercises: Interpreting Error and Diagnostic Information
Session 6: MYSQL ARCHITECTURE
Client/Server Overview
Communication Protocols
The SQL Parser and Storage Engine Tiers
How MySQL Uses Disk Space
How MYSQL Uses Memory
Exercises: Examining the Architecture
Session 7: TABLES, DATA TYPES AND CHARACTER SET SUPPORT
Table Properties
Creating Tables
Altering Tables
Dropping Tables
Emptying Tables
Obtaining Table Metadata
Column Attributes
Bit Data Type
Numeric Data Types
Character String Data Types
Binary String Data Types
Enum and Set Data Types
Temporal Data Types
Auto_Increment
Handling Missing or Invalid Data Values
Performance Issues with Character sets
Choosing Data Types for Character Columns
Exercises: Creating and Maintaining Tables
--------------------------------------------------------------------------------
MySQL Database Administration for Experienced Database Administrators Training Course
Course Contents - DAY 2
Session 8: LOCKING
Locking Concepts
Explicit Table Locking
Advisory Locking
Preventing Locking Problems
Exercises: Locking
Session 9: STORAGE ENGINES
Introduction
The MYISAM Engine
Locking with MYISAM Tables
The Merge Engine
Other Engines: Archive, Memory, Federated, Blackhole, NDBCluster
Exercises Using Storage Engines
Session 10: THE INNODB ENGINE
Introduction
Features of Innodb
Transactions
Referential Integrity
Physical Characteristics of Innodb Tables
Tablespace Configuration
Log File and Buffer Configuration
Innodb Status
Exercises Using the InnoDB Engine
Session 11: TABLE MAINTENANCE
Table Maintenance Operations
Check Table
Repair Table
Analyze Table
Optimize Table
MySQL Check
MYISAMCHK
Repairing Innodb Tables
Enabling MYISAM AutoRepair
Exercises: Maintaining Tables
Session 12: BACKUP AND RECOVERY
Planning and Implementing a Backup and Recovery Strategy
Defining a Disaster Recovery Plan
Testing a Backup and Recovery Plan
The Advantages and Disadvantages of Different Methods
Binary Backups of MYISAM Tables
Binary Backups of Innodb Tables
Recovery
Import and Export Operations
Exporting Using SQL
Importing Using SQL
Exporting from the Command Line using mysqldump
Importing from the Command Line using mysqlimport
Exercises: Backing up and Recovery
Session 13: USER MANAGEMENT
Introduction
User Accounts
Creating Users
Renaming Users
Changing Passwords
Dropping Users
Granting Privileges
The User Table
Connection Validation
Exercises: Creating, Managing and Dropping Users
Session 14: PRIVILEGES
Introduction
Types of Privileges
Revoking Privileges
Resource Limits
The MySQL Database
The Show Grants Command
Exercises: Granting and Revoking Privileges
--------------------------------------------------------------------------------
MySQL Database Administration for Experienced Database Administrators Training Course
Course Contents - DAY 3
Session 15: USER VARIABLES AND PREPARED STATEMENTS
User Variables
Prepared Statements
Exercises: User Variables and Prepared Statements
Session 16: STORED ROUTINES FOR ADMINISTRATION
Types of Stored Routines
Benefits of Stored Routines
Stored Routines Features
Stored Routine Maintenance
Stored Routine Privileges and Execution Security
Exercises: Creating and Using Stored Routine
Session 17: TRIGGERS
DML Triggers
The Create Trigger Statement
Managing Triggers
Exercises: Creating and Using Triggers
Session 18: SECURING THE SERVER
Security Issues
Operating System Security
Filesystem Security
Log Files and Security
Network Security
Upgrade-related Security Issues
Upgrading the Privilege Tables
Security-Related SQL_Mode Values
Exercises: Securing the Server
Session 19: OPTIMIZING QUERIES
Optimization Overview
Optimization Process
Planning a Routine Monitoring Regime
Setting Suitable Goals
Identifying Candidates for Query Analysis
Using Explain to Analyze Queries
Meaning of Explain Output
Using Explain Extended
Exercises: Explaining and Optimizing Queries
Session 20: OPTIMIZATION AND INDEXES
Indexes for Performance
Creating and Dropping Indexes
Obtaining Index Metadata
Indexing Principles
Indexing and Joins
MyIsam Index Caching
Exercises: Using Indexes for Optimization
Session 21: OPTIMIZING SCHEMAS
Normalisation
General Table Optimizations
Myisam Specific Optimizations
Innodb Specific Optimizations
Other Engine Specific Optimizations
Exercises: Optimizing Schemas
Session 22: OPTIMIZING THE SERVER
Measuring Server Load
System Factors
Server Parameters
Query Optimizer Performance
The Query Cache
Exercises: Optimizing the Server
--------------------------------------------------------------------------------
MySQL Database Administration for Experienced Database Administrators Training Course
Course Contents - DAY 3 ( continued)
Session 23: OPTIMIZING THE ENVIRONMENT
Choosing the Platform
Hardware Configurations
Disk Issues on Linux
Symbolic Links
Optimizing the Operating System
Exercises: Optimizing the Environment
Session 24: THE EVENT SCHEDULER
Event scheduler concepts
Event scheduler configuration
Creating, altering and dropping events
Event scheduler monitoring
Events and privileges
Exercises: Using the event scheduler
Session 25: PARTITIONED TABLES
Partitioned tables concepts
Range partitioning
Hash partitioning
Key partitioning
List partitioning
Composite partitioning or subpartitioning
Maintenance of partitioned tables
Exercises: Using partitioned tables
Session 26: SCALING MYSQL
Using Multiple Servers
Replication
Exercises: Using Replication
Do you need clarification regarding the course program?
Contact the person in charge , free and at no obligation, for information on how to enroll, enrollment limit/availability and more.
Course location
- c/o YSES, The Enterprise Suite, 9th Floor Centre City Tower, 5-7 Hill Street, Birmingham B5 4UA - B5 4UACity and Borough of Birmingham (England)
- c/o ComputerWorld, Apex House, Westerleigh Business Park, Turner Drive, Yate, Bristol, BS37 5YX - BS37 5YCity of Bristol (England)
- 16 St Mary's Street, Edinburgh, EH1 1SU - EH1 1SUCity of Edinburgh (Scotland)
- Halsbury House, Chancellor Court, 21 The Calls, Leeds, LS2 7EH - LS2 7EHCity and Borough of Leeds (England)
- c/o YSES, St James Court, 30 Brown Street, Manchester, M2 1DH - M2 1DHCity and Borough of Manchester (England)
StayAhead Training
StayAhead Training established 1992, centres throughout the UK...... with a team of full time dedicated professional training consultants......
StayAhead Training has been established since 1992 and is one of the UK's leading independent providers of Oracle, Unix, Linux, MySQL, Java, PHP Web Deveopment, ITIL & Business Analysis training.
Scheduled courses are available in 6 centres throughout the UK including London, Birmingham, Manchester, Bristol, Leeds and Edinburgh. We also provide training on our client's premises, this training can be tailored to the specific needs of the client.
Our courses have been designed to ensure that people can fulfil their job role and also help prepare delegates for certification in Oracle, Red Hat, Solaris, MySQL, Linux, SUSE Linux, Java, ITIL, Business Analysis and HP-UX .
Working in association with both the Oracle (OUG) and Hewlett Packard (HPUG) User Groups, StayAhead Training have a reputation of providing high quality value for money training at a cost effective price.
Our quality is assured by a team of dedicated professional training consultants who work full time for StayAhead Training and who blend their training skills with a wealth of hands-on experience.
StayAhead Training has been established since 1992 and is one of the UK's leading independent providers of Oracle, Unix, Linux, MySQL, Java, PHP Web Deveopment, ITIL & Business Analysis training.
Scheduled courses are available in 6 centres throughout the UK including London, Birmingham, Manchester, Bristol, Leeds and Edinburgh. We also provide training on our client's premises, this training can be tailored to the specific needs of the client.
Our courses have been designed to ensure that people can fulfil their job role and also help prepare delegates for certification in Oracle, Red Hat, Solaris, MySQL, Linux, SUSE Linux, Java, ITIL, Business Analysis and HP-UX .
Working in association with both the Oracle (OUG) and Hewlett Packard (HPUG) User Groups, StayAhead Training have a reputation of providing high quality value for money training at a cost effective price.
Our quality is assured by a team of dedicated professional training consultants who work full time for StayAhead Training and who blend their training skills with a wealth of hands-on experience.