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.
