Forum

What are the Mysql ...
 
Notifications
Clear all

What are the Mysql DBA Role and Responsibilities?


Zubair
Posts: 1
 Zubair
Topic starter
(@Zubair)
Joined: 1 year ago

Hi All,

I am newbie to Mysql and learning by my own. I have gone through all the basic information and got idea. For job purpose i would like to know the below details.

what are the regular activities of DBA

Infrastructure details

for what activities customer will contact us generally

what are the general troubleshooting scenarios

Common performance issues.

Will appreciate your response on this matter.

1 Reply
FA User
Posts: 36
 FA User
(@FA User)
Joined: 1 year ago

Here are the list of Mysql DBA role and responsibilities.

Overview of DBA duties

  1. Server startup/shutdown
  2. Mastering the mysqladmin administrative client
  3. Using the mysql interactive client
  4. User account maintenance
  5. Log file maintenance
  6. Database backup/copying
  7. Hardware tuning
  8. Multiple server setups
  9. Software updates and upgrades
  10. File system security
  11. Server security
  12. Repair and maintenance
  13. Crash recovery
  14. Preventive maintenance
  15. Understanding the mysqld server daemon
  16. Performance analysis

Obtaining and Installing MySQL

  1. Choosing what else to install (e.g. Apache, Perl +modules, PHP)
  2. Which version of MySQL (stable, developer, source, binary)
  3. Creating a user acccount for the mysql user and group
  4. Download and unpack a distribution
  5. Compile source code and install (or rpm)
  6. Initialize the data directory and grant tables with mysql_install_db
  7. Starting the server
  8. Installing Perl DBI support
  9. Installing PHP
  10. Installing Apache
  11. Obtaining and installing the samp_db sample database

The MySQL Data Directory

  1. Deciding/finding the Data Directory’s location
  2. Structure of the Data Directory
  3. How mysqld provides access to data
  4. Running multiple servers on a single Data Directory
  5. Database representation
  6. Table representation (form, data and index files)
  7. OS constraints on DB and table names
  8. Data Directory structure and performance, resources, security
  9. MySQL status files (.pid, .err, .log, etc)
  10. Relocating Data Directory contents
  11. Starting Up and Shutting Down the MySQL Server
  12. Securing a new MySQL installlation
  13. Running mysqld as an unprivileged user
  14. Methods of starting the server
  15. Invoking mysqld directly
  16. Invoking safe_mysqld
  17. Invoking mysql.server
  18. Specifying startup options
  19. Checking tables at startup
  20. Shutting down the server
  21. Regaining control of the server if you can’t connect

Managing MySQL User Accounts

  1. Creating new users and granting privileges
  2. Determining who can connect from where
  3. Who should have what privileges?
  4. Administrator privileges
  5. Revoking privileges
  6. Removing users

Maintaining MySQL Log Files

  1. The general log
  2. The update log
  3. Rotating logs
  4. Backing up logs

Backing Up, Copying, and Recovering MySQL Databases

  1. Methods: mysqldump vs. direct copying
  2. Backup policies
  3. Scheduled cycles
  4. Update logging
  5. Consistent and comprehensible file-naming
  6. Backing up the backup files
  7. Off-site / off-system backups
  8. Backing up an entire database with mysqldump
  9. Compressed backup files
  10. Backing up individual tables
  11. Using mysqldump to transfer databases to another server
  12. mysqldump options (flush-logs, lock-tables, quick, opt)
  13. Direct copying methods
  14. Database replication (live and off-line copying)
  15. Recovering an entire database
  16. Recovering grant tables
  17. Recovering from mysqldump vs. tar/cpio files
  18. Using update logs to replay post-backup queries
  19. Editing update logs to avoid replaying erroneous queries
  20. Recovering individual tables

Tuning the MySQL Server

  1. Default parameters
  2. The mysqladmin variables command
  3. Setting variables (command line and options file)
  4. Commonly used variables in performance tuning
  5. back_log
  6. delayed_queue_size
  7. flush_time
  8. key_buffer_size
  9. max_allowed_packet
  10. max_connections
  11. table_cache
  12. Erroneous use of record_buffer and sort_buffer

Running Multiple MySQL Servers

  1. For test purposes
  2. To overcome OS limits on per-process file descriptors
  3. Separate servers for individual customers (e.g. ISPs)
  4. Configuring and installing separate servers
  5. Procedures for starting up multiple servers

Updating MySQL

  1. Stable vs. development releases
  2. Updates for both streams
  3. Using the “Change Notes”
  4. Bug fixing vs. new features
  5. Dependencies on the MySQL C client library (PHP, Apache, Perl DBD::mysql)

MySQL Security

  1. Assessing risks and threats
  2. Internal security: data and directory access
  3. Access to database files and log files
  4. Securing both read and write access
  5. Filesystem permissions
  6. External security: network access
  7. Structure and content of the MySQL Grant Tables
  8. user, db, host, tables_priv, columns_priv
  9. Grant table scope fields/columns
  10. Grant table privilege columns
  11. Database and table privileges: ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE
  12. Administrative privileges: FILE, GRANT, PROCESS, RELOAD, SHUTDOWN
  13. Server control over client access: matching grant table entries to client connection requests and queries
  14. Scope column values: Host, User, Password, Db, Table_name, Column_name
  15. Query access verification
  16. Scope column mmatching order
  17. Grant table risks: the FILE and ALTER privileges
  18. Setting up users without GRANT
  19. The anonymous user and sort order

MySQL Database Maintenance and Repair

  1. Checking and repairing tables
  2. Invoking myisamchk and isamchk
  3. Extended checks
  4. Standard table repair
  5. Table repair with missing/damaged index or table description
  6. Avoid server-checking interaction, without shutdowns

Let me know in case of any queries. 

Reply
Share: