mysql8.0+环境搭建及密码修改

1、MySQL Windows安装

1)下载mysql安装包到本地并解压

下载完了进行解压,可解压到指定文件夹下。

mysql8.0+安装及密码修改_linux

解压完毕之后

2)创建my.ini文件

创建my.ini文件,并输入以下内容进行配置:

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录 最好使用‘/‘表示路径
basedir=D:\Tools\Database\MYSQL\mysql_8.0.18\mysql-8.0.18-winx64
# 设置mysql数据库的数据的存放目录 最好使用‘/‘表示路径
datadir=D:\Tools\Database\MYSQL\mysql_8.0.18\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
# character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#default_authentication_plugin=mysql_native_password
# 修改mode
#sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
[mysql]
# 设置mysql客户端默认字符集
# default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

写入内容后保存。

3)配置环境变量

(1)此电脑-》设置-》系统高级设置-》环境变量-》New新建变量-》写入MYSQL_HOME路径

mysql8.0+安装及密码修改_root用户_02

(2)在系统Path变量中添加MYSQL_HOME变量

mysql8.0+安装及密码修改_数据库_03

点击OK,连点三个OK, 环境变量配置完成

4)安装MySQL服务

以管理员身份启动cmd

在cmd下运行

mysqld --install # 安装mysql服务
mysqld --initialize --console # 对mysql进行初始化操作,执行时可记录给出的随机密码,以便登录时进行登录

会在​​D:\Tools\Database\MYSQL\mysql_8.0.18​​目录下生成data文件,因为my.ini设置的是该路径。

mysql8.0+安装及密码修改_root用户_04

然后通过​​net start mysql​​​启动MySQL服务, ​​net stop mysql​​关闭MySQL服务


如果出现了以下错误:

MySQL 服务正在启动 . MySQL 服务无法启动。 服务没有报告任何错误。 请键入 NET HELPMSG 3534 以获得更多的帮助。

则可以重新尝试初始化。

如果不行,①查看一下自己有没有配置好环境变量(详情请看第四步);②看看自己新建的my.ini里的basedir和datadir的路径有没有写错(详情请看第2步);


如果没出错的话,会显示:

C:\WINDOWS\system32>net start mysql
The MySQL service is starting.
The MySQL service was started successfully.

C:\WINDOWS\system32>net stop mysql
The MySQL service is stopping.
The MySQL service was stopped successfully.

mysql安装成功

5)更改mysql root用户密码

(1)登录root用户

C:\WINDOWS\system32>mysql -u root -p   # 用root用户登录
Enter password: ****** # 输入之前记录的随机密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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>

(2)通过以下命令对MySQL root用户进行密码更改

# 切换到mysql数据库
use mysql;

# 更改root用户密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

# 刷新权限
flush privileges;

mysql8.0+安装及密码修改_linux_05

6)退出验证密码是否更改成功

重新输入​​mysql -u root -p​​再输入密码回车,看是否登录成功。

C:\WINDOWS\system32>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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>

登录成功!密码更改成功。

至此,Windows安装MySQL-8.0.18成功完成,其余8.0.*版本安装类似

2、MySQL Linux 安装

建议采用方式二进行安装

方式一:

1)查看Linux系统是否已有MySQL

rpm -qa | grep mysql

没有输出则未安装MySQL

如果有输出,则可以直接使用,亦或是卸载重装。

2)下载mysql 8.0

(1)通过命令行​​wget​​下载

# 使用wget下载
wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

mysql8.0+安装及密码修改_windows 10_06

3)解压安装

(1)wget 下载的rpm包安装

rpm -ivh mysql80-community-release-el7-1.noarch.rpm
yum install mysql-server

mysql8.0+安装及密码修改_mysql_07

初始化MySQL:

mysqld --initialize

启动 MySQL:

service mysqld start

查看 MySQL 运行状态:

service mysqld status

验证 MySQL 安装

在成功安装 MySQL 后,一些基础表会被初始化,在服务器启动后,你可以通过简单的测试来验证 MySQL 是否工作正常。

使用 mysqladmin 工具来获取服务器状态:

mysqladmin --version

如果以上命令执行后未输入任何信息,说明你的Mysql未安装成功。

使用 MySQL Client(Mysql客户端) 执行简单的SQL命令

你可以在 MySQL Client(Mysql客户端) 使用 mysql 命令连接到Mysql服务器上,默认情况下Mysql服务器的密码为空,所以本实例不需要输入密码。

命令如下:

mysql -u root -p

以上命令执行后会输出 mysql>提示符,这说明你已经成功连接到Mysql服务器上,你可以在 mysql> 提示符执行SQL命令:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)

方式二

查看是否安装MySQL

find / -name mysql

(1)下载

# 通过tar 命令对压缩包进行解压
tar -zxvf mysql-8.0.18.tar.gz

(2)tar.gz解压安装

解压到指定目录之后,在该目录下创建一个data文件夹

cd / && mkdir -p /data/mysql8_data/

(3)配置my.cnf参数

创建用于初始化mysql数据库时my.cnf配置文件

Default options are read from the following files in the given order:

配置文件优先级:


  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. /usr/local/mysql/etc/my.cnf
  4. ~/.my.cnf

​vim /etc/my.cnf​​然后 按 i 进入编辑模式,把下面内容复制进去

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
server-id = 3306
user = mysql
socket = /tmp/mysql.sock
# 设置mysql的安装目录
basedir = /usr/local/mysql8
# 设置mysql数据库的数据的存放目录
datadir = /data/mysql8_data/mysql
log-bin = /data/mysql8_data/mysql/mysql-bin
innodb_data_home_dir =/data/mysql8_data/mysql
innodb_log_group_home_dir =/data/mysql8_data/mysql
#设置mysql数据库的日志及进程数据的存放目录
log-error =/data/mysql8_data/mysql/mysql.log
pid-file =/data/mysql8_data/mysql/mysql.pid
# 服务端使用的字符集默认为8比特编码
character-set-server=utf8mb4
lower_case_table_names=1
autocommit =1
##################以上要修改的########################
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 1024
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 64M
thread_cache_size = 128
#query_cache_size = 128M
tmp_table_size = 128M
explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

binlog_format=mixed

binlog_expire_logs_seconds =864000
# 创建新表时将使用的默认存储引擎
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
transaction-isolation=READ-COMMITTED

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

按ESC 后按住shift+: 输入wq保存并推出:wq!(强制保存退出)

(4)初始化

cd  /usr/local/mysql8/bin
#一行
./mysqld --defaults-file=/usr/local/etc/my.cnf --basedir=/usr/local/mysql8 --datadir=/data/mysql8_data/mysql --user=mysql --initialize


参数说明

–defaults-file=/usr/local/etc/my.cnf 指定配置文件(一定要放在最前面,至少 --initialize 前面)
–user=mysql 指定用户(很关键)
–basedir=/usr/local/mysql/ 指定安装目录
–datadir=/usr/local/mysql/data/ 指定初始化数据目录


(5)修改root用户密码

--initialize --console 初始化生成临时密码并显示在终端上
临时密码登录
mysql -u root -p
输入服务器在初始化序列期间生成的随机临时密码:
--initialize-insecure 初始化时无密码
无密码登录
mysql -u root --skip-password

更新root用户密码
ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password BY 'passwd';
#刷新权限
flush privileges;
#首次改密推荐使用本地密码插件with mysql_native_password
3.创建访问用户及主机ip
如果你配置时使用了skip_name_resolve,要创建一下127.0.0.1用户及主机ip
CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'passwd';
创建远程访问用户任意ip
CREATE USER 'root'@'%' IDENTIFIED BY 'passwd';

初始化完成后,通过​​mysql -u root -p​​输入密码进行登录

mysql8.0+安装及密码修改_linux_08

3、MySQL修改密码

Windows环境

(1)已知root用户密码的情况下进行修改

# 先通过mysql -u root -p回车输入密码进入mysql命令界面
# 切换数据库
use mysql;
# 更改root用户密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
# 刷新缓存
flush privileges;

运行如下所示:

C:\Users\caifl>MySQL -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 596
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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> use mysql;
Database changed
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.04 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

mysql>

(2)忘记密码的情况下进行用户密码的修改

以管理员身份运行命令行

1)停用MySQL服务​​net stop mysql​

2)并以以下命令行开启服务, 并绕过权限检查

mysqld --console --skip-grant-tables --shared-memory

3)启动另一个 CMD 命令行, 输入 ​​mysql -u root​​ 直接登陆, 接着输入以下命令, 将 root 密码123456并刷新权限

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
#刷新权限
flush privileges;

4)关闭所有cmd命令行串口,重新以管理员身份打开新的cmd窗口,重新启动mysql服务

net stop mysql
net start mysql

Linux环境

(1)已知root用户密码的情况下进行修改

参考Windows环境(1)相同方式方法

(2)忘记密码的情况下进行用户密码的修改

1)停止MySQL服务

systemctl stop mysqld    #停掉MySQL
systemctl status mysqld #查看状态

2)跳过授权表启动MySQL服务程序

打开/etc/my.cnf配置文件,my.cnf在MySQL根目录下,如果没有则创建一个, my.cnf内容参考如下:

[client]
port = 3306
socket = /usr/local/services/mysql/var/data/mysql.sock

[mysqld]
bind-address = 0.0.0.0
port = 3306
socket = /usr/local/services/mysql/var/data/mysql.sock
pid-file = /usr/local/services/mysql/var/logs/mysql.pid
character-set-server = utf8
basedir = /usr/local/services/mysql
datadir = /usr/local/services/mysql/var/data

skip-external-locking
skip-name-resolve
lower_case_table_names = 1
log-bin-trust-function-creators = 1

max_connections = 6000
max_user_connections = 6000
max_connect_errors = 4000
wait_timeout = 86400
interactive_timeout = 86400
table_open_cache = 512
max_allowed_packet = 32M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 32M
#default-storage-engine = InnoDB

#sql_mode="STRICT_ALL_TABLES,NO_AUTO_CREATE_USER"
server-id = 1

log-short-format
log-error = /usr/local/services/mysql/var/logs/mysql.log
slow_query_log
long_query_time = 2
slow_query_log_file = /usr/local/services/mysql/var/logs/mysql-slow.log

log-bin = /usr/local/services/mysql/var/binlog/mysql-bin
log_bin_trust_function_creators=1
binlog_format = MIXED
expire_logs_days = 10

# INNODB Specific options
innodb_data_home_dir = /usr/local/services/mysql/var/data
innodb_log_group_home_dir = /usr/local/services/mysql/var/redolog
innodb_additional_mem_pool_size = 10M
innodb_buffer_pool_size = 4G
innodb_data_file_path = ibdata1:100M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 128M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 50
innodb_file_per_table = 1

# MyISAM Specific options
key_buffer_size = 384M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 1G
myisam_repair_threads = 1
myisam_recover

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
default-character-set = utf8
no-auto-rehash
socket = /usr/local/services/mysql/var/data/mysql.sock

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

在​​[mysqld]​​下添加skip_grant_tables 一行,然后保存退出。

3)重启MySQL服务并连接

​systemctl start mysqld​​重启MySQL服务

mysql -u root #会直接进入mysql终端
use mysql; # 切换到mysql数据库
# 更改root用户密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
# 刷新缓存
flush privileges;

4)重新修改my.cnf 将​​skip_grant_tables​​删除并重启MySQL服务

systemctl start mysqld
systemctl stop mysqld

5)重新输入​​mysql -u root -p​​并输入密码进行登录即可。

注意:

当遇到skip_grant_tables`无法修改时。

可以通过​​vim /etc/my.cnf​​ 修改配置文件

在[mysqld]下添加​​skip-grant-tables​​ 跳过输入密码

systemctl restart mysqld #重启mysql服务
mysql -u root -p #登入root用户
update user set authentication_string = '' where user = 'root';# 将密码置空

然后​​vim /etc/my.cnf​​​ 修改配置文件,去掉​​skip_grant_tables​​,重启mysqld服务

再通过root用户登录

mysql -u root -p # 直接两个回车,不用输入密码
use mysql; # 选择数据库
alter user 'root'@'localhost' identified by '新密码'; # 修改密码
flush privileges; # 刷新权限

mysql8.0+安装及密码修改_数据库_09

alter 更新报错

如果Alter更新报错,显示​​Operation Alter user failed for 'root'@'localhost'​​​则通过​​select user, host from user​​查看用户主机列表中是否存在该用户主机,如果不存在,则根据用户主机列表进行更改。