Version 8.0 of MySQL introduced significant administrative enhancements to the MySQL database system, including advanced error logging capabilities, password policies, privilege management, user accounts, and comprehensive backup and recovery functionality. Throughout this training course, you will gain hands-on experience performing these administration functions using both the MySQL Workbench interface and command-line utilities. This course can be taken as a standalone one-day training program or as part of a four-day comprehensive series that combines this course with the three-day Querying MySQL course to provide a complete learning experience.
Audience:
- Database administrators
Prerequisites:
- Prior hands-on experience with MySQL Querying
Course Outline:
How to design a database
- Utilizing MySQL Workbench for database design
- Opening existing EER models
- Creating new EER models
- Working with EER model elements
- Working with EER diagram representations
How to create triggers and events
- Working with trigger functionality
- Creating BEFORE triggers
- Using triggers to enforce data consistency
- Creating AFTER triggers
- Viewing, modifying, or dropping triggers
- Working with scheduled events
- Enabling or disabling the event scheduler
- Creating and configuring events
- Viewing, modifying, or dropping events
An introduction to database administration
- Database administration concepts and principles
- Database administrator responsibilities and duties
- Types of database files and their purposes
- Types of log files and their functions
- Monitoring server performance and health
- Viewing server status information
- Viewing and terminating active processes
- Viewing status variables
- Viewing and configuring system variables
- Configuring server settings
- Setting system variables through MySQL Workbench
- Setting system variables using text editors
- Setting system variables using SQL statements
How to secure a database
- Introduction to user account management
- Introduction to SQL statements for user account management
- Summary of available privileges
- The four privilege levels in MySQL
- The grant tables in the MySQL system database
- Working with users and privilege assignments
- Creating, renaming, and removing user accounts
- Specifying proper user account names
- Granting privileges to users
- Viewing and reviewing privileges
- Revoking privileges from users
- Changing user passwords
- Scripts for creating user accounts
- Working with role-based access control
- Creating, managing, and removing roles
- Scripts for creating users and roles
- Using MySQL Workbench for user management
- Working with users and privilege assignments
- Connecting as different users for testing purposes
How to back up and restore a database
- Developing strategies for backing up and restoring databases
- Creating and implementing a backup strategy
- Implementing a restore strategy
- Backing up a database
- Using mysqldump for database backups
- Creating SQL script files for database backups
- Setting advanced options for database backups
- Restoring a database
- Using SQL script files to restore full backups
- Executing statements in the binary log
- Importing and exporting database data
- Exporting data to a file
- Importing data from a file
- Checking table integrity and performing repairs
- Using the CHECK TABLE statement
- Repairing MyISAM tables
- Repairing InnoDB tables
- Using the mysqlcheck utility program
- Using the myisamchk utility program
