Advanced MySQL: performance, replication and security
You will write fast MySQL queries, back up and replicate data, and configure the server securely.
Level 301 · 27 lessons · 24 practice labs
Lessons
Powerful queries
- MySQL subqueries and temporary tables: You will nest one query inside another and use temporary tables that live only for your session.
- MySQL CTEs and recursive queries: You will split a long query into readable parts with WITH and walk parent-child data such as an org chart.
- MySQL window functions: You will rank rows, number them and compute running totals without collapsing them, for example on login records.
- JSON data in MySQL: You will store JSON columns, extract values from them and index a JSON field.
- MySQL full-text search (FULLTEXT): You will search long text quickly and by relevance with a FULLTEXT index instead of LIKE.
- Short task: a product report with JSON and window functions
Performance
- MySQL indexes and the EXPLAIN query plan: You will see why an index speeds up a query and spot a full table read in EXPLAIN output.
- Composite and covering indexes: You will learn why column order matters in a multi-column index and how a query can avoid touching the table.
- Slow query log and server tuning: You will enable the slow query log, find the slowest queries and tune InnoDB memory settings by measurement.
- MySQL partitioning: You will split a very large table into date-based parts and drop old parts quickly.
- Short task: speed up the top three queries in the slow log
Logic inside the database
- MySQL views: You will turn a complex query into a virtual table and show a user only the permitted columns.
- Stored procedures, functions and error handling: You will write reusable procedures and functions in the database and stop cleanly when an error occurs.
- Triggers and change trails: You will use a trigger that fires when a row changes to record who changed what and when.
- MySQL Event Scheduler: You will run jobs such as purging old records on a schedule using the database’s own scheduler.
- Transactions, isolation levels and locks: You will learn how concurrent transactions affect each other and how to resolve a deadlock.
- Short task: track order status with a trigger
Operations: replication and backups
- MySQL replication basics: You will set up automatic copying of data to a second server and check whether the copy is falling behind.
- Binary log and point-in-time recovery: You will bring back accidentally deleted data to a chosen moment by replaying the binary log over a backup.
- Backup strategies: logical and physical: You will know the difference between mysqldump and Percona XtraBackup and choose which fits which case.
- Importing and exporting data: LOAD DATA and secure_file_priv: You will load a CSV file into a table, write results to a file and use the security setting that limits this.
- Short task: recover a dropped table with point-in-time recovery
Security
- Accounts, roles and password policy: You will group privileges with MySQL roles, block weak passwords with validate_password and lock accounts.
- TLS connections and tablespace encryption: You will protect client-server traffic with TLS and data on disk with InnoDB tablespace encryption.
- MySQL audit logging: You will record who connected and which statements they ran by using audit logging.
- MySQL hardening checklist: You will apply a checklist that reviews the listening address, local_infile, unused accounts and privileges in turn.
- Capstone: speed up, back up and harden a MySQL server
Practice labs
- Pull fields from a JSON column: Extract colour and size values from a JSON column holding product attributes.
- Index a JSON field with a generated column: Add a generated column and index for a frequently searched JSON field and show the query speeds up.
- Unnest a JSON array into rows: Unnest an order's product array into rows with JSON_TABLE and build a sales report per category.
- Full-text search product descriptions: Add a FULLTEXT index and find products whose description contains two words together.
- Narrow results with boolean mode: Apply required, excluded and prefix words with boolean mode for Defne Kitabevi's search.
- Fix a search that misses short words: Change the setting behind a full-text search that misses three-letter codes and rebuild the index.
- Partition a log table by month: Partition an access log table by monthly ranges and show a query touching only one partition.
- Purge old data by dropping a partition: Drop an old month's partition instead of deleting millions of rows and compare the time.
- Fix a partitioning key mistake: Reorganise a table partitioned on a key that no query can prune by, using the right key.
- Write a procedure returning an order summary: Write a stored procedure that takes a customer number and returns order count and total.
- Add an error handler to a procedure: Write a procedure with a handler that rolls back and raises a clear error when stock is short.
- Move repeated logic into a function: Move a shipping-fee calculation copied into five queries into a deterministic function and verify the results.
- Log price changes with a trigger: Create a trigger that writes old and new prices to a history table when a price changes.
- Hand nightly cleanup to the Event Scheduler: Define an event that deletes expired carts every night and verify it ran from the log.
- Untangle a runaway trigger chain: Find the error caused by two triggers updating each other and move the logic to one place.
- Load a CSV with LOAD DATA: Load a supplier's 100-thousand-row CSV with LOAD DATA and count the bad rows.
- Work within secure_file_priv properly: On a server rejecting exports, write a report into the allowed directory without loosening security.
- Load a file with a foreign date format: Load a file with day/month/year dates and comma decimals, converting them to proper types during the load.
- List the events in the binary log: Make a few changes and find them in the binary log with mysqlbinlog.
- Set up a simple replica: Connect a second server as a replica for reporting and verify new orders arrive from the source.
- Return to the moment before a bad UPDATE: Load the nightly backup, replay the binary log up to just before a bad UPDATE and recover the prices.
- Grant privileges through roles: Create reader and writer roles, assign them to users and activate the default role.
- Force connections onto TLS: Make the app account connect only over encryption and show an unencrypted attempt is refused.
- Run a hardening audit on a server: Harden a server with a checklist covering password policy, unused accounts, file permissions and audit logging.
About this course
You will write fast MySQL queries, back up and replicate data, and configure the server securely.
What you will learn
- Powerful queries
- Performance
- Logic inside the database
- Operations: replication and backups
- Security
Prerequisites
You should be comfortable with the level 101 and 201 topics of the same path.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 301; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.