Privileges: MySQL databases are only available to specific users; each user has a password. Users can be given specific access rights to entire databases, specific tables within databases, or specific columns within tables. This is all done by setting fields within master tables within a MySQL administrative database, using the SQL command GRANT:
- user—details global user privileges, which apply to every database in the system (schema in table 12.1). Most users will not be given any global privileges.
- db—lists specific databases and users, and the privileges that user has on that database.
- host—lists specific hosts from which a user may connect, and the privileges available to that user from that host.
- tables_priv—stores table-level privileges for each user, within specific databases.
- columns_priv—stores column-level privileges for each user, within specific tables, within specific databases.
The priveleges tables are read when MySQL is started and when a GRANT or REVOKE instruction is issued. If changes are made in the table manually you need to issue a flush privileges command to get the altered privileges to take effect (page 291).
Security
Start by not running the MySQL server as the root user, as this gives a user full control of all databases (virtually all files in the operating system). Use intelligent passwords (no dictionary words; use combinations of numbers and letters, upper and lower case); don't store passwords in script files that can be seen by anybody but the password owner. You can put login and password information into files that are stored outside of the web document tree, to be included in a page as needed, within php code so that nothing sensitive is sent to the browser.
Priveleges should be as restrictive as possible, only issued as needed. For web users, create a general user account that is limited to specific tables, and generally limited to select or to carefully protected administrative uses for insert, update, or delete.
MySQL Information (on a database) Statements
SHOW: SHOW databases; and SHOW tables from database_name; list available databases and tables within a database. SHOW columns from t—able_name from database_name; lists columns in a table (alternatively, SHOW columns from database_name.table_name;). SHOW grants from database_name lists privileges. Table 12.6 lists other variations. (see SHOW Syntax)
DESCRIBE: DESCRIBE table_name [column]; provides information about all of the columns in a table (an alternative to SHOW columns).
EXPLAIN: Used as EXPLAIN table_name; this is similar to DESCRIBE or SHOW columns. Used as EXPLAIN SELECT ... (any select statement); this produces a detailed analysis of the select query, including
- Select types (table 12.7, p. 298)
- SIMPLE—ordinary select
- PRIMARY—outer query, where subqueries and unions are used
- UNION—second or later query in a union
- DEPENDENT—second or later query, dependent on the primary query
- SUBQUERY—inner subquery
- DEPENDENT SUBQUERY—inner subquery, dependent on the primary query
- DERIVED—subquery used in FROM clause
- tables—lists tables used to answer the query
- type, lists joins (table 12.8, p. 298)
- const or system—table is read once (it has one row)
- eq_ref—for every set of rows from the other tables in the joing, read one row from this table. Used when the join uses all parts of the index on the table and the index is UNIQUE or the primary key
- ref—for every set of rows from the other tables in the join, you read a set of table rows that all match (join cannot choose a single row based onjoin condition, as when only part of the key is used in the join or it is not UNIQUE or a primary key
- ref_or_null—like ref, but alinclued rows that are NULL (used mostly in subqueries)
- index_merge—INDEX MERGE (a specific optimization) has been used
- unique_subquery—replaces ref for some IN subqueries where one unique row is returned
- index_subquery—similar to unique_subquery but used for indexed nonunique subqueries
- range—for every set of rows from the other tables in the join, you read a set of table rows that fall into a particular range
- index—the entire index is scanned
- ALL—every row in the table is scanned
The rows column lists the number of rows of each table that has to be scanned to perform the join. The smaller this number, the faster the execution time.
Output from EXPLAIN can be used to check column types (the same?) and widths (indexes can't be used to match columns if they have different widths; you can tell the join optimizer to look at key distributions and optimize joins more efficiently using myiasmchk or ANALYZE TABLE statements (see page 300).
Indexes: Indexes can speed up queries. Add an index using ALTER TABLE table_name ADD INDEX (column); (See Coding and Indexing Strategies for Optimal Performance)
Optimizing: You can speed up your database by using smaller database types (p. 301), simplifying permissions (p. 301), defragmenting a table using OPTIMIZE TABLE table_name (p. 301), using indexes (p. 302, although not at all clear), and using default data (p. 302).
Backing Up a Database: You can dump a copy of a database using instructions on page 302. You can also mirror a database on separate (slave) servers; setup and implementation are described on pages 303-305.