靳闯博客 记录是一种习惯,分享是一种态度
  • 波浪
  • 波浪
  • 波浪
  • 波浪
mysql8.0.12安装
发表于: | 分类: 技术积累 | 标签: Mysql | 评论:0 | 阅读: 639

Mysql8.0参考手册

MySQL 8.0用户和角色管理


mysql8-1.png

1.开始安装

下载地址:https://dev.mysql.com/downloads/mysql/

我这里选择的是:mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz 版本

#创建安装目录和用户
mkdir /data
useradd mysql -M -s /sbin/nologin

#解压移动更名
tar xf mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.12-linux-glibc2.12-x86_64 /data/mysql

#创建数据目录和日志目录
mkdir /data/mysql/data
mkdir /data/mysql/logs

#授权
chown mysql.mysql -R /data/mysql

【初始化选项二选一,配置文件我们初始化完再设置】
#有密码初始化mysql
/data/mysql/bin/mysqld --user=mysql --basedir=/data/mysql/ --datadir=/data/mysql/data --initialize
#无密码初始化mysql
/data/mysql/bin/mysqld --user=mysql --basedir=/data/mysql/ --datadir=/data/mysql/data --initialize-insecure

#选择有密码初始化,记住自动生成的root密码
[root@localhost ~]# /data/mysql/bin/mysqld --user=mysql --basedir=/data/mysql/ --datadir=/data/mysql/data --initialize
2018-09-25T06:05:24.555603Z 0 [System] [MY-013169] [Server] /data/mysql/bin/mysqld (mysqld 8.0.12) initializing of server in progress as process 8719
2018-09-25T06:05:27.264634Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: OasrDte=a0t2
2018-09-25T06:05:28.477791Z 0 [System] [MY-013170] [Server] /data/mysql/bin/mysqld (mysqld 8.0.12) initializing of server has completed

2.配置文件修改

我这里拷贝mysql5.7的配置文件,但是需要修改!

vim /etc/my.cnf
#----------------  配置文件开始  ------------------#
[client]
#no-beep
socket =/data/mysql/mysql.sock
# pipe
# socket=0.i0
port=3306
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
port=3306
pid-file=/data/mysql/mysqld.pid
bind-address=0.0.0.0
#skip-grant-tables
skip-name-resolve
socket = /data/mysql/mysql.sock
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true
default-time-zone = '+8:00'
#字符集
character-set-client-handshake = FALSE
character_set_filesystem = utf8mb4
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
skip-character-set-client-handshake = true

# Server Id
server-id=1
log-bin = mysql-bin
max_connections=2000
table_open_cache=2000
tmp_table_size=246M
thread_cache_size=300

#此参数在mysql8.0中是没有的,需要注释,
#query_cache_size=0

#logs初始化时如果指定配置文件并配置好了log,密码相关信息会写入logs中不会前台打印,日志文件需要提前手动创建
general_log = ON
general_log_file = /data/mysql/logs/mysql.log
log_error = /data/mysql/logs/mysql-error.log
#slow_query_log = on
#slow-query-log-file = /data/mysql5.7/logs/slow-query.log
#long_query_time = 2

#限定用于每个数据库线程的栈大小。默认设置足以满足大多数应用
thread_stack = 192k
key_buffer_size=512M
read_buffer_size=4M
read_rnd_buffer_size=32M
innodb_data_home_dir = /data/mysql/data
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=16M
innodb_buffer_pool_size=256M
innodb_log_file_size=256M
innodb_thread_concurrency=128
innodb_autoextend_increment=1000
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=128M
max_allowed_packet=1024M
max_connect_errors=2000
open_files_limit=4161
sort_buffer_size=32M
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
#批量插入数据缓存大小,可以有效提高插入效率,默认为8M
bulk_insert_buffer_size = 64M
interactive_timeout = 28800
wait_timeout = 28800
log-bin-trust-function-creators=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_DATE
#----------------  配置文件结束  ------------------#

#创建日志文件
touch /data/mysql/logs/mysql.log
touch /data/mysql/logs/mysql-error.log
chown mysql.mysql -R /data/mysql/logs/*

3.启动mysql

#拷贝启动程序文件到/etc/init.d/下面
cp support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld

#修改启动脚本文件
vim /etc/init.d/mysqld
修改mysql程序路径变量 为我们自定义的路径
basedir=/data/mysql
datadir=/data/mysql/data

#启动mysql,没有错误的情况下就正常启动了,有错误请检查日志和给出的提示
[root@localhost logs]# service mysqld start
Unit mysqld.service could not be found.
Starting MySQL...... SUCCESS! 

4.进入数据库

#修改系统变量
vim /etc/profile
最下面加上:
export PATH=${PATH}:/data/mysql/bin

#使环境变量生效
source /etc/profile  或者 . /etc/profile

#这时需要刚才初始化生成的root密码,进入后是不能操作的,需要更改root密码后才可以操作
[root@localhost logs]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.12

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> 
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.02 sec)

mysql>

5.远程工具连接会报错

mysql8error.png


5.1授权用户远程连接

#mysql8.0之前授权语句是不能用的,8.0后要先创建用户,然后再授权。
#mysql8.0 之前的版本加密认证规则是mysql_native_password, mysql8.0之后加密认证规则是caching_sha2_password

#创建一个root@%的用户
mysql> CREATE USER `root`@`%` IDENTIFIED BY '666666'; 
Query OK, 0 rows affected (0.01 sec)

#授权此用户
mysql> GRANT ALL PRIVILEGES ON *.* TO `root`@`%` WITH GRANT OPTION; 
Query OK, 0 rows affected (0.01 sec)

#查看表单注意密码加密规则,我们用工具是连接不上的,需要修改下加密认证规则
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | root             | $A$005$j*qJ>7g/h
                                                 ~,{h9pAbYlMkOlGOhVXlAsl1r6MvrXmoWsmPUzn.vQtGfMI5B6 | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root             | $A$005$KXNZN8'(&cpyQS
%8i1q6gZAsWclIjBljl1eR4LYkIMamL6tC0CAy5XbXVD | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)

#修改root@%用户加密规则为mysql_native_password,然后再查看表单,加密认证规则变化了,这样我们用工具才能连上
#当然我们也可以在创建用户时候指定mysql_native_password认证方式
CREATE USER `root`@`%` IDENTIFIED WITH mysql_native_password BY '666666'; 

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '666666';
Query OK, 0 rows affected (0.01 sec)

mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | root             | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC                              | mysql_native_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root             | $A$005$KXNZN8'(&cpyQS
%8i1q6gZAsWclIjBljl1eR4LYkIMamL6tC0CAy5XbXVD | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)

6.如果需要使用mysql8.0版本之前的验证方式并保持之前版本的密码加密认证方式需要在配置文件中修改,重启生效

#修改配置文件
vim /etc/my.cnf
[mysqld]
······
default_authentication_plugin = mysql_native_password
······

#重启mysql
service mysqld restart

#创建新用户授权测试,验证加密认证规则是否生效
mysql> create user 'jinc'@'%' identified by '123123';
Query OK, 0 rows affected (0.01 sec)

mysql> create database jinc;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on jinc.* to 'jinc'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, authentication_string, plugin from mysql.user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | jinc             | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1                              | mysql_native_password |
| %         | root             | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC                              | mysql_native_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root             | $A$005$KXNZN8'(&cpyQS
%8i1q6gZAsWclIjBljl1eR4LYkIMamL6tC0CAy5XbXVD | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
6 rows in set (0.00 sec)

7.更改root密码

alter user 'root'@'localhost' identified by 'newpasswd';

商业转载请联系作者获得授权,非商业转载请注明出处,谢谢合作。


如果这篇文章帮助到了你,我感到十分荣幸!

或许你不想写点什么·但我依旧在这里

icon_mrgreen.pngicon_neutral.pngicon_twisted.pngicon_arrow.pngicon_eek.pngicon_smile.pngicon_confused.pngicon_cool.pngicon_evil.pngicon_biggrin.pngicon_idea.pngicon_redface.pngicon_razz.pngicon_rolleyes.pngicon_wink.pngicon_cry.pngicon_surprised.pngicon_lol.pngicon_mad.pngicon_sad.pngicon_exclaim.pngicon_question.png2018new_aini_org.png2018new_baobao_thumb.png2018new_erha_org.png2018new_kuxiao_thumb.png2018new_yun_thumb.png2018new_hufen_thumb.png2018new_gui_org.png2018new_xiaoerbuyu_org.png2018new_heixian_thumb.png2018new_wabi_thumb.png2018new_tianping_thumb.png

站点地图 网站地图
豫ICP备17003270号 | Copyright © 2018 💖 靳闯博客
Typecho🍹Sgreen
TOP