Shulou(Shulou.com)06/01 Report--

1. Account locking

Support for administrators to lock / unlock user accounts, statement:

1 Lock

ALTER USER 'hechunyang'@'%' ACCOUNT LOCK

When the user logs in again after locking, the following information is prompted:

# mysql-h227.0.0.1-uhechunyang-p-P3312

Enter password:

ERROR 4151 (HY000): Access denied, this account is locked

2 unlock

ALTER USER 'hechunyang'@'%' ACCOUNT UNLOCK

Note: the account is locked, existing connections are not affected, and new clients are not allowed to connect.

3 View account locking information

> show create user hechunyang@'%'\ G

* * 1. Row *

CREATE USER for hechunyang@%: CREATE USER 'hechunyang'@'%'

IDENTIFIED BY PASSWORD

'* 1DA3AF2348DE66F7554E816DEDC1F1340814842E' ACCOUNT LOCK

> select * from mysql.global_priv where user='hechunyang'\ G

* * 1. Row *

Host:%

User: hechunyang

Priv:

{"access": 1073740799, "plugin": "mysql_native_password", "authentication_string": "*

1DA3AF2348DE66F7554E816DEDC1F1340814842E "," password_last_changed ": 15

76823835, "account_locked": true}

2. User password expires

1 set the user expiration time

> CREATE USER 'hechunyang'@'%' PASSWORD EXPIRE INTERVAL 1 DAY

> ALTER USER 'hechunyang'@'%' PASSWORD EXPIRE INTERVAL 1 DAY

Note: the unit is only DAY (day) by default, and the minimum is 1 day.

When the user permission expires, you will be prompted to change the password when you log in, as follows:

Hechunyang@127.0.0.1 [(none)] > show processlist

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

2 lift the restriction

> ALTER USER 'hechunyang'@'%' PASSWORD EXPIRE NEVER

3. Support changing character set utf8- > utf8mb4 using ALGORITHM=INSTANT algorithm

MariaDB supports a certain field of the table, or the whole table. The character set utf8 is converted to utf8mb4 using the ALGORITHM=INSTANT algorithm (only dictionary information is modified)

Example table structure:

CREATE TABLE T1 (

Id int (11) DEFAULT NULL

Cid int (11) DEFAULT NULL

Name varchar (60) DEFAULT NULL

KEY IX_cid (cid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

DDL change statement (change in seconds)

Alter table T1 modify name varchar (60) charset utf8mb4, ALGORITHM=INSTANT

Points to pay attention to:

1) conversely, utf8mb4- > utf8 does not support the INSTANT algorithm.

2) if your field is latin1 Latin, the INSTANT algorithm is not supported when converting to utf8/utf8mb4.

Note: in MySQL 8.0.18, the INSTANT algorithm cannot be used to modify the character set utf8- > utf8mb4. The table needs to be rebuilt (ALGORITHM=COPY algorithm), and the table will be locked, and the update/delete/insert/replace into statement will be locked by MDL (Waiting for table metadata lock).

4. The length of the field is no longer than varchar (256), and the ALGORITHM=INSTANT algorithm is supported.

Example table structure:

CREATE TABLE T1 (

Id int (11) DEFAULT NULL

Cid int (11) DEFAULT NULL

Name varchar (60) DEFAULT NULL

KEY IX_cid (cid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

DDL change statement (change in seconds)

Alter table T1 modify name varchar (80) DEFAULT NULL, ALGORITHM=INSTANT

Points to pay attention to:

1) INSTANT algorithm is not supported when varchar (60) is reduced to varchar (40).

2) greater than and equal to varchar (256), where 256 refers to bytes (UTF8 occupies 3 bytes) and does not support the INSTANT algorithm.

Note: only use INSTANT algorithm for varchar type, char and int are invalid, you still need to copy the data and lock the table.

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.