This page needs a newer browser to run its tools. You can still read everything here.

Start here: versions, a lab server and configuration interview questions

Interview questions on “Start here: versions, a lab server and configuration”: warm-ups, then what each harder question tests and how a good answer starts.

9 questions: 3 warm-ups and 6 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher

What do MySQL's LTS and Innovation tracks each promise, and how often must you upgrade on each?

Answered in the lesson MySQL and MariaDB today: versions and support
Fresher

In the mysql client on a Linux or macOS machine, how does connecting with host localhost differ from host 127.0.0.1?

On Unix the mysql client treats localhost specially: it connects through a Unix socket file, a local file that never touches the network, and any --port is ignored. 127.0.0.1 is an IP address, so the client makes a TCP/IP connection to that address on the port, even though the address is the same machine. The practical difference shows in STATUS: localhost reports "via UNIX socket" and 127.0.0.1 reports "via TCP/IP". It matters because the server must be listening the way you connect: a socket file in the place the client expects, or a TCP port on an address the client can reach. To force TCP even for a local server, use --host=127.0.0.1 or --protocol=TCP; to use the socket, connect as localhost or pass --socket with the file's path.
From the lesson Install MySQL 8.4, connect and create a database
Fresher to mid

How do SET GLOBAL and SET PERSIST differ, and which survives a restart?

Answered in the lesson Configuring MySQL: option files and SET PERSIST

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Mid to senior

A retail company's main database still runs on MySQL 8.0.46 and the team is anxious about touching it. What do you tell them about where 8.0 stands today, the supported path to a current release, and how to carry the move out safely?
What it tests

Knowing that 8.0 is on Sustaining Support, that upgrades step one LTS line at a time, and that a safe move needs a checker, replicas-first ordering and a rollback plan.

First step

Say plainly that 8.0 now gets no new fixes, so staying still is the real risk. Then lay out the only supported path, 8.0 to 8.4 LTS to 9.7 LTS one hop at a time, and de-risk each hop on a test copy with backups before touching production.
Follow-ups
- Why can the team not jump straight from 8.0 to 9.7 or 26.7?
- In a primary-and-replica setup, which server do you upgrade first, and why?
- What would make you stop and roll back during an upgrade, and how do you prepare for that?
- How do you decide whether to stop at 9.7 LTS or continue to 26.7?
From the lesson MySQL and MariaDB today: versions and support
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Mid to senior

A product team expects its service to run for at least five years and asks whether to build on a MySQL LTS line or to follow the Innovation releases. How do you frame the decision for them?
What it tests

Weighing support windows, behaviour-change risk and upgrade cadence against a five-year horizon, and giving a clear recommendation with the conditions that would change it.

First step

Put the two tracks against the five-year need. An LTS line gives five years of Premier plus three of Extended support and stable behaviour, matching the horizon with one or two planned upgrades; the Innovation track gives features sooner but a short support window and frequent, behaviour-changing upgrades. Recommend LTS unless a specific feature forces otherwise.
Follow-ups
- Which specific needs would justify choosing Innovation despite the five-year horizon?
- How many major upgrades would each choice imply over five years?
- How does the move to calendar versioning affect planning on either track?
- Where does MariaDB's yearly LTS fit into the same decision?
From the lesson MySQL and MariaDB today: versions and support
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Fresher to mid

On a new server the mysql client logs in fine from the server itself, but an application on another host fails to connect with "Can't connect to MySQL server". The team is sure the password is right. How do you find the cause, and what are the usual ones?
What it tests

Separating a transport problem (where the server listens, firewalls, the socket-versus-TCP rule) from an account or password problem, and checking each with the server's own settings instead of guessing.

First step

Split connecting from authenticating. The local client very likely uses the Unix socket, which proves nothing about the network. Check what the server listens on (bind_address, port, whether the host is reachable), then whether an account exists for the application's host, before touching the password.
Follow-ups
- Why does a working local mysql client tell you little about why a remote host cannot connect?
- How do bind_address and a 127.0.0.1-only binding change what a remote host sees?
- A MySQL account is user plus host; how could root@localhost exist while the app's user@host does not?
- Which error messages point at the transport, and which at authentication?
From the lesson Install MySQL 8.4, connect and create a database
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A teammate's MySQL, set up quickly for a demo, turns out to be listening on every network interface with an empty root password and the self-signed certificates from first start. What is the risk, and how do you bring it to a safe state without losing the data already in it?
What it tests

Recognising the lab defaults that are unsafe once a server is reachable, and knowing the order of hardening steps so the server is never both reachable and open.

First step

Reduce exposure before anything else: bind the server to 127.0.0.1 (or a trusted address) so it is not reachable while still open, then set a strong root password and review accounts and their host parts. Treat the self-signed certificates and the open ports as the next layer, not the first.
Follow-ups
- Why is an empty root password far more dangerous once the server listens beyond 127.0.0.1?
- In what order do you make the changes so there is never a window that is both reachable and passwordless?
- What would you do about the self-signed certificates and the X Protocol port for anything beyond a laptop?
- How does initialising with --initialize instead of --initialize-insecure help on a real server?
From the lesson Install MySQL 8.4, connect and create a database
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to staff

A configuration tool writes max_connections = 800 to /etc/my.cnf on 40 servers and restarts them. Afterwards 37 servers run with 800, two with 500 and one with 1000. How do you find the cause on each odd server, and what do you change so that the tool's value holds from now on?
What it tests

Reading variables_info across a fleet, knowing what can override a global option file or cap its value, and deciding who owns each setting so that the drift does not come back.

First step

On each odd server, read VARIABLE_SOURCE, VARIABLE_PATH, SET_USER and SET_TIME for max_connections in performance_schema.variables_info. Had the tool's line won, the source would be GLOBAL with the path /etc/my.cnf; any other source names what overrode it. Group the three servers by what you find before changing anything.
Follow-ups
- Both 500s name the tool's own file as their source. What else can hold max_connections below the value in the file?
- The 1000 shows PERSISTED, set by a DBA during an incident. How do you hand the setting back to the file, and when does that take effect?
- How do you keep the same drift from coming back on all 40 servers?
From the lesson Configuring MySQL: option files and SET PERSIST
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 5 minutes
Senior to staff

Six replicas move from MySQL 8.0 to 8.4 one at a time, and all six read one /etc/my.cnf from your configuration tool. It sets default_authentication_plugin = mysql_native_password and innodb_log_file_size = 1G. How do you change the file so every server starts, on either version, throughout the rollout?
What it tests

Which 8.0 settings 8.4 refuses or switches off, how one shared file stays valid on two versions, and how to check it with the new binary before each replica moves.

First step

Before the first replica moves, run the 8.4 mysqld with --defaults-file naming a copy of the shared file, followed by --validate-config. It stops at default_authentication_plugin, which 8.4.0 removed, with exit status 1. Fix that line and run the check again, because it reports only the first error and never starts InnoDB.
Follow-ups
- Why put the 8.0-only line in a [mysqld-8.0] group rather than writing it as loose-default_authentication_plugin?
- Applications log in with mysql_native_password accounts. What fails on the first 8.4 replica, and what do you add?
- The last replica now runs 8.4. What must change in the file before the next upgrade, to 9.7?
From the lesson Configuring MySQL: option files and SET PERSIST
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 5 minutes

All MySQL & MariaDB in Depth interview questions · The MySQL & MariaDB in Depth track

Warm-up questions

What do MySQL's LTS and Innovation tracks each promise, and how often must you upgrade on each?

In the mysql client on a Linux or macOS machine, how does connecting with host localhost differ from host 127.0.0.1?

How do SET GLOBAL and SET PERSIST differ, and which survives a restart?

Questions with model answers

A retail company's main database still runs on MySQL 8.0.46 and the team is anxious about touching it. What do you tell them about where 8.0 stands today, the supported path to a current release, and how to carry the move out safely?

A product team expects its service to run for at least five years and asks whether to build on a MySQL LTS line or to follow the Innovation releases. How do you frame the decision for them?

On a new server the mysql client logs in fine from the server itself, but an application on another host fails to connect with "Can't connect to MySQL server". The team is sure the password is right. How do you find the cause, and what are the usual ones?

A teammate's MySQL, set up quickly for a demo, turns out to be listening on every network interface with an empty root password and the self-signed certificates from first start. What is the risk, and how do you bring it to a safe state without losing the data already in it?

A configuration tool writes max_connections = 800 to /etc/my.cnf on 40 servers and restarts them. Afterwards 37 servers run with 800, two with 500 and one with 1000. How do you find the cause on each odd server, and what do you change so that the tool's value holds from now on?