 | MySQL & mSQL |  |
You should now have a fresh MySQL installation up and running. The first thing you should do is change the root password for the server using the following command from inside the MySQL installation directory:
./bin/mysqladmin -u root password `'mynewpasswd''
With the MySQL server up and running securely, you can begin tackling some basic database administration issues so that MySQL can begin serving your needs.
The mysqladmin is the primary tool for database administration under MySQL. Using this utility, you can create, destroy, and monitor your server and the databases it supports.
Your database server is useless to you unless it actually has databases to serve. Using mysqladmin, you can create new databases:
mysqladmin -p create DATABASENAME
The -p option tells MySQL that you want to be prompted for the root password you specified earlier. If you enter the correct password, mysqladmin will create a new, blank database with the name you specify. Because a database under MySQL is a set of files in a specific directory, the mysqladmin create command creates a new directory to hold the files for the new database. For example, if you created a database called "mydata," the directory mydata will appear in the data directory of your MySQL installation.
WARNING
Because MySQL databases and tables are stored as file system files, you will encounter an unfortunate behavioral mismatch between Win32 implementations and Unix implementations. Specifically, all Win32 file systems are case-insensitive while Unix file systems are case-sensitive. The result is that database and table names are case- sensitive under Unix and case-insensitive under Win32.
During the process of developing a new database application, you will likely want to create several databases to support the development process. For example, it is common in database application development to have separate databases for development, testing, and production. When development is complete, it is time to get rid of the development and testing databases. The mysqladmin utility provides the "drop" option to let you delete a database:
mysqladmin -p drop DATABASENAME
As with the mysqladmin create command, DATABASENAME is the name of the database you wish to destroy. MySQL will not let you accidentally drop the database. After issuing this command, it will warn you that dropping a database is potentially a very bad thing to do and ask you to confirm the drop. You can examine the data directory after dropping the database to verify that the directory that once served as that database no longer exists.
MySQL does not have a utility for renaming and copying databases. Because databases are simply files in a specific directory, you can, with care, use operating systems to copy or rename databases. Though using the file system commands will successfully copy or rename the database in question, they will not carry over the security configurations from the original table because MySQL keeps security information in a database table. In order to fully copy a database, you will have to also duplicate its security information in the MySQL system database. We will go into more detail on MySQL security later in the chapter.
MySQL provides a very rich array of commands in the mysqladmin utility for monitoring the MySQL server. Running the command mysqladmin status will provide a single line status display that looks like this:
Uptime: 395 Threads: 1 Questions: 14 Slow queries: 0 Opens: 10 Flush tables: 1 Open tables: 6
The values you see in the mysqladmin status output have the following meanings:
The number of seconds the server has been up and running.
The number of threads that are currently interacting with the database. When examining the number of threads, you will always see at least one thread. The one thread is the one counting all the other threads. The server also has three other threads that are not visible to this command -- one to handle signals, one to manage all of the other threads, and one to listen for incoming connections.
The number of queries that have been sent to the database since it started.
The number of queries that have taken longer than a configurable amount of time to execute. The configuration key is long_query_time. We will discuss configuration parameters later in the chapter.
The number of tables that have been opened since the server started.
The number of flush, refresh, and reload commands.
The number of tables currently open. Because MySQL is multithreaded, one table may be open more than once at any given time. For instance, any number of SELECT statements can be performed on the same table at the same time. Because of this trick, the value of "Open tables" can be larger than the number of tables in the system.
The mysqladmin status command also provides values for memory in use and maximum memory used if MySQL was compiled with the --with-debug option.
If you are looking for some more general, static information, then mysqladmin version is the command you are looking for. It provides the following output:
bin/mysqladmin Ver 7.8 Distrib 3.22.17, for sun-solaris2.6 on sparc TCX Datakonsult AB, by Monty Server version 3.22.17 Protocol version 10 Connection Localhost via Unix socket Unix socket /tmp/mysql.sock Uptime: 23 min 58 sec Threads: 1 Questions: 15 Slow queries: 0 Opens: 10 Flush tables: 1 Open tables: 6
The last line of information is, of course, identical to the information you saw from mysqladmin status. The rest of the display is entirely new.
The version of MySQL being run.
The version of the MySQL communications protocol that the server supports. If you are having problems with a tool that uses the MySQL communications protocol, you might want to check the value it expects against your MySQL protocol version from this display.
The method by which you are connected to the server. In the example above, the client is communicating with MySQL through a Unix socket. If you are looking at a remote MySQL server, this entry will hold the name of the machine from which you are connecting.
The file name of the socket you are using to communicate with the server. If you are communicating with MySQL via TCP/IP, this entry will disappear in favor of a TCP port entry that holds the port number of the MySQL server.
The total time the server has been running.
Two other commands, mysqladmin variables and mysqladmin extended-status, offer more information.
Because MySQL is multithreaded, monitoring process activity is not as simple as using the Unix ps command. Though many threads are running, only one process will appear in the process list. To help address this problem, MySQL provides the mysqladmin processlist to display all of the running threads in a nicely formatted table:
+----+------+-----------+------+-----------+------+-------+------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+-----------+------+-------+------+ | 920| joe | client.com|mydata| Sleep | 0 | | | | 939| root | localhost | | Processes | 0 | | | +----+------+-----------+------+-----------+------+-------+------+
This output tells you exactly what each thread is doing. The values in the display have the following meaning:
The internal identification number of the thread. This value has no relation to any operating system process IDs. You can use this number with the mysqladmin kill command to terminate the thread.
The user connected to the server with this thread.
The host from which the user is connected.
The database to which the user is connected.
The type of command being executed by the thread. The command can be one of the following:
The thread is waiting for user input. Most processes should be in this state.
The thread is in the process of terminating.
The thread is preparing the selected database for interaction. A client may communicate with only one database at a time, but it can switch any time it likes.
The thread is performing an actual query. While most interaction with the database is in the form of queries, these commands occur very quickly and thus rarely appear in this output.
The thread is generating a list of the fields in a table.
The thread is creating a new database.
The thread is deleting a database.
The thread is reloading the MySQL access tables. When the reload is finished, all new threads will use the refreshed access tables.
The thread is reloading the MySQL access tables. When the reload is finished, all new threads will use the refreshed access tables.
The thread is in the process of terminating all other threads and shutting down the server.
The thread is generating statistics.
This thread is examining other threads. The thread executing this command will show up with this value.
The thread is negotiating an incoming connection from a client.
The thread is terminating another thread.
The thread is flushing all of the caches and resetting the log files.
The MySQL log file provides yet another way to get useful administrative information about the MySQL server. MySQL generates the main log if mysqld is launched with the -- log option. This log appears in /usr/local/var/HOSTNAME.log where HOSTNAME is the name of the machine on which MySQL is running. This log tracks connections to the server and the commands that clients send to it.
By passing the -- debug option to mysqld (or safe_mysqld), you can have MySQL send additional information to the log file. The debug package that MySQL uses has dozens of options, most of which you will never use. The most common debug setting, however, is -d:t:o,FILENAME where FILENAME is the name of the debug log you wish to use. This option will log almost everything the server does, step-by-step.
MySQL supports one more human-readable log. When you start MySQL with the -- log-update option, MySQL will create a file with the name HOSTNAME.# where HOSTNAME is the name of the server machine and # is a unique number. This log will hold all changes to database tables. The log appears as SQL so that the operations can be replicated in another database server.
The following command will perform a clean shutdown of the MySQL database server:
mysqladmin -p shutdown
This command is the most orderly way to shut down the server. If you started MySQL with safe_mysqld and try using some other method for shutting down the server, safe_mysqld will just start up a new instance of the server. One can also shut down the server safely with the traditional Unix kill command. But avoid using the drastic kill-9 command.
The mysqladmin utility is a very rich tool with a handful of command line options. Its general format is
mysqladmin OPTIONS COMMAND1 COMMAND2 ... COMMANDn
In other words, you can issue multiple commands at one time with the mysqladmin utility. Just for grins, you could do
mysqladmin -p create silly drop silly
This command will both create and drop the database "silly" in one shot. The following is a list of commands you can send to mysqladmin:
Creates a new database with the specified name
Drops an existing database with the specified name
Provides an extended status message from the server
Flushes all cached hosts
Flushes all logs
Flushes all tables
Same as reload
Terminates the threads with the specified thread IDs
Changes the old password to the specified value
Verifies that mysqld is still running
Shows a list of active threads
Reloads all grant tables
Flushes all tables and closes and opens all log files
Shuts down the server
Gives a short status message from the server
Prints available variables
Shows server version information
In addition to the commands it supports, it also supports the following options:
Output debug log. Often this is `d:t:o,FILENAME'.
Do not ask for confirmation of a dropped table and continue to the next command even if this one fails.
Show help for the mysqladmin utility.
Use compression in the client/server protocol.
Connect to the specified host.
Use the specified password to validate the user. If this option is used without specifying a password, then the user will be prompted to enter the password.
Use the specified port number for a connection.
Execute the commands repeatedly with the specified sleep interval in between executions.
Silently exit if a connection to the server cannot be established.
The file to use for the Unix socket.
The timeout for the connection.
The user for the login if not the current user.
Show version information and exit.
Wait and retry the specified number of times if the server is not currently up.
The importance of regular backups in successful operation cannot be stressed enough. Without a usable backup, a single power outage can destroy months or years of work. However, with a properly planned backup schedule, you can recover from almost any catastrophe in a very short time.
Chapter 5, "mSQL", provides a detailed discussion of the role of the msqldump command in mSQL backups. MySQL supports nearly identical functionality in the form of the mysqldump command. We recommend that you have a look at that discussion to understand the use of mysqldump and full backups. In this section, we will focus on the next most important form up backup: the incremental backup.
While full data backups are technically all that are needed to recover from data loss, they can be difficult to work with at times. When you have a great deal of data, the files required to backup all of the data can take up a large amount of space. Therefore, it is common practice to only back up all data once a week or some similar data. Then, every day, a backup is performed of all data that has changed since the last full backup. This is referred to as an incremental backup.
With MySQL, it is possible to perform an incremental backup using a feature of the database server known as the "update log." If the mysqld database server is launched with the --log-update option, all changes to any database will be logged in a file as an SQL command. These changes will be logged in the order they happen. The result is a file that, when fed into the mysql monitor, will replay all actions that have been performed on the database. If the log has been kept from the beginning of the database, it will go through the entire life of the database and end up with the data in its current state.
More usefully, if the log is kept since a certain defined point, say the last full data backup, the log can then be used to catch up the backup to the current state. In this way, the functionality of an incremental backup is obtained. At a regular interval (such as every week) perform a full data backup. Then every day copy the update log either to tape, or to a backup area on hard disk. Keep a separate copy of the update log for every day back to the last full data backup. This provides the ability to recreate the database in case of disaster and also to recover any partial data lost since the last full data backup. Because the update log is plain ASCII, SQL commands they can be searched for specific data.
Whatever method you use to back up your data, make sure that you do it often and that your periodically check your backups to make sure that you can indeed use them to recover your system. Many database administrators have faithfully kept backups only to find out in their time of need that because of some error -- human, software, or physical media -- their backups were absolutely useless.
In addition to making sure you can get at your data reliably, you also want to make sure others cannot get to it at all. MySQL uses its own database server to implement security. When you first install MySQL, the installation process creates a database called "mysql." This database contains five tables: db, host, user, tables_priv, and columns_priv. Newer versions of MySQL also create a database called func, but it is unrelated to security. MySQL uses these tables to decide who is allowed to do what. The user table contains security information that applies to the server as a whole. The host table gives entire machines rights to the server. Finally, the db, tables_priv, and columns_priv tables control access to individual databases, tables, and columns.
We will take a brief look at all of MySQL's security tables and then discuss the details of how they work together to make MySQL a secure database engine.
The user table shown in Table 4-1 has the following structure:
|
Field |
Type |
Null |
Key |
Default |
Extra |
|---|---|---|---|---|---|
|
Host |
char(60) |
PRI |
|||
|
User |
char(16) |
PRI |
|||
|
Password |
char(16) |
||||
|
Select_priv |
enum(`N',`Y') |
N |
|||
|
Insert_priv |
enum(`N',`Y') |
N |
|||
|
Update_priv |
enum(`N',`Y') |
N |
|||
|
Delete_priv |
enum(`N',`Y') |
N |
|||
|
Create_priv |
enum(`N',`Y') |
N |
|||
|
Drop_priv |
enum(`N',`Y') |
N |
|||
|
Reload_priv |
enum(`N',`Y') |
N |
|||
|
Shutdown_priv |
enum(`N',`Y') |
N |
|||
|
Process_priv |
enum(`N',`Y') |
N |
|||
|
File_priv |
enum(`N',`Y') |
N |
|||
|
Grant_priv |
enum(`N',`Y') |
N |
|||
|
References_priv |
enum(`N',`Y') |
N |
|||
|
Index_priv |
enum(`N',`Y') |
N |
|||
|
Alter_priv |
enum(`N',`Y') |
N |
In both the Host and User columns, you can use "%" wildcard values. A host name of "chem%lab," for example, includes "chembiolab," "chemtestlab," and so on. The special user name "nobody" acts like a single "%." It covers any user not explicitly named elsewhere. Here is what the different access rights mean:
The ability to perform SELECT statements
The ability to perform INSERT statements
The ability to perform UPDATE statements
The ability to perform DELETE statements
The ability to perform CREATE statements or to create databases
The ability to perform DROP statements or to drop databases
The ability to reload access information via mysqladmin reload
The ability to shutdown the server via mysqladmin shutdown
The ability to manage server processes
The ability to read and write files using commands like SELECT INTO OUTFILE and LOAD DATA INFILE
The ability to grant your privileges to others
The ability to create or drop indices
The ability to perform the ALTER TABLE statement
MySQL provides a special function to keep passwords safe from prying eyes. The password() function encrypts a password. The following statements show the password() function in action in the course of adding users to the system.
INSERT INTO user (Host, User, Password, Select_priv,
Insert_priv, Update_priv, Delete_priv)
VALUES ('%', 'bob', password('mypass'), 'Y', 'Y', 'Y','Y')
INSERT INTO user (Host, User, Password, Select_priv)
VALUES ('athens.imaginary.com', 'jane', '', 'Y')
INSERT INTO user(Host, User, Password)
VALUES ('%', 'nobody', '')
INSERT INTO user (Host, User, Password, Select_priv,
Insert_priv, Update_priv, Delete_priv)
VALUES ('athens.imaginary.com', 'nobody',
password('thispass'), 'Y', 'Y', 'Y', 'Y')NOTE
MySQL user names are mostly unrelated to operating system user names. By default, the MySQL client tools use your operating system user name in attempting a login. There is, however, no necessary connection between the two. By using the -u option with most of the MySQL client utilities, you can connect to MySQL using any user name you like. Similarly, your operating system user name will not appear in the MySQL user table unless someone specifically adds it and grants you permissions.
The first user we created, "bob," can come from any host and can SELECT, INSERT, UPDATE, and DELETE records. The second user, "jane," can connect from "athens.imaginary.com." has no password, and can only execute SELECT statements. The third user is "nobody" from any host. This user is able to do absolutely nothing. The final user is "nobody" from "athens.imaginary.com" and can SELECT, INSERT, UPDATE, and DELETE records like "bob."
So how does MySQL do matching? Perhaps you noticed above that a given name could actually match several records. For example, "[email protected]" matches "nobody@%" and "[email protected]." Before checking the user table, MySQL sorts the data in the following manner:
MySQL first matches hosts that do not contain wildcards followed by hosts with wildcards. Empty Host fields are treated like they contain "%."
When hosts are the same, users without wildcards are checked before users with wildcards. As with Host, an empty User field is treated as if it contains "%."
The first match encountered is the only match considered.
In the earlier example, the user would be verified against "nobody" from "athens.imaginary.com" because "athens.imaginary.com" is sorted before "%." Because hosts are sorted before users, the values of any host from which you are connecting will take precedence over any specific privileges you might have. For example, if the user table contains the following entries:
|
Host |
User |
|---|---|
|
% |
jane |
|
athens.imaginary.com |
If "jane" connects from "athens.imaginary.com," the privileges associated with "athens.imaginary.com" are the privileges that MySQL will use.
You may have noticed that the user table makes no mention of specific databases or tables. The user table rules over the entire server. Most servers, however, have multiple databases. Different databases generally serve different purposes, and thus different user groups. The db table sets permissions for individual databases. The db table shown in Table 4-1 has the following structure:
This table looks a lot like the user table. The major distinction is that instead of having a Password column, this table has a Db column. This table manages a user's privileges within a specific database. Because user table permissions span the entire server, any activity granted to a user by the user table overrides that user's entry in the db table. Thus, if a user has INSERT access in the user table, that user will have INSERT access for all databases no matter what the db table says.
|
Field |
Type |
Null |
Key |
Default |
Extra |
|---|---|---|---|---|---|
|
Host |
char(60) |
PRI |
|||
|
Db |
char(32) |
PRI |
|||
|
User |
char(16) |
PRI |
|||
|
Select_priv |
enum(`N',`Y') |
N |
|||
|
Insert_priv |
enum(`N',`Y') |
N |
|||
|
Update_priv |
enum(`N',`Y') |
N |
|||
|
Delete_priv |
enum(`N',`Y') |
N |
|||
|
Create_priv |
enum(`N',`Y') |
N |
|||
|
Drop_priv |
enum(`N',`Y') |
N |
|||
|
References_priv |
enum(`N',`Y') |
N |
|||
|
Index_priv |
enum(`N',`Y') |
N |
|||
|
Alter_priv |
enum(`N',`Y') |
N |
This table looks a lot like the user table. The major distinction is that instead of having a Password column, this table has a Db column. This table manages a user's privileges within a specific database. Because user table permissions span the entire server, any activity granted to a user by the user table overrides that user's entry in the db table. Thus, if a user has INSERT access in the user table, that user will have INSERT access for all databases no matter what the db table says.
The most effective use of the user table is to create entries for each user in the user table with no permissions. This scheme enables a user to connect to the server, but do nothing else. The exception would be anyone who should be a server administrator. Everyone else should gain their permissions from the db table. Every user does have to appear in the user table, or they will not be allowed to connect to the database.
The same rules for user and host matching on the User and Host columns from the user table applies to this table -- with a bit of a twist. A blank Host field will prompt MySQL to look for an entry matching the user's host in the host table. If no such match is found, MySQL denies the operation. If an entry is found, MySQL calculates the permission as the intersection of those found in the db and host entries. In other words, both entries must have a `Y' in them or access is denied.
The host table serves a special purpose. The host table shown in Table 4-1 has the following structure:
|
Field |
Type |
Null |
Key |
Default |
Extra |
|---|---|---|---|---|---|
|
Host |
char(60) |
PRI |
|||
|
Db |
char(32) |
PRI |
|||
|
Select_priv |
enum(`N',`Y') |
N |
|||
|
Insert_priv |
enum(`N',`Y') |
N |
|||
|
Update_priv |
enum(`N',`Y') |
N |
|||
|
Delete_priv |
enum(`N',`Y') |
N |
|||
|
Create_priv |
enum(`N',`Y') |
N |
|||
|
Drop_priv |
enum(`N',`Y') |
N |
|||
|
Grant_priv |
enum(`N',`Y') |
N |
|||
|
References_priv |
enum(`N',`Y') |
N |
|||
|
Index_priv |
enum(`N',`Y') |
N |
|||
|
Alter_priv |
enum(`N',`Y') |
N |
The host table gives you a way of creating basic permissions on a host-by-host basis. When MySQL attempts to verify an operation, it seeks a match on the user name and host in the db table. If it finds a match on the user name with an empty Host field, it will consult the host table and use the intersection of the two sets of privileges to determine the outcome. For example, you may have a set of servers that you consider less secure than the rest of your network. You can deny them any kind of write access. If "bob" comes in from one of those machines and his entry in the db table has a blank Host field, he will be denied write access even though his db table entry would otherwise allow it.
These two tables are basically refinements of what the db table provides. Specifically, any operation is checked with the relevant db entry, followed by any relevant tables_priv entry, followed by any relevant columns_priv entry. If one of these is allowed, then the operation is allowed. With these two tables, you can narrow permissions down to the table and column levels. You can manipulate the contents of the tables through the GRANT and REVOKE commands in SQL.
You have had a look at the players in MySQL security. Now we need to put the players together and demonstrate how they are applied to real situations. MySQL divides access control into two stages. The first stage is connection. You must be able to connect to the server before you can do anything else.
Connection involves two checks. First, MySQL checks to see that the user name and host under which you are connecting has a corresponding entry in the user table. Matching an entry for you is based on the rules for matching we discussed earlier. If MySQL fails to find a match, your connection request is denied. If it finds a match and that match has a nonblank Password field entry, you must specify and match that password. Failure to match the password results in the denial of your connection request.
Once connected, the MySQL server enters the request verification stage. At this point, any specific requests you make are matched against your privileges. MySQL may take these privileges from any of the user, db, host, tables_priv, or columns_priv tables. If there is a match with the user table and the user table has a positive permission, then the operation is immediately allowed. Otherwise, MySQL looks for matches in the following tables in the following order:
db
tables_priv
columns_priv
If the db table has a positive entry, the operation is allowed and no further checking occurs. If the entry is negative, then MySQL checks with all matching tables_priv entries. If, for example, the operation is a SELECT that joins two tables, then the user must have positive entries for both tables in that database in the tables_priv table. If one or more of the entries is negative or nonexistent, then MySQL will perform the same logic for all of the columns in the columns_priv table.
You may find learning the MySQL security system confusing at first. To simplify matters a bit, MySQL comes with a utility called mysqlaccess. This command is a Perl script[4] that will take the host, username, and database combination and provide you with the exact access rights for that user and why. For example, using the command mysqlaccess nobody isp.com mydata might report the following output:
[4]For some reason, MySQL has shipped some distributions with the mysqlaccess script pointing to a nonstandard location for Perl binaries. If you get the response "command not found" when trying to execute mysqlaccess, you are almost certainly experiencing that problem. You will need to change line 1 of the mysqlaccess script so that it points to the proper location of your Perl binary, generally /usr/local/bin/perl.
Access-rights
for USER 'nobody', from HOST 'isp.com', to DB 'mydata'
+-----------------+---+ +-----------------+---+
| Select_priv | Y | | Drop_priv | N |
| Insert_priv | Y | | Reload_priv | N |
| Update_priv | Y | | Shutdown_priv | N |
| Delete_priv | Y | | Process_priv | N |
| Create_priv | N | | File_priv | N |
+-----------------+---+ +-----------------+---+
BEWARE: Everybody can access your DB as user 'nobody'
: from host 'isp.com' WITHOUT supplying a password.
: Be very careful about it!!
The following rules are used:
db : 'isp.com','mydata','nobody','Y','Y','Y','Y','N',
'N','N','N','N','N'
host : 'Not processed: host-field is not empty in db-
table.'
user : '%','nobody','','N','N','N','N','N','N','N','N','N'As you can see, even if you understand MySQL security fully, mysqlaccess can be a valuable tool in auditing your server's security.
MySQL loads the access tables at server startup. The advantage of this approach over constant lookups is speed. The downside, however, is that changes you make to the MySQL access tables are not immediately visible. In order to make those changes visible, you need to issue the command mysqladmin reload. If you change the tables through GRANT or REVOKE commands in SQL, you do not have to explicitly reload the tables.
|  | |
| 4.3. Running MySQL |  | 4.5. MySQL Utilities |
Copyright © 2001 O'Reilly & Associates. All rights reserved.