Centos7.6 MySQL 8 environment setup configuration remote login character set utf8 simple password

Time:2020-8-14

1、 Environmental preparation

1. Clean up MySQL that comes with the system in the environment

(1) Delete MySQL or MariaDB that comes with the system

yum remove mysql-libs

(2) Query whether there are any remaining dependent packages in the system

rpm -qa | grep mariadb 

(3) Remove RPM dependent packages

rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64

2 dependent package download

Note: please manage the package according to your actual needs

(1) Install all development kits

yum groupinstall -y "Development tools" 

(2) Install other required packages

yum install -y zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel 

2、 MySQL 8 installation

1. Download the RPM package management file on the official website

wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

2. Install MySQL locally

yum localinstall mysql80-community-release-el7-1.noarch.rpm 

3. Query whether you can connect to MySQL warehouse

yum repolist enabled | grep "mysql.*-community.*"

4. Install MySQL service

yum install mysql-community-server

3、 MySQL configuration

1. Enable compatibility with versions before MySQL 8

vim /etc/my.cnf  
default-authentication-plugin=mysql_native_password

2. Modify MySQL default initial password

(1) Query MySQL default initial password

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

(2) Log in to MySQL

mysql -u root -p 

(3) Enter the password for the query display

(4) Change password

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

(5) Refresh system permission list

flush privileges;

(6) Re login

3. Remote login authorization

use mysql;
select user,host from user; 
update user set host = '%' where user = 'root'; 

4. Firewall opens ports 3306 and 22

3306 MySQL default port
22 SSH access port

5. Password policy

(1) View MySQL password policy

show variables like '%validate%';

(2) Set simple password

Note: global configuration file changes my.cnf

set global validate_password.policy=0;
set global validate_password.length=6;
set global validate_password.mixed_case_count=0;
set global validate_password.number_count=0; 
set global validate_password.special_char_count=0; 
flush privileges; 

(3) Change password

MySQL > alter user 'root' @ '%' identified by 'password';

6. Default character set modification

(1) View MySQL password policy

show variables like '%character%';

(2) my.cnf Configuration file modification

[mysqld]
init_connect='set collation_connection = utf8_general_ci'
init_connect='set collation_database=utf8_general_ci' 
init_connect='set names utf8'
init_connect='set character_set_connection=utf8'
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

4、 MySQL startup

1. Start MySQL service

systemctl start mysqld

2. View MySQL service status

systemctl status mysqld

3. Start up automatically

systemctl enable mysqld.service

4. Restart MySQL service

systemctl restart mysqld;

5、 Precautions

1. All the above operations are root users
2、ERROR 1819 (HY000)

Mysql5.7 installs the password security check plug-in (validate) by default_ password)
The default password checking policy requires that the password must contain: upper and lower case letters, numbers and special symbols, and the length should not be less than 8 bits.

3. After setting a new user or changing the password, you need to flush privileges to refresh the MySQL system permission related table, otherwise access will be denied
flush privileges; 
4. If it is alicloud and other cloud servers, you need to open port 3306 in the security settings access management
5. If you use xshell connection to access port 22, use xftp to access port 22

6. Use “utf8” when configuring character set encoding_ general_ Ci “instead of” utf8 “_ unicode_ ci”