mysql5.7 mha redis_mysql与redis错误集合

mysql错误集合

1.使用flush logs;报ERROR 1105 (HY000): Unknown error错误

报此错误是因为日志所在目录只有root有权限新建文件，从mysql客户端执行flush logs其实是以mysql这个账号去执行的，所以会权限不足

查看错误日志所在位置

mysql> show variables like '%log_error%';

+---------------------+-----------------------+

| Variable_name | Value |

+---------------------+-----------------------+

| binlog_error_action | IGNORE_ERROR |

| log_error | /data/mysql/mysql.log |

+---------------------+-----------------------+

2 rows in set (0.00 sec)

查看权限

[root@db02 mysql]# ll

total 44

-rw-r--r-- 1 root root 0 Apr 24 19:53 mysql.log

修改权限

[root@db02 mysql]# chown -R mysql. ./*

[root@db02 mysql]# ll

total 44

-rw-rw---- 1 mysql mysql 290 Apr 25 11:49 mysql-bin.index

-rw-r--r-- 1 mysql mysql 0 Apr 24 19:53 mysql.log

再重新flush logs就没有报此问题了

mysql> flush logs;

Query OK, 0 rows affected (0.00 sec)

2.使用GTID复制时，报Slave_SQL_Running停止时错误

错误如下：从库已有数据，此时再复制会报以下错误

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.51

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 1444

Relay_Log_File: db03-relay-bin.000002

Relay_Log_Pos: 408

Relay_Master_Log_File: mysql-bin.000006

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: 1007

Last_Error: Error 'Can't create database 'oldboy'; database exists' on query. Default dat

abase: 'oldboy'. Query: 'create database oldboy' Skip_Counter: 0

Exec_Master_Log_Pos: 404

Relay_Log_Space: 1651

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: 1007

Last_SQL_Error: Error 'Can't create database 'oldboy'; database exists' on query. Default dat

abase: 'oldboy'. Query: 'create database oldboy' Replicate_Ignore_Server_Ids:

Master_Server_Id: 51

Master_UUID: 45984377-6897-11e9-8ce0-000c2938e32d

Master_Info_File: /application/mysql-5.6.42/data/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: 190427 20:02:11

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 45984377-6897-11e9-8ce0-000c2938e32d:2-6

Executed_Gtid_Set: 45984377-6897-11e9-8ce0-000c2938e32d:1

Auto_Position: 1

解决方法：

mysql> reset master;

Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave;

Query OK, 0 rows affected (0.01 sec)

mysql> set global gtid_purged='45984377-6897-11e9-8ce0-000c2938e32d:2'; #事务日志后加1即可

Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_host='10.0.0.51',master_user='repl',master_password='123' ,MASTER_AUTO_POSITI

ON=1;Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.51

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 1444

Relay_Log_File: db03-relay-bin.000002

Relay_Log_Pos: 1553

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 1444

Relay_Log_Space: 1756

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: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 51

Master_UUID: 45984377-6897-11e9-8ce0-000c2938e32d

Master_Info_File: /application/mysql-5.6.42/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 45984377-6897-11e9-8ce0-000c2938e32d:1:3-6

Executed_Gtid_Set: 45984377-6897-11e9-8ce0-000c2938e32d:1-6

Auto_Position: 1

1 row in set (0.00 sec)

ERROR:

No query specified

3.mha使用masterha_check_repl --conf=/etc/mha/app1.cnf 检查配置时报错

错误如下：

[root@db03 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf

Sat Apr 27 23:33:29 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping

.Sat Apr 27 23:33:29 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..

Sat Apr 27 23:33:29 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..

Sat Apr 27 23:33:29 2019 - [info] MHA::MasterMonitor version 0.58.

Sat Apr 27 23:33:29 2019 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when c

onnecting 10.0.0.53(10.0.0.53:3306) :1045:Access denied for user 'mha'@'db03' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.Sat Apr 27 23:33:29 2019 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] at /usr/share/

perl5/vendor_perl/MHA/ServerManager.pm line 297.Sat Apr 27 23:33:30 2019 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln309] Got fatal error

, stopping operationsSat Apr 27 23:33:30 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened

on checking configurations. at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 329.Sat Apr 27 23:33:30 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened

on monitoring servers.Sat Apr 27 23:33:30 2019 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

解决方法：

需要将每个数据库里的空用户进行删除，如root@db01,''@'db01'等用户，因主机名不同此处的用户也不同

mysql> select user,host,password from mysql.user;

+------+-----------+----------+

| user | host | password |

+------+-----------+----------+

| root | localhost | |

| root | db01 | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

| | db01 | |

+------+-----------+----------+

mysql> drop user root@'db01';

Query OK, 0 rows affected (0.00 sec)

mysql> drop user ''@'db01';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

4.redis cluster错误

重新添加已删除的节点时，报此错误

[ERR] Node 10.0.0.51:7007 is not empty. Either the node already knows other nodes (check with CLUSTER NODES) or contains some key in database 0.

解决方法：

[root@db01 7007]# redis-cli -h 10.0.0.51 -p 7007 # 连接到redis

10.0.0.51:7007> flushdb # 清空数据

OK

10.0.0.51:7007>

10.0.0.51:7007>

10.0.0.51:7007> cluster reset # 重置cluster

再重新添加节点即可