MySQL Administration

Course Description

This two-day hands-on course teaches students how to install, configure, administer, manage and protect their MySQL database environment. Students will also learn database concepts.

Course Objectives

Upon successful completion of this course, students will be able to:

• Install and configure MySQL on their system
• Search and display data in an MySQL database
• Interact with MySQL via the command line interface
• Protect the MySQL database
• Construct efficient SQL queries incorporating MySQL extensions
• Optimize database performance and enhance reliability
• Manage user accounts
• Perform backups
• Examine and understand the content of the server log files
• Understand the difference between InnoDB and MyISAM

Course Benefits

Students will quickly learn how to efficiently manage a MySQL database environment on Linux.

Who Should Attend

This course is valuable for anyone who needs to administer a MySQL database environment.

Prerequisite

This course assumes students have no previous knowledge of MySQL. Basic computer experience at the user level is expected.

Method Of Instruction

Lecture, demonstrations, several short interactive quizzes, questions and answers, and numerous hands-on exercises.

Hands-on Exercises

Throughout this course, students perform a series of extensive hands-on exercises including:

• Installing and installing MySQL.
• Configuring and customizing the MySQL database environment.
• Creating a database with tables.
• Inserting data into a database.
• Copy, delete, move and display data in tables.
• Automatically starting the MySQL server.
• Importing data from external sources.
• Performing transactional and non-transactional operations.
• Employing the MySQL command line interface to manipulate data.
• Manage user accounts.
• Examining the MyISAM and InnoDB storage engines.

Course Outline

Chapter 1: Installation

• MySQL Installation Overview
• Determining your current MySQL version
• Choosing Which MySQL Distribution to Install
• How to Get MySQL
• Verifying Package Integrity Using MD5 Checksums or GnuPG
• Installation Layouts
• Standard MySQL Installation Using a Binary Distribution
• Installing MySQL on Linux
• MySQL Installation Using a Source Distribution
• Post-Installation Setup and Testing
• Upgrading MySQL
• Downgrading MySQL
• Environment Variables

Chapter 2: Operation

• Connecting to and Disconnecting from the Server
• Entering Queries
• Creating and Using a Database
• Creating and Selecting a Database
• Creating a Table
• Loading Data into a Table
• Updating Records
• Deleting Records
• Retrieving Information from a Table
• Getting Information About Databases and Tables
• Using MySQL in Batch Mode
• Examples of Common Queries
• Using MySQL with Apache

Chapter 3: Administration

• SQL Modes
• The Shutdown Process
• Server-Side Help
• MySQL Server Startup Programs
• MySQL Instance Manager Command Options
• MySQL Instance Manager Configuration Files
• Starting the MySQL Server with MySQL Instance Manager
• Instance Manager User and Password Management
• MySQL Server Instance Status Monitoring
• Connecting to MySQL Instance Manager
• MySQL Instance Manager Commands
• Installation-Related Programs
• mysql_fix_privilege_tables — Upgrade MySQL System Tables
• mysql_install_db — MySQL Data Directory Initialization Script
• mysql_secure_installation — Improve MySQL Installation Security
• mysql_tzinfo_to_sql — Load the Time Zone Tables
• mysql_upgrade — Check Tables for MySQL Upgrade

Chapter 4: Securing MySQL

• General Security Guidelines
• Making MySQL Secure Against Attackers
• Security-Related mysqld Options
• Security Issues with LOAD DATA LOCAL
• How to Run MySQL as a Normal User
• The MySQL Access Privilege System
• What the Privilege System Does
• How the Privilege System Works
• Privileges Provided by MySQL
• Connecting to the MySQL Server
• When Privilege Changes Take Effect
• Causes of Access denied Errors
• Removing Permissions from a Database
• Removing Permissions from a User
• Flushing Priviledges
• MySQL User Account Management
• MySQL Usernames and Passwords
• Adding New User Accounts to MySQL
• Removing User Accounts from MySQL
• Limiting Account Resources
• Assigning Account Passwords
• Keeping Your Password Secure
• Using Secure Connections

Chapter 5: Backup and Recovery

• Database Backups
• Backup and Recovery Strategy
• Point-in-Time Recovery
• Table Maintenance and Crash Recovery

Chapter 6: Server Logs

• The Error Log
• The General Query Log
• The Binary Log
• The Slow Query Log
• Server Log Maintenance

Chapter 7: Storage Engines

• MyISAM Storage Engine
• MyISAM Startup Options
• MyISAM Table Storage Formats
• MyISAM Table Problems
• The InnoDB Storage Engine
• InnoDB Configuration
• InnoDB Startup Options and System Variables
• Creating the InnoDB Tablespace
• Creating and Using InnoDB Tables
• Adding and Removing InnoDB Data and Log Files
• Backing Up and Recovering an InnoDB Database
• Moving an InnoDB Database to Another Machine
• InnoDB Transaction Model and Locking
• InnoDB Performance Tuning Tips
• InnoDB Table and Index Structures
• InnoDB File Space Management and Disk I/O
• InnoDB Error Handling
• Restrictions on InnoDB Tables
• InnoDB Troubleshooting

Hardware Requirements

• Student computers should feature at a minimum
  • Pentium III class processor
  • Minimum 128 MByte RAM
  • Minimum 18.0 GByte hard drive
  • Linux operating system
  • 3.5-inch Floppy Drive
  • Linux compatible mouse
  • Browser
  • Computer monitor
• Instructor's computer should feature at a minimum
  • Pentium III class processor
  • Minimum 128 MByte RAM
  • Minimum 18.0 GByte hard drive
  • Linux operating system
  • 3.5-inch Floppy Drive
  • Linux compatible mouse
  • Browser
  • Computer monitor