The database directory is where the MySQL database server stores data files, including not only files about tables, but also data files and MySQL server option files.The default location of the database directory is different for different distributions.

Location of data directory

Default database location

The location of the default database is compiled in the server.

If you are installing MySQL in a source distribution, the typical default location may be/usr/local/var;

◆If MySQL is installed in the binary distribution, it is/usr/local/mysql/data;

◆Install in RPM file,/var/lib/mysql.

◆For distribution on the windwos platform, its location is BASEDIRdata

The location of the data directory can be specified explicitly with--datadir=/path/to/dir when starting the server.This option is useful if you want to place the data directory somewhere other than the default location.

Learn the location of the database directory

As a MySQL administrator, you should know where the data directory is.If you run multiple servers, then you should know the location of all data directories.However, if you don't know where the directory is (perhaps you're taking the place of the previous administrator and he left a terrible record), there are a few ways to find it:

1.You can use the mysqladmin variable to get the data directory path name directly from the server.On UNIX, the output looks like this:

$mysqladmin variables

+-------------------------+---------------------------------------

| Variable_name | Value

+-------------------------+---------------------------------------

| ansi_mode | OFF

| back_log | 50

| basedir |/var/local

| connect_timeout | 5

| concurrent_insert | ON

| datadir |/usr/local/var

This output indicates the location of the data directory on the server host/usr/local/var.

On Windows, the output will look like this:

c:mysqlbin>mysqladmin variables

+-------------------------+---------------------------------------

| Variable_name | Value

+-------------------------+---------------------------------------

| ansi_mode | OFF

| back_log | 50

| basedir | c:mysql

| connect_timeout | 5

| concurrent_insert | ON

If multiple servers are running, they will listen on different TCP/IP port numbers and sockets.You can connect to the port and socket each server listens on by supplying the appropriate--port or--socket options:

$mysqladmin –port=port=port_num variables

$mysqladmin –socket=/path/to/socket variables

mysqladmin commands can be run on any host from which you connect to the server.If you need to connect to a server on a remote host, use the--host=host_name option:

$mysqladmin –host=host_name varibles

2.On Unix platforms, you can use ps to view the command line of any currently executing mysql process.Try the following commands (depending on the version of ps your system supports) and look for the--datadir of these commands shown in the output:

$ps au | grep mysqld

The ps command is especially useful if the system is running multiple servers (because multiple data directory locations are discovered at once).Its downsides are: ps must be running on the server's host, and unless the--datadir option is explicitly specified on the mysqld command line, it will produce useless information.

3.If MySQL is installed from the source distribution package, you can check its configuration information to determine the location of the data directory.For example, in the top-level Makefile this location is available.However, be careful: the location is the value of the variable localstatedir in the Makefile, not the value of datadir.Likewise, if the distribution is located in an NFS mounted filesystem and is being used to build MySQL for several hosts, the configuration information reflects the host on which the distribution was most recently built.It may not show the data directory for the host you are interested in.

4.If any of the previous methods are unsuccessful, use find to search for database files.The following command will search for the.frm (description) file, which is part of the MySQL installer:

$find/-name “*.frm” –print

Searching on the Windows platform is so simple that no examples are given in this section.

In these examples in this chapter, I denote the location of the MySQL data directory as DATADIR.You can interpret this as the location of the data directory in your own machine.

Database representation

Each database managed by MySQL has its own database directory, which is a subdirectory of the data directory, with the same name as the database represented.For example, the database my_db corresponds to the database directory DATADIR/my_db.

This notation makes the implementation of several database-level statements very easy.CREATE DATABASE db_name Creates an empty directory db_name in the data directory with an ownership and mode that allows access only to the MySQL server user (the UNIX user the server runs as).This is equivalent to manually creating the database by executing the following command as the server user on the server host:

$ mkdir DATADIR/db_name create database directory

$ chmod 700 DATADIR/db_name to make it accessible only to MySQL server users

The method of representing a new database by an empty directory is quite different from other database systems, which even create many control files or system files for "empty" databases.

The

DROP DATABASE statement is also easy to implement.DROP DATABASE db_name Drops the db_name directory in the data directory and all table files in it.This statement is similar to the following command:

$rm-rf DATADIR/db_name

The difference is that the server only deletes files with the extension of the table.If other files have been created in the database directory, the server will keep them intact and not delete the directory itself.

SHOW DATABASE is nothing but a list of names corresponding to subdirectories located in the data directory.Some database systems need to keep a master table listing all databases that need to be maintained, however, in MySQL there is no such structure.Due to the simplicity of the data directory structure, the list of databases is implicit in the contents of that data directory, and tables like the master table can cause unnecessary overhead.

Representation of database table

Each table in the database exists as three files in the database directory: a format (description) file, a data file, and an index file.The base name of each file is the table name, and the extension indicates the type of file.The extensions are shown in Table 5-1.The extension of the data and index files indicates whether the table uses an older ISAM index or a newer MyISAM index.

When issuing a CREATE TABLE tbl_name statement that defines a table structure, the server creates the tbl_name.frm file, which contains the internal encoding for that structure.The statement also creates empty data files and index files whose initial information indicates that there are no records and no indexes (if the CREATE TABLE statement contains index specifications, the index file will reflect those indexes).The ownership and manner of the files describing the tables are set to allow access only to the MySQL server user.

When an ALTER TABLE statement is issued, the server recodes tbl_name.frm and modifies the contents of the data and index files to reflect the structural changes indicated by the statement.The same is true for CREATE and DROP INDEX, as the server considers them equivalent to ALTER TABLE statements.DROP TABLE drops the three files that represent the table.

Although a table can be dropped by dropping the three files in the database directory that correspond to the table, tables cannot be created or altered manually.For example, if my_db is the current database, DROP TABLE my_tbl is roughly equivalent to the following command:

The output from SHOW TABLES my_db is a listing of the base names of the.frm files in the my_db database directory.Some database systems maintain a registry that lists all the tables in the database.But MySQL doesn't do this because it's not necessary, this "registration" is implicit in the structure of the data directory.

MySQL Status File

In addition to the database directory, the MySQL data directory contains a number of state files.Table 10-3 provides an overview of these files.The default name for most state files is derived from the server hostname, represented in this table as HOSTNAME.

The server writes its process ID (PID) to a PID file on startup and deletes the file on shutdown.A PID file is a method by which other processes can find the server.For example, if you run the mysql.server script to shut down the MySQL server at system shutdown, the script will examine the PID file to determine which process it needs to send a kill signal.

The error log produced by safe_mysqld as a redirection of the server's standard error output contains all messages written to stderr by the server.This means that the error log only exists when the server is started by calling safe_mysqld (in any case, this is the preferred way to start the server, since safe_mysqld will restart the server if the error log exists due to an error).

The regular log and update log are optional, and the required log type can be enabled with the--log and--log-update server options.

The regular process provides general information about the operation of the server: who connected from where, and what queries they issued.The changelog also provides query information, but only query information for modified database contents.The contents of the changelog are SQL statements that can be run by entering them into the mysql client program.The changelog is useful if there is a crash and you have to go to a backup file, because you can repeat the updates that have been done since the crash by entering the changelog to the server.This will bring the database back to the state it was in when the crash occurred.

The following is an example that appears in messages in the regular log as a result of a short client session that replicates a table in the test database from mytest.pet and inserts a row into that table, Then delete the table:

Note that the second line is an incorrect statement, but it is also logged.

The general log contains date and time, server thread ID, event type, and specific event informationcolumn of interest.

The same conversation appears in the following changelog:

use test;

create table mytest select * from mytest.pet;

insert into mytest set name='tom',owner='jerry',species='cat',sex='f',birth='2000-01-01';

drop table mytest;

There are no erroneous statements recorded in the update log, so it makes sense to recover corrupted database contents.

For changelogs, the extended format of the log is available, even with the--log-long-format option.Extended logs provide information about who issued the query and when.Of course, this will use more disk space, but if you want to know who is doing what without correlating the contents of the changelog with the connection events in the regular log, the extended log might be useful.

It is a good idea to ensure that log files are safe and not arbitrarily read by users.Both the regular log and the update log contain sensitive information such as passwords because they contain the text of the query.Here is the log entry that you don't want anyone to read because it shows the root user's password:

010206 23:30:02 4 Query update mysql.user set password=password("peking77.") where User="root"

For information on checking permissions for the settable data directory, a short instruction for data directory security consists of the following commands:

$ chmod 700 DATADIR

Run this command as the UNIX user who owns the data directory.Also make sure the server is running as that user, otherwise this command will not only keep other users out of that data directory (what you want), it will also prevent the server from accessing your database (what you don't want).

Status files appear at the top level of the data directory, just like the database directory, so you might wonder if the names of those files get confused with each other or be mistaken for the database name (for example, when the server is executing a SHOW DATABASE statement).The answer is: no.Status and log information are stored in files, and databases are directories, so executable programs can distinguish them from a simple stat() call (the server tells them how).If you are monitoring the data directory, you can differentiate the status file from the database directory by using ls-l and check the first character of the mode information to see if it is a '-' or a 'd':

You can also tell this simply by looking at the name: all state file names contain a period, but database directory names do not (a period is not a legal character for a database name).

Summary

Through this section, the reader can have a certain understanding of the way of data storage in MySQL.The more important things in this section are:

1.The location of the database directory

2.How does MySQL represent data tables

3.Types and functions of MySQL state files

Understanding how MySQL saves data and the role of state files is of great significance for backing up databases.According to the characteristics of MySQL database directories, direct copying is one of the important methods for backing up data.