中国建设网官方网站下载e路最新版,国家住房和城乡建设部中国建造师网站,上海公司注册多久可以拍牌,温州装修公司1、MySQL的复制原理以及流程(1)、复制基本原理流程1. 主#xff1a;binlog线程——记录下所有改变了数据库数据的语句#xff0c;放进master上的binlog中#xff1b;2. 从#xff1a;io线程——在使用start slave 之后#xff0c;负责从master上拉取 binlog 内容#xff…1、MySQL的复制原理以及流程(1)、复制基本原理流程1. 主binlog线程——记录下所有改变了数据库数据的语句放进master上的binlog中2. 从io线程——在使用start slave 之后负责从master上拉取 binlog 内容放进 自己的relay log中3. 从sql执行线程——执行relay log中的语句(2)、MySQL复制的线程有几个及之间的关联MySQL 的复制是基于如下 3 个线程的交互( 多线程复制里面应该是 4 类线程)1. Master 上面的 binlog dump 线程该线程负责将 master 的 binlog event 传到slave2. Slave 上面的 IO 线程该线程负责接收 Master 传过来的 binlog并写入 relay log3. Slave 上面的 SQL 线程该线程负责读取 relay log 并执行4. 如果是多线程复制无论是 5.6 库级别的假多线程还是 MariaDB 或者 5.7 的真正的多线程复制 SQL 线程只做 coordinator只负责把 relay log 中的 binlog读出来然后交给 worker 线程 woker 线程负责具体 binlog event 的执行(3)、MySQL如何保证复制过程中数据一致性及减少数据同步延时一致性主要有以下几个方面1.在 MySQL5.5 以及之前 slave 的 SQL 线程执行的 relay log 的位置只能保存在文件( http://relay-log.info)里面并且该文件默认每执行 10000 次事务做一次同步到磁盘 这意味着 slave 意外 crash 重启时 SQL 线程执行到的位置和数据库的数据是不一致的将导致复制报错如果不重搭复制则有可能会导致数据不一致。MySQL 5.6 引入参数 relay_log_info_repository将该参数设置为 TABLE 时 MySQL 将 SQL 线程执行到的位置存到mysql.slave_relay_log_info 表这样更新该表的位置和 SQL 线程执行的用户事务绑定成一个事务这样 slave 意外宕机后 slave 通过 innodb 的崩溃恢复可以把 SQL 线程执行到的位置和用户事务恢复到一致性的状态。2. MySQL 5.6 引入 GTID 复制每个 GTID 对应的事务在每个实例上面最多执行一次 这极大地提高了复制的数据一致性3. MySQL 5.5 引入半同步复制 用户安装半同步复制插件并且开启参数后设置超时时间可保证在超时时间内如果 binlog 不传到 slave 上面那么用户提交事务时不会返回直到超时后切成异步复制但是如果切成异步之前用户线程提交时在 master 上面等待的时候事务已经提交该事务对 master上面的其他 session 是可见的如果这时 master 宕机那么到 slave 上面该事务又不可见了该问题直到 5.7 才解决4. MySQL 5.7 引入无损半同步复制引入参 rpl_semi_sync_master_wait_point该参数默认为 after_sync指的是在切成半同步之前事务不提交而是接收到 slave 的 ACK 确认之后才提交该事务从此复制真正可以做到无损的了。5.可以再说一下 5.7 的无损复制情况下 master 意外宕机重启后发现有 binlog没传到 slave 上面这部分 binlog 怎么办分 2 种情况讨论 1 宕机时已经切成异步了 2 是宕机时还没切成异步这个怎么判断宕机时有没有切成异步呢分别怎么处理延时性5.5 是单线程复制 5.6 是多库复制(对于单库或者单表的并发操作是没用的) 5.7 是真正意义的多线程复制它的原理是基于 group commit 只要master 上面的事务是 group commit 的那 slave 上面也可以通过多个 worker线程去并发执行。和 MairaDB10.0.0.5 引入多线程复制的原理基本一样。(4)、工作遇到的复制 bug 的解决方法5.6 的多库复制有时候自己会停止我们写了一个脚本重新 start slave;待补充…2、MySQL中myisam与innodb的区别至少5点(1)、问5点不同1.InnoDB支持事物而MyISAM不支持事物2.InnoDB支持行级锁而MyISAM支持表级锁3.InnoDB支持MVCC, 而MyISAM不支持4.InnoDB支持外键而MyISAM不支持5.InnoDB不支持全文索引而MyISAM支持。6.InnoDB不能通过直接拷贝表文件的方法拷贝表到另外一台机器 myisam 支持7.InnoDB表支持多种行格式 myisam 不支持8.InnoDB是索引组织表 myisam 是堆表(2)、innodb引擎的4大特性1.插入缓冲(insert buffer)2.二次写(double write)3.自适应哈希索引(ahi)4.预读(read ahead)(3)、各种不同 mysql 版本的Innodb的改进MySQL5.6 下 Innodb 引擎的主要改进(1) online DDL(2) memcached NoSQL 接口(3) transportable tablespace( alter table discard/import tablespace)(4) MySQL 正常关闭时可以 dump 出 buffer pool 的( space page_no)重启时 reload加快预热速度(5)索引和表的统计信息持久化到 mysql.innodb_table_stats 和mysql.innodb_index_stats可提供稳定的执行计划(6)Compressed row format 支持压缩表MySQL 5.7 innodb 引擎主要改进(1) 修改 varchar 字段长度有时可以使用 online DDL(2) Buffer pool 支持在线改变大小(3) Buffer pool 支持导出部分比例(4) 支持新建 innodb tablespace并可以在其中创建多张表(5) 磁盘临时表采用 innodb 存储并且存储在 innodb temp tablespace 里面以前是 myisam 存储(6) 透明表空间压缩功能(4)、2者select count(*)哪个更快为什么myisam更快因为myisam内部维护了一个计数器可以直接调取。(5)、2 者的索引的实现方式都是 B树索引 Innodb 是索引组织表 myisam 是堆表 索引组织表和堆表的区别要熟悉3、MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义(1)、varchar与char的区别在单字节字符集下 char(N) 在内部存储的时候总是定长 而且没有变长字段长度列表中。在多字节字符集下面 char(N)如果存储的字节数超过 N那么 char(N)将和 varchar(N)没有区别。在多字节字符集下面如果存储的字节数少于 N那么存储 N 个字节后面补空格补到 N 字节长度。都存储变长的数据和变长字段长度列表。varchar(N)无论是什么字节字符集都是变长的即都存储变长数据和变长字段长度列表。(2)、varchar(50)中50的涵义最多存放50个字符varchar(50)和(200)存储hello所占空间一样但后者在排序时会消耗更多内存因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中 50 代表字节数现在代表字符数。(3)、int(20)中20的涵义是指显示字符的长度不影响内部存储只是影响带 zerofill 定义的 int 时前面补多少个 0易于报表展示(4)、mysql为什么这么设计对大多数应用没有意义只是规定一些工具用来显示字符的个数int(1)和int(20)存储和计算均一样4、innodb的事务与日志的实现方式(1)、有多少种日志redo和undo(2)、日志的存放形式redo在页修改的时候先写到 redo log buffer 里面 然后写到 redo log 的文件系统缓存里面(fwrite)然后再同步到磁盘文件( fsync)。Undo在 MySQL5.5 之前 undo 只能存放在 ibdata*文件里面 5.6 之后可以通过设置 innodb_undo_tablespaces 参数把 undo log 存放在 ibdata*之外。(3)、事务是如何通过日志来实现的说得越深入越好基本流程如下因为事务在修改页时要先记 undo在记 undo 之前要记 undo 的 redo 然后修改数据页再记数据页修改的 redo。Redo(里面包括 undo 的修改) 一定要比数据页先持久化到磁盘。当事务需要回滚时因为有 undo可以把数据页回滚到前镜像的状态崩溃恢复时如果 redo log 中事务没有对应的 commit 记录那么需要用 undo把该事务的修改回滚到事务开始之前。如果有 commit 记录就用 redo 前滚到该事务完成时并提交掉。5、MySQL binlog的几种日志录入格式以及区别(1)、 各种日志格式的涵义1.Statement每一条会修改数据的sql都会记录在binlog中。优点不需要记录每一行的变化减少了binlog日志量节约了IO提高性能。(相比row能节约多少性能 与日志量这个取决于应用的SQL情况正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量但是考虑到如果带条 件的update操作以及整表删除alter表等操作ROW格式会产生大量日志因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况其所 产生的日志量会增加多少以及带来的IO性能问题。)缺点由于记录的只是执行语句为了这些语句能在slave上正确运行因此还必须记录每条语句在执行的时候的 一些相关信息以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能slave可与master上要保持一致会有很多相关问题(如sleep()函数 last_insert_id()以及user-defined functions(udf)会出现问题).使用以下函数的语句也无法被复制* LOAD_FILE()* UUID()* USER()* FOUND_ROWS()* SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁2.Row:不记录sql语句上下文相关信息仅保存哪条记录被修改。优点binlog中可以不记录执行的sql语句的上下文相关的信息仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下 每一行数据修改的细节。而且不会出现某些特定情况下的存储过程或function以及trigger的调用和触发无法被正确复制的问题缺点:所有的执行的语句当记录到日志中的时候都将以每行记录的修改来记录这样可能会产生大量的日志内容,比 如一条update语句修改多条记录则binlog中每一条修改都会有记录这样造成binlog日志量会很大特别是当执行alter table之类的语句的时候由于表结构修改每条记录都发生改变那么该表每一条记录都会记录到日志中。3.Mixedlevel: 是以上两种level的混合使用一般的语句修改使用statment格式保存binlog如一些函数statement无法完成主从复制的操作则 采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式也就是在Statement和Row之间选择 一种.新版本的MySQL中队row level模式也被做了优化并不是所有的修改都会以row level来记录像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句还是会记录所有行的变更。(2)、适用场景在一条 SQL 操作了多行数据时 statement 更节省空间 row 更占用空间。但是 row模式更可靠。(3)、结合第一个问题每一种日志格式在复制中的优劣Statement 可能占用空间会相对小一些传送到 slave 的时间可能也短但是没有 row模式的可靠。Row 模式在操作多行数据时更占用空间 但是可靠。6、下MySQL数据库cpu飙升到500%的话他怎么处理当 cpu 飙升到 500%时先用操作系统命令 top 命令观察是不是 mysqld 占用导致的如果不是找出占用高的进程并进行相关处理。如果是 mysqld 造成的 show processlist看看里面跑的 session 情况是不是有消耗资源的 sql 在运行。找出消耗高的 sql看看执行计划是否准确 index 是否缺失或者实在是数据量太大造成。一般来说肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)等进行相应的调整(比如说加索引、改 sql、改内存参数)之后再重新跑这些 SQL。也有可能是每个 sql 消耗资源并不多但是突然之间有大量的 session 连进来导致 cpu 飙升这种情况就需要跟应用一起来分析为何连接数会激增再做出相应的调整比如说限制连接数等。7、sql优化(1)、explain出来的各种item的意义id:每个被独立执行的操作的标志表示对象被操作的顺序。一般来说 id 值大先被执行如果 id 值相同则顺序从上到下。select_type查询中每个 select 子句的类型。table:名字被操作的对象名称通常的表名(或者别名)但是也有其他格式。partitions:匹配的分区信息。type:join 类型。possible_keys列出可能会用到的索引。key:实际用到的索引。key_len:用到的索引键的平均长度单位为字节。ref:表示本行被操作的对象的参照对象可能是一个常量用 const 表示也可能是其他表的key 指向的对象比如说驱动表的连接列。rows:估计每次需要扫描的行数。filtered:rows*filtered/100 表示该步骤最后得到的行数(估计值)。extra:重要的补充信息。(2)、profile的意义以及使用场景Profile 用来分析 sql 性能的消耗分布情况。当用 explain 无法解决慢 SQL 的时候需要用profile 来对 sql 进行更细致的分析找出 sql 所花的时间大部分消耗在哪个部分确认 sql的性能瓶颈。(3)、explain 中的索引问题Explain 结果中一般来说要看到尽量用 index(type 为 const、 ref 等 key 列有值)避免使用全表扫描(type 显式为 ALL)。比如说有 where 条件且选择性不错的列需要建立索引。被驱动表的连接列也需要建立索引。被驱动表的连接列也可能会跟 where 条件列一起建立联合索引。当有排序或者 group by 的需求时也可以考虑建立索引来达到直接排序和汇总的需求。8、备份计划mysqldump以及xtranbackup的实现原理(1)、备份计划视库的大小来定一般来说 100G 内的库可以考虑使用 mysqldump 来做因为 mysqldump更加轻巧灵活备份时间选在业务低峰期可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小压缩之后更小)。100G 以上的库可以考虑用 xtranbackup 来做备份速度明显要比 mysqldump 要快。一般是选择一周一个全备其余每天进行增量备份备份时间为业务低峰期。(2)、备份恢复时间物理备份恢复快逻辑备份恢复慢这里跟机器尤其是硬盘的速率有关系以下列举几个仅供参考20G的2分钟(mysqldump)80G的30分钟(mysqldump)111G的30分钟(mysqldump)288G的3小时(xtra)3T的4小时(xtra)逻辑导入时间一般是备份时间的5倍以上(3)、备份恢复失败如何处理首先在恢复之前就应该做足准备工作避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查等。如果万一报错再根据报错的提示来进行相应的调整。(4)、mysqldump和xtrabackup实现原理mysqldumpmysqldump 属于逻辑备份。加入--single-transaction 选项可以进行一致性备份。后台进程会先设置 session 的事务隔离级别为 RR(SET SESSION TRANSACTION ISOLATION LEVELREPEATABLE READ)之后显式开启一个事务(START TRANSACTION /*!40100 WITH CONSISTENTSNAPSHOT */)这样就保证了该事务里读到的数据都是事务事务时候的快照。之后再把表的数据读取出来。如果加上--master-data1 的话在刚开始的时候还会加一个数据库的读锁(FLUSH TABLES WITH READ LOCK),等开启事务后再记录下数据库此时 binlog 的位置(showmaster status)马上解锁再读取表的数据。等所有的数据都已经导完就可以结束事务Xtrabackup:xtrabackup 属于物理备份直接拷贝表空间文件同时不断扫描产生的 redo 日志并保存下来。最后完成 innodb 的备份后会做一个 flush engine logs 的操作(老版本在有 bug在5.6 上不做此操作会丢数据)确保所有的 redo log 都已经落盘(涉及到事务的两阶段提交概念因为 xtrabackup 并不拷贝 binlog所以必须保证所有的 redo log 都落盘否则可能会丢最后一组提交事务的数据)。这个时间点就是 innodb 完成备份的时间点数据文件虽然不是一致性的但是有这段时间的 redo 就可以让数据文件达到一致性(恢复的时候做的事情)。然后还需要 flush tables with read lock把 myisam 等其他引擎的表给备份出来备份完后解锁。这样就做到了完美的热备。9、mysqldump中备份出来的sql如果我想sql文件中一行只有一个insert....value()的话怎么办如果备份需要带上master的复制点信息怎么办--skip-extended-insert[roothelei-zhuanshu ~]# mysqldump -uroot -p helei --skip-extended-insertEnter password:KEY idx_c1 (c1),KEY idx_c2 (c2)) ENGINEInnoDB AUTO_INCREMENT51 DEFAULT CHARSETlatin1;/*!40101 SET character_set_client saved_cs_client */;---- Dumping data for table helei--LOCK TABLES helei WRITE;/*!40000 ALTER TABLE helei DISABLE KEYS */;INSERT INTO helei VALUES (1,32,37,38,2016-10-18 06:19:24,susususususususususususu);INSERT INTO helei VALUES (2,37,46,21,2016-10-18 06:19:24,susususususu);INSERT INTO helei VALUES (3,21,5,14,2016-10-18 06:19:24,susu);10、500台db在最快时间之内重启可以使用批量 ssh 工具 pssh 来对需要重启的机器执行重启命令。也可以使用 salt(前提是客户端有安装 salt)或者 ansible( ansible 只需要 ssh 免登通了就行)等多线程工具同时操作多台服务器11、innodb的读写参数优化(1)、读取参数global buffer 以及 local bufferGlobal bufferInnodb_buffer_pool_sizeinnodb_log_buffer_sizeinnodb_additional_mem_pool_sizelocal buffer(下面的都是 server 层的 session 变量不是 innodb 的)Read_buffer_sizeJoin_buffer_sizeSort_buffer_sizeKey_buffer_sizeBinlog_cache_size(2)、写入参数innodb_flush_log_at_trx_commitinnodb_buffer_pool_sizeinsert_buffer_sizeinnodb_double_writeinnodb_write_io_threadinnodb_flush_method(3)、与IO相关的参数innodb_write_io_threads 8innodb_read_io_threads 8innodb_thread_concurrency 0Sync_binlogInnodb_flush_log_at_trx_commitInnodb_lru_scan_depthInnodb_io_capacityInnodb_io_capacity_maxinnodb_log_buffer_sizeinnodb_max_dirty_pages_pct(4)、缓存参数以及缓存的适用场景query cache/query_cache_type并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的table发生了变更第一个读操作多的话看看比例简单来说如果是用户清单表或者说是数据比例比较固定比如说商品列表是可以打开的前提是这些库比较集中数据库中的实务比较小。第二个我们“行骗”的时候比如说我们竞标的时候压测把query cache打开还是能收到qps激增的效果当然前提示前端的连接池什么的都配置一样。大部分情况下如果写入的居多访问量并不多那么就不要打开例如社交网站的10%的人产生内容其余的90%都在消费打开还是效果很好的但是你如果是qq消息或者聊天那就很要命。第三个小网站或者没有高并发的无所谓高并发下会看到 很多 qcache 锁 等待所以一般高并发下不建议打开query cache12、你是如何监控你们的数据库的你们的慢日志都是怎么查询的监控的工具有很多例如zabbixlepus我这里用的是lepus。13、你是否做过主从一致性校验如果有怎么做的如果没有你打算怎么做主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等14、表中有大字段X(例如text类型)且字段X不会经常更新以读为为主请问您是选择拆成子表还是继续放一起?写出您这样选择的理由答拆带来的问题连接消耗 存储拆分空间不拆可能带来的问题查询性能如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序IO,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗如果能容忍不拆分带来的查询性能损失的话:上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择15、MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的为什么是这样子的答InnoDB是基于索引来完成行锁例: select * from tab_with_index where id 1 for update;for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,如果 id 不是索引键那么InnoDB将完成表锁,,并发将无从谈起16、如何从mysqldump产生的全库备份中只恢复某一个库、某一张表17、在当前的工作中你碰到到的最大的 mysql db 问题以及如何解决的可以选择一个处理过的比较棘手的案例或者选择一个老师在课程上讲过的死锁的案例;没有及时 Purge insert 唯一索引造成的死锁具体案例可以参考学委笔记。18、请简洁地描述下 MySQL 中 InnoDB 支持的四种事务隔离级别名称以及逐级之间的区别(1)、事物的4种隔离级别读未提交(read uncommitted)读已提交(read committed)可重复读(repeatable read)串行(serializable)(2)、不同级别的现象Read Uncommitted:可以读取其他 session 未提交的脏数据。Read Committed:允许不可重复读取但不允许脏读取。提交后其他会话可以看到提交的数据。Repeatable Read: 禁止不可重复读取和脏读取、以及幻读(innodb 独有)。Serializable: 事务只能一个接着一个地执行但不能并发执行。事务隔离级别最高。不同的隔离级别有不同的现象并有不同的锁定/并发机制隔离级别越高数据库的并发性就越差。
