数据库 一.基本配置 1.cd /etc/yum.repos.d/ ls rhel_dvd.repo vim rhel_dvd.repo 配置yum源 2.yum clean all 清空缓存 3. kill -9 将进程id (如果被占用结束进程) 4. yum clean all 清空缓存 5. yum search mariadb 查看软件包 6. yum install mariadb-server.x86_64 -y 安装软件 7. systemctl start mariadb 打开软件 8. systemctl enable mariadb 设置开机启动 9. netstat -antlupe | grep mysql 查看mysql服务的端口 10. vim /etc/my.cnf 编辑配置文件给数据库加密 内容: skip-networking=1 开机时跳过网络 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid 11. systemctl restart mariadb.service 重启服务 12. netstat -antlupe | grep mysql 查看mysql服务的端口 二.设置安全初始化: 1. mysql_secure_installation 命令 2.Enter current password for root (enter for none): 当前root用户密码(没有不用设) OK, successfully used password, moving on... 3.Set root password? [Y/n] Y 设置数据库密码 New password: 输入密码 Re-enter new password: 再次确认密码 Password updated successfully! Reloading privilege tables.. ... Success! 密码设置成功 三.创建数据库,修改数据苦信息,加密后登陆方式: 1.)mysql -uroot -p (-p后面加密码,回车后输入密码) mysql命令:(注意每条命令后必须以;结尾。在企业写命令用大写) 1.MariaDB [(none)]> show databases; 显示数据库 2.MariaDB [(none)]> use mysql; 进入数据库 3.MariaDB [mysql]> create database westos; 创建westos数据库 4.MariaDB [(none)]> show databases; 显示数据库 5.MariaDB [mysql]> use westos; 进入westos数据库 6.MariaDB [westos]> create table linux ( 创建linux表 -> username varchar(50) not null, -> password varchar(50) not null -> ); 7.MariaDB [westos]> select * from linux; 查看linux表 Empty set (0.01 sec) MariaDB [westos]> desc linux; 查看linux表的数据结构 8.MariaDB [westos]> insert into linux values ('lee','123'); 写入数据 Query OK, 1 row affected (0.04 sec) 9.MariaDB [westos]> select * from linux; 查看linux表 10.MariaDB [westos]> insert into linux values ('lee1','123'); 写入数据 Query OK, 1 row affected (0.02 sec) 11.MariaDB [westos]> select * from linux; 查看linux表 2.)更改linux表信息: 1.MariaDB [westos]> update linux set password='456' where username='lee'; Query OK, 1 row affected (0.09 sec) Rows matched: 1 Changed: 1 Warnings: 0 把lee密码改为456 2.MariaDB [westos]> select * from linux; 查看表信息 3.MariaDB [westos]> alter table linux add age varchar(4) after username; Query OK, 2 rows affected (0.50 sec) Records: 2 Duplicates: 0 Warnings: 0 在username之后加入一个字段age 4.MariaDB [westos]> select * from linux; 查看表 5.MariaDB [westos]> alter table linux drop age; 删除age字段 Query OK, 2 rows affected (0.43 sec) Records: 2 Duplicates: 0 Warnings: 0 6.MariaDB [westos]> select * from linux; 查询表 7.MariaDB [westos]> alter table linux rename messages; 把linux表名称改为messages Query OK, 0 rows affected (0.34 sec) 8.MariaDB [westos]> show tables; 显示数据库中的表 3.)删除: 1.MariaDB [westos]> delete from messages where username='lee'; 删除lee Query OK, 1 row affected (0.03 sec) 2.MariaDB [westos]> select * from messages; 查看表 3.MariaDB [westos]> drop tables messages; 删除messages表 Query OK, 0 rows affected (0.38 sec) 4.MariaDB [westos]> show databases; 查看数据库 5.MariaDB [westos]> drop database westos; 删除数据库 Query OK, 0 rows affected (0.00 sec) 6.MariaDB [(none)]> show databases; 查看数据库 四.用户登陆授权: 1. MariaDB [(none)]>create database westos; 创建库 Query OK, 1 row affected (0.00 sec) 2.MariaDB [(none)]> create table westos.linux( username varchar(50) not null, -> password varchar(50) not null) -> ; Query OK, 0 rows affected (0.36 sec) 创建linux表 3.MariaDB [(none)]> insert into westos.linux values ('lee','123'); 写入信息 Query OK, 1 row affected (0.35 sec) 4.MariaDB [westos]> select * from linux; 查看表 5.MariaDB [westos]> grant select on *.* to westos@locahost; 给westos的本地用户授权,查询所有数据库内容 Query OK, 0 rows affected (0.00 sec) 6.MariaDB [westos]> show grants for westos@locahost; 查看westos本地用户的权限 7.MariaDB [westos]> grant update on *.* to westos@locahost; 本地用户授权更新 Query OK, 0 rows affected (0.00 sec) 8.MariaDB [westos]> show grants for westos@locahost; 查看权限 9.MariaDB [westos]> grant SELECT,INSERT on westos.* to lee@locahost; Query OK, 0 rows affected (0.00 sec) 给lee用户在wstos库下权限 五.数据库的备份: 1.mysqldump -uroot -plinux westos > /mnt/westos.sql 把westos数据库备份到/mnt/westos.sql里面 2.drop database westos; 删除westos库 3.vim westos.sql 编辑文件 内容: CREATE DATABASE westos; 创建westos库 USE westos; 进入到库里 DROP TABLE IF EXISTS `linux`; 4.mysql -uroot -plinux < /mnt/westos.sql 将/mnt/westos.sql导入到westos库里 5.mysql -uroot -p 登陆 6.MariaDB [(none)]> show databases; 查看所有库 六.数据库root密码更改: 1.systemctl stop mariadb.service 关闭数据库服务 2. mysqld_safe --skip-grant-tables & 跳过网络打入后台 3.mysql 登陆 4.use mysql; 进入数据库 5.select * from user; 查看root密码 6.MariaDB [mysql]> update mysql.user set Password=password('redhat') where User='root'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 修改密码,成功后退出 7. mysqld_safe --skip-grant-tables 跳过初始化认证 8. killall -9 mysqld_safe 有进程时候,结束进程 [1]+ Killed mysqld_safe --skip-grant-tables 9. ps aux | grep mysql 查看mysql其他进程 10. kill -9 4659 结束进程 11. systemctl restart mariadb 重启服务 12. mysql -uroot -predhat 登陆(密码修改成功)