First, MySQL's data directory
1. Determine the location of the mysql data directory
· The default location of the mysql data directory is usually
/ usr / local / mysql / var (source installation)
Or / var / lib / mysql (RPM mode installation)
Or / usr / local / mysql / data (binary release version), and Windows default data directory is C: / mysql / data
· Query the location of the current data directory
# Mysqladmin -u root -p variables
The display is as follows:
......
| DATADIR | / USR / local / mysql / var
......
Enter the MySQL program inquiry.
Mysql> show variables like 'datadir';
If you run multiple servers, use the -Port parameter difference, use the following parameters:
# Mysqladmin --host = 127.0.0.1 --port =
Variables
If you are a source installation, you can view the Makefile file, find the localstatedir variable, and find the DataDir.
The last most bt command:
# Find / -name "* .frm" -print
Haha
2. Structure of the data directory
· Each database corresponds to a directory in the mysql data directory.
• Data tables in the same database correspond to the relevant files in the database directory. However, there is only an InnoDB data sheet. (Starting from 3.23.34a, it is activated in the binary version of MySQL -MAX. InnoDB provides higher performance. I haven't studied it. If I am interested, I can go to Google.com to check, there are a lot of related data of)
· The MYSQL server configuration file my.cnf.
· MySQL server process ID (PID) file, which saves the ID of the current MySQL process, of course, there is no file in the Windows system.
· The status and log files generated by mySQL server.
· DES key file and server SSL certificate (this is not specifically used).
3.Mysqld
Various client procedures do not operate directly, they must access data in the database through the server (MySQLD), which is the only passage of its unique use of data;
When starting, the MySQLD server will open some log files according to the requirements of the command line (or profile), and then provide a network interface to the mysql data directory and start listening to a variety of network connections on this interface.
The client must first establish a connection to the server, then issue various SQL query commands to let the server execute, and then return the result.
The MySQL server is multi-threaded, can serve simultaneously with multiple customers, but it can only perform a modification operation at a time, so the actual effect is that the request will be "serial", so two customers will never be in the same The time to modify a given record and conflicts.
The following two occasions are unable to control the mysql data directory excluence:
a. When running multiple MySQL servers on the same data directory.
b. When running a variety of data refusion tools.
Try to use the tools that use the data table to fix the tool when running at the MySQL server. .
4. File system
· Each database is actually a subdirectories in the mysql data directory, and the name is the same as the database name represented.
· MySQL supports the following processing programs for several different data tables:
A.isam
The most original type, each ISAM table represents the same name, the file name, and the data table name, but use different suffix names, represents it.
* .frm ---- Table definition file, store form structure definition.
* .ISD ---- Data file, save the content of each data line. * .Ism ---- index file, all index information of the stored table.
B.MYISAM
MySQL 3.23 begins to introduce the MyISAM type for an alternative to the ISAM type.
* .frm ---- Table definition file, store form structure definition.
* .Myd ---- Data file, save the contents of each data line.
* .Myi ---- Index file, all index information of the stored table.
C.MERGE
It is a logical structure that represents a collection of Myisam tables as a set of structures. Representing a .frm file and a .mrg file, .mrg file is a list of tables, can be directly modified (the flushtables command first refreshes the data table buffer).
D.BDB
Use two files to represent a data table, a table structure .frm, one data .db.
E.innoDB
There is only one file, which is physically similar to the tablespace of Oracle.
f.Heap
Only .frm files, the data table is created in memory.
5. Database and table naming rules
· The current character set letter and data are previously underlined and the dollar symbol.
· The maximum number of names is 64 characters.
· Mysql 3.23.6 start, you can use any characters and reserved words that can be caused by reverse number. (Such as `odd @ name`)
· To meet the current system's file naming rules.
· When you create a data sheet for disk files and query references a data sheet, the MySQL server will transfer their name to lowercase.
6. Status files and log files
-------------------------------------------------- ----------------------------------
File type default name file content
-------------------------------------------------- ----------------------------------
Process ID file Hostname.pid mysql server process ID
General query logs hostname.log connection / disconnecting events and query information
Slow Query Log Hostname-Slow.log spends a long query command for the text
Change the log hostname.nnn Create / change the table structure or modify the query command text of the table content
Binary change log hostname-bin.nnn is as follows, which is binary representation.
The index of binary change logs hostname-bin.index binary variable log file list
Error Log Hostname.err "Start / Off" event and exception
-------------------------------------------------- ----------------------------------
7. Resettlement data directory
Two ways, one is using - DataDir =
To start the server, implement it. The other is to create a symbolic link. If it is not a newly installed MySQL server, it is recommended that the second method is recommended because the first method may cause other programs to find a relative path, which is easy to generate errors.
The basic steps are as follows:
1) SHUTDOW MYSQL server.
2) Put the database (or DATADIR) directory COPY to a new location.
3) Rename the original database (or DATADIR)
4) Creating a symbol link in the original location to a new location.
5) Restart the MySQL server.
6) After normal, delete the original database (or DATADIR).
Here is an example of moving / usr / local / mysql / var / mal / mysql_db: # mysqladmin -u root -p shutdown
# CD / usr / local / mysql
# TAR CF - VAR | (CD / var / mysql_db; tar xf -) / / It is best not to use a CP -R to copy file directly, there will be permission errors.
# Mv var var.bak
# Ln -s / var / mysql_db.
# / Usr / local / mysql / bin / safe_mysqld --user = mysql & // Start MySQL, can be modified according to your server situation
If everything is normal, you can delete the original:
RM-RF VAR
