เนื้อหาหลักสูตร

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