MySQL数据库常用报错解决方法

1）Warning: (3719, u"‘utf8’ is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.")

报错如下：
解决方法： python 2.7+mysql:8.0

1. 查看mysql字符集：

   mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
   

2. 根据上述查询结果，修改sql语句；
   修改前 = ‘CREATE DATABASE IF NOT EXISTS database_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;’
   修改后 = ‘CREATE DATABASE IF NOT EXISTS database_name DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;’

2）pymysql.err.InternalError: (1130, u"Host ‘10.0.0.5’ is not allowed to connect to this MySQL server")

报错原因： 不容许本地机器连远程mysql服务；
解决步骤：

1. 本机连接MySQL数据库：

   # mysql -uroot -p123456
   

2. 操作mysql数据库

   mysql>use mysql;
   mysql>select 'host' from user where user='root';
   mysql>update user set host='%' where user='root';
   mysql>flush privileges;
   

3) ERROR 1062 (23000): Duplicate entry ‘%-root’ for key ‘PRIMARY’

报错场景： 开启MySQL远程操作时，会报1062错误

	[[e-mail] ~]# mysql -uroot -p123456
	mysql> update mysql.user set host='%' where user='root';
	ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'

若此处报错，则不予理会,不予理会,不予理会. 重要的事说三遍
//接着 刷新权限 或者服务重启操作

	mysql> flush privileges;
	mysql> select host,user from mysql.user ;
	+-----------+---------------+
	| host      | user          |
	+-----------+---------------+
	| %         | mysqld        |
	| %         | root          |
	| localhost | mysql.session |
	| localhost | mysql.sys     |
	| localhost | root          |
	+-----------+---------------+
	5 rows in set (0.00 sec)

如上所示，root用户对应的是root-%，即可远程登陆了。

4) ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password:yes)

报错原因： 这类错误，一般情况是由于登陆密码错误导致的；

解决方法： 重置密码

1. 跳过MySQL密码认证过程，方法为修改my.cnf文件，如下：

   [[e-mail] ~]# vim /etc/my.cnf
   

   在文档内搜索mysqld定位到[mysqld]文本段：
   在[mysqld]后面任意一行添加“skip-grant-tables”用来跳过密码验证的过程，如下：

   [mysqld]
   #
   skip-grant-tables //跳过表的权限验证，用户可以执行增删改查
   

2. 重启MySQL

   [[e-mail] ~]# service mysqld restart
   Redirecting to /bin/systemctl restart mysqld.service
   

3. 直接使用mysql进入mysql

   [[e-mail] ~]# mysql
   mysql>
   

4. 修改root的密码

   mysql>update mysql.user set password=password("你的新密码") where user="root";
   mysql>flust privileges；
   mysql>quit
   

   若出现ERROR 1054(42S22) Unknown column ‘password’ in ‘field list’，
   错误原因为： 5.7版本下的mysql数据库下已经没有password这个字段了，password字段改成了authentication_string，则使用以下命令：

   mysql>update mysql.user set authentication_string=password('123456') where user='root';
   

5. 编辑my.cnf，恢复原来需要密码认证，即将skip-grant-tables删除

6. 重启MySQL

      [[e-mail] ~]# service mysqld restart
      Redirecting to /bin/systemctl restart mysqld.service
   

7. 使用新修改的密码登陆MySQL，登陆成功，完整的操作流程如下：

   [[e-mail] ~]# mysql -uroot -p123456
   mysql: [Warning] Using a password on the command line interface can be insecure.
   ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
   [[e-mail] ~]# vim /etc/my.cnf
   [[e-mail] ~]# service mysqld restart
   Redirecting to /bin/systemctl restart mysqld.service
   [[e-mail] ~]# mysql
   mysql> update mysql.user set authentication_string=password('123456') where user='root';
   Query OK, 0 rows affected, 1 warning (0.00 sec)
   Rows matched: 1  Changed: 0  Warnings: 1
   mysql> flush privileges;
   Query OK, 0 rows affected (0.00 sec)
   mysql> quit
   Bye
   [[e-mail] ~]# vim /etc/my.cnf
   [[e-mail]~]# service mysqld restart
   Redirecting to /bin/systemctl restart mysqld.service
   [[e-mail] ~]# mysql -uroot -p123456
   

5) ERROR 1142 (42000): SELETE command denied to user ‘root’@‘localhost’ for table ‘user’

报错场景： 登陆数据库，操作数据库（增删改查）时，报ERROR 1142 (42000)的错误；
错误定位： MySQL ERROR 1142 (42000)为mysql grants权限不足引起的问题
解决方法： 修改mysql.user表的权限

1. 查看mysql所有用户信息的权限

   mysql> select * from mysql.user\G  
   	*************************** 1. row ***************************
   	                  Host: localhost
   	                  User: root
   	           Select_priv: N
   	           Insert_priv: N
   	           Update_priv: N
   	           Delete_priv: N
   	           Create_priv: N
   	             Drop_priv: N
   	           Reload_priv: N
   	         Shutdown_priv: N
   	          Process_priv: N
   	             File_priv: N
   	            Grant_priv: Y
   	       References_priv: N
   	            Index_priv: N
   	            Alter_priv: N
   	          Show_db_priv: N
   	            Super_priv: N
   

   从以上可以看出，很多权限都是N，即表示没有相应的权限；
2. 修改权限，根据需要选择

   mysql> update mysql.user set Select_priv='Y' where user='root';
   mysql> update mysql.user set Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_priv='Y',Drop_priv='Y' where user='root';
   

3. 查看权限，all privileges是对root主机的所有用户执行最大的权限

   mysql> show grants;
   	+---------------------------------------------------------------------+
   	| Grants for [e-mail]                                           |
   	+---------------------------------------------------------------------+
   	| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
   	| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
   	+---------------------------------------------------------------------+
   	2 rows in set (0.00 sec)