12. Advanced MySQL Administration

In this chapter, we cover some more advanced MySQL topics, including advanced privileges, security, and optimization.

Key topics covered in this chapter include

Understanding the privilege system in detail

Making your MySQL database secure

Getting more information about databases

Speeding things up with indexes

Optimizing your database

Backing up and recovering

Implementing replication

When you issue a GRANT statement, it affects tables in the special database called mysql. Privilege information is stored in seven tables in this database. Given this fact, when granting privileges on databases, you should be cautious about granting access to the mysql database.

You can look at what’s in the mysql database by logging in as an administrator and typing

USE mysql;

If you do this, you can then view the tables in this database as usual by typing

SHOW TABLES;

Your results look something like this:

Click here to view code image

+-------------------------------+
| Tables_in_mysql               |
+-------------------------------+
| columns_priv                  |
| db                            |
| event                         |
| func                          |
| general_log                   |
| help_category                 |
| help_keyword                  |
| help_relation                 |
| help_topic                    |
| host                          |
| innodb_index_stats            |
| innodb_table_stats            |
| ndb_binlog_index              |
| plugin                        |
| proc                          |
| procs_priv                    |
| proxies_priv                  |
| rds_configuration             |
| rds_global_status_history     |
| rds_global_status_history_old |
| rds_heartbeat2                |
| rds_history                   |
| rds_replication_status        |
| rds_sysinfo                   |
| servers                       |
| slave_master_info             |
| slave_relay_log_info          |
| slave_worker_info             |
| slow_log                      |
| tables_priv                   |
| time_zone                     |
| time_zone_leap_second         |
| time_zone_name                |
| time_zone_transition          |
| time_zone_transition_type     |
| user                          |
+-------------------------------+
36 rows in set (0.00 sec)

Each of these tables stores system information. Seven of them—user, host, db, tables_priv, columns_priv, proxies_priv, and procs_priv—store privilege information. They are sometimes called grant tables. These tables vary in their specific function but all serve the same general function, which is to determine what users are and are not allowed to do. Each of them contains several types of fields. There are scope fields, which identify the user, host, and part of a database that the privilege refers to; and privilege fields, which identify which actions can be performed by that user in that scope. There are also security fields, which contain security-related information, and resource control columns, which constrain the number of resources that may be consumed.

The user table is used to decide whether a user can connect to the MySQL server at all and whether he or she has any administrator privileges. The host table was formerly used and is now obsolete, but it is still present in the mysql database. The db table determines which databases the user can access. The tables_priv table determines which tables within a database a user can use, the columns_priv table determines which columns within tables she has access to, the proxies_priv table determines which users can act as proxies or grant proxy privileges to others, and the procs_priv table determines which routines a user can execute.

You can see the structure of this table by issuing a DESCRIBE user; statement. The schema for the user table is shown in Table 12.1.

Most rows in this table correspond to a set of privileges for a User coming from a Host and logging in with the password. These are the scope fields for this table because they describe the scope of the privilege fields. The privilege fields end with _priv.

The privileges listed in this table (and the others to follow) correspond to the privileges granted using GRANT in Chapter 9. For example, Select_priv corresponds to the privilege to run a SELECT command.

If a user has a particular privilege, the value in that column will be Y. Conversely, if a user has not been granted that privilege, the value will be N.

All the privileges listed in the user table are global; that is, they apply to all the databases in the system (including the mysql database). Administrators will therefore have some Ys in there, but the majority of users should have all Ns. Normal users should have rights to appropriate databases, not all tables.

The two other sets of fields in this table are the security fields and the resource control fields.

The security fields are ssl_type, ssl_cipher, x509_issuer, x509_subject, plugin, authentication_string, and password_expired. The plugin field is NULL by default but may be set to contain an authentication plugin that will be used to authenticate a particular user account. The authentication_string may be used by plugins. The ssl_type and ssl_cipher fields are used if you have enabled SSL connections.

The db table determines which users can access which databases from which hosts. The privileges listed in this table apply to whichever database is named in a particular row.

The schema of this table is shown in Table 12.2.

These tables have a slightly different structure than the user and db tables. The schemas for the tables_priv table, columns_priv table, procs_priv table, and proxies_priv table are shown in Tables 12.3, 12.4, 12.5, and 12.6, respectively.

The Grantor column in the tables_priv and procs_priv tables stores the name of the user who granted this privilege to this user. The Timestamp column in each of these tables stores the date and time when the privilege was granted. In the proxies_priv table, these columns are currently unused.

1. Connection verification. Here, MySQL checks whether you are allowed to connect at all, based on information from the user table, as shown previously. This authentication is based on username, hostname, and password. If a username is blank, it matches all users. Hostnames can be specified with a wildcard character (%). This character can be used as the entire field (that is, % matches all hosts) or as part of a hostname (for example, %.example.com matches all hosts ending in .example.com). If the password field is blank, no password is required. Your system is more secure if you avoid having blank users, wildcards in hosts, and users without passwords. If the hostname is blank, it is effectively a wildcard, but with lower precedence than the % wildcard.

2. Request verification. Each time you enter a request, after you have established a connection, MySQL checks whether you have the appropriate level of privileges to perform that request. The system begins by checking your global privileges (in the user table) and, if they are not sufficient, checks the db table. If you still don’t have sufficient privileges, MySQL will check the tables_priv table, and, if this is not enough, finally it will check the columns_priv table. If the operation uses stored routines, MySQL checks the procs_priv table instead of the tables_priv and columns_priv tables. If you are trying to proxy another user, or grant proxy privileges to a user, MySQL checks the proxies_priv table.

You need to point out to the server that a change has occurred, and you can do this in three ways. You can type

mysql> flush privileges;

at the MySQL prompt. (You need to be logged in as an administrator to use this command.) This is the most commonly used way of updating the privileges.

Alternatively, you can run either

> mysqladmin flush-privileges

or

> mysqladmin reload

from your operating system.

Finally, the MySQL server will reload the grant tables when it is restarted.

After this, global-level privileges will be checked the next time a user connects; database privileges will be checked when the next use statement is issued; and table- and column-level privileges will be checked on a user’s next request.

Setting up a MySQL user specifically for the purpose of running mysqld is a good idea. In addition, you can then make the directories (where the physical data is stored) accessible only by the MySQL user. In many installations, the server is set up to run as userid mysql, in the mysql group.

You should also set up your MySQL server on an internal network, or behind your firewall. This way, you can prevent connections from unauthorized machines. Check to see whether you can connect from outside to your server on port number 3306. This is the default port MySQL runs on and should be closed on your firewall.

If you are going to store passwords in script files, make sure only the user whose password is stored can see that script. This is generally only relevant in shared hosting environments, which are less common these days.

PHP scripts that are used to connect to the database need access to the password for that user. This can be done reasonably securely by putting the login and password in a file called, for example, dbconnect.php, that you then include when needed. This script can be carefully stored outside the web document tree and made accessible only to the appropriate user.

Remember that if you put these details in a file with .inc or some other extension in the web tree, you must be careful to check that your web server knows these files must be interpreted as PHP so that the details will not be viewed in plain text via a web browser. It’s better to avoid this situation altogether.

Don’t store passwords in plain text in your database. MySQL passwords are not stored that way, but commonly in web applications, you additionally want to store website members’ login names and passwords. You can encrypt passwords (one way) using MySQL’s password() function. Remember that if you insert a password in this format when you run SELECT (to log in a user), you will need to use the same function again to check the password a user has typed.

You will use this functionality when you implement the projects in Part V, “Building Practical PHP and MySQL Projects.”

As a starting point, don’t give access to the mysql database to any non-administrative user.

Don’t grant the PROCESS, FILE, SUPER, SHUTDOWN, and RELOAD privileges to any user other than an administrator unless absolutely necessary, and if you must, try to grant it only for the minimum time necessary. The PROCESS privilege can be used to see what other users are doing and typing, including their passwords. The FILE privilege can be used to read and write files to and from the operating system (including, say, /etc/password on a Unix system). The SUPER privilege can be used to terminate other connections, modify system variables, and control replication. The RELOAD privilege is used to reload grant tables.

The GRANT privilege should also be granted with caution because it allows users to share their privileges with others.

Finally, and perhaps non-obviously, grant the ALTER option with caution. Users may use it to rename a table and hence subvert the privilege system.

Make sure that when you set up users, you grant them access only from the hosts that they will be connecting from. Avoid using wildcards in hostnames for similar reasons.

You can further increase security by using IPs rather than domain names in your host table. This way, you can avoid problems with compromised DNS. You can enforce this by starting the MySQL daemon (mysqld) with the --skip-name-resolve option, which means that all host column values must be either IP addresses or localhost.

You should also prevent non-administrative users from having access to the mysqladmin program on your web server. Because this program runs from the command line, access to it is an issue of operating system privilege.

It is a good idea to start by setting up a special user just for the purpose of web connections from a particular web application. This way, you can give the user the minimum privilege necessary and not grant, for example, DROP, ALTER, or CREATE privileges to that user. You might grant SELECT only on catalog tables and INSERT only on order tables. Again, this is an illustration of how to use the principle of least privilege.

You should always check all data coming in from a user. Even if your HTML form consists of select boxes and radio buttons, someone might alter the URL to try to crack your script. Checking the size of the incoming data is also worthwhile.

If users are typing in passwords or confidential data to be stored in your database, remember that it will be transmitted from the browser to the server in plain text unless you use Secure Sockets Layer (SSL). We discuss using SSL in more detail later in this book.

mysql> SHOW TABLES;

to get a list of tables in the database.

The statement

mysql> SHOW DATABASES;

displays a list of available databases. You can then use the SHOW TABLES statement to see a list of tables in one of those databases:

mysql> SHOW TABLES FROM books;

When you use SHOW TABLES without specifying a database, it defaults to the one in use.

When you know what the tables are, you can get a list of the columns:

mysql> SHOW COLUMNS FROM Orders FROM books;

If you leave off the database name, the SHOW COLUMNS statement will default to the database currently in use. You can also use the table.column notation:

mysql> SHOW COLUMNS FROM books.Orders;

One other useful variation of the SHOW statement can be used to see what privileges a user has. For example, if you run

mysql> SHOW GRANTS FOR 'bookorama';

you get the following output:

Click here to view code image

+--------------------------------------------------------------------------------+
| Grants for bookorama@%                                                         |
+--------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bookorama'@'%' IDENTIFIED BY PASSWORD '*1ECE648641438A28E1910D0D7403C5EE9E8B0A85' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `books`.* TO 'bookorama'@'%'   |
+--------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The GRANT statements shown are not necessarily the ones that were executed to give privileges to a particular user, but rather summary equivalent statements that would produce the user’s current level of privilege.

Many other variations of the SHOW statement can be used as well. In fact, there are over 30 variations of the SHOW statement. Some of the more popular variations are shown in Table 12.7. For a complete list, see the MySQL Manual entry at http://dev.mysql.com/doc/refman/5.6/en/show.html. In all instances of [like_or_where] in the examples below, you can attempt to match a pattern using LIKE or an expression using WHERE.

DESCRIBE table [column];

This command gives information about all the columns in the table or a specific column if column is specified. You can use wildcards in the column name if you like.

EXPLAIN table;

This command gives similar output to DESCRIBE table or SHOW COLUMNS FROM table.

The second and more interesting way you can use EXPLAIN allows you to see exactly how MySQL evaluates a SELECT query. To use it this way, just put the word EXPLAIN in front of a SELECT statement.

You can use the EXPLAIN statement when you are trying to get a complex query to work and clearly haven’t got it quite right, or when a query is taking a lot longer to process than it should. If you are writing a complex query, you can check this in advance by running the EXPLAIN command before you actually run the query. With the output from this statement, you can rework your SQL to optimize it if necessary. It’s also a handy learning tool.

For example, try running the following query on the Book-O-Rama database:

Click here to view code image

EXPLAIN
SELECT Customers.Name
FROM Customers, Orders, Order_Items, Books
WHERE Customers.CustomerID = Orders.CustomerID
AND Orders.OrderID = Order_Items.OrderID
AND Order_Items.ISBN = Books.ISBN
AND Books.Title LIKE '%Java%';

This query produces the following output. (Note that we are displaying this output vertically because the table rows are too wide to fit in this book. You can get this format by ending your query with G instead of the semicolon.)

Click here to view code image

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Customers
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Orders
         type: ref
possible_keys: PRIMARY,CustomerID
          key: CustomerID
      key_len: 4
          ref: books.Customers.CustomerID
         rows: 1
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: Order_Items
         type: ref
possible_keys: PRIMARY,ISBN
          key: PRIMARY
      key_len: 4
          ref: books.Orders.OrderID
         rows: 1
        Extra: Using index
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: Books
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where; Using join buffer (Block Nested Loop)
4 rows in set (0.00 sec)

This output might look confusing at first, but it can be very useful. Let’s look at the columns in this table one by one.

The first column, id, gives the ID number of the SELECT statement within the query that this row refers to.

The column select_type explains the type of query being used. The set of values this column can have is shown in Table 12.8.

The column table just lists the tables used to answer the query. Each row in the result gives more information about how that particular table is used in this query. In this case, you can see that the tables used are Orders, Order_Items, Customers, and Books. (You know this already by looking at the query.)

The type column explains how the table is being used in joins in the query. The set of values this column can have is shown in Table 12.9. These values are listed in order from fastest to slowest in terms of query execution. The table gives you an idea of how many rows need to be read from each table to execute a query.

In the previous example, you can see that one of the tables is joined using eq_ref (Books), one is joined using ref (Order_Items), one is joined using index (Orders), and one (Customers) is joined using ALL—that is, by looking at every single row in the table.

The rows column backs this up: It lists (roughly) the number of rows of each table that has to be scanned to perform the join. You can multiply these numbers together to get the total number of rows examined when a query is performed. You multiply these numbers because a join is like a product of rows in different tables. Check out Chapter 10, “Working with Your MySQL Database,” for details. Remember that this is the number of rows examined, not the number of rows returned, and that it is only an estimate; MySQL can’t know the exact number without performing the query.

Obviously, the smaller you can make this number, the better. At present, you have a negligible amount of data in the database, but when the database starts to increase in size, this query would increase in execution time. We return to this matter shortly.

The possible_keys column lists, as you might expect, the keys that MySQL might use to join the table.

The key column is either the key from the table MySQL actually used or NULL if no key was used.

The key_len column indicates the length of the key used. You can use this number to tell whether only part of a key was used. The key length is relevant when you have keys that consist of more than one column.

The ref column shows the columns used with the key to select rows from the table.

Finally, the Extra column tells you any other information about the way the join was performed. Some possible values you might see in this column are shown in Table 12.10. For a complete list of the more than 30 different possibilities, see the MySQL Manual at http://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain-extra-information.

You can fix problems you spot in the output from EXPLAIN in several ways. First, you can check column types and make sure they are the same. This applies particularly to column widths. Indexes can’t be used to match columns if they have different widths. You can fix this problem by changing the types of columns to match or by building this in to your design from the start.

Second, you can tell the join optimizer to examine key distributions and therefore optimize joins more efficiently using an ANALYZE TABLE statement within the MySQL monitor:

Click here to view code image

ANALYZE TABLE Customers, Orders, Order_Items, Books;

Third, you might want to consider adding a new index to the table. If this query is a) slow and b) common, you should seriously consider this fix. If it’s a one-off query that you’ll never use again, such as an obscure report requested once, this technique may not be worth the effort because it may slow down other things.

If the possible_keys column from an EXPLAIN contains some NULL values, you might be able to improve the performance of your query by adding an index to the table in question. If the column you are using in your WHERE clause is suitable for indexing, you can create a new index for it using ALTER TABLE like this:

ALTER TABLE ADD INDEX (column);

Finally, there are a couple of things to look out for in the Extra column. If you see Using temporary, it often means that you have different columns in your GROUP BY and ORDER BY columns. If you can restructure your query to avoid that it will help. If you see Using filesort, it means that MySQL is making two passes: one to retrieve data, and the other to order the data (typically for an ORDER BY clause). In this case, consult the excellent (and long) article in the MySQL manual on how to optimize ORDER BY queries: http://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html.

Avoid variable length columns if at all possible with MyISAM tables (such as VARCHAR, TEXT, and BLOB). If your tables have fixed-length fields, they will be faster to use but might take up a little more space.

OPTIMIZE TABLE tablename;

The first way is to lock the tables while you copy the physical files, using a LOCK TABLES command with the following syntax:

Click here to view code image

LOCK TABLES table lock_type [, table lock_type ...]

Each table should be the name of a table, and the lock type should be either READ or WRITE. For a backup, you only need a read lock. You need to execute a FLUSH TABLES; command to make sure any changes to your indexes have been written to disk before performing a backup.

Users and scripts can still run read-only queries while you make your backup. If you have a reasonable volume of queries that alter the database, such as customer orders, this solution is not practical.

The second, and superior, method is using the mysqldump command. Usage is from the operating system command line, and is typically something such as

Click here to view code image

> mysqldump --all-databases > all.sql

This command dumps a set of all the SQL required to reconstruct the database to the file called all.sql.

If using InnoDB (the default engine), you can make an online backup, and even better keep track of where you were up to in the binary log. What this means is that if you are restoring your database from that backup, you will be able to reload the backup and then replay changes since the backup was made. You can do this with the following options:

Click here to view code image

> mysqldump --all-databases --single-transaction --flush-logs
  --master-data=2 > all_databases.sql

The --single-transaction option does the backup while the database is still running (by acquiring a read lock). The --flush-logs and --master-data options flush the logs and then note the point in the log where the backup was made.

A third method is using the mysqlhotcopy script. You can invoke it with

Click here to view code image

> mysqlhotcopy database /path/for/backup

A final method of backup, which also has other advantages, is to maintain one or more replicated copies of the database. Replication is discussed later in this chapter.

If you used the first method from the preceding section for backup, you can copy the data files back into the same locations in a new MySQL installation.

If you used the second method for backup, there are a couple of steps. First, you need to run the queries in your dump file. This step reconstructs the database up to the point where you dumped that file. Second, you need to update the database to the point stored in the binary log. You can do this by running the command

Click here to view code image

> mysqlbinlog bin.[0-9]* | mysql

More information about the process of MySQL backup and recovery can be found at the MySQL website at http://www.mysql.com.

The basic idea is to have a master server and add to it a number of slaves. Each of the slaves mirrors the master. When you initially set up the slaves, you copy over a snapshot of all the data on the master at that time. After that, slaves request updates from the master. The master transmits details of the queries that have been executed from its binary log, and the slaves reapply them to the data.

The usual way of using this setup is to apply write queries to the master and read queries to the slaves. This is called read-write splitting and can be implemented using a tool such as MySQL Proxy, or in your application logic.

More complex architectures are possible, such as having multiple masters, but we will only consider the setup for the typical master-slave example.

You need to realize that slave data is generally not as up to date as on the master. This occurs in any distributed database. The difference between the master and slaves is sometimes referred to as slave lag.

To begin setting up a master and slave architecture, you need to make sure binary logging is enabled on the master. Enabling binary logging is discussed in Appendix A, “Installing Apache, PHP, and MySQL.”

You need to edit your my.ini or my.cnf file on both the master and slave servers. On the master, you need the following settings:

[mysqld]
log-bin
server-id=1

The first setting turns on binary logging (so you should already have this one; if not, add it in now). The second setting gives your master server a unique ID. Each of the slaves also needs an ID, so you need to add a similar line to the my.ini/my.cnf files on each of the slaves. Make sure the numbers are unique! For example, your first slave could have server-id=2; the next, server-id=3; and so on.

In most cases, you will use a database snapshot to transfer the data, and in this case, only the special replication slave privilege is needed. If you decide to use the LOAD DATA FROM MASTER command to transfer data (you learn about it in the next section), this user will also need the RELOAD, SUPER, and SELECT privileges, but only for initial setup. As per the principle of least privilege, discussed in Chapter 9, you should revoke these other privileges after the system is up and running.

Create a user on the master. You can call it anything you like and give it any password you like, but you should make a note of the username and password you choose. In our example, we call this user rep_slave:

Click here to view code image

GRANT REPLICATION SLAVE
ON *.*
TO 'rep_slave'@'%' IDENTIFIED BY 'password';

Obviously, you should change the password to something else.

Click here to view code image

mysql> FLUSH TABLES WITH READ LOCK;

The reason for the read lock is that you need to record the place the server is up to in its binary log when the snapshot was taken. You can do this by executing this statement:

mysql> SHOW MASTER STATUS;

You should see output similar to the following from this statement:

Click here to view code image

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+

Note the File and Position; you will need this information to set up the slaves.

Now take your snapshot and unlock the tables with the following statement:

mysql> UNLOCK TABLES;

Next, run the following queries on your slave:

Click here to view code image

change master to
master-host='server',
master-user='user',
master-password='password',
master-log-file='logfile',
master-log-pos=logpos;
start slave;

You need to fill in the data shown in italics. The server is the name of the master server. The user and password come from the GRANT statement you ran on the master server. The logfile and logpos come from the output of the SHOW MASTER STATUS statement you ran on the master server.

You should now be up and running.

You might also want to consult Paul Dubois’ book MySQL (Fifth Edition), available from Addison-Wesley.