MySQL DBA Responsibilities

Overview of DBA duties

  • Server startup/shutdown
  • Mastering the mysqladmin administrative client
  • Using the mysql interactive client
  • User account maintenance
  • Log file maintenance
  • Database backup/copying
  • Hardware tuning
  • Multiple server setups
  • Software updates and upgrades
  • File system security
  • Server security
  • Repair and maintenance
  • Crash recovery
  • Preventive maintenance
  • Understanding the mysqld server daemon
  • Performance analysis

Obtaining and Installing MySQL

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

The MySQL Data Directory

  • deciding/finding the Data Directory’s location
  • Structure of the Data Directory
  • How mysqld provides access to data
  • Running multiple servers on a single Data Directory
  • Database representation
  • Table representation (form, data and index files)
  • OS constraints on DB and table names
  • Data Directory structure and performance, resources, security
  • MySQL status files (.pid, .err, .log, etc)
  • Relocating Data Directory contents

Starting Up and Shutting Down the MySQL Server

  • Securing a new MySQL installlation
  • Running mysqld as an unprivileged user
  • Methods of starting the server
  • Invoking mysqld directly
  • Invoking safe_mysqld
  • Invoking mysql.server
  • Specifying startup options
  • Checking tables at startup
  • Shutting down the server
  • Regaining control of the server if you can’t connect

Managing MySQL User Accounts

  • Creating new users and granting privileges
  • Determining who can connect from where
  • Who should have what privileges?
  • Administrator privileges
  • Revoking privileges
  • Removing users

Maintaining MySQL Log Files

  • The general log
  • The update log
  • Rotating logs
  • Backing up logs

Backing Up, Copying, and Recovering MySQL Databases

  • Methods: mysqldump vs. direct copying
  • Backup policies
  • Scheduled cycles
  • Update logging
  • Consistent and comprehensible file-naming
  • Backing up the backup files
  • Off-site / off-system backups
  • Backing up an entire database with mysqldump
  • Compressed backup files
  • Backing up individual tables
  • Using mysqldump to transfer databases to another server
  • mysqldump options (flush-logs, lock-tables, quick, opt)
  • Direct copying methods
  • Database replication (live and off-line copying)
  • Recovering an entire database
  • Recovering grant tables
  • Recovering from mysqldump vs. tar/cpio files
  • Using update logs to replay post-backup queries
  • Editing update logs to avoid replaying erroneous queries
  • Recovering individual tables

Tuning the MySQL Server

  • Default parameters
  • The mysqladmin variables command
  • Setting variables (command line and options file)
  • Commonly used variables in performance tuning
  • back_log
  • delayed_queue_size
  • flush_time
  • key_buffer_size
  • max_allowed_packet
  • max_connections
  • table_cache
  • Erroneous use of record_buffer and sort_buffer

Running Multiple MySQL Servers

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

Updating MySQL

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

MySQL Security

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

MySQL Database Maintenance and Repair

  • Checking and repairing tables
  • Invoking myisamchk and isamchk
  • Extended checks
  • Standard table repair
  • Table repair with missing/damaged index or table description
  • Avoid server-checking interaction, without shutdowns

 

One thought on “MySQL DBA Responsibilities”

Leave a Reply

Your email address will not be published. Required fields are marked *