Mysql8.0 installation tutorial and password issues

1: Download mysql source

mysql8.0 Introduction:

  1. Performance: MySQL 8.0 is 2 times faster than MySQL 5.7. MySQL 8.0 brings better performance in the following aspects: read / write workload, IO intensive workload, and high competition ("hot spot") workload.

  2. NoSQL: MySQL has provided NoSQL storage function since version 5.7. At present, this function has been greatly improved in version 8.0. This function eliminates the need for an independent NoSQL document database, and MySQL document storage also provides multi document transaction support and complete ACID compliance for JSON documents in schema less mode.

  3. Window functions: starting from MySQL 8.0, a new concept called window functions is added, which can be used to implement several new query methods. The window function is similar to the collection functions such as SUM() and COUNT(), but it does not merge multiple rows of query results into one row, but puts the results back into multiple rows. That is, the window function does not need GROUP BY.

  4. Hide index: in MySQL 8.0, indexes can be "hidden" and "displayed". When an index is hidden, it is not used by the query optimizer. We can use this feature for performance debugging. For example, we hide an index and then observe its impact on the database. If the database performance decreases, it indicates that this index is useful, and then "restore the display" can be used; If the database performance does not change, it indicates that the index is redundant and can be deleted.

  5. Descending index: MySQL 8.0 supports sorting indexes in descending order, and the values in this index will also be sorted in descending order.

  6. Common table expressions (CTE): when using embedded tables in complex queries, using CTE makes query statements clearer.

  7. UTF-8 encoding: starting from MySQL 8, use utf8mb4 as the default character set of MySQL.

  8. JSON: MySQL 8 has greatly improved its support for JSON by adding JSON that extracts data from JSON fields based on path query parameters_ The extract() function, and JSON for combining data into JSON arrays and objects, respectively_ Arrayagg() and JSON_OBJECTAGG() aggregate function.

  9. Reliability: InnoDB now supports the atomicity of table DDL, that is, the DDL on the InnoDB table can also achieve transaction integrity, either rollback in failure or commit successfully, so as to avoid the problem of partial success in DDL. In addition, it also supports the crash safe feature, and the metadata is stored in a single transaction data dictionary.

  10. High availability: InnoDB cluster provides an integrated native HA solution for your database.

  11. Security: improvements to OpenSSL, new default authentication, SQL role, password strength, authorization.

Detailed update Description:
Official release notes:
New features in MySQL 8 official edition:

Mysql installation:

1: Download Mysql source

[root@localhost ~]# rpm -Uvh

2: Install mysql8 0

[root@localhost ~]# yum --enablerepo=mysql80-community install mysql-community-server -y

The following contents indicate that the installation is successful

3: Start mysql

[root@localhost ~]# systemctl start mysqld

mysql8.0 will generate a temporary password after startup. Let's log in with the temporary password first

[root@localhost ~]# awk '/temporary password/ {print $13}' /var/log/mysqld.log

4: Login to mysql

[root@localhost ~]# mysql -uroot -p'ulq10uCz<oxo'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


5: Modify temporary password

mysql> alter user 'root'@'localhost' identified by 'Linux112';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

You can see the prompt: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement
Policy requirements (the password does not conform to the current policy)
In 8.0, the first password must be forcibly changed to the form of capital letters + symbols + numbers, and not less than 8 digits

mysql> alter user 'root'@'localhost' identified by 'Linux@112';
Query OK, 0 rows affected (0.00 sec

Next, let's look at the password policy:

mysql> show variables like 'validate_password%';
| Variable_name                        | Value  |
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
7 rows in set (0.00 sec)

Policy description
validate_password.length is the minimum length of the password. The default is 8. We change it to 6
Input: set global validate_password.length=6;
validate_password.policy the complexity of the verification password. We change it to 0
Input: set global validate_password.policy=0;
validate_password.check_user_name check the user name. The user name and password cannot be the same. We also turn it off
Input: set global validate_password.check_user_name=off;

Change to simple password again

mysql> set global validate_password.length=6;
Query OK, 0 rows affected (0.01 sec)

mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password.check_user_name=off;
Query OK, 0 rows affected (0.00 sec)

You can see that the modification is successful

mysql> alter user 'root'@'localhost' identified by 'Linux112';
Query OK, 0 rows affected (0.00 sec)

If you do not want to use a password, you can also skip the password in the following ways:

1: In / etc / my Add skip grant tables content to CNF file

2: Restart mysql

3: Directly enter mysql to log in

Keywords: Database MySQL

Added by pauleth on Thu, 30 Dec 2021 16:06:32 +0200