01-数据库管理服务

数据库管理系统

数据库介绍

• 数据是应用的核心

• 按照结构组织、存储和管理数据的参仓库

• 可视为电子化的文件柜（增删改查）

• 数据挖掘透视表象背后的真相

• 关系型数据库
  • 库、表、字段、记录
  • SQL
• 非关系型数据库NoSQL
  • 大数据技术的发展暴露出关系型数据库的问题
  • 存储不同格式的数据类型（文件型数据）
  • 图形数据库、文档数据库、键值对数据库、大表
• NoSQL并非取代SQL，而是互补

数据库管理系统

• Database Management System （DBMS）
• 操纵和管理数据库的软件
• 功能作用
  • 数据定义 ， 定义数据结构及完整性机密性约束
  • 数据操作
  • 运行管理，多用户、安全性、存取限制、事务管理、自动恢复
  • 组织与存储 分类管理各种数据
  • 数据保护 恢复、并发控制、完整性、机密性
  • 数据库维护 数据载入、转换、重组重构
  • 通信 OS接口、网络通信

MySQL安装配置

sudo apt install mysql-server

安全配置

sudo mysql_secure_installation

root账号密码登录

sudo mysql
select user,host,plugin,authentication_string from mysql.user;
alter user 'root'@'localhost' identified with mysql_native_password by '12345678';
flush privileges;

mysql -u root -p

--创建账户
create user 'cwz'@'localhost' identified by '123'; 

--给账号赋予权限
grant all privileges on *.* to 'cwz'@'localhost' with grant option;


--收回权限
revoke all @db from user;

--修改密码
set password for 'root'@'localhost'=password('123456789')

网络访问账号

--进入mysql库
use mysql;
--修改网络连接权限
grant all on *.* to 'admin'@'%' identified by '12345678' with grant option;

--查看密码策略
select @@validate_password_policy;

sudo netstat -pantu | grep 3306

此时mysql只是监听在127.0.0.1这个本机地址，需要更改配置文件

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

找到bind-address 后面改为网卡ip地址 sudo systemctl restart mysql.service

再次进入mysql，执行select @@validate_password_policy，此时密码等级提升到了MEDIUM

SQL语句

--当前账户
select user();

--查看已有库
show databases;

--创建库
create database test;

--选择库
use test;
select database();

--显示表
show tables;

创建表

mysql> create table users(id int not null primary key auto_increment,
    -> uname char(25),
    -> pass char(25),
    -> age int(5));

表数据描述：

包含4列：id、uname、pass、age

• int（5）：数据类型为整数（长度5）
• not null：字段不能为空
• primary key：主键（不能包含重复数据）
• auto_increment：自动增长
• char（25）：固定长字符串（是字符数，不是字节数）
• varchar（20）：可变长字符串
• text：最大64KB文本可变长字符串
• date：日期值（年，月，日）
• time：时间值（时，分，秒）
• enum（’value1’, ‘value2’）：枚举值列表

插入数据

insert into users values(null ,'cwz', '123', 20);  --要与字段名一一对应

insert into users(id,uname,pass) values(null,'neo','456');

查看表结构

desc users;

show create table users;    # InnoDB 存储引擎信息

show full columns from users;   # 查看列

查询

select * from users;  # 查询所有信息

select * from users where age<30;

select uname,pass order by age;  # 排序，默认从小到大

select uname,pass order by age desc;  # 从大到小排序

--concat:结合查询
select concat(uname,'(',pass,')') as user_pass from users;
+-----------+
| user_pass |
+-----------+
| cwz(123)  |
| neo(456)  |
+-----------+

修改数据

update users set age=18 where id=2;

删除数据

delete from users where id=2;

删除表

drop table users;

删除库

drop database test;

查看数据引擎

show engines;
alter table student engine=MyISAM;  # 更改引擎

修改表名

alter table users rename user;

修改字段数据类型

alter table user modify uname varchar(30);

修改字段名

alter table user change uname username varchar(30);

增加字段

alter table user add tel char(20) not null after pass;

删除字段

alter table user drop tel;

数据库备份

备份数据库、表

mysqldump -uroot -p test > db_bak.sql  # 备份库

mysqldump -uroot -p test user > db_user_bak.sql  # 备份表

mysqldump --all-databases -u root -p > all_bak.sql  # 备份所有

备份指定数据

echo 'select * from user;' > q.sql
mysql -u root -p test < q.sql > out.csv

在mysql界面备份

mysql> show variables like "%secure%";
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+

--把数据到出到/var/lib/mysql-files/文件中
select * from user into outfile '/var/lib/mysql-files/a.csv';

在终端导入

mysqladmin -u root -p create db2;  # 创建库db2
mysql -u root -p db2 < db_bak.sql  # 导入库的数据

在MySQL中导入csv文件

load data infile '/var/lib/mysql-files/a.csv' into table user;

MariaDB

与MySQL类似，是MySQL的开源版本

安装

sudo apt install mariadb-server

安全设置

mysql_secure_installation

改为密码认证

sudo mysql
use mysql;
update user set plugin='' where user='root';
flush privileges;

配置文件

/etc/mysql/mariadb.conf.d/50-server.cnf

数据库主从复制

如果服务器的硬件出现问题了，就会对企业有很大影响。所以数据库需要定期备份。

复制Replication

• Master——>Slave # 主备服务器，master坏了，slave可以读，升级成为Master
• 限定所有库、指定库、表进行复制

MySQL的主从复制是MySQL本身自带的一个功能，不需要额外的第三方软件就可以实现，其复制功能并不是copy文件来实现的，而是借助binlog日志文件里面的SQL命令实现的主从复制，可以理解为我再Master端执行了一条SQL命令，那么在Salve端同样会执行一遍，从而达到主从复制的，同步数据的效果。

主从复制原理

MySQL中有一种日志叫做 bin 日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL 语句

MySQL的主从复制是MySQL本身自带的一个功能，不需要额外的第三方软件就可以实现，其复制功能并不是copy文件来实现的，而是借助binlog日志文件里面的SQL命令实现的主从复制。可以理解为我在Master端执行了一条SQL命令，那么在Salve端同样会执行一遍，从而达到主从复制的效果。

• 从库生成两个线程，一个I/O线程，一个SQL线程；I/O线程去请求主库 的binlog，并将得到的 binlog日志 写到relay log（中继日志） 文件中；

• 主库会生成一个 log dump 线程，用来给从库 I/O线程传binlog； SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致。

• bin-log 二进制日志

• 所有DB事件写入bin-log
• Slave从Master读取bin-log

复制过程

• 主节点必须启用二进制日志，记录任何修改了数据库数据的事件。
• 从节点开启一个线程（I/O Thread)把自己扮演成 mysql 的客户端，通过 mysql 协议，请求主节点的二进制日志文件中的事件
• 主节点启动一个线程（dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
• 从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内部的哪一个位置。
• 从节点启动另外一个线程（sql Thread ），把 Relay log 中的事件读取出来，并在本地再执行一次。

主从复制中线程的作用

从节点

• I/O Thread: 从 Master 节点请求二进制日志事件，并保存于中继日志中。
• Sql Thread: 从Relay log 中读取日志事件并在本地完成重放。
• 如果从节点需要作为其他节点的主节点时，是需要开启二进制日志文件的。这种情况叫做级联复制。如果只是作为从节点，则不需要创建二进制文件。

主节点

• Dump Thread:为每个 Slave 的 I/O Thread 启动一个 dump 线程，用于向从节点发送二进制事件。

MySQL复制的特点

1. 异步复制：主节点中一个用户请求一个写操作时，主节点不需要把写的数据在本地操作完成同时发送给从服务器并等待从服务器反馈写入完成，再响应用户。主节点只需要把写入操作在本地完成，就响应用户。但是，从节点中的数据有可能会落后主节点，可以使用（很多软件来检查是否落后）
2. 主从数据不一致。

用途

• 扩展：Slave分担master负载
• 分析：数据分析不影响Master
• 备份：作为一种数据备份手段
• 分布：异地保存数据副本（应用访问本地库）

安装两台独立的MariaDB服务器

• 复制前用导入导出数据库的方式保持M / S数据一致
• 复制前锁定库（禁止写入）
  • flush tables with read lock；

复制Master

sudo vi /etc/mysql/conf.d/mysql.cnf

# 增加如下配置
[mysqld]
log-bin    # 开启binlog功能         
binlog-do-db=test
# bing-ignore-db="mysql"   # 忽略的库
server-id=1


sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
# 修改绑定的网卡

# 最后重启MariaDB服务
sudo systemctl restart mariadb.service

配置Master

sudo mysql

-- 允许slave复制master所有库
grant replication slave on *.* to 'replicate' @'1.1.1.1' identified by 'password';

-- 刷新权限
flush privileges; 

最后重启MariaDB服务

配置Slave

sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
# 修改帧听的网卡地址

sudo vi /etc/mysql/conf.d/mysql.cnf

[mysqld]
server-id=2


# 最后重启MariaDB服务
sudo systemctl restart mariadb.service

sudo mysql

-- 复制的指令
change master to master_host="1.1.1.2", master_user='replicate',master_password='password';

打开MASTER锁

-- 如果在做复制配置之前，锁定了数据库表，可以解锁：
UNLOCK TABLES;

查看SLAVE状态

start slave; 

show slave status\G;

这样数据同步就完成了，可以在master这边修改数据，slave可以实时同步

配置调优工具包

sudo apt install mysqltuner

sudo mysqltuner

Postgresql

• 下一代关系型数据库
• 高并发情况下性能优于MySQL
• 侦听端口 TCP 5432
• 安装
  • sudo apt install postgresql
• 身份认证
  • 默认使用IDENT身份认证方式
  • 使用与操作系统同名的数据库账号
  • sudo -u postgres psql temlpate1

修改密码

alter user postgrep with encrypted password 'your_password';
\q

vi /etc/postgrep/9.5/main/pg_hba.conf
	loacl   all    postgres   md5
	
sudo systemctl restart postgresql.service
psql -U postgres -W -d dbname

配置文件

/etc/postgresql/<version>/main           # 配置文件目录
/etc/postgresql/10/main/pg_ident.conf     # IDENT身份认证配置
/etc/postgresql/10/main/postgresql.conf    # 网络身份认证配置
	# listen_addresses='*'                

NoSQL概览与Mongdb

NoSQL介绍

NoSQL是一类数据库的统称

• 每种NoSQL数据库为特定目的而设计
• NoSQL不是也不可能成为SQL数据库的代替者
• 适用于大数据集、高并发、大流量的应用场景
• 并非完全不用SQL语句

NoSQL的共性

• 存储结构化数据、但不存储数据关系

优点：

• 处理巨大数据集
• 按需扩展（横向增加主句节点）
• 硬件透明，TOC低（传统小机）
• 数据模型松散，方便修改，不下线，快速dirty修复

缺点：

• 大部分来自于小型开源项目，缺少技术支持，学习曲线陡峭，部署周期长
• 并不总是符合ACID（原子性、一致性、隔离性、持久性）
• 不保证即时正确记录，多主复制无法保证最新数据（适合互联网，不适合金融）
• NoSQL，没有绝对优势
• 使用已知的技术栈，需要时再迁移（过早优化是万恶之源）

Berkeley DB

Cassandra

Memcached/MemcacheDB

Redis

Riak

Document Stores

CouchDB

MongoDB

安装配置MongoDB

sudo apt install mongodb

登录

mongo   直接本机登录

mongo server_ip:port/db

mongodb组织数据的基本形式

MongoDB 将数据存储为一个文档，数据结构由键值(key=>value)对组成。MongoDB 文档类似于 JSON 对象。字段值可以包含其他文档，数组及文档数组。

mongodb操作

库的管理语句

• 显示所有的库 show dbs

  > show dbs
  admin  (empty)
  local  0.078GB
  > 

• 切换数据库 use 数据库名

  > use mydb
  switched to db mydb
  > 

• 查看所在库 db

  > show dbs
  admin   0.000GB
  config  0.000GB
  local   0.000GB
  mydb    0.000GB
  
  
  > show collections
  users
  > 

• 删除库 db.dropDatabase()

  > db.dropDatabase()
  { "dropped" : "test", "ok" : 1 }
  > 

MongoDB 中默认的数据库为 test，如果你没有创建新的数据库，集合将存放在 test 数据库中。

集合的管理语句

• 创建集合

  > db.createCollection('student')
  { "ok" : 1 }
  > 

• 显示当前集合

  > show collections
  student
  system.indexes
  > 

• 删除集合

> db.student.drop()
true

数据的增删改查操作

• 插入数据

  > db.student.insert({name:'reese',age:29})
  WriteResult({ "nInserted" : 1 })
  >

  插入多条数据

  > db.student.insert([
  ... {name:'neo',age:59,sex:'男'},
  ... {name:'cwz',sex:'男',age:20,addr:'mon'}])
  BulkWriteResult({
  	"writeErrors" : [ ],
  	"writeConcernErrors" : [ ],
  	"nInserted" : 2,
  	"nUpserted" : 0,
  	"nMatched" : 0,
  	"nModified" : 0,
  	"nRemoved" : 0,
  	"upserted" : [ ]
  })
  > 

• 查询数据

  > db.users.find()
  { "_id" : ObjectId("5db841899ea8d30495ed512b"), "name" : "neo", "uid" : "1001" }
  { "_id" : ObjectId("5db842269ea8d30495ed512c"), "name" : "lisi", "uid" : 1003, "gid" : [ 1001, 1002, 1003 ] }
  > 
  
  
  
  > db.users.findOne({uid:1003})
  {
  	"_id" : ObjectId("5db842269ea8d30495ed512c"),
  	"name" : "lisi",
  	"uid" : 1003,
  	"gid" : [
  		1001,
  		1002,
  		1003
  	]
  }
  
  
  
  > db.users.findOne({uid:{$gt:1002}})     # gt  great than  大于
  {
  	"_id" : ObjectId("5db842269ea8d30495ed512c"),
  	"name" : "lisi",
  	"uid" : 1003,
  	"gid" : [
  		1001,
  		1002,
  		1003
  	]
  }
  > 
  
  # 默认自动生成_id 字段

  美观查询

  > db.student.find().pretty()
  {
  	"_id" : ObjectId("5b6a3edbbb74e74630ea80f9"),
  	"name" : "reese",
  	"age" : 29
  }
  {
  	"_id" : ObjectId("5b6a4088bb74e74630ea80fa"),
  	"name" : "neo",
  	"age" : 59,
  	"sex" : "男"
  }
  {
  	"_id" : ObjectId("5b6a4088bb74e74630ea80fb"),
  	"name" : "cwz",
  	"sex" : "男",
  	"age" : 20,
  	"addr" : "mon"
  }
  > 

• 修改数据

全文档更新，普通更新

> db.student.update({name:'cwz'},{xx:'yy'})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> 

指定更新，只会更新第一条数据

> db.student.update({name:'reese'},{$set:{age:46}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

全部更新

> db.student.update({name:'reese'},{$set:{age:34},{multi:true})

• 删除数据

  > db.student.remove({name:'cwz'})
  # 不加参数会删除所有数据
  
  
  > db.student.remove({name:'cwz'},{justone:1})
  # 删除一条数据

python操作mongodb

建立连接

import pymongo

# 建立连接
client = pymongo.MongoClient('127.0.0.1', 27017)

# 获取数据库
db = client['test']

# 获取集合
col = db['student']

# 测试一下是否成功
data = col.find()
print(data)

主要方法：

• insert_one
• insert_many
• update_one
• update_many
• delete_one
• delete_many
• find_one
• find

find_one

import pymongo

# 建立连接
client = pymongo.MongoClient('127.0.0.1', 27017)

# 获取数据库
db = client['test']

# 获取集合
col = db['student']

# 测试一下是否成功
data = col.find_one()
print(data)

# 打印结果：
{'_id': ObjectId('5b6a3edbbb74e74630ea80f9'), 'age': 46.0, 'name': 'reese'}