1.ERROR 2013 (HY000): Lost connection to MySQL server at reading initial comm解决查看myql服务是否正常。端口是否正常。防火墙是否关闭。检查myql配置文件。systemctl status firewalldlsof -i:3306systemctl status mysqld2.更新表时出现[1205] [40001]: Lock wait timeout exceeded; try restarting transaction原因一个事务等待另一个事务获得的锁时会发生禁用了自动提交第一个会话启动了一个事务接下来一旦update语句在事务中运行就会获得该行的独占锁但是没有提交事务处于打开状态导致其他事务一直等待。set autocommit0在同一事务内先后对同一条数据进行插入和更新操作并发访问冲突当多个事务同时访问相同的数据行并且其中一个事务正在修改数据行时其他事务可能会被阻塞等待修改完成。如果等待时间超过了MySQL的锁等待超时时间设置就会出现该异常。处理innodb_lock_wait_timeout 设置超时时间SET GLOBAL innodb_lock_wait_timeout 600; global对当前线程不生效或者分批处理数据可以减少锁等待时间降低异常发生概率。SELECT * FROM performance_schema.data_locks; select * from information_schema.innodb_trx; 查看是否有未结束的事务 kill {trx_mysql_thread_id} 这个id和show processlist的id对应 结束会话3.mysql服务器内存使用量很大free内存不足。原因linux管理内存的方式与windows不同写入缓存的重复利用的不会释放换出。正常现象。4.com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction原因死锁通常是两个mysql客户端都请求更新数据update和delete的时候客户端a锁定了某一行未提交事务此时客户端b也需要update或delete此时b就会进入等待直至deadlock。处理1.事务操作锁定的行数减少更精确的索引条件2.保证事务较短执行时间完成马上提交。处理分析在采用INNODB的MySQL中更新操作默认会加行级锁行级锁是基于索引的在分析死锁之前需要查询一下mysql的执行计划看看是否用到了索引用到了哪个索引对于没有用索引的操作会采用表级锁。如果操作用到了主键索引会先在主键索引上加锁然后在其他索引上加锁否则加锁顺序相反。在并发度高的应用中批量更新一定要带上记录的主键优先获取主键上的锁这样可以减少死锁的发生。--1.查询 show engine innodb status; show PROCESSLIST; show VARIABLES like autocommit; show variables like %innodb_lock_wait_timeout%; select version(); select transaction_isolation; --2.查询事务和锁 select * from information_schema.innodb_trx; 查看当前运行的事务 select * from performance_schema.data_locks; 当前出现的锁 select * from performance_schema.data_lock_waits; 锁等待的对应关系 --3.模拟死锁 会话1 start transaction; update stu set id111 where id1; 会话2 start transaction; delete from stu where id1;5.world-writable config file /etc/my.cnf is ignored,error!mysql is running but pid file could not be found分析my.cnf权限问题处理chmod 644 /etc/my.cnf6.从库slave_io_running变为no分析主键冲突或者主库删除或更新数据从库内找不到数据数据被修改导致处理方法1stop slave; set global sql_slave_skip_counter1;start slave;方法2set global read_onlytrue;7.ERROR! The server quit without updating PID file (/usr/local/mysql/data/oracletest.pid)原因可能存在mysql进程 ps -ef|grep mysqld处理pkill -9 mysqld杀掉8.mysql从库同步报错Slave_IO_Running: YesSlave_SQL_Running: NoMaster_Log_Filemysql-bin.000124代表从库读到的主库的binlog fileRead_Master_Log_Pos4353534代表从库读到的主库的binlog file 的日志偏移量Relay_Log_Filerelay-bin.000024代表从库执行到了哪一个 relay logRelay_Log_Pos1223434代表从库执行的relay log file 的日志偏移量Relay_Master_Log_Filemysql-bin.000355代表从库已经重放到了主库的哪个 binlog file。Exec_Master_Log_Pos2434343代表从库已经重放到了主库binlog file 的偏移量。Slave_IO_RunningYes说明 I/O 线程正在运行可以正常获取 binlog 并生成 relay log。Slave_SQL_RunningNo说明 SQL 线程已经停止运行不能正常解析 relay log也就不能执行主库上已经执行的命令。Master_Log_File 和 Read_Master_Log_Pos 这两个参数合起来表示的是读到的主库的最新位点。Relay_Master_Log_File 和 Exec_Master_Log_Pos这两个参数合起来表示的是从库执行的最新位点。如果红色框起来的两个参数 Master_Log_File 和 Relay_Master_Log_File 相等则说明从库读到的最新文件和主库上生成的文件相同这里前者是 mysql-bin.000956后者是 mysql-bin.000955说明两者不相同存在主从不同步。如果蓝色框起来的两个参数 Read_Master_Log_Pos 和 Exec_Master_Log_Pos 相等则说明从库读到的日志文件的位置和从库上执行日志文件的位置相同这里不相等说明主从不同步。当上面两组参数都相等时则说明主从同步正常且没有延迟。只要有任意一组不相等则说明主从不同步可能是从库停止同步了或者从库存在同步延迟。由于上面的SQL 线程已经停止了说明是从库同步出现问题了Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ANONYMOUS at master log binlog.001577, end_log_pos 228719429. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.select * from performance_schema.replication_applier_status_by_worker\G 查看具体的信息Worker 1 failed executing transaction ANONYMOUS at master log binlog.001577, end_log_pos 228719429;Could not execute Delete_rows event on table ekp.km_finance_main_reader; Query execution was interrupted,Error_code: 1317; Cant find record in km_finance_main_reader, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;the events master log binlog.001577, end_log_pos 228719429查看具体的sql语句根据end_log_pos id进行搜索找到删除的语句mysqlbinlog --no-defaults --base64-outputdecode-rows -v --stop-position228719429 binlog.001577 /root/xqqqq.sql解决方法一忽略错误继续同步。适用主从数据相差不大要求数据可以不完全统一由于master要删除一条记录而slave上找不到故报错这种情况主上都将其删除了那么从机可以直接跳过。可用命令stop slave;set global sql_slave_skip_counter1;start slave;解决方法二重新做主从完全同步。适用主从数据相差较大flush tables with read lock;mysqldump -uroot -p‘密码’ --all-databases mysql.back.sqlunlock tables;scp -r /root/mysql.bask.sql rootnode2:/tmp/stop slave;reset slave; 清除slave同步位置删除旧的同步日志适用新的日志重新开始source /tmp/mysql.back.sql ;change master to master_host 主库的IP’, master_user 设置主从时设定的主库的用户, master_port主库的端口, master_password主库设定的密码, master_log_file mysqld-bin.001989, master_log_pos24110520;start slave;9.从库报错Got fatal error 1236 from master when reading data from binary log: Could not find first log file name in binary log index file原因从主服务器读取二进制日志出现错误找不到binlog文件解决1如果没有binlog跳过报错binlog,查询当下主库的show master status;stop slave;change master to master_log_filebinlog.000018 , master_log_pos697;start slave;解决2如果有binlog重新维护binlog.index文件把物理存在的文件写入到里面然后重启mysqld数据库。解决3binlog丢失的很多由于mysql主库缺少从库所需要的binlog导致需要重新做主从10.mysql死锁日志76.101主库发现死锁这个是历史记录不是实时的并且死锁后会自动释放------------------------ LATEST DETECTED DEADLOCK ------------------------ 2024-11-22 13:00:00 140265203799808 *** (1) TRANSACTION: TRANSACTION 1725994635, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 23 lock struct(s), heap size 3488, 636 row lock(s) MySQL thread id 1059582, OS thread handle 140264426886912, query id 6594640519 10.44.61.45 ekp updating update sys_org_element set fd_parentorgidnull, fd_hierarchy_idconcat(concat(x, fd_id), x) where fd_is_available1 and (fd_org_type1 or fd_org_type1 and fd_org_type16 and (fd_parentid is null)) and (fd_parentorgid is not null or fd_hierarchy_idconcat(concat(x, fd_id), x) or fd_hierarchy_id is null) *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 37296 page no 1372 n bits 96 index PRIMARY of table ekp.sys_org_element trx id 1725994635 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) TRANSACTION: TRANSACTION 1725994610, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 32 lock struct(s), heap size 3488, 912 row lock(s), undo log entries 475 MySQL thread id 1059600, OS thread handle 140264673830656, query id 6594640631 10.44.61.43 ekp update insert into sys_profile_online_report(fd_id, fd_person_id, fd_online_time, fd_login_ip) values(193523dc4fb6d8059fd63c34f1bb94c4, 14da9dc631af5c15f2a197f444bbf2ae, 2024-11-22 13:00:00.123, 10.44.160.40) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 37296 page no 77 n bits 104 index PRIMARY of table ekp.sys_org_element trx id 1725994610 lock mode S locks rec but not gap Record lock, heap no 32 PHYSICAL RECORD: n_fields 33; compact format; info bits 128解决kill命令终止一个事务以解除死锁并恢复正常执行。kill thread id 要终止事务的线程id11.从库延迟很大sql线程状态applying batch of row changes (delete)可能原因主库执行了一个长事务delete执行很久没有结束主库手动终止了该sql。sql语句记录在binlog并在从库执行从库夯死。处理方法Show processlist; kill xxx;杀掉delete大事务对应的sql 处理过程解析相关relay log得到正在执行的事务信息 mysqlbinlog -vvv --base64-outputdecode-row relay.xxxxx --start-position | more 跳过该事务stop slave; set global sql_slave_skip_counter1; binlog主从 SET GTID_NEXTfee84d96-076b-11ed-8228-005056bf9190:217838841; gtid主从 start slave;12.在master上删除一条记录而slave上找不到。Slave_SQL_Running: No Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 3 failed executing transaction ANONYMOUS at master log binlog.001647, end_log_pos 566952515. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. 查询具体报错信息 select * from performance_schema.replication_applier_status_by_worker\G Worker 3 failed executing transaction ANONYMOUS at master log binlog.001647, end_log_pos 566952515; Could not execute Delete_rows event on table ekp.sys_notify_queue; Cant find record in sys_notify_queue, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the events master log binlog.001647, end_log_pos 566952515 LAST_ERROR_TIMESTAMP: 2024-12-09 09:13:43.428451 master库上解析binlog.001647文件并找到结束位置566952515。发现里面的delete语句拿出来看已经被删除了所以报错 mysqlbinlog --no-defaults --base64-outputdecode-rows -v --stop-position566952515 binlog.001647 /root/xqqqq.sql grep -i -50 566952515 /root/xqqqq.sql # at 566948840 #241209 9:13:42 server id 3309 end_log_pos 566952515 CRC32 0xc3a07815 Delete_rows: table id 407 flags: STMT_END_F ### DELETE FROM ekp.sys_notify_queue ### WHERE ### 1193a8fab26d844ee631a9c24a19b9d8b查询主从都没有这条记录select * from sys_notify_queue where fd_id193a8fab26d844ee631a9c24a19b9d8b;select * from sys_quartz_job where fd_id193a8fab332f7f023a993c6441a9008e;解决1从库跳过这条更新/删除stop slave;set global sql_slave_skip_counter1;start slave;解决2如果跳过的东西很多可在从库配置文件加配置直接跳过不影响业务的错误号slave-skip-errors 1032解决3找到解析的sql从库反向执行sql语句把从库缺少的sql的语句补全解决4终极方法重新做主从。