欢迎光临
我们每日更新

Linux下常见mysql故障实例总结及解决方法

linux系统下,我们搭建网站时,常常使用mysql数据库,但是在使用过程中,会遇到这样或者那样的问题,本篇教程,我们精选了几个mysql使用过程中会出现的故障,并给出解决方法,下面我们来具体看看。

1.1 报错

[[email protected] mysql]#/etc/init.d/mysql restart

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

Starting MySQL………………………………………..

解决

[[email protected] mysql]#ps -ef|grep mysql

root 4312 1 0 2016 ? 00:00:00 /bin/sh /disk1/mysql/bin/mysqld_safe –datadir=/disk1/mysql_data/ –pid-file=/disk1/mysql_data//b23.pid

mysql 7083 4312 2 Aug04 ? 03:23:59 /disk1/mysql/bin/mysqld –basedir=/disk1/mysql –datadir=/disk1/mysql_data/ –plugin-dir=/disk1/mysql/lib/plugin –user=mysql –loql_data//b23.err –pid-file=/disk1/mysql_data//b23.pid –socket=/disk1/mysql/mysql.sock –port=3306

root 17697 12436 0 14:56 pts/2 00:00:00 grep –color=auto mysql

如果看到上面的内容,那说明,Mysql的进程卡死了,这时用就要把这些卡死的进程都关闭

[[email protected] mysql]#kill 4312

[[email protected] mysql]#kill 7083

[[email protected] mysql]#/etc/init.d/mysql start

Starting MySQL…………………… [ OK ]

[[email protected] mysql]#/etc/init.d/mysql status

MySQL running (18926) [ OK ]

[[email protected] mysql]#ps -ef|grep mysql

[[email protected] ~]#ss -lntup|grep 330*

1.2 报错

[[email protected] ~]# mysql -uroot -p -h 192.168.241.48

Enter password:

ERROR 1129 (HY000): Host \’192.168.241.40\’ is blocked because of many connection errors; unblock with \’mysqladmin flush-hosts\’

解决:

由于mysql数据库的参数:max_connect_errors(系统默认10) mysqld已经得到了大量(max_connect_errors)的主机’hostname’的在中途被中断了的连接请求累计超过10次,就再也无法连接上mysqld服务,同一个ip在短时间内产生太多中断的数据库连接而导致的阻塞(超过mysql数据库max_connection_errors的最大值)

解决方法如下:

1.使用mysqladmin flush-hosts命令清除缓存,命令执行方法如下:

[[email protected] ~]# mysqladmin -uroot -p -h 192.168.241.48 flush-hosts

Enter password:

2.修改mysql配置文件,在[mysqld]下面添加 max_connect_errors=1000,然后重启mysql.

1.3 报错

[[email protected] ~]# /etc/init.d/mysqld status
Warning: World-writable config file \’/disk1/mysql/my.cnf\’ is ignored
SUCCESS! MySQL running (1845)
[[email protected] ~]# ls -ld /disk1/mysql/my.cnf
-rwxrwxrwx. 1 mysql mysql 5641 Jan 24 2016 /disk1/mysql/my.cnf

解决:

[[email protected] mysql]# chmod 644 my.cnf
[[email protected] mysql]# /etc/init.d/mysqld status
SUCCESS! MySQL running (1845)
[[email protected] mysql]#

1.4 报错

解决:

1)从告警信息,首先排除不是mysql问题,应该是外部环境引起的,例如:网络、硬件故障等
2)IO thread每次启动io_thread从主库拉binlog是都有以下逻辑,(handle_slave_io-> get_master_version_and_clock)检查serverid是否重复,时间钟,时区,字符集,设置master heartbeat等. 此错误是在检查serverid时发生网路中断所致。

处理方法:

stop slave; start slave; 即可恢复

1.5 报错 mysql 主从复制代码:1054

查看主从库表结构

解决:

1、stop slave;
2、在从库加上缺失的字段
alter table alert_post add type varchar(255) DEFAULT \’ \’;
3、start slave;

1.6 报错 代码 1032

错误日志

mysql 1032错误,一般是指要更改的数据不存在,造成同步失败。

解决:

法一:跳过错误
stop salve;
set global sql_slave_skip_counter=1;
start slave;

法二: 跳过所有1032 错误
更改 my.cnf 文件
slave-skip-errors = 1032
重启数据库
start slave;

1.7 报错

查看报错日志

根据报错解决问题
解决:

1.8 mysql从库 报错1032

参考博客:https://www.52os.net/articles/injecting-empty-transactions-repair-mysql-5-6-gtid-replication.html

主库:
mysql> show master status\G
*************************** 1. row ***************************
File: master-bin.000001
Position: 68478641
Binlog_Do_DB: bsppr,coopinion,pm,topic
Binlog_Ignore_DB:
Executed_Gtid_Set: 1f946f67-8c8d-11e6-a75e-5a485dfacc12:1-8783 #主库的事务id是1-8783
1 row in set (0.09 sec)

从库报错:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.241.32
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 29686324
Relay_Log_File: b34-relay-bin.000002
Relay_Log_Pos: 26712955
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table bsppr.meta_list; Can\’t find record in \’meta_list\’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event\’s master log master-bin.000001, end_log_pos 26713356
Skip_Counter: 0
Exec_Master_Log_Pos: 26712743
Relay_Log_Space: 29686738
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table bsppr.meta_list; Can\’t find record in \’meta_list\’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event\’s master log master-bin.000001, end_log_pos 26713356
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 1f946f67-8c8d-11e6-a75e-5a485dfacc12
Master_Info_File: /disk3/mysql_data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180417 23:09:10
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 1f946f67-8c8d-11e6-a75e-5a485dfacc12:1-1899 #记录relay日志从master获取了binlog日志的位置
Executed_Gtid_Set: 1f946f67-8c8d-11e6-a75e-5a485dfacc12:1-1741 #记录本机执行的binlog日志位置,从机上该项中包括主机和从机的binlog日志位置
Auto_Position: 1
1 row in set (0.00 sec)

解决:

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> set global sql_slave_skip_counter=1; #跳过1个事务(使用gtid进行主从复制,如果发生错误,这个办法是不能用的)
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

mysql> SET GTID_NEXT=”1f946f67-8c8d-11e6-a75e-5a485dfacc12:1742″; 第1742事务出现问题,我们插入空事务,跳过该错误。
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;COMMIT;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SET GTID_NEXT=”AUTOMATIC”;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.241.32
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 115145091
Relay_Log_File: b34-relay-bin.000003
Relay_Log_Pos: 19614608
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 54376946
Relay_Log_Space: 115145805
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 858
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 1f946f67-8c8d-11e6-a75e-5a485dfacc12
Master_Info_File: /disk3/mysql_data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: update
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 1f946f67-8c8d-11e6-a75e-5a485dfacc12:1-14000
Executed_Gtid_Set: 1f946f67-8c8d-11e6-a75e-5a485dfacc12:1-6825
Auto_Position: 1
1 row in set (0.00 sec)

1.9、报错

[[email protected] mysql]# service mysql start
Starting MySQL…[ERROR] The server quit without updating PID file (/disk1/mysql_data/mysqld.pid)

解决:

/disk1/mysql/bin/mysqld_safe –defaults-file=/disk1/mysql/my.cnf

关于Linux下常见mysql故障实例总结及解决方法的教程就讲解到这里,如果对于你有帮助,请分享站长技术网(www.adminmm.com)

赞(0)
未经允许不得转载:站长技术网 » Linux下常见mysql故障实例总结及解决方法
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址