Database Administration Essentials

Difficulty: Advanced
Generated on: 2025-07-10 02:37:17
Category: SQL Cheatsheet for Database Development

SQL Cheatsheet: Database Administration Essentials (Advanced)

This cheatsheet provides a quick reference for advanced database administration tasks using SQL. It’s designed for developers who need to manage and optimize databases in production environments.

1. Quick Overview

This section covers essential database administration tasks such as user management, security, performance monitoring, and data backup/recovery. These are critical for maintaining a healthy and reliable database system.

2. Syntax

2.1 User Management

Create User:

-- MySQL
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

-- PostgreSQL
CREATE ROLE username WITH LOGIN PASSWORD 'password';

-- SQL Server
CREATE LOGIN username WITH PASSWORD = 'password';
CREATE USER username FOR LOGIN username;

-- Oracle
CREATE USER username IDENTIFIED BY password;

Grant Privileges:

-- MySQL
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.table_name TO 'username'@'hostname';
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname';
FLUSH PRIVILEGES; -- Refresh privileges

-- PostgreSQL
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE table_name TO username;
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

-- SQL Server
GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO username;

-- Oracle
GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO username;

Revoke Privileges:

-- MySQL
REVOKE SELECT ON database_name.table_name FROM 'username'@'hostname';
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'hostname';
FLUSH PRIVILEGES;

-- PostgreSQL
REVOKE SELECT ON TABLE table_name FROM username;
REVOKE ALL PRIVILEGES ON DATABASE database_name FROM username;

-- SQL Server
REVOKE SELECT ON table_name FROM username;

-- Oracle
REVOKE SELECT ON table_name FROM username;

Alter User (Change Password):

-- MySQL
ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';

-- PostgreSQL
ALTER ROLE username WITH PASSWORD 'new_password';

-- SQL Server
ALTER LOGIN username WITH PASSWORD = 'new_password';

-- Oracle
ALTER USER username IDENTIFIED BY new_password;

Drop User:

-- MySQL
DROP USER 'username'@'hostname';

-- PostgreSQL
DROP ROLE username;

-- SQL Server
DROP USER username;
DROP LOGIN username;

-- Oracle
DROP USER username;

2.2 Backup and Restore

Backup (Logical): Using mysqldump, pg_dump, or similar tools. These create SQL scripts or text files.
- MySQL:
  Terminal window
```
mysqldump -u root -p database_name > backup.sql
```
- PostgreSQL:
  Terminal window
```
pg_dump -U postgres database_name > backup.sql
```
- SQL Server (using sqlcmd or SSMS): Requires specific tools and syntax.
Restore (Logical):
- MySQL:
  Terminal window
```
mysql -u root -p database_name < backup.sql
```
- PostgreSQL:
  Terminal window
```
psql -U postgres -d database_name -f backup.sql
```
- SQL Server (using sqlcmd or SSMS): Requires specific tools and syntax.
Backup (Physical): Copying the database files directly (e.g., using OS tools or database-specific utilities). Requires careful coordination with the database server.

2.3 Performance Monitoring

Query Slow Query Log (MySQL):

-- Enable slow query log (usually in my.cnf or my.ini)
-- long_query_time = 2
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/mysql-slow.log

-- Show global variables related to slow query log
SHOW GLOBAL VARIABLES LIKE '%slow_query%';

-- Analyze the slow query log file using mysqldumpslow (command line tool)
# mysqldumpslow /var/log/mysql/mysql-slow.log

Explain Plan (for query analysis):

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- PostgreSQL Extended Statistics:
-- CREATE STATISTICS order_customer_stats (dependencies) ON customer_id, order_date FROM orders;
-- ANALYZE orders;

Monitor Active Connections (MySQL):
```
SHOW PROCESSLIST;
```
Monitor Active Connections (PostgreSQL):
```
SELECT * FROM pg_stat_activity;
```
Monitor Active Requests (SQL Server):
```
SELECT * FROM sys.dm_exec_requests;
```
Monitor Active Sessions (Oracle):
```
SELECT * FROM v$session;
```

2.4 Index Management

Create Index:

CREATE INDEX idx_customer_id ON orders (customer_id);
CREATE INDEX idx_order_date_customer_id ON orders (order_date, customer_id); -- Composite index
CREATE UNIQUE INDEX idx_email ON users (email); -- Unique index

Drop Index:

DROP INDEX idx_customer_id ON orders;  -- MySQL, PostgreSQL

-- SQL Server
DROP INDEX idx_customer_id ON orders;

-- Oracle
DROP INDEX idx_customer_id ON orders;

Show Indexes:

-- MySQL
SHOW INDEXES FROM orders;

-- PostgreSQL
SELECT indexname FROM pg_indexes WHERE tablename = 'orders';

-- SQL Server
EXEC sp_helpindex 'orders';

-- Oracle
SELECT index_name FROM user_indexes WHERE table_name = 'ORDERS';

2.5 Table Partitioning (Example - MySQL)

Partition by Range:

CREATE TABLE sales (
    sale_date DATE,
    product_id INT,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE ( YEAR(sale_date) ) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION future VALUES LESS THAN MAXVALUE
);

-- Add a new partition
ALTER TABLE sales ADD PARTITION (PARTITION p2023 VALUES LESS THAN (2024));

Partition by List:

CREATE TABLE products (
    product_id INT,
    category VARCHAR(50),
    product_name VARCHAR(100)
)
PARTITION BY LIST (category) (
    PARTITION p_electronics VALUES IN ('Electronics'),
    PARTITION p_clothing VALUES IN ('Clothing'),
    PARTITION p_other VALUES IN (DEFAULT) -- Catches everything else.  Required.
);

3. Common Use Cases

User Management: Adding/removing users, granting/revoking permissions based on roles (e.g., read-only access for reporting).
Backup and Restore: Implementing a regular backup schedule to prevent data loss; restoring data after a system failure or accidental deletion.
Performance Monitoring: Identifying slow queries, optimizing indexes, and tuning database server parameters to improve application performance.
Index Management: Creating indexes on frequently queried columns to speed up data retrieval; dropping unused indexes to reduce storage space and improve write performance.
Table Partitioning: Dividing large tables into smaller, more manageable partitions to improve query performance and simplify data management (especially for time-series data).
Auditing: Tracking user activity for security and compliance reasons.

4. Best Practices

Principle of Least Privilege: Grant users only the minimum necessary privileges.
Regular Backups: Implement a robust backup and recovery strategy (consider both logical and physical backups). Test the restore process regularly!
Monitor Performance Regularly: Set up monitoring tools to track key performance metrics (CPU usage, memory usage, disk I/O, query execution times). Use slow query logs.
Index Wisely: Create indexes on frequently queried columns, but avoid over-indexing (which can slow down write operations). Use the EXPLAIN statement.
Optimize Queries: Use EXPLAIN to analyze query execution plans and identify areas for improvement (e.g., missing indexes, inefficient joins). Consider using covering indexes.
Secure Passwords: Use strong passwords and proper password management techniques (e.g., password policies, hashing).
Keep Software Updated: Apply security patches and updates regularly to protect against vulnerabilities.
Use Connection Pooling: Reduce the overhead of creating and closing database connections.
Parameterize Queries: Prevent SQL injection attacks.
Regularly Analyze and Rebuild Indexes: Over time, indexes can become fragmented, impacting performance. Use database-specific commands to rebuild or reorganize them.
Consider Read Replicas: Offload read traffic to read replicas to reduce the load on the primary database.

5. Examples

5.1 Example: Creating a Read-Only User (MySQL)

-- Create a read-only user
CREATE USER 'readonlyuser'@'%' IDENTIFIED BY 'securepassword';

-- Grant SELECT privileges on all tables in the 'ecommerce' database
GRANT SELECT ON ecommerce.* TO 'readonlyuser'@'%';

-- Flush privileges to apply the changes
FLUSH PRIVILEGES;

-- Test the user (login as readonlyuser and try to insert data - it should fail)

5.2 Example: Backing Up and Restoring a Database (PostgreSQL)

# Backup the 'mydatabase' database
pg_dump -U postgres mydatabase > mydatabase_backup.sql

# Restore the 'mydatabase' database (after dropping it first, for example)
dropdb mydatabase;
createdb mydatabase;
psql -U postgres -d mydatabase < mydatabase_backup.sql

5.3 Example: Analyzing a Slow Query (MySQL)

-- Enable slow query log (edit my.cnf or my.ini)
-- long_query_time = 2
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/mysql-slow.log

-- Restart MySQL server to apply the changes

-- Run a query that is known to be slow (e.g., a full table scan)
SELECT * FROM large_table WHERE some_column LIKE '%search_term%';

-- Check the slow query log file (/var/log/mysql/mysql-slow.log) for the query
-- Use mysqldumpslow to analyze the log file
# mysqldumpslow /var/log/mysql/mysql-slow.log

-- Use EXPLAIN to understand the query execution plan
EXPLAIN SELECT * FROM large_table WHERE some_column LIKE '%search_term%';

-- Output of EXPLAIN (example):
-- +----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
-- | id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
-- +----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
-- |  1 | SIMPLE      | large_table | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where |
-- +----+-------------+-------------+------+---------------+------+---------+------+------+-------------+

-- In this case, 'type' is ALL (full table scan), and 'key' is NULL (no index used).  Add an index to some_column.
CREATE INDEX idx_some_column ON large_table (some_column);

-- Run EXPLAIN again after adding the index.  The output should change significantly.
EXPLAIN SELECT * FROM large_table WHERE some_column LIKE '%search_term%'; -- Leading wildcard still hurts.

-- A better approach might be full-text indexing, depending on the use case.

5.4 Example: Creating a Partitioned Table (MySQL - Time Series Data)

CREATE TABLE sensor_data (
    timestamp DATETIME,
    sensor_id INT,
    value DECIMAL(10,2)
)
PARTITION BY RANGE (TO_DAYS(timestamp)) (
    PARTITION p20230101 VALUES LESS THAN (TO_DAYS('2023-01-02')),
    PARTITION p20230102 VALUES LESS THAN (TO_DAYS('2023-01-03')),
    PARTITION p20230103 VALUES LESS THAN (TO_DAYS('2023-01-04')),
    PARTITION future VALUES LESS THAN MAXVALUE
);

-- Querying a specific partition
SELECT * FROM sensor_data PARTITION (p20230101) WHERE sensor_id = 123;

-- Adding new partitions (important for time-series data)
ALTER TABLE sensor_data ADD PARTITION (PARTITION p20230104 VALUES LESS THAN (TO_DAYS('2023-01-05')));

5.5 Example: Auditing (SQL Server)

-- Create a server audit
CREATE SERVER AUDIT audit_server
TO FILE (FILEPATH = 'C:\SQLAuditLogs\', MAXSIZE = 100MB, MAX_ROLLOVER_FILES = 10)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

ALTER SERVER AUDIT audit_server WITH (STATE = ON);

-- Create a database audit specification
CREATE DATABASE AUDIT SPECIFICATION audit_database
FOR SERVER AUDIT audit_server
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.orders BY dbo),
ADD (EXECUTE ON OBJECT::sp_who2 BY dbo)
WITH (STATE = ON);

ALTER DATABASE AUDIT SPECIFICATION audit_database WITH (STATE = ON);

-- To read the audit logs:
-- Use SQL Server Management Studio (SSMS) to view the audit logs in the file path.
-- You can also query the audit log files using sys.fn_get_audit_file.

6. Common Pitfalls

Over-Indexing: Too many indexes can slow down write operations. Test the performance impact of indexes.
Ignoring EXPLAIN: Not analyzing query execution plans leads to inefficient queries.
Lack of Backups: Data loss can be catastrophic.
Insufficient Security: Weak passwords, excessive privileges, and unpatched vulnerabilities can lead to security breaches.
Not Monitoring Performance: Ignoring performance metrics can lead to slow application performance and system outages.
Incorrect Data Types: Using the wrong data types can lead to performance issues and data integrity problems.
Using Cursors: Cursors are often slow and inefficient. Try to use set-based operations instead.
Not Using Parameterized Queries: Leads to SQL injection vulnerabilities.
Not Understanding Isolation Levels: Can lead to data inconsistencies. Choose the appropriate isolation level for your application.
Assuming Default Settings are Optimal: Database server default settings may not be optimal for your workload. Tune the configuration parameters as needed.

7. Database Variations

User Management: The syntax for creating and managing users varies significantly between database systems.
Backup and Restore: The tools and methods for backup and restore are database-specific.
Performance Monitoring: The system tables and functions for monitoring performance differ across databases. Each database has its own set of performance tuning tools.
Index Management: While the basic concepts are similar, the syntax and options for creating and managing indexes vary.
Table Partitioning: The syntax and features for table partitioning are database-specific (e.g., MySQL has more limited partitioning options than Oracle). Not all databases support partitioning.
Auditing: Auditing features and syntax vary greatly. Some databases have more robust auditing capabilities than others.
Transactions: Handling of transactions can vary. Isolation levels are implemented differently, and features like savepoints may have different syntax or behavior.

Important Note: Always consult the official documentation for your specific database system for the most accurate and up-to-date information. This cheatsheet is a general guide and may not cover all features or nuances.