Becoming a Production MySQL DBA

Prepare a Linux VM on AWS, Amazon Linux 2, for MySQL server installation by configuring SSH access, ports 22 and 3306 in a security group, and a public IP.

Create an AWS account and log into the AWS console to access EC2. Configure SSH key authentication and a security group for ports 22 and 3306 to enable MySQL access.

Install and configure MySQL server community edition on AL2023 by using superuser privileges, enabling and starting the mysqld service, and verifying the 3306 port and MySQL process.

Install and enable the MariaDB server on Amazon Linux 2, start and verify the service, and observe its MySQL-like daemon and file locations to confirm installation.

Download and locally install server rpm packages using yum local install, enable the repository, and start and verify the mysql service.

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, enable, and secure MySQL on Ubuntu 22.04, updating the system, installing MySQL 8.0, starting the service, and removing anonymous users and test databases.

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.

Learn the four-layer MySQL architecture from the client layer to the physical storage, including the mysqld daemon, cache, and storage engines, and how the RDBMS handles undo and recovery.

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.

Learn how deleting the MySQL socket and lock files breaks local connections, and how restarting the MySQL service recreates the socket and restores access.

Discover MySQL global variables and their global scope, including default values. Override these settings via option files or the command line, and view them with show global or select.

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.

Explore the MySQL show command as a read-only query that reveals the information schema and objects like databases, tables, sessions, jobs, and creation details for tables, users, and triggers.

Demonstrate the mysql show command by listing databases, showing processlist, and checking events, then show tables and switch databases to explore schema.

Explore MySQL system databases, including information_schema, MySQL, performance_schema, and sys, plus the test database; understand metadata access via read-only information_schema and admin-friendly insights from sys.

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.

Bob demonstrates storing root credentials securely in an encrypted login path file used by MySQL clients to auto-authenticate with different hosts and accounts.

Use mysql_config_editor to save encrypted credentials for a login path, enabling root login without a password via the client path; verify with print and test.

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 execute the employees.sql file to create a staff table in the employees database, with id, f_name, l_name, title, and is_active, using multiple run methods.

execute sql commands from terminal using the mysql client -e option, without logging into the shell; you can run multiple commands separated by semicolons, with or without saved credentials.

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.

Use the MySQL check utility to check, repair, optimize, and optionally analyze tables. The table is locked during the operation, so plan a maintenance window for production tables.

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.

Learn to load time zone data into MySQL using mysql_tzinfo_to_sql, pulling zone info files from the zone info directory into MySQL time zone tables and configuring credentials.

Learn to safely test MySQL operations using free example databases by downloading and deploying the world database, running world.sql with the MySQL client, and exploring the data.

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 nine MySQL storage engines by running show engines, and learn to install new engines via the plugin directory.

Explore the federated storage engine, disabled by default, which links a requester table on one MySQL server to a remote table on another server, acting like a view.

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.

Log into the MySQL host, recreate the continents table in the world database using the MyISAM storage engine, insert and update rows, and observe that MyISAM cannot rollback transactions.

Explore how the InnoDB storage engine handles transactions, rollback, indexing, and row-level locking through hands-on demos, including creating tables, updating data, and enforcing engine defaults.

Migrate legacy tables to the InnoDB storage engine during MySQL upgrades by using alter table to convert engines from older storage types, as shown in a practical demo.

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.

As a DBA, learn to disable storage engines in MySQL by configuring the disabled storage engines variable in configuration file, restart the server, and prevent table creation with those engines.

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.

Explain MySQL native password and caching sha two password plugins, with caching sha two password as the default in MySQL 8, and advise DBAs to use the latest plugin.

Learn how MySQL roles define named privilege sets, how to create and lock roles, and how to grant roles to users with concrete assignments for continents in the world database.

Learn how MySQL uses option files for startup options, differentiate global, server, client, and user-specific configs, and determine the files used by the MySQL daemon.

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.

Remove comments from the MySQL option file and add the server-id option with a proper value, then restart the MySQL daemon to apply changes and prep for replication.

Learn to relocate MySQL default options by creating a new Etsy/mysql directory, copy the existing my.cnf, ensure proper root ownership, restart MySQL, and verify startup from the new location.

Learn how to relocate the MySQL default option file by copying the config, backing up the old file, restarting the server, and confirming the server comes back online.

Learn to troubleshoot a production MySQL server using strace to see which option file mysqld reads and lsof to inspect open files and their types.

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.

Disable binary logging in MySQL by editing the my.cnf and restarting the MySQL service. Show binary logs to verify, remove bin log files from var lib mysql prod, and restart.

Enable binary logging by configuring a separate file system for binary logs, creating var log mysql bin logs, and setting prod bin log as the base name.

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.

Relocate the MySQL error log by creating /var/log/mysql and a log file, set ownership to the mysql user, configure log_error to /var/log/mysql/error.log, then restart MySQL to apply changes.

Adjust the MySQL error log timestamps to the system clock by setting log_timestamps = system in the MySQL configuration, and apply the change by restarting the MySQL service.

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.

Bob recaps MySQL configuration options, default option files, their locations and structure, option groups and inclusions, security practices, and binary log and data directory moves.

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.

Change the innodb_flush_method from the default fsync to o_direct by editing a dedicated option file, then stop and start MySQL to verify the new flush method.

Learn to enable or disable the MySQL double write buffer, verify its default state, and relocate its files by editing the configuration and restarting the service, noting 16 kilobyte pages.

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.

Carry out the assignment to move the system tablespace to a new home, adjust permissions and config, restart MySQL, and verify the change in information_schema.

Learn how undo tablespaces store undo logs that restore previous data during transactions. Discover the requirement for at least two undo tablespaces and how to relocate them.

Relocate and configure the undo tablespace in ODB, set the undo directory and permissions, and verify undo files while practicing transactions with commit and rollback.

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.

Explore general tablespaces in production MySQL, where each tablespace has its own .ibd file and can contain multiple tables, with file-per-table options vs journal table spaces.

Learn how file-per-table tablespaces give each table its own ibd data file and table space. Note that innodb_file_per_table is on by default from MySQL 5.7.1.

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.

Override the dedicated server settings after turning it on to adjust resources, including clean shutdown, buffer pool size, and redo logs of 50 megabytes.

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 physical (cold) backup by cleanly shutting down MySQL, copy the data directory, system space (undo and redo logs), and the options file with copy commands, then restart.

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.

Learn how logical backups generate sql statements in a dot sql file to move databases, tables, or schemas, verify structures, and use MySQL dump or MySQL pump with parallelism.

Learn to back up MySQL data with mysqldump, capturing table structure and data, using where clauses and ignore options to exclude tables, and back up single, multiple, or all databases.

demonstrate using MySQLpump to back up a single table, inspect the dump, and compare with MySQL dump, highlighting how the added drop table option prevents restore failures and simplifies recovery.

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.

Enable compression for MySQL pump backups by setting the compress output option and selecting an algorithm such as LZ four or Z lib, for space savings.

Compress a MySQL backup using the ls4 algorithm, verify available utilities, and compare uncompressed versus compressed sizes for the world database, preparing for decompression and restoration in the next assignment.

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 xtrabackup creates a hot backup of a running MySQL server and what files—databases, system tablespace, redo and undo logs, binary logs, and config—are saved for point-in-time restoration.

Prepare for a hot backup restore by locating and copying essential data and log files to the MySQL data directory, while noting non-backed items like certs.

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.

Compare traditional binary log file position based replication with gtid-based replication, highlighting how gtid ensures consistency by applying committed transactions from primary to replica.

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.

Configure the primary MySQL server for binary log replication by setting a server ID, enabling mixed binlog format, and creating a replication user; restart the service and show master status.

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.

Demonstrates installing MySQL on a replica server in AWS, including provisioning prod db zero two, updating the system, configuring the MySQL repository and version, and ensuring client and server compatibility.

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.

Start the MySQL service on the replica, verify server id and log locations, and prepare to restore an online backup from prod using the DBA login after completing security setup.

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.

Restore a hot backup on a MySQL replica, ensure the correct system tablespace and the undo and inner db paths, adjust the replica server ID, and troubleshoot replication configuration.

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.

Explore how to use show replica status to monitor MySQL replication, verify the IO and SQL threads, binary and relay logs, and ensure the replica is synchronized with the primary.

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 to clean up traditional replication by aligning primary and replica, reset replication, and prepare to test git replication before starting from scratch.

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.

Master GTID replication in MySQL by solving authentication plugin issues, enabling secure transport, and verifying replication status between primary and replica in cloud and on-prem setups.

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.

Promote a replica to primary and verify MySQL master and replica status, then reset replicas and enforce read-only modes for reliable failover.

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.