(22) linux 数据库管理
数据库
数据库的类型
db2 oracle mysql（mariadb） sqlserver
1.重置虚拟机，修改网络配置、主机名和yum源
2.安装数据库（mariadb）服务
若不知道mariadb的安装包：
[[email protected] ~]# yum search mariadb
3.数据库相关设置
打开并设置开启自启动
[[email protected] ~]# systemctl start mariadb
查看数据库接口mysql ##3306接口打开不安全
[[email protected] ~]# netstat -antuple | grep mysql
关闭数据库接口（修改配置文件/etc/my.cnf）
skip-networking=1
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
Set root password? [Y/n] Y
Remove anonymous users? [Y/n]
Disallow root login remotely? [Y/n]
Remove test database and access to it? [Y/n]
登陆数据库 ##注意：密码不要写在-p后（quit退出）
[[email protected] ~]# mysql -uroot -p
Enter password:
二、数据库编辑
1.显示数据库（相当于ls）
MariaDB [(none)]> show databases; (注意：命令以；结尾)
2.进入数据库（相当于cd）
MariaDB [(none)]> use mysql; ##切换到mysql数据库中
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
3.显示数据库中的表格（相当于ls）
4.查询user表中的Host,User,Password信息
MariaDB [mysql]> select Host,User,Password from user;
5.查询user表的数据结构 desc user;
MariaDB [mysql]> desc user;
6.新建数据库 create database westos;
MariaDB [(none)]> create database westos;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
7.新建数据库westos中的表格 create table linux
MariaDB [(none)]> use westos;
Database changed
MariaDB [westos]> show tables;
Empty set (0.00 sec)
MariaDB [westos]> create table linux (
-> username varchar(50) not null, ##字符类型为char，不能为空
-> password varchar(50) not null ##字符类型为char，不能为空
-> );
Query OK, 0 rows affected (0.01 sec)
MariaDB [westos]> show tables;
8.在linux表格中插入信息 insert into linux values('hi','1234');
MariaDB [westos]> insert into linux values('hi','1234');
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> select * from linux; ##查看linux表格中的所有信息
9.修改linux表格中的信息（例：password）
MariaDB [westos]> update linux set password='01230' where username='hi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
10.在linux表格中添加age字段
MariaDB [westos]> alter table linux add age varchar(4) after username;
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
11.在linux表格中移除age字段
MariaDB [westos]> alter table linux drop age;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
12.修改表格名字
MariaDB [westos]> alter table linux rename mydata;
Query OK, 0 rows affected (0.01 sec)
MariaDB [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| mydata |
+------------------+
13.删除表格中的某一行
MariaDB [westos]> delete from mydata where username='hi';
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> select * from mydata;
+----------+----------+
| username | password |
+----------+----------+
| hello | 123 |
+----------+----------+
14.删除表格
MariaDB [westos]> drop table mydata;
Query OK, 0 rows affected (0.01 sec)
MariaDB [westos]> show tables;
Empty set (0.00 sec)
15.删除数据库
MariaDB [westos]> drop database westos;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
三、数据库用户管理
1.用户创建
MariaDB [(none)]> create user [email protected] identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)
##[email protected] 本地用户 ##[email protected]‘%’ 远程用户
2.用户授权
MariaDB [(none)]> grant INSERT,SELECT on westos.* to [email protected];
MariaDB [(none)]> show grants for [email protected];
3.此时用hate用户登陆实验
具备select权限，可以查看westos数据库：
MariaDB [(none)]> show databases;
具备insert权限，可以在westos.linux表格中插入：
MariaDB [(none)]> insert into westos.linux values('hi','123');
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> select * from westos.linux;
+----------+----------+
| username | password |
+----------+----------+
| hello | 123 |
| hi | 123 |
+----------+----------+
不具备delete权限，不能删除：
MariaDB [(none)]> delete from westos.linux where username='hi';
ERROR 1142 (42000): DELETE command denied to user 'hate'@'localhost' for table 'linux'
4.撤销用户权限（切换root用户）
MariaDB [(none)]> revoke INSERT on westos.* from [email protected];
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for [email protected];
5.重载授权表 flush privileges
MariaDB [(none)]> flush privileges;
6.删除用户
MariaDB [(none)]> drop user [email protected];
四、数据库资料备份
1.数据库资料备份方式
mysqldump -uroot -predhat --all-database > /mnt/westos.all ##所有数据库资料备份
mysqldump -uroot -predhat --all-database --no-data > /mnt/westos.err ##只备份数据结构，不备份数据
2.数据恢复
注意：当备份的数据库不存在时，会报错（例：删除westos数据库）
[[email protected] ~]# mysql -uroot -predhat < /mnt/westos.sql
恢复方式1：修改备份文件 /mnt/westos.sql
CREATE DATABASE westos;
USE westos;
DROP TABLE IF EXISTS `linux`;
[[email protected] ~]# mysql -uroot -predhat < /mnt/westos.sql
恢复后，登陆数据库查看
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
恢复方式2：建立westos数据库
[[email protected] ~]# mysql -uroot -predhat -e "create database westos;"
[[email protected] ~]# mysql -uroot -predhat westos < /mnt/westos.sql
登陆数据库查看
MariaDB [(none)]> show databases;
五、用户密码更改
1.若用户密码忘记，关闭数据库服务，跳过认证列表
[[email protected] ~]# systemctl stop mariadb.service
[[email protected] ~]# mysqld_safe --skip-grant-tables &
[1] 2678
[[email protected] ~]# 180428 01:33:00 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
180428 01:33:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2.进入数据库修改（mysql回车即可）
MariaDB [(none)]> update mysql.user set Password='redhat' where User='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
查看密码 select * from mysql.user ##以上修改密码为明文
| localhost | root | redhat | Y
以加密方式修改密码：
MariaDB [mysql]> update mysql.user set Password=password('123') where User='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
再次查看密码（mysql.user）：
| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y
3.查看mysql相关进程，全部关闭
[[email protected] ~]# ps aux | grep mysql
root 3228 0.0 0.1 113252 1560 pts/1 S 01:44 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql 3383 0.1 9.2 859064 89276 pts/1 Sl 01:44 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root 3416 0.0 0.0 112640 936 pts/1 R+ 01:45 0:00 grep --color=auto mysql
关闭后重启mysql服务，新密码登陆即可
[[email protected] ~]# systemctl restart mariadb.service
[[email protected] ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB Server
数据库的类型
db2 oracle mysql（mariadb） sqlserver
数据库相当于高级的excel表格，其中的字段相当于
一、数据库的设置
1.重置虚拟机，修改网络配置、主机名和yum源
vim /etc/sysconfig/network-scripts/ifcfg-eth0
hostnamectl set-hostname testdb.westos.com(修改主机名字)
vim /etc/yum.repos.d/yum.repo
2.安装数据库（mariadb）服务
若不知道mariadb的安装包：
[[email protected] ~]# yum search mariadb
yum install mariadb-server.x86_64 -y
3.数据库相关设置
打开并设置开启自启动
[[email protected] ~]# systemctl start mariadb
[[email protected] ~]# systemctl enable mariadb
查看数据库接口mysql ##3306接口打开不安全
[[email protected] ~]# netstat -antuple | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 68511 3272/mysqld
关闭数据库接口（修改配置文件/etc/my.cnf）
skip-networking=1
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
(修改配置文件后重起服务systemctl restart mariadb)
重启后再次查看数据库接口
[[email protected] ~]# netstat -antuple | grep mysql
修改数据库密码 mysql_secure_installation
Enter current password for root (enter for none):Set root password? [Y/n] Y
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]
登陆数据库 ##注意：密码不要写在-p后（quit退出）
[[email protected] ~]# mysql -uroot -p
Enter password:
二、数据库编辑
1.显示数据库（相当于ls）
MariaDB [(none)]> show databases; (注意：命令以；结尾)
2.进入数据库（相当于cd）
MariaDB [(none)]> use mysql; ##切换到mysql数据库中
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
3.显示数据库中的表格（相当于ls）
4.查询user表中的Host,User,Password信息
MariaDB [mysql]> select Host,User,Password from user;
5.查询user表的数据结构 desc user;
MariaDB [mysql]> desc user;
6.新建数据库 create database westos;
MariaDB [(none)]> create database westos;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
7.新建数据库westos中的表格 create table linux
MariaDB [(none)]> use westos;
Database changed
MariaDB [westos]> show tables;
Empty set (0.00 sec)
MariaDB [westos]> create table linux (
-> username varchar(50) not null, ##字符类型为char，不能为空
-> password varchar(50) not null ##字符类型为char，不能为空
-> );
Query OK, 0 rows affected (0.01 sec)
MariaDB [westos]> show tables;
8.在linux表格中插入信息 insert into linux values('hi','1234');
MariaDB [westos]> insert into linux values('hi','1234');
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> select * from linux; ##查看linux表格中的所有信息
9.修改linux表格中的信息（例：password）
MariaDB [westos]> update linux set password='01230' where username='hi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
10.在linux表格中添加age字段
MariaDB [westos]> alter table linux add age varchar(4) after username;
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
11.在linux表格中移除age字段
MariaDB [westos]> alter table linux drop age;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
12.修改表格名字
MariaDB [westos]> alter table linux rename mydata;
Query OK, 0 rows affected (0.01 sec)
MariaDB [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| mydata |
+------------------+
13.删除表格中的某一行
MariaDB [westos]> delete from mydata where username='hi';
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> select * from mydata;
+----------+----------+
| username | password |
+----------+----------+
| hello | 123 |
+----------+----------+
14.删除表格
MariaDB [westos]> drop table mydata;
Query OK, 0 rows affected (0.01 sec)
MariaDB [westos]> show tables;
Empty set (0.00 sec)
15.删除数据库
MariaDB [westos]> drop database westos;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
三、数据库用户管理
1.用户创建
MariaDB [(none)]> create user [email protected] identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)
##[email protected] 本地用户 ##[email protected]‘%’ 远程用户
2.用户授权
MariaDB [(none)]> grant INSERT,SELECT on westos.* to [email protected];
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show grants for [email protected];
3.此时用hate用户登陆实验
具备select权限，可以查看westos数据库：
MariaDB [(none)]> show databases;
具备insert权限，可以在westos.linux表格中插入：
MariaDB [(none)]> insert into westos.linux values('hi','123');
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> select * from westos.linux;
+----------+----------+
| username | password |
+----------+----------+
| hello | 123 |
| hi | 123 |
+----------+----------+
不具备delete权限，不能删除：
MariaDB [(none)]> delete from westos.linux where username='hi';
ERROR 1142 (42000): DELETE command denied to user 'hate'@'localhost' for table 'linux'
4.撤销用户权限（切换root用户）
MariaDB [(none)]> revoke INSERT on westos.* from [email protected];
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for [email protected];
5.重载授权表 flush privileges
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
6.删除用户
MariaDB [(none)]> drop user [email protected];
Query OK, 0 rows affected (0.00 sec)
四、数据库资料备份
1.数据库资料备份方式
mysqldump -uroot -predhat --all-database > /mnt/westos.all ##所有数据库资料备份
mysqldump -uroot -predhat --all-database --no-data > /mnt/westos.err ##只备份数据结构，不备份数据
mysqldump -uroot -predhat westos > /mnt/westos.sql ##指定westos数据库的资料备份
2.数据恢复
注意：当备份的数据库不存在时，会报错（例：删除westos数据库）
[[email protected] ~]# mysql -uroot -predhat < /mnt/westos.sql
ERROR 1046 (3D000) at line 22: No database selected
恢复方式1：修改备份文件 /mnt/westos.sql
CREATE DATABASE westos;
USE westos;
DROP TABLE IF EXISTS `linux`;
[[email protected] ~]# mysql -uroot -predhat < /mnt/westos.sql
恢复后，登陆数据库查看
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
恢复方式2：建立westos数据库
[[email protected] ~]# mysql -uroot -predhat -e "create database westos;"
[[email protected] ~]# mysql -uroot -predhat westos < /mnt/westos.sql
登陆数据库查看
MariaDB [(none)]> show databases;
五、用户密码更改
1.若用户密码忘记，关闭数据库服务，跳过认证列表
[[email protected] ~]# systemctl stop mariadb.service
[[email protected] ~]# mysqld_safe --skip-grant-tables &
[1] 2678
[[email protected] ~]# 180428 01:33:00 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
180428 01:33:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2.进入数据库修改（mysql回车即可）
MariaDB [(none)]> update mysql.user set Password='redhat' where User='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
查看密码 select * from mysql.user ##以上修改密码为明文
| localhost | root | redhat | Y
以加密方式修改密码：
MariaDB [mysql]> update mysql.user set Password=password('123') where User='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
再次查看密码（mysql.user）：
| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y
3.查看mysql相关进程，全部关闭
[[email protected] ~]# ps aux | grep mysql
root 3228 0.0 0.1 113252 1560 pts/1 S 01:44 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql 3383 0.1 9.2 859064 89276 pts/1 Sl 01:44 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root 3416 0.0 0.0 112640 936 pts/1 R+ 01:45 0:00 grep --color=auto mysql
关闭后重启mysql服务，新密码登陆即可
[[email protected] ~]# systemctl restart mariadb.service
[[email protected] ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB Server