Mysql数据库管理系统原理及基本操作

Mysql数据库管理系统原理

一.引子：

mysql数据库管理系统是我国常见的关系型数据库管理系统，mysql严格来说不能算是数据库，它只是一个数据库管理系统。mysql相对于企业来说就相当于百度云盘相对于个人用户，都是用来更加方便安全地去存储数据。

二、数据库种类:

层次模型：按照层次结构的形式组织数据库数据的模型
缺点：拥有大量的冗余数据 （IMS）
网状模型：按照网状结构的形式组织数据库数据的模型
缺点：后期维护困难 （DBTG）
关系模型（RDBMS：Relational Database Management System）：按照数据关系的形式组织数据库数据的模型

三、数据库解决的问题:

原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。

一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

四、数据库的作用:

数据库：指的是以一定的方式存在一起、能为多个用户共享、具有尽可能小的冗余度的特点、是与应用程序彼此独立的数据集合

实现数据共享
减少数据的冗余度
数据实现集中控制
数据的一致性
故障恢复

五、SQL结构语句:

结构化查询语言 ( Structured Query Language ) 简称 SQL，结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；最早诞生于 IBM SYSTEMR 操作系统之上
DML：数据操作语言，操作数据库中数据的命令 select insert update delete
DDL：数据库描述语言，建立数据库、定义数据关系 create drop
DCL：数据库控制语言，控制数据库组件的权限 grant

六、关系型数据库结构:

文件逻辑关系：
上层：逻辑方式保存文件
底层：数据块方式以二进制的形式存储在硬盘
中间层：文件系统

数据库逻辑关系：
上层：数据表
底层：逻辑方式保存的文件
中间层：存储引擎

存储引擎：存储数据，建立索引以及更新和查询数据等技术的实现方法

七、存储引擎:

MyISAM：MYSQL 默认存储引擎，诞生即存在，插入和查询数据较快。不支持事物、行级锁以及外键约束等功能
锁：行级锁（ InnoDB ）、表级锁（ MyISAM ）、页级锁（ DBD ）
约束：域约束、外键约束
InnoDB oracle 08 5.5
支持事物、行级锁以及外键约束等功能，相对于 MyISAM 更慢
MEMORY
运行在内存中、使用 hash 索引，数据存取速度非常快。但是数据不能持久化，适用于缓存

八、数据库存储和查询:

存储管理器：通过 数据描述语言（ DDL ）来创建表的结构，在通过 数据操作语言（ DML ） 来保存 SQL 语句产生的数据的数据库组件
权限及完整性管理器
事物管理器
文件管理器
缓冲区管理器

查询管理器：接收用户的查询请求、理解查询请求、并将请求提交给存储管理器的数据库组件
DDL 和 DML 解释器
查询执行引擎

九、数据库后台工作模式:

MySQL：单进程多线程的工作模式，每个线程维持一个连接
守护线程：MySQL 不需要跟用户进行交互
应用线程：MySQL 需要跟用户进行交互

十、数据库缺点:

SMP 对称多处理器结构,一核有难,八核围观

十一、优化方案:

垂直扩展:
更换性能更高的硬性
水平扩展:
利用更多的服务器节点来承接压力

缓存:
用Rids来缓解mysql的压力
线程重用:
线程在完成连接之后不去关闭,等待其他用户来连接
分区、分库、分表、读写分离:
分区分成各种大区
在区内分库
分表要剥丝抽茧,慢慢分开关联

补充:
数据库的数据表:
frm 表的格式
MYD 表的数据
MYI 表的索引
套接字:源地址源端口目标地址目标端口的结合
连接进程
TCP UDP : IP+PORT 面向连接

十二、数据库测试与安装:

1.安装MySQL:客户端和服务器端
yum -y install mysql mysql-server
2.开启MySQL:
service mysqld start
3.主配置文件:/etc/my.cnf
端口:3306
数据目录: /var/lib/mysql
4.创建root用户:
mysqladmin -uroot password 123
5.用户登录:
mysql -u 用户名 -p 密码 -h 服务器地址 -P 端口 -S 套接字
mysql -uroot -p -P 3306 -h localhost -S /var/lib/mysql/mysql.sock
root用户默认不允许远程连接
6.创建普通用户:
create user ‘用户名’@‘登录地址’ identified by ‘密码’;
create user ‘user1’@‘localhost’ identified by ‘123’;
允许user1本地登录
create user ‘user2’@‘192.168.21.13’ identified by ‘123’;
允许user2 192.168.21.13登录
create user ‘user3’@‘192.168.21.14’ identified by ‘123’;
允许user3 192.168.21.14登录
create user ‘user4’@’%’ identified by ‘123’;
%:除了本地之外的地址登录
create user ‘user5’@’%’ identified by ‘123’;
create user ‘user5’@‘localhost’ identified by ‘123’;
既能本地登录也能远程登录
grant all on . to ‘qian’@’%’ identified by ‘123’;
windows登录远程软件：vanicat
7.MySQL修改密码:(忘记密码)
管理员自己修改密码,进入到数据库内:
set password=password(“qian”);
管理员给普通用户修改密码:
set password for ‘user3’@‘192.168.21.14’=password(“qqq”);
普通用户自己修改密码:
set password=password(“www”);
管理员忘记密码:
先关闭数据库: service mysqld stop；
打开配置文件添加: 第7行
vim /etc/my.cnf

进入 MySQL: 修改密码
update mysql.user set password=password(“123”) where user=‘root’;
再次进去配置文件删掉第7行
重启MySQL:
service mysqld restart

十三、SQL语句:（数据级别的操作）

1.数据的增加:
登录数据库:
mysql -uroot -p
创建库:
create create database hongfu;
进入库:
use hongfu;
创建表:
mysql> create table users (
-> id int unsigned not null auto_increment,
-> name char(40) not null default ‘无’,
-> age int not null default ‘0’,
-> primary key (id));
查看表:
describe users;
创建数据:
insert into users values (1, “张三”, 21);
查看数据:
select * from users;
自增长:
insert into users (name, age) values (“单独”, 23);
默认不写:
insert into users (id) values (7);

将表1复制到表2:
创建表2:
create table user (id int, name char(20), age int);
将表1全部提取到表2:
insert into user (id, name, age) select id,name,age from users;

部分提取:
insert into user (id, name) select id,name from users;

2.数据的删除:
删除库:
drop database test;
删除表:
use hongfu;
drop table user;
删除表中字段:
delete from users where id=6;
删除用户:
创建用户:
create user ‘user2’@‘192.168.21.13’ identified by ‘123’;
进MySQL库中:
Delete FROM user Where User=‘uses1’ and Host=‘localhost’;
刷新当前用户权限:
flush privileges;

3.数据的修改:
更新表中字段数值:
update users set age=10 where id=7;
select * from users;
多字段更新数值:
update users set age=40 where id between 1and 3;

更改表名:
alter table users rename usess;
select * from usess;
更改表中字段参数:
alter table usess modify age int(3);
describe usess;
更改表中字段名称:
alter table usess change age addr char(200) not null default ‘无’;
在表中添加一个字段:
alter table usess add date year;
在表中插入到第一位的一个字段:
alter table usess add age int first;
在表中间插入一个字段:
alter table usess add passwd char(15) after name;
删除表字段:
alter table usess drop date;
4.数据的查看:
查看数据库:
show databases;
查看数据表:
show tables;
查看表结构:
describe usess;
5.授权:
创建普通用户:
create user ‘qq’@‘192.168.21.14’ identified by ‘123’;
给qq用户所有权限:
grant all on . to ‘qq’@‘192.168.21.14’; 前一个* 表示所有库 ,后一个* 表示所有表
普通用户登录:

给管理员修改密码:
update mysql.user set password=password(“456456”) where user=‘root’;
修改之后要刷新:
flush privileges;

权限种类:
create(创建)、 delete(删除)、 drop(退出)、 select(选择)、 all(所有)、 grant(查看)、 slave(从设备)
查看用户权限:
show grants for ‘qq’@‘192.168.21.14’;

取消授权:
revoke insert on . from ‘qq’@‘192.168.21.14’; (取消qq添加表字段的权限)
flush privileges;
QQ用户:

添加授权:
grant insert on . to ‘qq’@‘192.168.21.14’;

创建并授权:
grant all on hongfu.* to ‘aaa’@’%’ identified by ‘123’;
给用户aaa宏福库的所有表权限
可以添加表中字段:
insert into usess (name) values (“EEID”);

十四、数据库备份:

1.第三方软件备份:

从一个终端传到另一个终端:
开发备份:
创建数据库及用户
mysqladmin -uroot password 123
mysql -uroot -p
grant all on . to ‘qq’@’%’ identified by ‘123’;

备份保存路径:
C:\Users\cn\Documents\Navicat\MySQL\servers\qian\hongfu
运维接收:
创建用户:
mysqladmin -uroot password 123
远程登录:
grant all on . to ‘root’@’%’ identified by ‘123’;
创建相同库名:
create databases hongfu;
还原备份:

select * from users;

2.系统备份:
mysqldump -uroot -p456456 hongfu > /root/hongfu.sql
将宏福库删掉:
drop database hongfu;

再创建宏福库: 这个库为空
create database hongfu;
还原备份:
mysql -uroot -p456456 hongfu < /root/hongfu.sql

将数据库一起备份:
mysqldump -uroot -p456456 --databases hongfu > /root/1hongfu.sql
还原:
mysql -uroot -p < /root/1hongfu.sql

#.mysqlhotcopy 在复制时锁定表的信息,只能读取,不能修改
mysqlhotcopy --flushlog -u=‘root’ -p=‘456456’ --regexp=^h /root/

三、日志备份:
日志的类型:
1.错误日志2.查询日志3.慢查询日志4.二进制日志5.事物日志6.中继日志
查看日志信息:
show global variables like ‘%log%’;
general_log | OFF 一般查询日志
general_log_file | /var/run/mysqld/mysqld.log
log_bin | OFF 二进制日志
log_error | /var/log/mysqld.log 错误日志
log_slow_queries | OFF 慢查询日志 官方指定10s
relay_log | 中继日志
relay_log_inde |
relay_log_info_file | relay-log.info
innodb_flush_log_at_trx_commit | 1
innodb_locks_unsafe_for_binlog | OFF

innodb_log_buffer_size |1048576 innodb_log_file_size | 5242880 innodb_log_files_in_group | 2 innodb_log_group_home_dir | ./ innodb_mirrored_log_groups | 1 事务日志

MySQL 日志
一般查询日志：记录所有 SQL 语句，一般不开启，除非在测试环境中进行代码测试
二进制日志：以二进制方式存储的日志文件，只记录对数据结构发生更改的 SQL 语句
错误日志：记录 MySQL 进程错误包括服务的开启以及关闭操作
慢查询日志：超过定义的慢的时间限定的 SQL 被保存
中继日志：主从环境中，主服务器将二进制日志传输至从服务器被保存的文件
事务日志：事务操作的记录

1.开启一般查询日志:
打开主配置文件:
vim /etc/my.cnf
7 general_log=ON
8 log=ON
会记录所有操作:

2.查询慢查询时间:
show global variables like ‘%long%’;

开启慢查询日志:
vim /etc/my.cnf

9 log-slow-queries=/var/mysql-slow.log
10 long_query_time=5

3.开启二进制日志:
vim /etc/my.cnf

11 log-bin=mysql-bin

正在运行的二进制的文件名:
show master status;

增加一条信息:
insert into users (name, age) values (“455”, 23);

查看二进制日志文件信息:
show binlog events in ‘mysql-bin.000001’;

在数据库外打开: 一定要在/var/lib/mysql/ 打开
mysqlbinlog mysql-bin.000001

4.使用二进制日志进行数据恢复:
通过大小截取:
mysqlbinlog --start-position 134 --stop-position 247 mysql-bin.000001 > /root/1.txt
通过时间截取:
mysqlbinlog --start-datetime ‘2020-08-29 23:40:17’ --stop-datetime ‘2020-08-29 23:44:08’ mysql-bin.000001
还原:
mysqlbinlog --start-datetime ‘2020-08-29 23:40:17’ --stop-datetime ‘2020-08-29 23:44:08’ mysql-bin.000001 | mysql -uroot -p

mysql数据库管理系统就给大家介绍到这里了，本篇章笔者整理了关于mysql的一些基础操作，下一篇笔者将会整理基于mysql的一些简单的集群。协助大家更加了解mysql这款存储神器。（大家浏览不易，感谢三连）