MySQL administration: installation, configuration, monitoring
You install a MySQL server, manage its configuration files and logs, set up users and storage engines and automate routine maintenance.
Level 201 · 19 lessons · 15 practice labs
Lessons
The server
- MySQL architecture: server, engines and files
- Installation, versions and how MariaDB differs
- Configuration files and system variables
- Service management and mysqladmin
- The data directory and its files
- Short task: configure a new server
Users and engines
- Accounts and connecting hosts
- Privileges in depth
- Authentication plugins
- InnoDB basics
- Character sets and collations
- Short task: accounts for three applications
Daily operations
- The error log and general log
- Running queries: PROCESSLIST and KILL
- Status variables and key metrics
- Table maintenance: ANALYZE, OPTIMIZE, CHECK
- Upgrading versions
- Scheduling maintenance jobs
- Capstone: take over a company’s MySQL server
Practice labs
- Make a system variable persistent: Change max_connections at runtime, see it lost on restart and make it persistent.
- Find the config error that stops the server: Read the error log to bring up a MySQL server that fails to start because of a typo in my.cnf.
- Trace where a setting comes from: On a server with several config files, prove which file and which order a variable is read from.
- Tie an app account to one host: Create an account that can connect only from the app server's address and test from another address.
- Prepare grants for three applications: Set up three least-privilege accounts for ordering, reporting and backup apps and document them with SHOW GRANTS.
- Migrate an old authentication plugin: Find accounts on an old authentication plugin and move them to the current one without breaking clients.
- Fix garbled Turkish characters: Find in the connection character set why ğ, ş and ı show as question marks and fix it.
- Move a table to utf8mb4: Convert a comments table that cannot store emoji to utf8mb4 without data loss.
- Resolve duplicates caused by collation: Find the records that break a unique index after a collation change and apply a fix.
- Find and kill a long-running query: Use PROCESSLIST to find a report running for minutes and stop just that query with KILL QUERY.
- Read key metrics over one minute: Write a short script that computes queries and connections per second from status variables.
- Find the source of a connection spike: On a server failing with too many connections, find which user and host are using up connections.
- Check and repair a table: Find a damaged table with CHECK TABLE and repair it with the right command.
- Schedule a weekly maintenance job: Set up a weekly job that runs ANALYZE and OPTIMIZE on big tables and logs the result.
- Plan and perform a minor version upgrade: Back up the server, perform a minor version upgrade and verify the app with a checklist.
About this course
You install a MySQL server, manage its configuration files and logs, set up users and storage engines and automate routine maintenance.
What you will learn
- The server
- Users and engines
- Daily operations
Prerequisites
Finishing the level 101 courses of the same path, or knowing those topics, is enough.
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 201; 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.