Shulou(Shulou.com)05/31 Report--

How to understand user and rights management and log system in the foundation of MySQL, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

1. MySQL user 1. Basic description

In the process of using the database, the user, as an authentication factor to access the database, plays a very important role. When MySQL is installed, a root user is automatically generated, as a database administrator, with all permissions. In a multi-user application scenario, it may be necessary to assign different permissions to different users to improve the stability of the system, for example, report libraries only provide read permissions, or are open to third-party libraries, and only provide readable users.

2. User management

Basic description

MySQL stores user information in the user table of the system database mysql. The account is defined based on the username and password and the client host.

User password: basic authentication operation

Client-side IP: similar to blacklist and whitelist restrictions, supports wildcard expressions

SELECT t.`Host`, t.`User`, t.authentication_string FROM mysql.`user`t

Add user

You can add, delete, modify and check a series of operations on the user table, and then add users, different users will involve different operation rights, which is another problem: user rights management.

Here, a user01 user is added as the test user of the permissions module, and the permissions are first given the same permissions as the root users.

INSERT INTO `mysql`.`user` (`Host`, `User`, `User`) VALUES ('%', 'user01',' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'); FLUSH PRIVILEGES

Note: here the host assignment% means that all IP can connect to the MySQL server through user01 users. You need to perform a refresh operation after modifying the system table.

Second, access authentication 1. Access control

In MySQL database system, permission assignment involves the following core tables: user, db, table_pric, columns_priv. Follow this order to verify step by step during permission authentication.

Permission table description

User table: stores users and users' global permissions, and is also the first table to bear the brunt of MySQL authentication process.

Db: saving database permissions

Tables_ private table: stores table permissions for a specific table and all columns in it

Columns_ private table: stores column permissions for a single column in a specific table

Note: the management of the permission table is not only a few of the above description, but life is short, straighten out these, other tables should also be able to find the past.

User table structure

It deals with the connection information of the user, as well as a lot of authority point authentication.

CREATE TABLE `user` (`Host` char (60) COLLATE utf8_bin NOT NULL DEFAULT'', `User`User` char (32) COLLATE utf8_bin NOT NULL DEFAULT'', `User` enum ('nasty enum Y') CHARACTER SET utf8 NOT NULL DEFAULT' Native, `Update_ priv`enum ('Nongyun Y') CHARACTER SET utf8 NOT NULL DEFAULT' nasty, `Update_ priv`enum ('Nongyuny`) CHARACTER SET utf8 NOT NULL DEFAULT' Nsuch, `Delete_ priv`enum ('Numeric journal Y') CHARACTER SET utf8 NOT NULL DEFAULT' N' `Create_ priv` enum ('Numeric recording Y') CHARACTER SET utf8 NOT NULL DEFAULT' Native, `Drop_ priv`enum ('Numeric recording Y') CHARACTER SET utf8 NOT NULL DEFAULT' Native,. / / A lot of omitted here) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

Note: notes, user and global rights management.

Permission point description

For the general development process, know the following permission point management, the reason is the same as above, and then get familiar with it when you need to use it.

SELECT: query records in tables; INSERT: write new data to tables; UPDATE: update table data; DELETE: delete table records; CREATE: create databases and tables; DROP: delete databases and tables

One sentence: what is your favorite program logic after working for a few years? Anyway, I just want to write a simple operation of adding, deleting, changing and searching.

2. Authentication process

First verify the user table, then the db table, then the table table, then the column table

Based on the gradual narrowing of the scope, the permissions are constantly refined.

Test user01 user rights

Permission query

First check the query permissions of the user01 user. At this point, the user has select privileges.

SELECT t.`Host`, t.`User`, t.Select_priv FROM mysql.`user`t WHERE t.`User` = 'user01'

Based on the client tool, you can use query permissions to log in to the MySQL server using user01 users.

Disable query permission points

UPDATE `mysql`.`user`SET `User` ='N' WHERE `User` = 'user01';FLUSH PRIVILEGES

Permission verification

Clients that log in using user01 cannot query table data, indicating that rights management works.

Log recording system 1. Log configuration check

Based on this statement, view log-related configurations, such as log address, whether to enable or disable, log cache size, and related configuration information.

SHOW GLOBAL VARIABLES LIKE'% log%'

Stop the MySQL server normally, and you can change the relevant configuration through my.cnf. The configuration file under Linux is usually in / etc/my.cnf.

2. InnoDB transaction log

InnoDB's transaction logs include Redo-log and Undo-log, and this log is described under the InnoDB storage engine-disk structure module of MySQL5.7 official documents.

Redo-log

Redo log: a disk-based data structure that records data that is not normally written to the library during the crash of a transactional operation. Redo: deal with data records that are not written normally in the log, and complete data storage.

Undo-log

Rollback log: provides a rollback operation and multiple row version control MVCC. When a transaction commits, the Undo-log is recorded. When the transaction fails or rollback is performed, it needs to be rolled back through Undo-log. Thinking leap: when writing data, the log record should be a new mark, the record to be performed is to delete the data operation, delete the data, the process should be the opposite, to record the write operation of the deleted data.

2. Error log

In the configuration file of MySQL, log_error is forcibly enabled, and there is no shutdown switch, which is used to record the details of each startup and shutdown of the mysql server, as well as serious warning and error messages during operation. The configuration under Linux is as follows:

Log-error=/var/log/mysqld.log

The error log contains records of mysqld startup and shutdown times. It also contains diagnostic messages, such as errors, warnings, and comments, which occur during server startup and shutdown and during server operation. For example, if mysqld notices that the table needs to be checked or repaired automatically, it will write a message to the error log.

3. General query log

General-Query-Log, all connections and statements are recorded to the log file. This log can be useful when you want to know that an error has occurred on the client and want to know exactly what statements the client sent to mysqld. Mysqld logs statements to the query log in the order it receives. It may be different from the order in which it is executed. This is different from update logs and binary logs, which log after the query is executed, but before any lock is released. The MySQL5.6 version is turned off by default.

4. Binary log

Binary-Log is mainly used to record database changes, such as table creation operations or table data change events. For the master-slave replication process, the binary logs on the master database server are sent to the slave database server, and these events are executed from the slave server to ensure the data synchronization of the master-slave server.

Log_bin OFF

In the MySQL5.6 version, the log is turned off by default.

5. Slow query log

The Slow-Query-Log slow log mainly records the sql that has been executed for a long time in the mysql. The default threshold is 10 seconds. Sql statements with execution time longer than 10 seconds will be recorded in the slow log. The configuration of the slow log can be configured in the mysql configuration file and is not enabled by default.

SHOW GLOBAL VARIABLES LIKE'% long_query_time%'

Open the slow query log, through the adjustment of this time, you can record poor performance SQL statements for analysis and optimization, which is very helpful to improve the performance of the system.

Fourth, source code address GitHub address https://github.com/cicadasmile/mysql-data-baseGitEE address https://gitee.com/cicadasmile/mysql-data-base on the basis of MySQL how to understand users and rights management and log system questions to share here, I hope the above content can be of some help to you, if you still have a lot of doubts unsolved, you can follow the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.