mysql故障归纳
2019-06-21
- 错误描述：mysql主从同步错误代码1008。
# 前一天在主库上建立了daxia数据库，然后立马就删除了，导致从库并没有同步完成，然后再去执行binlog中删除的操作时，找不到daxia数据库，所以导致同步中断。
# 在从库上查看同步状态，如下
MySQL [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.80
Master_User: tongbu
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 153538609
Relay_Log_File: mysql2-relay-bin.000013
Relay_Log_Pos: 117201315
Relay_Master_Log_File: mysql-bin.000007
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: 1008
Last_Error: Error 'Can't drop database 'daxia'; database doesn't exist' on query. Default database: 'daxia'. Query: 'DROP DATABASE `daxia`'
- 原因及解决办法：在从库执行，将同步指针移动到下一指令就可以，跳过这个错误。
MySQL [(none)]> stop slave;
MySQL [(none)]> set global sql_slave_skip_counter = 1;
MySQL [(none)]> start slave;
2019-08-18
- 错误描述：刚搭建完mysql的环境，使用初始化的默认密码登入后，输入任何命令都会提示如下：
MySQL [(none)]>use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
MySQL [(none)]>show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
- 原因及解决办法：其实已经提示的很清楚了：在执行语句之前，必须要使用ALTER USER重置密码，那就重置呗。
MySQL [(none)]>alter user user() identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
2021-03-02
- 错误描述：授权时提示“ERROR 1221 (HY000)”
mysql> grant reload on cat.* to 'backup'@'172.16.%';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
- 原因及解决办法：有些权限是针对全局授权的，不能单独对某个库或者表。
mysql> grant reload on *.* to 'backup'@'172.16.%';
Query OK, 0 rows affected (0.00 sec)
2021-03-05
- 错误描述：授权时提示“1819 - Your password does not satisfy the current policy requirements”
mysql> GRANT SELECT ON *.* TO 'liuxx'@'172.16.1.11' IDENTIFIED by 'EarluweT';
1819 - Your password does not satisfy the current policy requirements
- 原因及解决办法：应该是设置的密码不符合mysql密码策略的要求。
# 首先看看密码长度要求。
mysql> show variables like 'validate_password_length';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| validate_password_length | 8 |
+--------------------------+-------+
1 row in set (0.00 sec)
# 再看看密码的策略等级，其等级解释如下：
## 0 or LOW：Length
## 1 or MEDIUM：Length; numeric, lowercase/uppercase, and special characters
## 2 or STRONGLength：numeric, lowercase/uppercase, and special characters; dictionary file
mysql> show variables like 'validate_password_policy';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| validate_password_policy | MEDIUM |
+--------------------------+--------+
# 此时看来可能是我们的密码不符合策略要求，修改为LOW，使其只验证密码的长度就可以。
mysql> set global validate_password_policy = 'LOW';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
2021-03-22
- 错误描述：root用户远程连接授权时报错。
> 1044 - Access denied for user 'root'@'x.x.x.x' to database 'test'
- 原因及解决办法：应该是没有开启root远程的grant_priv权限。
root@localhost 21:05:21 [(none)]> select user,host,grant_priv from mysql.user where user = 'root';
+------+---------------+------------+
| user | host | grant_priv |
+------+---------------+------------+
| root | localhost | Y |
| root | x.x.x.x | N |
+------+---------------+------------+
2 rows in set (0.00 sec)
# 可以看到x.x.x.x并未授权为Y，update下就好了
root@localhost 21:09:39 [(none)]> update mysql.user set grant_priv = 'Y' where user = 'root' and host = 'x.x.x.x'
root@localhost 21:12:19 [(none)]> flush privileges;
# Navicat的话，关闭mysql连接，再重新打开，就可以进行远程授权了。
