mysql 数据库的基本管理

###### 1.数据库的介绍 ######
1.什么是数据库
数据库就是个高级的表格软件

2.常见数据库
Mysql Oracle    mongodb  db2 sqlite sqlserver .......

3.Mysql (SUN  -----> Oracle)

4.mariadb

###### 2.mariadb的安装 ######
dnf install mariadb-server.x86_64 -y

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

###### 4.数据库开启  ######
systemctl enable --now mariadb

###### 5.数据库的安全初始化 ######
1.关闭数据库开放端口
vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
skip-networking=1

systemctl restart mariadb

ss -antlupe  | grep mysql    #此命令查询不到端口

2.执行安全初始化脚本
mysql_secure_installation

[root@localhost1 ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

 

[root@Mariadb ~]# mysql            ##默认不需要密码，初始化完毕后需要
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@Mariadb ~]# mysql -uroot -p    ## -u 指定登陆用户 -p 密码

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

 

2.新建
CREATE DATABASE westos;            ##新建库
CREATE TABLE linux (
username varchar(6) not null,
password varchar(30) not null
);                        ##新建表
DESC linux;                    ##显示表结构
INSERT INTO linux VALUES ('user1','123');     #插入数据
FLUSH PRIVILEGES;                #刷新数据库

 

3.更改
ALTER TABLE  linuxRENAMEredhat;
ALTER TABLE redhat ADD age varchar(4) AFTER password;
ALTER TABLE redhat DROP age;
UPDATE redhat SET password='g' WHERE username='user2';

4.删除
DELETE from redhat where username='user2' and password='g';
DROP TABLE redhat;
DROP DATABASE westos;

###### 7.数据密码管理 ######
1.数据密码更改
mysqladmin  -uroot -pwestos password lee

2.数据库密码破解
systemctl stop mariadb
mysqld_safe --skip-grant-tables &
UPDATE mysql.user set Password=password('lee') WHERE User='root';        ##RHEL7
UPDATE mysql.user set authentication_string=password('lee') WHERE User='root';    ##RHEL8

flush privileges;

ps aux | grep mariadb
kill -9 mysql的所有进程
systemctl start mariadb

###### 8.用户授权 #####
CREATE USER lee@localhost identified by 'lee';    ##只能用localhost登陆
CREATE USER lee@% identified by '%';        ##可以通过网络或localhost登陆
GRANT INSERT,SELECT  ON westos.* TO lee@localhost;
SHOW GRANTS for lee@localhost;
REVOKE SELECT ON westos.* FROM lee@localhost;
DROP user lee@localhost;

 

###### 9.数据库的备份 #####
mysqldump -uroot -pwestos --all-database
mysqldump -uroot -p westos --all-database --no-data    没有数据信息

备份

mysqldump -uroot -pwestos westos
mysqldump -uroot -pwestos westos > /mnt/westos.sql
 

恢复

test1

mysql -uroot -pwestos -e "DROP DATABASE westos;"    先进行库删除，以便验证下一步恢复。

mysql -uroot -pwestos -e "create database westos;"创建库
mysql -uroot -pwestos westos < /mnt/westos.sql  恢复库中的数据信息

test2

mysql -uroot -pwestos -e "DROP DATABASE westos;"   

vim /mntwestos.sql

CREATE DATABASE westos;

USE westos;

mysql -uroot -pwestos < /mnt/westos.sql

 

###### 10.phpmyadmin的安装 #####
dnf install httpd php php-mysqlnd -y
systemctl enable --now httpd
systemctl stop firewalld
cp phpMyAdmin-3.4.0-all-languages.tar. bz2 /var/www/html/
cd /var/www/html/
tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2
mv phpMyAdmin-3.4.0-all-languages/ mysqladmin
cd mysqladmin
cp config.sample.inc.php  config.inc.php
vim config.inc.php
$cfg['blowfish_secret'] = 'ba17c1ec07d65003';
systemctl restart httpd.server

 

 

 

firefox http://192.168.0.12/mysqladmin