共计 26681 个字符，预计需要花费 67 分钟才能阅读完成。
MySQL 8 正式版 8.0.11 已公布，官网示意 MySQL8 要比 MySQL 5.7 快 2 倍，还带来了大量的改良和更快的性能！到底谁最牛呢？请看：MySQL 5.7 vs 8.0，哪个性能更牛？
Mysql8.0 装置 (YUM 形式)
- 首先删除零碎默认或之前可能装置的其余版本的 mysql
[root@DB-node01 ~]# for i in $(rpm -qa|grep mysql);do rpm -e $i --nodeps;done
[root@DB-node01 ~]# rm -rf /var/lib/mysql && rm -rf /etc/my.cnf
- 装置 Mysql8.0 的 yum 资源库
mysql80-community-release-el7-1.noarch.rpm
[root@DB-node01 ~]# yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm
- 装置 Mysql8.0
[root@DB-node01 ~]# yum install mysql-community-server
#启动 MySQL 服务器和 MySQL 的主动启动
[root@DB-node01 ~]# systemctl start mysqld
[root@DB-node01 ~]# systemctl enable mysqld
- 应用默认明码首次登录后, 必须要重置明码
查看默认明码, 如下默认明码为 "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 数据库，应用 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 的值无关, mysql8.0 更改了 validate_password_policy 相干的配置名称, 这跟 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](http://mp.weixin.qq.com/s?__biz=MzI0MDQ4MTM5NQ==&mid=2247495595&idx=1&sn=f6c169e610b38050c4eecda80f23cf37&chksm=e9188ab7de6f03a1531aa276b91cdbbc43d4ef0fafa13f21f8b6d93c88710e350f0b10ff18b1&scene=21#wechat_redirect)> 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
[root@DB-node01 ~]# mysql -p123456
...........
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15 |
+-----------+
1 row in set (0.00 sec)
查看服务端口
mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.01 sec)
查看 mysql 连贯的受权信息
mysql> select host,user,password from mysql.user;
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
下面这是 mysql5.6 及以下版本的查看命令, mysql5.7 之后的数据库里 mysql.user 表里曾经没有 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 装置 (二进制形式)
- 首先删除零碎默认或之前可能装置的其余版本的 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
- 装置须要的软件包
[root@mysql8-node ~]# yum -y install libaio
[root@mysql8-node ~]# yum -y install net-tools
- 下载并装置 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
- 创立数据目录
[root@mysql-node src]# mkdir -p /data/mysql/{data,log,binlog,conf,tmp}
[root@mysql-node src]# chown -R mysql.mysql /data/mysql
- 配置 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
- 初始化 (稍等一会儿, 能够到 /data/mysql/log/mysqld.err 日子里查看初始化过程, 看看有没有 error 信息)
[mysql@mysql-node ~]$ mysqld --defaults-file=/data/mysql/conf/my.cnf --initialize-insecure --user=mysql - 启动 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)
- 登录 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)
#授予用户权限. 必须先要创立用户, 能力受权!!
(创立用户时要带 @并指定地址, 则 grant 受权时的地址就是这个 @前面指定的!, 否则 grant 受权就会报错!)
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 单机多实例装置配置
通过下面二进制部署可知, 曾经起来一个 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 -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 端口实例, 并设置明码
[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 端口实例, 并设置明码
[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
不过为了解决大家平时反复装置的问题，特意将多实例装置办法编辑成脚本了，有须要的读者能够在本公众号后盾间接回复 MySQL8 获取多实例装置脚本。
Mysql8.0 应用过程中踩过的一些坑
- 1)创立用户和受权 在 mysql8.0 创立用户和受权和之前不太一样了，其实严格上来讲，也不能说是不一样, 只能说是更严格, mysql8.0 须要先创立用户 (创立用户时要带 @并指定地址, 则 grant 受权时的地址就是这个 @前面指定的!, 否则 grant 受权就会报错!) 和设置明码, 而后能力受权。
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 及之前版本的间接受权的办法, 会有报错:
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 账号的权限
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 的数据库了
- 批改 root 账号权限, 容许 root 账号近程登录后, 用 navicat 进行 mysql 的近程连贯时，呈现了弹窗报错：呈现这个起因是 mysql8 之前的版本中加密规定是 mysql_native_password, 而在 mysql8 之后, 加密规定是 caching_sha2_password, 解决问题办法有两种:
- 一种是降级 navicat 驱动;
- 一种是把 mysql 用户登录明码加密规定还原成 mysql_native_password; 这里抉择第二种办法来解决:
# 批改加密规定
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)
#刷新权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)
这样问题就解决了。
- 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’;
- sqlyog 链接时呈现 2058 异样
- 批改默认编码方式 mysql8.0 默认编码方式为 utf8mb4，因而应用时不须要批改，可应用如下命令查看：
mysql > SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE '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='SET NAMES utf8mb4'
而后重启 mysqld 服务即可, 其中:
character_set_client (客户端起源数据应用的字符集)
character_set_connection (连贯层字符集)
character_set_database (以后选中数据库的默认字符集)
character_set_results (查问后果字符集)
character_set_server (默认的外部操作字符集)
数据库连贯参数中:
characterEncoding=utf8 会被自动识别为 utf8mb4，也能够不加这个参数，会自动检测。而 autoReconnect=true 是必须加上的。- 6)局部参数配置查问命令
# 查问 mysql 最大连接数设置
mysql> show global variables like 'max_conn%';
mysql> SELECT @@MAX_CONNECTIONS AS 'Max Connections';
# 查看最大链接数
mysql> show global status like 'Max_used_connections';
# 查看慢查问日志是否开启以及日志地位
mysql> show variables like 'slow_query%';
# 查看慢查问日志超时记录时间
mysql> show variables like 'long_query_time';
# 查看链接创立以及当初正在链接数
mysql> show status like 'Threads%';
# 查看数据库以后链接
mysql> show processlist;
# 查看数据库配置
mysql> show variables like '%quer%'; 作者：散尽浮华
出处：https://www.cnblogs.com/kevin…
正文完
