MySQL Administration and Optimization — A 3-Day Course

A practical introduction to SQL

• 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
• SQL summary

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
• Log file locations

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
• Data returned by repair table

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
• When not to use indexes

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
• Non-Potable Tricks
• General Performance Tip