Chinaunix首页 | 论坛 | 博客
  • 博客访问: 3043985
  • 博文数量: 206
  • 博客积分: 3409
  • 博客等级: 中校
  • 技术积分: 4066
  • 用 户 组: 普通用户
  • 注册时间: 2010-12-24 10:21
个人简介

● ITPUB名人堂嘉宾 ● ChinaUnix社区博客专家 ● ChinaUnix社区Oracle板块版主 ● 优酷网认证音乐牛人:EricGuitar ● SDOUG 核心成员 ●E-mail:gaoqiangdba@163.com

文章分类

全部博文(206)

文章存档

2021年(11)

2020年(7)

2019年(7)

2016年(5)

2015年(36)

2014年(23)

2013年(15)

2012年(23)

2011年(61)

2010年(18)

分类: Mysql/postgreSQL

2015-04-06 14:45:02

  

错误信息:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111 "Connection refused")

该错误可能由很多原因引发,因此我们需要耐心的寻找蛛丝马迹。


处理过程:

查看一下hostname.err文件:
150404 22:48:57 mysqld_safe The server is respawning too fast. Sleeping for 1 second.
150404 22:48:58 mysqld_safe Number of processes running now: 0
150404 22:48:58 mysqld_safe mysqld restarted
150404 22:48:58 [Note] InnoDB: Using mutexes to ref count buffer pool pages
150404 22:48:58 [Note] InnoDB: The InnoDB memory heap is disabled
150404 22:48:58 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
150404 22:48:58 [Note] InnoDB: Memory barrier is not used
150404 22:48:58 [Note] InnoDB: Compressed tables use zlib 1.2.3
150404 22:48:58 [Note] InnoDB: Using Linux native AIO
150404 22:48:58 [Note] InnoDB: Using CPU crc32 instructions
150404 22:48:58 [Note] InnoDB: Initializing buffer pool, size = 128.0M
150404 22:48:58 [Note] InnoDB: Completed initialization of buffer pool
150404 22:48:58 [Note] InnoDB: Highest supported file format is Barracuda.
150404 22:48:58 [Note] InnoDB: 128 rollback segment(s) are active.
150404 22:48:58 [Note] InnoDB: Waiting for purge to start
150404 22:48:58 [Note] InnoDB:  Percona XtraDB () 5.6.22-72.0 started; log sequence number 1681155
150404 22:48:58 [Note] Plugin 'FEEDBACK' is disabled.
150404 22:48:58 [Note] Server socket created on IP: '::'.
150404 22:48:58 [ERROR] mysqld: Error writing file '/var/lib/mysql/localhost.localdomain.pid' (Errcode: 28 "No space left on device")
150404 22:48:58150404 22:48:58 mysqld_safe Number of processes running now: 0
150404 22:48:58 mysqld_safe mysqld restarted
150404 22:48:58 [Note] InnoDB: Using mutexes to ref count buffer pool pages
150404 22:48:58 [Note] InnoDB: The InnoDB memory heap is disabled
150404 22:48:58 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
150404 22:48:58 [Note] InnoDB: Memory barrier is not used
150404 22:48:58 [Note] InnoDB: Compressed tables use zlib 1.2.3
150404 22:48:58 [Note] InnoDB: Using Linux native AIO
150404 22:48:58 [Note] InnoDB: Using CPU crc32 instructions
150404 22:48:58 [Note] InnoDB: Initializing buffer pool, size = 128.0M
150404 22:48:58 [Note] InnoDB: Completed initialization of buffer pool
150404 22:48:58 [Note] InnoDB: Highest supported file format is Barracuda.
150404 22:48:58 [Note] InnoDB: 128 rollback segment(s) are active.
150404 22:48:58 [Note] InnoDB: Waiting for purge to start
150404 22:48:58 [Note] InnoDB:  Percona XtraDB () 5.6.22-72.0 started; log sequence number 1681155
150404 22:48:58 [Note] Plugin 'FEEDBACK' is disabled.
150404 22:48:58 [Note] Server socket created on IP: '::'.
150404 22:48:58 [ERROR] mysqld: Error writing file '/var/lib/mysql/localhost.localdomain.pid' (Errcode: 28 "No space left on device")
150404 22:48:58 [ERROR] Can't start server: can't create PID file: No space left on device



根据以上红色部分我们可以推断该问题的原因很可能跟存储空间有关,因此李科查看一下空间:



[root@localhost ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2        11G  9.9G  9.0M 100% /                -----果然空间满了
tmpfs           1.9G     0  1.9G   0% /dev/shm
/dev/sda1       291M   39M  238M  14% /boot


[root@localhost /]# cd pgarchlog     ---初步判定可能是由于在同一台机器上的PostgreSQL的归档日志导致的
[root@localhost pgarchlog]# ls
music
[root@localhost pgarchlog]# du -sm
5694 .                                         ----10G的空间,PostgreSQL的归档日志占了5.6G



清理PostgreSQL的归档日志:
[root@localhost music]# rm -fr  00000003*
[root@localhost music]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2        11G  4.5G  5.4G  46% /                 ---------可以看到空间释放了
tmpfs           1.9G     0  1.9G   0% /dev/shm
/dev/sda1       291M   39M  238M  14% /boot


此时发现hostname.err里面还有一些报错信息:




[root@localhost music]# tail -f /var/lib/mysql/localhost.localdomain.err
150404 22:54:32 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:32 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:33 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:33 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:34 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:34 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:35 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:35 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:36 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:36 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:37 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:37 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:38 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:38 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:39 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:39 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150404 22:54:40 [Note] InnoDB: Using mutexes to ref count buffer pool pages
150404 22:54:40 [Note] InnoDB: The InnoDB memory heap is disabled
150404 22:54:40 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
150404 22:54:40 [Note] InnoDB: Memory barrier is not used
150404 22:54:40 [Note] InnoDB: Compressed tables use zlib 1.2.3
150404 22:54:40 [Note] InnoDB: Using Linux native AIO
150404 22:54:40 [Note] InnoDB: Using CPU crc32 instructions
150404 22:54:40 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:40 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:40 [Note] InnoDB: Initializing buffer pool, size = 128.0M
150404 22:54:40 [Note] InnoDB: Completed initialization of buffer pool
150404 22:54:40 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:40 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:40 [Note] InnoDB: Retrying to lock the first data file
150404 22:54:41 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:41 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:41 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:41 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:42 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:42 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:42 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:42 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:43 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:43 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:43 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:43 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:44 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:44 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
150404 22:54:44 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
150404 22:54:44 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.




此时MySQL的启动和关闭还有一些小问题:


[root@localhost ~]# service mysql start
Starting MySQL SUCCESS! 
[root@localhost ~]# service mysql stop
 ERROR! MySQL server PID file could not be found!
[root@localhost ~]# service mysql start
Starting MySQL SUCCESS! 


结合刚才的err日志看,应该是有进程打瞌睡没听到指令也没释放资源,就“杀”了他吧... ...



[root@localhost /]# ps -ef|grep mysql
root      41597      1  1 22:44 pts/1    00:00:17 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.localdomain.pid
mysql     94579  41597  0 22:52 pts/1    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/localhost.localdomain.err --pid-file=/var/lib/mysql/localhost.localdomain.pid
root      94820  44278  0 22:55 pts/0    00:00:00 mysql
root      94865  58733  0 23:01 pts/1    00:00:00 grep mysql
[root@localhost /]# service mysql stop
 ERROR! MySQL server PID file could not be found!




[root@localhost /]# kill -9 41597
[root@localhost /]# kill -9 94579
[root@localhost /]# kill -9 94820
[root@localhost /]# ps -ef|grep mysql
root      94892  58733  1 23:03 pts/1    00:00:00 grep mysql
[root@localhost /]# service mysql stop
 ERROR! MySQL server PID file could not be found!
[root@localhost /]# service mysql start
Starting MySQL. SUCCESS! 
[root@localhost /]# service mysql stop
Shutting down MySQL... SUCCESS! 
[root@localhost /]# service mysql start
Starting MySQL. SUCCESS! 


经过几次重启,数据库终于恢复正常!~~~


验证一下数据库:

查看当前登录用户:


MariaDB [(none)]> select current_user();
+----------------+
| current_user() |
+----------------+
| eric@localhost |
+----------------+
1 row in set (0.01 sec)


登陆MUSIC数据库看看:
[root@localhost ~]# mysql -u eric -D music
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 30
Server version: 10.0.17-MariaDB MariaDB Server


Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


MariaDB [music]> insert into summary values(5,'Eric Gao is a PostgreSQL DBA');
Query OK, 1 row affected (0.01 sec)



MariaDB [music]> update summary set id=3 where info like '%PostgreSQL%';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0



MariaDB [music]> update summary set id=5 where info like '%AIX%';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0




MariaDB [music]> select * from summary order by id;
+------+------------------------------+
| id   | info                         |
+------+------------------------------+
|    1 | Eric Gao is a Oracle DBA     |
|    2 | Eric Gao is a MySQL DBA      |
|    3 | Eric Gao is a PostgreSQL DBA |
|    4 | Eric Gao is a Linux  SA      |
|    5 | Eric Gao is a AIX  SA        |
+------+------------------------------+
5 rows in set (0.00 sec)

OK,数据可访问,也可插入新的数据!~~~





阅读(13545) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~