Becoming a Production MySQL DBA

Learn to use the mysql_secure_installation script to secure a fresh MySQL server: set the root password, restrict remote logins, and remove test databases and anonymous users.

Perform a secure MySQL installation by locating the /usr/bin script, logging in with the temporary root password, setting a strong root password, removing anonymous users, and reloading privileges.

Install and verify common linux utilities such as wget, ls, and net-tools using yum and rpm. Identify their packages with rpm -q -l and yum provides.

Identify where MySQL stores its files, including the data dir at the default var lib mysql, the log file var log mysql.log, and the global config file under ETSI.

Explore the locations of mysql executable programs installed with the mysql server, located under /user/bin on Linux, and learn how a normal user with permissions can run them.

Explore how to view and set MySQL session variables, verify SQL mode with show session variables, and understand that changes persist only within a session.

Identify how local host, specific host, and any host connections define who can log in to a MySQL server, including root restrictions, application user boundaries, and the percent sign meaning.

Explore how MySQL stores users in a user table, identify internal users without passwords, and show that the root login is limited to localhost while remote users are introduced.

Bob explores the MySQL server architecture, data and log locations, the socket file, and configuration files, uses shell commands, and understands system variables, default databases, and authentication.

Explore essential MySQL DBA interview topics, from provisioning microservice credentials and datadir discovery to global versus session variables, socket files, and MySQL user accounts.

Explore how to use mysqladmin to check server status, verify availability with ping, view version, and create a database while listing databases.

Learn four practical methods to execute SQL files in MySQL: using source in the shell, the MySQL client with a file input, a shell script, or piping.

Learn to import a tab-delimited staff.txt into a MySQL staff table, handle auto_increment id, specify columns, and reset table data with delete before import.

Bob logs into the MySQL host, runs mysqlcheck on the staff table, consults the help for options and database names, and confirms data integrity for each row.

Use mysqlshow to display database information, list all tables, and inspect a staff table's columns, including data types, null or not null, and primary keys on the employees database.

Bob downloads the world db zip with wget, unzips it, and uses the MySQL client to load world db, view three tables, and confirm the first five rows from country.

Use the mysqlbinlog utility to inspect binary log files, list and view events with show binary logs and show binlog events, and convert logs to text to locate drop statements.

Explore the memory storage engine, formerly heap, an in-memory, non-persistent option ideal for temporary or cached data and lookup tables, with caveats like no transactions, foreign keys, or text/blob columns.

Create a continents table in the memory storage engine, insert seven rows in the world database, and verify its metadata in information_schema before restarting MySQL to show data loss.

Bob demonstrates the black hole storage engine in MySQL by creating a table with engine=memory, inserting data, and showing that a select from continents returns an empty set.

Learn how to create and manage a continents table using the CSV storage engine in MySQL, including dropping, recreating, inserting, querying, and exporting the continents.csv file to Excel.

Explore the MyISAM storage engine, its indexed sequential access method, and speed in data warehouses, and note its lack of transactions versus inner DB with the default ADB recommendation.

Learn to load, install, use, and uninstall a new storage engine on a running MySQL server via the pluggable storage engine architecture, plugin directory, and shared libraries.

Bob learns about storage engines in MySQL, identifies the default and recommended engines, and can check status, install, uninstall, disable, and migrate tables between engines.

Learn how the with grant option clause lets a DBA grant permissions to others, and see an example creating Bob with all privileges and grant option for remote login.

Learn to connect to a remote MySQL host using three tools, regular MySQL client, MySQL shell, and MySQL Workbench, and verify TCP/IP connections as a production DBA.

MySQL's mysqld reads default options from files in a specific order; check server-related files first, then the user-specific my.cnf, to determine which file started the server.

Demonstrates using estrus (strace) and lsof to inspect a running MySQL service, examining file descriptors, open files, logs, and memory-mapped libraries to understand connections and server behavior.

Explore option file inclusions in MySQL, using include and include_dir directives to assemble multiple config files and restart MySQL to apply changes.

Apply option file inclusions by copying the option file into the correct directory, include da, verify the file exists, and restart MySQL with systemctl to complete the assignment.

Discover how MySQL binary log files record changes as events, enable replication and point in time recovery, and configure retention and the base log name, plus enabling or disabling logging.

List and inspect MySQL binary log files with show binary logs and show bin log events, then purge binary logs to free space using the purge binary logs command.

Understand how the MySQL error log file records startup and shutdown events, diagnostic messages, and component entries; learn how log_error directs logs to console or a specific file.

Spot duplicate entries in the MySQL error log option, delete the repeated line, restart the server, and verify the final log destination with show variables.

Explore the InnoDB storage engine, a general purpose, high reliability and performance foundation for OLTP and OLAP, supporting transactions, row-level locking, and features like caching and replication.

Explore InnoDB architecture by linking in-memory structures like buffer pool and adaptive hash index with on-disk components such as the system tablespace and redo logs, highlighting primary key and indexes.

Explore how the InnoDB buffer pool caches data in memory to speed up queries and reduce disk I/O, and tune memory size and the flush method, including fsync considerations.

Configure the InnoDB redo log files by increasing each to 100 MB, placing two in the redo logs group directory, then restart and verify MySQL.

Understand global and session temporary table spaces, where the session space holds temporary tables and the global space stores rollback segments, with ip temp one and related files showing size.

Enable the inner db dedicated server and verify its effect on buffer pool size and log file settings, then compare them to configured values and plan overrides.

Prepare for production MySQL backups and recovery by understanding physical and logical backups, and protect the instance with option files and redo logs in source control.

Perform a cold backup by cleanly shutting down MySQL, copying the data and config directories to a temp backup, excluding redo, double write, and bin logs, to prepare for restore.

Back up all MySQL accounts, including users and roles, using mysqlpump with databases excluded and include drop user before create user in a users dump file.

Master restoring dropped MySQL accounts from backups, recreate users and roles, grant and flush privileges, and distinguish users from roles with monthly backups.

Learn how to compress the MySQL dump output, decompress the backup, and restore the world database using the MySQL client, demonstrating space savings and a practical recovery workflow.

Always use the single transaction flag when taking logical backups from mysqldump or mysqlpump to produce a consistent data dump without locking the database.

Explore online hot backups for large MySQL databases, comparing logical and physical backups that keep services online, and review MySQL Enterprise Backup and Maria Backup.

Learn how MySQL replication moves data from a primary to a replica on separate servers, with asynchronous defaults and options to replicate databases or specific tables for high availability.

Explore replication formats by examining how bin log format controls statement-based, row-based, and mixed replication, and compare their advantages and disadvantages for configuring the primary and replica.

Enable binary logging on the primary, assign a unique server ID for each node, and optionally create a dedicated replication user with replication slave privileges; set the binlog format.

Clean up stray option file lines copied from mid rc and f into replication rc and f to keep the configuration tidy and ensure correct replication cnf settings.

Install MySQL on the replica server and perform a minor upgrade to version 8.0.28, including the extra backup and server updates. Align prod and replica.

Set up a binary based, position based replication by backing up the primary, restoring on the replica, and configuring the binary log file and position to start streaming data.

Copy a hot backup from the primary MySQL server to the replica, test connectivity, enable password authentication, create a dedicated user, and transfer files with scp.

Configure binary log position based replication by retrieving the log file name and position from extra backup info, then use change master, start replica, and show replica status.

Demonstrates pausing and resuming replication by stopping and starting the IO thread, and shows how binary log positions influence the relay and database visibility (scratch) during replication.

Demonstrates replica sql thread, showing how to pause and resume replication, check replica status, and apply a finance database on the primary to verify replication with io and sql threads.

Demonstrates how to ignore the finance database in MySQL replication by configuring the replica with replicate ignore db, starting and stopping services, and verifying ignored tables.

Learn GTID-based replication, the recommended method, guaranteeing primary to replica consistency by applying committed transactions. The primary still requires a binary log; GTID removes the need for log positions.

Execute a production failover by switching the primary to the replica, enable binary logging, reset relay logs, and promote the replica to primary in a grid-based replication setup.

Complete the assignment by configuring the old primary to become a replica, enabling replication, starting MySQL, and validating failover with replica and master status checks.