MySQL Course - 75 Hours, Become a DBA

Create your first database named bookstore using MySQL Workbench, learn about charset and collation, and assign proper user privileges instead of logging in as root.

Create a new MySQL database with a specific character set and collation, then verify, modify with alter, and explore show commands to manage collations and errors.

Create your first author, publisher, and book tables in a bookstore database. Learn data modeling concepts, primary keys, and relational design using SQL and graphical design tools.

Create a new table using sql, defining publisher_id as an auto increment integer primary key and editor name as varchar, and discuss char vs varchar and storage considerations.

Create the books table, define a primary key (single or composite), and use a foreign key to the authors table to enforce referential integrity and relationships across authors and publishers.

Create the livro table with author and publisher foreign keys, enforcing referential integrity with on delete and on update restrict. Visualize the 1-to-many relationships in the ER model.

Explore data modeling foundations for relational databases, including physical modeling, normalization forms, and designing efficient MySQL databases with SQL in Workbench.

Design a club database by modeling data with an ERD in MySQL Workbench, creating a member table with an auto-increment primary key, and defining relationships to payments and dependents.

Design MySQL tables in Workbench by creating fields, using varchar and date types, and applying not null constraints; weigh storage, memory, and normalization up to third normal form for performance.

Build a member data model in MySQL Workbench by adding country, state, city, neighborhood, zip code, full address, and contact fields.

Create a dedicated contact table to store multiple emails and phone numbers for a member, linked by a foreign key in a one-to-many relationship, using Workbench for modeling.

Create a primary key and use foreign keys to model a member's contacts, introduce a separate contact type table, and establish an associative one-to-many relationship to reduce redundancy.

Explore creating a first entity-relationship model in MySQL Workbench by improving a data model through normalization, reducing redundancy, and structuring country, state, city, and address tables; balance normalization with performance.

Learn to create an address table in MySQL Workbench, define a primary key with auto increment, and model a one-to-many relation from member to address via a foreign key.

Create an associative table to model a many-to-many member-address relationship, using composite primary keys from both tables and foreign keys to enforce normalization and avoid redundancy.

Model a member and dependent system in MySQL Workbench by creating a dependent table with generated primary key and a foreign key to the member table, establishing a one-to-many relationship.

Explore MySQL Workbench by building a first MER model, detailing primary and foreign keys, one-to-many relationships, and associative tables for dependents, contacts, and addresses.

Create a payments table to track member fees, enforce a one-to-many relationship with members, and introduce a payment types table with keys, dates, and decimal amounts for accurate club billing.

Generate a physical database from a data model in MySQL Workbench using forward engineering, creating the club schema with tables, primary keys, foreign keys, and constraints.

Learn to generate a MySQL database from a data model in Workbench, including opening the model, defining primary and foreign keys, creating indexes, and exploring table relationships for queries.

Create the database from a provided sql script and use it, then build supplier and product tables with a foreign key and referential integrity on delete and on update.

Populate the client, order, order item, product, and supplier tables using insert into scripts. Learn how to maintain referential integrity by ordering inserts and handling primary keys and foreign keys.

Explore the select command in MySQL, learning how to retrieve specific fields from tables, filter with where, sort with order by, limit rows, and use distinct.

Master the MySQL select with functions like min, max, sum, avg, count, and year. Use where, order by, and aliases within ANSI standards.

Master MySQL data filtering with select, where, and, or, not, in, not in, between, like, is null and is not null. Count, sum, and range queries across dates.

Learn how to use group by to count and group data by country, count rows, and apply aliases, order by descending totals with sum, avg, min, and max.

Master selecting across related tables with inner joins, on clauses, and aliases, using primary and foreign keys; explore reverse engineering to build an entity relationship diagram for clearer queries.

Learn how to pull data from orders, order items, and products using inner joins, aliases, and on conditions to display order dates, totals, product names, and quantities.

Master the MySQL convert function to cast data types for selecting data, converting between character, date, and numeric, while noting SQL Server differences and practicing backups and testing before production.

Master left joins and inner joins to retrieve all members or customers with and without related records, linked by primary and foreign keys.

Explore subqueries, inner and outer queries, and views; compare with joins for performance, and apply in, not in, and exists techniques.

Use exists and not exists to find suppliers with products priced above 100 via efficient subqueries and proper key relationships. Note aliasing behavior in MySQL.

Create a new table from a select query in MySQL using create table as select, filtering to USA. Compare this with SQL Server's select into approach.

Master updating table data with the update command using set and where clauses, preview changes with select, and guard them with start transaction, commit, and rollback.

Learn how to use insert into with values, handle primary keys without auto increment, and enable auto increment via alter table, including managing referential constraints.

Discover how views restrict access and simplify data retrieval by presenting a filtered perspective on multiple tables. Create and use views to predefine complex queries, boosting performance and consistency.

Master inserting into and deleting from single-table views, understand why joins block updates, and learn referential integrity and cascade settings for safe data operations.

Learn how to query data through views, filter by partial phone matches, and manage multi-step transactions with begin, commit, and rollback to ensure data integrity and recoverability.

Build a daily sales view to simplify a complex multi-table select with inner joins on orders, order items, and products; derive year, month, day, and sales.

Learn how to query a view to select and alias year and month, sum sales, group by year and month, and sort by year ascending and month descending.

Modify an existing view with alter view to add columns such as first name and last name, and use concat for a full name while monitoring performance with complex joins.

Explore stored procedures in MySQL: store and call SQL blocks on the server, improve performance via execution plans, boost security and reuse, while weighing debugging, deployment, and memory considerations.

Create and execute MySQL stored procedures using begin and end blocks, manage delimiters, and call procedures to view results and status via routines.

Learn to modify and recreate stored procedures in MySQL using drop procedure, create, and limit clauses. Pass parameters as input, output, or both with call, contrasting with SQL Server behavior.

Explore working with stored procedures in MySQL: pass multiple parameters, use between and like for range and pattern searches, and understand parameter order, indexing, and performance considerations.

Learn how to handle optional parameters in MySQL stored procedures by implementing null checks and defaults, and build flexible price-range queries using min and max values.

Learn to work with MySQL variables using set, colon equals, and double colon syntax; fetch data from order and supplier tables, extract year, count rows, and prepare for stored procedures.

Learn to use output parameters in MySQL stored procedures to return values, counting rows with count(id) and retrieving the result via an output variable.

Learn to create and call MySQL stored procedures with input and output parameters, returning product names by id, and using if, else if, else and while loops.

Master dynamic SQL in MySQL by building, preparing, and executing concatenated queries while managing performance and security risks, including SQL injection and indexing considerations for complex reports.

Explore how triggers in MySQL automatically execute on insert, update, or delete events, attaching to tables to enforce data integrity, automate tasks, and enable audit trails.

Explore creating triggers in MySQL, compare with SQL Server, and log inserts and deletes in a product audit table, including drop table if exists.

create and manage MySQL triggers on the product table to log inserts and deletes into the product audit log table, using delimiter tricks and new/old row concepts.

Demonstrates inserting into the product table and automatic population of the product audit table via triggers, with autoincrement keys, now() timestamps, and tracking deletes in the audit.

Implement MySQL triggers to log who performed changes in a product audit table, adding a user field and the server timestamp, and cover drop and recreate steps.

Explore how a before insert trigger runs before data insertion, compare MySQL before triggers with SQL Server’s after triggers, and see how a sum variable accumulates values.

Compare before and after triggers in MySQL, focusing on execution moments during insert, update, and delete, including potential rollbacks and checks of data.

Learn how MySQL functions work, how they compare to stored procedures, how to create and call deterministic functions, and how they return values using begin, end, and select statements.

Learn to create a MySQL function and compare it with SQL Server practices, using begin and end, delimiter handling, and a practical calculation of sales value.

Build a MySQL function called client level that returns a varchar(20) based on credit, using if/else if logic, set and return, and illustrate correct ordering by calling it.

Use a function we created in MySQL to calculate discounts from quantity and unit price, per order item row, and aggregate results with sum and avg.

Create a MySQL function that sums unit price times quantity, stores results in a table, and use temporary tables to simplify complex queries and boost performance.

Define and manage MySQL users, privileges, and roles with commands and workbench. Learn global, database, and object privileges, plus the grant option and service user access.

Create and manage MySQL users with create user if not exists, assign passwords, and grant privileges across databases, including usage default and host patterns like %.

Create a MariaDB user on localhost, grant select on a table, and verify with show grants; compare mysql community edition to sql server’s login and note MariaDB as an alternative.

Create and manage MySQL users with IP restrictions; grant select on specific fields, update those fields, revoke all privileges (including grant option), illustrating a DBA-like user.

Learn how to grant and revoke select and update privileges, revoke the with grant option, and organize access with roles such as senior, junior, and read-only roles to streamline permissions.

Learn to create and manage roles in MySQL, grant privileges to roles, assign roles to users, and verify role-based access with show grants.

Create a user linked to a developer role, connect as that user, and activate the role to apply the privileges granted to the role, validating active roles and grants.

Create and manage MySQL users, roles and privileges using revoke and grant commands, explore root and DBA privileges, and perform graphically via Workbench, including show privileges and shutdown.

Learn to create users, assign roles, grant or revoke privileges, and set per-hour limits in MySQL using Workbench, with database and schema privileges and secure connections.

Create and schedule automated MySQL events that run at defined times, with event scheduling enabled by default. Use create, alter, show, and drop commands to manage events and their history.

Create a MySQL event that runs automatically one minute from now using interval and now to insert data. Learn how to schedule and verify the event's one-time execution in lab.

Learn to create a one-time MySQL event that runs on a specific date and time, performing an insert and a delete inside a single transaction with start transaction and commit.

Demonstrates creating and executing a MySQL event that automatically runs a stored procedure to create a table in a database, illustrating event scheduling, automatic execution, and idempotent object creation.

Create a monthly event that runs every month, starts on a specific day, and ends on chosen date. It updates a table to purge old data as a recurring schedule.

Create a MySQL event that runs a procedure every hour, starting now and ending ten hours later, using server time and interval expressions.

Learn to manage MySQL events by enabling or disabling them without deletion, altering or renaming them, and applying changes that run hourly and perform inserts, deletes, or truncates.

Learn to perform robust backups in MySQL Workbench, both manually and automatically, exporting databases, tables, views, and procedures for reliable data protection and easy restoration.

Learn how to back up and restore MySQL databases using Workbench, including file-by-file versus single-file backups, rollback-safe strategies, and InnoDB considerations for reliable recovery.

Back up MySQL databases using mysqldump from the operating system, locate the datadir and set up a dedicated backup folder, and use a snapshot that avoids locking tables.

Learn to back up all databases with mysqldump, including routines and events, and to back up specific databases or a single table, with guidance on file organization and restoration.

Master manual backups and restores of MySQL databases using mysqldump and the mysql client from the command line. Learn to handle drop database operations and troubleshoot common issues.

Back up a single account table with mysqldump, drop it, and restore from the backup into the client two database; learn about table locking during restoration.

Learn to implement incremental backups with mysqldump, combine full and transactional backups, and use binary logs for point-in-time restoration and replication.

Learn how MySQL uses binary log files for point-in-time recovery and incremental backups, including managing binlog size, recycling up to 30 files, and purging logs safely.

Master incremental backups with mysql dump by preserving binary logs for precise point in time recovery; learn safe transactions and log purging to manage disk space.

perform a full mysql backup with mysqldump, create incremental binlog backups, and restore data by applying transactional logs to recover to a precise point in time.

Learn to protect binlog files for incremental backups and restore a full backup by applying transactions.

Create a robust mysqldump backup workflow that builds a full backup, compresses backups and binlog files, transfers them to a separate disk, and deletes originals via a secure config file.

Create incremental backups and compress them with seven zip to cut disk space and cloud storage costs, and verify seven zip is installed and accessible via cmd and PowerShell.

Develop a PowerShell backup script for MySQL named backup full db ps1 that automatically backs up, compresses, and saves to a designated folder, with admin PowerShell execution policy steps.

Explore building a PowerShell-based MySQL backup workflow that uses mysqldump, config files for credentials, timestamped backup files, error logging, and automated retention with compression and old-file cleanup.

Prepare a PowerShell script to back up a MySQL database, set precise file paths, run it manually to verify, then schedule automatic backups with Task Scheduler and monitor zip archives.

Back up all databases with a MySQL dump using the all databases option to create a single date-stamped SQL file, including stored procedures, functions, routines, and events.

Automate binlog backups with a PowerShell script, recycling logs, compressing them into a dated zip, and storing them in a designated backups folder for recovery.

Learn to create automated MySQL full backups on Windows using Task Scheduler and PowerShell scripts, scheduling tasks for a DB test backup, full instance backups, and transaction logs.

Schedule a full backup of all MySQL databases with a PowerShell script, configure a log backup every ten minutes, and verify restoration using bin logs and full backups.

Explore using PowerShell and batch scripts to automate MySQL backups on Windows and Windows Server, including creating batch files, scheduling backups with Task Scheduler, compressing, and securing credentials.

Automate MySQL backups by creating Windows Task Scheduler triggers for full backups, all databases, and log backups using a batch file and mysqldump.

Learn to restore a MySQL full backup and apply binlog transactions to recover to the moment before a crash, using backups created by task scheduler and careful restoration steps.

Learn Percona Xtrabackup for fast physical backups on Windows, compare with mysqldump's logical backups, and master full, incremental, and compressed backups across Windows and Linux in lab settings.

Explore using the MySQL clone plugin in Workbench for fast physical backups. Learn its limitations, such as InnoDB dependency, same-version cloning, and no binary logs.

Perform high-performance physical backups with the MySQL clone plugin without locking tables, by editing my.ini to load the clone dll, configuring IO and network limits, and restarting MySQL.

Perform fast physical backups by cloning all databases on a MySQL instance with the clone plugin, storing a complete clone backup in a secure local folder for disaster recovery.

Simulate a major disaster on your SQL instance to practice recovering all databases with the clone plugin and safeguard configuration by backing up my.ini.

Learn to fix a failed database copy after restoring databases by re-copying and replacing corrupted files, and by reviewing the SQL error log and backups.

Demonstrates fixing a startup error in MySQL Workbench by cleaning junk files in a user folder path and setting up an automated backup routine for the next class.

Learn to automate MySQL cloning with Workbench events and Windows task scheduler, pairing clone and move and compress steps to run daily without manual intervention.

Master automating backups by creating a Windows task scheduler that copies clones to a separate disk, timestamps, compresses, moves to final location, and deletes redundant folders to streamline recovery.

Learn to integrate MySQL Workbench with Windows Task Scheduler to automate backups in Windows and Linux environments by running a three-minute recurring event that clones databases and compresses folders.

Move all databases to a separate disk, back up first, stop the database service, copy files to a new location, update the data directory, restart, and verify.

Learn to run integrity checks with DBCC CHECKDB, assessing logical and physical structures; update statistics and indexes, and perform repairs only after backups.

Learn to perform integrity checks on MySQL databases by verifying table structures, running targeted or full database checks, interpreting results, and planning repairs or backups.

Learn to perform integrity checks and repair corrupted database tables using command-line options such as -r, -R, and -f, after backing up the database.

In block 4, learn to automatically check all tables in all databases with a script, generate daily log files, and delete logs older than 15 days via Windows task scheduler.

Learn how to implement auditing in MySQL by configuring logs to capture executed commands, user activity, and performance data, enabling slow-transaction logs, and supporting internal and external audits for compliance.

Enable and manage MySQL audit logs, generating logs in tables and in files, while using a separate fast disk to protect production performance.

Activate the audit to record slow sql transactions, log results to a table, and learn how disk access, memory, and blocking influence performance.

Configure MySQL block 4 to log slow commands over five seconds, saving the on-disk log in Windows and verifying service user permissions to write in the data folder.

Configure MySQL to generate audit logs in files or tables by editing the my.ini or cnf file, enabling the general log and slow log, and ensuring logs persist across restarts.

Learn to generate audit logs in MySQL using triggers and track tables. See how inserts, updates, and deletes are logged with who, when, and what changed.

Explore MySQL enterprise auditing in mission-critical environments, using an auditing plugin to capture who did what, when, and where, within a virtual lab running Windows Server and SQL Enterprise.

Download Windows Vista from Microsoft's official site, save the 180-day trial, and keep Windows and SQL Server in English for the online Azure environment.

Download and install VirtualBox from official sources to create and manage virtual machines for the course, using the Windows version with admin privileges.

Create a VirtualBox VM as the domain controller for a SQL Server environment, allocating five gigabytes of memory and a dynamically allocated disk with Windows Server installer ready.

Install Windows Server on a virtual machine using VirtualBox, mount the ISO, set language and admin credentials, and prepare a second VM to host SQL Server in a domain controller.

learn to configure VirtualBox to exchange files between a Windows Server VM and the host by installing guest additions, enabling bidirectional shared clipboard and drag-and-drop, and mounting shared folders.

Rename the virtual machine to a domain controller–related name using Windows settings, avoiding forbidden characters, then restart to apply the new domain controller designation.

Explore how a domain centralizes user login and access to computers, servers, and printers through a domain controller and global catalog, with DNS and DHCP support.

Configure the virtual machine’s network adapter, assign a fixed IP on the same network as the host, switch to bridged mode, and verify connectivity with a ping.

Promote a simple virtual machine to a domain controller by installing the domain services feature with Server Manager and creating a new forest with a root domain.

Learn to create and configure a dedicated service account in Active Directory for running SQL Server services, including domain user setup, password policies, and domain-joined deployment for enterprise environments.

Create and configure a dedicated virtual machine for MySQL, naming the VM, allocating memory, and adding separate data and log disks on Windows Server 2019 with bridged networking.

Initialize the database VM by installing Windows Server, selecting the desktop experience, and configuring disks. Then prepare to join the domain and install SQL Server on the VM.

Install and configure VirtualBox guest additions to fix video display and enable full-screen mode. Set up shared folders, bidirectional clipboard, and automatic mounting, then reboot to verify improved video resolution.

Configure a database VM in VirtualBox, assign a fixed IP and DNS, and set up two disks for data and log with volumes for database files, preparing domain participation.

Learn to join a database VM to the domain by validating network connectivity with the control VM and using domain administrator credentials to enroll and log in.

Install and configure MySQL enterprise edition on a Windows server, enabling enterprise auditing via a plug-in. Deploy in a virtualized environment using VirtualBox or Hyper-V.

Download and install the MySQL enterprise suite, unzip to create three folders for the installer, engine, and workbench, and copy the files to the server via a shared path.

Install MySQL Server Enterprise in a best-practices server environment, using a domain service account to run the database, and optimize memory and storage for virtual machines.

Connect to a MySQL enterprise server via Workbench from inside or outside a virtual machine, configure privileges and ports, and create a bank database with tables.

Identify why MySQL does not start after editing my.ini by checking the error log and backing up the config. Remove stray hexadecimal characters with a hex editor.

Learn how to apply a dark theme in MySQL Workbench by replacing an XML color file, with steps to back up the original and deploy on Windows, Mac, or Linux.