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.

Select Amazon Linux 2023 as the latest Linux operating system and follow lessons to create a new VM, key pair, security group, and connect to EC2 for beginners.

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.

Create an AWS account, log in to console, and set up a key pair and security group for an EC2 instance on Amazon Linux 2023, enabling SSH and MySQL access.

Create an Amazon Linux 2023 EC2 VM in the Northern Virginia region, configure a t2 small instance with 8 GB gp3 storage and public SSH access for MySQL server installation.

Identify Amazon Linux 2023's compatibility with Red Hat Enterprise Linux 9 for MySQL. Download the MySQL 8 no-arch rpm repository and prepare to install on the VM.

Download the MySQL 8 community repo for enterprise linux nine in a temp directory, install it with rpm and yum, and verify the repo and gpg key setup.

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.

Bob demonstrates installing Percona Server for MySQL, downloading the latest release package, installing locally, enabling the service, and verifying the server runs with status checks.

Remove mysql by locating and deleting repo package and server client libraries. Delete the mysql user to purge the data directory, search for leftovers, and verify cleanup with linux utilities.

Becoming a production MySQL DBA covers installing a specific MySQL version, 8.0.26, by downloading the repo package, installing locally, enabling it, and installing server, client, shared, and shared combat packages.

Install a specific MySQL version by enabling the 8 repo, setting the version to 8.0.26-16, and installing shared-compat, compact, client, and server in order, then verify and start the service.

Download the 8.0.28 MySQL rpm packages from Percona and install them locally using wget, selecting server, shared, compat, and client packages on a CentOS/Oracle Linux 7 system.

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.

Learn basic Linux utilities and commands, including pidof for the running process id, netstat for network connections, lsof for open files, and wget for non interactive downloads.

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.

Bob learns to install and manage MySQL, start and stop services, securely uninstall, harden the server by setting root password, removing remote root, and deleting anonymous users using Linux commands.

Explore common DBA interview questions covering basic MySQL knowledge, default port 3306, using netstat to check open ports, and locating the MySQL pid with pidof on rpm-based systems.

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 MySQL data directory and its physical files, including .cnf configuration files, log files, and the dx log; learn how these files define server behavior and access control.

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 systemd uses systemctl to manage the mysqld.service—start, stop, enable, and status—while noting two systemd locations and to avoid editing files directly, using systemctl for changes.

Master essential MySQL shell commands for daily dba tasks, including help, status, system, use, sauce command for SQL files, prompt, and added command, with a practical demo.

Master MySQL shell commands to log in locally via unix socket, view status, server uptime, and slow queries; run os commands, source sql files, and execute statements like hostname.

Learn how MySQL creates the unix socket file (mysqld.sock) at startup to manage local connections, stored in /var/lib/mysql, while remote connections use TCP/IP; a lock file tracks the server PID.

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.

View and adjust MySQL global variables with show global variables and set global, using max_connections as an example, and learn how global scope interacts with persistence via the option file.

Learn how MySQL session variables affect only the current session, how to change them on the command line, and how they reset after quitting (using sql_mode as an example).

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 inner db startup options and system variables, including global and session scopes, dynamic variables, and how to set or change values via command line or option file for MySQL.

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.

Explore MySQL system databases by querying information_schema and performance_schema, describe tables and their columns, recognize views pull from base tables, and review users and hosts.

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.

Master MySQL Shell, an advanced command-line client and code editor for MySQL on macOS, Linux, and Windows, with Python and JavaScript scripting and classic or X protocol connections.

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.

The MySQL admin client provides tools to manage MySQL server tasks, including creating and dropping databases, checking status, pinging the server, and managing replication.

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.

Use mysqlimport to load tab-delimited data from a file named after its table into a specified database, addressing errors such as column mismatch and secure file path restrictions.

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.

Inspect the employees database schema with mysql show to enumerate tables, fields, data types, nullability, defaults, and constraints, generating a focused report for DBAs with restricted permissions.

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.

Load the time zone data into MySQL by running mysql_tzinfo_to_sql on the zoneinfo directory, piping into the MySQL client, and verify time_zone_name and time_zone_transition tables contain 1786 rows.

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.

Review the binary logs to locate the drop database employees command. Trace the event in the binary log to confirm when the database was dropped.

Bob saves credentials in DOT my login CNF and uses the MySQL config editor. He manages databases, monitors connections, runs SQL files, imports data, checks integrity, and reads binary logs.

Explore MySQL storage engines and the pluggable architecture that supports transactional, in-memory, temporary, and CSV tables. Learn to install, uninstall, and inspect engines via show engines and plugin_dir.

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.

Explore the black hole storage engine, where written data is never retrieved and returns an empty result set, akin to /dev/null, used for master-slave replication where one node stores data.

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 MySQL stores tables as csv files using the csv storage engine, noting no transactions and no indexing. Explore why sharing csv data with Excel is useful.

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 the archive storage engine in MySQL, which compresses unindexed table data into dot rc files using gzip, with no deletes, updates, or partitioning support.

Explore the archive storage engine in MySQL by creating and populating a table; learn that index and delete operations are not supported and data resides in gzip compressed rs files.

Explore InnoDB, the acid-compliant storage engine for OLTP, featuring atomicity, crash recovery, transaction-level isolation levels, durability, a buffer pool, locking, and indexing, and its default behavior for table creation.

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.

Learn to check storage engine status with show engine in MySQL, view transaction and lock details, and diagnose blocking and row locks through a practical commit-or-rollback example.

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.

Set up a remote non-root MySQL account for Bob, explain roles versus users, manage permissions with grant option, and practice locking, password expiry, and changes through demos.

Understand MySQL permissions through authentication and authorization, mastering grantable privileges such as all, alter, drop, create, execute, insert, update, delete, rename, show, and replication replica controls.

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.

Create a non-root DBA user, assign a strong password, configure host access, grant all privileges with grant option, then flush privileges and verify with show grants.

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.

Explore how to use MySQL Workbench to manage servers, view status, handle users and privileges, monitor connections, and perform data export and import.

Learn to create regular MySQL users with Workbench or the client, inspect mysql.user, set an initial password, enforce expiration, and require password changes via alter user.

Grant and test permissions in MySQL by assigning select, insert, update, and delete on a schema and specific tables, and verify with show grants.

Learn how to manage MySQL user accounts as a DBA by listing users, identifying locked or expired passwords and authentication plugins, and locking or unlocking accounts with alter user.

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.

Create reader, writer, and db admin roles with appropriate permissions, then create users and assign those roles. Optionally flush privileges and verify roles by querying users and their authentication strings.

Bob demonstrates MySQL user administration by creating non-root users with full permissions, enabling remote logins, and applying grant option, while distinguishing roles from users and managing password expiry.

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.

Learn to configure MySQL option files by understanding comments starting with a pound sign, option groups or stanzas, and option=value syntax with optional spaces, quoting rules, and dash-dash prefixes.

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 how to use server_id as a variable or option in the option file, keep naming uniform (underscore preferred), and apply changes by restarting MySQL.

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.

Relocate the MySQL data directory to its own dedicated file system, configure the data_dir option in the my.cnf, shut down the server, move the data files, and restart to verify.

Move the MySQL data directory to a new prod path by stopping the service, relocating files, setting ownership to MySQL, updating my.cnf, and validating with show global variables like data_dir.

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.

Learn to set binary log retention in MySQL by converting expire times from seconds to days, adjusting expire_logs_days from 30 to seven days, and applying the change with a restart.

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.

Configure the MySQL temporary directory using the tmpdir system variable, defaulting to /tmp, and set a dedicated path under /var/log/mysql for temp files owned by MySQL.

Edit the mysql config to set tmpdir to /tmp, create the /tmp directory, restart mysqld, and verify by logging into mysql.

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.

Dedicate physical hardware for MySQL and allocate about 80% of RAM to the innodb buffer pool, with 20% for the operating system, and set pool as chunk size times instances.

Explains InnoDB buffer pool size, derived from the inner db buffer pool and instances, verifies 128 MB in status, and shows increasing to 256 MB via the option file.

InnoDB log buffer holds data to be written to the redo log files, allowing transactions to run without committing until flushing to disk; size is 16 MB in MySQL 8.

Check the global log buffer size, default 16 megabytes, via show global variables, and monitor the log buffer status; if it increases, raise the size or reduce large transactions.

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.

Explore O_DIRECT and O_DIRECT_NO_FSYNC flush methods, compare F Sync and all_direct, and learn how dirty pages move from memory to disk with double buffering and benchmarking recommendations.

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.

Understand how the double write buffer on disk, introduced in MySQL 8.0.20, stores flushed pages before they reach the data file to enable crash recovery from half-written pages.

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.

Explore how the flush logs at transaction commit system variable balances strict asset compliance with IO performance, with values 0, 1, and 2.

Check the innodb_flush_log_at_trx_commit value with show global variables, observe it is 1, set it to 2, then stop and start MySQL to apply the change.

Learn how InnoDB redo log files protect durability and enable crash recovery by recording modified pages before disk writes and replaying logs after a crash.

Demonstrates removing ib_logfile files, performing a clean shutdown by setting innodb_fast_shutdown to 0, and restarting MySQL to recreate the redo log files for data recovery.

Clean up the InnoDB redo log double-write buffer files after relocation, verify they sit under var/lib/mysql/prod, and restart to confirm no errors.

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 the system tablespace, ibdata1 data file, and change buffer; learn how InnoDB metadata and data reside here, and practice adding a 10 mb data file and relocating the tablespace.

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 dedicated MySQL server so the inner DB manages memory and flash methods, with ODB automatically configuring the buffer pool size, redo log capacity, and log file group settings.

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.

Bob gains hands-on, live mastery of the inode storage engine architecture, including the buffer pool, redo logs, and system and undo table spaces for production readiness.

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.

Restore a corrupted MySQL database from a cold backup by identifying included items (data files, system and undo tablespaces) and excluded items like redo and binary logs.

Identify the essential files for a cold backup, including the data directory, system tablespace data files, and options files, while excluding redo, binary, undo, and temp files.

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.

Execute logical backups with the MySQL dump utility to generate SQL scripts that recreate databases, supporting full backups, selective tables or rows via where clauses, and multi-database backups.

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.

Restore from a mysql dump using the mysql client to recreate the world database and continents table from backup. Verify restoration and preview mysql pump as an advanced backup tool.

Compare MySQL pump with MySQL dump, noting parallel processing and selective object dumps, including users, plus compressed output and progress tracking.

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.

Demonstrates using MySQLPUMP to back up and restore a database, including add drop table and add drop database options for reliable recovery of tables and databases.

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.

Back up a single table in MySQL by using create table like to copy the structure, then insert into to copy data, enabling rollback via drop or rename.

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 about hot backup tools for production MySQL databases, comparing MySQL backup (enterprise) with free open-source Maria backup, a fork of Extra Backup from Kona, that delivers non-blocking backups.

Learn how to use XtraBackup as a free, enterprise-ready production-grade hot backup tool for MySQL, enabling online backups with point in time recovery through logging and cron automation.

Install the extra backup dash 80 utility on the MySQL 8.0.28 server, ensuring all components share the same version. Use yum to install dependencies and perform online hard backups.

Perform an online backup with xtrabackup, capture binary logs, and run prepare to achieve a consistent snapshot with point-in-time recovery.

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.

Demonstrates restoring a MySQL production database from a hot backup, including stopping the server, copying the data and redo logs, and cleaning unnecessary files, then verifying the restored databases.

Bob demonstrates understanding of backups by distinguishing hot and cold backups, physical and logical backups, using tools like mysqldump, mysqlpump, and extra backup, and restoring accounts.

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.

Compare statement-based, row-based, and mixed-based replication formats, listing their advantages, disadvantages, and implications for data integrity and auditing, with mixed-based recommended.

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 server for replication by using replication.cnf, enabling binary logging (log_bin, log_bin_index), setting a unique server_id, and creating a replicator user with replication privileges and mixed 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 a replica server for binary position based replication by copying backups, enabling relay logs, and setting unique server IDs, while keeping the replica read-only and avoiding auto start.

Configure a MySQL replica by creating relay directories, setting relay log paths, and using a replication cnf with server-id, read_only, and skip_replica, then start and verify readiness.

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.

Configure binary log replication by recording the master log file and position, updating the replica source, starting the replica, and validating with replica status and sample insertions.

Configure the replica by setting the report host to prod db zero two, restart the replica service, and verify status with show replica status to distinguish replicas.

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 how to ignore a specific table in MySQL replication, stop and start replication, and verify replica status while applying table-level filtering.

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.

Configure GTID based replication by enabling GT mode and enforcing GTID consistency on both primary and replica, ensuring each transaction has a global transaction ID and enabling auto start.

Prepare GTID replication by configuring gated mode and enforced gated consistency on both primary and replica, then initialize and start services, and verify master and replica status.

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.

Use the until condition in gtid-based replication to start the replica and stop before a risky transaction, identified by the global transaction identifier, enabling safe backup without applying destructive changes.

Demonstrates how to manage MySQL replication using the until condition, starting and stopping the replica, and applying transactions up to a specific point to ensure primary and replica synchronization.

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.

Practice a step-by-step primary-replica failover in MySQL by switching the primary to read-only, syncing both sides, and reconfiguring hosts to swap roles.

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.

Bob explains his understanding of MySQL replication, covering replication methods, formats, binary log position based replication, SQL and IO threads on the replica, and how to set up git replication.