MySQL常见问题和处理方法

一、忘记root密码

方法 1：使用 --skip-grant-tables 跳过权限表认证

#1、杀掉 mysql 进程
kill `cat /home/mysql/mysql.pid`

#2、使用 --skip-grant-tables 启动 MySQL 服务时跳过权限表认证
/home/mysql/mysql/bin/mysqld --defaults-file=/home/mysql/my.cnf --user=mysql --skip-grant-tables & 

#3、连接 MySQL
mysql -uroot -S /home/mysql/mysql.sock

#4、先刷新权限表，不然报（(ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement)）
flush privileges;

#5、更改密码
alter user root@'localhost' identified by "newhadoop";

#6、刷新权限表
flush privileges;

方法 2：使用 --init-file

#1、杀掉 mysql 进程
kill `cat /home/mysql/mysql.pid`

#2、创建初始化 sql 文件，文件包含修改用户密码的 sql 语句
[mysql@localhost tmp]$ cat alter_user.sql
alter user root@'localhost' identified by "newhadoop";

#3、使用 --init-file 启动 MySQL 服务，启动完成密码已修改，需要用新密码连接
/home/mysql/mysql/bin/mysqld --defaults-file=/home/mysql/my.cnf --user=mysql --init-file=/home/mysql/tmp/alter_user.sql & 

二、数据目录磁盘空间不足

​ 可以在其他目录创建通用表空间，在通用表空间上建新表或将已存在的表用导出、重建表、导入的方法迁移到通用表空间。

#1、创建通用表空间
create tablespace general_ts add datafile '/home/mysql/mysqldata_sec/general_ts.ibd' engine=innodb;

#2、在通用表空间上创建新表
CREATE TABLE `t6` (
  `id` bigint(32) NOT NULL,
  `insert_time` datetime DEFAULT NULL
) tablespace general_ts ENGINE=InnoDB DEFAULT CHARSET=utf8;

三、mysql.sock 丢失，连接数据库

#使用 TCP 连接
mysql --protocol=TCP -uroot -p -P3306 -hlocalhost

四、mysql 出现了 Waiting for table metadata lock

#1、查看未提交事务
mysql> select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx \G;
*************************** 1. row ***************************
          trx_state: RUNNING
        trx_started: 2020-03-12 12:01:41
trx_mysql_thread_id: 4
          trx_query: NULL
1 row in set (0.00 sec)

ERROR:
No query specified


#2、查询此事务在做什么操作,判断是否要 kill 掉
select * from information_schema.processlist where ID=4;

输出项说明

# trx_state: 事务状态，一般为RUNNING
# trx_started: 事务执行的起始时间，若时间较长，则要分析该事务是否合理
# trx_mysql_thread_id: MySQL的线程ID，用于kill
# trx_query: 事务中的sql 一般只要kill掉这些线程，DDL操作就不会Waiting for table metadata lock。