欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

CentOS8下MySQL 8.0安裝部署的方法

 更新時間：2020年11月04日 14:20:53   作者：寰宇001  
這篇文章主要介紹了CentOS 8下 MySQL 8.0 安裝部署的方法，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧

MySQL 8正式版8.0.11已發(fā)布，官方表示MySQL8要比MySQL 5.7快2倍，還帶來了大量的改進和更快的性能！到底誰最牛呢？請看：MySQL 5.7 vs 8.0，哪個性能更牛？

Mysql8.0安裝 (YUM方式)

1、首先刪除系統(tǒng)默認或之前可能安裝的其他版本的mysql

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

2、安裝Mysql8.0 的yum資源庫

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

在這里插入圖片描述

在這里插入圖片描述

在這里插入圖片描述

3、安裝Mysql8.0

# yum install mysql-community-server
  
#啟動MySQL服務(wù)器和MySQL的自動啟動
# systemctl start mysqld
# systemctl enable mysqld
# systemctl status mysqld

在這里插入圖片描述

[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

在這里插入圖片描述

登錄報錯

在這里插入圖片描述

跳過密碼登錄，添加skip-grant-tables，然后重啟MySQL服務(wù)。

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

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

在這里插入圖片描述

在這里插入圖片描述

用sql來修改root的密碼

進入到終端當中，敲入 mysql -u root -p 命令然后回車，當需要輸入密碼時，直接按enter鍵，便可以不用密碼登錄到數(shù)據(jù)庫當中

mysql> update user set password=password("你的新密碼") where user="root";
或者
mysql> set password for 'username'@'host' = password('newpassword') 
mysql> flush privileges;
mysql> quit

在這里插入圖片描述

注意
set password for ‘username'@‘host' = password(‘newpassword') 命令修改新的密碼。

如果在執(zhí)行該步驟的時候出現(xiàn)ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement 錯誤。則執(zhí)行下 flush privileges 命令，再執(zhí)行該命令即可。

注意：如果在執(zhí)行該步驟的時候出現(xiàn)ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement 錯誤。則執(zhí)行下 flush privileges 命令，再執(zhí)行該命令即可。

4、使用默認密碼初次登錄后, 必須要重置密碼

查看默認密碼, 如下默認密碼為"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.

報錯提示必須要重置初始密碼, 下面開始重置mysql登錄密碼（注意要切換到mysql數(shù)據(jù)庫，使用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

這個其實與validate_password_policy的值有關(guān), mysql8.0更改了validate_password_policy相關(guān)的配置名稱, 這跟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)

在這里插入圖片描述

接著再修改密碼

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)

退出, 重新使用新密碼登錄mysql

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

查看服務(wù)端口

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

在這里插入圖片描述

查看mysql連接的授權(quán)信息

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

在這里插入圖片描述

上面這是mysql5.6及以下版本的查看命令, mysql5.7之后的數(shù)據(jù)庫里mysql.user表里已經(jīng)沒有password這個字段了，password字段改成了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)

在這里插入圖片描述

mysql8.0修改用戶密碼命令

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

Mysql8.0安裝 (二進制方式)

1、首先刪除系統(tǒng)默認或之前可能安裝的其他版本的mysql

[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、安裝需要的軟件包

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

3、下載并安裝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 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、創(chuàng)建數(shù)據(jù)目錄

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

5、配置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、初始化 (稍等一會兒, 可以到/data/mysql/log/mysqld.err日子里查看初始化過程, 看看有沒有error信息)

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

7、啟動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、登錄mysql, 重置密碼

本地首次使用sock文件登錄mysql是不需要密碼的
[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)

退出, 此時密碼重置后, 就不能使用sock文件無密碼登錄了

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

做sock文件的軟鏈接

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

登錄

[root@mysql-node ~]# mysql -p123456
或者
[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)
 
#授予用戶權(quán)限. 必須先要創(chuàng)建用戶, 才能授權(quán)!!
(創(chuàng)建用戶時要帶@并指定地址, 則grant授權(quán)時的地址就是這個@后面指定的!, 否則grant授權(quán)就會報錯!)
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 kevin@172.16.60.%                                                                                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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單機多實例安裝配置

通過上面二進制部署可知, 已經(jīng)起來一個3306端口的MySQL實例, 現(xiàn)在需要再起來兩個實例, 分別為3307, 3308. 操作如下:

創(chuàng)建實例的數(shù)據(jù)目錄

[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

配置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*

進行初始化兩個實例

[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

接著啟動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 &

查看啟動是否成功

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

登錄3307端口實例, 并設(shè)置密碼

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

退出, 使用新密碼登錄

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

同理, 登錄3308端口實例, 并設(shè)置密碼

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

退出, 使用新密碼登錄

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

3306, 3307, 3308三個端口實例的啟動命令分別為:

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 &

登錄命令分別為:

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

不過為了解決大家平時重復(fù)安裝的問題，特意將多實例安裝方法編輯成腳本了，有需要的讀者可以在本公眾號后臺直接回復(fù) MySQL8 獲取多實例安裝腳本。

Mysql8.0使用過程中踩過的一些坑

1)創(chuàng)建用戶和授權(quán) 在mysql8.0創(chuàng)建用戶和授權(quán)和之前不太一樣了，其實嚴格上來講，也不能說是不一樣, 只能說是更嚴格, mysql8.0需要先創(chuàng)建用戶(創(chuàng)建用戶時要帶@并指定地址, 則grant授權(quán)時的地址就是這個@后面指定的!, 否則grant授權(quán)就會報錯!)和設(shè)置密碼,然后才能授權(quán)。

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

如果還是用Mysql5.7及之前版本的直接授權(quán)的方法, 會有報錯:

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)Mysql8.0默認是不能使用root賬號進行遠程登錄的! root賬號只能本地登錄!

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)

如果想要遠程登錄, 則需要進行update更新下root賬號的權(quán)限

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)

這樣就能在遠程使用root賬號登錄該mysql8.0的數(shù)據(jù)庫了

修改root賬號權(quán)限, 允許root賬號遠程登錄后, 用navicat進行mysql的遠程連接時，出現(xiàn)了彈窗報錯：

在這里插入圖片描述

出現(xiàn)這個原因是mysql8 之前的版本中加密規(guī)則是mysql_native_password, 而在mysql8之后,加密規(guī)則是caching_sha2_password, 解決問題方法有兩種:

1、一種是升級navicat驅(qū)動;

2、一種是把mysql用戶登錄密碼加密規(guī)則還原成mysql_native_password; 這里選擇第二種方法來解決:

#修改加密規(guī)則
mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;    
Query OK, 0 rows affected (0.16 sec)
 
#更新一下用戶的密碼
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.08 sec)
 
#刷新權(quán)限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)

這樣問題就解決了。

1、使用sqlyog鏈接時會出現(xiàn)2058的異常，此時我們需要修改mysql，命令行登錄mysql（與修改密碼中登錄相同，使用修改后的密碼），然后執(zhí)行下面的命令：mysql > ALTER USER ‘root'@‘localhost' IDENTIFIED WITH mysql_native_password BY ‘password'; 其中password為自己修改的密碼。然后SQLyog中重新連接，則可連接成功，OK。

2、如果報錯：ERROR 1396 (HY000): Operation ALTER USER failed for ‘root'@‘localhost'則使用下面命令：mysql > ALTER USER ‘root'@'%' IDENTIFIED WITH mysql_native_password BY ‘password';
sqlyog鏈接時出現(xiàn)2058異常

修改默認編碼方式 mysql8.0默認編碼方式為utf8mb4，因此使用時不需要修改，可使用如下命令查看：

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

如果需要修改其他編碼方式，比如需要修改為utf8mb4，可以使用如下方式：

修改mysql配置文件my.cnf, 找到后請在以下三部分里添加如下內(nèi)容：
[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'

然后重啟mysqld服務(wù)即可, 其中:

character_set_client (客戶端來源數(shù)據(jù)使用的字符集)
character_set_connection   (連接層字符集)
character_set_database  (當前選中數(shù)據(jù)庫的默認字符集)
character_set_results (查詢結(jié)果字符集)
character_set_server (默認的內(nèi)部操作字符集)

數(shù)據(jù)庫連接參數(shù)中:

characterEncoding=utf8 會被自動識別為utf8mb4，也可以不加這個參數(shù)，會自動檢測。
而autoReconnect=true 是必須加上的。

6)部分參數(shù)配置查詢命令

#查詢mysql最大連接數(shù)設(shè)置
mysql> show global variables like 'max_conn%';
mysql> SELECT @@MAX_CONNECTIONS AS 'Max Connections';
 
# 查看最大鏈接數(shù)
mysql> show global status like 'Max_used_connections';
 
# 查看慢查詢?nèi)罩臼欠耖_啟以及日志位置
mysql> show variables like 'slow_query%';
 
# 查看慢查詢?nèi)罩境瑫r記錄時間
mysql> show variables like 'long_query_time';
 
# 查看鏈接創(chuàng)建以及現(xiàn)在正在鏈接數(shù)
mysql> show status like 'Threads%';
 
# 查看數(shù)據(jù)庫當前鏈接
mysql> show processlist;

# 查看數(shù)據(jù)庫配置
mysql> show variables like '%quer%';

參考鏈接 ：

出處：https://www.cnblogs.com/kevingrace/p/10482469.html

CentOS 下 MySQL 8.0 安裝部署 ：https://mp.weixin.qq.com/s/OQaL0T-jT2xfsJBulau7sQ

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

到此這篇關(guān)于CentOS8下MySQL 8.0安裝部署的方法的文章就介紹到這了,更多相關(guān)MySQL安裝部署內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

  • MySQL 搭建MHA架構(gòu)部署的步驟

    MySQL 搭建MHA架構(gòu)部署的步驟

    這篇文章主要介紹了MySQL 搭建MHA架構(gòu)部署的步驟，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
    2021-02-02
  • MySQL?常用引擎總結(jié)分享

    MySQL?常用引擎總結(jié)分享

    這篇文章主要介紹了MySQL?常用引擎總結(jié)分享，MySQL有很多存儲引擎，所謂的存儲引擎是指用于存儲、處理和保護數(shù)據(jù)的核心服務(wù)，更多常用引擎分享，需要的小伙伴可以參考下面文章內(nèi)容
    2022-06-06
  • mysql5.7.18解壓版啟動mysql服務(wù)

    mysql5.7.18解壓版啟動mysql服務(wù)

    這篇文章主要為大家詳細介紹了mysql5.7.18解壓版啟動mysql服務(wù)的相關(guān)資料，具有一定的參考價值，感興趣的小伙伴們可以參考一下
    2017-05-05
  • 解決MySQL錯誤碼:1054 Unknown column ‘**‘ in ‘field list‘的問題

    解決MySQL錯誤碼:1054 Unknown column ‘**‘ in&n

    這篇文章主要介紹了解決MySQL錯誤碼:1054 Unknown column ‘**‘ in ‘field list‘的問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
    2024-05-05
  • CentOS 7中安裝mysql server的步驟分享

    CentOS 7中安裝mysql server的步驟分享

    這篇文章主要介紹了CentOS 7中安裝mysql server的步驟分享,需要的朋友可以參考下
    2015-09-09
  • MySQL安全設(shè)置圖文教程

    MySQL安全設(shè)置圖文教程

    MySQL安全設(shè)置，跟mssql差不多都是以普通用戶權(quán)限運行mysql。其它的也需要注意下。
    2011-01-01
  • mysql數(shù)據(jù)庫表的多條件查詢語句

    mysql數(shù)據(jù)庫表的多條件查詢語句

    這篇文章主要介紹了mysql數(shù)據(jù)庫表的多條件查詢語句,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
    2023-06-06
  • MySQL 權(quán)限控制細節(jié)分析

    MySQL 權(quán)限控制細節(jié)分析

    這篇文章主要介紹了MySQL 權(quán)限控制細節(jié)分析的相關(guān)資料，幫助大家更好的理解和學習使用MySQL，感興趣的朋友可以了解下
    2021-03-03
  • MySQL慢查詢查找和調(diào)優(yōu)測試

    MySQL慢查詢查找和調(diào)優(yōu)測試

    MySQL慢查詢查找和調(diào)優(yōu)測試,接下來詳細介紹，需要了解的朋友可以參考下
    2013-01-01
  • 安裝Mysql時可能會遇到的一些疑難雜癥

    安裝Mysql時可能會遇到的一些疑難雜癥

    這篇文章主要給大家介紹了關(guān)于安裝Mysql時可能會遇到的一些疑難雜癥，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
    2021-01-01

最新評論