MYSQL use trilogy -- MYSQL installation

1, Ubuntu installation

The apt tool can be used directly for Ubuntu installation

sudo apt-get install mysql-server //Server

sudo apt-get install mysql-client //client

sudo apt-get install libmysqlclient-dev //Library linked at compile time

After installation, the mysql server will run automatically. You can run the downlink command to view the running status of mysql.

sudo systemctl status mysql

After successful installation, there will be three accounts by default, one of which is root. The password authentication protocol for root user is auth_socket, the default password is random, so you can't log in to mysql through the root password.

The other two accounts and passwords can be found in / etc / MySQL / Debian Found in CNF file.

Therefore, initially, we can only use Debian Log in to mysql with the account information in the CNF file. Of course, you can also log in to mysql through the root user using the "sudo mysql" command.

Using the mysql initialization tool,

sudo mysql_secure_installation

The initial run will require you to set the VALIDATE PASSWORD PLUGIN, which is used to test the strength of MySQL user passwords and improve security:

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

If you select Yes, three levels of password authentication policies are provided:

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 

Enter the selected policy.

Then you will be asked to set the root password (although it is useless here because of the auth_socket mentioned above). If you set the password verification plug-in, this script will display the strength of your new password. Next, you will be asked to remove any anonymous users, restrict root user access to the local machine, remove the test database and reload the permission table. You should answer all the questions y.

If you need to customize the password authentication policy later, you can log in to mysql and use it

show variables like 'validate_password%';

View password authentication policy

| 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      |

Similar can be used

set global validate_password.policy=0;

To set password authentication policy globally.


1.1 modify the password authentication method of root user

After logging into mysql, we can use the following statement to set the new password of root.

use mysql;
alter user 'root'@'localhost' identified with mysql_native_password by 'New password';

  mysql_native_password is a password encryption policy that uses this password. Because mysql8 After 0.11, the default password encryption policy is changed to caching_sha2_password, but there are still many that do not support this new strategy.

It can be found in MySQL View User information in User. Some common information, such as Host (access address), User (User name), authentication_string (login password), plugin (password encryption policy).

1.2 remote access users

The Host of the root user is localhost, that is, it can only be accessed locally. If you need to access the database remotely, you need a user who can access it remotely. Generally, you can select to create a new user and give it the permissions you need.

create user 'admin'@'%' identified with mysql_native_password by 'admin';

grant all privileges on *.* to 'admin'@'%';

flush privileges;

The first sentence is to create an admin user,% which means that the access address is arbitrary. The second sentence is the permission granting statement, ** It represents all tables of all databases. At the same time, I chose to give all permissions here. In fact, I can give the required permissions. The third sentence is to refresh the permission table so that the child can take effect.

If you do not execute the third sentence, you can also reload the permission table by restarting mysql.

1.3 database settings

In / etc / MySQL / MySQL There is mysqld in the conf.d folder CNF configuration file, which sets the database access port and allowed IP address. Bind address =, that is, only local access is allowed. If you want remote access, you can modify this to the specified IP address or wildcard (

This file will only be configured at startup, so you need to restart mysql after modification.

service mysql restart


2, Client installation under Windows

MySQL provides a client tool workbench. Official download address:

Create a new MySQL Connections


We can access the database server remotely through this client.

Added by vMan on Tue, 04 Jan 2022 18:55:22 +0200