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 (isamchk & myisamchk)
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
- Internal and external locking
- Locking for checks and locking for repairs
- Speeding up checks
- Scheduled checks and preventive maintenance with cron
- Automated checks at startup
|
