mysql8.0.33绿色版本安装以及用法

绿色mysql

MySQL官网

MySQL :: Download MySQL Community Server (Archived Versions)

[root@mysql ~]# rm -rf /etc/my.cnf
[root@mysql ~]# find / -name "*mysql*" -exec rm -rf {} \;               //清除以前的环境
[root@mysql ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar     //解压
[root@mysql ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz       //解压
[root@mysql ~]# cd mysql-8.0.33-linux-glibc2.12-x86_64/
[root@mysql mysql-8.0.33-linux-glibc2.12-x86_64]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@mysql ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64/ /usr/local/mysql    //将解压文件放在/usr/local下
[root@mysql ~]# yum list installed |grep libaio                              //查看是否安装依赖
libaio.x86_64                          0.3.109-13.el7                  @anaconda
[root@mysql ~]# useradd -r -s /sbin/nologin mysql         //创建用户
[root@mysql ~]# id mysql
uid=27(mysql) gid=27(mysql) 组=27(mysql)
[root@mysql ~]# cd /usr/local/
[root@mysql local]# mkdir mysql/mysql-files       //创建mysql-files文件
[root@mysql local]# chown mysql:mysql /usr/local/mysql/mysql-files     //属主属组
[root@mysql local]# chmod 750 /usr/local/mysql/mysql-files             //权限
[root@mysql ~]# /usr/local/mysql/bin/mysqld --initialize --basedir=/usr/local/mysql/    //初始化数据库
3HgeAd+8HumE            //临时密码
[root@mysql ~]# ls /usr/local/mysql/              //有data目录，初始化成功
bin  data  docs  include  lib  LICENSE  man  mysql-files  README  share  support-files
[root@mysql ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data  //设置ssl安全加密连接
[root@mysql ~]# chown -R mysql:mysql /usr/local/mysql/data           //给新生成的文件添加属主属组
[root@mysql ~]# ls /usr/local/mysql/data/
root@mysql ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8  //方便启动
[root@mysql ~]# service mysql8 start             //启动服务

[root@mysql ~]# /usr/local/bin/mysql_secure_installation       //改密码，不允许远程等
[root@repo ~]# /usr/local/mysql/bin/mysql -u root -p               //连接
Enter password: 
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'root';   //在mysql中将密码修改为root

Query OK, 0 rows affected (0.00 sec)

远程连接指令
mysql -h主机ip或者域名 -P端口号 -u账号 -p密码

mysql> show variables like 'validate%';        //查看安全策略
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)
//修改安全策略
mysql> set global validate_password.length=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password.policy=LOW;
Query OK, 0 rows affected (0.00 sec)

mysql>  flush privileges;                   //刷新权限

mysql>  create user 'efg'@'%' identified by 'efgh';             //创建用户
Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from mysql.user;             //查看用户权限
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | efg              |
| %         | zhangsan         |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
6 rows in set (0.00 sec)

mysql> drop user 'efg'；           //删除用户
Query OK, 0 rows affected (0.00 sec)

//设置root用户远程连接
mysql> grant all on root.* to 'root'@'%';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
//报错，这是因为MySQL的新版本，不再允许使用GRANT给root用户授权，因为这可能引发安全问题。

mysql> use mysql;

mysql>  update user set host='%' where user='root';       //使用这个命令
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;            //刷新权限
Query OK, 0 rows affected (0.02 sec)

mysql> grant all on root.* to 'root'@'%';           //重新赋予权限
Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from mysql.user;             //查看
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | zhangsan         |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
5 rows in set (0.00 sec)
//        现在就可以使用navicat远程连接root账户

mysql>  create role 'a';                         //创建角色 
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'a';             //查看角色权限
+-------------------------------+
| Grants for a@%                |
+-------------------------------+
| GRANT USAGE ON *.* TO `a`@`%` |
+-------------------------------+
1 row in set (0.00 sec)

mysql> grant select ,insert,delete,update on test.user to 'a';          //为a添加select insert delete update权限
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from mysql.user;             //查看角色保存的表格
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | a                |
| %         | root             |
| %         | zhangsan         |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
6 rows in set (0.00 sec)