MySQL Certifications

Database Administration with MySQL

Overview:

You already have a basic knowledge of computer hardware and system administration. Now you would like to focus on Linux system administration and prepare to take the LPI 101 and 102 exams. In this course, you will install, configure, and manage Linux systems.

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

We are committed to offer the best and most effective training solution for you and your staff whatever your needs may be. Contact us now.