MySQL Administrator Guide
2001-06 Yu Feng
Mysql database introduction
MySQL is a real multi-user, multi-threaded SQL database server. SQL (Structured Query Language) is the most popular and standard in the world
Database language. MySQL is an implementation of a client / server structure, which is made by a server daemon mysqld and a lot of different
Composition of client programs and libraries.
SQL is a standardized language that makes it easier to store, update, and access information. For example, you can retrieve a website with SQL language
Product information and storage customer information, while MySQL is also fast enough to allow you to store record files and images.
The main goal of mysql is fast, robust and easy to use. Originally because we need such a SQL server, it can handle it with any
Provide a large database of a database on a quantitude hardware platform, but the speed is faster, and MySQL is developed. Since 1996
Come, we have been using MySQL, and its environment has more than 40 databases, including 10,000 tables, 500 tables more than 7 million rows.
This is about 100 guitar articles (GB) key application data.
MySQL database features
1. Use the complete multi-thread of the core thread. This means that it can easily use multiple CPUs (if any).
2. Can run on a different platform.
3. A variety of column types: 1, 2, 3, 4, and 8 bytes of symbol / unsigned integers (int), float, double, char,
VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, and ENUM types.
4. Using an optimized scanning multi-Sweep Multi-join, Join.
5. Supports all operators and functions in the SELECT and WHERE section of the query.
6. Implement SQL function libraries through a highly optimized class library and as fast as they can reach, usually should not have anything after the query is initialized.
What memory is allocated.
7. Fully support SQL's Group By and Order By clauses, support polymerization functions (count (), count (distance), avg (), std (),
SUM (), max () and min ()).
8. Support ANSI SQL Left Outer Join and ODBC syntax.
9. You can mix the table from different databases in the same query.
10. A very flexible and secure permissions and password system, and it allows host-based authentication. The password is safe, because when it is with one service
All password transfer is encrypted when connecting.
11. ODBC for WINDIWS 95.
12. Equipped with a fast B tree magnetic disk table.
13. Each table allows 16 indexes. Each index can consist of 1 to 16 columns or columns. The maximum index length is 256 bytes (in
When compiling mysql, it can change). A index can use a prefix of a char or varchar field.
14. Delivery and becoming long records.
15. Used as a list of internal memory of a temporary table.
16. Great database processing. We are using MySQL for some databases that contain 50,000,000 records.
17. All columns have default, you can insert a subset of table columns with INSERT, those who do not use clear gives values to their default
value.
18. A very fast thread-based memory allocation system.
19. There is no memory vulnerability. Tested (Purify) with a commercial memory vulnerability monitoring program.
20. Includes a faster utility of Myisamchk, an inspection, optimization, and fixing database tables.
21. Fully support ISO-8859-1 Latin1 character sets.
22. All data is saved in ISO-8859-1 Latin1 format. All normal strings are ignored.
23. DELETE, INSERT, REPLACE and UPDATE Return how many rows are changed (affected). 24. The function name will not conflict with the table or column name. For example, ABS is a valid column name. The only restriction on the function call is the function name and the subsequent
"(" You can't have spaces.
25. All MySQL programs can be used with option --help or -?
26. The server can provide customers with error messages in multiple languages.
27. The client uses TCP / IP connection or UNIX socket (socket) or NT's named pipe connected to MySQL.
28. MySQL unique show commands can be used to retrieve information about databases, tables, and indexes, which can be used to determine how the optimizer solves one
Query.
The startup and stop of the mysql server
First, the method of starting the server
Start the server by three main methods:
1, call mysqld directly.
#. / mysqld &
This may be the least common method, it is recommended not to use more.
2, call the Safe_Mysqld script, the best way.
#. / saffer = 128m -o key_buffer = 128m -o record_buffer = 256m -o sort_buffer = 128m -o table_cache = 2048 -o TMP_TABLE_SIZE = 16m -o max_connections = 2048 &
3, call the mysql.server script.
The SAFE_MYSQLD script is installed in the bin directory of the MySQL installation directory, or can be found in the scripts directory that is distributed in the mysql source code.
MySQL.Server scripts are installed in the Share / MySQLD directory in the mysql installation directory or can be distributed to the mysql source code for Support_Files
Located under the catalog. If you want to use them, you need to copy them to the appropriate directory under mysql / bin.
#. / mysql.server start
SUN Solariys Method for Automatic Mysql
Write a batch file web (under the path /etc/init.d), the content is as follows:
#! / bin / sh
Opt _ = $ 1
Case "$ OPT_" in
START)
/ bin / echo "$ 0: (start)
#
# Your Service Startup Command Goes Here.
#
/ usr / local / apache / bin / apachectl start
/ home3 / mysql / bin / safe_mysqld -o join_buffer = 128m -o key_buffer = 128m -o record_buffer = 256m -o sort_buffer = 128m -o Table_cache = 2048 -o TMP_TABLE_SIZE = 16m -o max_connections = 2048 &
# NOTE: MUST EXIT with ZERO UnsS ERROR IS Severe.
EXIT 0
;
STOP)
/ bin / echo "$ 0: (stop)"
#
# Your Service Shutdown Command Goes Here.
#
/ usr / local / apache / bin / apachectl stop
# NOTE: MUST EXIT with ZERO UnsS ERROR IS Severe.
EXIT 0
;
*) / bin / echo ''
/ bin / echo "USAGE: $ 0 [start | stop]"
/ bin / echo "invalid argument ==> /" $ {opt _} / "/ bin / echo '"
EXIT 0
;
ESAC
Confirm this file has an executable right
#CHMOD 500 Web
#CD /ETC/RC2.D
#LN -s ../init.d/web s99mysql
When the system is started, the S99MYSQL script will automatically call with a START parameter. Pay attention to the header must be capitalized.
Second, the method of stopping the server
1. To manually stop the server, use mysqladmin:
#mysqladmin -u User Name -P 'Password' Shutdown
2, call the mysql.server script, the best way.
#. / mysql.server stop
3, kill the OS directly
#kill -9 process number
This may be the least common method, it is recommended not to use more.
To automatically stop the server, you don't need special things. Just add another closing program.
#CD /etc/rc0.d
#LN -s ../init.d/web k01mysql
When the system is started, the K01MYSQL script will automatically call with a STOP parameter.
MySQL directory structure and common command
First, the location of the data directory
This is the default mysql directory structure
Bin Info Libexec Share Var
INCLUDE LIB MAN SQL-BENCH
A default data directory is compiled into the server if you distribute mysql from a source code, typically default directory
/ usr / local / var, if you installed from the RPM file for / var / lib / mysql, if you are installed from a binary distribution
/ usr / local / mysql / data.
As a mysql administrator, you should know where your data directory is. If you run multiple servers, you should be to all data directories
Where is it, but if you don't know the exact location, find it from a variety of ways:
1. Use MySQLADMIN VARIABLES to get the data directory path name directly from your server. Find the value of the DATADIR variable, on UNIX, its output is similar to:
% mysqladmin -u username -p '*****' Variables
--------------------------------------------
| variable_name | Value |
--------------------------------------------
| Back_LOG | 5 |
| Connect_timeout | 5 |
| Basedir | / VAR / LOCAL / |
| DATADIR | / USR / local / var / |
....
2, find the path to mysql running
% PS-EF | GREP MySQLD
Second, the data directory structure
Each database corresponds to a directory in the data directory.
The table in a database corresponds to the file in the data directory.
The data directory also contains several status files generated by the server, such as log files. These files provide important information about server operations.
Very valuable to manage the problem in trial of the problem.
1. Database table representation
There are three files in the database directory: a style (description file), a data file, and an index file. The basic name of each file is the table name, the file name extension represents the file type. The extension is as follows. The extension of the data and index files indicates that the table uses old IASM indexes or new
MyISAM index.
MySQL file type
File type file name extension file content
Style file. FRM Description Table structure (its column, column type, index, etc.)
Data file .ISD (isam) or .myd (MyISAM) contains all the data in the table
Index file .ism (isam) or .myi (MyISAM) contains index tree on all indexes on the data file
When you send a CREATE TABLE TBL_NAME, when the structure of the statement is defined, the server creates a file called TBL_NAME.FRM, it
Including internal encoding of this structure, and also creates an empty data and an index file, which is initialized to include information indicating that there is no record and no index (eg
If the CREATE TABLE statement includes an index designation, the index file reflects these indexes). The primary and mode corresponding to the files of the table are set to only
Allow mysql server users to access.
Let's introduce a common mysql command.
Enter the MySQL database
#. / mysql -u username -P 'password'
View all databases
mysql> show data;
Enter a specific database
MySQL> USE database name;
View Database All Table
mysql> show tables;
Rename table
MySQL> ALTER TABLE Name 1 Rename Table Name 2;
Example: mysql> ALTER TABLE DEPT RENAME DEPT2;
2. Construction of the index:
First set the fields to be associated to non-empty
MySQL> ALTER TABLE Table Name CHANGE Field Name Field Name Field Description NOT NULL;
example:
We create such a table
MySQL> CREATE TABLE EMPLOYEEEEEEEEEEEE
(ID INT (5) Not NULL,
DepNo Int (5),
Name varchar (20) Not null,
CardNumber Bigint (15) NOT NULL);
Mysql> Alter Table Employee Change DepNo DepNo Int (5) Not NULL;
Plus
MySQL> ALTER TABLE Name Add Index Index Name (Field Name 1 [, Field Name 2 ...]);
Example: mysql> ALTER TABLE Employee Add Index Emp_Name (Name);
Adding the index of the key
MySQL> ALTER TABLE Name Add Primary Key;
Example: mysql> ALTER TABLE Employee Add Primary Key (ID);
Add only the index of the limited conditions
MySQL> ALTER TABLE Table Name Add UNIQUE Index Name (Field Name);
Example: mysql> ALTER TABLE EMPLOYEE Add Unique Emp_name2 (CardNumber);
View the index of a table
MySQL> Show Index from the Name;
Example: mysql> show index from Employee;
Delete an index
mysql> ALTER TABLE Name DROP INDEX Index Name;
Example: mysql> ALTER TABLE EMPLOYEE DROP INDEX EMP_NAME
MySQL user management
MySQL administrator should know how to set the mysql user account, pointing which user can connect to the server, where to connect, what to do after the connection. MySQL 3.22.11 begins to introduce two statements to make this work easier: GRANT statement creates a mysql user and specify its permissions, and
Revoke statement deletes permissions. Two statements play a front-end role of the mysql database and provide another one to directly operate the contents of these tables.
Method. CREATE and REVOKE statements affect 4 tables:
Authorization form:
User can connect to the server users and any global permissions they have
DB database level permissions
TABLES_PRIV table-level permissions
COLUMNS_PRIV level permission
There is also the 5th authorization form (Host), but it is not affected by Grant and Revoke.
When you send a GRANT statement to a user, create a record for the user in the User table. If the statement specifies any global permissions (tube
Personal or permissions for all databases), which are also recorded in the User table. If you specify the database, table, and column level permissions, they are
Record in db, tables_priv, and columns_priv tables, respectively.
In the following chapter, we will show how to set the mysql user account and authorize. We also involve how to withdraw and remove users from the license list.
First, create users and authorize
The grammar of the GRANT statement looks like this:
Grant Privileges (Column)
On What
To User Identified by "Password"
With Grant Option
To use this statement, you need to fill out the following:
PRIVILEGES
Grant the user's permissions, the following table lists the permission specifiers that can be used for the GRANT statement:
Permissions Specifier Permissions Allowed:
ALTER modifies tables and indexes
CREATE Create a database and table
Delete deletes already records in the table
DROP Abandon (Delete) Database and Table
Index creates or discards the index
Insert inserts a new line into the table
Reference unused
SELECT search record
Update modified existing surveillance table record
File reads or writes files on the server
Process View thread information or kill threads executed in the server
Reload Overload Authorization Table or Empty Log, Host Cache, or Table Cache.
Shutdown Close Server
All; All privileges synonyms
USAGE special "no right" permission
Columns
The columns used by permissions, it is optional, and you can only set column-specific permissions. If the command is more than one column, you should separate them with a comma.
WHAT
Level of permissions. Permissions can be global (for all databases and all tables), specific databases (suitable for a database)
All tables) or specific tables. It can be permissions by specifying a columns sentence.
User
Users granted by permissions, which consists of a username and hostname. One user name in MySQL is the username specified when you connect to the server.
This name does not have to be in contact with your UNIX login or Windows name. By default, if you don't specify a name, the client will use
Your login name is a MySQL username. This is just a convention. You can change the name to Nobody in the license list, then connect with the Nobody connection
Perform operations that require superuser privileges.
Password
Give the user password, it is optional. If you don't specify the Identified by clause for the new user, the user does not assign a password (insecure).
For existing users, any password you specify will replace the old password. If you don't specify a password, the old password remains unchanged. When you use my Identified by, the password string is used to use the literal meaning of the password, and the Grant will coding the password for you, don't use Password () like you with set password.
function.
The with grant Option clause is optional. If you contain it, the user can grant permissions to authorize other users via the GRANT statement. you can
Use this clause to give the ability to authorize other users.
User name, password, database, and table name are case sensitive in authorization form records, host names and column names are not.
Example: Create a super user Test1
Mysql> grant all privileges on *. * to test1 @ localhost identified by '123456' with grant option;
Create a user who can only query Test2
Mysql> grant select on *. * to test2 @ localhost identified by '9876543';
Second, withdrawal and delete users
To cancel a user's permissions, use the revoke statement. Revoke's syntax is very similar to the GRANT statement, except for TO to replace it with from
INDETIFED BY and WITH GRANT OPTION clause:
Revoke Privies (Column) on What from User
The User section must match the User section of the user you want to withdrawn by the original GRANT statement. The Privileges section does not need to match, you can use GRANT
Statement authorization, then use the revoke statement to revoke some permissions. The REVOKE statement only deletes permissions without deleting users. Even if you revoke all
Permissions, user records in the User table remain, means that users can still connect to the server. To completely delete a user, you must
Use a DELETE statement to delete user records from the USER table:
#mysql -u root mysql
MySQL> Delete from User
-> where user = "user_name" and host = "host_name";
mysql> flush privileges;
DELETE statement deletes user records, and the flush statement tells the server overload authorization form. (When you use the GRANT and REVOKE statements, the table is automatically
Overload, and you don't have a licensed form directly. )
Example: Delete Users Test1
MySQL> Revoke all on *. * from test2 @ localhost;
Mysql> USE Mysql;
Mysql> delete from user where user = 'test' and host = 'localhost';
mysql> flush privileges;
MySQL database backup
Backing up your database is important in situations where database tables are lost or corrupted. I have known that the table is destroyed, editing such as VI or Emacs.
Attempt to edit them directly, this pair is definitely not a good thing!
Two main methods of backup databases are to copy database files (such as CP, CPIO or TAR, etc.) with mysqldump programs. Each method
Have advantages:
MySQLDUMP works with the MySQL server. Direct copying methods are performed outside the server, and you must take measures to ensure that no customers are
In the modified table that will be copied, it is generally done in the database shutdown. MySQLDUMP is slower than the direct copy. MySQLDUMP generates text files that can be ported to other machines, even those machines with different hardware structures. Direct copy files can be ported to the same machine, but cannot
Migrate to other machines unless you are copying the table using the Myisam storage format.
First, use MySQLDUMP backup and copy database
When you use the mysqldump program to generate a database backup file, the default, the file content contains create statements and packages that create poured tables.
Insert statement with a table. In other words, the output generated by mysqldump can be used as an input to Mysql in the future to rebuild the database.
The mysqldump parameter is as follows:
#mysqldump -u User Name -P 'Password' Database Name [Table Name]> The file name under the operating system
Example: #. / Mysqldump -u root -p'123456 'samp_db> Samp.db.txt
The beginning of the output file looks like this:
# Mysql dump 6.0
#
# Host: Localhost Database: SAMP_DB
# ---------------------------------------
# Server Version 3.23.2-Alpha-log
#
# TABLE STRUCTURE for TABLE 'Absence'
#
CREATE TABLE ABSENCE
Student_id int (10) unsigned default '0' NOT NULL,
Date Date Default '0000-00-00' NOT NULL,
PRIMARY key (student_id, date)
);
#
# DUMPING DATA for TABLE 'Absence'
#
INSERT INTO ABSENCE VALUES (3, '1999-09-03');
INSERT INTO ABSENCE VALUES (5, '1999-09-03');
INSERT INTO ABSENCE VALUES (10, '1999-09-08');
......
The remaining part of the file has more INSERT and CREATE TABLE statements.
Output a single table:
#mysqldump Samp_DB Student Score Event ABSENCE> Grapbook.sql
#mysqldump Samp_DB Member President> Hist-League.sql
By default, mysqldump reads the entire content of a table into memory before writing. This is often unnecessary, and actually if you have a big
Table, almost failed. You can use the --quick option to tell MysqldUMP to write every line as long as it retrieves a line. In order to further optimize
Inverted process, use -opt instead --quick. --opt options Open other options, accelerate data, and read them back.
Second, use direct copy database backup and copy method
Another way that does not involve MySQLDUMP backup databases and tables are directly copied database table files. Typically, this is used, such as CP, TAR or CPIO
Utility. The examples of this article use CP.
% CD DATADIR
% CP -R SAMP_DB / USR / Archive / MySQL
Single table can be backed up as follows:
% CD DATADIR / SAMP_DB
% CP Member. * / usr / archive / mysql / samp_db
% cp score. * / usr / archive / mysql / samp_db
....
When you complete your backup, you can restart the server (if you turn it off) or release the lock on the table (if you let the server run). To copy a database from a machine with a direct copy file to another machine, just copy the file to another server host.
When the data directory can be. To make sure the file is MyiaSM format or two machines have the same hardware structure, otherwise your database is in another host.
There is a strange content. You should also guarantee that the server on another machine does not access them when you are installing a database table.
Built a SH file Bakmysql.sh
#! / bin / sh
CD / usr / local / mysql /
TAR CVF /MOUNT2/Mysqlvar.tar Var
It can be set to the operating system automatically run.
Root user crontab file
/ var / spool / cron / crontabs / root Add the following
0 2 1 * * /mount2/bakmysql.sh
# / etc / rc2.d / s75cron stop
# / etc / rc2.d / s75cron start
Re-attack live Sun Solaris automatic processing process
Third, use backup recovery data
There are many reasons why database damage, and the degree is different. If you walk, you may only damage one or two tables (such as dropping) if you are unlucky
You may have to replace the entire data directory (such as disk corruption). In some cases, it also needs to be recovered, such as the user incorrectly deleted the database.
Or table. Regardless of these unlucky events, you will need to implement some recovery.
If the table is damaged but is not lost, try to fix them with Myisamchk or Isamchk, if such damage can be repaired.
1. Restore the entire database
The mysqldump parameter is as follows:
#mysqldump -u User Name -P 'Password' Database Name Example: Create another database in MySQL first Mysql> Create Database Test; Then import the backup data #MYSQLDUMP -U root -p'123456 'test2
