Mysql index, user and authorization (root password recovery)


1, MySQL index

1. Basic concepts of MySQL index

1) Advantages and disadvantages

2) Classification

2. Create index

1) General index

3. View index

4. Delete index

2, User and authorization

1. Authorization

2. Relevant query instructions

3. Authorization database mysql

4. Revoke authority

5.root password recovery

1, MySQL index

1. Basic concepts of MySQL index

An index is a data structure that helps MySQL get data efficiently. We can simply understand it as: it is a data structure for fast search and sorting.

It can be used to quickly query specific records in database tables, and all data types can be indexed.

Mysql indexes mainly have two structures: B+Tree index and Hash index

1) Advantages and disadvantages


       - It can greatly improve the retrieval speed of MySQL

       - Indexing greatly reduces the amount of data that the server needs to scan

       - Indexes help the server avoid sorting and temporary tables

       - Indexes can change random IO into sequential io


       - Although the index greatly improves the query speed, it will reduce the speed of updating the table, such as INSERT, UPDATE and DELETE. When updating tables, MySQL saves not only data but also index files.

       - Index files that take up disk space when indexing. Generally, this problem is not too serious, but if you create multiple composite indexes on a large table, the index file will expand rapidly.

       - If a data column contains many duplicate contents, indexing it will not have much practical effect.

       - For very small tables, simple full table scanning is more efficient in most cases;

2) Classification

General index

       - An index with no restrictions applied, which can be created in any data type.

       - The constraints of the field itself can determine whether its value is empty or unique.

       - After creating this type of index, users can query through the index when querying.

Unique index

       - Use the UNIQUE parameter to set a UNIQUE index.

       - When creating the index, the index value must be unique. Through the unique index, users can quickly locate a record

       - A primary key is a special unique index.

Full text index

       - Use the FULLTEXT parameter to set the index to a full-text index.

       - Full TEXT indexes can only be created on fields of type CHAR, VARCHAR, or TEXT. When querying string type fields with large amount of data, using full-TEXT index can improve the query speed.

       - By default, applying full-text search is case insensitive. If the indexed columns use binary sorting, case sensitive full-text indexing can be performed.

single column

       - As the name suggests, a single column index is an index that corresponds to only one field.

       - The conditions for applying the index only need to ensure that the index value corresponds to a field.

       - Can include normal, unique, full-text indexes

Multi column index

       - Multi column index is to create an index on multiple fields of a table.

       - The index points to the corresponding fields when it is created. Users can query through these fields.

       - To apply the index, the user must use the first of these fields.

2. Create index

1) General index

       - There can be multiple index es in a table

       - The value of the field can be repeated and can be assigned null

       - Index is usually configured on the field in the where condition

       - The flag of the index field is mul

Create index when creating table

CREATE TABLE Table name(
    Field list,
    index(Field name),
    index(Field name),

Create an index in an existing table

CREATE INDEX Index name ON Table name(Field name);

3. View index

DESC Table name;    # Pay attention to the Key column

4. Delete index

DROP INDEX Index name ON library.Table name;

2, User and authorization

1. Authorization

1) Create user and authorize


GRANT Permission list ON Library name.Table name TO 'user name'@'Client address' IDENTIFIED BY 'password' WITH GRANT OPTION;

         Permission list: the user's operation permissions, such as SELECT, INSERT, UPDATE, etc. if you want to grant the required permissions, use ALL

         Table name: table name. If you want to grant the user the corresponding operation permissions on all databases and tables, it can be represented by *, such as **

WITH GRANT OPTION: the user has authorization permission


# Grant zzg user permission to log in locally
mysql> grant select,update(phone_number,email) on nsd2021.employees to zzg@'localhost' identified by '';
# Grant zzg users the right to log in at any address
GRANT SELECT, INSERT, UPDATE(phone_number,email) ON nsd2021.employees to zzg@'%' IDENTIFIED BY '';

Client connection test

# Install mysql/mariadb client
[root@zzgrhel8 ~]# yum install -y mariadb
[root@zzgrhel8 ~]# mysql -h server - u username - p password

2. Relevant query instructions

1) View user information


2) Display the login user's own permissions


3) The administrator can view the permissions of the specified user. If the user does not exist, an error is reported

SHOW GRANTS FOR user name@'Client address';

4) Users modify their passwords

SET password=password('password');

5) The administrator modifies the specified user password

SET PASSWORD FOR user name@'Client address'=password('password');

6) Delete user

DROP USER user name@'Client address';

3. Authorization database mysql

1) Correlation table

               User: records the existing authorized users and permissions. This table is mainly concerned with the host and user fields

               db: records the access rights of existing authorized users to the database. This table is mainly concerned with the host, db and user fields

         tables_priv: records the access rights of an authorized user to the table

         columns_priv: records the access rights of the existing authorized user to the field

mysql> grant select,insert,update(phone_number,email) on nsd2021.employees to zzg@'localhost' identified by '';

  2) View all authorized users

mysql> select user, host from mysql.user;
| user      | host      |
| root      | %         |
| tom       | %         |
| zzg       | %         |
| mysql.sys | localhost |
| root      | localhost |
| zzg       | localhost |
6 rows in set (0.01 sec)

  3) Permission to query zzg@ '%'

mysql> show grants for zzg@'%';
mysql> select host, user, db from mysql.db;
| host      | user      | db      |
| %         | tom       | nsd2021 |
| localhost | mysql.sys | sys     |
2 rows in set (0.00 sec)
mysql> select * from tables_priv where User like '%zzg%'\G
*************************** 1. row ***************************
       Host: localhost
         Db: nsd2021
       User: zzg
 Table_name: employees
    Grantor: root@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select,Insert
Column_priv: Update
1 row in set (0.00 sec)

4. Revoke authority


REVOKE Permission list ON Library name.Table name FROM user name@'Client address';


# See what permissions the user has
SELECT host, user FROM mysql.user;
# View permissions
SHOW GRANTS FOR user name@'Client address';
# Revoke authorization
REVOKE GRANT OPTION ON *.* FROM user name@'Client address';
# Revoke user delete permission
REVOKE DELETE ON *.* FROM user name@'Client address';
# Create tom user with authorization authority
mysql> grant all on *.* to tom@'%' identified by '' with grant option;
# After tom logs in, create jerry user
[root@zzgrhel8 ~]# mysql -utom -h192.168.1.11
MySQL [(none)]> grant select on nsd2021.* to 'jerry'@'%' identified by '';

5.root password recovery


         Stop MySQL service

         Skip authorization table and start MySQL service program

         Change root password

         Restart MySQL service program in normal mode


# Stop MySQL service
[root@mysql1 ~]# systemctl stop mysqld
# Modify the configuration file, skip the authorization table and start the MySQL service program
[root@mysql1 ~]# vim /etc/my.cnf
... ...
# Start service
[root@mysql1 ~]# systemctl start mysqld
# Change root password
[root@mysql1 ~]# mysql
mysql> update mysql.user set authentication_string=password('123456')
    -> where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
# Restart MySQL service program in normal mode
[root@mysql1 ~]# systemctl stop mysqld
[root@mysql1 ~]# vim /etc/my.cnf
# skip-grant-tables
... ...
[root@mysql1 ~]# systemctl start mysqld
[root@mysql1 ~]# mysql -uroot -p123456

Tags: Database MySQL

Posted on Fri, 10 Sep 2021 18:12:16 -0400 by rhunter007