首页 资讯频道 互联频道 智能频道 网络 数据频道 安全频道 服务器频道 存储频道

敲黑板!超详细的CentOS下MySQL 8.0安装部署

2020-11-04 11:32:15 来源 : 民工哥技术之路公众号

MySQL 8正式版8.0.11已发布,官方表示MySQL8要比MySQL 5.7快2倍,还带来了大量的改进和更快的性能!到底谁最牛呢?

Mysql8.0安装 (YUM方式)

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

[[email protected]~]#foriin$(rpm-qa|grepmysql);dorpm-e$i--nodeps;done

[[email protected]~]#rm-rf/var/lib/mysql&&rm-rf/etc/my.cnf

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

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

[[email protected]~]#yumlocalinstallhttps://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

3. 安装Mysql8.0

[[email protected]~]#yuminstallmysql-community-server

#启动MySQL服务器和MySQL的自动启动

[[email protected]~]#systemctlstartmysqld

[[email protected]~]#systemctlenablemysqld

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

查看默认密码,如下默认密码为"e53xDalx.*dE"

[[email protected]~]#grep'temporarypassword'/var/log/mysqld.log

2019-03-06T01:53:19.897262Z5[Note][MY-010454][Server][email protected]:e53xDalx.*dE

[[email protected]~]#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

[[email protected]~]#mysql-p123456

...........

mysql>selectversion();

+-----------+

|version()|

+-----------+

|8.0.15|

+-----------+

1rowinset(0.00sec)

查看服务端口

mysql>showglobalvariableslike'port';

+---------------+-------+

|Variable_name|Value|

+---------------+-------+

|port|3306|

+---------------+-------+

1rowinset(0.01sec)

查看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

[[email protected]~]#foriin$(rpm-qa|grepmysql);dorpm-e$i--nodeps;done

[[email protected]~]#rm-rf/var/lib/mysql&&rm-rf/etc/my.cnf

2. 安装需要的软件包

[[email protected]~]#yum-yinstalllibaio

[[email protected]~]#yum-yinstallnet-tools

3. 下载并安装Mysql8.0.12

[[email protected]~]#groupaddmysql

[[email protected]~]#useradd-gmysqlmysql

[[email protected]~]#cd/usr/local/src/

[[email protected]]#ll

-rw-r--r--1rootroot620389228Aug222018mysql8.0.12_bin_centos7.tar.gz

[[email protected]]#tar-zvxfmysql8.0.12_bin_centos7.tar.gz

[[email protected]]#mvmysql/usr/local/

[[email protected]]#chown-Rmysql.mysql/usr/local/mysql

[[email protected]]#vim/home/mysql/.bash_profile

exportPATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH

[[email protected]]#source/home/mysql/.bash_profile

[[email protected]]#echo"PATH=$PATH:/usr/local/mysql/bin">>/etc/profile

[[email protected]]#source/etc/profile

4. 创建数据目录

[[email protected]]#mkdir-p/data/mysql/{data,log,binlog,conf,tmp}

[[email protected]]#chown-Rmysql.mysql/data/mysql

5. 配置mysql

[[email protected]]#su-mysql

[[email protected]~]$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

############logset###################

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信息)

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

7. 启动mysqld

[[email protected]~]$mysqld_safe--defaults-file=/data/mysql/conf/my.cnf&

[[email protected]~]$lsof-i:3306

COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAME

mysqld24743mysql23uIPv6231329880t0TCP*:mysql(LISTEN)

8. 登录mysql, 重置密码

本地首次使用sock文件登录mysql是不需要密码的

[[email protected]~]#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文件无密码登录了

[[email protected]~]#mysql-S/data/mysql/tmp/mysqld.sock

ERROR1045(28000):Accessdeniedforuser'root'@'localhost'(usingpassword:NO)

[[email protected]~]#mysql-p123456

mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.

ERROR2002(HY000):Can'tconnecttolocalMySQLserverthroughsocket'/tmp/mysql.sock'(2)

做sock文件的软链接

[[email protected]~]#ln-s/data/mysql/tmp/mysqld.sock/tmp/mysql.sock

登录

[[email protected]~]#mysql-p123456

或者

[[email protected]~]#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:0

mysql>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>[email protected]'172.16.60.%';

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

|[email protected]%|

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

|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. 操作如下:

创建实例的数据目录

[[email protected]~]#mkdir-p/data/mysql3307/{data,log,binlog,conf,tmp}

[[email protected]~]#mkdir-p/data/mysql3308/{data,log,binlog,conf,tmp}

[[email protected]~]#chown-Rmysql.mysql/data/mysql3307

[[email protected]~]#chown-Rmysql.mysql/data/mysql3308

配置mysql

[[email protected]~]#cp-r/data/mysql/conf/my.cnf/data/mysql3307/conf/

[[email protected]~]#cp-r/data/mysql/conf/my.cnf/data/mysql3308/conf/

[[email protected]~]#sed-i's#/data/mysql/#/data/mysql3307/#g'/data/mysql3307/conf/my.cnf

[[email protected]~]#sed-i's#/data/mysql/#/data/mysql3308/#g'/data/mysql3308/conf/my.cnf

[[email protected]~]#sed-i's/3306/3307/g'/data/mysql3307/conf/my.cnf

[[email protected]~]#sed-i's/3306/3308/g'/data/mysql3308/conf/my.cnf

[[email protected]~]#chown-Rmysql.mysql/data/mysql*

进行初始化两个实例

[[email protected]~]#mysqld--defaults-file=/data/mysql3307/conf/my.cnf--initialize-insecure--user=mysql

[[email protected]~]#mysqld--defaults-file=/data/mysql3308/conf/my.cnf--initialize-insecure--user=mysql

接着启动mysqld

[[email protected]~]#mysqld_safe--defaults-file=/data/mysql3307/conf/my.cnf&

[[email protected]~]#mysqld_safe--defaults-file=/data/mysql3308/conf/my.cnf&

查看启动是否成功

[[email protected]~]#ps-ef|grepmysql

mysql239961014:37?00:00:00/bin/sh/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/mysql/conf/my.cnf

mysql2474323996014: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

root3047323727015:33pts/000:00:00/bin/sh/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/mysql3307/conf/my.cnf

mysql31191304731715: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=3307

root3125423727015:33pts/000:00:00/bin/sh/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/mysql3308/conf/my.cnf

mysql31977312543915: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=3308

root3204423727015:34pts/000:00:00grep--color=automysql

[[email protected]~]#lsof-i:3307

COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAME

mysqld31191mysql22uIPv6231448440t0TCP*:opsession-prxy(LISTEN)

[[email protected]~]#lsof-i:3308

COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAME

mysqld31977mysql22uIPv6231457270t0TCP*:tns-server(LISTEN)

[[email protected]~]#lsof-i:3306

COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAME

mysqld24743mysql23uIPv6231329880t0TCP*:mysql(LISTEN)

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

[[email protected]~]#mysql-S/data/mysql3307/tmp/mysqld.sock

............

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

QueryOK,0rowsaffected(0.11sec)

mysql>flushprivileges;

QueryOK,0rowsaffected(0.11sec)

退出, 使用新密码登录

[[email protected]~]#mysql-uroot-S/data/mysql3307/tmp/mysqld.sock-p123456

.............

mysql>

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

[[email protected]~]#mysql-S/data/mysql3308/tmp/mysqld.sock

...........

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

QueryOK,0rowsaffected(0.13sec)

mysql>flushprivileges;

QueryOK,0rowsaffected(0.03sec)

退出, 使用新密码登录

[[email protected]~]#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

不过为了解决大家平时重复安装的问题,特意将多实例安装方法编辑成脚本了,有需要的读者可以在本公众号后台直接回复 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<^[email protected]|

|%|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<^[email protected]|

|%|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:0

mysql>flushprivileges;

QueryOK,0rowsaffected(0.14sec)

mysql>selecthost,user,authentication_stringfrommysql.user;

+-----------+------------------+------------------------------------------------------------------------+

|host|user|authentication_string|

+-----------+------------------+------------------------------------------------------------------------+

|%|bobo|$A$005$1VY")q?G6<^[email protected]|

|%|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=FALSE

character-set-server=utf8mb4

collation-server=utf8mb4_unicode_ci

init_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>[email protected]@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%';

最近更新