MySQL Database Training for Beginners

Explore database design by creating an entity-relationship diagram that maps entities, attributes, keys, and relationships, and understand cardinality in relational databases using MySQL Workbench 6.0.

Install and use MySQL Workbench to model and administer your database locally. Create diagrams, define tables and relationships, and connect as a non-root user for secure development.

Explore how tables organize data into rows and columns, define primary and candidate keys, use foreign keys for relationships, apply constraints, and normalize to third normal form in MySQL.

Learn how to normalize a school database by separating student and course data into dedicated tables and using a student course link table to enforce data consistency and reduce redundancy.

Explore database normalization by splitting instructor and course data into dedicated tables, define one-to-many relationships with foreign keys, and implement lookup tables for departments and levels to reduce repetition.

Explore how string storage choices (char, varchar, text, blob) affect storage efficiency and performance, and review common date types (date, datetime, time, timestamp, year) and their storage.

This lecture demonstrates designing a contacts table by selecting varchar and fixed lengths, applying normalization, and using MySQL Workbench for forward engineering to optimize storage and retrieval.

Master ACID in MySQL by exploring atomicity, consistency, isolation, and durability, and learn implicit and explicit transactions, autocommit, savepoints, and rollback.

Explore row-level and table-level locks, understand deadlocks and their prevention, compare isolation levels, and examine durability through the binary log in MySQL.

Explore how isolation levels and locking affect data consistency and concurrency. Learn to view and set the isolation level for read uncommitted, read committed, repeatable read, and serializable.

Demonstrates row-level locking and read committed isolation as two connections update the same row, shows commit releasing locks, and explains enabling binary logs for durability and how to configure them.

Master the three duties of a database administrator in MySQL—security, performance optimization, and data integrity—using check, optimize, and analyze commands to manage users and privileges.

Create a MySQL user and restrict it to a specific table with granted permissions, then perform routine maintenance using show table status, check table, analyze table, and optimize table.

Protect your MySQL data with logical or physical backups, full and incremental backups, and binary log driven recovery using mysqldump and restore processes.

Establish a performance baseline for the MySQL server, monitor CPU, memory, disk, and network, and target slow queries with server status, show, and OS tools.

Encourage learners to leave a review and rate the course, with an option to update the review later. Show how feedback improves training material.

Explore how InnoDB uses clustered and non-clustered indexes, primary keys, and covering indexes to speed data retrieval while balancing storage and update costs.

Identify the high-frequency queries and test index impact to speed selects without harming updates or deletes; use where clause equality, joins, and indices—clustered, non-clustered, or covering.

Examine how indexing affects MySQL database performance by comparing a 2.2 million row actors table with and without an index, showing explain plans, query times, and a covering index.

Tune a MySQL database system by optimizing memory, log settings, and concurrency based on available resources, and monitor changes with show global variables to improve performance.

Explore how char and varchar choices affect storage and performance, and learn create, alter, and drop commands, plus views and triggers, in MySQL database design.

Explore data definition language (ddl) by creating databases, tables, and views; establish primary and foreign keys, alter and drop objects, and manage dependencies.

Learn to write efficient SQL select statements that retrieve only the needed columns and rows from a table using where, and, like, between, in, then order by to sort results.

Learn to use the SQL select statement to retrieve specific columns and rows, applying where, between, in, like, and order by to filter and sort film data.

Explore how to join related tables in MySQL using inner, left, right, and full outer joins, and when to use union to combine unrelated data.

combine related tables using joins in MySQL to query film, language, film category, and category data, employing inner joins, left joins, right joins, and union to simulate full outer joins.

Master insert, update, and delete commands in MySQL. Learn long and short insert forms, where clauses, archiving inactive data, and set-based operations with aggregate functions.

Explore MySQL insert, update, and delete commands in the products database, practicing with category, product, and prices tables, and use where clauses, safe updates, and truncate to manage data.

Optimize MySQL queries by reducing data retrieved and balancing work between application and database. Review the query lifecycle, including parsing, optimizer decisions, execution plans, and the cache.

Demonstrates query optimization on the Akila sample database, analyzing joins and index usage, reviewing execution plans, and testing slow queries to optimize frequently run workloads.

MySQL replication copies changes from the master to replicas using the binary log, with either synchronous or lazy delivery, not acid compliant across replicas, balancing performance and network traffic.

Follow a 10-step process to set up replication in MySQL, configuring master and slave servers with unique IDs, binary logs, and a replication user, then start replication.

Learn how to protect MySQL data through classification (public, sensitive, secret), encryption methods, and key management, applying column-level and transparent database encryption while avoiding compromised algorithms.

Demonstrates column-level encryption in MySQL by converting the national id field to varbinary, encrypting with AC_encrypt and an encryption key, and decrypting for display, noting performance impacts and legal implications.

Maximize database availability by planning downtime and targeting five nines uptime for high availability. Embed redundancy with standby servers, replication, clustering, and NDB cluster storage to minimize downtime.

Explore MySQL basics through its modular architecture, security, performance, and development responsibilities, learn clustered and non-clustered indexing, and apply explain plans and encryption as a last line of defense.