mysql的安装配置与基础用户使用

第五周 周一

早

mysql安装配置

1.官网下载或者wget

[root@mysql ~]# ls

anaconda-ks.cfg

initserver.sh

mysql-8.0.33-1.el7.x86_64.rpm-bundle.tar

mysql-community-client-8.0.33-1.el7.x86_64.rpm

mysql-community-client-plugins-8.0.33-1.el7.x86_64.rpm

mysql-community-common-8.0.33-1.el7.x86_64.rpm

mysql-community-debuginfo-8.0.33-1.el7.x86_64.rpm

mysql-community-devel-8.0.33-1.el7.x86_64.rpm

mysql-community-embedded-compat-8.0.33-1.el7.x86_64.rpm

mysql-community-icu-data-files-8.0.33-1.el7.x86_64.rpm

mysql-community-libs-8.0.33-1.el7.x86_64.rpm

mysql-community-libs-compat-8.0.33-1.el7.x86_64.rpm

mysql-community-server-8.0.33-1.el7.x86_64.rpm

mysql-community-server-debug-8.0.33-1.el7.x86_64.rpm

mysql-community-test-8.0.33-1.el7.x86_64.rpm

2.确定mysql-community-server正常安装之后就可以开始配置

3.初始化mysqld 服务 mysqld initeialize

4.启动服务 systemctl start mysqld

5.添加开机启动列表 systecmctrl enable mysqld

6.在/var/log/mysql.log中找到初始密码，前后复制空格7.登录管理 mysql -uroot -p

将复制的密码粘贴到输入密码的区

域，实现登录

8.设置新的密码

9.退出登录

使用mysql8.0.33包an安装配置

1. 安装依赖包

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

   libaio.x86_64 0.3.109- 13.el7 @anaconda

2. 解压查看

   [root@localhost ~]# ls

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

   [root@localhost ~]# tar -xvf 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

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

   mysql-8.0.33-linux-glibc2.12-x86_64

   [root@localhost ~]# ls mysql-8.0.33-linux-glibc2.12-x86_64/

   bin docs include lib LICENSE man README

   share support-files

3. 创建用户

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

   [root@localhost ~]# id mysql

   uid=27(mysql) gid=27(mysql) 组=27(mysql)

4. 清空mariadb环境

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

5. 将解压文件放置在根目录下

   [root@localhost ~]# mv mysql-8.0.33-linux

   glibc2.12-x86_64/ /mysql

   [root@localhost ~]# ls /mysql/

   bin docs include lib LICENSE man README

   share support-files

6. 切换mysql目录，创建mysql-filles

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

   [root@localhost mysql]# chown mysql:mysql mysql-files/

   [root@localhost mysql]# chmod 750 mysql-files/

   [root@localhost mysql]# ls -l

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

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

9. 查看是否初始化成功过，可以通过文件夹中是否有data文件夹验证

   [root@localhost mysql]# ls

   bin docs lib man README support-files

   data include LICENSE mysql-files share

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

    [root@localhost mysql]# ls ./bin/ssl

    ./bin/mysql_ssl_rsa_setup

    [root@localhost mysql]# ./bin/mysql_ssl_rsa_setup --datadir=/mysql/data

    [root@localhost mysql]# ls ./data/

11. 拷贝support-files/mysql.server文件

    [root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysql8

    [root@localhost mysql]# ls /etc/init.d/

12. 启动服务器，不能使用systemctl

    service mysql8 start

下午

数据库远程及操作

1.远程登录前提条件是mysql.user表中的host属性为%，如果是

localhost就不允许远程登录，update mysql.user set host="%"

where user="root",flush privileges;

2.远程管理，可以使⽤图形化⼯具，sqlyog，navicat，掌握命令⼯

具，客户端⼯具 mysql

3.mysql -h192.168.71.129 -P3306 -uzhangmin -pZhang_min123

-h 主机 ip或者是域名 如果是localshost或者是127.0.0.1可省略

-P 端⼝ 默认是3306，如果是默认的，可以省略

-u ⽤户名

-p 密码，可以不换⾏直接输⼊，也可以换⾏ 不回显输⼊密码

创建账户

create user 'cc'@'%' identified by '12324cc';

给权限

grant all on . to 'cc'

创建库

create database if not exists test;

创建表

use test;

create table user(

id int primary key,

username varchar(45) not null,

password varchar(45) not null

);

添加数据

insert into test.user values(1,"zhangsan","123");

insert into test.user values(2,"lisi","456");

insert into test.user values(3,"wamngwi","789");

insert into test.user values(4,"zhaoliu","aaa");

添加lilaosi账号，修改密码，查看mysql.user中的lilaosi的信息

mysql> create user 'lilaosi'@'%' identified by 'Lilaosi_123';

Query OK, 0 rows affected (0.01 sec)

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

grant all on test.* to 'lilaosi';

密码安全策略

查看密码策略

mysql> show variables like 'validate%';

修改策略

mysql> set global validate_password.length=0;

mysql> set global validate_password.policy=LOW;

mysql> show variables like 'validate%'; //查看修改后策略，验证