ERROR 1442 (HY000): Can't update table '...' in stored function/trigger because it is already used

Mysqlliuliangsong 发表了文章 • 0 个评论 • 403 次浏览 • 2016-12-22 16:31 • 来自相关话题

一、MySQL错误现象:MySQL执行创建的触发器时,报如下错误:ERROR 1442 (HY000): Can't update table 'tmp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
 下面为错误触发器的创建过程:1、创建测试表格:drop table if exists tmp;
create table tmp (id int, n1 int, n2 int);
insert tmp values(1, 10, 50);

MariaDB [test]> select * from tmp;
+------+------+------+
| id | n1 | n2 |
+------+------+------+
| 1 | 10 | 50 |
+------+------+------+
1 row in set (0.01 sec)2、创建一个MySQL触发器:DELIMITER $
drop trigger if exists tmp_update$
create trigger tmp_update
after update on tmp
for each row
begin
update tmp set n2=n1*5 where id=new.id;
end$
DELIMITER ;3、测试触发效果:mysql> select * from tmp;
+------+------+------+
| id | n1 | n2 |
+------+------+------+
| 1 | 10 | 50 |
+------+------+------+
1 row in set (0.00 sec)

mysql> update tmp set n1=2 where id=1;
ERROR 1442 (HY000): Can't update table 'tmp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 二、错误原因:  这是由于MySQL触发器,触发事件的表与触发更新的表是用一表。触发更新同一表时,不应该使用update语句更新,应该使用set修改NEW对象更新即可。
 三、解决方案:1、将触发器的创建SQL语句改成如下并执行创建即可:DELIMITER $
drop trigger if exists tmp_update$
create trigger tmp_update
before update on tmp -- 更新之前执行,这样才能在NEW插入到数据库之前,修改NEW.n2
for each row
begin
set new.n2 = new.n1*5; -- 直接修改new.n2
end$
DELIMITER ; 触发器测试效果:aiezu.com> select * from tmp;
+------+------+------+
| id | n1 | n2 |
+------+------+------+
| 1 | 10 | 50 |
+------+------+------+
1 row in set (0.00 sec)

aiezu.com> update tmp set n1=12 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

aiezu.com> select * from tmp;
+------+------+------+
| id | n1 | n2 |
+------+------+------+
| 1 | 12 | 60 |
+------+------+------+
1 row in set (0.00 sec) 查看全部

一、MySQL错误现象:

MySQL执行创建的触发器时,报如下错误:
ERROR 1442 (HY000): Can't update table 'tmp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

 下面为错误触发器的创建过程:

1、创建测试表格:

drop table if exists tmp;
create table tmp (id int, n1 int, n2 int);
insert tmp values(1, 10, 50);

MariaDB [test]> select * from tmp;
+------+------+------+
| id | n1 | n2 |
+------+------+------+
| 1 | 10 | 50 |
+------+------+------+
1 row in set (0.01 sec)

2、创建一个MySQL触发器:

DELIMITER $
drop trigger if exists tmp_update$
create trigger tmp_update
after update on tmp
for each row
begin
update tmp set n2=n1*5 where id=new.id;
end$
DELIMITER ;

3、测试触发效果:

mysql> select * from tmp;
+------+------+------+
| id | n1 | n2 |
+------+------+------+
| 1 | 10 | 50 |
+------+------+------+
1 row in set (0.00 sec)

mysql> update tmp set n1=2 where id=1;
ERROR 1442 (HY000): Can't update table 'tmp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
 

二、错误原因:

  这是由于MySQL触发器,触发事件的表与触发更新的表是用一表。触发更新同一表时,不应该使用update语句更新,应该使用set修改NEW对象更新即可。
 

三、解决方案:

1、将触发器的创建SQL语句改成如下并执行创建即可:
DELIMITER $
drop trigger if exists tmp_update$
create trigger tmp_update
before update on tmp -- 更新之前执行,这样才能在NEW插入到数据库之前,修改NEW.n2
for each row
begin
set new.n2 = new.n1*5; -- 直接修改new.n2
end$
DELIMITER ;
 触发器测试效果:
aiezu.com> select * from tmp;
+------+------+------+
| id | n1 | n2 |
+------+------+------+
| 1 | 10 | 50 |
+------+------+------+
1 row in set (0.00 sec)

aiezu.com> update tmp set n1=12 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

aiezu.com> select * from tmp;
+------+------+------+
| id | n1 | n2 |
+------+------+------+
| 1 | 12 | 60 |
+------+------+------+
1 row in set (0.00 sec)

MySQL触发器用法详解

Mysqlliuliangsong 发表了文章 • 0 个评论 • 1060 次浏览 • 2016-12-22 10:53 • 来自相关话题

一、MySQL触发器创建:1、MySQL触发器的创建语法:CREATE [DEFINER = { 'user' | CURRENT_USER }] 
TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
[trigger_order]
trigger_body 2、MySQL创建语法中的关键词解释:字段含义可能的值DEFINER=可选参数,指定创建者,默认为当前登录用户(CURRENT_USER);
该触发器将以此参数指定的用户执行,所以需要考虑权限问题;DEFINER='root@%'
DEFINER=CURRENT_USERtrigger_name触发器名称,最好由表名+触发事件关键词+触发时间关键词组成; trigger_time触发时间,在某个事件之前还是之后;BEFORE、AFTERtrigger_event触发事件,如插入时触发、删除时触发;
  INSERT:插入操作触发器,INSERT、LOAD DATA、REPLACE时触发;
  UPDATE:更新操作触发器,UPDATE操作时触发;
  DELETE:删除操作触发器,DELETE、REPLACE操作时触发;INSERT、UPDATE、DELETEtable_name 触发操作时间的表名; trigger_order可选参数,如果定义了多个具有相同触发事件和触法时间的触发器时(
如:BEFORE UPDATE),默认触发顺序与触发器的创建顺序一致,可以
使用此参数来改变它们触发顺序。mysql 5.7.2起开始支持此参数。
  FOLLOWS:当前创建触发器在现有触发器之后激活;
  PRECEDES:当前创建触发器在现有触发器之前激活;FOLLOWS、PRECEDEStrigger_body触发执行的SQL语句内容,一般以begin开头,end结尾begin .. end 3、触发执行语句内容(trigger_body)中的OLD,NEW:  在trigger_body中,我们可以使用NEW表示将要插入的新行(相当于MS SQL的INSERTED),OLD表示将要删除的旧行(相当于MS SQL的DELETED)。通过OLD,NEW中获取它们的字段内容,方便在触发操作中使用,下面是对应事件是否支持OLD、NEW的对应关系:事件OLDNEWINSERT×√DELETE√×UPDATE√√  由于UPDATE相当于删除旧行(OLD),然后插入新行(NEW),所以UPDATE同时支持OLD、NEW;
 4、MySQL分隔符(DELIMITER):  MySQL默认使用“;”作为分隔符,SQL语句遇到“;”就会提交。而我们的触发器中可能会有多个“;”符,为了防止触发器创建语句过早的提交,我们需要临时修改MySQL分隔符,创建完后,再将分隔符改回来。使用DELIMITER可以修改分隔符,如下:DELIMITER $
... --触发器创建语句;
$ --提交创建语句;
DELIMITER ; 二、MySQL触发器创建进阶:1、MySQL触发器中使用变量:  MySQL触发器中变量变量前面加'@',无需定义,可以直接使用:-- 变量直接赋值
set @num=999;

-- 使用select语句查询出来的数据方式赋值,需要加括号:
set @name =(select name from table);2、MySQL触发器中使用if语做条件判断:-- 简单的if语句:
set sex = if (new.sex=1, '男', '女');

-- 多条件if语句:
if old.type=1 then
update table ...;
elseif old.type=2 then
update table ...;
end if; 三、MySQL查看触发器:  可以使用“show triggers;”查看触发器。由于MySQL创建的触发器保存在“information_schema库中的triggers表中,所以还可以通过查询此表查看触发器:-- 通过information_schema.triggers表查看触发器:
select * from information_schema.triggers;

-- mysql 查看当前数据库的触发器
show triggers;

-- mysql 查看指定数据库"aiezu"的触发器
show triggers from aiezu; 四、MySQL删除触发器:1、可以使用drop trigger删除触发器:drop trigger trigger_name; 2、删除前先判断触发器是否存在:drop trigger if exists trigger_name 五、Msql触发器用法举例:1、MySQL触发器Insert触发更新同一张表:  下面我们有一个表“tmp1”,tmp1表有两个整型字段:n1、n2。我们要通过触发器实现,在tmp插入记录时,自动将n2字段的值设置为n1字段的5倍。
 创建测试表和触发器:-- 创建测试表
drop table if exists tmp1;
create table tmp1 (n1 int, n2 int);

-- 创建触发器
DELIMITER $
drop trigger if exists tmp1_insert$
create trigger tmp1_insert
before insert on tmp1
for each row
begin
set new.n2 = new.n1*5;
end$
DELIMITER ;测试触发更新效果:mysql> insert tmp1(n1) values(18);
Query OK, 1 row affected (0.01 sec)

mysql> insert tmp1(n1) values(99);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tmp1;
+------+------+
| n1 | n2 |
+------+------+
| 18 | 90 |
| 99 | 495 |
+------+------+
2 rows in set (0.00 sec) 2、MySQL触发器Update触发更新另一张表:  下面有有两个表tmp1、tmp2,两个表都有一个相同的字段name。使用触发器实现更新一个表的name时,将另外一个表的name也更新。
 创建测试表和触发器:-- 创建测试表和插入测试数据
drop table if exists tmp1;
drop table if exists tmp2;
create table tmp1 (id int, name varchar(128)) default charset='utf8';
create table tmp2 (fid int, name varchar(128)) default charset='utf8';
insert into tmp1 values(1, '爱E族');
insert into tmp2 values(1, '爱E族');

-- 创建触发器
DELIMITER $
drop trigger if exists tmp1_update$
create trigger tmp1_update
after update on tmp1
for each row
begin
update tmp2 set name=new.name where fid=new.id;
end$
DELIMITER ;测试触发更新效果:mysql> select * from tmp1;
+------+---------+
| id | name |
+------+---------+
| 1 | 爱E族 |
+------+---------+
1 row in set (0.00 sec)

mysql> select * from tmp2;
+------+---------+
| fid | name |
+------+---------+
| 1 | 爱E族 |
+------+---------+
1 row in set (0.00 sec)

mysql> update tmp1 set name='aiezu.com' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from tmp1;
+------+-----------+
| id | name |
+------+-----------+
| 1 | aiezu.com |
+------+-----------+
1 row in set (0.00 sec)

mysql> select * from tmp2;
+------+-----------+
| fid | name |
+------+-----------+
| 1 | aiezu.com |
+------+-----------+
1 row in set (0.00 sec) 查看全部

一、MySQL触发器创建:

1、MySQL触发器的创建语法:

CREATE [DEFINER = { 'user' | CURRENT_USER }] 
TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
[trigger_order]
trigger_body

 

2、MySQL创建语法中的关键词解释:

字段含义可能的值
DEFINER=可选参数,指定创建者,默认为当前登录用户(CURRENT_USER);
该触发器将以此参数指定的用户执行,所以需要考虑权限问题;
DEFINER='root@%'
DEFINER=CURRENT_USER
trigger_name触发器名称,最好由表名+触发事件关键词+触发时间关键词组成; 
trigger_time触发时间,在某个事件之前还是之后;BEFORE、AFTER
trigger_event触发事件,如插入时触发、删除时触发;
  INSERT:插入操作触发器,INSERT、LOAD DATA、REPLACE时触发;
  UPDATE:更新操作触发器,UPDATE操作时触发;
  DELETE:删除操作触发器,DELETE、REPLACE操作时触发;
INSERT、UPDATE、DELETE
table_name 触发操作时间的表名; 
trigger_order可选参数,如果定义了多个具有相同触发事件和触法时间的触发器时(
如:BEFORE UPDATE),默认触发顺序与触发器的创建顺序一致,可以
使用此参数来改变它们触发顺序。mysql 5.7.2起开始支持此参数。
  FOLLOWS:当前创建触发器在现有触发器之后激活;
  PRECEDES:当前创建触发器在现有触发器之前激活;
FOLLOWS、PRECEDES
trigger_body触发执行的SQL语句内容,一般以begin开头,end结尾begin .. end
 

3、触发执行语句内容(trigger_body)中的OLD,NEW:

  在trigger_body中,我们可以使用NEW表示将要插入的新行(相当于MS SQL的INSERTED),OLD表示将要删除的旧行(相当于MS SQL的DELETED)。通过OLD,NEW中获取它们的字段内容,方便在触发操作中使用,下面是对应事件是否支持OLD、NEW的对应关系:
事件OLDNEW
INSERT×
DELETE×
UPDATE
  由于UPDATE相当于删除旧行(OLD),然后插入新行(NEW),所以UPDATE同时支持OLD、NEW;
 

4、MySQL分隔符(DELIMITER):

  MySQL默认使用“;”作为分隔符,SQL语句遇到“;”就会提交。而我们的触发器中可能会有多个“;”符,为了防止触发器创建语句过早的提交,我们需要临时修改MySQL分隔符,创建完后,再将分隔符改回来。使用DELIMITER可以修改分隔符,如下:
DELIMITER $
... --触发器创建语句;
$ --提交创建语句;
DELIMITER ;
 

二、MySQL触发器创建进阶:

1、MySQL触发器中使用变量:

  MySQL触发器中变量变量前面加'@',无需定义,可以直接使用:
-- 变量直接赋值
set @num=999;

-- 使用select语句查询出来的数据方式赋值,需要加括号:
set @name =(select name from table);

2、MySQL触发器中使用if语做条件判断:

-- 简单的if语句:
set sex = if (new.sex=1, '男', '女');

-- 多条件if语句:
if old.type=1 then
update table ...;
elseif old.type=2 then
update table ...;
end if;
 

三、MySQL查看触发器:

  可以使用“show triggers;”查看触发器。由于MySQL创建的触发器保存在“information_schema库中的triggers表中,所以还可以通过查询此表查看触发器:
-- 通过information_schema.triggers表查看触发器:
select * from information_schema.triggers;

-- mysql 查看当前数据库的触发器
show triggers;

-- mysql 查看指定数据库"aiezu"的触发器
show triggers from aiezu;
 

四、MySQL删除触发器:

1、可以使用drop trigger删除触发器:

drop trigger trigger_name;
 

2、删除前先判断触发器是否存在:

drop trigger if exists trigger_name
 

五、Msql触发器用法举例:

1、MySQL触发器Insert触发更新同一张表:

  下面我们有一个表“tmp1”,tmp1表有两个整型字段:n1、n2。我们要通过触发器实现,在tmp插入记录时,自动将n2字段的值设置为n1字段的5倍。
 创建测试表和触发器:
-- 创建测试表
drop table if exists tmp1;
create table tmp1 (n1 int, n2 int);

-- 创建触发器
DELIMITER $
drop trigger if exists tmp1_insert$
create trigger tmp1_insert
before insert on tmp1
for each row
begin
set new.n2 = new.n1*5;
end$
DELIMITER ;
测试触发更新效果:
mysql> insert tmp1(n1) values(18);
Query OK, 1 row affected (0.01 sec)

mysql> insert tmp1(n1) values(99);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tmp1;
+------+------+
| n1 | n2 |
+------+------+
| 18 | 90 |
| 99 | 495 |
+------+------+
2 rows in set (0.00 sec)
 

2、MySQL触发器Update触发更新另一张表:

  下面有有两个表tmp1、tmp2,两个表都有一个相同的字段name。使用触发器实现更新一个表的name时,将另外一个表的name也更新。
 创建测试表和触发器:
-- 创建测试表和插入测试数据
drop table if exists tmp1;
drop table if exists tmp2;
create table tmp1 (id int, name varchar(128)) default charset='utf8';
create table tmp2 (fid int, name varchar(128)) default charset='utf8';
insert into tmp1 values(1, '爱E族');
insert into tmp2 values(1, '爱E族');

-- 创建触发器
DELIMITER $
drop trigger if exists tmp1_update$
create trigger tmp1_update
after update on tmp1
for each row
begin
update tmp2 set name=new.name where fid=new.id;
end$
DELIMITER ;
测试触发更新效果:
mysql> select * from tmp1;
+------+---------+
| id | name |
+------+---------+
| 1 | 爱E族 |
+------+---------+
1 row in set (0.00 sec)

mysql> select * from tmp2;
+------+---------+
| fid | name |
+------+---------+
| 1 | 爱E族 |
+------+---------+
1 row in set (0.00 sec)

mysql> update tmp1 set name='aiezu.com' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from tmp1;
+------+-----------+
| id | name |
+------+-----------+
| 1 | aiezu.com |
+------+-----------+
1 row in set (0.00 sec)

mysql> select * from tmp2;
+------+-----------+
| fid | name |
+------+-----------+
| 1 | aiezu.com |
+------+-----------+
1 row in set (0.00 sec)

一次由于磁盘空间满导致的MySQL故障

Mysqlliuliangsong 发表了文章 • 0 个评论 • 614 次浏览 • 2016-12-03 11:45 • 来自相关话题

一、故障描述:
  今天早上起来,发现收到了邮件报警,提示网站500错误。测试服务器上的网站,凡是没使用数据库的页面能正常打开、而使用到数据库的要么500错误,要么一直在加载中。
 
二、追根溯源:
1、“show processlist”:
  看到这种现象,第一个想到的可能是不良SQL语句导致的锁表,才会导致网站页面一直加载中;连接到数据库,发现“use dbname”语句不能用,也就是不能切换数据库;直接运行“show processlist”查看正在运行的SQL语句,发现有好几百条SQL语句未执行完正常退出,选了两条时间最长的:Id User Host db Command Time State Info
4507153 root localhost:35745 ad Execute 1568 query end INSERT INTO `sessions` (`id`, `modified`, `data`, `lifetime`) VALUES (?, ?, ?, ?)
4507157 root localhost:35752 ad Query 1566 Opening tables DESCRIBE `sessions`  可以看出,就是简单的插入操作和显示表结果操作,所以不使用由于SQL语句不良导致的锁表;想“DESCRIBE `sessions`”复原错误现场试试,但是由于切换不了数据库,只能暂时放弃。所以这里找不出问题所在,决定看看MySQL日志看看。

2、mysqld日志:
  打开MySQL日志(日志文件的路径在“/etc/my.cnf”中指定,默认为“/var/log/mysqld.log”),终于在日志文件底部发现了那么几行错误:161203 10:30:39 [Warning] Disk is full writing '/storage/aiezu.com/mysql/ad/sessions.MYI' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
161203 10:30:39 [Warning] Retry in 60 secs. Message reprinted in 600 secs
161203 10:40:39 [Warning] Disk is full writing '/storage/aiezu.com/mysql/ad/sessions.MYI' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
161203 10:40:39 [Warning] Retry in 60 secs. Message reprinted in 600 secs
161203 10:50:39 [Warning] Disk is full writing '/storage/aiezu.com/mysql/ad/sessions.MYI' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
161203 10:50:39 [Warning] Retry in 60 secs. Message reprinted in 600 secs  从上面错误提示可以看出,写“sessions”表索引文件时发现磁盘空间不足;
 
3、清理磁盘空间:
  使用“df -h”命令查看当前磁盘空间的使用情况,发现我的“/storage”挂载点的磁盘空间使用率确实达到了“100%”:[root@aiezu.com ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/xvda1 20G 11G 7.7G 59% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
/dev/xvdb1 189G 189G 0G 100% /storage  当然,如果即使使用“df -h”命令查看磁盘空间使用率不是“100%”,还得使用“df -i”命令看看磁盘的“inode”使用率,如果“inode”用完,也会导致无法创建文件:[root@aiezu.com ~]# df -i
Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/xvda1 1310720 640191 670529 49% /
tmpfs 490142 1 490141 1% /dev/shm
/dev/xvdb1 26214408 1451980 24762428 6% /storage  发现磁盘空间不足后,删除到一些不重要的上传文件腾出了一半的磁盘空间:[root@aiezu.com ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/xvda1 20G 11G 7.7G 59% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
/dev/xvdb1 189G 98G 91G 51% /storage 
三、前因后果:
  重启数据库后,发现其他网站正常了,而有一个原来加载很慢的网站,现在刷新直接报500错误了;查看此网站的错误日志,发现依然是提示“sessions”表有错误。
  再次连接到MySQL,切换到站点的数据库,执行“select * from sessions limit 1”语句,提示如下错误:mysql> use ad;
Database changed
mysql> select * from sessions limit 1;
ERROR 145 (HY000): Table './ad/sessions' is marked as crashed and should be repaired  看来是由于磁盘空间满的时候导致表文件损坏,经网上搜索,使用下面命令成功将表修复,问题全部得到解决:[root@aiezu.com ~]# myisamchk -c -r /storage/aiezu.com/mysql/ad/sessions.MYI
- recovering (with sort) MyISAM-table '/storage/aiezu.com/mysql/ad/sessions.MYI'
Data records: 8
- Fixing index 1
- Fixing index 2 
四、总结:
  这次的问题非常严重,导致了好几个站点不能正常访问,极大的影响了用户和搜索引擎对网站的信任;主要是由于未预计到用户的图片上传量,未及时扩充磁盘空间,也没有做磁盘空间监控,确实应该深刻检讨。 查看全部
一、故障描述:
  今天早上起来,发现收到了邮件报警,提示网站500错误。测试服务器上的网站,凡是没使用数据库的页面能正常打开、而使用到数据库的要么500错误,要么一直在加载中。
 
二、追根溯源:
1、“show processlist”:
  看到这种现象,第一个想到的可能是不良SQL语句导致的锁表,才会导致网站页面一直加载中;连接到数据库,发现“use dbname”语句不能用,也就是不能切换数据库;直接运行“show processlist”查看正在运行的SQL语句,发现有好几百条SQL语句未执行完正常退出,选了两条时间最长的:
Id	User	Host	db	Command	Time	State	Info
4507153 root localhost:35745 ad Execute 1568 query end INSERT INTO `sessions` (`id`, `modified`, `data`, `lifetime`) VALUES (?, ?, ?, ?)
4507157 root localhost:35752 ad Query 1566 Opening tables DESCRIBE `sessions`
  可以看出,就是简单的插入操作和显示表结果操作,所以不使用由于SQL语句不良导致的锁表;想“DESCRIBE `sessions`”复原错误现场试试,但是由于切换不了数据库,只能暂时放弃。所以这里找不出问题所在,决定看看MySQL日志看看。

2、mysqld日志:
  打开MySQL日志(日志文件的路径在“/etc/my.cnf”中指定,默认为“/var/log/mysqld.log”),终于在日志文件底部发现了那么几行错误:
161203 10:30:39 [Warning] Disk is full writing '/storage/aiezu.com/mysql/ad/sessions.MYI' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
161203 10:30:39 [Warning] Retry in 60 secs. Message reprinted in 600 secs
161203 10:40:39 [Warning] Disk is full writing '/storage/aiezu.com/mysql/ad/sessions.MYI' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
161203 10:40:39 [Warning] Retry in 60 secs. Message reprinted in 600 secs
161203 10:50:39 [Warning] Disk is full writing '/storage/aiezu.com/mysql/ad/sessions.MYI' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
161203 10:50:39 [Warning] Retry in 60 secs. Message reprinted in 600 secs
  从上面错误提示可以看出,写“sessions”表索引文件时发现磁盘空间不足;
 
3、清理磁盘空间:
  使用“df -h”命令查看当前磁盘空间的使用情况,发现我的“/storage”挂载点的磁盘空间使用率确实达到了“100%”:
[root@aiezu.com ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/xvda1 20G 11G 7.7G 59% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
/dev/xvdb1 189G 189G 0G 100% /storage
  当然,如果即使使用“df -h”命令查看磁盘空间使用率不是“100%”,还得使用“df -i”命令看看磁盘的“inode”使用率,如果“inode”用完,也会导致无法创建文件:
[root@aiezu.com ~]# df -i
Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/xvda1 1310720 640191 670529 49% /
tmpfs 490142 1 490141 1% /dev/shm
/dev/xvdb1 26214408 1451980 24762428 6% /storage
  发现磁盘空间不足后,删除到一些不重要的上传文件腾出了一半的磁盘空间:
[root@aiezu.com ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/xvda1 20G 11G 7.7G 59% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
/dev/xvdb1 189G 98G 91G 51% /storage
 
三、前因后果:
  重启数据库后,发现其他网站正常了,而有一个原来加载很慢的网站,现在刷新直接报500错误了;查看此网站的错误日志,发现依然是提示“sessions”表有错误。
  再次连接到MySQL,切换到站点的数据库,执行“select * from sessions limit 1”语句,提示如下错误:
mysql> use ad;
Database changed
mysql> select * from sessions limit 1;
ERROR 145 (HY000): Table './ad/sessions' is marked as crashed and should be repaired
  看来是由于磁盘空间满的时候导致表文件损坏,经网上搜索,使用下面命令成功将表修复,问题全部得到解决:
[root@aiezu.com ~]# myisamchk -c -r /storage/aiezu.com/mysql/ad/sessions.MYI 
- recovering (with sort) MyISAM-table '/storage/aiezu.com/mysql/ad/sessions.MYI'
Data records: 8
- Fixing index 1
- Fixing index 2
 
四、总结:
  这次的问题非常严重,导致了好几个站点不能正常访问,极大的影响了用户和搜索引擎对网站的信任;主要是由于未预计到用户的图片上传量,未及时扩充磁盘空间,也没有做磁盘空间监控,确实应该深刻检讨。

MySQL的selinux配置

Mysqlliuliangsong 发表了文章 • 0 个评论 • 478 次浏览 • 2016-12-01 16:53 • 来自相关话题

一、selinux的概念:
  selinux是Security Enhanced Linux (安全强化 Linux)的简称,selinux涉及到主体(subject,一般指进程)、操作(operation)、对象(object,又称资源,如:文件、网络端口),selinux用于控制“主体”能以何种“操作”方式访问什么“对象”。
  selinux环境中,所有的“主体(进程)”被赋予一个“domain”标签,所有的“对象”也被赋予一个“type”类型标签,“domain”标定的“主体”能访问哪些“type”标定的“对象”,由selinux的安全策略定义。
 
二、selinux环境:
selinux有三种状态,分别是:Enforcing:强制模式,selinux正严格按照策略运行中;Permissive:宽容模式,selinux正在运行中,但是只记录日志,不执行实际限制;Disabled:禁用模式,selinux环境未被开启;
 
  我们可以使用“getenforce”命令查看selinux是否在强制模式中运行:[root@aiezu.com ~]# getenforce
Disabled  如果状态为“Disabled”,我们必须在"/etc/sysconfig/selinux"文件中,将“SELINUX=disabled”改为“SELINUX=enforcing”,并重启服务器后才能开启;
  如果状态为“Permissive”,可以直接使用“setenforce 1”命令临时改为强制状态:[root@aiezu.com ~]# getenforce
Permissive
[root@aiezu.com ~]# setenforce 1
[root@aiezu.com ~]# getenforce
Enforcing  
三、mysql服务中的selinux定义:
1、mysql进程主体的domain标签:
  在selinux环境中,mysqld的“domain”标签为“mysqld_t”、mysqld在受限的“mysqld_t”域中仅能访问指定的资源;

2、selinux环境中mysql能访问的资源标签:标签名用途mysqld_db_t 这种文件类型用于标记MySQL数据库数据文件;
在RHEL/CentOS中数据文件的默认位置是“/var/lib/mysql”;
如果修改了MySQL数据文件的位置,新的位置必须使用这种类型标签;mysqld_etc_t这种文件类型用于标记MySQL的配置文件;
默认位置为:
  主配置文件:“/etc/my.cnf”;
  其他配置文件:“/etc/my.cnf.d/”目录下的文件;mysqld_exec_t 这种文件类型用于标记MySQL主进程文件;
默认为:“/usr/libexec/mysqld”或者“/usr/sbin/mysqld”;
可以通过“whereis mysqld”命令查找;mysqld_initrc_exec_t 这种文件类型用于标记MySQL的初始化脚本;
默认为:“/etc/rc.d/init.d/mysqld”;
“service mysqld start”中“mysqld”用的就是此脚本;mysqld_log_t 这种文件类型用于标记日志文件;mysqld_var_run_t 这种文件类型用于标记MySQL运行时产生的文件;
默认在“/var/run/mysqld”目录中,主要包括:
  /var/run/mysqld/mysqld.pid
  /var/run/mysqld/mysqld.sock 
3、selinux中mysq的布尔变量:变量名含义allow_user_mysql_connect 当这个变量值为“1”时允许用户连接数据库;exim_can_connect_db 当这个变量值为“1”时允许exim邮件程序访问数据库服务器;ftpd_connect_db 当这个变量值为“1”时允许ftpd进程访问数据库服务器;httpd_can_network_connect_db当这个变量值为“1”时允许httpd进程访问数据库服务器; 
四、配置演示:
  下面演示在selinux环境中,由于修改了mysql的数据存放路径,而导致的selinux权限错误、及其修正方法;在测试前,我们得先得确认我们的linux系统处于“selinux”环境,请参考第二步。
  1、首先,我们在修改mysql路径前确定mysql是能正常运行的,并停止mysql服务:[root@aiezu.com ~]# service mysql stop
Shutting down MySQL.. SUCCESS!
[root@aiezu ~]# service mysql start
Starting MySQL. SUCCESS!
[root@aiezu.com ~]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 5166 mysql 10u IPv6 22791 0t0 TCP *:mysql (LISTEN)
[root@aiezu.com ~]# service mysql stop
Shutting down MySQL.. SUCCESS!
  2、创建新的mysql数据存放路径,并使用“mysql_install_db”初始化目录;我这里只是测试,原数据库的数据不需要保留,如果需要保留,需要想将原数据库备份,修改路径完成正常运行后再恢复:[root@aiezu.com ~]# mkdir -p /storage/db
[root@aiezu.com ~]# mysql_install_db --datadir=/storage/db/
[root@aiezu.com ~]# chown -R mysql:mysql /storage/db/
  3、在mysql的配置文件"/etc/my.cnf"中,在"[mysqld]"组下,将"datadir="的值改成新的路径;修改后我的配置文件内容如下:[mysqld]
datadir=/storage/db/
socket=/storage/db/mysql.sock
pid-file=/storage/db/aiezu.com.pid

[mysqld_safe]
log-error=/var/log/mysqld.log

[client]
socket=/storage/db/mysql.sock  注意“[mysqld]”组和“[client]”组下的“socket=”后面的值要一致,否则运行mysql连接数据的时候会提示如下错误:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
   4、尝试启动MySQL,观察提示的错误(由于我们上面没有为新的mysql目录设置selinux权限,这里一定会报错的):[root@aiezu.com ~]# service mysql start
Starting MySQL. ERROR! The server quit without updating PID file (/storage/db/aiezu.com.pid).  在mysql的日志文件中能看到更详细的错误信息,可以在“my.cnf”中找到日志文件的路径,我这里的路径为“/var/log/mysqld.log”:161201 23:26:55 mysqld_safe Starting mysqld daemon with databases from /storage/db/
2016-12-01 23:26:56 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-12-01 23:26:56 0 [Warning] Can't create test file /storage/db/aiezu.lower-test
2016-12-01 23:26:56 0 [Note] /usr/sbin/mysqld (mysqld 5.6.34) starting as process 6215 ...
2016-12-01 23:26:56 6215 [Warning] Can't create test file /storage/db/aiezu.lower-test
2016-12-01 23:26:56 6215 [Warning] Can't create test file /storage/db/aiezu.lower-test
2016-12-01 23:26:56 6215 [Note] Plugin 'FEDERATED' is disabled.
^G/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied)
2016-12-01 23:26:56 6215 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
...
2016-12-01 23:26:56 6215 [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions
2016-12-01 23:26:56 6215 [ERROR] InnoDB: The system tablespace must be writable!
...  可以看到日志文件中出现了“Permission denied”字样,这就是由于selinux权限引起的(上面我们设置了文件系统权限,所以不是文件系统权限引起的);
  5、使用chcon命令为mysql数据目录设置selinux权限,并启动mysql,发现启动成功:[root@aiezu.com ~]# chcon -R -t mysqld_db_t /storage/db/
[root@aiezu.com ~]# service mysql start
Starting MySQL. SUCCESS!
[root@aiezu.com ~]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 6590 mysql 10u IPv6 24127 0t0 TCP *:mysql (LISTEN)
[root@aiezu.com ~]# ps -eZ|grep mysqld
unconfined_u:system_r:mysqld_safe_t:s0 6437 ? 00:00:00 mysqld_safe
unconfined_u:system_r:mysqld_t:s0 6590 ? 00:00:00 mysqldchcon命令的用法请参考:http://aiezu.com/article/linux_chcon_command.html 查看全部
一、selinux的概念:
  selinux是Security Enhanced Linux (安全强化 Linux)的简称,selinux涉及到主体(subject,一般指进程)、操作(operation)、对象(object,又称资源,如:文件、网络端口),selinux用于控制“主体”能以何种“操作”方式访问什么“对象”。
  selinux环境中,所有的“主体(进程)”被赋予一个“domain”标签,所有的“对象”也被赋予一个“type”类型标签,“domain”标定的“主体”能访问哪些“type”标定的“对象”,由selinux的安全策略定义。
 
二、selinux环境:
selinux有三种状态,分别是:
  • Enforcing:强制模式,selinux正严格按照策略运行中;
  • Permissive:宽容模式,selinux正在运行中,但是只记录日志,不执行实际限制;
  • Disabled:禁用模式,selinux环境未被开启;

 
  我们可以使用“getenforce”命令查看selinux是否在强制模式中运行:
[root@aiezu.com ~]# getenforce 
Disabled
  如果状态为“Disabled”,我们必须在"/etc/sysconfig/selinux"文件中,将“SELINUX=disabled”改为“SELINUX=enforcing”,并重启服务器后才能开启;
  如果状态为“Permissive”,可以直接使用“setenforce 1”命令临时改为强制状态:
[root@aiezu.com ~]# getenforce 
Permissive
[root@aiezu.com ~]# setenforce 1
[root@aiezu.com ~]# getenforce
Enforcing
  
三、mysql服务中的selinux定义:
1、mysql进程主体的domain标签:
  在selinux环境中,mysqld的“domain”标签为“mysqld_t”、mysqld在受限的“mysqld_t”域中仅能访问指定的资源;

2、selinux环境中mysql能访问的资源标签:
标签名用途
mysqld_db_t 这种文件类型用于标记MySQL数据库数据文件;
在RHEL/CentOS中数据文件的默认位置是“/var/lib/mysql”;
如果修改了MySQL数据文件的位置,新的位置必须使用这种类型标签;
mysqld_etc_t这种文件类型用于标记MySQL的配置文件;
默认位置为:
  主配置文件:“/etc/my.cnf”;
  其他配置文件:“/etc/my.cnf.d/”目录下的文件;
mysqld_exec_t 这种文件类型用于标记MySQL主进程文件;
默认为:“/usr/libexec/mysqld”或者“/usr/sbin/mysqld”;
可以通过“whereis mysqld”命令查找;
mysqld_initrc_exec_t 这种文件类型用于标记MySQL的初始化脚本;
默认为:“/etc/rc.d/init.d/mysqld”;
“service mysqld start”中“mysqld”用的就是此脚本;
mysqld_log_t 这种文件类型用于标记日志文件;
mysqld_var_run_t 这种文件类型用于标记MySQL运行时产生的文件;
默认在“/var/run/mysqld”目录中,主要包括:
  /var/run/mysqld/mysqld.pid
  /var/run/mysqld/mysqld.sock
 
3、selinux中mysq的布尔变量:
变量名含义
allow_user_mysql_connect 当这个变量值为“1”时允许用户连接数据库;
exim_can_connect_db 当这个变量值为“1”时允许exim邮件程序访问数据库服务器;
ftpd_connect_db 当这个变量值为“1”时允许ftpd进程访问数据库服务器;
httpd_can_network_connect_db当这个变量值为“1”时允许httpd进程访问数据库服务器;
 
四、配置演示:
  下面演示在selinux环境中,由于修改了mysql的数据存放路径,而导致的selinux权限错误、及其修正方法;在测试前,我们得先得确认我们的linux系统处于“selinux”环境,请参考第二步。
  1、首先,我们在修改mysql路径前确定mysql是能正常运行的,并停止mysql服务:
[root@aiezu.com ~]# service mysql stop
Shutting down MySQL.. SUCCESS!
[root@aiezu ~]# service mysql start
Starting MySQL. SUCCESS!
[root@aiezu.com ~]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 5166 mysql 10u IPv6 22791 0t0 TCP *:mysql (LISTEN)
[root@aiezu.com ~]# service mysql stop
Shutting down MySQL.. SUCCESS!

  2、创建新的mysql数据存放路径,并使用“mysql_install_db”初始化目录;我这里只是测试,原数据库的数据不需要保留,如果需要保留,需要想将原数据库备份,修改路径完成正常运行后再恢复:
[root@aiezu.com ~]# mkdir -p /storage/db
[root@aiezu.com ~]# mysql_install_db --datadir=/storage/db/
[root@aiezu.com ~]# chown -R mysql:mysql /storage/db/

  3、在mysql的配置文件"/etc/my.cnf"中,在"[mysqld]"组下,将"datadir="的值改成新的路径;修改后我的配置文件内容如下:
[mysqld]
datadir=/storage/db/
socket=/storage/db/mysql.sock
pid-file=/storage/db/aiezu.com.pid

[mysqld_safe]
log-error=/var/log/mysqld.log

[client]
socket=/storage/db/mysql.sock
  注意“[mysqld]”组和“[client]”组下的“socket=”后面的值要一致,否则运行mysql连接数据的时候会提示如下错误:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

   4、尝试启动MySQL,观察提示的错误(由于我们上面没有为新的mysql目录设置selinux权限,这里一定会报错的):
[root@aiezu.com ~]# service mysql start
Starting MySQL. ERROR! The server quit without updating PID file (/storage/db/aiezu.com.pid).
  在mysql的日志文件中能看到更详细的错误信息,可以在“my.cnf”中找到日志文件的路径,我这里的路径为“/var/log/mysqld.log”:
161201 23:26:55 mysqld_safe Starting mysqld daemon with databases from /storage/db/
2016-12-01 23:26:56 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-12-01 23:26:56 0 [Warning] Can't create test file /storage/db/aiezu.lower-test
2016-12-01 23:26:56 0 [Note] /usr/sbin/mysqld (mysqld 5.6.34) starting as process 6215 ...
2016-12-01 23:26:56 6215 [Warning] Can't create test file /storage/db/aiezu.lower-test
2016-12-01 23:26:56 6215 [Warning] Can't create test file /storage/db/aiezu.lower-test
2016-12-01 23:26:56 6215 [Note] Plugin 'FEDERATED' is disabled.
^G/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied)
2016-12-01 23:26:56 6215 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
...
2016-12-01 23:26:56 6215 [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions
2016-12-01 23:26:56 6215 [ERROR] InnoDB: The system tablespace must be writable!
...
  可以看到日志文件中出现了“Permission denied”字样,这就是由于selinux权限引起的(上面我们设置了文件系统权限,所以不是文件系统权限引起的);
  5、使用chcon命令为mysql数据目录设置selinux权限,并启动mysql,发现启动成功:
[root@aiezu.com ~]# chcon -R -t mysqld_db_t /storage/db/
[root@aiezu.com ~]# service mysql start
Starting MySQL. SUCCESS!
[root@aiezu.com ~]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 6590 mysql 10u IPv6 24127 0t0 TCP *:mysql (LISTEN)
[root@aiezu.com ~]# ps -eZ|grep mysqld
unconfined_u:system_r:mysqld_safe_t:s0 6437 ? 00:00:00 mysqld_safe
unconfined_u:system_r:mysqld_t:s0 6590 ? 00:00:00 mysqld
chcon命令的用法请参考:http://aiezu.com/article/linux_chcon_command.html

Lost connection to MySQL server at 'reading initial communication packet', system error: 0

Mysqlliuliangsong 发表了文章 • 0 个评论 • 593 次浏览 • 2016-11-23 13:29 • 来自相关话题

连接MySQL提示:ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0这是由于库文件初始化连接MySQL时连接失败引起的。
 
导致此错误的原因有:mysql设置文件中“bind-address”值的问题;访问权限限制问题;防火墙、杀毒软件阻拦的问题(特别是Windows);负载过大、最大连接限制了访问(特别正式提供服务的mysql) 
 
1、mysql设置文件的问题:
  在mysql数据库一些版本,为了保证安全,mysql安装后,默认只监听本机ip的“127.0.0.1”,如下:


  这时表现为连接“127.0.0.1”或“localhost”可以连通,连接局域网ip和公网IP无法连通。这时我们将“/etc/my.cnf”文件中的“bind-address”行前添加一个“#”号注释掉,并重启mysqld服务即可:[mysqld]
datadir=/storage/aiezu.com/mysql
socket=/storage/aiezu.com/mysql/mysql.sock

#bind-address=127.0.0.1  注:linux下mysql配置文件默认在“/etc/my.cnf”,Windows平台mysql配置文件在安装目录下的“my.ini”。
 
2、访问权限的问题:
  ①、默认mysql的账户只允许本机登录的,要给账户授权通过“ip+端口”进行远程访问,需要先在mysql服务器上授权。在服务器桑登录到mysql,执行下面SQL语句授权:use mysql;
-- 将数据库"aiezu"的所有权限授权给"root"通过密码"123456"在"8.8.8.8"上进行访问;
grant all privileges on aiezu.* to 'root'@'8.8.8.8' identified by '123456';

-- 将数据库"test"的所有权限授权给"aiezu"通过密码"123321"在任何主机上进行访问;
grant all privileges on test.* to 'aiezu'@'%' identified by '123321';

--刷新权限表
flush privileges;
   ②、 在mysql客户端连接到服务端时,服务端会根据客户端的ip,尝试将客户端的ip解析为“主机名”,再通过“主机名”去检查mysql的权限表,这项功能叫“name-resolv”。由于此功能的某些原因,也会导致我们文章标题中的错误,还有就是会导致连接数据库变慢,所以需要将吃功能禁用。
  禁用方法:在mysql设置文件“[mysqld]”选项组下一行“skip-name-resolve”;[mysqld]
datadir=/storage/aiezu.com/mysql
socket=/storage/aiezu.com/mysql/mysql.sock

#bind-address=127.0.0.1
skip-name-resolve 
  ③、如果是linux,修改“/etc/hosts.allow”文件,在文件的末尾添加下面内容,允许所有主机访问本机的“mysql”服务:mysqld : ALL : ALLOW
mysqld-max : ALL : ALLOW 
3、防火墙和杀毒软件:
  关闭杀毒软件和防火墙试试,windows方面:关闭系统防火墙,中国人常用的QQ电脑管家、360安全卫士、QQ医生、金山毒霸、卡巴斯基等;linux方便,检查iptables、ipfire系统自带防火墙的设置。
 
4、服务过载的问题:
  ①、mysql服务过载:执行“show processlist;”SQL命令看是否当前打开的连接过多; 使用“show variables like '%connect_timeout%';”语句的设置值,改成一个稍大的值,如20:“SET GLOBAL connect_timeout = 20;”使用“SHOW STATUS like 'aborted_%';”语句查询异常终止的连接和失败的连接;
 
  ②、系统过载:使用shell命令“top”观察mysql占有的内存、cpu情况;使用shell命令“free -h”看内存占有情况;使用shell命令“uptime”看cpu负载情况;使用shell命令“df -h”看磁盘空间使用率,特别占用率100%的情况;
  查看全部
连接MySQL提示:
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
这是由于库文件初始化连接MySQL时连接失败引起的。
 
导致此错误的原因有:
  • mysql设置文件中“bind-address”值的问题;
  • 访问权限限制问题;
  • 防火墙、杀毒软件阻拦的问题(特别是Windows);
  • 负载过大、最大连接限制了访问(特别正式提供服务的mysql)
 
 
1、mysql设置文件的问题:
  在mysql数据库一些版本,为了保证安全,mysql安装后,默认只监听本机ip的“127.0.0.1”,如下:
reading_initial_communication_packet.png
  这时表现为连接“127.0.0.1”或“localhost”可以连通,连接局域网ip和公网IP无法连通。这时我们将“/etc/my.cnf”文件中的“bind-address”行前添加一个“#”号注释掉,并重启mysqld服务即可:
[mysqld]
datadir=/storage/aiezu.com/mysql
socket=/storage/aiezu.com/mysql/mysql.sock

#bind-address=127.0.0.1
  注:linux下mysql配置文件默认在“/etc/my.cnf”,Windows平台mysql配置文件在安装目录下的“my.ini”。
 
2、访问权限的问题:
  ①、默认mysql的账户只允许本机登录的,要给账户授权通过“ip+端口”进行远程访问,需要先在mysql服务器上授权。在服务器桑登录到mysql,执行下面SQL语句授权:
use mysql;
-- 将数据库"aiezu"的所有权限授权给"root"通过密码"123456"在"8.8.8.8"上进行访问;
grant all privileges on aiezu.* to 'root'@'8.8.8.8' identified by '123456';

-- 将数据库"test"的所有权限授权给"aiezu"通过密码"123321"在任何主机上进行访问;
grant all privileges on test.* to 'aiezu'@'%' identified by '123321';

--刷新权限表
flush privileges;

   ②、 在mysql客户端连接到服务端时,服务端会根据客户端的ip,尝试将客户端的ip解析为“主机名”,再通过“主机名”去检查mysql的权限表,这项功能叫“name-resolv”。由于此功能的某些原因,也会导致我们文章标题中的错误,还有就是会导致连接数据库变慢,所以需要将吃功能禁用。
  禁用方法:在mysql设置文件“[mysqld]”选项组下一行“skip-name-resolve”;
[mysqld]
datadir=/storage/aiezu.com/mysql
socket=/storage/aiezu.com/mysql/mysql.sock

#bind-address=127.0.0.1
skip-name-resolve
 
  ③、如果是linux,修改“/etc/hosts.allow”文件,在文件的末尾添加下面内容,允许所有主机访问本机的“mysql”服务:
mysqld : ALL : ALLOW
mysqld-max : ALL : ALLOW
 
3、防火墙和杀毒软件:
  关闭杀毒软件和防火墙试试,windows方面:关闭系统防火墙,中国人常用的QQ电脑管家、360安全卫士、QQ医生、金山毒霸、卡巴斯基等;linux方便,检查iptables、ipfire系统自带防火墙的设置。
 
4、服务过载的问题:
  ①、mysql服务过载:
  • 执行“show processlist;”SQL命令看是否当前打开的连接过多;
 
  • 使用“show variables like '%connect_timeout%';”语句的设置值,改成一个稍大的值,如20:“SET GLOBAL connect_timeout = 20;”
  • 使用“SHOW STATUS like 'aborted_%';”语句查询异常终止的连接和失败的连接;

 
  ②、系统过载:
  • 使用shell命令“top”观察mysql占有的内存、cpu情况;
  • 使用shell命令“free -h”看内存占有情况;
  • 使用shell命令“uptime”看cpu负载情况;
  • 使用shell命令“df -h”看磁盘空间使用率,特别占用率100%的情况;

 

MySQL事物表与非事物表的优缺点

回复

Mysqllinyu520 回复了问题 • 1 人关注 • 1 个回复 • 650 次浏览 • 2016-11-21 23:21 • 来自相关话题

MySQL有哪些存储引擎(MySQL存储引擎大全)

Mysqllinyu520 发表了文章 • 0 个评论 • 488 次浏览 • 2016-11-21 00:25 • 来自相关话题

名称介绍MyISAMMyISAM存储引擎提供高速存储和检索,以及全文搜索能力。
MyISAM在所有MySQL版本里被支持;不支持事务处理;它是MySQL的默认的存储引擎;MEMORYMEMORY存储引擎,别称HEAP存储引擎;
提供“内存中”表,将数据存储在内存中。
MEMORY存储引擎不支持事务处理;MySQL的所有版本都支持InnoDB存储引擎;
注释:MEMORY存储引擎正式地被确定为HEAP引擎。MERGEMRG_MYISAM存储引擎,别名MERGE;
MRG_MYISAM存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。
MRG_MYISAM存储引擎不支持事务处理;MySQL的所有版本都支持MRG_MYISAM存储引擎;ISAMObsolete storage engine, now replaced by MyISAM MRG_ISAMObsolete storage engine, now replaced by MERGE InnoDBInnoDB存储引擎,别名INNOBASE;提供事务安全表;
MySQL的所有版本都支持InnoDB存储引擎;它支持事务处理;BDBBDB存储引擎,别名BERKELEYDB;
BDB存储引擎提供事务安全表;
mysql 5.1以下版本才支持此存储引擎;EXAMPLEEXAMPLE存储引擎是一个“存根”引擎,它不做什么。
你可以用这个引擎创建表,但没有数据被存储于其中或从其中检索。
这个引擎的目的是服务,在MySQL源代码中的一个例子,它演示说明如何开始编写新存储引擎。
同样,它的主要兴趣是对开发者。NDB
 NDB存储引擎,别名NDBCLUSTER;
NDB Cluster是被MySQL Cluster用来实现分割到多台计算机上的表的存储引擎。
它在MySQL-Max 5.1二进制分发版里提供。
这个存储引擎当前只被Linux, Solaris, 和Mac OS X 支持。
在未来的MySQL分发版中,我们想要添加其它平台对这个引擎的支持,包括Windows。ARCHIVEARCHIVE存储引擎被用来无索引地,非常小地覆盖存储的大量数据。CSVCSV存储引擎把数据以逗号分隔的格式存储在文本文件中。
CSV存储引擎不支持事物处理;BLACKHOLEBLACKHOLE存储引擎接受但不存储数据,并且查询也总是返回一个空集;
/dev/null storage engine (anything you write to it disappears)FEDERATEDFEDERATED存储引擎把数据存在远程数据库中。
在MySQL 5.1中,它只和MySQL一起工作,使用MySQL C Client API。
在未来的分发版中,我们想要让它使用其它驱动器或客户端连接方法连接到另外的数据源。
FEDERATED存储引擎支持事务处理; 查看全部
名称介绍
MyISAMMyISAM存储引擎提供高速存储和检索,以及全文搜索能力。
MyISAM在所有MySQL版本里被支持;不支持事务处理;它是MySQL的默认的存储引擎;
MEMORYMEMORY存储引擎,别称HEAP存储引擎;
提供“内存中”表,将数据存储在内存中。
MEMORY存储引擎不支持事务处理;MySQL的所有版本都支持InnoDB存储引擎;
注释:MEMORY存储引擎正式地被确定为HEAP引擎。
MERGEMRG_MYISAM存储引擎,别名MERGE;
MRG_MYISAM存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。
MRG_MYISAM存储引擎不支持事务处理;MySQL的所有版本都支持MRG_MYISAM存储引擎;
ISAMObsolete storage engine, now replaced by MyISAM 
MRG_ISAMObsolete storage engine, now replaced by MERGE 
InnoDBInnoDB存储引擎,别名INNOBASE;提供事务安全表;
MySQL的所有版本都支持InnoDB存储引擎;它支持事务处理;
BDBBDB存储引擎,别名BERKELEYDB;
BDB存储引擎提供事务安全表;
mysql 5.1以下版本才支持此存储引擎;
EXAMPLEEXAMPLE存储引擎是一个“存根”引擎,它不做什么。
你可以用这个引擎创建表,但没有数据被存储于其中或从其中检索。
这个引擎的目的是服务,在MySQL源代码中的一个例子,它演示说明如何开始编写新存储引擎。
同样,它的主要兴趣是对开发者。
NDB
 
NDB存储引擎,别名NDBCLUSTER;
NDB Cluster是被MySQL Cluster用来实现分割到多台计算机上的表的存储引擎。
它在MySQL-Max 5.1二进制分发版里提供。
这个存储引擎当前只被Linux, Solaris, 和Mac OS X 支持。
在未来的MySQL分发版中,我们想要添加其它平台对这个引擎的支持,包括Windows。
ARCHIVEARCHIVE存储引擎被用来无索引地,非常小地覆盖存储的大量数据。
CSVCSV存储引擎把数据以逗号分隔的格式存储在文本文件中。
CSV存储引擎不支持事物处理;
BLACKHOLEBLACKHOLE存储引擎接受但不存储数据,并且查询也总是返回一个空集;
/dev/null storage engine (anything you write to it disappears)
FEDERATEDFEDERATED存储引擎把数据存在远程数据库中。
在MySQL 5.1中,它只和MySQL一起工作,使用MySQL C Client API。
在未来的分发版中,我们想要让它使用其它驱动器或客户端连接方法连接到另外的数据源。
FEDERATED存储引擎支持事务处理;

Linux忘记mysql root密码解决方法

Mysqllinyu520 发表了文章 • 0 个评论 • 709 次浏览 • 2016-11-20 15:52 • 来自相关话题

一、使用跳过权限检查的方式运行MySQL服务:
  要使用跳过权限检查的方式运行MySQL服务,有两种方式:1、在mysql服务的配置文件中添加“skip-grant-tables”选项;2、在启动mysql服务的命令后添加“--skip-grant-tables”选项。下面分别讲述两种方法:
 
方法一:修改配置文件的方式:
  修改mysql服务的配置文件“/etc/my.cnf”(根据你的安装方式不同,路径也可能不同),在“[mysqld]”配置组下添加一行“skip-grant-tables”后保存:[mysqld]
...爱E族..aiezu.com..省略N行...
skip-grant-tables保存后,重启“mysqld”服务,这时登录mysql就不再需要密码了。
 
方法二:在启动命令的后添加选项的方式:
  在linux终端运行下面命令,停止并重新以“跳过权限检查”的方式运行mysql服务,登录mysql即可不在需要密码:/etc/init.d/mysql stop
/etc/init.d/mysql start --mysqld --skip-grant-tables
二、设置新密码:
  按上步操作后,在终端运行“mysql -u root”命令,免密码进入mysql的sql命令行,并执行下面SQL语句设置新密码:
use mysql;
--将root密码设置为“aiezu.com”
update user set password=PASSWORD("aiezu.com") where user='root';
flush privileges  注意:在“skip-grant-tables”模式下,不要尝试使用“mysqladmin password”命令修改密码,否则将会报如下错误:[root@aiezu.com ~]# mysqladmin password
New password:
Confirm new password:
mysqladmin:
You cannot use 'password' command as mysqld runs
with grant tables disabled (was started with --skip-grant-tables).
Use: "mysqladmin flush-privileges password '*'" instead 
三、恢复正常启动方式:
  根据第一步的操作不同,选择下面两种方式之一将mysqld服务由“跳过权限检查”模式转换为正常模式,这样就可以使用新设置的密码登录了: 在“my.cnf”配置文件中去掉“skip-grant-tables”,并重启“mysqld”服务;停止mysqld服务,并再启动mysqld服务(去掉“--skip-grant-tables”选项) 
 
[size=14]  本文介绍的是Linux忘记mysql root密码解决方法,如果是Windows忘记了root密码,可以参考:[/size]Windows忘记mysql root密码解决方法 查看全部
一、使用跳过权限检查的方式运行MySQL服务:
  要使用跳过权限检查的方式运行MySQL服务,有两种方式:1、在mysql服务的配置文件中添加“skip-grant-tables”选项;2、在启动mysql服务的命令后添加“--skip-grant-tables”选项。下面分别讲述两种方法:
 
方法一:修改配置文件的方式:
  修改mysql服务的配置文件“/etc/my.cnf”(根据你的安装方式不同,路径也可能不同),在“[mysqld]”配置组下添加一行“skip-grant-tables”后保存:
[mysqld]
...爱E族..aiezu.com..省略N行...
skip-grant-tables
保存后,重启“mysqld”服务,这时登录mysql就不再需要密码了。
 
方法二:在启动命令的后添加选项的方式:
  在linux终端运行下面命令,停止并重新以“跳过权限检查”的方式运行mysql服务,登录mysql即可不在需要密码:
/etc/init.d/mysql stop
/etc/init.d/mysql start --mysqld --skip-grant-tables

二、设置新密码:
  按上步操作后,在终端运行“mysql -u root”命令,免密码进入mysql的sql命令行,并执行下面SQL语句设置新密码:
use mysql;
--将root密码设置为“aiezu.com”
update user set password=PASSWORD("aiezu.com") where user='root';
flush privileges
  注意:在“skip-grant-tables”模式下,不要尝试使用“mysqladmin password”命令修改密码,否则将会报如下错误:
[root@aiezu.com ~]# mysqladmin password
New password:
Confirm new password:
mysqladmin:
You cannot use 'password' command as mysqld runs
with grant tables disabled (was started with --skip-grant-tables).
Use: "mysqladmin flush-privileges password '*'" instead
 
三、恢复正常启动方式:
  根据第一步的操作不同,选择下面两种方式之一将mysqld服务由“跳过权限检查”模式转换为正常模式,这样就可以使用新设置的密码登录了: 
  • 在“my.cnf”配置文件中去掉“skip-grant-tables”,并重启“mysqld”服务;
  • 停止mysqld服务,并再启动mysqld服务(去掉“--skip-grant-tables”选项)
 
 
[size=14]  本文介绍的是Linux忘记mysql root密码解决方法,如果是Windows忘记了root密码,可以参考:[/size]Windows忘记mysql root密码解决方法

让Mysql支持事务处理(修改数据库的存储引擎类型)

Mysqllinyu520 发表了文章 • 0 个评论 • 558 次浏览 • 2016-11-18 10:39 • 来自相关话题

  有时我们发现我们的Mysql数据库使用事务时无效,这很有可能是数据库使用的存储引擎(storage engine)不支持事务处理。比如创建数据库时的默认存储引擎“MyISAM”,它是性能优先的存储引擎,不支持事务处理。这时我们可以按下面步骤处理,解决MySQL不支持事务的问题。
 
一、查看数据库的存储引擎:
1、查看MySQL系统当前使用的存储引擎:
  使用SQL“show variables like '%storage_engine%';”命令,可以查看MySQL系统当前使用的存储引擎:MariaDB [aiezu]> show variables like '%storage_engine%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | MyISAM |
| storage_engine | MyISAM |
+------------------------+--------+
2 rows in set (0.01 sec) 
2、查看指定数据库所有表使用的存储引擎:
  使用“show table status from aiezu;”命令,可以查看指定数据库所有表使用的存储引擎,其中“aiezu”为数据库名。

3、查看指定表的存储引擎:MariaDB [aiezu]> show create table test;
+-------+---------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(36) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
+-------+----------------------------------------------------------+
1 row in set (0.00 sec) 
二、查看当前数据库系统支持的引擎:
  使用show engines;命令可以查看当前数据库支持的引擎:


可以看出,笔者用的MariaDB-5.5.47,支持10种存储引擎,而这10种存储引擎中,只有“InnoDB”、“FEDERATED” 这两种存储引擎支持事务处理。
 
三、修改数据库存储引擎:
1、修改整个系统创建数据表时的默认存储引擎:
  要让以后新建的数据表,默认使用支持事务处理的“InnoDB”做为默认存储引擎,修改“/etc/my.conf”文件,在“[mysqld]”下添加一行“default-storage-engine=InnoDB”,然后重启数据库即可:[mysqld]
...省略N行,爱E族 aiezu.com ...
default-storage-engine=InnoDB 
2、修改已有数据表的存储引擎:
  要将已有的数据表,修改为使用“InnoDB”作为默认存储引擎,通过“alter table”命令:
低版本:mysql> alter table test type=INNODB;
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0高版本:MariaDB [aiezu]> alter table test ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 
四、测试MySQL事务处理:
  MySQL使用BEGIN(或START TRANSACTION)、COMMIT、ROLLBACK三命令分别代表开始一个事务、提交事务、回滚事务,事务简单的流程如下:--回滚的事务
BEGIN;
-- 事务要执行的操作,update、delete等..
ROLLBACK;

--提交的事务
BEGIN;
-- 事务要执行的操作,update、delete等..
COMMIT;
 事务的简单例子:MariaDB [aiezu]> truncate table test;
Query OK, 0 rows affected (0.00 sec)

MariaDB [aiezu]> BEGIN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [aiezu]> insert test(name,url) values('天猫','tmall.com');
Query OK, 1 row affected (0.00 sec)

MariaDB [aiezu]> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

MariaDB [aiezu]> insert test(name,url) values('爱E族','aiezu.com');
Query OK, 1 row affected (0.01 sec)

MariaDB [aiezu]> select * from test;
+----+---------+-----------+
| id | name | url |
+----+---------+-----------+
| 2 | 爱E族 | aiezu.com |
+----+---------+-----------+
1 row in set (0.00 sec)  上面可以看出,插入的站点“天猫”,由于在事务里,事务被回滚,所以插入被撤销。但是它的“id:1”已经被其占用,所以后面成功插入的站点“爱E族”使用的ID是“2”。
 
五、常见错误:
错误一:ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'type=innodb' at line 1解决方法:
  这个错误是当前MySQL版本不支持type方式设置存储引擎的,将type关键词改成ENGINE 即可。alter table test type=INNODB; --低版本
alter table test ENGINE=INNODB; --高版本 查看全部
  有时我们发现我们的Mysql数据库使用事务时无效,这很有可能是数据库使用的存储引擎(storage engine)不支持事务处理。比如创建数据库时的默认存储引擎“MyISAM”,它是性能优先的存储引擎,不支持事务处理。这时我们可以按下面步骤处理,解决MySQL不支持事务的问题。
 
一、查看数据库的存储引擎:
1、查看MySQL系统当前使用的存储引擎:
  使用SQL“show variables like '%storage_engine%';”命令,可以查看MySQL系统当前使用的存储引擎:
MariaDB [aiezu]> show variables like '%storage_engine%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | MyISAM |
| storage_engine | MyISAM |
+------------------------+--------+
2 rows in set (0.01 sec)
 
2、查看指定数据库所有表使用的存储引擎:
  使用“show table status from aiezu;”命令,可以查看指定数据库所有表使用的存储引擎,其中“aiezu”为数据库名。

3、查看指定表的存储引擎:
MariaDB [aiezu]> show create table test;
+-------+---------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(36) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
+-------+----------------------------------------------------------+
1 row in set (0.00 sec)
 
二、查看当前数据库系统支持的引擎:
  使用show engines;命令可以查看当前数据库支持的引擎:
QQ图片20161118104554.png
可以看出,笔者用的MariaDB-5.5.47,支持10种存储引擎,而这10种存储引擎中,只有“InnoDB”、“FEDERATED” 这两种存储引擎支持事务处理。
 
三、修改数据库存储引擎:
1、修改整个系统创建数据表时的默认存储引擎:
  要让以后新建的数据表,默认使用支持事务处理的“InnoDB”做为默认存储引擎,修改“/etc/my.conf”文件,在“[mysqld]”下添加一行“default-storage-engine=InnoDB”,然后重启数据库即可:
[mysqld]
...省略N行,爱E族 aiezu.com ...
default-storage-engine=InnoDB
 
2、修改已有数据表的存储引擎:
  要将已有的数据表,修改为使用“InnoDB”作为默认存储引擎,通过“alter table”命令:
低版本:
mysql> alter table test type=INNODB;
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
高版本:
MariaDB [aiezu]> alter table test ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
四、测试MySQL事务处理:
  MySQL使用BEGIN(或START TRANSACTION)、COMMIT、ROLLBACK三命令分别代表开始一个事务、提交事务、回滚事务,事务简单的流程如下:
--回滚的事务
BEGIN;
-- 事务要执行的操作,update、delete等..
ROLLBACK;

--提交的事务
BEGIN;
-- 事务要执行的操作,update、delete等..
COMMIT;

 事务的简单例子:
MariaDB [aiezu]> truncate table test;
Query OK, 0 rows affected (0.00 sec)

MariaDB [aiezu]> BEGIN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [aiezu]> insert test(name,url) values('天猫','tmall.com');
Query OK, 1 row affected (0.00 sec)

MariaDB [aiezu]> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

MariaDB [aiezu]> insert test(name,url) values('爱E族','aiezu.com');
Query OK, 1 row affected (0.01 sec)

MariaDB [aiezu]> select * from test;
+----+---------+-----------+
| id | name | url |
+----+---------+-----------+
| 2 | 爱E族 | aiezu.com |
+----+---------+-----------+
1 row in set (0.00 sec)
  上面可以看出,插入的站点“天猫”,由于在事务里,事务被回滚,所以插入被撤销。但是它的“id:1”已经被其占用,所以后面成功插入的站点“爱E族”使用的ID是“2”。
 
五、常见错误:
错误一:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'type=innodb' at line 1
解决方法:
  这个错误是当前MySQL版本不支持type方式设置存储引擎的,将type关键词改成ENGINE 即可。
alter table test type=INNODB; --低版本
alter table test ENGINE=INNODB; --高版本

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '***' (2)

Mysqlllslx520 发表了文章 • 0 个评论 • 10547 次浏览 • 2016-11-04 16:50 • 来自相关话题

  有时候,当我们使用“mysql”、“mysqladmin”、“mysqldump”等命令管理数据库时,服务器抛出类似如下错误:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)这个错误是由于什么原因导致的呢?请看后面爱E族(aiezu.com)为您提供的分析。 一、错误现场还原:下面我们通过三种方式来连接,然后观察提示的错误信息:1、直接使用“mysql”命令,不带主机名参数;2、使用带了主机名“localhost”参数的“mysql -h localhost”命令;3、使用带了主机名“127.0.0.1”参数的“mysql -h 127.0.0.1”命令。[root@aiezu.com ~]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@aiezu.com ~]# mysql -h localhost
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@aiezu.com ~]# mysql -h 127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)  通过上面实验可以看出,前面两种方式都能产生标题中的错误,而第三种方式连接是不会产生标题中的错误的(第三种方式这里产生的是由于密码问题拒绝访问的错误信息)(爱E族)。 二、错误产生原因解析:   这是由于我们连接数据库使用的主机名参数为“localhost”,或者未使用主机名参数、服务器默认使用“localhost”做为主机名(爱E族)。 使用主机名参数为“localhost”连接mysql服务端时,mysql客户端会认为是连接本机,所以会尝试以socket文件方式进行连接(socket文件连接方式,比“ip:端口”方式效率更高),这时根据配置文件“/etc/my.cnf”的路径,未找到相应的socket文件,就会引发此错误。
   三、修复故障前准备:1、看mysql服务是否在运行:  由于“socket”文件是由mysql服务运行时创建的,如果提示“ERROR 2002 (HY000): Can't connect to local MySQL server through socket '***' (2)”,找不到“socket”文件,我们首先要确认的是mysql服务是否正在运行。# 1、 端口是否打开
[root@aiezu.com ~]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 12207 mysql 14u IPv4 52350 0t0 TCP *:mysql (LISTEN)

# 2、mysqld服务是否正在运行
[root@aiezu.com ~]# service mysqld status
mysqld (pid 4717) is running...

# 3、如果mariadb,同样方法查服务是否正在运行:
[root@aiezu.com ~]# service mariadb status
Redirecting to /bin/systemctl status mariadb.service
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: active (running) since 四 2016-11-03 13:47:37 CST; 23min ago  2、确定“socket”文件正确位置:  确定mysql服务正常运行后,产生此错误的原因只剩下“socket”文件路径不正确了,我们可以使用“find”命令或者“lsof”命令来确定socket文件的正确路径:[root@aiezu.com ~]# lsof -c mysqld|grep sock$
mysqld 4717 mysql 12u unix 0xffff88010a655b80 0t0 77474827 /storage/db/mysql/mysql.sock

[root@aiezu.com ~]# find / -name '*.sock'
/storage/db/mysql/mysql.sock 四、故障解决方法:解决方案一:  修改“/etc/my.cnf”配置文件,在配置文件中添加“[client]”选项和“[mysql]”选项,并使用这两个选项下的“socket”参数值,与“[mysqld]”选项下的“socket”参数值,指向的socket文件路径完全一致。如下: [mysqld]
datadir=/storage/db/mysql
socket=/storage/db/mysql/mysql.sock
...省略n行(爱E族)...

[client]
default-character-set=utf8
socket=/storage/db/mysql/mysql.sock

[mysql]
default-character-set=utf8
socket=/storage/db/mysql/mysql.sock修改完后,重启mysqld服务,即可解决此问题。
 解决方案二:  使用“ln -s /storage/db/mysql/mysql.sock /var/lib/mysql/mysql.sock”命令,将正确的socket文件位置,软链接到提示错误的socket文件路径位置,即可解决此问题:[root@aiezu.com ~]# ls /var/lib/mysql/mysql.sock
ls: cannot access /var/lib/mysql/mysql.sock: No such file or directory
[root@aiezu.com ~]# ln -s /storage/db/mysql/mysql.sock /var/lib/mysql/mysql.sock
[root@aiezu.com ~]# ls /var/lib/mysql/mysql.sock
/var/lib/mysql/mysql.sock   五、后序:  这篇文章只讲述了mysql提供的“mysql”、“mysqldump”、“mysqladmin”等命令提示“ERROR 2002 (HY000): Can't connect to local MySQL server through socket '***' (2)”的解决方法,如果需要解决“php”、“perl”、“python”等脚本语言提示此错误的问题,请参考爱E族的另外一篇文章:http://aiezu.com/article/mysql_php_connect_through_socket.html 查看全部
  有时候,当我们使用“mysql”、“mysqladmin”、“mysqldump”等命令管理数据库时,服务器抛出类似如下错误:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
这个错误是由于什么原因导致的呢?请看后面爱E族(aiezu.com)为您提供的分析。

 

一、错误现场还原:

下面我们通过三种方式来连接,然后观察提示的错误信息:
  • 1、直接使用“mysql”命令,不带主机名参数;
  • 2、使用带了主机名“localhost”参数的“mysql -h localhost”命令;
  • 3、使用带了主机名“127.0.0.1”参数的“mysql -h 127.0.0.1”命令。
[root@aiezu.com ~]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@aiezu.com ~]# mysql -h localhost
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@aiezu.com ~]# mysql -h 127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
  通过上面实验可以看出,前面两种方式都能产生标题中的错误,而第三种方式连接是不会产生标题中的错误的(第三种方式这里产生的是由于密码问题拒绝访问的错误信息)(爱E族)。

 

二、错误产生原因解析:

   这是由于我们连接数据库使用的主机名参数为“localhost”,或者未使用主机名参数、服务器默认使用“localhost”做为主机名(爱E族)。 使用主机名参数为“localhost”连接mysql服务端时,mysql客户端会认为是连接本机,所以会尝试以socket文件方式进行连接(socket文件连接方式,比“ip:端口”方式效率更高),这时根据配置文件“/etc/my.cnf”的路径,未找到相应的socket文件,就会引发此错误。
   

三、修复故障前准备:

1、看mysql服务是否在运行:

  由于“socket”文件是由mysql服务运行时创建的,如果提示“ERROR 2002 (HY000): Can't connect to local MySQL server through socket '***' (2)”,找不到“socket”文件,我们首先要确认的是mysql服务是否正在运行。
# 1、 端口是否打开
[root@aiezu.com ~]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 12207 mysql 14u IPv4 52350 0t0 TCP *:mysql (LISTEN)

# 2、mysqld服务是否正在运行
[root@aiezu.com ~]# service mysqld status
mysqld (pid 4717) is running...

# 3、如果mariadb,同样方法查服务是否正在运行:
[root@aiezu.com ~]# service mariadb status
Redirecting to /bin/systemctl status mariadb.service
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: active (running) since 四 2016-11-03 13:47:37 CST; 23min ago
  

2、确定“socket”文件正确位置:

  确定mysql服务正常运行后,产生此错误的原因只剩下“socket”文件路径不正确了,我们可以使用“find”命令或者“lsof”命令来确定socket文件的正确路径:
[root@aiezu.com ~]# lsof  -c mysqld|grep sock$
mysqld 4717 mysql 12u unix 0xffff88010a655b80 0t0 77474827 /storage/db/mysql/mysql.sock

[root@aiezu.com ~]# find / -name '*.sock'
/storage/db/mysql/mysql.sock
 

四、故障解决方法:

解决方案一:

  修改“/etc/my.cnf”配置文件,在配置文件中添加“[client]”选项和“[mysql]”选项,并使用这两个选项下的“socket”参数值,与“[mysqld]”选项下的“socket”参数值,指向的socket文件路径完全一致。如下: 
[mysqld]
datadir=/storage/db/mysql
socket=/storage/db/mysql/mysql.sock
...省略n行(爱E族)...

[client]
default-character-set=utf8
socket=/storage/db/mysql/mysql.sock

[mysql]
default-character-set=utf8
socket=/storage/db/mysql/mysql.sock
修改完后,重启mysqld服务,即可解决此问题。
 

解决方案二:

  使用“ln -s /storage/db/mysql/mysql.sock /var/lib/mysql/mysql.sock”命令,将正确的socket文件位置,软链接到提示错误的socket文件路径位置,即可解决此问题:
[root@aiezu.com ~]# ls /var/lib/mysql/mysql.sock
ls: cannot access /var/lib/mysql/mysql.sock: No such file or directory
[root@aiezu.com ~]# ln -s /storage/db/mysql/mysql.sock /var/lib/mysql/mysql.sock
[root@aiezu.com ~]# ls /var/lib/mysql/mysql.sock
/var/lib/mysql/mysql.sock

  

 五、后序:

  这篇文章只讲述了mysql提供的“mysql”、“mysqldump”、“mysqladmin”等命令提示“ERROR 2002 (HY000): Can't connect to local MySQL server through socket '***' (2)”的解决方法,如果需要解决“php”、“perl”、“python”等脚本语言提示此错误的问题,请参考爱E族的另外一篇文章:http://aiezu.com/article/mysql_php_connect_through_socket.html