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连接,再重新打开,就可以进行远程授权了。


posted @ 2019-06-21 10:48  merlee  阅读(458)  评论(0编辑  收藏  举报