[乐意黎原创]MySQL Error: SQLSTATE[HY000] [2002] Connection refused及server quit without updating PID(持续更新)

MySQL Error: SQLSTATE[HY000] [2002] Connection refused

解决:

 /usr/local/mysql/bin/mysqld_safe --user=mysql & (启动mysql)

killall mysqld -9(重启mysql)

killall mysqld(关闭mysql)

-----------------------------------------------------------------------------

[root@aerchi.com ~]# systemctl stop mysqld
[root@aerchi.com ~]# /usr/local/mysql/bin/mysqld_safe --user=root &
[1] 22090
[root@aerchi.com ~]# 2018-06-25T10:45:52.106908Z mysqld_safe Logging to '/usr/local/mysql/logs/mysqld.log'.

2018-06-25T10:45:52.126600Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@aerchi.com ~]# killall mysqld -9
[root@aerchi.com ~]# /usr/local/mysql/bin/mysqld_safe: line 198: 22561 Killed                  nohup /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=root --log-error=/usr/local/mysql/logs/mysqld.log --pid-file=/var/run/mysqld.pid < /dev/null > /dev/null 2>&1
2018-06-25T10:51:44.105560Z mysqld_safe Number of processes running now: 0
2018-06-25T10:51:44.110274Z mysqld_safe mysqld restarted

-----------------------------------------------------------------------------


另:
/usr/local/mysql/bin/mysqld_safe: line 198: 23470 Killed            nohup /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=root --log-error=/usr/local/mysql/logs/mysqld.log --pid-file=/var/run/mysqld.pid < /dev/null > /dev/null 2>&1 > /dev/null 2>&1 > /dev/null 2>&1 > /dev/null 2>&1 > /dev/null 2>&1 > /dev/null 2>&1 > /dev/null 2>&1 > /dev/null 2>&1 > /dev/null 2>&1
2018-06-25T11:13:00.053559Z mysqld_safe Number of processes running now: 0
2018-06-25T11:13:00.142445Z mysqld_safe mysqld restarted

解决方法:

vi /etc/security/limits.conf

*      soft    nproc           65535
*      hard    nproc           65535
*      soft    nofile          65535

*      hard    nofile          65535

之后重启服务器。

[root@aerchi.com ~]# mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

-----------------------------------------------------------------------------

根据重启mysql 反馈的问题:

第一:查询是否 存在mysql 僵尸进程

#ps -ef |grep mysql*

关闭相关的mysql 僵尸进程。killall mysqld(关闭mysql)

-----------------------------------------------------------------------------
 systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (exited) since Mon 2018-06-25 19:18:36 CST; 17h ago
     Docs: man:systemd-sysv-generator(8)


service mysqld restart

MySQL server PID file could not be found!                  [FAILED]

Starting MySQL.The server quit without updating PID file (/[FAILED]l/mysql/data/i***r6Z.pid).

解决方法:

1. 修改 MySQL的my.cnf 文件
vim /etc/my.cnf

[mysqld]
  2 #datadir=/var/lib/mysql
  3 #socket=/var/lib/mysql/mysql.sock
  4 # Disabling symbolic-links is recommended to prevent assorted security risks
  5 #symbolic-links=0
  6 # Settings user and group are ignored when systemd is used.
  7 # If you need to run mysqld under a different user or group,
  8 # customize your systemd unit file for mariadb according to the
  9 # instructions in http://fedoraproject.org/wiki/Systemd
 10
 11 datadir = /usr/local/mysql/data
 12
 13 [mysqld_safe]
 14 log-error=/usr/local/mysql/logs/mysqld.log
 15 #pid-file=/var/run/mysqld.pid
 16 pid-file=/usr/local/mysql/mysqld.pid
 17
 18 #
 19 # include all files from the config directory
 20 #
 21 #!includedir /etc/my.cnf.d
 22 max_connections = 2000

 23 max_user_connections = 1900

 24 max_connect_errors = 10000

 25 max_allowed_packet = 100M

 26 #实际是去除NO_ZERO_IN_DATE,NO_ZER  O_DATE
 27 disable-partion-engine-check = 1
 28 sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 29 table_open_cache = 8000
 30 connect_timeout = 20
 31 wait_timeout=2880000
 32 interactive_timeout=2880000
 33 # aerchi.com
 
 34 innodb_buffer_pool_size = 128M

 35 key_buffer_size = 128M

----------------------------------------

open your my.ini/my.cnf configuration file
under innodb_lock_wait_timeout = 50 add: innodb_flush_method=normal
restart MySql
….enjoy!
-----------------------------------------------------------------------------

 2. 移除 MySQL配置文件 /etc/my.cnf 注意备份

移除 my.cnf文件,注意备份

备份: mv /etc/my.cnf /etc/my.cnf.backup

重启: service mysqld start

Starting MySQL                                             [  OK  ]
[root@*6Z ~]# 2018-06-29T02:12:15.787884Z mysqld_safe A mysqld process already exists

-----------------------------------------------------------------------------

以上内容,

------------------------------------------------------------------------------------------

当抛错时,应该查看MySQL的日志,

比如错误信息:2018-06-28T04:40:26.338321Z 0 [Note] InnoDB: Using CPU crc32 instructions

2018-06-28T04:40:26.339820Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M2018-06-28T04:40:26.339855Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12

2018-06-28T04:40:26.340077Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool

请修改my.cnf, 添加

 34 innodb_buffer_pool_size = 128M

 35 key_buffer_size = 128M

-----------------------------------------------------------------------------

比如错误信息:

Starting MySQL.The server quit without updating PID file (/usr/local/mysql/data/***6Z.pid). 

在my.cnf配置文件中的 [mysqld_safe] 下添加

 log-error = /apps/logs/mysql/error.log      # 表示mysql服务启动日志文件的位置,路径可自己更改

 最后发现根源是操作系统未安全关机,MySQL未能安全关闭,而系统表是用myisam引擎的,myisam不支持崩溃修复损坏,然后导致系统表损坏,而启动又检查myisam-recover,无法recover就无法启动了。 

提示: MySQL启动时出现:Starting MySQL.The server quit without updating PID file (/usr/local/mysql/data/***6Z.pid). 

请注意重启(reboot)即可解决。

-----------------------------------------------------------------------------

Q问题: Starting MySQL.2018-07-02T02:33:49.152186Z mysqld_safe error: log-error set to '/usr/local/mysql/logs/mysqld.log', however file don't exists. Create writable for user 'mysql'.

A解决方法:   touch /usr/local/mysql/logs/mysqld.log  即可

-----------------------------------------------------------------------------

Q问题:当启动时: service mysqld startStarting MySQL...The server quit without updating PID file (/usr/local/mysql/data/***6Z.pid). 

查看日志: 

2018-07-02T02:37:02.560138Z 0 [Note] Server socket created on IP: '::'.

2018-07-02T02:37:02.560178Z 0 [ERROR] Could not open unix socket lock file /tmp/mysql.sock.lock.

2018-07-02T02:37:02.560182Z 0 [ERROR] Unable to setup unix socket lock file.

如图找到路径

在日志文件 /mysql/data/**6Z.err 中可以看到具体的ERROR 信息:Could not create unix socket lock file /tmp/mysql.sock.lock。

这种错误一般都是目录不存在或者权限不足,所以我们直接使用命令 mkdir -p /tmp/ 创建该目录即可,然后可以设置目录权限 chown -R mysql:mysql /tmp/mysql.sock.lock 。

A解决方法:  chown -R mysql:mysql /tmp/mysql.sock.lock

-----------------------------------------------------------------------------                           

MySQL 日志中出现警告 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

在5.7中,通过ssl方式连接mysql被作为默认和推荐。如果你并不需要(比如web端和db端同一台服务器,db服务器处于web端内网等),就可以不用使用ssl方式。干掉警告很简单,只要在my.cnf声明跳过ssl就行了。

skip-ssl

-----------------------------------------------------------------------------

# systemctl restart mysqld

Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

-----------------------------------------------------------------------------

# systemctl status mysqld.service -l

● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: failed (Result: exit-code) since Thu 2018-06-28 17:01:27 CST; 1min 19s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 2576 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=1/FAILURE)


Jun 28 17:01:26 *6Z systemd[1]: Starting LSB: start and stop MySQL...
Jun 28 17:01:27 *6Z mysqld[2576]: Starting MySQL.The server quit without updating PID file (/usr/local/mysql/data/*6Z.pid).[FAILED]
Jun 28 17:01:27 *6Z systemd[1]: mysqld.service: control process exited, code=exited status=1


Jun 28 17:01:27 *6Z systemd[1]: Failed to start LSB: start and stop MySQL.
Jun 28 17:01:27 *6Z systemd[1]: Unit mysqld.service entered failed state.
Jun 28 17:01:27 *6Z systemd[1]: mysqld.service failed.

解决方法:

可能是/usr/local/mysql/data/mysql.pid文件没有写的权限

解决方法 :给予权限,执行 “chown -R mysql:mysql /var/data” “chmod -R 755 /usr/local/mysql/data”  然后重新启动mysqld!
-----------------------------------------------------------------------------

 #/etc/init.d/mysqld status
MySQL is not running, but lock file (/var/lock/subsys/mysql[FAILED]


解决命令:

rm -rf /var/lock/subsys/mysql

-----------------------------------------------------------------

ervice mysqld restart
MySQL server PID file could not be found!                  [FAILED]
Starting MySQL.2018-06-29T06:52:45.424469Z mysqld_safe error: log-error set to '/usr/local/mysql/logs/mysqld.log', however file don't exists. Create writable for user 'mysql'.

The server quit without updating PID file (/usr/local/mysql/data/iZwz99mho1vp798njmvvr6Z.pid).                                             [FAILED]

[root@*6Z ~]# touch /usr/local/mysql/logs/mysqld.log

[root@*6Z ~]# chown -R mysql:mysql /usr/local/mysql/logs/

-----------------------------------------------------------------------------

[root@*6Z ~]#touch /usr/local/mysql/data/mysqld.pid
[root@*6Z ~]# touch /usr/local/mysql/data/mysqld.pid
[root@*6Z ~]# service mysqld restart

--------------------------------------------------------------------------------

 service mysqld start
Starting MySQL                                             [  OK  ]
# 2018-06-29T15:12:57.189830Z mysqld_safe A mysqld process already exists

ps -ef|grep mysql
kill -9 mysql进程

service mysqld start
Starting MySQL                                             [  OK  ]

当出现 MySQL server PID file could not be found! , 请尝试做好my.cnf 备份并删除。

cp /etc/my.cnf~ /etc/my.cnf
rm -rf /etc/my.cnf

--------------------------------------------------------------------------------

重新启动  systemctl restart mysqld    / service mysqld start 

查看进程 ps -ef |grep mysql
root      6343     1  0 14:00 pts/1    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/***6Z.pid
mysql     6600  6343  1 14:00 pts/1    00:00:10 /usr/local/mysql/bin/mysqld .....data/***6Z.pid
root      7559  5728  0 14:13 pts/1    00:00:00 grep --color=auto mysql
-----------------------------------------------------------------------------

再次查看日志正常了


乐意黎总结: 出了问题一定要注意仔细查看错误日志的 [ERROR]级别信息,比如我的日志路径为: /mysql/logs/mysqld.log 及 /mysql/data/**6Z.err , 再想办法解决。我是耽搁了太多,心一慌,忘记了仔细查看日志,没有针对性性的去请教或是查看解决方法。



乐意黎
2018-06-28
发布了430 篇原创文章 · 获赞 415 · 访问量 925万+
展开阅读全文

某些查询出现PDO / MySQL错误(SQLSTATE [HY000]

04-05

<div class="post-text" itemprop="text"> <p>In this snip-it of PHP code, I'm trying to access my database (dbconn is already defined)</p> <pre><code>public function get($statement) { echo $statement . '<br/>'; $fetch = $this->dbconn->prepare($statement); $fetch->execute(); $res = $fetch->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP); return $res; } </code></pre> <p>Some example outputs of the whole program are, with the query = "example1"</p> <pre><code>SELECT * FROM keywords WHERE 1 SELECT bin FROM bins WHERE place LIKE 'example1' {"centralLat":0,"centralLon":0,"errors":[],"posts":[],"location":"example1"} </code></pre> <p>The code returns the intersection of posts a location (that has been hashed out into bins on a map) and the keywords. The code works fine, it does interpretation on the string and does the appropriate querys, finds intersections, yadda yadda. The problem is for some queries, PDO fails to execute the query</p> <p>in this case of query = "example2"</p> <pre><code>SELECT * FROM keywords WHERE 1 SELECT bin FROM bins WHERE place LIKE 'example2' (this the the query that errors) Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: Invalid column index' </code></pre> <p>In the actual cases, it seems to consistently throw out these errors on SOME queries, but not all. For example: it will always run example1 successfully, but never run example2 successfully. But if I run either of the querys on phpMyAdmin, I cannot replicate the errors, and get the desired results. </p> <p>The only notable difference, between example1 (and the like) and example2 (and the like) is that querys that work are LONGER (40+ characters), while queries that fail are SHORTER (39-)</p> <p>Im not sure whats causing this error, any help would be appreciated.</p> </div> 问答

SQLSTATE [HY000]:一般错误:21个错误参数或其他API滥用 - 在pyrocms中

01-22

<div class="post-text" itemprop="text"> <p>I'm developing a web site on pyrocms and it worked fine in my laptop (WAMP environment). But it gives me error <code>SQLSTATE[HY000]: General error: 21 bad parameter or other API misuse</code> when I try this in my ubuntu server.</p> <p>Server's PHP version - 7.2.13</p> <p>I found <a href="https://github.com/pyrocms/pyrocms/issues/4867" rel="nofollow noreferrer">this</a> issue in github but I'm not clear what they suggesting. </p> <p>My form's code</p> <pre><code>{% set company_types = {} %} {% set form = form({ 'ajax': true, 'options': { 'redirect': '/merchants/mobile/pin/get', }, 'handler': 'Ksn\\CompanyModule\\Company\\Form\\CompanyFormHandler@handle', 'fields': { 'name':{ 'type':'text', 'required':true, 'label': 'Comapany Name' } . . . . </code></pre> <p>and my form handlers code is this.</p> <pre><code><?php namespace Ksn\CompanyModule\Company\Form; use Anomaly\UsersModule\User\UserRepository; use Illuminate\Contracts\Auth\Guard; use Illuminate\Foundation\Bus\DispatchesJobs; use Anomaly\Streams\Platform\Ui\Form\FormBuilder; use Ksn\CompanyModule\Company\Contract\CompanyRepositoryInterface; class CompanyFormHandler { use DispatchesJobs; public function handle( Guard $auth, UserRepository $users, FormBuilder $builder, CompanyRepositoryInterface $companyRepository ) { if(!$user = $auth->user()) { abort(404); } $values = $builder->getFormValues(); $company_arr = []; foreach ($values as $key => $value) { if($key == 'mobile') { $user->telephone = $value; continue; } $company_arr[$key] = $value; } $company = $companyRepository->newInstance($company_arr); $company->save(); $user->company_id = $company->getAttribute('id'); // generate random pin $digits = 4; $user->mobileverificationpin = rand(pow(10, $digits-1), pow(10, $digits)-1); $user->status = 4; $user->save(); //dd($company->getAttribute('id')); return $builder->getFormResponse(); } } </code></pre> <p>As my observations, error occurred only on data insertions.</p> <p><a href="https://i.stack.imgur.com/b4yLe.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/b4yLe.jpg" alt="enter image description here"></a></p> <p>as I highlighted above in red, it gives my desired response after series of error messages btw. Is anyone know how to fix this? Thanks</p> </div> 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览