Certification : Database Administration with MySQL

Request for Course DetailsDownload as PDF

Suitable For:

Database administrators and system administrators who need to manage MySQL based services.

 

Prerequisites:

  • Practical knowledge of SQL
  • Some knowledge of relational database administration issues

 

Duration:  (4 days)

Instructor-led in-house training with practical exercises managing a sample SQL database

  • Running the mysql client program
  • The simplest query: select *
  • Displaying query results
  • Splitting up queries
  • Selecting columns and rows from database tables
  • Queries over multiple tables
  • Combining where and column choice
  • Examining a MySQL database
  • Using SQL insert queries to add data with and without column names
  • Rearranging columns with insert
  • Inserting several rows at once
  • Using the SQL update statement to change existing data in a table
  • Using the SQL delete statement to remove data from a table
  • Counting rows with the SQL count function
  • Finding the largest and smallest items (SQL min and max functions)
  • Finding averages (SQL avg function)
  • Rows with missing data (null values)
  • Finding rows with missing data (SQL is null and is not null tests)
  • Sorting result rows (SQL order by clause, sorting in ascending or descending order with asc and desc)
  • Using column-name aliases for long-winded column names in select
  • Simple joins across multiple tables

A introduction to database design

  • Creating a database (SQL create database statement)
  • Creating a simple table (SQL create table statement)
  • Text types (e.g., varchar(255))
  • Primary keys, identifying numbers
  • integer not null auto_increment primary key
  • Cross-table linking (matching foreign keys to primary keys)
  • Changing the type of a a column (SQL alter table statement)
  • A non-entity table
  • Junction tables (auxillary tables to enable ‘many to many’ joins)

Database design

  • Data types
  • Text types (SQL varchar and char, MySQL specific mediumtext and longtext)
  • Binary column types (MySQL specific mediumblob and longblob)
  • Relationships between tables (‘one to many’ and ‘many to many’)
  • Unique IDs (including MySQL specific extension auto_increment)
  • Primary and foreign keys
  • not null type qualifier
  • Joining across many-to-many relationships

Getting started with the MySQL server

  • The MySQL suite of programs
  • Obtaining MySQL
  • Installing and configuring MySQL
  • The MySQL data directory
  • Default directories for binary installs
  • How mysqld provides access to data
  • MySQL database file types
  • Starting up and shutting down the server on Unix and Windows
  • MySQL logging and log files
  • The error log
  • The general query log
  • The binary update log

Privileges in MySQL

  • Users and privileges
  • MySQL users
  • Local and remote users
  • The MySQL specific user() function
  • Controlling access rights with SQL
  • Using the SQL grant statement
  • grant with wildcards
  • Granting multiple privileges
  • Setting passwords for users (SQL grant statement with identified by clause)
  • Revoking privileges (SQL revoke statement)
  • Granting the grant privilege itself
  • show grants
  • Grant tables
  • flush privileges

MySQL backup and recovery

  • Backup principles
  • Backup methods
  • Backing up with mysqldump
  • Transfers to another database or server
  • Useful mysqldump options
  • Backing up with mysqlhotcopy
  • Backup by direct copying
  • Recovering an entire database
  • Recovering individual tables
  • Database replication
  • Live replication
  • How slaves update themselves
  • Setting up live replication
  • Checking and repairing database tables
  • Checking tables with isamchk and myisamchk
  • Repairing tables with isamchk and myisamchk
  • Checking tables with the check table statement
  • Repairing tables with the repair table statement

MySQL Development

  • MySQL Development
  • Subqueries in MySQL
  • Character Sets and Collation
  • Spatial Data and OpenGIS
  • Spatial Columns
  • Spatial Functions
  • Spatial Indexed
  • MySQL Product Objectives
  • Development stages

Further MySQL queries

  • Aliases for column names, table names and computed values
  • Getting only distinct results (SQL select statement with distinct qualifier)
  • Counting distinct rows
  • Limiting the number of results (SQL limit statement)
  • Limiting updates
  • Specifying limit and start position
  • Creating tables from query results (SQL create table statement with select clause)
  • Creating temporary tables
  • Replacing rows
  • Copying rows into an existing table
  • Replacing rows in a table from a query
  • Arithmetic operators and functions
  • String manipulation functions
  • Storing dates and times
  • Timestamp values
  • Time related functions
  • Increasing and decreasing dates and times
  • Using + and – operators with dates
  • Formatting dates and times for output
  • Unix time values

Advanced MySQL queries

  • Aggregate queries
  • Grouping rows together
  • Using group by
  • Multiple aggregate functions
  • Grouping by multiple fields
  • Using group by with other where
  • Sorting group by queries
  • Using group by with multiple tables
  • More multi-table group by queries
  • Selecting groups by their aggregate value
  • where and having
  • where and having example
  • Inner joins (SQL inner join syntax)
  • Inner joins on matching field names (natural joins)
  • Left joins (SQL left join syntax)
  • Left joins with multiple matching rows
  • Right joins
  • Equivalence of left and right joins
  • Full outer joins
  • Components of a select query
  • Subselects
  • Left joins instead of subselects
  • MySQL & subselects
  • Using temporary tables for difficult queries
  • create temporary table syntax
  • Transactions
  • Atomic operations
  • Locking tables
  • Table locking details
  • Table locking with aliases

Option files, Multiple Servers

  • Multiple Server Rationale
  • Multiple Server Basics
  • Server Options
  • Option File Format
  • Sample Option File
  • Using Localhost
  • Making Multiple Servers Work

Storage Enginges and Table Types

  • Storage Engines
  • MyISAM
  • InnoDB
  • MERGE Tables
  • Berkley DB Tables
  • HEAP (MEMORY) Tables
  • NBD Cluster Engine
  • InnoDB Transaction Support
  • Performing Transactions
  • InnoDB Differences from MyISAM

Optimising tables and queries

  • Indexes in MySQL
  • Primary keys and unique keys
  • Creating primary keys
  • Primary keys over multiple columns
  • Creating tables with unique keys
  • Non-unique indexes
  • Adding an index while creating a table
  • Adding indexes to existing tables
  • Finding out how MySQL will execute a query
  • Using explain to analyse queries
  • Interpreting the output of explain
  • Interpreting the ‘join’ type
  • explain when an index can be used
  • Differences in the output of explain

Using the Command-Line Tools

  • Why use the Command Line?
  • The MySQL Command-Line Tool
  • MySQL Command-Line Options

Replication of MySQL Databases

  • How Slaves Work
  • Setting Up the Master Server
  • Setting Up Slaves
  • Fine Tuning Replication
  • Monitoring and Managing Replication
  • Rotating Log Files

MySQL Optimization and Tuning

  • What One Can and Should Optimize
  • Optimizing Hardware for MySQL
  • Optimizing Disks
  • Optimizing OS
  • Choosing API
  • Optimizing the Application
  • Portable Applications
  • Increasing Speed
  • Performance Figures
  • MySQL Startup Options
  • How MyQL Stores Data
  • MySQL Buffer Variables
  • How the MySQL Table Cache Works
  • MySQL Extensions
  • MySQL Indexes

Download as PDF