2022-10-27 323
MySQL 8正式版8.0.11已发布，官方表示MySQL8要比MySQL 5.7快2倍，还带来了大量的改进和更快的性能！到底谁最牛呢？请看：MySQL 5.7 vs 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.log 2019-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>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
[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_profile exportPATH=/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=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信息)
[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 COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAME mysqld24743mysql23uIPv6231329880t0TCP*: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.sock ERROR1045(28000):Accessdeniedforuser'root'@'localhost'(usingpassword:NO) [root@mysql-node~]#mysql-p123456 mysql:[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: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>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|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 [root@mysql-node~]#lsof-i:3307 COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAME mysqld31191mysql22uIPv6231448440t0TCP*:opsession-prxy(LISTEN) [root@mysql-node~]#lsof-i:3308 COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAME mysqld31977mysql22uIPv6231457270t0TCP*:tns-server(LISTEN) [root@mysql-node~]#lsof-i:3306 COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAME mysqld24743mysql23uIPv6231329880t0TCP*: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-p123456 mysql-uroot-S/data/mysql3307/tmp/mysqld.sock-p123456 mysql-uroot-S/data/mysql3308/tmp/mysqld.sock-p123456
不过为了解决大家平时重复安装的问题，特意将多实例安装方法编辑成脚本了，有需要的读者可以在本公众号后台直接回复 MySQL8 获取多实例安装脚本。
Mysql8.0使用过程中踩过的一些坑
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
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:0 mysql>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的数据库了
#修改加密规则 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)
这样问题就解决了。
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是必须加上的。
#查询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%';
原文链接：https://77isp.com/post/10193.html
=========================================
https://77isp.com/ 为 “云服务器技术网” 唯一官方服务平台，请勿相信其他任何渠道。
数据库技术 2022-03-28
网站技术 2022-11-26
网站技术 2023-01-07
网站技术 2022-11-17
Windows相关 2022-02-23
网站技术 2023-01-14
Windows相关 2022-02-16
Windows相关 2022-02-16
Linux相关 2022-02-27
数据库技术 2022-02-20
抠敌 2023年10月23日
嚼餐 2023年10月23日
男忌 2023年10月22日
瓮仆 2023年10月22日
簿偌 2023年10月22日
扫码二维码
获取最新动态
