mysql数据库的基本管理

1、数据库的介绍

• 什么是数据库
  数据库就是个高级的表格软件
• 常见数据库
  Mysql Oracle mongodb db2 sqlite sqlserver …
• Mysql (SUN -----> Oracle)
• mariadb

两个网站架构
LAMP === Linux Apache Mysql per/python/php
LNMP === Linux Nginx Mysql per/python/php

2 、软件基本信息

• mariadb.service ##启动服务
• 3306 ##默认端口号
• /etc/my.cnf ##主配置文件
• /var/lib/mysql ##数据目录,当需要重新安装mariadb时需要清理此目录或备份

3、数据库的安全初始化

root@localhost Desktop]# dnf search mariadb
[root@localhost Desktop]# dnf install mariadb-server.x86_64 -y
[root@localhost Desktop]# systemctl enable  --now mariadb   ##数据库开启
[root@localhost Desktop]# rpm -ql mariadb-server   ##查看软件相关信息 所有数据放在
var/lib/mysql		                                ##数据目录,当需要重新安装mariadb时需要清理此目录或备份
[root@localhost Desktop]# rpm -qc mariadb-server ##查看配置文件
/etc/my.cnf.d/mariadb-server.cnf                         ##主配置文件
[root@localhost Desktop]# mysql_secure_installation      ##执行安全初始化脚本
Enter current password for root (enter for none): 回车
Set root password? [Y/n] 
New password: 
Re-enter new password: 
Password updated successfully!
Remove anonymous users? [Y/n]    是否去除匿名用户登陆的权力
Disallow root login remotely? [Y/n] 是否去除超级用户远程登陆的权力
Remove test database and access to it? [Y/n] 是否去除测试库
Reload privilege tables now? [Y/n]  是否刷新数据库

[root@localhost Desktop]# mysql -uroot -p    ## -u 指定登陆用户 -p 密码
[root@localhost Desktop]# netstat -antlupe | grep mysql        ##查看网络安全端口 
tcp6       0      0 :::3306                 :::*                    LISTEN      27         1635473    44552/mysqld    
[root@localhost Desktop]# vim /etc/my.cnf.d/mariadb-server.cnf 
 21 skip-networking=1         ##关闭数据库开放端口                              
[root@localhost Desktop]# systemctl restart mariadb
[root@localhost Desktop]# netstat -antlupe | grep mysql        ##此命令查询不到端口

4、数据库的基本管理

1、查看

SHOW DATABASES;				##显示库名称
USE mysql;				##进入mysql库
SHOW TABLES；				##显示库中的所有表
SELECT * FROM user;			##查询所有数据
SELECT Host,User,Password FROM user;	##查询指定字段

2、新建

CREATE DATABASE westos;			       ##新建库
SHOW DATABASES;		
USE westos;	
CREATE TABLE linux (                           ##westos内新建表
username varchar(10) not null,
password varchar(40) not null
);							
DESC linux;					##显示表结构
SHOW TABLES;
INSERT INTO linux VALUES ('user1','123'); 	#插入数据
INSERT INTO linux VALUES ('user2','123'),('user3','123');
SELECT * FROM linux;
FLUSH PRIVILEGES;				#刷新数据库

3、更改

[root@localhos ~]# cd /var/lib/mysql/
[root@localhos mysql]# ls
aria_log.00000001  ib_buffer_pool  ib_logfile0  ibtmp1             mysql       mysql_upgrade_info  tc.log
aria_log_control   ibdata1         ib_logfile1  multi-master.info  mysql.sock  performance_schema

[root@localhost mysql]# mv westos/ lee   ##更改库的名字 一般情况下不改 防止数据丢失
[root@localhost mysql]# systemctl restart mariadb
SHOW DATABASES;		
USE westos;	
SHOW TABLES；
ALTER TABLE linux RENAME user;         ##更改表的名字
ALTER TABLE user RENAME linux; 
SHOW TABLES；
DESC linux;
ALTER TABLE linux ADD class varchar(6); 
ALTER TABLE linux ADD age varchar(6);   ## 更改表的结构
ALTER TABLE linux DROP age;
ALTER TABLE linux ADD age varchar(6) AFTER password;  
UPDATE westos.linux SET age='18',class='linux' WHERE username='user2';
UPDATE westos.linux SET age='18',class='java' WHERE username='user3';
SELECT * FROM westos.linux;

4、删除

DELETE FROM linux WHERE username='user1' AND password='123';
SELECT * FROM westos.linux;
DROP  TABLE linux;
SHOW TABLES;
DROP DATABASE westos;
SHOW DATABASES;

5、数据库的备份

[root@localhost mysql]# mysqldump -uroot -p --all-databases           ##备份所有的库
[root@localhost mnt]# mysqldump -uroot -p  --all-databases --no-data
[root@localhost mnt]# mysqldump -uroot -p westos > /mnt/westos.sql  ##备份westos库
[root@localhost mnt]# cd /mnt/
[root@localhost mnt]# mysql -uroot -p
MariaDB [(none)]> USE westos;
MariaDB [westos]> DELETE FROM linux WHERE username='user1' AND password='123';
MariaDB [westos]> SELECT * FROM linux; 
MariaDB [westos]> DROP TABLE linux;
MariaDB [westos]> DROP DATABASE westos;
MariaDB [(none)]> SHOW DATABASES;

[root@localhost mnt]# vim /mnt/westos.sql 
##添加行
21 CREATE DATABASE westos;
22 USE westos;

[root@localhost mnt]# mysql -uroot -p < /mnt/westos.sql
[root@localhost mnt]# mysql -uroot -p -e "SELECT * FROM westos.linux;"

[root@localhost mnt]# mysql -uroot -p -e "DROP DATABASE westos;"
[root@localhost mnt]# vim /mnt/westos.sql                            ##删除写的内容21 22
[root@localhost mnt]# mysql -uroot -p -e "CREATE DATABASE westos;"
[root@localhost mnt]# mysql -uroot -p westos < /mnt/westos.sql
[root@localhost mnt]# mysql -uroot -p -e "SELECT * FROM westos.linux;" 

6、数据密码管理

[root@localhost mnt]# mysqladmin  -uroot -p password westos ##密码改为westos 显示密码不安全

[root@localhost mnt]# systemctl stop mariadb.service 
[root@localhost mnt]# mysqld_safe --skip-grant-tables &
[root@localhost mnt]# mysql
UPDATE mysql.user set authentication_string=password('lee') WHERE User='root';
SELECT * FROM westos.linux;

[root@localhost mnt]# ps aux | grep mysql
[root@localhost mnt]# kill -9 6383
[root@localhost mnt]# systemctl start mariadb
[root@localhost mnt]# mysql -uroot -p  

7、用户授权

##超户
[root@localhost mnt]# mysql -uroot -p
MariaDB [(none)]> CREATE USER westos@localhost identified by 'westos';  ##只能用localhost登陆
MariaDB [(none)]> SELECT User FROM mysql.user;
MariaDB [(none)]> SHOW GRANTS FOR westos@localhost;
MariaDB [(none)]> GRANT SELECT ON westos.* TO westos@localhost;
MariaDB [(none)]> GRANT INSERT ON westos.* TO  westos@localhost;
MariaDB [(none)]> REVOKE INSERT ON westos.* FROM westos@localhost; ##收回插入权力
MariaDB [(none)]> DROP USER westos@localhost; ##删除用户

##普通用户
[root@localhost mnt]# mysql -uwestos -pwestos;
MariaDB [(none)]> SHOW DATABASES;
MariaDB [(none)]> USE westos;
MariaDB [westos]> SHOW TABLES;
MariaDB [westos]> SELECT * FROM linux;
MariaDB [(none)]> INSERT INTO westos.linux VALUES ('lee','123','20','python');
MariaDB [(none)]> DELETE FROM westos.linux WHERE username='lee'; ##没有授权删除功能，不能删除。

8、phpmyadmin的安装

搭建web形式的管理器

[root@localhost mnt]# dnf install php httpd -y
[root@localhost mnt]# dnf install tar bzip2 -y
[root@localhost html]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@localhost html]# mv phpMyAdmin-3.4.0-all-languages myadmin
[root@localhost html]# dnf search php
[root@localhost html]# dnf install php-mysqlnd.x86_64 -y
[root@localhost html]# cd myadmin
[root@localhost myadmin]# less README
[root@localhost myadmin]# vim Documentation.txt  ##查看是否有内容
[root@localhost myadmin]# cp config.sample.inc.php config.inc.php
[root@localhost myadmin]# systemctl enable --now httpd
[root@localhost myadmin]# systemctl restart httpd
196.168.0.66/myadmin   

9、远程登陆数据库

[root@localhost ~]# systemctl stop mariadb
[root@localhost ~]# rm -fr /var/lib/mysql/
[root@localhost ~]# dnf reinstall mariadb-server -y

[root@localhost ~]# ll /etc/my.cnf.d/mariadb-server.cnf 
[root@localhost ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
# skip-networking=1 注释
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# netstat -antlupe | grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      27         2452708    48881/mysqld      
[root@localhost ~]#  mysql -uroot -e "SELECT User,Host FROM mysql.user;"
+------+-----------+
| User | Host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
| root | server1   |
+------+-----------+
[root@localhost ~]# mysql -uroot -h 192.168.0.66 ##在本机内可以登陆

[root@localhost ~]# mysql -uroot -e "CREATE USER linux@'%' identified by 'linux';"
[root@localhost ~]# mysql -uroot -e "SELECT User,Host FROM mysql.user;"
+-------+-----------+
| User  | Host      |
+-------+-----------+
| linux | %         |
| root  | 127.0.0.1 |
| root  | ::1       |
| root  | localhost |
| root  | server1   |
+-------+-----------+

[root@foundation  ~]# mysql -ulinux -plinux -h 192.168.0.1  ##192.168.0.100远程登陆
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# ss -antlupe | grep mysql