MySQL for Database Administrator

Install and upgrade MySQL across Linux, Windows, and Mac by selecting a distribution, resolving dependencies with yum or dpkg, configuring services and my.cnf, and handling password changes in newer versions.

Install MySQL on a Linux on-premises server using yum repository, start services, secure installation, set root password, remove anonymous users, and verify 8.3 community server version.

Install and configure MySQL via repository-based installation, create a system user, initialize the data directory, and start the daemon while setting data and socket paths.

Understand MySQL installation files and directories, including the data directory at var lib mysql, my.cnf, base and bin directories, and InnoDB, system tablespace, and data dictionary components.

Learn how to connect to a newly installed MySQL server, configure the client, log in as root, reset the temporary password using alter user, and access a database.

Configure the MySQL service by installing from different packages, stopping the server, and preparing a systemd unit. Enable and start the service with systemctl, then verify its status.

Upgrade MySQL from 5.7 using the rpm, stop and restart the service, validate readiness with the upgrade utility, and rely on MySQL 8.x auto upgrade.

Deploy and run MySQL with Docker by loading the MySQL Enterprise Server 8.0 image, launching a container, securing the root password, and verifying status with Docker commands.

Explore the MySQL architecture and file structure, create and attach general and external tablespaces, and observe how data files map to tables within a server environment.

Demonstrates configuring the InnoDB buffer pool in MySQL, increasing size from 128 MB to 12 GB (75 percent of memory), editing my.cnf, and restarting the instance to apply changes.

Modify MySQL settings through the command-line arguments by adjusting dynamic and global variables, ports, and maximum connections, then start and stop MySQL server using the terminal.

Modify the MySQL configuration file to adjust the port and max connections, restart the service, and verify that changes show in the option file source and are applied on restart.

Change dynamic settings at runtime to adjust port, max connections, and autocommit without restarting the service. Use set commands to apply and verify changes.

Persist global variables in MySQL by changing max_connections with persistent settings, restart to see enduring values, and learn how autoconf, auto.cnf, and performance_schema reflect startup versus runtime changes.

Learn to configure the MySQL client to connect without a password by using a credentials file (my login.cnf), enabling localhost login on port 3306.

Run multiple MySQL server instances on a single host by creating separate data directories, configuring a multi-server setup, and managing them with systemctl across ports 3311–3314.

Configure the slow query log to monitor long-running queries in MySQL. Use performance schema and admin tools, with table-output logging for analysis.

Learn to monitor MySQL performance using the performance schema, configure setup instruments and setup objects, and identify slow queries by latency measurements and sleep-based examples.

Install and configure MySQL Enterprise Monitor to monitor MySQL databases using the bundled MySQL database, configure ports, and access the interface via browser.

Use MySQL Enterprise Monitor and MySQL admin tools to monitor server activity, view connections, status, and CPU usage, and analyze performance with graphs and load testing.

Create and manage MySQL users and roles, configure host access, set passwords and expire them in 30 days, rename roles, grant privileges, and remove users and roles.

Create users and a manager role, grant select, insert, update, delete on the implied database, then activate the role in the session and test access to employee and salaries tables.

Enable SSL for secure MySQL connections by configuring SSL on both client and server, restarting the service, and verifying TLS versions and certificates.

Enable SSL for secure connections in MySQL by logging in via tcp, verifying SSL status and cipher, editing my.cnf, and restarting the service.

Demonstrates encrypting MySQL data at rest using an encrypted file plugin and keyring, enabling InnoDB, binary log, and undo log encryption, and rotating master keys.

Configure the MySQL enterprise firewall, train whitelisted statements for a user, and enable firewall protection to safeguard data. Observe how the firewall blocks unlisted queries and permits whitelisted patterns.

Diagnose slow MySQL performance by tracing blocked queries with the process list and performance_schema, identify lock waits and blocking transactions, and practice resolving with timeouts and killing sessions.

Learn how to identify slow queries and boost MySQL performance by adding and refining indexes, generating explain plans, and comparing optimizer choices to minimize scanned rows.

Learn to use the MySQL Query Analyzer to identify poorly performing queries, analyze their execution time with graphs and views, and optimize performance by indexing and explain plans.

Plan a MySQL backup strategy by evaluating hot, warm, and cold backups. Explore logical, physical, snapshot, replication, and incremental backups.

Master MySQL backups with dump and MySQL pump, configure secure_file_priv and backup locations, and verify dumps from SQL and text files across databases such as Sakila.