Mysql8.0参考手册

MySQL 8.0用户和角色管理

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.配置文件修改

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

[mysql]
default-character-set=utf8mb4

[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
port=3306
bind-address=0.0.0.0
pid-file=/data/mysql/mysql.pid
socket = /data/mysql/mysql.sock

# 忘记root密码启用这个
# skip-grant-tables

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-name-resolve
skip-character-set-client-handshake = true

# 区分大小写配置(0区分，1不区分)
#lower_case_table_names = 0

# Server Id
server-id=1
log-bin = mysql-bin
log-bin-trust-function-creators=1

# 最大连接数
max_connections=2000

#日志配置
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/mysql/logs/slow-query.log
#long_query_time = 2

# innodb相关
innodb_data_home_dir = /data/mysql/data
innodb_thread_concurrency=0
innodb_concurrency_tickets=5000
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=16M
innodb_log_file_size=128M
innodb_log_files_in_group = 3
innodb_buffer_pool_size=256M
innodb_buffer_pool_instances=8
innodb_autoextend_increment=128M
innodb_old_blocks_time=0
innodb_open_files=500
innodb_stats_on_metadata=0
innodb_file_per_table=1

# 其他参数
table_open_cache=2000
tmp_table_size=246M
thread_cache_size=128
key_buffer_size=512M
read_buffer_size=4M
read_rnd_buffer_size=16M
back_log=100
join_buffer_size=8M
sort_buffer_size=32M
max_allowed_packet=1024M
max_connect_errors=2500
open_files_limit=4161
table_definition_cache=1400
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

# 超时时间
interactive_timeout = 7200
wait_timeout = 7200

# 批量插入数据缓存大小，可以有效提高插入效率，默认为8M
bulk_insert_buffer_size = 64M

# sql模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_DATE

[mysqldump]
# 开启快速导出
quick
default-character-set = utf8mb4
max_allowed_packet = 32M
#----------------  配置文件结束  ------------------#

#创建日志文件
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> 

5.远程工具连接会报错

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';