Sample questions
Mysql OperationsDifficulty 1
What does mysqldump produce by default?
- aA raw binary copy of InnoDB's
.ibd data files - bA compressed snapshot of the entire data directory, including redo logs
- cA plain-text file of SQL statements (
CREATE TABLE, INSERT)✓ - dA diff of only the rows changed since the last backup
Explanation:mysqldump is a logical backup tool: it queries the server and writes out SQL statements (schema DDL plus INSERTs) that can recreate the data by replaying them with a client like mysql. It does not copy physical files.
Mysql OperationsDifficulty 1
Which statement grants a user only SELECT privilege on every table in the shop database?
- a
GRANT SELECT ON shop.* TO 'reporter'@'%';✓ - b
GRANT SELECT ON . TO 'reporter'@'%' DATABASE shop; - c
ALLOW SELECT shop.* TO 'reporter'@'%'; - d
CREATE USER 'reporter'@'%' PRIVILEGE SELECT ON shop.*;
Explanation:MySQL privilege grants use GRANT <privilege(s)> ON <db>.<table> TO 'user'@'host';. shop.* scopes it to every table inside the shop database. The other forms are not valid MySQL syntax.
Mysql OperationsDifficulty 1
What kind of information does information_schema expose?
- aRow-by-row query execution timings for the currently running statement
- bMetadata about databases, tables, columns, and indexes✓
- cA live list of every lock currently held, refreshed every second
- dHistorical CPU and disk usage graphs for the MySQL process
Explanation:information_schema is MySQL's standard (ANSI SQL-inspired) metadata database — it exposes the data dictionary: which databases, tables, columns, indexes, and constraints exist and their properties. Runtime performance counters live in performance_schema instead.
Mysql OperationsDifficulty 1
What does the max_connections server variable control?
- aThe maximum number of tables a single query may join
- bThe maximum number of rows returned by a single
SELECT - cThe maximum size, in bytes, of the InnoDB buffer pool
- dThe maximum number of simultaneous client connections the server will accept✓
Explanation:max_connections caps how many clients can be connected to the MySQL server at once. Once that limit is reached, new connection attempts fail with ERROR 1040: Too many connections. It has nothing to do with join limits, row limits, or buffer pool sizing.
Mysql OperationsDifficulty 1
What is the main purpose of innodb_buffer_pool_size?
- aIt sizes the in-memory cache InnoDB uses to hold table data and indexes, avoiding disk reads✓
- bIt sets how many rows InnoDB may lock at once during a transaction
- cIt controls how often the binary log is flushed to disk
- dIt limits how many concurrent
ALTER TABLE statements InnoDB can run
Explanation:innodb_buffer_pool_size sizes InnoDB's main memory cache, which holds table and index pages so repeated reads/writes can be served from RAM instead of disk. It's one of the most impactful tuning knobs for InnoDB performance — it doesn't govern locking, binlog flushing, or DDL concurrency.
Mysql OperationsDifficulty 1
Which statement removes a previously granted INSERT privilege from a user, without dropping the user account?
- a
DROP PRIVILEGE INSERT ON shop.* FROM 'app'@'%'; - b
REVOKE INSERT ON shop.* FROM 'app'@'%';✓ - c
DELETE GRANT INSERT ON shop.* FOR 'app'@'%'; - d
ALTER USER 'app'@'%' REMOVE INSERT;
Explanation:REVOKE <privilege> ON <db>.<table> FROM 'user'@'host'; is the counterpart to GRANT — it removes a specific privilege while leaving the user account itself intact. The other syntaxes don't exist in MySQL.