Faysal AzizMySQL Administration: Replication, Automation and Security
A focused workshop for experienced database administrators
Review MySQL replication, stored-object automation, schema tools and security through selected exercises in a prepared database environment.
Why this course
This one-day workshop is for experienced MySQL administrators. It surveys replication, import/update workflows, triggers, views, stored procedures, scheduled SQL events, schema modelling and security.
The lab environment is prepared in advance. Participants complete a few small exercises and review demonstrations for the broader topics; the day is not a fast track to mastery, optimal performance or a production high-availability deployment.
Learning outcomes
The course teaches participants to:
- Inspect a prepared MySQL server and verify the selected Workbench features are compatible.
- Review import/update choices and data-integrity checks for sample data.
- Explain source/replica replication, lag and its operational limitations.
- Create or inspect a selected trigger, view, procedure and scheduled SQL task.
- Use an EER diagram to review schema relationships.
- Evaluate least-privilege access, TLS and available logging/monitoring options.
- Identify prerequisites and verification needed before applying changes to production.
Prerequisites
- Strong understanding of MySQL and relational database management.
- Working experience and solid understanding of SQL queries and database concepts.
- Prior experience with MySQL Workbench.
A disposable prepared MySQL environment, sample data and compatible Workbench tooling; replication examples require supplied isolated instances.
8 modules
01Environment and Architecture4 topics
- Overview of course objectives.
- Inspect a prepared MySQL Community Server and discuss installation/runtime requirements.
- Check Workbench/server compatibility for the selected functions; do not assume every feature works on newer server releases.
- Overview of the MySQL architecture and essential components.
02Data Import and Updates2 topics
- Compare import approaches and measure a small prepared example; large-scale efficiency depends on workload/configuration.
- CSV and SQL file imports.
- Batch processing for large-scale imports.
- Updating and altering databases:
- Modifying tables and columns.
- Validate updates using appropriate transaction, constraint and recovery considerations.
03Replication and Reliability3 topics
- Understanding replication concepts:
- Source-to-replica replication terminology; recognise older master/slave wording in legacy material.
- Multi-source replication overview, not a full hands-on topology.
- Configuring replication:
- Inspect or configure one prepared source/replica training example.
- Monitoring and troubleshooting replication issues.
- Use cases for replication:
- Where replication can support availability/recovery designs; replicas may lag, and replication alone is not a backup or complete failover plan.
04Triggers3 topics
- Introduction to triggers:
- Types of triggers in MySQL (before/after insert, update, delete).
- Writing and implementing triggers:
- Automating tasks using triggers.
- Use cases for business logic automation.
- Avoiding common pitfalls with trigger usage.
05Views3 topics
- Purpose of views in MySQL:
- Simplifying complex queries.
- Views and privileges: access restriction depends on correct object security and grants.
- Creating views:
- Syntax and examples for creating basic and complex views.
- Managing views:
- Review view update limitations and safe changes in the disposable schema.
06EER Schema Modelling3 topics
- Introduction to the Enhanced Entity-Relationship (EER) Diagram Editor.
- Visualizing database structure:
- Designing schemas and relationships.
- Modifying database designs using the editor.
- Exporting and integrating EER diagrams into your database.
07Stored Procedures and Scheduled SQL Events2 topics
- Creating stored procedures:
- Syntax and practical examples.
- Benefits of stored procedures in automation.
- Implementing scheduled events:
- Scheduled SQL data-maintenance tasks. External backup tools require appropriate external orchestration and restore verification; an Event Scheduler object is not a general operating-system backup job.
- Inspect event status, privileges and overlapping execution; demonstrate only in the disposable training environment.
08Security and Operational Review3 topics
- Importance of database security:
- Common vulnerabilities in MySQL.
- Best practices for securing MySQL:
- User roles and permissions.
- Implementing SSL/TLS encryption.
- Auditing and monitoring:
- Review available logs, monitoring and alert integration; audit capabilities vary by edition/components and are not assumed to be included in Community Server.
Review selected lab results and distinguish a successful example from a verified production backup, failover or security design.
A programme built around your team.
Share your training goals and requirements.