How to Connect to MySQL — SQL Technical Interview Questions & Answers

How to Connect to MySQL

How to Connect to MySQL

  • A connection is a login to the server: host + port + user + password + database. Default host is 127.0.0.1 (this computer), port 3306. From PHP/Java/Node you pass the same four things in a connection string or DSN.
  • CLI: mysql -u asha -p -h 127.0.0.1 college. Workbench: New Connection, same fields. If it fails, the error is usually wrong password, server not running, or user not allowed from that host.
  • Apps should use a normal user with rights only on that database — not root. Close the connection when the request ends (or use a pool). Leaving 500 open connections hits ‘too many connections’.
  • Trap: ‘localhost’ vs 127.0.0.1 can use a socket vs TCP on Linux — different user grants. If one works and the other doesn’t, check mysql.user host column.
  • On the example next to this theory: How to Connect to MySQL: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Technical Interview Tip: Four fields: host, user, password, database. Port 3306.

Code Examples for How to Connect to MySQL

How to Connect to MySQL — sample query

-- How to Connect to MySQL
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

How to Connect to MySQL: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

How to Explain How to Connect to MySQL to an Interviewer

What do you need to connect?

Host, user, password, database Port 3306 by default

Why not root from PHP?

Too much power Make a user with GRANT only on that DB