数据库的基本操作使用
1、安装数据库
[root@localhost ~] dnf search mariadb ##搜索
[root@localhost ~] dnf install mariadb-server.x86_64 -y ##安装
[root@localhost ~] systemctl enable --now mariadb ##启用数据库服务
2、登陆数据库
[root@localhost ~] mysql ##安装之后未设置passwd可以直接进入,所以不安全
[root@localhost ~] mysql_secure_installation ##设置mysql的密码等基本设定
[root@localhost ~] mysql -uroot -pwestos ##这样是直接输入明文密码登陆不安全
[root@localhost ~] mysql -uroot -p ##这样输入的密码不显示
Enter password:
[root@localhost ~] netstat -antlupe | grep mysql ##开放了3306网络端口,不安全
tcp6 0 0 :::3306 :::* LISTEN 27 102680 32932/mysqld
[root@localhost ~] vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
skip-networking=1 ##关闭网络端口
[root@localhost ~]# systemctl restart mariadb.service ##重启刷新设定
[root@localhost ~]# netstat -antlupe | grep mysql ##查看网络端口是否关闭
3、数据库的基本使用
[root@M0 ~]# mysql -uroot -p ##进入数据库
MariaDB [(none)]> SHOW DATABASES; ##查看所有库
MariaDB [(none)]> USE mysql; ##使用mysql库(数据库的名字一般不更改,容易造成数据丢失,影响注册表)
MariaDB [mysql]> SHOW TABLES; ##查看mysql中的所有表
MariaDB [mysql]> SELECT * FROM user; ##查看user表中的参数
MariaDB [mysql]> CREATE DATABASE westos; ##创建westos库
MariaDB [mysql]> USE westos; ##进入westos库
MariaDB [westos]> CREATE TABLE linux ( ##创建linux表
->username varchar(6) not null,
->password varchar(30) not null
->);
MariaDB [westos]> DESC linux; ##查看linux表的参数设定
MariaDB [westos]> INSERT INTO linux VALUES ('user1','123'),('user2','123'); ##在表中填入数据
MariaDB [westos]> FLUSH PRIVILEGES; ##刷新
MariaDB [westos]> SELECT * FROM linux;
4、数据库的数据更改备份及删除
MariaDB [westos]> ALTER TABLE linux RENAME user; ##更改数据库中表名
MariaDB [westos]> ALTER TABLE user ADD age varchar(6) AFTER password; ##在指定字段后添加字段
MariaDB [westos]> ALTER TABLE user ADD class varchar(6) AFTER age;
MariaDB [westos]> UPDATE user SET age='18',class='linux' WHERE username='user1'; ##在新添加的字段中插入值
[root@M0 ~]# mysqldump -uroot -p westos > /mnt/westos.sql ##备份数据库
[root@M0 mnt]# vim westos.sql
CREATE DATABASE westos;
USE westos;
MariaDB [westos]> DELETE FROM user WHERE username='user1' AND password='123'; ##删除表中某指定的某一行参数
MariaDB [(none)]> DROP TABLE westos.user; ##删除表
[root@M0 mnt]# mysql -uroot -p -e “DROP DATABASE westos;” ##删除某个数据库
[root@M0 mnt]# mysql -uroot -p < /mnt/westos.sql ##用备份文件恢复数据库
5、数据库的密码更改及破解
-
更改密码
[root@M0 mnt]# mysqladmin -uroot -p password redhat ##知道密码的情况下更改密码 -
破解密码
[root@M0 mnt]# systemctl stop mariadb.service ##关闭服务
[root@M0 mnt]# mysqld_safe --skip-grant-tables & ##跳过授权表
[root@M0 mnt]# mysql ##无密码登陆
MariaDB [(none)]> UPDATE mysql.user set authentication_string=password(‘lee’) WHERE User=‘root’; ##更改密码为lee
MariaDB [(none)]> quit
[root@M0 mnt]# ps aux | grep mysql ##找出有关mysql的进程然后全部kill
[root@M0 mnt]# systemctl start mariadb.service ##开启服务,破解成功
6、用户授权
@localhost 只能本机登陆
%通过网络登陆
[root@localhost Desktop]# mysql -uroot -p -e "SELECT User FROM mysql.user;"
Enter password:
+------+
| User |
+------+
| root |
| root |
| root |
+------+
[root@localhost Desktop]# mysql -uroot -p -e "CREATE USER westos@localhost identified by 'westos';"
Enter password:
[root@localhost Desktop]# mysql -uroot -p -e "SELECT User FROM mysql.user;"
Enter password:
+--------+
| User |
+--------+
| root |
| root |
| root |
| westos |
+--------+
[root@localhost Desktop]# mysql -uroot -p -e "SHOW GRANTS FOR westos@localhost;"
Enter password:
+---------------------------------------------------------------------------------------------------------------+
| Grants for westos@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'westos'@'localhost' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
+---------------------------------------------------------------------------------------------------------------+
[root@localhost Desktop]# mysql -uwestos -p -e "SHOW DATABASES;"
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
[root@localhost Desktop]# mysql -uroot -p -e " GRANT SELECT ON westos.* TO westos@localhost;"
Enter password:
[root@localhost Desktop]# mysql -uroot -p -e "SHOW GRANTS FOR westos@localhost;"
Enter password:
+---------------------------------------------------------------------------------------------------------------+
| Grants for westos@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'westos'@'localhost' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
| GRANT SELECT ON `westos`.* TO 'westos'@'localhost'
|
+---------------------------------------------------------------------------------------------------------------+
[root@localhost Desktop]#
[root@localhost Desktop]# mysql -uwestos -p -e "SHOW DATABASES;"
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| westos |
+--------------------+
[root@localhost Desktop]#
[root@localhost Desktop]# mysql -uwestos -p -e "SELECT * FROM westos.redhat;"
Enter password:
+----------+----------+
| username | password |
+----------+----------+
| user1 | haha |
| user2 | 123 |
| user3 | 123 |
+----------+----------+
[root@localhost Desktop]# mysql -uroot -p -e "GRANT INSERT on westos.* TO westos@localhost;"
Enter password:
[root@localhost Desktop]# mysql -uroot -p -e "SHOW GRANTS FOR westos@localhost;"
Enter password:
+---------------------------------------------------------------------------------------------------------------+
| Grants for westos@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'westos'@'localhost' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
| GRANT SELECT, INSERT ON `westos`.* TO 'westos'@'localhost' |
+---------------------------------------------------------------------------------------------------------------+
[root@localhost Desktop]# mysql -uwestos -p -e "INSERT INTO westos.redhat VALUES ('lww' ,'234');"
Enter password:
[root@localhost Desktop]# mysql -uwestos -p -e "SELECT * FROM westos.redhat;"
Enter password:
+----------+----------+
| username | password |
+----------+----------+
| user1 | haha |
| user2 | 123 |
| user3 | 123 |
| lww | 234 |
+----------+----------+
mysql -uroot -p -e "REVOKE INSERT ON westos.* FROM westos@localhost;" --->去除授权
[root@localhost Desktop]# mysql -uroot -p -e "DROP user westos@localhost;" ---->删掉
用户
Enter password:
[root@localhost Desktop]# mysql -uroot -p -e "SELECT User FROM mysql.user;"
Enter password:
+------+
| User |
+------+
| root |
| root |
| root |
+------+
7、网络端管理mysql
[root@M0 mnt]# dnf install httpd php php-mysqlnd -y
[root@M0 mnt]# systemctl enable --now httpd
[root@M0 mnt]# systemctl stop firewalld.service
[root@M0 mnt]# cd /var/www/html/
[root@M0 html]# ls
[root@M0 html]# wget http://172.25.254.250/software/phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@M0 html]# tar fxj phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@M0 html]# mv phpMyAdmin-3.4.0-all-languages myadmin
[root@M0 html]# cd myadmin/
[root@M0 myadmin]# cp config.sample.inc.php config.inc.php
[root@M0 myadmin]# systemctl restart httpd.service
8、网络登陆的用户----->非常不安全
[root@localhost myadmin]# mysql -uroot -p -e “SELECT User,Host FROM mysql.user;”
Enter password:
±-----±----------+
| User | Host |
±-----±----------+
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
±-----±----------+
[root@localhost myadmin]# mysql -uroot -p -e “CREATE USER lee@’%’ identified by ‘lee’;”
Enter password:
[root@localhost myadmin]# mysql -uroot -p -e “SELECT User,Host FROM mysql.user;”
Enter password:
±-----±----------+
| User | Host |
±-----±----------+
| lee | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
±-----±----------+
mysql -ulee -p -h172.25.254.104 可以登陆
9、重置数据库
systemctl stop mariadb
rm -fr /var/lib/mysql/
dnf reinstall mariadb-server -y
去掉之前的操作
vim /etc/my.cnf.d/mariadb-server.cnf
#skip-networking=1
打开端口
systemctl restart mariadb