2023年7月21日发(作者：)

数据库mysql各种⼯作原理1、什么是数据库？什么是关系型数据库？什么是⾮关系型数据库？数据库就是⼀个存储数据的仓库。关系型数据库是依据关系模型（就是“⼀对⼀、⼀对多、多对多”等关系模型，关系模型就是指⼆维表格模型,因⽽⼀个关系型数据库就是由⼆维表及其之间的联系组成的⼀个数据组织。）来创建的数据库。例如：Oracle、DB2、PostgreSQL、Microsoft SQL Server、MicrosoftAccess、MySQL。⾮关系型数据库主要是基于“⾮关系模型”（列模型：Hbase；键值对模型：redis,MemcacheDB；⽂档类模型：mongoDB）的数据库（由于关系型太⼤，所以⼀般⽤“⾮关系型”来表⽰其他类型的数据库）关系型数据库与⾮关系型数据⽐较：查询速度：nosql数据库优于关系型数据库。nosql数据库将数据存储于缓存之中，关系型数据库将数据存储在硬盘中，⾃然查询速度远不及nosql数据库。安全：关系型数据库优于nosql数据库。因为关系型数据库存储在磁盘中，不会说突然断电数据就没有了。断电丢失数据，但其中redis可以同步数据到磁盘中，现在很多⾮关系型数据库都开始⽀持转存到磁盘中。存储数据的格式：关系型数据库优于nosql数据库。nosql的存储格式是key,value形式、⽂档形式、图⽚形式等等，所以可以存储基础类型以及对象或者是集合等各种格式，⽽数据库则只⽀持基础类型（varchar等）。2、MYSQL、ORACLE、SQL SERVER功能和应⽤场合Mysql 端⼝号3306，主要⽤于⼤型门户（中⼩型公司也在使⽤），例如搜狗、新浪等，它主要的优势就是开放源代码，因为开放源代码这个数据库是免费的，它现在是甲⾻⽂公司的产品。Oracle 端⼝号 1521，主要⽤于银⾏、铁路、飞机场等。该数据库功能强⼤，软件费⽤⾼。也是甲⾻⽂公司的产品。sql server 端⼝号1433，是微软公司的产品，主要应⽤于⼤中型企业，如联想、⽅正等。

3、数据库服务器、数据库、表与记录的关系所谓安装数据库服务器，只是在机器上装了⼀个数据库管理程序，这个管理程序可以管理多个数据库，⼀般开发⼈员会针对每⼀个应⽤创建⼀个数据库。为保存应⽤中实体的数据，⼀般会在数据库创建多个表，以保存程序中实体的数据。

4、数据的存储引擎。存储引擎（engines）：存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储⽅式。mysql⽀持的存储引擎包括：MyISAM、InnoDB、MEMORY、ARCHIVE、MRG_MYISAM等。其中，InnoDB为默认存储引擎。最常使⽤的两种存储引擎是MyISAM和InnoDB（5.5以上默认Engine是Innodb，其他版本默认是MyISAM）。

语⾔的分类1）数据定义语⾔（DDL） ⽤于创建、修改、和删除数据库内的数据结构，如：1：创建和删除数据库(CREATE DATABASE || DROP DATABASE)；2：创建、修改、重命名、删除表(CREATE TABLE || ALTER TABLE|| RENAME TABLE||DROP TABLE||TRUNCATE TABLE)；3：创建和删除索引 (CREATEINDEX || DROP INDEX) 2）数据查询语⾔（DQL） 从数据库中的⼀个或多个表中查询数据(SELECT) 3）数据操作语⾔（DML） 修改数据库中的数据，包括插⼊(INSERT)、更新(UPDATE)和删除(DELETE) 4）数据控制语⾔（DCL） ⽤于对数据库的访问，如：1：给⽤户授予访问权限（GRANT）;2：取消⽤户访问 权限（REMOKE）问：什么时候使⽤DROP?什么时候使⽤DELETE?什么时候⽤TRUNCATE？答：由上可看出，对于结构删除，如数据库删除、表删除、索引删除等当使⽤DROP;⽽对于数据的删除则是DELETE，DELETE语句执⾏删除的过程是每次从表中删除⼀⾏，并且同时将该⾏的删除操作作为事务记录在⽇志中保存以便进⾏进⾏回滚操作；TRUNCATE TABLE 则⼀次性地从表中删除所有的数据并不把单独的删除操作记录记⼊⽇志保存，删除⾏是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执⾏速度快。从表和索引所占空间来看，当表被TRUNCATE 后，这个表和索引所占⽤的空间会恢复到初始⼤⼩，DELETE操作不会减少表或索引所占⽤的空间。drop语句将表所占⽤的空间全释放掉。

6、SQL语句查询的基本语句：

7、mysql密码的修改与恢复a) ⽅法⼀：①　停⽌mysql服务②　启动跳过授权列表 mysqld --skip-grant-tables或者在mysql主配置⽂件中增加--skip-grant-tables再重启③　update user set password=password('新密码') where host='主地址' and user='⽤户名';④　flush privileges;//刷新权限

⑤　重启登陆mysql服务

b) ⽅法⼆：①　停⽌mysql服务②　启动跳过授权列表 mysqld --skip-grant-tables或者在mysql主配置⽂件中增加--skip-grant-tables再重启③　set password for ⽤户名@'主机地址' =password('新密码'); //注这种⽅法⽆需刷新权限④　重启并登陆mysql服务

8、数据库的备份与恢复a)备份的概念：为应付⽂件、数据丢失或损坏等可能出现的意外情况，将电⼦计算机存储设备中的数据复制到磁带等⼤容量存储设备中,从⽽在原⽂中独⽴出来单独贮存的程序或⽂件副本; 如果系统的硬件或存储媒体发⽣故障，“备份”⼯具可以帮助您保护数据免受意外的损失。b)⼀般的备份可分为：1、系统备份：指的是⽤户操作系统因磁盘损伤或损坏，计算机病毒或⼈为误删除等原因造成的系统⽂件丢失，从⽽造成计算机操作系统不能正常引导，因此使⽤系统备份，将操作系统事先贮存起来，⽤于故障后的后备⽀援。2、数据备份：指的是⽤户将数据包括⽂件，数据库，应⽤程序等贮存起来，⽤于数据恢复时使⽤。c)备份更专业地可分为：i.全量备份：完全备份就是指对某⼀个时间点上的所有数据或应⽤进⾏的⼀个完全拷贝ii.增量备份：增量备份是指在⼀次全备份或上⼀次增量备份后，以后每次的备份只需备份与前⼀次相⽐增加和者被修改的⽂件iii.差异备份：差异备份是指在⼀次全备份后到进⾏差异备份的这段时间内，对那些增加或者修改⽂件的备份d)对mysql数据库⽽⾔，就是数据备份，可分为全量备份与增量备份e)备份⼯具有：mysqldump、xtrabackup、mydumper 9、备份计划，mysqldump以及xtranbackup的实现原理

(1)、备份计划；

这⾥每个公司都不⼀样，您别说那种1⼩时1全备什么的就⾏

(2)、备份恢复时间；

这⾥跟机器，尤其是硬盘的速率有关系，以下列举⼏个仅供参考

20G的2分钟（mysqldump）

80G的30分钟(mysqldump)

111G的30分钟（mysqldump)

288G的3⼩时（xtra)

3T的4⼩时（xtra)

逻辑导⼊时间⼀般是备份时间的5倍以上(3) 、xtrabackup实现原理

Xtrabackup是由percona提供的mysql数据库备份⼯具, 有两个主要的⼯具：xtrabackup、innobackupex，备份过程快速、可靠，备份过程不会打断正在执⾏的事务，能够基于压缩等功能节约磁盘空间和流量，⾃动实现备份检验，还原速度快，备份可在线备份，但是恢复要关闭服务器，恢复后再启动.在InnoDB内部会维护⼀个redo⽇志⽂件，我们也可以叫做事务⽇志⽂件。事务⽇志会存储每⼀个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据⽂件和事务⽇志，并执⾏两个步骤：它应⽤（前滚）已经提交的事务⽇志到数据⽂件，并将修改过但没有提交的数据进⾏回滚操作。(4) mysqldump实现原理mysqldump的本质是通过select * from tab来获取表的数据的。mysqldump只适合放到业务低峰期做，如果备份的过程中数据操作很频繁，会造成Undo表空间越来越⼤，undo表空间默认是放到共享表空间中的，⽽ibdata的特性是⼀旦增⼤，就不会收缩。(5) mydumper实现原理mydumper是⼀个使⽤C语⾔编写的多线程导出导⼊⼯具,并且能够保证多个表之间的⼀致性

10、mysql⽇志类型错误⽇志：记录启动、运⾏或停⽌mysqld时出现的问题。参数:log-error。⼆进制⽇志： 该⽇志⽂件会以⼆进制形式记录数据库的各种操作，但是不记录查询语句。参数：log-bin。查询⽇志：该⽇志分为两类：通⽤查询⽇志（参数：log）和慢查询⽇志（参数：log-slow-queries和设置时间的参数：long_query_time）。前者记录建⽴的客户端连接和执⾏的语句。 后者记录所有执⾏时间超过long_query_time秒的所有查询或不使⽤索引的查询。

11、mysql主从复制优点健壮性:主服务器出现故障时,可以切到从服务器作为备份速度快:更新操作在主服务器端,查询操作在从服务器端,可以加快⽤户的响应时间备份:避免影响业务

12、mysql主从复制原理

从库⽣成两个线程，⼀个I/O线程，⼀个SQL线程； i/o线程去请求主库 的binlog，并将得到的binlog⽇志写到relay log（中继⽇志） ⽂件中；主库会⽣成⼀个 log dump 线程，⽤来给从库 i/o线程传binlog；SQL 线程，会读取relay log⽂件中的⽇志，并解析成具体操作，来实现主从的操作⼀致，⽽最终数据⼀致；

13、mysql⽀持的复制类型：(1)：基于语句的复制： 在主服务器上执⾏的SQL语句(写⼊bin log中)，在从服务器上执⾏同样的语句。MySQL默认采⽤基于语句的复制，效率⽐较⾼。binlog_format = 'STATEMENT'(2)：基于⾏的复制：把改变的内容(写⼊bin log中)复制过去，⽽不是把命令在从服务器上执⾏⼀遍. 从mysql5.0开始⽀持。binlog_format ='ROW'(3)：混合类型的复制: 默认采⽤基于语句的复制，⼀旦发现基于语句的⽆法精确的复制时，就会采⽤基于⾏的复制。binlog_format ='MIXED'

14、mysql读写分离原理Amoeba(变形⾍)项⽬，该开源框架于2008发布⼀款Amoeba for mysql软件，该软件致⼒于mysql的分布式数据库前端代理层，主要的作⽤是应⽤服务访问mysql服务器时充当SQL路由功能，并具有负载均衡、⾼可⽤性、SQL过滤、读写分离、可路由相关SQL的到⽬标数据库、可并发请求多台数据库全并结果的作⽤。通过Amoeba能够完成多数据源的⾼可⽤、负载均衡、数据切⽚的功能。原理如下：在数据库集群架构中，让主库负责处理事务性查询，⽽从库只负责处理select查询，让两者分⼯明确达到提⾼数据库整体读写性能。当然，主数据库另外⼀个功能就是负责将事务性查询导致的数据变更同步到从库中，也就是写操作。

读写分离优点：1）分摊服务器压⼒，提⾼机器的系统处理效率 读写分离适⽤于读多于写的场景，如果有⼀台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能并不⾼，⽽主从只负责各⾃的写和读，极⼤程度的缓解X锁和S锁争⽤； 假如我们有1主3从，不考虑上述1中提到的从库单⽅⾯设置，假设现在1分钟内有10条写⼊，150条读取。那么，1主3从相当于共计40条写⼊，⽽读取总数没变，因此平均下来每台服务器承担了10条写⼊和50条读取（主库不承担读取操作）。因此，虽然写⼊没变，但是读取⼤⼤分摊了，提⾼了系统性能。另外，当读取被分摊后，⼜间接提⾼了写⼊的性能。所以，总体性能提⾼了，说⽩了就是拿机器和带宽换性能；2）增加冗余数据库服务器宕机后可以调整另外⼀台从库以最快速度恢复服务，提⾼服务可⽤性。

15、amoeba进⾏数据切分根据数据的特点，将不同特性的数据纪录存放到不同的数据库服务器中就是数据切分，可以设置规则匹配数据特性以实现数据切分，其实，数据切分就是根据设置规则将数据分别存放到不同的数据库，以减轻数据库服务器的压⼒和减⼩单个数据库的⼤⼩，可通过设置amoeba的⽂件中的切⽚规则实现。原理如图： ⽔平分割:将关键字段按特定的算法存放到不同的服务器 垂直分割:将数据按业务的不同分别保存到不同的服务器

数据库扩展读写分离软件还有：mycat,mysql-proxy,maxscale