Complete MySQL Database Administration Course

Design and set up a lab environment for MySQL database administration. Apply the lab design and setup concepts from the Complete MySQL Database Administration Course.

Confirm administrator rights, 50 gb disk space, 4 gb memory, and a 64-bit processor with virtualization to set up a CentOS 8 or 9 Stream Linux lab for MySQL.

Design your lab by confirming system requirements, installing virtualization software (VMware Workstation Player recommended or Oracle VirtualBox), creating a virtual machine, and installing CentOS with MySQL Community Edition.

Explain how virtualization on a single physical server uses a virtualization layer to run multiple operating systems simultaneously, maximizing resource utilization of RAM and CPU.

Download and install VMware Workstation Player, verify processor, memory, and disk space, navigate Broadcom licensing restrictions for version 17 or use version 16, and run a virtual machine.

Download and install Oracle VirtualBox as optional alternative to VMware Workstation Player, select your host platform (Windows, macOS, or Linux), and complete the setup wizard, noting the networking reset.

Create a VM with VMware Workstation Player or Oracle VirtualBox for MySQL on Linux. Name it my sql server one, assign 50 GB disk, 4 GB RAM, and 1 vCPU.

Download CentOS Linux 8 ISO and install it on a virtual machine using VMware Workstation Player, configure the hostname, and complete the GUI-enabled server setup.

Download and install CentOS Stream 9 on a virtual machine using VMware Player, attach the ISO image, and complete setup with hostname, root password, and user creation.

Learn virtual machine management by adjusting VM resources, memory, CPU, and disk, and configuring network and storage in VMware Workstation Player, including ISO mounting and bridged networking.

Learn linux basics for database administrator, including logging in, navigating the file system, permissions, and essential commands for managing files and users.

Master Linux basics: root is the superuser, remember case sensitivity and avoid spaces in file and directory names; the kernel passes commands, and the command line interface dominates over GUI.

Learn to navigate the Unix file system using cd, pwd, and ls, switching directories, printing the current location, and listing directory contents with practical Windows/Linux comparisons.

Distinguish the Linux root account, the root directory (slash), and the root home directory (/root). Learn when to log into root and when to navigate to each directory.

Explore directory listing attributes revealed by ls and ls -l, including type, links, owner, group, size, date, and name. See how to distinguish directories, regular files, and links.

Master Linux command syntax by identifying commands, options, and arguments, using examples like ls with -l and -LTR, and exploring mkdir and man for options.

Learn to create files and directories in a Linux home directory using touch, cp, vi, and mkdir, verify with ls, and understand basic permissions.

Learn how to change a user password on a Linux system using the password command, including prompts for old and new passwords, root privileges, and dictionary checks.

Change file and directory permissions with chmod to grant or revoke read, write, and execute access for user, group, and others.

Explore linux help commands, including what is, command --help, and man, to quickly view command basics, extended options, and detailed manuals.

Learn to add text to files using echo and redirects, including overwrite versus append, view with cat, create with touch, and explore the vi editor for editing.

Explore how pipes connect command outputs to inputs using the vertical bar, refining results with commands like ls, more, and tail in a practical Linux workflow.

Master basic linux file maintenance using cp, rm, mv, mkdir, and rmdir, then adjust file ownership and group with change ownership and change group commands.

Master file display commands like cat, more, less, head, and tail to view file contents in Linux. Learn viewing first or last lines, paging options, and navigation techniques.

Learn to use the VI editor on Linux to create and edit files, switch between command and insert modes, and save, quit, search, replace, and undo edits.

Learn to manage Linux user accounts using useradd, groupadd, usermod, userdel, and groupdel, and understand how /etc/passwd, /etc/group, and /etc/shadow store user data.

Learn to switch users with su, elevate privileges using sudo, and configure /etc/sudoers for group-based access, enabling selected users to run root-level commands.

Master Linux process monitoring with ps and top. Learn to view and filter running processes, sort by memory, switch to full paths, and safely manage processes in real time.

Learn how to schedule tasks with crontab, including editing with crontab -e, listing with -l, and managing the cron daemon with systemctl, to automate commands.

Learn Linux network files and commands to configure interfaces, set IP addresses, and manage DHCP or static setups, with ifconfig, ping, netstat, tcpdump, and /etc/hosts and /etc/resolv.conf.

Learn how secure copy protocol (scp) transfers files securely from a local to a remote host using ssh on port 22, with authentication and verification.

Learn to manage system updates and repositories using yum and rpm; yum downloads and installs from configured repositories, while rpm installs local packages and handles dependencies.

Explore the history and forks of MySQL, then install, configure, and verify MySQL on Linux, including prerequisites, distributions, versions, and using MySQL Workbench to restore the tequila test database.

Trace the brief history of MySQL, its open-source evolution, and forks like Maria Dib and Persona Server, then explore cloud deployments via AWS EC2 and RDS.

Assess platform compatibility, choose a MySQL distribution, and download the binary or source package, while distinguishing development releases from general availability releases and performing integrity checks.

Download, install, and secure MySQL on CentOS 9 stream, enable auto-start, and remove anonymous users. Create test databases and connect locally to explore privileges and case sensitivity, with md5 verification.

Install and explore MySQL Workbench on CentOS8 to visually design, connect to MySQL, administer servers, manage users and backups, and monitor performance with visual tools.

Download, install, and secure MySQL on CentOS 8 by upgrading to CentOS Stream 8 and running mysql_secure_installation; log into MySQL, manage a test database, verify integrity with MD5.

Install MySQL Workbench on CentOS 9 stream and explore its graphical interface. Leverage data modeling, administration, performance dashboards, and migration features with a visual explained plan.

Learn to download and install the Sakila database, restore its schema, data, and ER diagram using MySQL Workbench, and explore views, stored procedures, triggers, and relationships.

Explore database fundamentals and designs with MySQL on Linux, covering storage engines, table structures, primary, foreign, and unique keys, transactions, ACID properties, normalization, and creating your first database with data.

Define data as raw facts and information, and distinguish qualitative and quantitative data. Examine how databases organize data into tables with columns and rows and apply data types.

Explore what a database is, how relational databases organize data in tables, and why they outperform traditional file systems and spreadsheets for scalable, multiuser access.

Relational databases store data in tables and define relations among them using the relational model, while a relational database management system uses SQL to create, update, and secure data.

Learn how data is stored in tables with defined columns and data types, creating rows for each record, while SQL manipulates structured data through queries.

Discover how Linux MySQL saves data in the data directory (/var/lib/mysql), with each database as a folder of table files, and how 8.0 uses idb files with serialized dictionary information.

Explore MySQL storage engines, including InnoDB and others, and learn how to choose the right engine for transactional integrity, performance, and use cases such as full-text search and data sharing.

Explore how a table stores data as uniquely named columns and rows of fields. Learn how column types and row sizes shape constraints on tables in MySQL.

Define a key as a data item that uniquely identifies a record and links tables, covering primary, foreign, and composite keys built from single or grouped attributes like student ID.

Explore how primary keys, candidate keys, secondary keys (alternate keys), unique keys, composite keys, and foreign keys identify and relate records in MySQL, with practical examples.

Transactions are atomic units of database manipulation that commit or roll back, and acid properties—atomicity, consistency, isolation, and durability—guarantee reliable, durable data.

Learn how normalization reduces data redundancy and preserves integrity by organizing data into first, second, and third normal forms, using atomic values and separating tables with primary and foreign keys.

Learn to create your first database and a table, define a primary key with auto_increment, and populate data using SQL insert in both MySQL Workbench and the CLI.

Build and verify a simple MySQL table by using create table and describe commands to understand rows, columns, and data storage for database administration.

Explore sql statements, including dml, ddl, dci, and tcl, and understand how these commands work through theoretical concepts.

SQL statements cover data manipulation language, data definition language, data control language, and transaction control language, enabling select, insert, update, delete, create, and drop operations on relational databases.

Master the data manipulation language basics with select, insert, update, and delete to store, retrieve, and modify data in MySQL. Use where clauses and pre-update checks to avoid unintended changes.

Master data definition language concepts by using ddl statements like create, alter, drop, and truncate to define database schemas and objects such as tables and stored procedures.

Apply data control language to grant and revoke granular privileges on databases, tables, and stored procedures, ensuring users access only what their role requires.

Explore tcl concepts such as begin transaction, commit, rollback, savepoints, and lock table and unlock table to manage atomic operations and safeguard data until committed.

Learn to create and manage tables, including temporary tables, write select statements, apply where and group by, explore joins and subqueries, and perform inserts, updates, deletes with labs on indexes.

Create and manage tables and temporary tables in MySQL, define data types and constraints, set primary and foreign keys, and run a hands-on lab in MySQL Workbench to practice.

Learn how to read data with the select statement, including selecting columns, and using where, group by, having, order by, and describe for table details.

Master arithmetic, relational, and logical operators, and apply expressions and conditions in SQL to filter data with select statements, including where, group by, having, and order by.

Explore how to use where, order by, group by, and having clauses to filter, sort, and group data in select statements with practical examples.

Learn how joins enable retrieving data from two tables at once, linking on foreign keys, and leveraging normalization to avoid redundancy, as shown with employee and sales tables.

Explore inner, left, right, cross, and full joins in MySQL, learn how to join multiple tables, and practice with a lab using user and user_profile tables.

Explore subqueries in MySQL, learning how inner queries filter the main query with conditions using in, equal to, greater than, and between, illustrated with customers, addresses, and payments.

Explore how the insert statement adds one or more rows to a MySQL table, using values, copying data from another table or view, or creating a new table via select.

Load a CSV into MySQL by using MySQL workbench or the command line, creating the table on the fly, with fields terminated by commas and header row ignored.

Export data from MySQL to CSV using MySQL workbench or command line. Explore exporting full tables or query results, with configurable separators and save locations for analytics.

Learn how to use the MySQL update statement to modify existing records with set and where clauses, update from another table via join, and apply multi-column updates.

Explore how the delete statement removes rows using from and where, using limit or subqueries, and distinguish delete from drop while backing up before production.

learn how a stored procedure groups and stores sql statements as a reusable unit, accepts in, out, or in out parameters, and can improve performance, reduce network traffic, and enhance security.

Explore how a view acts as a virtual table that stores no data, combines columns from one or more tables, and saves reusable queries.

Define MySQL functions, including user defined and built in, with input parameters and return values; compare them to stored procedures and show a platinum/gold/silver customer ranking.

Explore triggers in MySQL: automatically execute row-level actions on insert, update, or delete with before and after timing, using low level triggers for auditing, archiving, and data integrity.

Understand what an index is and why we need it to speed up data lookup. See how a root-to-leaf index structure locates rows quickly, avoiding table scans.

Explore MySQL index types including unique, primary (clustered), regular, full-text, and descending indexes; learn creation methods, constraints, and performance benefits for queries.

Explore the MySQL architecture and option files, learn how to invoke programs, edit the my CNF file, and understand options scope and which options are in effect.

Explore MySQL's client-server architecture and its application, server, and storage layers, including thread handling, parsing, optimization, caches, and storage engines, to understand authentication and query processing.

Explore the common MySQL programs, from server daemons to client tools, and learn how to invoke them with command-line options or option files, including option precedence and cnf files.

Explore how the my.cnf file and included cnf files shape MySQL server settings, examine precedence of values, and adjust variables like max_connections and data directory with live examples.

Explore the scope of MySQL options, identify system wide, server specific, and user specific CNF files in effect, and learn to view defaults with print defaults.

Explore MySQL backups and restore concepts, including backup types, binary logs, offsite storage encryption, mysqldump, and point in time recovery, with hands-on labs using Workbench and extra backup tools.

Learn how MySQL backups ensure data availability after outages, corruption, or human error, and explore RTO and RPO concepts to guide backup strategies.

Explore MySQL backup types: logical, physical, snapshot, binary log, and incremental or differential backups. Learn how offsite storage, encryption, restore testing, and retention policies safeguard data availability.

Explore binary logs in MySQL, a set of log files recording data-modifying events for replication and recovery, enabling bin logs and reading them with mysqlbinlog.

Explore the mysqldump utility for logical backups of one or more MySQL databases, generating SQL statements (or CSV) to reproduce schemas and data, with practical options and performance considerations.

Learn how to restore a MySQL database from backups using the command line and MySQL Workbench, including creating the database, handling schema and data backups, and recovery best practices.

Learn how to perform table-level backups and restores in MySQL using the dump utility and MySQL Workbench, including selective backups and single-table restores.

Apply point in time recovery in MySQL by restoring the last full backup and replaying binary log events up to a time, using a log position from show master status.

Explore Percona XtraBackup, a non blocking, open source MySQL backup tool that provides consistent, crash-recoverable backups with redo logs and supports streaming, compression, and incremental backups.

Learn to perform a full MySQL backup with extra backup, prepare and verify it, then restore data to the MySQL data directory using a dedicated backup user configured in my.cnf.

Execute full backups and incremental backups with Percona XtraBackup, then prepare and apply logs to restore up to the latest incremental.

Schedule and automate MySQL backups with a bash script and cron, backing up a single database and all databases, storing in daily backups and deleting files older than seven days.

Back up MySQL databases with mysqldump and MySQL Workbench, choosing all or selected databases, data or structure, and options like routines, triggers, and single transaction for consistency.

Explore MySQL security concepts, including root user, create and alter user statements, grant options, and roles and privileges in MySQL 8.0 with hands-on labs in MySQL Workbench.

Explore MySQL users and privileges, including coarse and fine-grained controls and admin versus ordinary accounts, to secure data and ensure availability.

Explore the root user and its super privileges in MySQL. Learn how to set, reset, and recover the root password, including recovery steps.

Learn to create and alter MySQL users, specify local or remote hosts, and manage password, lock/unlock, and expiration. Explore drop user and host-based access options.

Learn to create a new MySQL user using MySQL Workbench, including localhost access and password setup. Manage privileges for specific databases, explore login prompts and password expiration.

Explore how MySQL privileges are granted and controlled across server, database, table, and column levels, including key rights such as select, insert, update, and execute.

Learn how the grant statement works, how with grant option enables privilege delegation, and practice granting select, insert, update, delete, and more on databases, tables, and procedures.

Discover how MySQL grant tables store user privileges and how show grants audit privileges across global, database, table, and column levels.

Learn to use the revoke statement to selectively remove privileges granted by grant, verifying the right user and host for select, insert, update, delete, and execute.

Drop user removes the account and its privileges, clearing related grants; it does not delete databases or objects, and active sessions must close for the drop to take effect.

Create and manage roles in MySQL to group privileges, grant or revoke them at the role level, and assign roles to users for scalable access control.