CentOS中怎么安装部署MySQL 8.0

2025-03-27 技术教程

本篇文章给大家分享的是有关CentOS中怎么安装部署MySQL 8.0，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

Mysql8.0安装 (YUM方式)

1. 首先删除系统默认或之前可能安装的其他版本的mysql

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

2. 安装Mysql8.0 的yum资源库

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

3. 安装Mysql8.0

[root@DB-node01~]#yuminstallmysql-community-server#启动MySQL服务器和MySQL的自动启动[root@DB-node01~]#systemctlstartmysqld[root@DB-node01~]#systemctlenablemysqld

4. 使用默认密码初次登录后, 必须要重置密码

查看默认密码,如下默认密码为"e53xDalx.*dE"[root@DB-node01~]#grep'temporarypassword'/var/log/mysqld.log2019-03-06T01:53:19.897262Z5[Note][MY-010454][Server]Atemporarypasswordisgeneratedforroot@localhost:e53xDalx.*dE[root@DB-node01~]#mysql-pe53xDalx.*dE............mysql>selectversion();ERROR1820(HY000):YoumustresetyourpasswordusingALTERUSERstatementbeforeexecutingthisstatement.

报错提示必须要重置初始密码, 下面开始重置mysql登录密码（注意要切换到mysql数据库，使用use mysql）

mysql>usemysql;ERROR1820(HY000):YoumustresetyourpasswordusingALTERUSERstatementbeforeexecutingthisstatement.mysql>ALTERUSER'root'@'localhost'IDENTIFIEDBY'123456';ERROR1819(HY000):Yourpassworddoesnotsatisfythecurrentpolicyrequirements

这个其实与validate_password_policy的值有关, mysql8.0更改了validate_password_policy相关的配置名称, 这跟Mysql5.7有点不一样了.

mysql>setglobalvalidate_password.policy=0;QueryOK,0rowsaffected(0.00sec)mysql>setglobalvalidate_password.length=1;QueryOK,0rowsaffected(0.00sec)

接着再修改密码

mysql>ALTERUSER'root'@'localhost'IDENTIFIEDBY'123456';QueryOK,0rowsaffected(0.05sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.03sec)

退出, 重新使用新密码登录mysql

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

查看服务端口

查看mysql连接的授权信息

mysql>selecthost,user,passwordfrommysql.user;ERROR1054(42S22):Unknowncolumn'password'in'fieldlist'

上面这是mysql5.6及以下版本的查看命令, mysql5.7之后的数据库里mysql.user表里已经没有password这个字段了，password字段改成了authentication_string。

mysql>selecthost,user,authentication_stringfrommysql.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|+-----------+------------------+------------------------------------------------------------------------+4rowsinset(0.00sec)

mysql8.0修改用户密码命令

mysql>usemysql;mysql>ALTERUSER'root'@'localhost'IDENTIFIEDBY'123456';mysql>flushprivileges;

Mysql8.0安装 (二进制方式)

1. 首先删除系统默认或之前可能安装的其他版本的mysql

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

2. 安装需要的软件包

[root@mysql8-node~]#yum-yinstalllibaio[root@mysql8-node~]#yum-yinstallnet-tools

3. 下载并安装Mysql8.0.12

[root@mysql8-node~]#groupaddmysql[root@mysql8-node~]#useradd-gmysqlmysql[root@mysql8-node~]#cd/usr/local/src/[root@mysql-nodesrc]#ll-rw-r--r--1rootroot620389228Aug222018mysql8.0.12_bin_centos7.tar.gz[root@mysql-nodesrc]#tar-zvxfmysql8.0.12_bin_centos7.tar.gz[root@mysql-nodesrc]#mvmysql/usr/local/[root@mysql-nodesrc]#chown-Rmysql.mysql/usr/local/mysql[root@mysql-nodesrc]#vim/home/mysql/.bash_profileexportPATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH[root@mysql-nodesrc]#source/home/mysql/.bash_profile[root@mysql-nodesrc]#echo"PATH=$PATH:/usr/local/mysql/bin">>/etc/profile[root@mysql-nodesrc]#source/etc/profile

4. 创建数据目录

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

5. 配置mysql

[root@mysql-nodesrc]#su-mysql[mysql@mysql-node~]$vim/data/mysql/conf/my.cnf[mysqld]lower_case_table_names=1user=mysqlserver_id=1port=3306default-time-zone='+08:00'enforce_gtid_consistency=ONgtid_mode=ONbinlog_checksum=nonedefault_authentication_plugin=mysql_native_passworddatadir=/data/mysql/datapid-file=/data/mysql/tmp/mysqld.pidsocket=/data/mysql/tmp/mysqld.socktmpdir=/data/mysql/tmp/skip-name-resolve=ONopen_files_limit=65535table_open_cache=2000#################innodb########################innodb_data_home_dir=/data/mysql/datainnodb_data_file_path=ibdata1:512M;ibdata2:512M:autoextendinnodb_buffer_pool_size=12000Minnodb_flush_log_at_trx_commit=1innodb_io_capacity=600innodb_lock_wait_timeout=120innodb_log_buffer_size=8Minnodb_log_file_size=200Minnodb_log_files_in_group=3innodb_max_dirty_pages_pct=85innodb_read_io_threads=8innodb_write_io_threads=8innodb_thread_concurrency=32innodb_file_per_tableinnodb_rollback_on_timeoutinnodb_undo_directory=/data/mysql/datainnodb_log_group_home_dir=/data/mysql/data###################session###########################join_buffer_size=8Mkey_buffer_size=256Mbulk_insert_buffer_size=8Mmax_heap_table_size=96Mtmp_table_size=96Mread_buffer_size=8Msort_buffer_size=2Mmax_allowed_packet=64Mread_rnd_buffer_size=32M############logset###################log-error=/data/mysql/log/mysqld.errlog-bin=/data/mysql/binlog/binloglog_bin_index=/data/mysql/binlog/binlog.indexmax_binlog_size=500Mslow_query_log_file=/data/mysql/log/slow.logslow_query_log=1long_query_time=10log_queries_not_using_indexes=ONlog_throttle_queries_not_using_indexes=10log_slow_admin_statements=ONlog_output=FILE,TABLEmaster_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:3306COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAMEmysqld24743mysql23uIPv6231329880t0TCP*:mysql(LISTEN)

8. 登录mysql, 重置密码

本地首次使用sock文件登录mysql是不需要密码的[mysql@mysql-node~]#mysql-S/data/mysql/tmp/mysqld.sock.............mysql>ALTERUSER'root'@'localhost'IDENTIFIEDBY'123456';QueryOK,0rowsaffected(0.07sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.03sec)mysql>selecthost,user,authentication_stringfrommysql.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|+-----------+------------------+------------------------------------------------------------------------+4rowsinset(0.00sec)

退出, 此时密码重置后, 就不能使用sock文件无密码登录了

[root@mysql-node~]#mysql-S/data/mysql/tmp/mysqld.sockERROR1045(28000):Accessdeniedforuser'root'@'localhost'(usingpassword:NO)[root@mysql-node~]#mysql-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.ERROR2002(HY000):Can'tconnecttolocalMySQLserverthroughsocket'/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>selectversion();+-----------+|version()|+-----------+|8.0.12|+-----------+1rowinset(0.00sec)#授予用户权限.必须先要创建用户,才能授权!!(创建用户时要带@并指定地址,则grant授权时的地址就是这个@后面指定的!,否则grant授权就会报错!)mysql>createuser'kevin'@'%'identifiedby'123456';QueryOK,0rowsaffected(0.11sec)mysql>grantallprivilegeson*.*to'kevin'@'%'withgrantoption;QueryOK,0rowsaffected(0.21sec)mysql>selecthost,user,authentication_stringfrommysql.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|+-----------+------------------+------------------------------------------------------------------------+5rowsinset(0.00sec)mysql>updatemysql.usersethost='172.16.60.%'whereuser="kevin";QueryOK,1rowaffected(0.16sec)Rowsmatched:1Changed:1Warnings:0mysql>flushprivileges;QueryOK,0rowsaffected(0.05sec)mysql>selecthost,user,authentication_stringfrommysql.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|+-------------+------------------+------------------------------------------------------------------------+5rowsinset(0.00sec)mysql>createuser'bobo'@'172.16.60.%'identifiedby'123456';QueryOK,0rowsaffected(0.09sec)mysql>grantallprivilegeson*.*to'bobo'@'172.16.60.%';QueryOK,0rowsaffected(0.17sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.04sec)mysql>selecthost,user,authentication_stringfrommysql.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|+-------------+------------------+------------------------------------------------------------------------+6rowsinset(0.00sec)mysql>showgrantsforkevin@'172.16.60.%';+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|Grantsforkevin@172.16.60.%|+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP,RELOAD,SHUTDOWN,PROCESS,FILE,REFERENCES,INDEX,ALTER,SHOWDATABASES,SUPER,CREATETEMPORARYTABLES,LOCKTABLES,EXECUTE,REPLICATIONSLAVE,REPLICATIONCLIENT,CREATEVIEW,SHOWVIEW,CREATEROUTINE,ALTERROUTINE,CREATEUSER,EVENT,TRIGGER,CREATETABLESPACE,CREATEROLE,DROPROLEON*.*TO`kevin`@`172.16.60.%`WITHGRANTOPTION|+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)

MySQL单机多实例安装配置

通过上面二进制部署可知, 已经起来一个3306端口的MySQL实例, 现在需要再起来两个实例, 分别为3307, 3308. 操作如下:

创建实例的数据目录

[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-Rmysql.mysql/data/mysql3307[root@mysql-node~]#chown-Rmysql.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-Rmysql.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|grepmysqlmysql239961014:37?00:00:00/bin/sh/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/mysql/conf/my.cnfmysql2474323996014: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=3306root3047323727015:33pts/000:00:00/bin/sh/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/mysql3307/conf/my.cnfmysql31191304731715:33pts/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=3307root3125423727015:33pts/000:00:00/bin/sh/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/mysql3308/conf/my.cnfmysql31977312543915:33pts/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=3308root3204423727015:34pts/000:00:00grep--color=automysql[root@mysql-node~]#lsof-i:3307COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAMEmysqld31191mysql22uIPv6231448440t0TCP*:opsession-prxy(LISTEN)[root@mysql-node~]#lsof-i:3308COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAMEmysqld31977mysql22uIPv6231457270t0TCP*:tns-server(LISTEN)[root@mysql-node~]#lsof-i:3306COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAMEmysqld24743mysql23uIPv6231329880t0TCP*:mysql(LISTEN)

登录3307端口实例, 并设置密码

[root@mysql-node~]#mysql-S/data/mysql3307/tmp/mysqld.sock............mysql>ALTERUSER'root'@'localhost'IDENTIFIEDBY'123456';QueryOK,0rowsaffected(0.11sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.11sec)

退出, 使用新密码登录

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

同理, 登录3308端口实例, 并设置密码

[root@mysql-node~]#mysql-S/data/mysql3308/tmp/mysqld.sock...........mysql>ALTERUSER'root'@'localhost'IDENTIFIEDBY'123456';QueryOK,0rowsaffected(0.13sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.03sec)

退出, 使用新密码登录

[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-p123456mysql-uroot-S/data/mysql3307/tmp/mysqld.sock-p123456mysql-uroot-S/data/mysql3308/tmp/mysqld.sock-p123456

不过为了解决大家平时重复安装的问题，特意将多实例安装方法编辑成脚本了，有需要的读者可以在本公众号后台直接回复 MySQL8 获取多实例安装脚本。

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

1)创建用户和授权在mysql8.0创建用户和授权和之前不太一样了，其实严格上来讲，也不能说是不一样, 只能说是更严格, mysql8.0需要先创建用户(创建用户时要带@并指定地址, 则grant授权时的地址就是这个@后面指定的!, 否则grant授权就会报错!)和设置密码,然后才能授权。

mysql>createuser'kevin'@'%'identifiedby'123456';QueryOK,0rowsaffected(0.04sec)mysql>grantallprivilegeson*.*to'kevin'@'%'withgrantoption;QueryOK,0rowsaffected(0.04sec)mysql>createuser'bobo'@'%'identifiedby'123456';QueryOK,0rowsaffected(0.06sec)mysql>grantallprivilegeson*.*to'bobo'@'%'withgrantoption;QueryOK,0rowsaffected(0.03sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.04sec)mysql>selecthost,user,authentication_stringfrommysql.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及之前版本的直接授权的方法, 会有报错:

mysql>grantallprivilegeson*.*to'shibo'@'%'identifiedby'123456';ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'identifiedby'123456''atline1

2)Mysql8.0默认是不能使用root账号进行远程登录的! root账号只能本地登录!

mysql>selecthost,user,authentication_stringfrommysql.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|+-----------+------------------+------------------------------------------------------------------------+6rowsinset(0.00sec)

如果想要远程登录, 则需要进行update更新下root账号的权限

mysql>updatemysql.usersethost='%'whereuser="root";QueryOK,1rowaffected(0.10sec)Rowsmatched:1Changed:1Warnings:0mysql>flushprivileges;QueryOK,0rowsaffected(0.14sec)mysql>selecthost,user,authentication_stringfrommysql.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|+-----------+------------------+------------------------------------------------------------------------+6rowsinset(0.00sec)

这样就能在远程使用root账号登录该mysql8.0的数据库了

3.修改root账号权限, 允许root账号远程登录后, 用navicat进行mysql的远程连接时，出现了弹窗报错：出现这个原因是mysql8 之前的版本中加密规则是mysql_native_password, 而在mysql8之后,加密规则是caching_sha2_password, 解决问题方法有两种:

1. 一种是升级navicat驱动;

2. 一种是把mysql用户登录密码加密规则还原成mysql_native_password; 这里选择第二种方法来解决:

#修改加密规则mysql>ALTERUSER'root'@'%'IDENTIFIEDBY'123456'PASSWORDEXPIRENEVER;QueryOK,0rowsaffected(0.16sec)#更新一下用户的密码mysql>ALTERUSER'root'@'%'IDENTIFIEDWITHmysql_native_passwordBY'123456';QueryOK,0rowsaffected(0.08sec)#刷新权限mysql>FLUSHPRIVILEGES;QueryOK,0rowsaffected(0.03sec)

这样问题就解决了。

1、使用sqlyog链接时会出现2058的异常，此时我们需要修改mysql，命令行登录mysql（与修改密码中登录相同，使用修改后的密码），然后执行下面的命令：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';

4. sqlyog链接时出现2058异常

5. 修改默认编码方式 mysql8.0默认编码方式为utf8mb4，因此使用时不需要修改，可使用如下命令查看：

mysql>SHOWVARIABLESWHEREVariable_nameLIKE'character_set_%'ORVariable_nameLIKE'collation%';

如果需要修改其他编码方式，比如需要修改为utf8mb4，可以使用如下方式：

修改mysql配置文件my.cnf,找到后请在以下三部分里添加如下内容：[client]default-character-set=utf8mb4[mysql]default-character-set=utf8mb4[mysqld]character-set-client-handshake=FALSEcharacter-set-server=utf8mb4collation-server=utf8mb4_unicode_ciinit_connect='SETNAMESutf8mb4'

然后重启mysqld服务即可, 其中:

character_set_client(客户端来源数据使用的字符集)character_set_connection(连接层字符集)character_set_database(当前选中数据库的默认字符集)character_set_results(查询结果字符集)character_set_server(默认的内部操作字符集)

数据库连接参数中:

characterEncoding=utf8会被自动识别为utf8mb4，也可以不加这个参数，会自动检测。而autoReconnect=true是必须加上的。

6)部分参数配置查询命令

#查询mysql最大连接数设置mysql>showglobalvariableslike'max_conn%';mysql>SELECT@@MAX_CONNECTIONSAS'MaxConnections';#查看最大链接数mysql>showglobalstatuslike'Max_used_connections';#查看慢查询日志是否开启以及日志位置mysql>showvariableslike'slow_query%';#查看慢查询日志超时记录时间mysql>showvariableslike'long_query_time';#查看链接创建以及现在正在链接数mysql>showstatuslike'Threads%';#查看数据库当前链接mysql>showprocesslist;#查看数据库配置mysql>showvariableslike'%quer%';

以上就是CentOS中怎么安装部署MySQL 8.0，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。