Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | 51CTO学院 | CSDN程序员研修院 | OSChina 博客 | 腾讯云社区 | 阿里云栖社区 | Facebook | Linkedin | Youtube | 打赏(Donations) | About

第 13 章 FAQ


13.1. Reset root password 重置MySQL root密码
13.1.1. MySQL 5.7.x
13.1.2. MySQL 8.0
13.2. 查看错误代码
13.2.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
13.2.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
13.3. 临时表是否需要建索引
13.4. [Warning] Changed limits: max_open_files: 5000 (requested 20480)
13.5. Table 'performance_schema.session_variables' doesn't exist
13.6. SQL Error (1038): Out of sort memory, consider increasing server sort buffer size
13.7. this is incompatible with sql_mode=only_full_group_by
13.8. ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 767 bytes
13.9. ERROR 1086 (HY000): File '/var/lib/mysql-files/order.txt' already exists
13.10. ERROR 1114 (HY000): The table 'your_table' is full
13.11. Error Code: 1146. Table 'test.CACHE_UPDATE' doesn't exist
13.12. Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
13.13. ERROR 1273 (HY000) at line 3364: Unknown collation: 'utf8mb4_0900_ai_ci'
13.14. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
13.15. ERROR 1364: 1364: Field 'id' doesn't have a default value
13.16. Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00' for column 'create_time' at row 95692
13.17. ERROR 1415: Not allowed to return a result set from a trigger
13.18. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
13.19. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
13.20. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
13.21. ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
13.22. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
13.23. ERROR 2013 (HY000): Lost connection to MySQL server during query
13.24. ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
13.25. ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
13.26. Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such fileor directory
13.27. com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Public Key Retrieval is not allowed
13.28. mysqldump: Couldn't execute 'SELECT COLUMN_NAME,
13.29. this is incompatible with sql_mode=only_full_group_by
13.30. mysqldump: [Warning] Using a password on the command line interface can be insecure.
13.31. mysql: [Warning] Using a password on the command line interface can be insecure.
13.32. 时间自动被加一秒

13.1. Reset root password 重置MySQL root密码

忘记root密码是使用 --skip-grant-tables 启动项

CentOS 6.x

# vim /etc/init.d/mysqld

 $exec --skip-grant-tables  --datadir="$datadir" --socket="$socketfile" \
    --pid-file="$mypidfile" \
    --basedir=/usr --user=mysql >/dev/null 2>&1 &
# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

# mysqladmin -u root flush-privileges password "newpassword"

13.1.1. MySQL 5.7.x

CentOS 7.x

添加 skip-grant-tables=1 选项,然后重启mysql

# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
# Disabling symbolic-links is recommended to prevent assorted security risks

# Recommended in standard MySQL setup

# systemctl restart mysqld
update mysql.user set authentication_string=password('netkiller') where user='root' and Host = 'localhost';
flush privileges;
# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> update mysql.user set authentication_string=password('netkiller') where user='root' and Host = 'localhost';
Query OK, 1 row affected, 1 warning (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;

删除 skip-grant-tables=1 重启MySQL

13.1.2. MySQL 8.0

[root@localhost log]# vim /etc/my.cnf

ALTER USER root@localhost identified by 'MQiEge1ikst7S_6tlXzBOmt';