![](https://img-blog.csdnimg.cn/20201014180756757.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
数据库运维-MySQL
mysql数据库的各类备份恢复,性能优化,故障处理
戒掉贪嗔痴(薛双奇)
10年以上DBA从业经验,Oracle ACE,中国DBA联盟成员,青学会专家顾问,Oracle,MySQL,PostgreSQL DBA,大数据运维工程师。
熟悉20种数据库的运维及管理:横跨关系数据库,NoSQL数据库,搜索引擎,大数据等。
获得IT类认证十几张。
擅长数据库系统架构涉及,容量规划,灾备系统建设,故障诊断及性能优化。
展开
-
【MySQL】mysql5.6.20主从同步到8.0.33
虽然mysql5.6.20可以同步主从同步到8.0.33,但是如果在主库创建用户,则同步会失败。原因是5.6.20的版本的创建用户的语法与8.0.33的不相同。4.mysql8.0.33 作为从节点同步来自主节点的数据;2.mysql8.0.33 创建用户。3.mysql5.6 查看主节点状态。1.mysql5.6创建用户。7.目标端日志检查。原创 2024-03-15 11:35:44 · 447 阅读 · 0 评论 -
【MySQL】mysql5.7.36 二进制版本升级到mysql5.7.43
现有版本:mysql-5.7.36-linux-glibc2.12-x86_64。3. 修改软件目录和启动文件。原创 2024-03-08 11:42:25 · 723 阅读 · 0 评论 -
【MySQL】大表上筛选字段-时间戳字段上不要使用函数
遇到日期时间戳字段,尽量将函数放在时间戳或日期后面的常量上,而不是直接在时间字段上使用函数。同时在该字段上创建索引,提能够有效提升运行效率。原创 2024-02-28 13:33:46 · 424 阅读 · 0 评论 -
【MySQL】日期字段空值比例超过99.9%是否可以建索引
事实证明,对于日期时间类型的字段,即使99.9%的值是空的,且访问的记录是非部分,那么创建索引是一个比较好的优化方法。4692.5s=78.2分钟。原创 2024-02-28 13:24:26 · 381 阅读 · 0 评论 -
【MySQL】select子查询优化
针对select 查询 in 条件后是子查询的情况,如果主表筛选条件是索引或主键字段,则可以通过将IN子查询修改为JOIN操作。这样主表也可以使用索引,从而优化SQL;原创 2024-02-28 13:03:35 · 499 阅读 · 0 评论 -
【MySQL】update语句的子查询优化
对于UPDATE子查询优化,需要修改为 INNER JOIN 后修改。两个表都可以用索引。原创 2024-02-26 17:23:22 · 511 阅读 · 0 评论 -
【MySQL】119.MySQL8首次创建主从同步报错Last_IO_Errno: 13114
针对mysql8,搭建主从同步,需要将主库查询到的GTID信息,都设置,否则就会报错。原因是主库上有的GTID信息在从库都必须设置。1.首次同步,开启同步后报错。原创 2024-02-19 11:43:10 · 668 阅读 · 0 评论 -
【MySQL】118.MySQL慢日志过大处理
对慢日志重命名后,原来的文件句柄还被占用,依然会写入旧的文件中。所以需要设置slow_query_log='OFF',释放旧的文件句柄。使用slow_query_log='ON'开启新建的文件的使用。1.mysql慢日志文件过大清理。原创 2024-02-19 11:31:07 · 563 阅读 · 0 评论 -
【MySQL】117.锁的分类及锁概念总结
间隙锁不仅锁定了查询范围的值,还锁定了查询范围之外相邻的一个区域。2.MyISam只支持表级锁,而INNODB支持行级和表级锁。可重复读(RR):脏读,不可重复读,幻读都不会出现。在辅助索引里面,索引存储的是二级索引和主键的值。未提交读(RU):脏读,不可以重复读,幻读。串行化:脏读,不可重复读,幻读都不会出现。S遇X:两边都有I兼容,否则不兼容。X遇X:两边都有I兼容,否则不兼容。已提交读(RC):不可重复读和幻读。1.INNODB的锁机制及锁分类。S遇S:两边有I无I都兼容。3.关于锁的一些思考。原创 2024-02-06 14:52:21 · 357 阅读 · 0 评论 -
【MySQL】116.Native table ‘performance_schema‘.‘session_variables‘ has the wrong structure
1.无法查看mysql变量。2.修改参数文件。原创 2024-02-02 15:43:36 · 441 阅读 · 0 评论 -
【MySQL】115.临时表空间满如何处理
应该优化SQL,避免在OLTP中使用大的查询导致整个系统临时表空间巨大,甚至撑满整个系统。大的查询导致ibtmp1 的临时表空间文件持续增大,查询结束后,没有自动释放。2.修改临时表空间,限制大小。原创 2024-02-02 15:05:15 · 588 阅读 · 0 评论 -
【MySQL】114.Last_IO_Error: Source command COM_REGISTER_REPLICA failed
开启基于GTID的同步,需要首先设置从库的GTID_PURGED的值,并且该值和主库完全一致。2.mysql8的创建复制用户需要指定IP地址。--3.查看主库的GTID位置。5.启动基于GTID的同步。1.mysql8复制报错。原创 2024-02-01 15:03:00 · 550 阅读 · 0 评论 -
113.mysql编写shell脚本时登录连接串中出现“!“感叹号无法登录问题处理技巧
当密码中出现特殊符号,例如感叹号导致无法通过shell编写自动化脚本,可以将密码单独提取到一个变量中。这样可以处理感叹号问题。1.有感叹号无法赋值。原创 2024-01-30 15:03:12 · 460 阅读 · 0 评论 -
112.mysql in导致的索引失效优化案例
嵌套循环连接:Using join buffer (Block Nested Loop),在当前SQL中性能差,目前应该是索引范围扫描。今天9:00~16点,有2000多条慢SQL,大多数都是和表:b_vehicle_loss_component相关。WHERE 条件中使用了 IN 导致表没有使用到索引,执行时间:20s~100s的都有。现在执行时间:0.00s ,IN 改为JOIN 后使用到了索引。原来执行时间:100.474167s,IN 导致未使用到索引。--优化后执行时间0s;原创 2024-01-24 17:36:26 · 601 阅读 · 0 评论 -
111.mysql5.7Table ‘mysql.proc‘ doesn‘t exists 或者Table ‘mysql.event‘ doesn‘t exist
mysql.event 和mysql.proc 系统表,可以通过查看其他系统相同版本的表结构,然后创建。1.偶然发现某个mysql5.7的系统有两个系统表不存在。原创 2024-01-23 17:26:59 · 703 阅读 · 0 评论 -
110.三条慢SQL的优化及分析
虽然有时候SQL访问的表可以走索引,但是还要看走哪个索引最优。例如上面的SQL,走车牌号这个索引才是最优的,而走时间的索引,访问量较大,不是最优的。虽然少访问一次prptmain 表,但是所有表都使用了索引,且需要分组并排序的结果集本身很大。--相关的表只有8条数据,PLATENO 是车牌号,应该走这个字段上的索引。using where:使用索引的情况下,需要回表查询所需的数据。--但是 该字段上没有索引,所以需要在该字段上创建索引。ID=3的四个表都使用了索引,已经是最优的了。3.SQL2分析及优化。原创 2024-01-23 16:37:21 · 365 阅读 · 0 评论 -
109.MySQL大表优化为分区表-并使用定时任务实现按日自动分区功能
mysql的分区功能相对较弱,目前尚无法实现自动分区,不过我们可以通过创建事件的方式,实现自动增加分区的功能。(2)创建事件,定时删除30天前的分区,并创建新的分区。(6)检查定时任务执行后的表分区。1.mysql 大表优化。(5)定时任务开始前检查。--(3)验证分区功能。--创建定时任务事件。原创 2024-01-19 13:11:40 · 456 阅读 · 0 评论 -
108.mysql8.0.34-GTID模式下主库取消长事务&从库遇到长事务夯死
主库需要给表建立主键或者索引,或者如果表比较大时可以对其分区,防止主库执行大事务,并未结束,而SQL传输到从库,导致从库夯死的情况发送,如果有这类情况发送,需要人工处理该夯死问题。在这个GTID的基础上加1;2.跳过该DELETE大事务对应的GTID。1.有GTID模式下从库不同步修复。原创 2024-01-17 11:21:26 · 593 阅读 · 0 评论 -
108.Replica I/O for channel log event entry exceeded max_allowed_packet
log event entry exceeded max_allowed_packet, 这个报错的原因是指定的binlog的pos不对。原因是指定的POS 在BINLOG里面不存在,起初以为是max_allowed_packet过小,但是修改并没有用。BINLOG内容查看:合适的位置应该从:# at 481505175 这里开始。GTID_MODE修改为:ON_PERMISSIVE ,再次启动正常修复。关闭mysql8.0.34从库的同步,过了一会开启后,报错。3.从下一个POS重新同步。原创 2024-01-15 17:51:24 · 451 阅读 · 0 评论 -
107.SELECT语句带有日期的优化
在带有日期时间戳的字段上执行范围查询,最好给该时间字段建立索引。(3)查看执行计划可以知道,该SQL未使用索引,全表扫描。1.SELECT语句优化。(6)查看SQL执行次数。(5)SQL等价改写。原创 2024-01-06 23:46:06 · 404 阅读 · 0 评论 -
106.’binlog_error_action’ is set to ‘ABORT_SERVER
建议做好备份,业务切换到备库,更换存储设备,重新同步后,再切换回主库。磁盘出现问题前,一般都会有IO突然变慢,或磁盘写入故障等问题。dm-0 是 rhel-root 这个逻辑卷的。rhel-root 挂载在根目录 /,/opt目录在 根目录 / 下。所以DM-0有问题,就整个根目录会有问题。这两个过程基本跟刷磁盘有关联,一般出现问题的情况是硬盘空间出现坏道,或则 刷盘过程中出现外部原因导致服务器突然crash。’ 无法写入binlog,且会导致宕机。,所以偶发性出现mysql写入异常并重启。原创 2024-01-04 18:32:51 · 412 阅读 · 0 评论 -
105.mysql8.0.30之后REDO日志的管理有所不同-#innodb_redo 目录
由此可见redo日志文件的大小=innodb_redo_log_capacity/16=100M/16=6.25M;同时可以通过修改 innodb_redo_log_capacity 参数在线修改REDO LOG,1.mysql8.0.30之后REDO日志的管理有所不同-#innodb_redo 目录。同时依然兼容:8.0.30之前的参数。比之前需要重启数据库来说方便很多。原创 2024-01-04 10:02:39 · 571 阅读 · 0 评论 -
104.主从复制如何设置不复制某些表或者数据库
通过从库复制的参数,可以控制要复制哪些库,忽略哪些库,复制哪些表,忽略哪些表。可以非常灵活的配置想复制的库和表,及不想复制的库和表。逻辑从库类似Oracle OGG,只复制自己想要的内容。1.主从复制如何设置不复制某些表或者数据库。原创 2024-01-04 09:13:15 · 464 阅读 · 0 评论 -
102.MySQL远程登录时IP地址发生变化并报错
由此可见,mysql.user表中存储的是可以连接到本机的主机IP,要连接的IP地址在mysql.user表中,则允许该IP地址远程连接过来,如果没有则不允许连接。1.远程连接时报本地IP地址连接拒绝。原创 2023-12-25 09:45:21 · 381 阅读 · 0 评论 -
101.半同步之-AFTER_SYNC 和 AFTER_COMMIT的区别
(3)after_sync:从库先返回接收到relaylog的确认信息,主库才提交。等主库提交后,所有客户端看到的值一样。2.半同步参数rpl_semi_sync_master_wait_point 的两个值的区别。(2)after_commit:主库先提交到存储引擎,然后等待从库返回写入binlog的消息。(4)after_commit:主库的其他客户端比当前提交的客户端更早的看到提交的数据。(1)after_sync:从库返回确认后主库才提交存储引擎。建议:使用默认的after_sync。原创 2023-12-22 16:41:08 · 565 阅读 · 0 评论 -
100.神奇的report_host
由查询结果可以看到,主从查询,一目了然可以看到从库的SERVER_ID,IP地址,端口等信息。加入从库/etc/my.cnf 文件并重启后,主从同步时可以在主库查询有几个从节点,需要注意的是,该参数只在从库添加即可。由于该参数是只读参数,需要重复数据库才能生效。并且主从同步的时候可以看到记录,主从不同步时不显示从库记录。分别IP是多少,查看非常方便。原创 2023-12-22 16:03:15 · 509 阅读 · 0 评论 -
99.mysql导出报:mysqldump: Error 2020: Got packet bigger than ‘max_allowed_packet‘
数据库安装时,可以评估数据库的大小等因素,设置一个合理的值max_allowed_packet=128M/256M/512M/1024M。1.mysqldump 导出报错。原创 2023-12-21 16:16:48 · 484 阅读 · 0 评论 -
98.mysql DELETE语句优化
对于delete的优化,特别是针对复杂关联的删除,应该尽可能的使用索引。如果执行计划中没有用到索引,则会很慢,如上述SQL 1.8小时尚未运行结束,而使用索引,则只需要0.68s.还有一种方法就是SQL改写,将原来的DELETE FROM XXX UUID IN;underwriting.prpcautoprice有几百万数据,但是删除时没有用到索引。由此可见,改写后的SQL可以DELETE的表中用到了索引。6467/60/60=1.8小时,还没有执行结束。原创 2023-12-21 15:35:27 · 335 阅读 · 0 评论 -
97.xtrabackup: Error: xtrabackup_copy_logfile() failed
1.mysql使用xtrabackup备份失败。原创 2023-12-20 14:37:56 · 571 阅读 · 0 评论 -
96.mysql修改重做日志缓冲区和重做日志大小
mysql修改 innodb_log_file_size。2.备份原来的 innodb_log_file。(2)备份原来的日志,修改参数。3.修改重做日志文件大小。5.检查重做日志文件大小。原创 2023-12-20 14:36:02 · 396 阅读 · 0 评论 -
95.mysql5.7/MySQL8.0root密码忘记重置
mysql8.0建议使用alter user方式修改root密码。mysql5.7建议使用update方式修改root密码。1.mysql5.7密码重置方法。2.mysql8.0密码重置。原创 2023-12-20 13:44:14 · 466 阅读 · 0 评论 -
94.Unable to lock ./ibdata1 error: 11
在数据库或者的时候启动数据库:initialization has started.猜想应该是属于误操作,数据库启动的,然后又启动数据库,导致这个错误。2.没整明白,直接重启,重启后数据库正常。原创 2023-12-20 13:18:31 · 977 阅读 · 1 评论 -
93.mysql 8.0 切换步骤
mysql8.0,和mysql5.7 在具有GTID的情况下,却换上有略微的差异。1.mysql8.0主从同步。原创 2023-12-02 12:20:58 · 423 阅读 · 0 评论 -
92.MySQL8.0在主从切换时需要注意的事项
等到主从的GTID都同步了,后续的切换过程,则使用;去自动识别GTID.2.如果是新搭建的主从想用位置的方式同步。--关闭AUTO POSITION功能。1.密码认证方式的改变。原创 2023-11-29 21:40:38 · 536 阅读 · 0 评论 -
91.MySQL5.7升级到MySQL8.0.33升级方案
旧的mysql5.7.22的目录mysql 替换为:mysql7 ,新目录mysql8.0.33的mysql8修改为mysql。三、mysql5.7.22升级到MySQL8.0.33升级步骤。四、其他低版本MySQL升级方案说明。(5)INNODB存储引擎方面的变化。(13)INNODB存储引擎的改进。(5)mysql8.0.33升级。(3)停止mysql5.7.22。(15)测试发现的过期参数。一、升级建议。(6)SQL方面的变化。(4)服务器端的变化。(11)新的数据类型。原创 2023-11-29 21:32:20 · 634 阅读 · 1 评论 -
90.MySQL5.6数据库安装
mysql5.6只有rpm的安装包,没有其他形式的安装包。且安装方法与后面的包有所有不同。1.MySQL 5.6.20 数据库安装。2.查看MYSQL密码。原创 2023-11-29 21:15:10 · 379 阅读 · 0 评论 -
89.如何保证MySQL主库的数据都已经写入备库
和从库的relaylog 的最后一个事务的:事务ID,GTID,END_LOG_POS相同。1.首先当主库停止写入时,主库binlog pos的位置不再改变。2.通过binlog和relaylog的对比,确认主备是否同步。事务ID会随着事务的执行递增,GTID也会随着事务递增。事务ID,GTID,END_LOG_POS。原创 2023-11-03 22:47:39 · 66 阅读 · 0 评论 -
87.MySQL服务器内存VIRT,RES,MEM%高问题排查
降低VIRT,RES,MEM%等的内存,持续观察,看是否每次都会导致SWAP分区使用较多,如果是则增加物理内存。Innodb_buffer_pool_size代表仅仅只是数据页的大小,对于MySQL服务器来说,还有其他的缓存区域,都占用一定的内存空间。如果内存不够,则考虑增加内存。起初我们测试了,thread_cache_size的变化对于内存的影响,测试结果显示,thread_cache_size对MEM%内存的使用几乎没有影响。=20G+4.8G+15G=39.8G~近似等于VIRT:37G.原创 2023-11-01 12:48:31 · 1046 阅读 · 0 评论 -
86.mysql8.0.16升级到8.0.33
mysql8.0.16之后升级方式有变化,新的升级方式方便很多。1.mysql8.0.16升级到8.0.33。2.mysql升级参数upgrade的参数解释。原创 2023-10-17 17:12:18 · 136 阅读 · 0 评论 -
86.MYSQL执行计划详细解读
MYSQL 的执行计划对于MySQL的性能优化非常重要,熟练解读执行计划的内容能够帮助我们快速优化MYSQL 的SQL性能。MySQL执行通过使用explain select ....;或者explain extended select ....;在MYSQL5.7之后可以获得DML语句的执行计划。5.Explain 查看执行计划的局限性。2.mysql执行计划中各个字段的含义。4.其他执行计划列的解释。1.MySQL执行计划。原创 2023-10-17 14:46:39 · 134 阅读 · 0 评论