###数据库###

####mariadb数据库的安装###
1.查看yum配置
cd /etc/yum.repos.d/

vim rhel_dvd.repo ##配置yum文件
[rhel_dvd]
gpgcheck = 0
enabled = 1
baseurl = http://172.25.254.250/rhel7.0/x86_64/dvd
name=rhel
yum repolist ##yum 安装包查看
Loaded plugins: langpacks
repo id repo name status
rhel_dvd rhel 4,305
repolist: 4,305

###安装数据库###
yum install mariadb-server -y

systemctl start mariadb ##开启数据库
systemctl enable mariadb ##设定开机自动打开
mysql ##进入数据库（数据库命令一般用大写）

####数据库的安全初始化
默认情况下数据库的网络接口是打开的，为了安全需要关闭接口拒绝外部通过网络访问数据库
vim /etc/my.cnf ##关闭网络接口
skip-networking=1

systemctl restart mariadb

[root@foundation95 ~]# netstat -antlupe |grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      27         75005      2674/mysqld         
[root@foundation95 ~]# vim /etc/my.cnf
[root@foundation95 ~]# systemctl restart mariadb

[root@foundation95 ~]# netstat -antlupe |grep mysql

数据库起始状态设定信息不安全，都可以进入，需做以下设定：
mysql_secure_installation ##设定安全密码
mysql -uroot -p ##超级用户登陆

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] y
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!

mysql -uroot -pwestos ##进入数据库的方式会显现密码，一般为了数据库的保密性不建议使用

###数据库的管理 （注：在数据库中输入的命令必须以“；”结尾才能执行命令)
SHOW DATABASES ##查询库信息

USE mysql ##进入数据库

SHOW TABLES ##查看库中表格

SELECT * FROM user ##查看一个表格中的所有信息

SELECT Host FROM user WHERE User= ‘root’ ##按条件查询表中信息

2 .建立
SHOW DATABASES； ##列出库

Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

CREATE DATABASE westos; ##建立库
USE westos; ##进入库

CREATE TABLE linux ##建立表
MariaDB [westos]> CREATE TABLE linux (
-> username varchar(10) not null,
-> password varchar(8) not null
-> );

DESC linux ##查看表结构

MariaDB [westos]> DESC linux
-> ;
±---------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±------------±-----±----±--------±------+
| username | varchar(10) | NO | | NULL | |
| password | varchar(8) | NO | | NULL | |
±---------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)

INSERT INTO linux VALUES (‘haha’,‘12345’) ##插入数据到linux表格
MariaDB [westos]> INSERT INTO linux VALUES (‘haha’,‘12345’);
Query OK, 1 row affected (0.33 sec)

MariaDB [westos]> INSERT INTO linux VALUES (‘tony’,‘12345’);
Query OK, 1 row affected (0.33 sec)

SELECT * FROM linux; ##查询所有字段在linux表格中
MariaDB [westos]> SELECT * FROM linux;
±---------±---------+
| username | password |
±---------±---------+
| haha | 12345 |
| tony | 12345 |
±---------±---------+
2 rows in set (0.00 sec)

SELECT username,password from linux; ##查询指定字段在linux表中

2.更改
UPDATE linux SET password=‘1234’ WHERE username=‘haha’ ##更改用户名和密码

ALTER TABLE linux RENAME redhat ##更改表格名称为redhat

ALTER TABLE linux ADD class varchar(9) not null; ##添加class 表列,默认最后位置
ALTER TABLE linux ADD classroom varchar(9) not null AFTER username; ##指定位置添加classrom位置
ALTER TABLE linux DROP class ##移除class
MariaDB [westos]> SELECT * FROM linux

| username | password |
+----------+----------+
| haha     | 12345    |
| tony     | 12345    |
+----------+----------+
2 rows in set (0.00 sec)

MariaDB [westos]> UPDATE linux set password='4567'; ##更改密码
Query OK, 2 rows affected (0.34 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [westos]> SELECT * FROM linux
    -> ;
+----------+----------+
| username | password |
+----------+----------+
| haha     | 4567     |
| tony     | 4567     |
+----------+----------+
2 rows in set (0.00 sec)

 UPDATE linux set password='1234' WHERE username='haha'  ##更改haha的密码
  
Query OK, 1 row affected (0.33 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [westos]> SELECT * FROM linux
    -> ;
+----------+----------+
| username | password |
+----------+----------+
| haha     | 1234     |
| tony     | 4567     |
+----------+----------+
2 rows in set (0.00 sec)

##4删除
DELETE FROM linux WHERE
username= ‘haha’ ##删除haha这一行
DROP TABLE westoes ##删除westos表
DROP DATABLE linux ##删除linux库

##用户的授权
CREATE USER student@‘localhost’ identified by ‘1234’; ##建立用户student,密码1234
新建的用户可以登陆数据库,但对数据库没有任何权限看不到任何内容
GRANT SELECT,INSERT ON westos.* TO student@localhost; ##给student和westos数据库一样的权限

REVOKE INSERT ON westos.* FROM student@localhost; ##收回权限
DROP USER student@localhost; ##删除添加的用户

##5.数据库的备份
mysqldump -uroot -pwestos westos > /mnt/westos.sql ##将westos的数据备份到 /mnt/westos.sql
mysqldump -uroot -pwestos westos --no-data ##只备份westos结构不备份数据内容
mysqldump -uroot -pwestos --all-database ##对所有数据库内容进行备份
mysqldump -uroot -pwestos --all-database --no-date ##所有数据库只备份结构不备份数据内容

恢复方式1
mysql -uroot -pwestos -e “CREATE DATABASE westos;”##数据库westos的建立
mysql -uroot -pwestos westos < /mnt/westos.sql
mysql -uroot -pwestos -e "DROP DATABASE westos; ##数据库westos的删除

恢复方式2
vim /mnt/westos.sql
CREATE DATABASE westos;
USE westos;

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

[root@localhost ~]# mysql -uroot -pwestos westos < /mnt/westos.sql
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 24
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| westos             |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> quit
Bye

##数据库密码的更改
mysqladmin -uroot -pwestos password lee123 ##当知道密码时直接更改
update mysql.user set Password=password(‘westos’) where User=‘root’; ##进入数据库更该
systemctl restart mariadb.service ##重启之后密码改变

当超级用户忘记密码时更改
systemctl stop mariadb ##关闭数据库
mysqld_safe --skip-grant-tables & ##跳过验证列表进行登陆,并后台运行
mysql ##进入数据库
update mysql.user set Password=password(‘westos’) whwre User=‘root’; ##更新密码
killall -9 mysql ## 结束进程
##查看没被杀死的进程
systemctl restart mariadb.service ##重启

[root@localhost ~]# mysqld_safe --skip-grant-tables &
[1] 29835
[root@localhost ~]# 190427 14:19:53 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
190427 14:19:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
mysql;
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>  update mysql.user set Password=password('westos') where User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0