8.3 数据库（mariab+MySQL）

2301_79993321

于 2024-08-05 18:28:38 发布

阅读量118

点赞数 3

文章标签： mysql adb android

本文链接：https://blog.csdn.net/2301_79993321/article/details/140933839

版权

一．下载安装 mysql-8.0.33-linux-glibc2.12-x86_64.tar

1.清空/etc/目录下的my.cnf

[root@000 ~]# rm -rf /etc/my.cnf

[root@000 ~]# yum -y remove mariadb

2.解压

[root@000 ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar

[root@000 ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

[root@000 ~]# ls

anaconda-ks.cfg

mysql-8.0.33-linux-glibc2.12-x86_64

mysql-8.0.33-linux-glibc2.12-x86_64.tar

mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

mysql-router-8.0.33-linux-glibc2.12-x86_64.tar.xz

mysql-test-8.0.33-linux-glibc2.12-x86_64.tar.xz

[root@000 ~]# cd mysql-8.0.33-linux-glibc2.12-x86_64/

[root@000 mysql-8.0.33-linux-glibc2.12-x86_64]# ls

bin   include  LICENSE  README  support-files

docs  lib      man      share

3.将项目文件移动到/usr/local/mysql/

[root@000 ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64/ /usr/local/mysql/

4.查看有没有安装libaio

[root@000 ~]# yum list installed |grep libaio

libaio.x86_64                         0.3.109-13.el7                   @anaconda

[root@000 ~]# echo $?

0

5.创建用户

[root@000 ~]# useradd -r -s /sbin/nologin mysql

[root@000 ~]# id mysql

uid=997(mysql) gid=995(mysql) 组=995(mysql)

6.切换/usr/localmysql目录，创建一个mysql-files目录

[root@000 ~]# mkdir /usr/local/mysql/mysql-files

7.修改mysql-files文件权限750和所属组和属主mysql

[root@000 ~]# chown mysql:mysql /usr/local/mysql/mysql-files/

[root@000 ~]# chmod 750 /usr/local/mysql/mysql-files/

[root@000 ~]# ll /usr/local/mysql/

8.初始化数据库，找到初始化密码

[root@000 ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/

9.判断是否生成了data目录

[root@000 ~]# ls /usr/local/mysql/

bin   docs     lib      man          README  support-files

data  include  LICENSE  mysql-files  share

10.设置ssl安全加密连接敏感数据

[root@000 ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup  --datadir=/ /usr/local/mysql/data

11.其他配置

[root@000 ~]# ls /usr/local/mysql/support-files/

mysqld_multi.server  mysql-log-rotate  mysql.server

12.把mysql.server文件放到/etc/init.d/目录下，并改名为mysql8，方便启动mysql服务

[root@000 ~]# cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysql8

[root@000 ~]# service mysql8 start

Starting MySQL.Logging to '/usr/local/mysql/data/000.err'.

.. SUCCESS!

二．可以直接写一个脚本

vim  mysql.sh

#!/bin/bash

cp $1 /usr/local/mysql/

mdkir /usr/local/mysql/mysql-files/

grep /mysql/ /etc/password
if [ $? ne 0 ];then

useradd -r -s /sbin/nologin mysql

fi



chown mysql:msyql /usr/local/mysql/mysql-files

chmod 750 /usr/local/msyql/msyql-files



# init

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/

# password



# service

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8



# start server

service mysql8 start

三．回到mysql这台机器

远程登陆前提条件是mysql.user表中的host属性为%,如果是localhost就不允许远程登录，update mysql.user set host=”% “where user=”root”,flish privileges;

远程管理，可以使用图形化工具，sqlyog,navicat,掌握命令工具，客户端工具mysql

1.远程链接指令

mysql -h主机ip或者域名（如果是localhost或127.0.0.1可以省略） -P端口号（默认是3306，如果是默认的，可以省略） -u账号 -p密码

[root@mysql ~]# mysql -h127.0.0.1 -P3306 -uroot -p531242@Tt

2.创建账户

create user ‘tjj’@’%’ identified by ‘tj_jT123’

3.给权限

grant all on *.* to ‘tjj’

4.创建库

create database if not exists test;

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| sys                |

| test               |

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

5 rows in set (0.00 sec)

5.创建表

mysql> use test;

Database changed

mysql> create table user(

    -> id int primary key,

    -> username varchar(45) not null,

    -> password varchar(45) not null

    -> );

Query OK, 0 rows affected (0.03 sec)

6.添加数据

mysql> insert into user values(1,"zhangsan","123");

Query OK, 1 row affected (0.07 sec)



mysql> insert into user values(2,"lisi","456");

Query OK, 1 row affected (0.01 sec)



mysql> insert into user values(3,"wangwu","789");

Query OK, 1 row affected (0.00 sec)



mysql> insert into user values(4,"zhaoliu","000");

Query OK, 1 row affected (0.00 sec)

7.查看表数据

mysql> select *from test.user;

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

| id | username | password |

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

|  1 | zhangsan | 123      |

|  2 | lisi     | 456      |

|  3 | wangwu   | 789      |

|  4 | zhaoliu  | 000      |

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

4 rows in set (0.00 sec)





mysql> create user 'lilaosi'@'%' identified by 'Lilaosi_123';  //创建lilaosi的账号

Query OK, 0 rows affected (0.06 sec)



mysql> alter user 'lilaosi'@'%' identified by 'Lilaosi_123';  //修改密码

Query OK, 0 rows affected (0.01 sec)



mysql> select host,user from mysql.user;        //查看lilaosi的信息有没有过来

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

| host      | user             |

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

| %         | lilaosi          |

| localhost | mysql.infoschema |

| localhost | mysql.session    |

| localhost | mysql.sys        |

| localhost | root             |

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

5 rows in set (0.01 sec)

8.使用root账号，为lilaosi账号添加test库存中所有的表的所有权限

mysql> grant all on test.* to 'lilaosi'; 

#lilaosi就获得了test库中所有表的操作权限，但是，由于root没有给lilaosimysql库的权限，所以lilaosi账号无法查看mysql库

mysql> show databases;

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

| Database           |

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

| information_schema |

| performance_schema |

| test               |

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

3 rows in set (0.00 sec)

四．密码安全策略

回到root账户

[root@mysql ~]# mysql -uroot -p

mysql> show variables like 'validate%';

mysql> set global validate_password.policy=LOW;  

Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password.length=4;    

Query OK, 0 rows affected (0.00 sec)



mysql> show variables like 'validate%';

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

| Variable_name                        | Value |

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

| validate_password.check_user_name    | ON    |

| validate_password.dictionary_file    |       |

| validate_password.length             | 4     |

| validate_password.mixed_case_count   | 1     |

| validate_password.number_count       | 1     |

| validate_password.policy             | LOW   |

| validate_password.special_char_count | 1     |

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

7 rows in set (0.00 sec)



mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)



mysql> create user 'aaa'@'%' identified by 'aaaa';

Query OK, 0 rows affected (0.01 sec)

五．用户

1.创建用户

创建三个账号，abc[abcd].ccc[a1b2c3],ddd[231343]

mysql> create user 'abc'@'%' identified by 'abcd';

Query OK, 0 rows affected (0.00 sec)



mysql> create user 'ccc'@'%' identified by 'a1b2c3';

Query OK, 0 rows affected (0.00 sec)



mysql> create user 'ddd'@'%' identified by '231343';

Query OK, 0 rows affected (0.00 sec)

2.删除用户

mysql> drop user 'lilaosi';

Query OK, 0 rows affected (0.03 sec)



mysql> select host,user from mysql.user;

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

| host      | user             |

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

| %         | aaa              |

| %         | abc              |

| %         | ccc              |

| %         | ddd              |

| %         | tjj              |

| localhost | mysql.infoschema |

| localhost | mysql.session    |

| localhost | mysql.sys        |

| localhost | root             |

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

9 rows in set (0.00 sec)

3.修改用户

将abc,ccc,ddd三个账号的密码修改为1234

mysql> alter user 'aaa' identified by '1234';

Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'ccc' identified by '1234';

Query OK, 0 rows affected (0.01 sec)



mysql> alter user 'ddd' identified by '1234';

Query OK, 0 rows affected (0.01 sec)

五．角色

1.mysql> grant select on test.user to 'aaa'; 

#将test库中user表的查看权限给aaa，其他权限用不了，只能查看

Query OK, 0 rows affected (0.01 sec)

[root@mysql ~]# mysql -uaaa -p1234

mysql> select * from test.user;

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

| id | username | password |

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

|  1 | zhangsan | 123      |

|  2 | lisi     | 456      |

|  3 | wangwu   | 789      |

|  4 | zhaoliu  | 000      |

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

4 rows in set (0.00 sec)

mysql> quit

Bye

[root@mysql ~]# mysql -uroot -p

2.mysql> grant insert on test.user to 'aaa';

#给aaa用户添加insert权限

[root@mysql ~]# mysql -uaaa -p1234

mysql> insert into test.user values(5,"ermazi","ermazi");

Query OK, 1 row affected (0.00 sec)



mysql> select * from test.user;

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

| id | username | password |

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

|  1 | zhangsan | 123      |

|  2 | lisi     | 456      |

|  3 | wangwu   | 789      |

|  4 | zhaoliu  | 000      |

|  5 | ermazi   | ermazi   |

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

5 rows in set (0.00 sec)

3.修改user中的一行数据的password为111，没有update权限

mysql> update test.user set password='111' where username='aaa';

4.这样很麻烦，可以一次性给很多权限

mysql> grant delete,update on test.user to 'aaa';

Query OK, 0 rows affected (0.01 sec)



mysql> show grants for 'aaa';

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

| Grants for aaa@%                                                   |

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

| GRANT USAGE ON *.* TO `aaa`@`%`                                    |

| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.`user` TO `aaa`@`%` |

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

2 rows in set (0.00 sec)

5.创建角色

练习：

mysql> create role 'jingli';   //添加jingli角色

Query OK, 0 rows affected (0.00 sec)

mysql> create role 'yuangong';   //添加yuangong角色

Query OK, 0 rows affected (0.01 sec)

mysql> grant insert,delete,update,select on test.user to 'jingli';   //为jingli添加select,insert,delete,update权限

Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'jingli';     //查看角色的权限

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

| Grants for jingli@%                                                   |

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

| GRANT USAGE ON *.* TO `jingli`@`%`                                    |

| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.`user` TO `jingli`@`%` |

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

2 rows in set (0.00 sec)

mysql> grant select,insert on test.user to 'yuangong';  //为yuangong添加select insert权限

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'yuangong';   //查看角色的权限

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

| Grants for yuangong@%                                   |

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

| GRANT USAGE ON *.* TO `yuangong`@`%`                    |

| GRANT SELECT, INSERT ON `test`.`user` TO `yuangong`@`%` |

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

2 rows in set (0.00 sec)

mysql> select host,user from mysql.user;  //查看角色保存的表格

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

| host      | user             |

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

| %         | aaa              |

| %         | abc              |

| %         | ccc              |

| %         | ddd              |

| %         | jingli           |

| %         | tjj              |

| %         | yuangong         |

| localhost | mysql.infoschema |

| localhost | mysql.session    |

| localhost | mysql.sys        |

| localhost | root             |

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

11 rows in set (0.00 sec)

6.新增ccc和ddd两个用户ccc是经理需要增删改查权限，ddd是员工只需要新增的查看的权限

grant jingli to ‘ccc’;

grant yuangong to ‘ddd’;

mysql> grant jingli to 'ccc';

Query OK, 0 rows affected (0.00 sec)



mysql> grant yuangong to 'ddd';

Query OK, 0 rows affected (0.01 sec)



mysql> flush privileges;  //刷新权限

Query OK, 0 rows affected (0.00 sec)

2301_79993321

关注

3
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
8.3 数据库（mariab+MySQL）

mysql -h主机ip或者域名（如果是localhost或127.0.0.1可以省略） -P端口号（默认是3306，如果是默认的，可以省略） -u账号 -p密码。12.把mysql.server文件放到/etc/init.d/目录下，并改名为mysql8，方便启动mysql服务。远程管理，可以使用图形化工具，sqlyog,navicat,掌握命令工具，客户端工具mysql。6.切换/usr/localmysql目录，创建一个mysql-files目录。10.设置ssl安全加密连接敏感数据。
复制链接

扫一扫