数据库服务概述 | 构建MySQL服务 | 数据库基本管理 | MySQL基本类型
- 数据库服务概述
- 构建mysql服务
- 安装mysql软件包
- 连接mysql服务器 修改密码
- 密码管理
- 修改密码策略（需要登陆）
- 破解数据库管理员root密码（数据库服务处于运行状态但是root忘记了密码）
- 破解线下数据库服务器管理员root密码
- 破解线上服务器root密码（线上服务器不允许随便重启）
- 修改root密码
- 安装图形软件
- 必备命令
- 相关参数
- 基础查询
数据库服务概述
数据库就是存储数据的仓库
用来存储数据的服务器 就成为数据库服务器
构建mysql服务
如果之前有mariadb，则需要先卸载，并删除对应的配置与数据：
[root@host50 ~]# systemctl stop mariadb
删除/etc/my.cnf配置文件
此配置文件由RHEL自带的mariadb-libs库提供：
[root@host50 ~]# rm -rf /etc/my.cnf
删除数据
[root@host50 ~]# rm -rf /var/lib/mysql/*
卸载软件包
[root@host50 ~]# rpm -e --nodeps mariadb-server mariadb
警告：/var/log/mariadb/mariadb.log 已另存为/var/log/mariadb/mariadb.log.rpmsave
安装mysql软件包
解压mysql-5.7.17.tar 软件包
[root@host50 ~]# tar -xvf mysql-5.7.17.tar //解压mysql整合包
安装MySQL软件包
[root@host50 ~]# yum -y install mysql-community-*.rpm //yum安装自动解决依赖
启动MySQL数据库服务并设置开机自启(提示：第一次启动，需要初始化数据，会比较慢)
[root@host50 ~]# systemctl start mysqld //启动mysql服务
[root@host50 ~]# systemctl enable mysqld //设置开机自启
[root@host50 ~]# systemctl status mysqld //查看mysql服务状态
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 二 2018-08-28 10:03:24 CST; 8min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 4284 (mysqld)
CGroup: /system.slice/mysqld.service
└─4284 /usr/sbin/mysqld --daemonize --pid-file=/var/r...
8月 28 10:02:56 localhost.localdomain systemd[1]: Starting MySQ...
8月 28 10:03:24 localhost.localdomain systemd[1]: Started MySQL...
Hint: Some lines were ellipsized, use -l to show in full.
[root@host50 ~]# netstat -utnlp | grep 3306
连接mysql服务器 修改密码
- 查看初始密码
[root@host50 ~]#grep -i 'password' /var/log/mysqld.log
2017-04-01T18:10:42.948679Z 1 [Note] A temporary password is generated for root@localhost: mtoa>Av<p6Yk //随机生成的管理密码为mtoa>Av<p6Yk
- 使用初始密码连接mysql服务
[root@host50 ~]# mysql -u root -p'mtoa>Av<p6Yk' //初始密码登录
...
mysql> //登录成功后，进入SQL操作环境
...
- 重置数据库管理员root本机登陆密码
mysql> show database;
mysql> alter user root@"localhost" identified by "123qqq...A"; // 修改密码
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
mysql> show databases; //查看数据库（看到默认的4个库）
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit //断开连接
密码管理
- 修改密码策略（设置密码复杂度）
- 破解线下服务器root密码
- 破解线上服务器root密码
- 修改服务器root密码
修改密码策略（需要登陆）
[root@host50 ~]# mysql -uroot -p123qqq...A
mysql> show variables like "validata_password_%"; //查看与密码相关的全局变量
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| 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.00 sec)
mysql> set global validate_password_length = 6; //修改密码最小长度
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_policy = 0 ; //修改密码策略等级为0
Query OK, 0 rows affected (0.00 sec)
mysql> alter user root@"localhost" identified by "123456"; //修改root密码
Query OK, 0 rows affected (0.03 sec)
mysql> exit; 断开连接
永久配置，修改配置文件
[root@host50 ~]# vim /etc/my.cnf
[mysqld]
validate_password_length = 6 //密码最小长度
validate_password_policy = 0 //密码等级
[root@host50 ~]# systemctl restart mysqld //重启服务
[root@host50 ~]# mysql -uroot -p123456 //登录
mysql> show variavles like "validate_password_length"; //只查看密码长度
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| validate_password_length | 6 |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like "validate_password_policy"; //只查看密码策略
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| validate_password_policy | LOW |
+--------------------------+-------+
1 row in set (0.00 sec)
破解数据库管理员root密码（数据库服务处于运行状态但是root忘记了密码）
破解线下数据库服务器管理员root密码
1）修改运行参数并重启服务
如果修改了密码策略必须恢复为默认的默默策略，不然服务无法跳过授权表启动
[root@host50 ~]# vim /etc/my.cnf
[mysqld]
skip-grant-tables #逃过授权表
#validate_password_length = 6 //注释掉
#validate_password_policy = 0 //注释掉
[root@host50 ~]# systemctl restart mysqld //重启服务
- 无密码登录
[root@host50 ~]# mysql //无密码登录
mysql> update mysql.user set authentication_string=password("123qqq...A")
-> where user="root" and host="localhost"; //修改root用户本机登录密码(要符合默认的密码策略)
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> flush privileges; //让修改生效
Query OK, 0 rows affected (0.02 sec)
3）修改配置文件并重启服务
[root@host50 ~]# vim /etc/my.cnf
[mysqld]
#skip-grant-tables //注释
validate_password_length = 6 //删除注释
validate_password_policy = 0 //删除注释
[root@host50 ~]# systemctl restart mysqld
4）修改后的密码登录
[root@host50 ~]# mysql -uroot -p123qqq...A //登录
# 如果觉得破解后的密码太复杂，也可以用alter user设置简单的密码
破解线上服务器root密码（线上服务器不允许随便重启）
1）拷贝管理员能正常登录的数据库服务器的MySQL库覆盖本机的mysql库
把host51主机的mysql库拷贝给host50主机
[root@host50 ~]# scp -r root@192.168.4.51:/var/lib/mysql/mysql /var/lib/mysql/
root@192.168.4.51's password: 输入登录51主机的密码
2)重新加载数据
[root@host50 ~]# which pstree || yum -y install psmisc 安装pstree命令软件 可以查看父进程
/usr/bin/pstree
[root@host50 ~]# pstree -p | grep mysqld | head -1 查看父进程pid号
|-mysqld(20261)-+-{mysqld}(20262)
[root@host50 ~]# kill -SIGHUP 20261 发送信号给进程 重新加载
3)使用和host51主机一样的密码连接服务
[root@host50 ~]# mysql -uroot -pNSD123...a 密码登录
mysql>
修改root密码
为了数据库管理员root用户密码安全，可以定期修改密码（比如每隔10天修改一次密码）
注意：修改密码必须要知道旧密码 才能修改新密码
使用 alter user命令也可以修改登陆密码，这个命令前提是 数据库管理员能够登陆服务 才能修改
[root@host50 ~]# mysqladmin -uroot -pNSD123...a password "123qqq...A"
# 第二种方式
[root@host50 ~]# mysqladmin -uroot -p password
Enter password: 旧密码
New password: 新密码
Confirm new password: 再输入一遍新密码
安装图形软件
安装phpmyadmin软件
在50主机做如下配置：
1） 部署phpmyadmin 运行环境LAP （A指的是apache P指的是php）
[root@host50 ~]# yum -y install httpd php php-mysql 安装软件
[root@host50 ~]# systemctl start httpd 启动服务
[root@host50 ~]# systemctl enable httpd 开机运行
2）安装phpmyadmin软件
[root@host50 ~]# tar -zxvf phpMyAdmin-2.11.11-all-languages.tar.gz 解压软件
[root@host50 ~]# mv phpMyAdmin-2.11.11-all-languages /var/www/html/phpmyadmin 移动并改名
- 修改配置文件
[root@host50 ~]# cd /var/www/html/phpmyadmin/ 进入安装目录
[root@host50 phpmyadmin]# cp config.sample.inc.php config.inc.php # 拷贝模板文件，生成主配置文件config.inc.php
[root@host50 phpmyadmin]# vim +17 config.inc.php 只需要修改第17行
$cfg['blowfish_secret'] = 'sww123'; 随便添点字符就可以
4）打开真机浏览器，地址栏输入网址 http://192.168.4.50/phpmyadmin
必备命令
sql> select version(); //查看版本
sql> select user(); //查看登陆用户
sql> select database(); // 查看当前所在的库
sql> show databases; // 查看已有的库
sql> use 库名; //切换库
sql> show table; // 查看一有的表
相关参数
基础查询
- 查询格式
select 字段名列表 from 库名.表名; # 查找表里的所有数据
select 字段名列表 from 库名.表名 where 查询条件; # 只查询与条件匹配的数据
- 别名 拼接 去重
//别名
mysql> select name as 姓名 , homedir 家目录 from tarena.user;
+-----------------+--------------------+
| 姓名 | 家目录 |
+-----------------+--------------------+
| root | /root |
| bin | /bin |
| daemon | /sbin |
| adm | /var/adm |
| lp | /var/spool/lpd |
//拼接
mysql> select concat(name, "-" , uid ) from tarena.user;
+--------------------------+
| concat(name, "-" , uid ) |
+--------------------------+
| root-0 |
| bin-1 |
| daemon-2 |
| adm-3 |
| lp-4 |
| sync-5 |
//去重
mysql> select distinct gid from tarena.user;
+-------+
| gid |
+-------+
| 0 |
| 1 |
| 2 |
| 4 |
- 数值比较的使用
// 查看uid号和号相等的用户、UID、gid号
mysql> select name ,uid,gid from tarena.user where uid = gid ;
+-----------------+-------+-------+
| name | uid | gid |
+-----------------+-------+-------+
| root | 0 | 0 |
| bin | 1 | 1 |
| daemon | 2 | 2 |
| nobody | 99 | 99 |
| systemd-network | 192 | 192 |
| dbus | 81 | 81 |
//查看uid号不等与gid号的用户名、UID号、gid号
mysql> select name ,uid,gid from tarena.user where uid != gid ;
+----------+------+------+
| name | uid | gid |
+----------+------+------+
| adm | 3 | 4 |
| lp | 4 | 7 |
| sync | 5 | 0 |
| shutdown | 6 | 0 |
| halt | 7 | 0 |
| mail | 8 | 12 |
//查看表记录的前5行
mysql> select * from tarena.user where id <= 5;
+----+--------+----------+------+------+---------+----------------+---------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+--------+----------+------+------+---------+----------------+---------------+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
| 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
| 4 | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
| 5 | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
+----+--------+----------+------+------+---------+----------------+---------------+
5 rows in set (0.01 sec)
- 字符比较的使用
//查找名字叫apache的用户
mysql> select name from tarena.user where name="apache";
+--------+
| name |
+--------+
| apache |
+--------+
//查看shell是/bin/bash的用户
mysql> select name , shell from tarena.user
where shell = "/bin/bash" ;
+------+-----------+
| name | shell |
+------+-----------+
| root | /bin/bash |
| plj | /bin/bash |
+------+-----------+
mysql> select name , uid from tarena.user where uid is null ;
+------+------+
| name | uid |
+------+------+
| bob | NULL |
+------+------+
1 row in set (0.00 sec)
//零个字符
mysql> select name , comment from tarena.user where comment="" ;
+---------+---------+
| name | comment |
+---------+---------+
| postfix | |
| chrony | |
| plj | |
+---------+---------+
3 rows in set (0.00 sec)
- 范围匹配的使用
//查看uid号是1或3或5或7的记录
mysql> select name,uid,shell from tarena.user where uid in (1,3,5,7);
+------+------+---------------+
| name | uid | shell |
+------+------+---------------+
| bin | 1 | /sbin/nologin |
| adm | 3 | /sbin/nologin |
| sync | 5 | /bin/sync |
| halt | 7 | /sbin/halt |
+------+------+---------------+
4 rows in set (0.01 sec)
//查看shell不是/bin/bash 或 /sbin/nologin的用户
mysql> select name,uid,shell from tarena.user where shell not in ("/bin/bash","/sbin/nologin");
+----------+------+----------------+
| name | uid | shell |
+----------+------+----------------+
| sync | 5 | /bin/sync |
| shutdown | 6 | /sbin/shutdown |
| halt | 7 | /sbin/halt |
| mysql | 27 | /bin/false |
+----------+------+----------------+
4 rows in set (0.00 sec)
//查看uid 在10到30之间的记录，包括10和30本身
mysql> select name , uid , gid from tarena.user where uid between 10 and 30 ;
+----------+------+------+
| name | uid | gid |
+----------+------+------+
| operator | 11 | 0 |
| games | 12 | 100 |
| ftp | 14 | 50 |
| rpcuser | 29 | 29 |
| mysql | 27 | 27 |
+----------+------+------+
5 rows in set (0.00 sec)
- 模糊匹配的使用
//查看名字是3个字符的
mysql> select name from tarena.user where name like "___";
+------+
| name |
+------+
| bin |
| adm |
| ftp |
| rpc |
| plj |
| bob |
+------+
6 rows in set (0.00 sec)
//查看名字至少是4个字符的
mysql> select name from tarena.user where name like "__%__";
+-----------------+
| name |
+-----------------+
| root |
| daemon |
| sync |
| shutdown |
| halt |
| mail |
| operator |
| games |
| nobody |
| systemd-network |
| dbus |
| polkitd |
| sshd |
| postfix |
//查看名字里有字母a的
mysql> select name from tarena.user where name like "%a%";
+----------+
| name |
+----------+
| daemon |
| adm |
| halt |
| mail |
| operator |
| games |
| haproxy |
| apache |
+----------+
8 rows in set (0.00 sec)
- 正则匹配的使用
// 查看名字必须是r开头且是t结尾的名字
mysql> select name from tarena.user where name regexp "^r.*t$";
+------+
| name |
+------+
| root |
+------+
1 row in set (0.00 sec)
//看名字以数字开头的
mysql> select name from tarena.user where name regexp "^[0-9]";
Empty set (0.00 sec)
- 逻辑比较的使用
//查看名字叫mysql 或者uid 是 0 的 记录
mysql> select name , uid from tarena.user where name = "mysql" or uid = 0 ;
+-------+------+
| name | uid |
+-------+------+
| root | 0 |
| mysql | 27 |
+-------+------+
2 rows in set (0.00 sec)
// 既有and又有or 优先匹配and
mysql> select name , uid from tarena.user where name = "root" or name = "bin" and uid = 1 ;
+------+------+
| name | uid |
+------+------+
| root | 0 |
| bin | 1 |
+------+------+
2 rows in set (0.00 sec)
// () 先匹配or 再匹配and
mysql> select name , uid from tarena.user where (name = "root" or name = "bin") and uid = 1 ;
+------+------+
| name | uid |
+------+------+
| bin | 1 |
+------+------+
1 row in set (0.00 sec)
