How to install and deploy MySQL 8.0 under CentOS8

MySQL 8 official version 8.0.11 has been released. Officials say MySQL 8 is 2 times faster than MySQL 5.7 and brings a lot of improvements and faster performance! Who is the best after all? Please see: MySQL 5.7 vs 8.0, which one has better performance?

MySQL 8.0 installation (YUM method)

1. First, delete the system default or other versions of MySQL that may have been installed before.

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

2. Install the yum repository of Mysql8.0

mysql80-community-release-el7-1.noarch.rpm  
 
# yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

insert image description here

3. Install MySQL 8.0

# yum install mysql-community-server
  
#Start the MySQL server and MySQL automatic startup# systemctl start mysqld
# systemctl enable mysqld
# systemctl status mysqld

insert image description here

[root@localhost opt]# netstat -lantp | grep 3306
tcp6 0 0 :::33060 :::* LISTEN 25431/mysqld    
tcp6 0 0 :::3306 :::* LISTEN 25431/mysqld    
[root@localhost opt]# ps -aux | grep mysqld
mysql 25431 0.8 17.2 1776932 350232 ? Ssl 16:24 0:01 /usr/sbin/mysqld
root 25672 0.0 0.0 112828 980 pts/1 S+ 16:28 0:00 grep --color=auto mysqld

insert image description here

Skip password login, add skip-grant-tables, and restart the MySQL service.

[root@localhost opt]# vim /etc/my.cnf
[mysqld]
skip-grant-tables

[root@localhost opt]# systemctl restart mysqld
[root@localhost opt]#mysql

insert image description here

Use sql to modify the root password

Enter the terminal, type the mysql -u root -p command and press Enter. When you need to enter a password, just press Enter to log in to the database without a password.

mysql> update user set password=password("your new password") where user="root";
Or mysql> set password for 'username'@'host' = password('newpassword') 
mysql> flush privileges;
mysql> quit

insert image description here

Notice
The set password for 'username'@'host' = password('newpassword') command changes the new password.

If the ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement error occurs when executing this step. Run the flush privileges command, and then run this command.

Note: If the error ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement appears when executing this step. Run the flush privileges command, and then run this command.

4. After logging in for the first time with the default password, you must reset the password

Check the default password, the default password is "e53xDalx.*dE"
[root@DB-node01 ~]# grep 'temporary password' /var/log/mysqld.log
2019-03-06T01: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.

The error message indicates that the initial password must be reset. Now start to reset the MySQL login password (note that you need to switch to the MySQL database and 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. MySQL 8.0 changed the configuration name related to validate_password_policy, which is a bit different from MySQL 5.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)

insert image description here

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 and log in to mysql again with the new password

#mysql -p123456
...........
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15 |
+-----------+
1 row in set (0.00 sec)

View service port

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

insert image description here

View the authorization information for MySQL connection

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

insert image description here

The above is the view command for MySQL 5.6 and below. In the database after MySQL 5.7, there is no password field in the mysql.user table. The password field has been 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 in set (0.00 sec)

insert image description here

mysql8.0 change user password command

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

MySQL 8.0 installation (binary mode)

1. First, delete the system default or other versions of MySQL that may have been installed 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

2. Install the required software packages

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

3. Download and install MySQL 8.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 22 2018 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

4. Create a 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

5. 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 = 3306
 
default-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

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

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

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

8. Log in to mysql and reset your password

The first time you use the sock file to log in to mysql locally, no password is required [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 in set (0.00 sec)

Exit. After the password is reset, you can no longer use the sock file to log in without a 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 interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Make a soft link to the sock file

[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 in set (0.00 sec)
 
#Grant user permissions. You must create a user before you can grant permissions!!
(When creating a user, you must include @ and specify an address. The address for grant authorization is the address specified after this @! 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 in set (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 in set (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 in set (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 in set (0.00 sec)

MySQL single machine multi-instance installation and configuration

From the above binary deployment, we can see that a MySQL instance with port 3306 has been started. Now we need to start two more instances, 3307 and 3308. The operation is as follows:

Create the instance data directory

[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 23996 1 0 14:37 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/conf/my.cnf
mysql 24743 23996 0 14: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 30473 23727 0 15:33 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql3307/conf/my.cnf
mysql 31191 30473 17 15:33 pts/0 00: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 31254 23727 0 15:33 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql3308/conf/my.cnf
mysql 31977 31254 39 15:33 pts/0 00: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 32044 23727 0 15:34 pts/0 00: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>

The startup commands for the three port instances 3306, 3307, and 3308 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 installation, the multi-instance installation method is 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 pitfalls encountered when using MySQL 8.0

1) Creating users and authorizations in MySQL 8.0 are different from before. Strictly speaking, it cannot be said to be different, but it can only be said to be stricter. MySQL 8.0 requires you to create a user (use @ and specify an address when creating a user, then the address for grant authorization is the address specified after this @!, otherwise grant authorization will report an error!) and set a password before authorization can be performed.

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 MySQL 5.7 and earlier versions, you will get an error:

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

2) By default, Mysql8.0 cannot 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 in set (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 in set (0.00 sec)

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

After modifying the root account permissions and allowing the root account to log in remotely, a pop-up window error appears when using navicat to connect to mysql remotely:

insert image description here

The reason for this is that the encryption rule in versions before MySQL 8 is mysql_native_password, and after MySQL 8, the encryption rule is caching_sha2_password. There are two ways to solve the problem:

1. One is to upgrade the navicat driver;

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

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

This solves the problem.

1. When using sqlyog to link, an exception 2058 will occur. At this time, we need to modify mysql. Log in to mysql through the command line (the same as logging in to change the 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 changed. Then reconnect in SQLyog, and the connection will be successful, OK.

2. If the error message is: ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost', use the following command: mysql > ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
2058 exception occurred when sqlyog was linked

Modify the default encoding method. The default encoding method of mysql8.0 is utf8mb4, so it does not need to be modified when used. You can use the following command to view it:

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

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

Modify the MySQL configuration file my.cnf. After finding 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 (character set used by client source data)
character_set_connection (Connection Level Character Set)
character_set_database (the default character set for the currently selected database)
character_set_results (Query Result Character Set)
character_set_server (default character set for internal operations)

In the database connection parameters:

characterEncoding=utf8 will be automatically recognized as utf8mb4. You can also omit this parameter and it will be automatically detected.
And autoReconnect=true is a must.

6) Some parameter configuration query commands

#Query the maximum number of connections in MySQL mysql> show global variables like 'max_conn%';
mysql> SELECT @@MAX_CONNECTIONS AS 'Max Connections';
 
# View the maximum number of connections mysql> show global status like 'Max_used_connections';
 
# Check whether the slow query log is enabled and the log locationmysql> show variables like 'slow_query%';
 
# View the timeout record of the slow query logmysql> show variables like 'long_query_time';
 
# View the number of links created and currently connected mysql> show status like 'Threads%';
 
# View the current database connection mysql> show processlist;

# View database configurationmysql> show variables like '%quer%';

Reference Links:

Source: https://www.cnblogs.com/kevingrace/p/10482469.html

MySQL 8.0 installation and deployment under CentOS: https://mp.weixin.qq.com/s/OQaL0T-jT2xfsJBulau7sQ

https://blog.csdn.net/vv19910825/article/details/82979563

This is the end of this article about how to install and deploy MySQL 8.0 under CentOS8. For more relevant MySQL installation and deployment content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

MySQL high availability cluster deployment and failover implementation
Example of deploying MySQL on Docker
Docker deploys mysql remote connection to solve 2003 problems
Implementation of docker-compose deployment project based on MySQL8
mysql-canal-rabbitmq installation and deployment super detailed tutorial
Detailed deployment steps for MySQL MHA high availability configuration and failover
Steps to build MHA architecture deployment in MySQL
Installation and deployment of MySQL Router

<<: How to install kibana tokenizer inside docker container

>>: Simple steps to encapsulate components in Vue projects

Detailed steps for debugging VUE projects in IDEA

How to install and deploy MySQL 8.0 under CentOS8

Detailed steps for debugging VUE projects in IDEA

Implementation of Nginx configuration of multi-port and multi-domain name access

HTML+CSS+JavaScript to achieve list loop scrolling example code

Vue implements three-level navigation display and hiding

Summary of some HTML code writing style suggestions

Detailed example of deploying Nginx+Apache dynamic and static separation

Brief Analysis of MySQL B-Tree Index

Detailed tutorial on docker-compose deployment and configuration of Jenkins

How to modify Flash SWF files in web pages

A brief discussion on the difference between Mysql primary key index and non-primary key index

Recommend

Problems with nodejs + koa + typescript integration and automatic restart

Introduction to CSS BEM Naming Standard (Recommended)

JavaScript singleton mode to implement custom pop-up box

Markup Language - Image Replacement

jQuery solves the problem of not executing the original event after adding elements

How to backup MySQL regularly and upload it to Qiniu

Vue implements seamless scrolling of lists

How to fix some content in a fixed position when scrolling HTML page

24 Practical JavaScript Development Tips

Native JS implements a very good-looking counter

Web realizes the code of popping up the window of uploading files by clicking the picture

Detailed explanation of using Nginx reverse proxy to solve cross-domain problems

Timeline implementation method based on ccs3

Detailed explanation of outfile, dumpfile, load_file functions in MySQL injection

How to set up scheduled backup tasks in Linux centos