Mysql 常见错误

发表于 更新于 上层目录 阅读次数：

环境信息说明

  • mysql 5.7
  • Centos 7

错误集锦

MySQL 主从不同步，尝试跳过执行错误的事务

执行以下命令，可跳过对应的 slave 事务

slave
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;

可以通过分析主库日志中的事务，来确定 SQL_SLAVE_SKIP_COUNTER 的合适值。
具体步骤如下：
在 slave 库中执行 show slave status\G ，确认以下两个参数:

  • Relay_Master_Log_File
  • Exec_Master_Log_Pos

或者以下2个参数：

  • Master_Log_File
  • Read_Master_Log_Pos

    通过以上 2 个值，可以知道当前 slave 执行到了 master 对应的哪个 binlog 文件 ( Relay_Master_Log_File) 以及文件中的对应位置 (Exec_Master_Log_Pos )

根据上述两个参数的值，在 master 中可以查看当前阻碍从库复制的事务以及之后的事务。可以执行以下命令查看

master
SHOW BINLOG EVENTS in 'mysql-bin.001015' from 366269088;

这个可以查看到对应日志文件 mysql-bin.001015 中，事务 ID 为 366269088 之后的所有事务，

以下命令可以限制查询条数

master
SHOW BINLOG EVENTS in 'mysql-bin.001015' from 366269088 limit 10;

主从具有相同的 server-uuid

报错信息：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work

问题原因： 主从数据目录(默认/var/lib/mysql/)都有一个 auto.cnf 文件，里面记录的 server-uuid 是相同的，在云主机 clone 的场景可能会出现此问题。

解决方法： 修改 auto.cnf 文件中 uuid 的值，重启 Mysql 服务即可

查询 uuid 的方式，登录 mysql，执行以下命令查看

mysql
select uuid();

主从具有相同的 server-id

报错信息：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;

问题原因： 主从配置文件中配置了同样的 server-id

解决方法： 修改 my.cnf， 主从配置不同的server-id

主从同步错误 1032

报错信息：

Could not execute Update_rows event on table ceshi.bbs_online; Can’t find record in ‘bbs_online’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.001423, end_log_pos 866646715

问题原因： 主从不同步，从数据库中记录被删除或者未同步到从库，主数据库中存在记录，主DB更新后，从无法同步

解决方法1： 重新备份 master DB，恢复到 slave，重新 恢复主从

解决方法2： 找出丢失的数据在 slave 上插入。可以通过 分析出错的 binlog 找出丢失的记录

主从同步错误 1396

报错信息：

ERROR 1396 (HY000): Operation CREATE USER failed for ‘XXXX‘@’XXXX’

问题原因： slave 同步时，从数据库中已存在用户

解决方法： slave 上删除已存在的用户

主从同步错误 1062

报错信息：

Last_SQL_Error: Could not execute Write_rows event on table *;
Duplicate entry ‘2’ for key ‘PRIMARY’,
Error_code: 1062;
handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql-bin.000006, end_log_pos 924

问题原因： slave 上已有该记录，master 上又插入，可能存在主从数据不一致

解决方法： 删除 slave 上重复的主键记录

主从同步错误 1042

报错信息：

error code 1042 (ER_BAD_HOST_ERROR): Can’t get hostname for your address

问题原因： 解析主机名失败

解决方法： mysql 配置（my.cnf）中设置 skip-name-resolve

使用以下命令解析 mysql 错误码

perror 1042

导出导入数据错误 1290

报错场景： 使用 select/insert 导出/导入 时报错

报错信息：

ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

问题原因： secure_file_priv 参数用于限制 LOAD DATA, SELECT …OUTFILE, LOAD_FILE() 传到哪个指定目录。

  • secure_file_priv 为 NULL 时，表示限制 mysqld 不允许导入或导出。
  • secure_file_priv 为 /tmp 时，表示限制 mysqld 只能在 /tmp 目录中执行导入导出，其他目录不能执行。
  • secure_file_priv 没有值时，表示不限制 mysqld 在任意目录的导入导出。

可以通过以下命令，查看当前的配置 

show global variables like "%secure_file_priv%";

解决方法： 因为 secure_file_priv 参数是只读参数，不能使用 set global 命令修改。

需要修改配置文件，添加以下内容

my.cnf
secure_file_priv = ''

修改配置后，重启 mysql 服务生效

xtrbackup 备份时被阻塞，一直显示 log scanned up to

报错场景: xtrbackup 备份时间过长，一直输出 log scanned up to

可能原因： 备份在等待锁

解决方法： 如果对业务无影响，可以重启 mysql 服务后重新备份，或者根据 ID kill 掉对应的线程

mysql
show processlist;
kill ID;

Cannot add or update a child row: a foreign key constraint fails

修改表字段，添加外键时报错，sql 语句及报错信息如下

$ ALTER TABLE `my_table1` ADD COLUMN `usage_id` BIGINT DEFAULT 1 NOT NULL , ADD CONSTRAINT `usage_id_43803979_fk_domains_c` FOREIGN KEY (`usage_id`) REFERENCES `domainusage`(`id`);

错误代码： 1452
Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-648_bce`, CONSTRAINT `usage_id_43803979_fk_domains_c` FOREIGN KEY (`usage_id`) REFERENCES `domainusage` (`id`))

这个错误通常发生在尝试添加外键约束时，存在不一致的数据。在这种情况下，它指示 my_table1 表中的 usage_id 列的默认值与 domainusage 表的外键约束不匹配。

可能的原因和解决方法包括：

  1. domainusage 表中没有 id 为 1 的记录：如果你将 my_table1 表的 usage_id 列设置为引用 domainusage 表的 id 列作为外键约束，那么确保在 domainusage 表中存在一个 id 为 1 的记录。如果没有，请先添加一条记录，并确保它的 id 值为 1。

  2. domainusage 表的 id 为 1 的记录与 my_table1 表的数据不匹配：检查 domainusage 表中 id 为 1 的记录与 my_table1 表中的数据是否一致。确保 my_table1 表中的每一行都具有在 domainusage 表中存在的有效的 id 值。

  3. 数据库中已经存在不一致的数据：如果你已经尝试了上述步骤并且仍然遇到问题，可能是由于数据库中存在不一致的数据导致的。在这种情况下，你需要检查并修复 my_table1 表和 domainusage 表之间的数据一致性问题。可以通过更新或删除相关行来解决不一致的数据。

本示例中遇到的问题为第 1 个原因导致，解决办法为： 先在表 domainusage 中添加 id 为 1 的数据，然后再更新表 my_table1