How to use a specific database on our mysql instance?
To select a database in MySQL, we use the SQL USE statement. Once a specific database is selected, we can perform different operations such as creating tables, adding data, updating, and deleting information. Every operation we perform after selecting a database will be stored in that particular database.
Scenario Questions
- 1
We have a MySQL server with several databases. How would you write a script to connect and run a query against the 'analytics' database?
- 2
If you run SELECT DATABASE(); after connecting, what will it return if you haven't selected a database?
- 3
What command would you use to switch from the 'test' database to the 'production' database in a MySQL session?
- 1
Our new feature needs to write logs to a dedicated 'logs' database, but the existing codebase always connects to the default schema. How would you modify the connection logic, and what pitfalls would you watch for?
- 2
During a deployment we saw errors like 'Unknown database "metrics"' after adding a new DB. Walk me through how you'd debug why the application couldn't see the new database.
- 3
We want to run a migration that creates tables in a specific database without affecting others. Which MySQL options or statements would you use, and why?
- 1
We are building a multi‑tenant SaaS where each tenant gets its own MySQL database on a shared instance. How would you design the connection handling to reliably select the correct database per request, considering connection pooling and failover?
- 2
Our read replica lag is causing occasional 'Database not selected' errors when the app switches databases mid‑transaction. How would you address this at the system level?
- 3
If we need to enforce that certain users can only access a specific database, what MySQL privileges and architecture would you put in place to prevent accidental cross‑database queries?
- 1
We plan to migrate from a monolithic MySQL instance with many databases to a sharded architecture where each shard hosts a subset of databases. What high‑level strategy would you propose for routing traffic to the correct database, handling schema changes, and minimizing downtime across teams?
- 2
Our organization wants to standardize how services specify the target database in connection strings to avoid hard‑coded USE statements. How would you design a cross‑team convention and tooling to enforce this, and what migration path would you suggest?
Follow-up Questions
- ❓What exact SQL would you run to switch databases?
- ❓How does your code react if the target database is absent?
- ❓How would you prevent cross‑database queries in a pooled environment?