MYSQL Tutorial: check data tables and repair data tables _ MySQL

MYSQL Tutorial: check the data table and repair the data table MySQL server uses the permission table to control user access to the database. The permission table is stored in the mysql database and initialized by the mysql_install_db script. These permission tables are user, db, table_priv, columns_priv, and host respectively. The following describes the structure and content of these tables:

User permission table: records the information of user accounts allowed to connect to the server. the permissions in the table are global.

Database permission table: records the operation permissions of each account on each database.

Table_priv permission table: Records Data Table-level operation permissions.

Columns_priv permission table: records the operation permissions at the data column level.

Host permission table: the database permission table provides more detailed control over Database-level operation permissions on a given host. This permission table is not affected by the GRANT and REVOKE statements.

You have noticed that the preceding permissions are not limited to row-level settings. In MySQL, you only need to implement row-level control by writing a program (using the GET-LOCK () function.

MySQL has many versions, so the structure of the permission table varies with versions. In this case, use the mysql_fix_privilege_tables script to fix the issue. Run as follows:

% Mysql_fix_privilege_tables rootpassword # The MySQL root user password is provided here.

It is best to upgrade to MySQL 4.0.4 at once, because the db tables 4.0.2 and 4.0.3 do not have the Create_tmp_table_priv and Lock_tables_priv permissions.

The MySQL permission table defines two parts: one part defines the permission range, that is, who (account) where (client host) can access what (database, data table, data column ); define permissions in other parts, that is, control the operations that users can perform. The following are some common permissions that can be used directly in the GRANT statement.

Create temporary tables: Allows you to create temporary tables.

EXECUTE: permission to EXECUTE stored procedures. the stored procedures are not implemented in the current MySQL version.

FILE, allowing you to read and write files on the server host through the MySQL server. However, there are some restrictions that only accessible files can be read by any user. files written on the server must not exist to prevent important system files from being overwritten by Files written on the server. Despite these restrictions, do not grant this permission to common users for security purposes. Do not run the MySQL server as the root user, because the root user can create files anywhere in the system.

Grant option, which allows you to GRANT permissions to other users.

Lock tables. you can use the lock tables statement to LOCK the data table.

PROCESS, allowing you to view and terminate any client thread. The show processlist statement or mysqladmin processlist command can be used to view the thread. the KILL statement or mysqladmin kill command can terminate the thread. In versions 4.0.2 and later, the PROCESS permission is only available for viewing threads, and the thread termination capability is controlled by the SUPER permission.

RELOAD allows you to perform database management operations, such as FLUSH and RESET. It also allows you to execute mysqladmin commands: reload, refresh, flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, and flush-threads.

Replication client, which allows you to query the locations of the master server and slave server in the image mechanism.

Replication slave allows a customer to connect to the master server in the image mechanism and request to send a binary change log. This permission should be granted to the account used by the slave server to connect to the master server. Before version 4.0.2, the slave server was connected with the FILE permission.

Show databases controls the user's permission to execute the show databases statement.

SUPER: allows thread termination. use the mysqladmin debug command, change master, purge master logs, and SET statement to modify global variables. SUPER also allows you to decrypt DES based on the key stored in the DES key file.

The user permission table has an ssl_type data column to show whether the connection uses encrypted connections and which type of connections are used. it is an ENUM type data column and may take the following values:

NONE. the default value indicates no encrypted connection is required.

ANY, which indicates the encrypted connection is required. it can be ANY type of encrypted connection. Set by GRANT's require ssl clause.

X509 indicates that an encrypted connection is required and a valid X509 certificate is required. Set by the REQUIRE X509 clause of GRANT.

SPECIFIED indicates that the encrypted connection must meet certain requirements. set the value of ISSUER, SUBJECT, or CIPHER in the REQUIRE clause. As long as the value of the ssl_type column is SPECIFIED, MySQL checks the values in the ssl_cipher (encryption algorithm), x509_issuer (certificate issuer), and x509_subject (Certificate subject) columns. The column types of these columns are BLOB type.