MySQL Administration

Course Introduction

• Introductions
• Instructor
• Students
• Course Objectives
• Intended Audience
• Course Structure
• Must have JavaScript enabled browser for review questions to work.
• Course Content

Installation

• Who Developed MySQL
• What Is MySQL
• MySQL is a database management system
• MySQL is a relational database management system
• MySQL software is Open Source
• MySQL is fast, reliable, and easy to use
• MySQL is multithreaded and multi-user
• Main Features Of MySQL
• Internals and Portability
• Data Types
• Statements and Functions
• Security
• Scalability
• Connectivity
• Localization
• Client Tools
• Which MySQL To Install
• MySQL Enterprise
• MySQL Community Server
• Begin To Install MySQL Community Server
• Operating Systems Supported
• Distribution To Install
• Binary Or Source
• Where To Obtain MySQL
• Verify Package Integrity
• Verify The MD5 Checksum
• Verify Signatures Using GPG
• Signature Checking Using RPM
• rpm --checksig package_name.rpm
• Installation Layout
• Difference In Layouts
• Installing MySQL On Linux
• Available RPM Packages
• Install An RPM Package
• Installing MySQL Using A Source Distribution
• Commands To Installing MySQL
• configure Options
• Problems Compiling
• Post-Installation Procedures
• Start The Server
• Verify The Server Is Up
• Problems Running mysql_install_db
• Problems Starting The Server
• Shut Down The Server
• Securing the Initial MySQL Accounts
• Set Passwords
• Anonymous Account Removal
• Assign A Password To root
• Use the SET PASSWORD statement.
• Use the mysqladmin command-line client program.
• Use the UPDATE statement.
• Before An Upgrade
• After An Upgrade
• Downgrade MySQL
• Environment Variables
• Review Questions
• Review

Operation

• Connecting To And Disconnecting From The Server
• Enter Commands
• MySQL Prompts
• Access A Database
• Create A Database
• Create A Table
• Load Data Into A Table
• The LOAD DATA and INSERT statements are useful for this.
• Retrieve Information
• The SELECT statement is used to pull information from a table.
• The WHERE clause can be used to select records based on one or more criteria.
• Sort Rows
• Date Calculations
• MySQL expects dates in 'YYYY-MM-DD' format.
• NULL Values
• NULL means "a missing unknown value".
• Pattern Matching
• Other Operations
• mysql In Batch Mode
• Using MySQL With Apache
• Review Questions
• Review

Administration

• Server-Side Programs
• Other Server-Side Programs
• The MySQL Server
• mysqld
• Command Options
• System Variables
• Types Of System Variables
• Global Variables
• Session Variables
• Set Variables
• When Variables Take Effect
• Status Variables
• MySQL Modes
• Important sql_mode Values
• ANSI
• STRICT_TRANS_TABLES
• TRADITIONAL
• Default sql_mode Value
• The Shutdown Process
• The shutdown process is initiated.
• The server creates a shutdown thread if necessary.
• The server stops accepting new connections.
• The server terminates current activity.
• Storage engines are shut down or closed.
• The server exits.
• Server-Side Help
• mysqld_safe
• mysqld_safe is the recommended way to start a mysqld server.
• mysql.server
• mysql.server is a script to start and stop system services.
• mysql_multi
• mysql.server
• mysqlmanager
• mysqlmanager Options
• Instance Manager Configuration File
• Starting The MySQL Server With Instance Manager
• Guarded Instance
• Instance Manager Password Management
• Instance Status Monitoring
• Instance Manager Commands
• Instance Manager Command Principles
• Installation-Related Programs
• comp_err
• make_win_bin_dist
• make_win_src_distribution
• mysql_fix_privilege_tables
• mysql_install_db
• mysql_secure_installation
• mysql_tzinfo_to_sql
• mysql_upgrade
• Review Questions
• Review

Securing MySQL

• Security Guidelines
• Secure MySQL Against Attack
• When connecting to MySQL always require a password.
• Do not grant the PROCESS or SUPER privilege to non-administrative users.
• Do not grant the FILE privilege to non-administrative users.
• Security Checklist
• Security-Related mysqld Options
• Security Issues With LOAD DATA LOCAL
• Run MySQL As A Normal User
• MySQL Access Privilege System
• The Privilege System Works
• user, db, and host Tables
• tables_priv and columns_priv Tables
• procs_priv Table
• Use The Grant Tables
• The user Table
• Privileges Provided By MySQL
• Caution
• It is a good idea to grant an account only those privileges that it needs.
• Cannot Be Done With Privileges
• Process Of Connecting To The Server
• Connection Verification
• More Than One Connection Match
• Request Verification
• Sort The db Table And The host Table
• The tables_priv, columns_priv, And procs_priv Tables
• Database-Related Requests
• Privilege Changes Take Effect
• Typical Error Messages
• Usernames And Passwords
• Add New User Accounts To MySQL
• Remove User Accounts From MySQL
• Limit Account Resources
• Assign Account Passwords
• Keep Passwords Secure
• It is extremely insecure to store passwords in the MYSQL_PWD environment variable.
• The safest methods are to have the client program prompt for the password or to specify the password in a properly protected option file.
• Secure Connections
• Using SSL Connections
• Allow Clients To Connect Via SSL
• Create Client And Server Certificate And Key Files
• Review Questions
• Review

Backup And Recovery

• Database Backups
• Apply LOCK TABLES on the relevant tables.
• Follow with FLUSH TABLES for the tables.
• LOCK TABLES syntax
• The FLUSH Statement
• Use mysqldump For Backup
• Also mysqlhotcopy can be use for backup.
• mysqlhotcopy only works for MyISAM tables.
• Backup Of Individual Files
• Backup Data Stored In An InnoDB Storage Engine
• Backup Strategy
• Point-In-Time Recovery
• Specify Recovery Time
• Specify Recovery Position
• Table Maintenance
• To check or repair MyISAM tables, use CHECK TABLE or REPAIR TABLE.
• To optimize MyISAM tables, use OPTIMIZE TABLE.
• To analyze MyISAM tables, use ANALYZE TABLE.
• myisamchk
• Symptoms Of Corrupted Tables
• Repair Tables
• Optimize Tables
• Table Maintenance Schedule
• cron
• Review Questions
• Review

Server Logs

• MySQL Server Logs
• The Error Log
• The General Query Log
• The Binary Log
• Size Of Binary Logs
• The Slow Query Log
• Server Log Maintenance
• Review Questions
• Review

Storage Engines

• Types Of Storage Engines
• Specify A Storage Engine
• Transaction-Safe Tables Or Non-Transaction-Safe Tables
• Characteristics Of MyISAM Tables
• Table Storage Formats
• Static Table Characteristics
• Dynamic Table Characteristics
• Compressed Table Characteristics
• Table Problems
• Tables Not Being Closed Properly
• InnoDB
• Configure InnoDB
• Sample InnoDB Configuration File
• Using Per-Table Tablespaces
• innodb_file_per_table
• Raw Devices For Shared Tablespace
• InnoDB System Variables
• Problems Creating The InnoDB Table Space
• Create An InnoDB Table
• AUTO_INCREMENT With InnoDB
• FOREIGN_KEY Constraints
• Add InnoDB Data Files
• Remove InnoDB Data Files
• Binary Backup
• Recover
• Move An InnoDB Database
• lower_case_table_names=1
• InnoDB Transaction Model
• Performance Tuning
• More Performance Tuning
• Versioning
• Table And Index Structure
• Disk I/O
• Defragment A Table
• Error Handling
• Restrictions On InnoDB Tables
• Troubleshooting
• Review Questions
• Review

Conclusion

Exercises

• Downloading and Installing MySQL
• Starting the MySQL Server Automatically
• Creating A MySQL Database
• Granting and Revoking Privileges
• Privileges Quiz
• autocommit
• Atomicity: MyISAM vs InnoDB
• Configuring MySQL
• Configuration A
• Configuration B
• Configuration C
• Configuration D
• Configuration E