mysql 8.0安装及部署

防火墙+selinux：均已关闭

1. hosts文件

echo "oracle12c1 192.168.0.100" >> /etc/hosts

2.卸载 系统自带mysql，mariadb

rpm -qa | grep mysql

rpm -qa | grep mariadb

rpm -e --nodeps mysql*

rpm -e --nodeps mariadb*

3.安装

tar -zxf mysql-8.0.19-el7-x86_64.tar.gz -C /usr/local      解压的文件放在 usr/local  并重命名为mysql

mkdir /var/lib/mysql            socket目录

mkdir -p /usr/local/mysql/log

mkdir -p /usr/local/mysql/data

4.配置环境变量

vim ~/.bash_profile

PATH后面添加    :/usr/local/mysql/mysql/bin

5.

[root@oracle12c1 mysql]# groupadd mysql

useradd -r -g mysql -s /sbin/nologin mysql                 (-r -s 的目的 不让其作为登录用户)

chown -R mysql:mysql /usr/local/mysql

chown -R mysql:mysql /var/lib/mysql

6.mysql配置文件    vim /etc/my.cnf                            和之前版本不一样的地方-1-：字符集只能是utf8mb4,最后一行

[mysql]

default-character-set=utf8mb4               #8.0都是用utf8mb4

#socket=/var/lib/mysql/mysql.sock

[mysqld]

port=3306

#socket=/var/lib/mysql/mysql.sock

basedir=/usr/local/mysql/mysql

datadir=/usr/local/mysql/data

character-set-server=utf8mb4

default-storage-engine=INNODB

innodb_buffer_pool_size=200M

max_allowed_packet=16M

explicit_defaults_for_timestamp=1

log-output=FILE

general_log=0

general_log_file=/usr/local/mysql/log/general.err

slow_query_log=ON

slow_query_log_file=/usr/local/mysql/log/query.err

long_query_time=10

log-error=/usr/local/mysql/log/error.err

default-authentication-plugin=mysql_native_password                                mysql5.7.6版本后 废弃user表中 password字段 和 password（）方法，所以旧方法重置密码对mysql8.0版本是行不通的           ，这条是恢复默认密码策略

[root@oracle12c1 mysql]# /usr/local/mysql/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/mysql --datadir=/usr/local/mysql/data

有临时密码

7.安装后的设置和启动

vim /usr/lib/systemd/system/mysqld.service

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=mysql

Group=mysql

ExecStart=/usr/local/mysql/mysql/bin/mysqld --defaults-file=/etc/my.cnf

LimitNOFILE=65536

LimitNPROC=65536

systemctl daemon-reload

systemctl stop mysqld

systemctl start mysqld

systemctl enable mysqld

systemctl status mysqld

命令行启动和关闭：

nohup /usr/local/mysql/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

mysqladmin -uroot -p shutdown

8.进入数据库

[root@oracle12c1 bin]# mysql -u root -p

### 改密码            不一样-2- root用户和远程登陆的用户  with mysql_native_password

mysql> alter user 'root'@'localhost' IDENTIFIED with mysql_native_password by 'root';        这里的localhost是本地ip的表示方式

mysql> use mysql

mysql> select host,user from user where user='root';

####开启远程登录，用在win上连接 ， 这两个用户是不一样的

mysql> create USER 'root'@'%' IDENTIFIED with mysql_native_password by 'root';    %是所有的ip

mysql> grant all privileges on *.* to 'root'@'%' with grant option;  修改所有权限，到所有的数据库的所有的表 到root 并设置管理员

####测试 可不可以远程登陆

mysql> select host,user from user where user='root';

[root@oracle12c1 bin]# mysql -u root -p -horacle12c1          -h远程登录 hostname 或者 IP地址

mysql> show databases;    

[root@oracle12c1 bin]# systemctl stop firewalld

[root@oracle12c1 bin]# systemctl disable firewalld

9.创建数据库，建表    -3-  普通用户 没变化

[root@oracle12c1 bin]# mysql -uroot -p

mysql> create database testdb default charset utf8mb4;      

mysql> create user 'test'@'%' identified by 'test';

mysql> select host,user from mysql.user;

授权           和之前版本不一样-4-   授权的命令不再区分

mysql> grant all privileges on testdb.* to 'test'@'%' with grant option;        

mysql> use testdb

mysql> create table test01(

     id int auto_increment primary key,

     name varchar(15)

     )engine=InnoDB;

insert into test01 values (1,'zhang');

insert into test01 values (2,'liu');

mysql> select * from test01;

+----+-------+

| id | name  |

+----+-------+

|  1 | zhang |

|  2 | liu   |

+----+-------+

2 rows in set (0.00 sec)

mysql> commit;

                            忘记登录密码

1：//免密码登陆

找到mysql配置文件:my.cnf，

在【mysqld】模块添加：skip-grant-tables   保存退出；

2：//使配置生效

重启mysql服务：  service mysqld restart；

看下 ps -ef | grep mysql           停掉mysql，停不掉的话，直接kill -9 mysql的进程

3：//将旧密码置空

mysql -u root -p    //提示输入密码时直接敲回车。

//选择数据库

use mysql

//将密码置空

update user set authentication_string = '' where user = 'root';

//退出

quit

4://去除免密码登陆

删掉步骤1的语句  skip-grant-tables

重启服务  service mysqld restart

5：//修改密码

mysql -u root -p  //提示输入密码时直接敲回车，刚刚已经将密码置空了

ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc123@xxx';

alter USER 'root'@'%' IDENTIFIED with mysql_native_password by 'root';