MySQL administrator Guide

Mysql database Introduction
MySQL is a real multi-user, multi-thread SQL database server. SQL (Structured Query Language) is the most popular and standard in the world.
Database language. MySQL is implemented in a Client/Server structure. It is implemented by a server daemon mysqld and many different
Customer program and library composition.

SQL is a standardized language that makes it easier to store, update, and access information. For example, you can use SQL to search for a website.
Product information and customer information are stored. MySQL is fast and flexible enough to allow you to store recorded files and images.

MySQL is designed to be fast, robust, and easy to use. It was initially because we needed such an SQL Server, which could handle anything
The database manufacturers on your hardware platform provide large databases of an order of magnitude, but the speed is faster, MySQL will be developed. Since January 1996
MySQL is used all the time. Its Environment has more than 40 databases, including 10,000 tables, of which more than 500 tables exceed 7 million rows.
About 100 key application data in GB.

Mysql database features
1. Use full multithreading of core threads. This means that it can easily use multiple CPUs (if any ).
2. It can run on different platforms.
3. Multiple column types: Signed/unsigned integer (INT), FLOAT, DOUBLE, CHAR,
VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, and ENUM types.
4. Use an optimized one-scan multi-join to quickly perform join operations ).
5. All operators and functions are supported in the SELECT and WHERE clauses of the query.
6. Use a highly optimized class library to implement the SQL function library and they can achieve the same speed. Generally, after the query initialization, there should not be any
Memory allocation.
7. Fully supports SQL GROUP BY and ORDER BY clauses, including Aggregate functions (COUNT (), COUNT (DISTINCT), AVG (), STD (),
SUM (), MAX (), and MIN ()).
8. Supports the left outer join and ODBC Syntax of ansi SQL.
9. You can mix tables from different databases in the same query.
10. A flexible and secure permission and password system that allows host-based authentication. The password is safe because
All password transfers are encrypted when the server is connected.
11. ODBC for Windiws 95.
12. Fast B-tree disk table with index compression.
13. Each table can have 16 indexes. Each index can be composed of 1 ~ Consists of 16 columns or a part of a column. The maximum index length is 256 bytes (in
It can be changed when MySQL is compiled ). An index can use the prefix of a CHAR or VARCHAR field.
14. Fixed Length and variable length records.
15. The memory hash used as a temporary table.
16. Big database processing. We are using MySQL for some databases that contain 50,000,000 records.
17. All columns have default values. You can use INSERT to INSERT a subset of a table column. columns that do not explicitly set values are set as their default values.
Value.
18. A very fast thread-based memory allocation system.
19. No memory vulnerability. Tested with a commercial memory vulnerability Monitor (purify ).
20. Including myisamchk, a quick utility for checking, optimizing, and repairing database tables.
21. Full support for ISO-8859-1 Latin1 character set.
22. All data is saved in ISO-8859-1 Latin1 format. All normal strings are case-insensitive.
23. The number of rows returned by DELETE, INSERT, REPLACE, and UPDATE is changed (affected ).
24. The function name does not conflict with the table or column name. For example, ABS is a valid column name. The only restriction on function calling is the function name and subsequent
"(" Cannot contain spaces.
25. All MySQL programs can use the options -- help or -? Obtain online help.
26. The server can provide customers with error messages in multiple languages.
27. The client uses a TCP/IP connection or a named pipe under Unix socket (socket) or NT to connect to MySQL.
28. The SHOW command exclusive to MySQL can be used to retrieve information about databases, tables, and indexes. The EXPLAIN command can be used to determine how the optimizer solves the first problem.
Queries.

Start and Stop a MySQL Server

I. How to start the server
There are three main methods to start a server:

1. Call mysqld directly.
#./Mysqld &
This may be the least commonly used method. We recommend that you do not use it more.

2. Call the safe_mysqld script in the best way.
#. /Safe_mysqld-O join_buffer = 128 M-O key_buffer = 128 M-O record_buffer = 256 M-O sort_buffer = 128 M-O table_cache = 2048-O tmp_table_size = 16 M-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 of the MySQL source code distribution.
The mysql. server script is installed in the share/mysqld directory under the MySQL installation directory or support_files that can be distributed in the MySQL source code.
Directory. If you want to use them, you need to copy them to the appropriate directory mysql/bin.
#./Mysql. server start

How does Sun Solariys automatically enable mysql upon startup?
Write a start and close batch processing 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 = 128 M-O key_buffer = 128 M-O record_buffer = 256 M-O sort_buffer = 128 M-O table_cache = 2048-O tmp_table_size = 16 M-O max_connections = 2048 &

# NOTE: Must exit with zero unless 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 unless error is severe.
Exit 0
;;
*)/Bin/echo
/Bin/echo "Usage: $0 [start | stop]"
/Bin/echo "Invalid argument =>" $ {OPT _}""
/Bin/echo
Exit 0
;;
Esac
Confirm that this file has the right to execute
# Chmod 500 web
# Cd/etc/rc2.d
# Ln-s ../init. d/web S99mysql
When the system starts, the S99mysql script is automatically called with a start parameter. Note that the first letter must be in uppercase.

Ii. How to stop a server
1. to manually stop the server, use mysqladmin:
# Mysqladmin-u username-p password shutdown
2. Call the mysql. server script in the best way.
#./Mysql. server stop
3. directly kill the OS process number
# Kill-9 process number
This may be the least commonly used method. We recommend that you do not use it more.

To automatically stop the server, you do not need to do anything special. You only need to add another program to close the program.
# Cd/etc/rc0.d
# Ln-s ../init. d/web K01mysql
When the system starts, the K01mysql script is automatically called with a stop parameter.

MySQL directory structure and Common commands
I. Data directory location
This is the default mysql directory structure.
Bin info libexec share var
Include lib man SQL-Example

A default data directory is compiled into the server. If you install MySQL from a source code distribution, the typical default directory is
/Usr/local/var. If it is installed from the RPM file, it is/var/lib/mysql. If it is installed from a binary distribution, it is
/Usr/local/mysql/data.
As a MySQL administrator, You should know where your data directory is. If you run multiple servers, you should go to all data directories.
But if you do not know the exact location, you can find it in multiple ways:

1. Use mysqladmin variables to directly obtain the path name of the data directory from your server. Find the value of the datadir variable. On Unix, the output is similar:
% Mysqladmin-u username-p ***** variables

+ ---------------------- +
| Variable_name | Value |
+ ---------------------- +
| Back_log | 5 |
| Connect_timeout | 5 |
| Basedir |/var/local/|
| Datadir |/usr/local/var/|
....
2. Find the mysql running path
% Ps-ef | grep mysqld

Ii. Data directory structure
Each database corresponds to a directory under the Data Directory.
Tables in a Database correspond to files in the data directory.
The data directory also contains several state files generated by the server, such as log files. These files provide