Centos7.9下 Mysql8.0 安装

海盗的小口琴
328 阅读8分钟

一、Mysql下载

dev.mysql.com/downloads/m…

二、安装

创建目录

mkdir -p /home/database

解压

cd /home/database
tar  -Jxvf mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz

更改路径

mv mysql-8.0.27-linux-glibc2.12-x86_64 mysql-8.0.27

创建mysql组和用户

groupadd mysql useradd -r -g mysql mysql
mkdir -p /home/database
groupadd mysql
useradd -g mysql mysql

创建mysql数据目录 / 回到根目录

cd / && mkdir -p /home/database/mysql-8.0.27/data

赋权限

chown mysql:mysql -R /home/database/mysql-8.0.27/data
chmod 750 /home/database/mysql-8.0.27/data -R

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
# bind-address = 127.0.0.1
# bind-address = 0.0.0.0 
 
[mysqld]
port       = 3306
server-id  = 3306
user       = mysql
socket     = /tmp/mysql.sock

#跳过权限
# skip-grant-tables

#sql_mode
sql_mode   = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

# 设置mysql的安装目录
basedir    = /home/database/mysql-8.0.27
# 设置mysql数据库的数据的存放目录
datadir    = /home/database/mysql-8.0.27/data
log-bin    = /home/database/mysql-8.0.27/data/mysql-bin
innodb_data_home_dir      =/home/database/mysql-8.0.27/data
innodb_log_group_home_dir =/home/database/mysql-8.0.27/data
#设置mysql数据库的日志及进程数据的存放目录
log-error =/home/database/mysql-8.0.27/data/mysql.log
pid-file  =/home/database/mysql-8.0.27/data/mysql.pid
# 服务端使用的字符集默认为8比特编码
character-set-server=utf8mb4
[
lower_case_table_names 参数
官方原话，简单来说就是，unix默认是0，windows默认是1，mac是2。
0是区分大小写的，1是不区分的，也就是windows是默认不区分大小写的。
lower_case_table_names 只能在初始化服务器时配置，无法在服务器初
始化后更改lower_case_table_names设置
]
#0=table区分大小写,1=table不区分大小写,初始化mysql时配置,初始化后不可修改
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

初始化mysql

cd  /home/database/mysql-8.0.27/bin
./mysqld --defaults-file=/etc/my.cnf --basedir=/home/database/mysql-8.0.27 --datadir=/home/database/mysql-8.0.27/data --user=mysql --initialize

参数说明

defaults-file=/usr/local/etc/my.cnf 指定配置文件（一定要放在最前面，至少 --initialize 前面）

user=mysql 指定用户（很关键）

basedir=/usr/local/mysql/ 指定安装目录

datadir=/usr/local/mysql/data/ 指定初始化数据目录

1.1 
--initialize 初始化生成临时密码
 临时密码登录

mysql -u root -p

输入服务器在初始化序列期间生成的随机临时密码：

1.2
 --initialize-insecure 初始化时无密码

无密码登录
mysql -u root --skip-password

2.
#首次改密推荐使用本地密码插件
with mysql_native_password
 
ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password BY '你的新密码';

#刷新权限
flush privileges;

 3.创建访问用户及主机ip
如果你配置时使用了skip_name_resolve,要创建一下127.0.0.1用户及主机ip

CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY '你的新密码';

创建远程访问用户任意ip
CREATE USER 'root'@'%' IDENTIFIED BY '你的新密码';

查看mysql.log初始密码,复制出来

cat /home/database/mysql-8.0.27/data/mysql.log

初始临时密码
r+.,47T%W&#B

启动mysql，并更改root 密码

bin/mysqld_safe --defaults-file=/etc/my.cnf &
./mysqld_safe --defaults-file=/etc/my.cnf &

查看进程

--#查看mysql 进程
ps -ef|grep mysql 

--#查看3306端口
netstat -ano |grep "3306"

三、更改密码

登录

1、进入安装路径，登录mysql

cd /home/database/mysql-8.0.27/bin
./mysql -u root -p
password:输入临时密码

2、无密码时登录

./mysql -u root --skip-password

执行修改密码与root用户,主机host ip,并刷新权限

1、修改新密码 'data_cube':

===============57以后===================
--#首次改密推荐使用本地密码插件 with mysql_native_password
ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password BY 'data_cube';

--#刷新权限
flush privileges;

--查询用户
use mysql;
select user,host,plugin,authentication_string from user;

--#创建用户任意远程访问
CREATE user 'root'@'%';

--#修改密码
alter user 'root'@'%' identified with mysql_native_password by 'data_cube';

--#给用户授权
grant all privileges on *.* to "root"@"%";

--#刷新权限
flush privileges;

--更改具体用户远程访问
--创建'root'@'127.0.0.1'用户
CREATE USER 'root'@'127.0.0.1' IDENTIFIED with mysql_native_password BY 'data_cube';

--#记住刷新权限
flush privileges;

--查询
select user,host,plugin,authentication_string from user;

四、解决sql_mode=only_full_group_by问题

--#查看mode
select version(), @@sql_mode;

--方法1
vi /etc/my.cnf
在[mysqld]下方插入
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

--方法2
1 mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
2 Query OK, 0 rows affected (0.01 sec)
3 
4 mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
5 Query OK, 0 rows affected (0.00 sec)

五、创建开机自启动mysql服务

--#确保my.cnf在路径/etc/my.cnf 
 cd /home/database/mysql-8.0.27
 cp support-files/mysql.server /etc/rc.d/init.d/mysql
 chmod +x /etc/init.d/mysql
 chkconfig --add mysql
 
 # 检查服务是否生效
 chkconfig --list mysql
 
--#启动Mysql服务
--#启动：
service mysql start
--#停止：
service mysql stop
--#重启：
service mysql restart
--#查看状态: 
service mysql status
--#重载配置：
service mysql reload

六、查看进程

--#查看mysql 进程
ps -ef|grep mysql
--#查看3306端口
netstat -ano |grep "3306"

---软连接 使用 mysql命令
ln -s /home/database/mysql-8.0.27/bin/mysql /usr/bin

---查看mysql下 user
select user,host,authentication_string from user;

七、全局使用 mysql命令 配置环境变量

vi /etc/profile 
--在最后行添加: 
export PATH=$PATH:/home/database/mysql-8.0.27/bin
--重新加载/etc/profile 
source /etc/profile

八、添加帐户，分配特权和删除帐户

use mysql;
#创建新用户及密码
CREATE USER 'root'@'localhost' IDENTIFIED with mysql_native_password  BY '新密码';
FLUSH PRIVILEGES;
CREATE USER 'root'@'%' IDENTIFIED with mysql_native_password  BY '123456';
FLUSH PRIVILEGES;
CREATE USER 'mysql'@'%' IDENTIFIED with mysql_native_password  BY '123456';
CREATE USER 'test'@'%' IDENTIFIED with mysql_native_password  BY '123456';

#给用户授权
GRANT ALL ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'mysql'@'%' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'test'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
select user,host,plugin,authentication_string from user;

#撤消test数据库级特权
REVOKE CREATE,DROP ON test.* FROM 'test'@'%';
FLUSH PRIVILEGES;

#删除帐户
DROP USER 'test'@'%';
FLUSH PRIVILEGES;

九、安装时废弃,后续可以添加权限

创建远程访问新用户并授权
use mysql;
#mysql8==>不允许<==授权并创建用户了,要分开使用
grant all privileges on *.* to '新用户名'@'%' identified by '密码' with grant option;
grant all privileges on *.* to '新用户名'@'指定ip' identified by '密码' with grant option;
例如:以前使用
grant all privileges on *.* to 'root'@'%' identified by "123456" with grant option;

mysql8分开操作为:
CREATE ROLE 'root'@'%'; #创建角色CREATE USER 'root'@'%' IDENTIFIED BY '123456';
ALTER USER 'root'@'%' IDENTIFIED with mysql_native_password by '123456'; #修改密码
grant all privileges on *.* to "root"@"%"; #给角色授权
flush privileges; #刷新权限

#另外修改主机
update mysql.user set host='具体要指定的主机ip' where user='root';
RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1'; #重命名
flush privileges; #刷新权限
select user,host,authentication_string from user;

十、my.cnf

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
port       = 3306
socket     = /tmp/mysql.sock
# bind-address = 127.0.0.1
# bind-address = 0.0.0.0
 
[mysqld]
port       = 3306
server-id  = 3306
user       = mysql
socket     = /tmp/mysql.sock
#跳过权限
# skip-grant-tables

#sql_mode
sql_mode   = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
# 设置mysql的安装目录
basedir    = /home/database/mysql-8.0.27
# 设置mysql数据库的数据的存放目录
datadir    = /home/database/mysql-8.0.27/data
log-bin    = /home/database/mysql-8.0.27/data/mysql-bin
innodb_data_home_dir      =/home/database/mysql-8.0.27/data
innodb_log_group_home_dir =/home/database/mysql-8.0.27/data
#设置mysql数据库的日志及进程数据的存放目录
log-error =/home/database/mysql-8.0.27/data/mysql.log
pid-file  =/home/database/mysql-8.0.27/data/mysql.pid
# 服务端使用的字符集默认为8比特编码
character-set-server=utf8mb4
#0=table区分大小写,1=table不区分大小写,初始化mysql时配置,初始化后不可修改
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

参考与引用

blog.csdn.net/u010565545/…

avatar
文章
阅读
粉丝