MySQL administrator Guide

MySQL administrator Guide

MySQL database Introduction

MySQL is a real multi-user, multi-thread SQL database server. SQL (Structured Query Language) is the world's most popular and standardized database language. MySQL is implemented in a Client/Server structure, which is guarded by a serverProgramMysqld is composed of many different client programs and libraries.

SQL is a standardized language that makes it easier to store, update, and access information. For example, you can use SQL to search product information and store customer information for a website. 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 that could process large databases of an order of magnitude with any manufacturers providing databases on non-expensive hardware platforms, but it was faster and MySQL was developed. We have been using MySQL since 1996. Its Environment has more than 40 databases, including 10,000 tables, of which more than 500 tables exceed 7 million rows, there are 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: 1, 2, 3, 4, and 8-byte length signed/unsigned integer (INT) float, double, Char, varchar, text, blob, date, time, datetime, timestamp, year, set, and enum.

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, no memory allocation should be made after the query initialization.

7. SQL group by and order by clauses are fully supported. Aggregate functions (count (), count (distinct), AVG (), STD (), sum (), and Max () are supported () 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 when connected to a server, all the password transmission is encrypted.

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 (which 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.

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 the 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 specific 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 a query.

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 in the MySQL installation directorySource codeFind the distribution in the scripts directory.

The MySQL. server script is installed in the share/mysqld directory under the MySQL installation directory or in the MySQL SourceCodeDistribution of support_files

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 user name-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, where should you go to all the 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 important information about server operations.

It is very valuable for management to try to identify the cause of the problem especially when there is a problem.

1. Database Table Representation

There are three types of 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, and the file name extension represents the file type. The following table lists the extensions. The extension of the data and index file indicates that the table uses the old iasm index or the new MyISAM index.

MySQL file type

File type, file name extension, File Content

The Style File. frm describes the table structure (its columns, column types, indexes, etc)

The data file. ISD (isam) or. MYD (MyISAM) contains all the data in the table.

The index file. ISM (isam) or. myi (MyISAM) contains the index tree of all the indexes on the data file.

When you issue a create table tbl_name statement to define the table structure, the server creates a file named tbl_name.frm, which includes the internal encoding of the structure, at the same time, an empty data and index file is created and initialized to contain information indicating no record or no index (if the create table statement includes the specified index, the index file reflects these indexes ). The owner and mode of the file corresponding to the table are set to allow access only by the MySQL server user.

The following describes common MySQL commands.

Enter the MySQL database

#./MySQL-u username-P 'Password'

View All databases

Mysql> show databases;

Enter a specific database

Mysql> Use Database Name;

View all tables in the database

Mysql> show tables;

Rename a table

Mysql> alter table table name 1 rename table name 2;

Example: mysql> alter table dept rename dept2;

2. Notes for indexing:

First, you must set the field to be indexed as non-empty.

Mysql> alter table table name change field Name field description not null;

Example:

We create such a table

Mysql> Create Table employee

(ID int (5) not null,

Depno int (5 ),

Name varchar (20) not null,

CardNumber bigint (15) not null );

Mysql> alter table employee change depno int (5) not null;

Add Index

Mysql> alter table table name Add Index name (field name 1 [, field name 2...]);

Example: mysql> alter table employee add index emp_name (name );

Index with primary keywords

Mysql> alter table table name add primary key (field name );

Example: mysql> alter table employee add primary key (ID );

Add an index with unique 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 table name;

Example: mysql> show index from employee;

Delete An index

Mysql> alter table Table Name drop index name;

Example: mysql> alter table employee drop index emp_name;

MySQL user management

The MySQL administrator should know how to set up the MySQL user account and specify which user can connect to the server, where to connect, and what to do after the connection. MySQL 3.22.11 introduces two statements to make this work easier: grant statements create mysql users and specify their permissions, while revoke statements delete permissions. The two statements assume the front-end role of the MySQL database and provide a different method than directly operating the contents of these tables. The create and revoke statements affect four tables:

Authorization Table content:

Users can connect to users on the server and have any global permissions.

Database-level Permissions

Tables_priv table-level Permissions

Columns_priv column-level permission

There are 5th other authorization tables (hosts), but they are not affected by Grant and revoke.

When you issue a grant statement to a user, create a record for the user in the User table. If the statement specifies any global permissions (administrative permissions or permissions applicable to all databases), these are also recorded in the User table. If you specify database, table, and column-level permissions, they are recorded in dB, tables_priv, and columns_priv tables respectively.

In the following sections, we will introduce how to set up and authorize a MySQL user account. We also involve how to revoke permissions and delete users from the authorization table.

1. Create and authorize a user

The syntax of the grant statement looks like this:

Grant privileges (columns)

On what

To user identified by "password"

With grant option

To use this statement, you must enter the following parts:

Privileges

The following table lists the permissions that can be used for grant statements:

Operation permitted by the permission specified OPERATOR:

Alter table and Index

Create Database and table Creation

Delete Delete existing records in the table

Drop discard (delete) databases and tables

Create or discard an index

Insert Insert a new row into the table

Reference unused

Select to retrieve records in a table

Update modify existing table records

File: reads or writes files on the server.

Process: View information about the thread executed on the server or kill the thread.

Reload: Reload the authorization table or clear logs, host caches, or table caches.

Shutdown the server

All; All privileges Synonyms

Usage special "no permission" permission

Columns

The permission column is optional, and you can only set specific permissions for the column. If the command has more than one column, separate them with commas.

What

Permission usage level. Permissions Can be global (applicable to all databases and tables), specific databases (applicable to all tables in a database), or specific tables. You can specify a columns statement to indicate that the permission is column-specific.

User

The user authorized by the permission, which consists of a user name and host name. A user name in MySQL is the user name specified when you connect to the server. It does not need to be associated with your UNIX or Windows Name. By default, if you do not specify a specific name, the customer program uses your login name as the MySQL user name. This is just an agreement. You can change the name to nobody in the authorization table, and then use the nobody connection to perform operations that require superuser permissions.

Password

The password assigned to the user. It is optional. If you do not specify the identified by clause for a new user, the user is not assigned a password (Insecure ). For existing users, any password you specify will replace the old password. If you do not specify a password, the old password remains unchanged. When you use identified by, the password string uses the literal meaning of the password, and grant will encode the password for you, do not use the password () function as you use set password.

The with grant option clause is optional. If you include it, you can grant permissions to other users through the grant statement. You can use this clause to grant permissions to other users.

The username, password, database, and table name are case sensitive in the authorization table record, and the host name and column name are not.

Example: Create a Super User test1

Mysql> grant all privilleges on *. * To test1 @ localhost identified by '000000' with grant option;

Create a user Test2 that can only be queried

Mysql> grant select on *. * To Test2 @ localhost identified by '123 ';

Ii. revoke permissions and delete users

To cancel a user's permissions, use the revoke statement. The syntax of revoke is very similar to the grant statement, except that it is replaced by from without the indetifed by and with grant option clauses:

Revoke privileges (columns) on what from user

The user part must match the user part of the user you want to revoke permission from the original grant statement. Privileges does not need to be matched. You can use the grant statement to grant permissions, and then use the revoke statement to revoke only some permissions. The revoke statement only deletes permissions, but does not delete users. Even if you revoke all permissions, the user records in the User table are retained, which means that the user can still connect to the server. To completely delete a user, you must use a delete statement to explicitly 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;

The delete statement deletes user records, while the flush statement tells the server to overload the authorization table. (When you use the grant and revoke statements, the table is automatically reloaded, but you do not modify the authorization table directly .)

Example: delete user 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

It is important to back up your database when database tables are lost or damaged. We already know that tables are damaged. Using editors such as VI or Emacs to directly edit them is definitely not a good thing!

The two main methods for backing up a database are to use the mysqldump program or directly copy database files (such as CP, cpio, or tar ). Each method has its advantages and disadvantages:

Work with the MySQL server. The direct copy method is performed outside the server, and you must take measures to ensure that no customer is modifying the table you will copy, which is generally done when the database is closed. Mysqldump is slower than direct copy. Mysqldump generates text files that can be transplanted to other machines, even those machines with different hardware structures. Directly copying a file can be transplanted to a similar machine, but cannot be transplanted to another machine, unless the table you are copying uses the MyISAM storage format.

I. Use mysqldump to back up and copy Databases

When you use the mysqldump program to generate a database backup file, by default, the file content includes the create statement for creating the table being dumped and the insert statement containing the row data in the table. In other words, the output produced by mysqldump can be used as MySQL input to recreate the database.

The mysqldump parameters are as follows:

# Mysqldump-u username-p' password' database name [Table name]> file name in the operating system

example :#. /mysqldump-u root-P '000000' samp_db> samp.db.txt
the start 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 table 'absence '
#
Create Table absence (
student_id int (10) unsigned default '0' not null,
Date default '2014-00-00 'not null,
Primary Key (student_id, date)
);
# Dumping data for table 'absence '
#
insert into absence values (3, '2017-09-03 ');
insert into absence values (5, '2017-09-03');
insert into absence values (10, '2017-09-08 ');
......
the rest of the file consists of 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 the memory before writing. This is usually unnecessary, and in fact, if you have a large table, it is almost a failure. You can use the -- quick option to tell mysqldump to write each row as long as it retrieves a row. To further optimize the dumping process, use -- opt instead of -- quick. -- Opt option opens other options to accelerate data dumping and read them back.

Ii. Using Direct Copy of database backup and copying Methods

Another method that does not involve mysqldump to back up databases and tables is to directly copy database table files. Typically, this uses utilities such as CP, tar, or cpio. The example in this article uses CP.

% Cd datadir

% CP-r samp_db/usr/archive/MySQL

A 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 have completed the backup, you can restart the server (if it is disabled) or release the lock added to the table (if you want the server to run ). To copy a database from one machine to another, just copy the file to the appropriate data directory of another server host. Make sure the file is in myiasm format or the two machines have the same hardware structure. Otherwise, your database has strange content on another host. You should also ensure that the servers on the other machine do not access them when you are installing database tables.

Create an SH file bakmysql. Sh

#! /Bin/sh

CD/usr/local/MySQL/

Tar CVF/mount2/mysqlvar.tar VaR

You can set the OS to run automatically.

Root User crontab file

/Var/spool/cron/crontabs/root Add the following content

0 2 1 **/mount2/bakmysql. Sh

#/Etc/rc2.d/s75cron stop

#/Etc/rc2.d/s75cron start

Restart the automatic process of Sun Solaris.

Iii. Use Backup to restore Data

There are many causes for database corruption, and their severity varies. If you are lucky, you may only damage one or two tables (such as power loss). If you are unlucky, you may have to replace the entire data directory (such as disk damage ). Recovery is also required in some cases. For example, the user mistakenly deleted the database or table. Regardless of the cause of these unfortunate events, you will need to implement a recovery.

If the table is corrupted but not lost, try to use myisamchk or isamchk to fix them. If the table is damaged, the repair program can fix it.

1. Restore the entire database

The mysqldump parameters are as follows:

# Mysqldump-u username-p' password' database name <file name in the operating system

Example:

Create another database in MySQL first

Mysql> Create Database test;

Import the backup data

# Mysqldump-u root-P '000000' Test2 <samp.db.txt