第 61 章 FAQ

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

目录

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 系列 手札
本文作者:陈景峯
转载请与作者联系,同时请务必标明文章原始出处和作者信息及本声明。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
存储 SQL 关系型数据库
|
存储 运维 关系型数据库
RDSPostgreSQL FAQ
RDSPostgreSQL FAQ
|
Oracle MySQL 关系型数据库
|
Java 开发工具 前端开发
|
NoSQL 数据库 Redis
|
分布式计算 Hadoop 资源调度
|
开发工具