MySQL 8.0 installation and deployment under CentOS, super detailed!

MySQL 8 official version 8.0.11 has been released, the official said MySQL8 is 2 times faster than MySQL 5.7, and also brings a lot of improvements and more Fast performance! Who is the best? Please see: MySQL 5.7 vs 8.0, which performance is better?

Mysql8.0 installation (YUM method)

First delete other versions of mysql that may be installed by the system by default or before

[ root@DB-node01 ~]# for i in$(rpm -qa|grep mysql);do rpm -e $i --nodeps;done
[ root@DB-node01 ~]# rm -rf /var/lib/mysql && rm -rf /etc/my.cnf

Install Mysql8.0 yum resource library

mysql80-community-release-el7-1.noarch.rpm    
 
[ root@DB-node01 ~]# yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

Install Mysql8.0

[ root@DB-node01 ~]# yum install mysql-community-server
   
# Start MySQL server and automatic start of MySQL
[ root@DB-node01 ~]# systemctl start mysqld
[ root@DB-node01 ~]# systemctl enable mysqld

After logging in for the first time with the default password, the password must be reset

View default password,The following default password is"e53xDalx.*dE"[root@DB-node01 ~]# grep 'temporary password'/var/log/mysqld.log
2019- 03- 06 T01:53:19.897262Z 5[Note][MY-010454][Server] A temporary password is generated for root@localhost: e53xDalx.*dE
  
[ root@DB-node01 ~]# mysql -pe53xDalx.*dE
............
mysql> select version();
ERROR 1820(HY000): You must reset your password using ALTER USER statement before executing this statement.

If you get an error message, you must reset the initial password, and then start to reset the mysql login password (note that you must switch to the mysql database, use use mysql)

mysql> use mysql;
ERROR 1820(HY000): You must reset your password using ALTER USER statement before executing this statement.
   
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
ERROR 1819(HY000): Your password does not satisfy the current policy requirements

This is actually related to the value of validate_password_policy, mysql8.0 has changed the configuration name related to validate_password_policy, which is a bit different from Mysql5.7.

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

Then change the password

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK,0 rows affected(0.05 sec)
   
mysql> flush privileges;
Query OK,0 rows affected(0.03 sec)

Exit, log in to mysql with the new password again

[ root@DB-node01 ~]# mysql -p123456
...........
mysql> select version();+-----------+|version()|+-----------+|8.0.15|+-----------+1 row inset(0.00 sec)

View service port

mysql> show global variables like 'port';+---------------+-------+| Variable_name | Value |+---------------+-------+| port          |3306|+---------------+-------+1 row inset(0.01 sec)

View authorization information for mysql connection

mysql> select host,user,password from mysql.user;
ERROR 1054(42S22): Unknown column 'password'in'field list'

The above is the view command for mysql5.6 and below. There is no password field in the mysql.user table in the database after mysql5.7, and the password field is changed to authentication_string.

mysql> select host,user,authentication_string from mysql.user;+-----------+------------------+------------------------------------------------------------------------+| host      | user             | authentication_string                                                  |+-----------+------------------+------------------------------------------------------------------------+| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | root             | $A$005${7J0=4Dc7Jym8eI/FU4jimKWFvkD9XmoAkF1ca5.Un0bc6zgmPtU.0|+-----------+------------------+------------------------------------------------------------------------+4 rows inset(0.00 sec)

mysql8.0 modify user password command

mysql> use mysql;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql> flush privileges;

Mysql8.0 installation (binary mode)

First delete other versions of mysql that may be installed by the system by default or before

[ root@mysql8-node ~]# for i in$(rpm -qa|grep mysql);do rpm -e $i --nodeps;done
[ root@mysql8-node ~]# rm -rf /var/lib/mysql && rm -rf /etc/my.cnf

Install the required packages

[ root@mysql8-node ~]# yum -y install libaio
[ root@mysql8-node ~]# yum -y install net-tools

Download and install Mysql8.0.12

[ root@mysql8-node ~]# groupadd mysql
[ root@mysql8-node ~]# useradd -g mysql mysql
 
[ root@mysql8-node ~]# cd /usr/local/src/[root@mysql-node src]# ll
- rw-r--r--1 root root 620389228 Aug 222018 mysql8.0.12_bin_centos7.tar.gz
[ root@mysql-node src]# tar -zvxf mysql8.0.12_bin_centos7.tar.gz
[ root@mysql-node src]# mv mysql /usr/local/[root@mysql-node src]# chown -R mysql.mysql /usr/local/mysql
 
[ root@mysql-node src]# vim /home/mysql/.bash_profile
export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH
[ root@mysql-node src]# source /home/mysql/.bash_profile
[ root@mysql-node src]# echo "PATH=$PATH:/usr/local/mysql/bin">>/etc/profile
[ root@mysql-node src]# source /etc/profile

Create data directory

[ root@mysql-node src]# mkdir -p /data/mysql/{data,log,binlog,conf,tmp}[root@mysql-node src]# chown -R mysql.mysql /data/mysql

Configure mysql

[ root@mysql-node src]# su - mysql
[ mysql@mysql-node ~]$ vim /data/mysql/conf/my.cnf
[ mysqld]
lower_case_table_names          =1
user                            = mysql
server_id                       =1
port                            =3306default-time-zone ='+08:00'
enforce_gtid_consistency        = ON
gtid_mode                       = ON
binlog_checksum                 = none
default_authentication_plugin   = mysql_native_password
datadir                         =/data/mysql/data
pid-file                        =/data/mysql/tmp/mysqld.pid
socket                          =/data/mysql/tmp/mysqld.sock
tmpdir                          =/data/mysql/tmp/
skip-name-resolve               = ON
open_files_limit                =65535
table_open_cache                =2000
 
################# innodb########################
innodb_data_home_dir            =/data/mysql/data
innodb_data_file_path           = ibdata1:512M;ibdata2:512M:autoextend
innodb_buffer_pool_size = 12000M
innodb_flush_log_at_trx_commit =1
innodb_io_capacity =600
innodb_lock_wait_timeout =120
innodb_log_buffer_size = 8M
innodb_log_file_size = 200M
innodb_log_files_in_group =3
innodb_max_dirty_pages_pct =85
innodb_read_io_threads =8
innodb_write_io_threads =8
innodb_thread_concurrency =32
innodb_file_per_table
innodb_rollback_on_timeout
 
innodb_undo_directory           =/data/mysql/data
innodb_log_group_home_dir       =/data/mysql/data
 
################### session###########################
join_buffer_size = 8M
key_buffer_size = 256M
bulk_insert_buffer_size = 8M
max_heap_table_size = 96M
tmp_table_size = 96M
read_buffer_size = 8M
sort_buffer_size = 2M
max_allowed_packet = 64M
read_rnd_buffer_size = 32M
 
############ log set###################
log-error                       =/data/mysql/log/mysqld.err
log-bin                         =/data/mysql/binlog/binlog
log_bin_index                   =/data/mysql/binlog/binlog.index
max_binlog_size                 = 500M
slow_query_log_file             =/data/mysql/log/slow.log
slow_query_log                  =1
long_query_time                 =10
log_queries_not_using_indexes   = ON
log_throttle_queries_not_using_indexes  =10
log_slow_admin_statements       = ON
log_output                      = FILE,TABLE
master_info_file                =/data/mysql/binlog/master.info

Initialization (wait a while, you can check the initialization process in /data/mysql/log/mysqld.err days to see if there is any error message)

[ mysql@mysql-node ~]$ mysqld --defaults-file=/data/mysql/conf/my.cnf  --initialize-insecure  --user=mysql

Start mysqld

[ mysql@mysql-node ~]$ mysqld_safe --defaults-file=/data/mysql/conf/my.cnf &[mysql@mysql-node ~]$ lsof -i:3306
COMMAND   PID  USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
mysqld  24743 mysql   23u  IPv6 23132988      0t0  TCP *:mysql(LISTEN)

No password is required to log in to mysql using the sock file locally for the first time
[ mysql@mysql-node ~]# mysql -S /data/mysql/tmp/mysqld.sock
.............
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK,0 rows affected(0.07 sec)
 
mysql> flush privileges;
Query OK,0 rows affected(0.03 sec)
 
mysql> select host,user,authentication_string from mysql.user;+-----------+------------------+------------------------------------------------------------------------+| host      | user             | authentication_string                                                  |+-----------+------------------+------------------------------------------------------------------------+| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | root             |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              |+-----------+------------------+------------------------------------------------------------------------+4 rows inset(0.00 sec)

Log out, after resetting the password, you cannot use the sock file to log in without password

[ root@mysql-node ~]# mysql -S /data/mysql/tmp/mysqld.sock
ERROR 1045(28000): Access denied for user 'root'@'localhost'(using password: NO)[root@mysql-node ~]# mysql -p123456
mysql:[Warning] Using a password on the command line interfacecan be insecure.
ERROR 2002(HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Make soft links to sock files

[ root@mysql-node ~]# ln -s /data/mysql/tmp/mysqld.sock /tmp/mysql.sock

[ root@mysql-node ~]# mysql -p123456
or
[ root@mysql-node ~]# mysql -uroot -S /data/mysql/tmp/mysqld.sock -p123456
.............
mysql> select version();+-----------+|version()|+-----------+|8.0.12|+-----------+1 row inset(0.00 sec)
 
# Grant user permissions.Must first create user,To authorize!!(When creating a user to bring@And specify the address,Then the address when grant is authorized is this@Specified later!,Otherwise, grant authorization will report an error!)
mysql> create user 'kevin'@'%' identified by '123456';
Query OK,0 rows affected(0.11 sec)
 
mysql> grant all privileges on *.* to 'kevin'@'%'with grant option; 
Query OK,0 rows affected(0.21 sec)
 
mysql> select host,user,authentication_string from mysql.user;+-----------+------------------+------------------------------------------------------------------------+| host      | user             | authentication_string                                                  |+-----------+------------------+------------------------------------------------------------------------+|%| kevin            |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              || localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | root             |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              |+-----------+------------------+------------------------------------------------------------------------+5 rows inset(0.00 sec)
 
mysql> update mysql.user set host='172.16.60.%' where user="kevin";
Query OK,1 row affected(0.16 sec)
Rows matched:1  Changed:1  Warnings:0
 
mysql> flush privileges;
Query OK,0 rows affected(0.05 sec)
 
mysql> select host,user,authentication_string from mysql.user;+-------------+------------------+------------------------------------------------------------------------+| host        | user             | authentication_string                                                  |+-------------+------------------+------------------------------------------------------------------------+|172.16.60.%| kevin            |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              || localhost   | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost   | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost   | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost   | root             |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              |+-------------+------------------+------------------------------------------------------------------------+5 rows inset(0.00 sec)
 
mysql>  create user 'bobo'@'172.16.60.%' identified by '123456';      
Query OK,0 rows affected(0.09 sec)
 
mysql> grant all privileges on *.* to 'bobo'@'172.16.60.%';     
Query OK,0 rows affected(0.17 sec)
 
mysql> flush privileges;
Query OK,0 rows affected(0.04 sec)
 
mysql> select host,user,authentication_string from mysql.user;+-------------+------------------+------------------------------------------------------------------------+| host        | user             | authentication_string                                                  |+-------------+------------------+------------------------------------------------------------------------+|172.16.60.%| bobo             |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              ||172.16.60.%| kevin            |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              || localhost   | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost   | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost   | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost   | root             |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              |+-------------+------------------+------------------------------------------------------------------------+6 rows inset(0.00 sec)
 
mysql> show grants for kevin@'172.16.60.%';+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Grants for [email protected].%|+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `kevin`@`172.16.60.%` WITH GRANT OPTION |+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row inset(0.00 sec)

MySQL single machine multi-instance installation configuration

From the above binary deployment, we can see that a MySQL instance with port 3306 has been set up. Now two more instances need to be set up, 3307 and 3308 respectively. The operations are as follows:

Create a data directory for the instance

[ root@mysql-node ~]# mkdir -p /data/mysql3307/{data,log,binlog,conf,tmp}[root@mysql-node ~]# mkdir -p /data/mysql3308/{data,log,binlog,conf,tmp}[root@mysql-node ~]# chown -R mysql.mysql /data/mysql3307
[ root@mysql-node ~]# chown -R mysql.mysql /data/mysql3308

Configure mysql

[ root@mysql-node ~]# cp -r /data/mysql/conf/my.cnf /data/mysql3307/conf/[root@mysql-node ~]# cp -r /data/mysql/conf/my.cnf /data/mysql3308/conf/[root@mysql-node ~]# sed -i 's#/data/mysql/#/data/mysql3307/#g'/data/mysql3307/conf/my.cnf
[ root@mysql-node ~]# sed -i 's#/data/mysql/#/data/mysql3308/#g'/data/mysql3308/conf/my.cnf
[ root@mysql-node ~]# sed -i 's/3306/3307/g'/data/mysql3307/conf/my.cnf
[ root@mysql-node ~]# sed -i 's/3306/3308/g'/data/mysql3308/conf/my.cnf  
[ root@mysql-node ~]# chown -R mysql.mysql /data/mysql*

Initialize two instances

[ root@mysql-node ~]# mysqld --defaults-file=/data/mysql3307/conf/my.cnf  --initialize-insecure  --user=mysql 
[ root@mysql-node ~]# mysqld --defaults-file=/data/mysql3308/conf/my.cnf  --initialize-insecure  --user=mysql

Then start mysqld

[ root@mysql-node ~]# mysqld_safe --defaults-file=/data/mysql3307/conf/my.cnf &[root@mysql-node ~]# mysqld_safe --defaults-file=/data/mysql3308/conf/my.cnf &

Check whether the startup is successful

[ root@mysql-node ~]# ps -ef|grep mysql
mysql    239961014:37?00:00:00/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/conf/my.cnf
mysql    2474323996014:38?00:00:17/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/conf/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/data/mysql/log/mysqld.err --open-files-limit=65535--pid-file=/data/mysql/tmp/mysqld.pid --socket=/data/mysql/tmp/mysqld.sock --port=3306
root     3047323727015:33 pts/000:00:00/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql3307/conf/my.cnf
mysql    31191304731715:33 pts/000:00:02/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql3307/conf/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql3307/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql3307/log/mysqld.err --open-files-limit=65535--pid-file=/data/mysql3307/tmp/mysqld.pid --socket=/data/mysql3307/tmp/mysqld.sock --port=3307
root     3125423727015:33 pts/000:00:00/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql3308/conf/my.cnf
mysql    31977312543915:33 pts/000:00:02/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql3308/conf/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql3308/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql3308/log/mysqld.err --open-files-limit=65535--pid-file=/data/mysql3308/tmp/mysqld.pid --socket=/data/mysql3308/tmp/mysqld.sock --port=3308
root     3204423727015:34 pts/000:00:00 grep --color=auto mysql
 
[ root@mysql-node ~]# lsof -i:3307
COMMAND   PID  USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
mysqld  31191 mysql   22u  IPv6 23144844      0t0  TCP *:opsession-prxy(LISTEN)[root@mysql-node ~]# lsof -i:3308
COMMAND   PID  USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
mysqld  31977 mysql   22u  IPv6 23145727      0t0  TCP *:tns-server(LISTEN)[root@mysql-node ~]# lsof -i:3306
COMMAND   PID  USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
mysqld  24743 mysql   23u  IPv6 23132988      0t0  TCP *:mysql(LISTEN)

[ root@mysql-node ~]# mysql -S /data/mysql3307/tmp/mysqld.sock
............
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK,0 rows affected(0.11 sec)
 
mysql> flush privileges;
Query OK,0 rows affected(0.11 sec)

Log out, log in with new password

[ root@mysql-node ~]# mysql -uroot -S /data/mysql3307/tmp/mysqld.sock -p123456   
.............
mysql>

Similarly, log in to the 3308 port instance and set a password

[ root@mysql-node ~]# mysql -S /data/mysql3308/tmp/mysqld.sock
...........
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK,0 rows affected(0.13 sec)
 
mysql> flush privileges;
Query OK,0 rows affected(0.03 sec)

Log out, log in with new password

[ root@mysql-node ~]# mysql -uroot -S /data/mysql3308/tmp/mysqld.sock -p123456
....................
mysql>

3306, 3307, 3308 The startup commands for the three port instances are:

mysqld_safe --defaults-file=/data/mysql/conf/my.cnf &
mysqld_safe --defaults-file=/data/mysql3307/conf/my.cnf &
mysqld_safe --defaults-file=/data/mysql3308/conf/my.cnf &

The login commands are:

mysql -uroot -S /data/mysql/tmp/mysqld.sock -p123456
mysql -uroot -S /data/mysql3307/tmp/mysqld.sock -p123456
mysql -uroot -S /data/mysql3308/tmp/mysqld.sock -p123456

However, in order to solve the problem of repeated installations, the multi-instance installation method has been specially edited into a script. Readers in need can directly reply to MySQL8 in the background of this official account to obtain the multi-instance installation script.

Some pits stepped on during the use of Mysql8.0

1. User creation and authorization The creation and authorization of users in mysql8.0 are different from before. In fact, strictly speaking, it cannot be said to be different. It can only be said to be stricter. mysql8.0 needs to create users first (when creating users, Bring @ and specify the address, then the address when grant authorization is specified after this @!, otherwise the grant authorization will report an error!) and set the password before authorization.

mysql> create user 'kevin'@'%' identified by '123456';
Query OK,0 rows affected(0.04 sec)
 
mysql> grant all privileges on *.* to 'kevin'@'%'with grant option;   
Query OK,0 rows affected(0.04 sec)
 
mysql> create user 'bobo'@'%' identified by '123456';    
Query OK,0 rows affected(0.06 sec)
 
mysql> grant all privileges on *.* to 'bobo'@'%'with grant option;
Query OK,0 rows affected(0.03 sec)
 
mysql> flush privileges;
Query OK,0 rows affected(0.04 sec)
 
mysql> select host,user,authentication_string from mysql.user;+-----------+------------------+------------------------------------------------------------------------+| host      | user             | authentication_string                                                  |+-----------+------------------+------------------------------------------------------------------------+|%| bobo             | $A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1  ||%| kevin            | $A$005$hy`U}ZB#R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85       || localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | root             | $A$005$/VO_y^7,]6;2qxggBLmJzhA0Qylu5/AHuRScZ/ykKedgZKh/6krOIzPs2       |+-----------+------------------+------------------------------------------------------------------------+

If you still use the direct authorization method of Mysql5.7 and previous versions, an error will be reported:

mysql> grant all privileges on *.* to 'shibo'@'%' identified by '123456';
ERROR 1064(42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '123456'' at line 1

1. Mysql8.0 default is not to use the root account for remote login! The root account can only log in locally!

mysql> select host,user,authentication_string from mysql.user;+-----------+------------------+------------------------------------------------------------------------+| host      | user             | authentication_string                                                  |+-----------+------------------+------------------------------------------------------------------------+|%| bobo             | $A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1 ||%| kevin            | $A$005$hy`U}ZB#R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85 || localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | root             | $A$005$/VO_y^7,]6;2qxggBLmJzhA0Qylu5/AHuRScZ/ykKedgZKh/6krOIzPs2 |+-----------+------------------+------------------------------------------------------------------------+6 rows inset(0.00 sec)

If you want to log in remotely, you need to update the permissions of the root account

mysql> update mysql.user set host='%' where user="root";
Query OK,1 row affected(0.10 sec)
Rows matched:1  Changed:1  Warnings:0
 
mysql> flush privileges;
Query OK,0 rows affected(0.14 sec)
 
mysql> select host,user,authentication_string from mysql.user;+-----------+------------------+------------------------------------------------------------------------+| host      | user             | authentication_string                                                  |+-----------+------------------+------------------------------------------------------------------------+|%| bobo             | $A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1  ||%| kevin            | $A$005$hy`U}ZB#R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85       ||%| root             | $A$005$/VO_y^7,]6;2qxggBLmJzhA0Qylu5/AHuRScZ/ykKedgZKh/6krOIzPs2       || localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |+-----------+------------------+------------------------------------------------------------------------+6 rows inset(0.00 sec)

In this way, you can use the root account to log in to the mysql8.0 database remotely

After modifying the permissions of the root account and allowing the root account to log in remotely, when using Navicat to remotely connect to mysql, a pop-up error message appeared:

The reason for this is that the encryption rule in the version before mysql8 is mysql_native_password, and after mysql8, the encryption rule is cache_sha2_password, there are two ways to solve the problem:

One is to upgrade the navicat driver;

One is to restore the mysql user login password encryption rule to mysql_native_password; here we choose the second method to solve:

# Modify encryption rules
mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;       
Query OK,0 rows affected(0.16 sec)
 
# Update the user&#39;s password
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK,0 rows affected(0.08 sec)
 
# Refresh permissions
mysql> FLUSH PRIVILEGES;
Query OK,0 rows affected(0.03 sec)

This way the problem is solved.

1、 When using the sqlyog link, there will be an exception of 2058. At this time, we need to modify mysql, log in to mysql from the command line (the same as in the modified password, use the modified password), and then execute the following command: mysql> ALTER USER'root'@ 'localhost' IDENTIFIED WITH mysql_native_password BY'password'; where password is the password you modified. Then reconnect in SQLyog, the connection can be successful, OK.
2、 If an error is reported: ERROR 1396 (HY000): Operation ALTER USER failed for'root'@'localhost' then use the following command: mysql> ALTER USER'root'@'%' IDENTIFIED WITH mysql_native_password BY'password';

Sqlyog link 2058 exception

Modify the default encoding The default encoding of mysql8.0 is utf8mb4, so there is no need to modify it when using it, you can use the following command to view:

mysql > SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

If you need to modify other encoding methods, such as utf8mb4, you can use the following methods:

Modify the mysql configuration file my.cnf,After you find it, please add the following content in the following three parts:
[ client]default-character-set= utf8mb4
[ mysql]default-character-set= utf8mb4
[ mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

Then restart the mysqld service, where:

character_set_client(The character set used by the client source data)character_set_connection(Connection layer character set)character_set_database(The default character set of the currently selected database)character_set_results(Query result character set)character_set_server(The default internal operation character set)

In the database connection parameters:

characterEncoding=utf8 will be automatically recognized as utf8mb4, or it can be automatically detected without adding this parameter.
While autoReconnect=true must be added.

1. Some parameter configuration query commands

# Query mysql maximum connection number setting
mysql> show global variables like 'max_conn%';
mysql> SELECT @@MAX_CONNECTIONS AS 'Max Connections';
 
# View the maximum number of links
mysql> show global status like 'Max_used_connections';
 
# Check whether the slow query log is enabled and the log location
mysql> show variables like 'slow_query%';
 
# View the timeout recording time of the slow query log
mysql> show variables like 'long_query_time';
 
# View the link creation and the number of links currently in progress
mysql> show status like 'Threads%';
 
# View current database link
mysql> show processlist;

# View database configuration
mysql> show variables like '%quer%';

Author: flashy cleared ** Source: https: //www.cnblogs.com/kevingrace/p/10482469.html*