centOs7 installation and configuration mysql

Posted by boby on Sun, 20 Feb 2022 02:35:06 +0100

1, Installing YUM Repo

1. Download command:

wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

2, To install repo:

rpm -ivh mysql57-community-release-el7-9.noarch.rpm

[root@VM-0-10-centos server]# rpm -ivh mysql57-community-release-el7-9.noarch.rpm
warning: mysql57-community-release-el7-9.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql57-community-release-el7-9  ################################# [100%]
[root@VM-0-10-centos server]# 

After execution, it will be displayed in / etc / yum repos. D / directory to generate two repo files MySQL community repo mysql-community-source. repo

3, Use the yum command to complete the installation

Note: you must enter / etc / yum repos. D / directory before executing the following script

1. Installation command:

yum install mysql-server

2. Start mysql:

systemctl start mysqld


4, Login:

1. First obtain the temporary password during installation

grep 'temporary password' /var/log/mysqld.log

Then log in and enter the obtained temporary password

mysql -u root -p

[root@VM-0-10-centos yum.repos.d]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.33

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.


2. Change the password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'test123';

An error may occur

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '12345678';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

You need to set the authentication strength level of the password and set validate_ password_ The global parameter is LOW policy,

Enter the set value statement "set global validate_password_policy=LOW;" Set the value,

mysql> set global validate_password_policy=LOW;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '!abc1234';
Query OK, 0 rows affected (0.00 sec)


3. View password policy

mysql> SHOW VARIABLES LIKE 'validate_password%';
| Variable_name                        | Value |
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 8     |
| validate_password_mixed_case_count   | 1     |
| validate_password_number_count       | 1     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 1     |
7 rows in set (0.00 sec)


The current password is 8 digits. If you need to change it to 6 digits,

Enter the set value statement "set global validate_password_length=6;" Set the value,

Note: the minimum length of the default password is 4, which consists of one upper / lower case letter + one Arabic numeral + one special character. As long as the length of the password is less than 3, it will be automatically set to 4,

4. Related parameters of mysql password policy;

1),validate_password_length  Total length of fixed password;

2),validate_password_dictionary_file Specify the file path of password authentication;

3),validate_password_mixed_case_count  The entire password must contain at least one large password/The total number of lowercase letters;

4),validate_password_number_count  The whole password must contain at least the number of Arabic numerals;

5),validate_password_policy Specifies the strength authentication level of the password. The default is MEDIUM;

about validate_password_policy Value of:

LOW: Only verify the length;

1/MEDIUM: Verify the length, number, case and special characters;

2/STRONG: Verify the length, number, case, special characters and dictionary files;

6),validate_password_special_char_count At least the number of special characters in the whole password;

5, Turn on remote control

MySQL does not have remote control enabled by default, and remote access users must be added, that is, it can only be accessed by itself by default, but not by other machines.

1.1. Connect to the server: mysql -u root -p

1.2. View all current databases: show databases;

1.3. Enter mysql database: use mysql;

1.4. View all tables in mysql database: show tables;

1.5. Modify the host in the user table: update user set host = '%' where User = 'root';

            explain: % Represents any client,Can be replaced by specific IP Address.

1.6. Finally refresh: flush privileges;

6, Other configurations

1. Set security options:


2. Close MySQL

systemctl stop mysqld

3. Restart MySQL

systemctl restart mysqld

4. View MySQL running status

systemctl status mysqld

5. Set startup

systemctl enable mysqld

6. Turn off startup

systemctl disable mysqld

7. Configure the default code as utf8:

vi /etc/my.cnf
[mysqld] character_set_server=utf8 init_connect='SET NAMES utf8'

7, Creating users and rights management

1. Create user:

  • Login of mjj user with specified ip:

create user 'alex'@'' identified by '123';

  • Specify ip: 192.118.1 Login of mjj user at the beginning

create user 'alex'@'192.118.1.%' identified by '123';

  • Specify the mjj user login for any ip

create user 'alex'@'%' identified by '123';

2. Delete user

drop user 'user name' @ 'IP address';

3. Modify user

rename user 'user name' @ 'IP address' to' new user name '@' IP address';

4. Change password

set password for 'user name' @ 'IP address' = Password('New password');

5. Authorization management of current users

  • View permissions

show grants for 'user' @ 'IP address'

  • Authorized mjj users are only available to db1 T1 files can be queried, inserted and updated

grant select ,insert,update on db1.t1 to "test"@'%';

  • It means that you have all permissions. Except grant, this command is only available to root. mjj user has any operation on t1 file under db1

grant all privileges on db1.t1 to "alex"@'%';

  • mjj users do nothing with files in the db1 database

grant all privileges on db1.* to "alex"@'%';

  • mjj user has any operation on files in all databases

grant all privileges on . to "test"@'%';

Cancel permission

  • Cancel any operation of mjj user on t1 file of db1

revoke all on db1.t1 from 'alex'@"%";

  • Cancel all permissions of mjj user from remote server on all tables of database db1

revoke all on db1.* from 'alex'@"%";

  • Revoke permissions on all tables of mjj user database from remote server

revoke all privileges on . from 'alex'@'%';

Topics: Operation & Maintenance Database MySQL