未加星标

MySQL使用的常见问题解决与应用技巧汇总

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二03 | 时间 | 作者 红领巾 ] 0人收藏点击收藏

前言

mysql日常开发或者是维护中,有一些问题或是故障往往是难以避免的,如丢失密码、表损坏。在此总结一下常见的问题,以备今后所需。

一、 忘记 MySQL 的 root 密码

1. 登录到数据库所在的服务器,手工 kill 掉 mysql 进程。

(1) 登录到数据库所在的服务器,手工 kill 掉 MySQL 进程:

[email protected]:/data/mysql# kill `cat ./mysql.pid`

其中,mysql.pid 指的是 MySQL 数据目录下的 pid 文件,它记录了 MySQL 服务的进程号。

(2) 使用 --skip-grant-tables 选项重启 MySQL 服务:

[email protected]:/data/mysql$ sudo /usr/local/mysql/bin/mysqld --skip-grant-tables --user=root &

--skip-grant-tables 选项意思是启动 MySQL 服务时跳过权限表认证。启动后,连接到 MySQL 的 root 将不需要口令。

(3) 用空密码的 root 用户连接到 mysql ,并且更改 root 口令:

[email protected]:/usr/local/mysql/bin$ mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log Source distribution
Copyright (c) 2000, 2017, 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 [(none)]> set password = password('123456');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
MySQL [(none)]> use mysql
Database changed
MySQL [mysql]> update user set authentication_string=password('123456') where user="root" and host="localhost";
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 1
MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MySQL [mysql]> exit;
Bye
****************************************************************
[email protected]:/usr/local/mysql/bin$ mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.18-log Source distribution
Copyright (c) 2000, 2017, 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 [(none)]>

由于使用了 --skip-grant-tables 选项启动,使用 “set password” 命令更改密码失败,直接更新 user 表的 authentication_string(测试版本为5.7.18,有的版本密码字段是 ‘password') 字段后,更改密码成功。刷新权限表,使权限认证重新生效。重新用 root 登录时,就可以使用刚刚修改后的口令了。

二、如何处理 myisam 存储引擎的表损坏

有的时候可能会遇到 myisam 表损坏的情况。一张损坏的表的症状通常是查询意外中断,并且能看到下述错误:

'table_name.frm' 被锁定不能更改
不能找到文件 'tbl_name.MYYI' (errcode:nnn)
文件意外结束
记录文件被毁坏
从表处理器得到错误 nnn。

通常有以下两种解决方法:

1. 使用 myisamchk 工具

使用 MySQL 自带的 myisamchk 工具进行修复:

shell> myisamchk -r tablename

其中 -r 参数的含义是 recover,上面的方法几乎能解决所有问题,如果不行,则使用命令:

shell> mysiamchk -o tablename

其中 -o 参数的含义是 --safe-recover,可以进行更安全的修复。

2. 使用 sql 命令

使用 MySQL 的 check table 和 repair table 命令一起进行修复,check table 用来检查表是否有损坏;repair table 用来对坏表进行修复。

三、 数据目录磁盘空间不足的问题

系统上线后,随着数据量的不断增加,会发现数据目录下的可用空间越来越小,从而给应用造成了安全隐患。

1. 对于 myisam 存储引擎的表

对于 myisam 存储引擎的表,在建表时可以用如下选项分别制定数据目录和索引目录存储到不同的磁盘空间,而默认会同时放在数据目录下:

data directory = 'absolute path to directory'
index directory = 'absolute path to directory'

如果表已经创建,只能先停机或者将表锁定,防止表的更改,然后将表的数据文件和索引文件 mv 到磁盘充足的分区上,然后在原文件处创建符号链接即可。

2. 对于 innodb 存储引擎的表

因为数据文件和索引文件是存放在一起的,所以无法将它们分离。当磁盘空间出现不足时,可以增加一个新的数据文件,这个文件放在充足空间的磁盘上。
具体实现方法是在参数 innodb_data_file_path 中增加此文件,路径写为新磁盘的绝对路径。

例如,如果 /home 下空间不足,希望在 /home1 下新增加一个可自动扩充数据的文件,那么参数可以这么写:

innodb_data_file_path = /home/ibdata1:2000M;/home1/ibdata2:2000M:autoextend

参数修改后,必须重启数据库才可以生效。

四、DNS反向解析的问题 (5.0 以后的版本默认跳过域名逆向解析)

在客户端执行 show processlist 命令,有时会出现很多进程,类似于:

unauthenticated user | 192.168.10.10:55644 | null | connect | null | login | null

这些进程会累计的越来越多,并且不会消失,应用无法正常相应,导致系统瘫痪。

MySQL 在默认情况下对于远程连接过来的 IP 地址会进行域名的逆向解析,如果系统的 hosts 文件中没有与之对应的域名,MySQL 就会将此连接认为是无效用户,所以下进程中出现 unauthenticated user 并导致进程阻塞。

解决的方法很简单,在启动时加上 --skip-name-resolve 选项,则 MySQL 就可以跳过域名解析过程,避免上述问题。

五、mysql.sock 丢失后如何连接数据库

在 MySQL 服务器本机上连接数据库时,经常会出现 mysql.sock 不存在,导致无法连接的问题。这是因为如果指定 localhost 作为一个主机名,则 mysqladmin 默认使用 Unix 套接字文件连接,而不是 tcp/ip。而这个套接字文件(一般命名为 mysql.sock)经常会因为各种原因而被删除。通过 --protocol=TCP|SOCKET|PIPE|MEMORY 选项,用户可以显式地指定连接协议,下面演示使用了 Unix 套接字失败后使用 tcp 协议连接成功的例子。

1. Unix 套接字连接:

[email protected]:~$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

2. tcp 连接

[email protected]:~$ mysql --protocol=TCP

六、MyISAM表过大,无法访问问题

首先我们可以通过myisamchk命令来查看MyISAM表的情况。如下图,我查看admin表
MySQL使用的常见问题解决与应用技巧汇总

datefile length代表当前文件大小
keyfile length代表索引文件大小
max datefile length 最大文件大小
max keyfile length 最大索引大小
可以通过如下命令来进行扩展数据文件大小

alter table table_name MAX_ROWS=88888888 AVG_ROW_LE=66666

七、数据目录磁盘空间不足的问题

针对MyISAM存储引擎

可以将数据目录和索引目录存储到不同的磁盘空间。

针对InnoDB存储引擎

对于InnoDB存储引擎的表,因为数据文件和索引文件时存放在一起的。所以无法将他们分离。当磁盘空间出现不足时候,可以增加一个新的数据文件,这个文件放在有充足空间的磁盘上。具体实现是通过InnoDB_data_file_path中增加此文件。

innodb_data_file_path=/home/mysql/data:10000M;/user/mysql/data:10000M:autoextend

参数修改之后,需要重启服务器,才可以生效。

八、同一台主机上安装多个Mysql

除了每个Mysql安装目录不能相同外,还需要的是port和socket不能一样。

mysql.sock就是客户端连接与mysql间通信用的。socket文件,只能本机使用,远程连接要通过tcp/ip了。

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: MySQLSQL服务器数据InnoDB数据库谢大RY删除
tags: MySQL,mysql,文件,data,gt,root,磁盘,table,user,连接,password,bogon
分页:12
转载请注明
本文标题:MySQL使用的常见问题解决与应用技巧汇总
本站链接:http://www.codesec.net/view/569341.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(75)