一: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)