Mysql常见问题解决

原创

51奔跑在路上 2014-10-14 14:21:21 博主文章分类：数据库运维 ©著作权

©著作权归作者所有：来自51CTO博客作者51奔跑在路上的原创作品，如需转载，请与作者联系，否则将追究法律责任

一：mysql忘记密码：

killall -TERM mysqld
mysqld_safe --user=root --skip-grant-tables &
mysql -u root
use mysql
update user set password=password("new_pass") where user="root";  

#MySQL5.7修改方法： 
update mysql.user set authentication_string=password('new_pass') where user='root' ;
flush privileges;

二、ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/tmp/mysql.sock' (111)

1、[root@localhost ~]# find /-name mysql.sock/var/lib/mysql/mysql.sock
创建符号连接：
ln -s/var/lib/mysql/mysql.sock /tmp/mysql.sock

2、vi /etc/my.conf
检查下行内容是否存在：
[client]
socket=/tmp/mysql.sock

三、mysql找不到mysql.sock或文件为空

查看日志如下：

[ERROR] Can't start server: Bind on TCP/IP port:Cannot assign requested address
[ERROR] Do you already have another mysqld server running on port: 3306 ?
[ERROR] Aborting

提示是端口可能被占用，于是执行：

# netstat -anp |grep "3306"
# vi /etc/my.cnf

#把port改成3307：
# vi/etc/mysql/my.cnf
[client]
port            = 3307
[mysqld]port            = 3307

准备保存后启动mysql，再启动 mysql 就成功了

四、ERROR 1045 (28000): Access denied for user ’root’@’localhost’ (usingpassword: NO)

方法一：

# /etc/init.d/mysql stop 
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking & 
# mysql -u root mysql 
mysql> update user set password = PASSWORD('newpassword') where user='root';
#MySQL5.7修改方法：
#mysql> update user set authentication_string = PASSWORD('newpassword') where user='root';
mysql> FLUSH PRIVILEGES; 
mysql> quit 

# /etc/init.d/mysql restart 
# mysql -uroot -p 
Enter password:  
mysql>

方法二：

1、到安装的MySQL的目录下，找my.cnf文件；
在[mysqld]后添加skip-grant-tables（使用 set password for设置密码无效，且此后登录无需键入密码）
skip-grant-tables     #在my.cnf，[mysqld]下添加一行，使其登录时跳过权限检查
2、重启mysqld服务
$systemctl restart mysqld
此时再登陆mysql时，不需要密码就登陆进去了
3、修改root密码
mysql> use mysql;
mysql> select host,user,authentication_string from user;
#修改root密码
mysql> update user set authentication_string = password('newpassword') where user = 'root';
mysql> flush privileges;
4、注释my.cnf文件中 skip-grant-tables
#skip-grant-tables
5、重启mysqld服务，登陆MySQL

方法三：

直接使用
/etc/mysql/debian.cnf
文件中[client]节提供的用户名和密码: # 
mysql -udebian-sys-maint -p 
Enter password:  
mysql> UPDATE user SET Password=PASSWORD(’newpassword’) whereUSER=’root’; 
mysql> FLUSH PRIVILEGES; 
mysql> quit 
# mysql -uroot -p 
Enter password: <
输入新设的密码
newpassword> 
mysql>

五、ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

原因分析：

MySQL版本5.6.6版本起，添加了password_expired功能，它允许设置用户的过期时间。这个特性已经添加到mysql.user数据表，但是它的默认值是”N”，可以使用ALTER USER语句修改。

## MySQL版本5.7.6版本以前用户可以使用如下命令：
mysql> SET PASSWORD = PASSWORD('newpass'); 

## MySQL版本5.7.6版本开始的用户可以使用如下命令：
mysql> ALTER USER USER() IDENTIFIED BY 'newpass';

六、启动报错:File './mysql-bin.index'not found (Errcode: 13)

errcode13，一般就是权限问题，mysql用户是否对数据库目录内的所有文件具有写的权限，查看一下权限

chown mysql.mysql -R < mysql-bin.index所在的目录>

七、mysql主从库同步错误：1062 Error'Duplicate entry '1438019' for key 'PRIMARY'' on query

1、解决的办法是在从库上执行:

mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;

2、修改mysql的配置文件，让从库的同步线程忽略这个错误，方法：

# vim /etc/my.cnf

#在 [mysqld]下加一行 ,保存.重启mysql. mysql slave可以正常同步了. 
slave_skip_errors = 1062

八、mysql主从库同步错误：Got fatalerror 1236 from master when reading data from binary log

##在source(主)那边，执行：
flush logs;
show master status;

##记下File, Position。
##在target（从）端，执行：
CHANGE MASTER TOMASTER_LOG_FILE='testdbbinlog.000008',MASTER_LOG_POS=107;
slave start;
show slave status \G

##一切正常。

九、解决Mysql数据库提示innodb表不存在的问题

发现mysql的error.log里面有报错：

InnoDB: Error: Table "mysql"."innodb_table_stats" not found.

InnoDB: Error: Fetch of persistent statistics requested for table "qsh"."hello_hh" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

那么需重新构建mysql这个databases的5个表

1、登录数据库，进入mysql库，执行如下SQL删除5张表

记住，一定要是drop table if exists

mysql> use mysql;
mysql> drop table if exists innodb_index_stats; 
mysql> drop table if exists innodb_table_stats; 
mysql> drop table if exists slave_master_info; 
mysql> drop table if exists slave_relay_log_info; 
mysql> drop table if exists slave_worker_info;

执行完后，可以用show tables查看一下，看表的数据是否已经比删除之前减少了，如果减少了，说明你成功了！

2、上一步操作完成后，停止数据库，并进入到数据库数据文件所在目录，删除上面5个表所对应的idb文件，如下所示：

# systemctl stop mysqld.service
# cd /data/mysql/data/mysql/

# ls -l *.ibd
-rw-rw---- 1 mysql mysql 98304 May 27 14:17 innodb_index_stats.ibd
-rw-rw---- 1 mysql mysql 98304 May 27 14:17 innodb_table_stats.ibd
-rw-rw---- 1 mysql mysql 98304 May 27 14:14 slave_master_info.ibd
-rw-rw---- 1 mysql mysql 98304 May 27 14:14 slave_relay_log_info.ibd
-rw-rw---- 1 mysql mysql 98304 May 27 14:14 slave_worker_info.ibd

# /bin/rm -rf *.ibd

重新启动数据库，进入到mysql库，重建上面被删除的表结构：

数据库的建表脚本在mysql软件的安装目录的share目录下或者mysql的安装包的script目录下，我们这里可以find一下：

# find / -name mysql_system_tables.sql
/usr/share/mysql/mysql_system_tables.sql

# ls -l /usr/share/mysql/*.sql
-rw-r--r-- 1 root root 1066437 2021/03/26 15:08:18 /usr/share/mysql/fill_help_tables.sql
-rw-r--r-- 1 root root    3999 2021/03/26 14:58:52 /usr/share/mysql/innodb_memcached_config.sql
-rw-r--r-- 1 root root    2221 2021/03/26 15:19:00 /usr/share/mysql/install_rewriter.sql
-rw-r--r-- 1 root root    2171 2021/03/26 14:58:52 /usr/share/mysql/mysql_security_commands.sql
-rw-r--r-- 1 root root  288342 2021/03/26 14:58:52 /usr/share/mysql/mysql_sys_schema.sql
-rw-r--r-- 1 root root    1214 2021/03/26 14:58:52 /usr/share/mysql/mysql_system_tables_data.sql
-rw-r--r-- 1 root root  155031 2021/03/26 14:58:52 /usr/share/mysql/mysql_system_tables.sql
-rw-r--r-- 1 root root   10862 2021/03/26 14:58:52 /usr/share/mysql/mysql_test_data_timezone.sql
-rw-r--r-- 1 root root    1243 2021/03/26 15:19:00 /usr/share/mysql/uninstall_rewriter.sql

# systemctl start mysqld.service
mysql> use mysql;
mysql> source /usr/share/mysql/mysql_system_tables.sql;
mysql> show tables;
28 rows in set (0.00 sec)

再随便desc下5个的其中一倆个表看看：

mysql> desc innodb_table_stats;
mysql> desc slave_master_info;

最后再查看mysql的error.log日志，确认没有新的报错之后，就表示成功。

十、[ERROR] InnoDB: Table `mysql`.`server_cost` does not exist in the InnoDB internal data dictionary though MySQL is trying to drop it.

报错信息：

2021-06-10T15:32:16.115310+08:00 180 [Warning] InnoDB: Cannot open table mysql/engine_cost from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2021-06-10T15:32:16.116410+08:00 180 [Warning] InnoDB: Cannot open table mysql/gtid_executed from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2021-06-10T15:32:16.116512+08:00 180 [Warning] InnoDB: Cannot open table mysql/help_category from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2021-06-10T15:32:16.116593+08:00 180 [Warning] InnoDB: Cannot open table mysql/help_keyword from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2021-06-10T15:32:16.116666+08:00 180 [Warning] InnoDB: Cannot open table mysql/help_relation from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2021-06-10T15:32:16.116739+08:00 180 [Warning] InnoDB: Cannot open table mysql/help_topic from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2021-06-10T15:32:16.117659+08:00 180 [Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2021-06-10T15:32:16.118677+08:00 180 [Warning] InnoDB: Cannot open table mysql/server_cost from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

解决方法：

mysql> use mysql;
mysql> drop table if exists time_zone;
mysql> drop table if exists time_zone_name;
mysql> drop table if exists time_zone_transition;
mysql> drop table if exists time_zone_transition_type;
mysql> drop table if exists time_zone_leap_second;
mysql> drop table if exists engine_cost;
mysql> drop table if exists server_cost;
mysql> drop table if exists general_log;
mysql> drop table if exists gtid_executed;
mysql> drop table if exists help_category;
mysql> drop table if exists help_keyword;
mysql> drop table if exists help_relation;
mysql> drop table if exists help_topic;
mysql> drop table if exists plugin;

mysql> source /usr/share/mysql/innodb_memcached_config.sql;

mysql> desc plugin;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(64)  | NO   | PRI |         |       |
| dl    | varchar(128) | NO   |     |         |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

十一、Table 'mysql.servers' doesn't exist

报错信息：

mysql> flush privileges;
ERROR 1146 (42S02): Table 'mysql.servers' doesn't exist

解决方法：

mysql> drop table if exists mysql.servers;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `servers` (
    -> `Server_name` char(64) NOT NULL,
    -> `Host` char(64) NOT NULL,
    -> `Db` char(64) NOT NULL,
    -> `Username` char(64) NOT NULL,
    -> `Password` char(64) NOT NULL,
    -> `Port` int(4) DEFAULT NULL,
    -> `Socket` char(64) DEFAULT NULL,
    -> `Wrapper` char(64) NOT NULL,
    -> `Owner` char(64) NOT NULL,
    -> PRIMARY KEY (`Server_name`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    -> COMMENT='MySQL Foreign Servers table';
Query OK, 0 rows affected (0.00 sec)

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

OK，问题解决了。

十二、Table 'mysql.servers' doesn't exist

报错信息：

Error    : Table 'sys.sys_config' doesn't exist

解决方法：

#删除数据库
mysql> drop database sys;

#删除sys数据库文件
rm -rf /data/mysql/data/sys

##数据库的建表脚本在mysql软件的安装目录的share目录下或者mysql的安装包的script目录下，我们这里可以find一下：
# find / -name 'mysql_sys_schema.sql';
/usr/share/mysql/mysql_sys_schema.sql

#导入sql重建sys库
mysql> source /usr/share/mysql/mysql_sys_schema.sql;

#检查
mysql> desc sys.sys_config;
+----------+--------------+------+-----+-------------------+-----------------------------+
| Field    | Type         | Null | Key | Default           | Extra                       |
+----------+--------------+------+-----+-------------------+-----------------------------+
| variable | varchar(128) | NO   | PRI | NULL              |                             |
| value    | varchar(128) | YES  |     | NULL              |                             |
| set_time | timestamp    | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| set_by   | varchar(128) | YES  |     | NULL              |                             |
+----------+--------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

十三、ERROR 1044 (42000): Access denied for user

报错信息：

ERROR 1044 (42000): Access denied for user 'root'@'192.168.86.%' to database 'qa_testdb'

解决方法：

授权命令没带with grant option ，那么用户不能给其他用户授权。

#使用'root'@'localhost' 可以授权，但是'root'@'192.168.86%'无法正常授权
#查看权限表，发现'192.168.86%' 未带 WITH GRANT OPTION

mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| 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)

mysql> show grants for 'root'@'192.168.86%';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '192.168.86%'
mysql> show grants for 'root'@'192.168.86.%';
+------------------------------------------------------+
| Grants for root@192.168.86.%                         |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.86.%' |
+------------------------------------------------------+
1 row in set (0.00 sec)

###重新授权 'root'@'192.168.86.%' 后问题解决；
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.86.%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)