Database Administrator (DBA) Cheat Sheet

Here’s a cheat sheet for Database Administrators (DBAs) covering key tasks and commands:

Database Management Systems (DBMS)

Common DBMS:

SQL Commands

Connect to Database:

mysql -u username -p

Show Databases:

SHOW DATABASES;

Use a Database:

USE database_name;

Show Tables:

SHOW TABLES;

Describe Table:

DESCRIBE table_name;

Select Data:

SELECT * FROM table_name WHERE condition;

Insert Data:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Update Data:

UPDATE table_name SET column1 = value1 WHERE condition;

Delete Data:

DELETE FROM table_name WHERE condition;

Database Backup and Restore

Backup Database:

mysqldump -u username -p database_name > backup.sql

Restore Database:

mysql -u username -p database_name < backup.sql

User Management

Create User:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Grant Privileges:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

Performance Monitoring

Check Active Connections:

SHOW PROCESSLIST;

Check Server Status:

SHOW STATUS;

Optimize Table:

OPTIMIZE TABLE table_name;

Security

Change Password:

SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

Database Maintenance

Analyze Table:

ANALYZE TABLE table_name;

Repair Table:

REPAIR TABLE table_name;

Transactions

Commit Transaction:

COMMIT;

Rollback Transaction:

ROLLBACK;

Database Version Control

Version Information:

SELECT VERSION();

Monitoring Tools

Use Monitoring Tools:

  • MySQL: MySQL Enterprise Monitor, Percona Monitoring and Management (PMM)
  • PostgreSQL: pg_stat_statements, pgBadger, etc.

Troubleshooting

Check Error Log:

  • MySQL: /var/log/mysql/error.log
  • PostgreSQL: /var/log/postgresql/postgresql-version-main.log

Check Locks:

SHOW OPEN TABLES WHERE In_use > 0;

Backup Strategies

Regular Backups:

  • Schedule regular backups to ensure data integrity.

Regular Maintenance

Update Software:

  • Keep the DBMS and related tools up to date.

Monitor Disk Space:

  • Regularly check and manage disk space.

This cheat sheet provides a basic overview, and tasks may vary based on the specific DBMS and requirements. Always refer to the official documentation for the respective database system for more details and advanced features.