目录
- 61.1. Reset root password 重置MySQL root密码
- 61.2. 数据库内容替换
- 61.3. 查看错误代码
-
- 61.3.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
- 61.3.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
- 61.4. 临时表是否需要建索引
- 61.5. Kill 脚本
- 61.6. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
- 61.7. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
- 61.8. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
- 61.9. 重新整理AUTO_INCREMENT字段
- 61.10. 转换 latin1 到 UTF-8
- 61.11. this is incompatible with sql_mode=only_full_group_by
- 61.12. [Warning] Changed limits: max_open_files: 5000 (requested 20480)
- 61.13. ERROR 1364: 1364: Field 'id' doesn't have a default value
- 61.14. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
- 61.15. ERROR 1086 (HY000): File '/var/lib/mysql-files/order.txt' already exists
- 61.16. ERROR 1415: Not allowed to return a result set from a trigger
61.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"
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 [mysqld] # # 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 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-grant-tables=1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Recommended in standard MySQL setup sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
# systemctl restart mysqld
update mysql.user set authentication_string=password('netkiller') where user='root' and Host = 'localhost'; flush privileges; quit;
# 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 owners. 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; Bye
删除 skip-grant-tables=1 重启MySQL
原文出处:Netkiller 系列 手札
本文作者:陈景峯
转载请与作者联系,同时请务必标明文章原始出处和作者信息及本声明。