1：Mysql主从断开
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
从二进制日志读取数据时从主站收到致命错误 1236：“无法打开日志文件”
Last_SQL_Errno： 0
Last_SQL_Error：Replicate_Ignore_Server_Ids：Master_Server_Id： 132
Master_UUID： 03ab7a6c-33f3-11e9-b59b-000c2956d023
Master_Info_File： /data/mysql/data/master.info
SQL_Delay： 0
SQL_Remaining_Delay： 空
Slave_SQL_Running_State：
从站已读取所有中继日志;等待从属 I/O 线程更新它
Master_Retry_Count： 86400
Master_Bind：Last_IO_Error_Timestamp： 190220 09：00：13
Last_SQL_Error_Timestamp：Master_SSL_Crl：Master_SSL_Crlpath：
Retrieved_Gtid_Set：Executed_Gtid_Set：Auto_Position：
解决方法：
主库
mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000399 | 157 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
记住file和position这两个选项
备库操作：
mysql> stop slave
-> ;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> change master to master_log_file ='binlog.000399',master_log_pos=157;
Query OK, 0 rows affected, 3 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.166
Master_User: jack
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000399
Read_Master_Log_Pos: 157
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 323
Relay_Master_Log_File: binlog.000399
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
2：Mysql主从 Slave_SQL_Running=NO
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.166
Master_User: jack
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000400
Read_Master_Log_Pos: 14339919
Relay_Log_File: edu-mysql-relay-bin.000007
Relay_Log_Pos: 1221771
Relay_Master_Log_File: binlog.000400
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1146
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing tran saction 'ANONYMOUS' at master log binlog.000400, end_log_pos 1236185. See error log and/or performance_schema.replication_applier_status_by_worker table for m ore details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 1221649
Relay_Log_Space: 14340377
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1146
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing tran saction 'ANONYMOUS' at master log binlog.000400, end_log_pos 1236185. See error log and/or performance_schema.replication_applier_status_by_worker table for m ore details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 41048910-396b-11ed-89ca-005056ad782e
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 221124 09:54:51
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
解决方法：
重置备库：
reset slave;
重新刷一遍
mysql> change master to master_host='10.0.0.166' ,master_user='jack',master_password='oracle',master_log_file=' binlog.000401',master_log_pos=157;
Query OK, 0 rows affected, 8 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G;
日志在刷一遍
mysql> change master to master_log_file ='binlog.000402',master_log_pos=157;
Query OK, 0 rows affected, 3 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status;
3 [ERROR] [MY-012574]
2023-05-16T08:13:23.218879Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2023-05-16T08:13:24.219668Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2023-05-16T08:13:25.219835Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2023-05-16T08:13:26.219984Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2023-05-16T08:13:27.220134Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2023-05-16T08:13:28.220283Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2023-05-16T08:13:29.221063Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2023-05-16T08:13:30.221227Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2023-05-16T08:13:31.221377Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2023-05-16T08:13:32.221529Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2023-05-16T08:13:33.221679Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2023-05-16T08:13:34.222450Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2023-05-16T08:13:35.222615Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2023-05-16T08:13:36.222778Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
解决方案
[root@mha tmp]# rm -rf mysql.sock.lock
[root@mha tmp]# rm -rf mysqlx.sock.lock
[root@mha tmp]# /etc/init.d/mysqld start
Starting MySQL..... SUCCESS!
[ERROR]mysqldump: Got error: 1356:
mysql 全库备份时报如下错误
mysqldump: Got error: 1356: View 'xxx' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES
mysqldump: Couldn't execute 'SHOW FIELDS FROM `t_wms_factory_contrast`': View 'shpt_ordos_dev.t_wms_factory_contrast' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
解决
添加 --force
mysqldump -uxxx -pxxx --force --all-databases >/xxx/xxx/xxx.sql
原因
此选项的一个用途是使 mysqldump 继续执行，即使它遇到由于定义引用已删除的表而变得无效的视图。如果没有 --force，mysqldump 会退出并显示错误消息。使用 --force，mysqldump 会打印错误消息，但它也会将包含视图定义的 SQL 注释写入转储输出并继续执行。
4 [ERROR] [MY-010262]
mysql启动失败：[ERROR] [MY-010262] [Server] Can‘t start server: Bind on TCP/IP port: Address already in u
解决方法：mysql 3306端口号被占用，kill -9 pid解决，然后重启启动mysql数据库
5 ERROR 1227 (42000)
mysql> alter user 'mes'@'%' identified by 'oracle';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
解决思路：MySQL8版本中新增了一个system_user帐户类型，由于root用户没有SYSTEM_USER权限，导致错误出现。
为root添加权限：
grant system_user on *.* to 'root';
5 mysqld: error while loading shared libraries: libaio.so.1: cannot open
yum install -y libaio.so.1
如果执行了上面的命令仍无法解决，那么执行如下：
yum install -y libaio
222
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
