5个经典 MySQL 数据库运维案例 - SEO - 新闻资讯 - 10个经典 MySQL 数据库运维案例
新闻资讯

5个经典 MySQL 数据库运维案例

发布时间:2021-01-18 浏览次数:38

今天,爱可生小编将列出MySQL数据库中十大经典的错误案例,以及处理问题的解决方案和方法。希望能给新人或者数据库爱好者一些帮助,以后有任何错误我们都能从容处理。

65587da09fcb87a9

学习任何技术,其实都是一个自我修养的过程。趴下,试着拥抱数据的世界!情况1:连接太多(连接太多,导致连接到数据库,业务无法正常进行)


问题还原:


mysql> show variables like ‘%max_connection%’;


| Variable_name | Value |


max_connections | 151 |


mysql> set global max_connections=1;Query OK, 0 rows affected (0.00 sec)


[root@node4 ~]# mysql -uzs -p123456 -h 192.168.56.132


ERROR 1040 (00000): Too many connections


解决问题的思路:


1、第一要考虑 MySQL数据库参数文件中的相应max_connections参数值是否设置得太小,导致客户机连接数超出数据库所能承受的最大值。


该值默认大小是 151,我们可以根据实际情况进行调整。


对应解决办法:set global max_connections=500


但是这样的调整会有隐患,因为我们无法证实数据库是否能承受如此大的连接压力,就好像原来一个人只能吃一个馒头,而现在非得让他吃10个才行,他肯定不能接受。如果对服务器进行响应,就有可能发生宕机。


所以这又反映出了,我们在新上线一个业务系统的时候,要做好压力测试。保证后期对数据库进行优化调整。


2、其次可以限制 InnoDB的并发处理数量,如果 innodb_thread_concurrency = 0(这种代表不受限制) 可以先改成 16 或是 64 看服务器压力。


如果非常大,可以先改的小一点让服务器的压力下来之后,然后再慢慢增大,根据自己的业务而定,个人建议可以先调整为 16 即可。


MySQL 随着连接数的增加性能是会下降的,在 MySQL 5.7 之前都需要让开发配合设置 thread pool,连接复用。MySQL 5.7 之后数据库自带 thread pool 了,连接数问题也得到了相应的解决。


另外对于有的监控程序会读取 information_schema 下面的表,可以考虑关闭下面的参数:


innodb_stats_on_metadata=0


set global innodb_stats_on_metadata=0


案例二 主从复制报错类型

Last_SQL_Errno: 1062 (从库与主库数据冲突)


Last_Errno: 1062


Last_Error: Could not execute Write_rows event on table test.t;


Duplicate entry ‘4’ for key ‘PRIMARY’,


Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;


the event’s master log mysql-bin.000014, end_log_pos 1505


针对这个报错,我们首先要考虑是不是在从库中误操作导致的。结果发现,我们在从库中进行了一条针对有主键表的SQL语句的插入,导致主库再插入相同 sql 的时候,主从状态出现异常。发生主键冲突的报错。


解决方法:在确保主从数据一致性的前提下,可以在从库进行错误跳过。一般使用 percona-toolkit 中的 pt-slave-restart 进行。


在从库完成如下操作:


[root@zs bin]# ./pt-slave-restart -uroot -proot123


2017-07-20T14:05:30 p=…,u=root node4-relay-bin.000002 1506 1062


之后最好在从库中开启 read_only 参数,禁止在从库进行写入操作。


Last_IO_Errno: 1593(server-id冲突)


Last_IO_Error:


Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;


these ids must be different for replication to work


(or the –replicate-same-server-id option must be used on slave but this


does not always make sense; please check the manual before using it)


这个报错出现之后,就能一目了然看到两台机器的 server-id 是一样的。


在搭建主从复制的过程中,我们要确保两台机器的 server-id 是唯一的。这里再强调一下 server-id 的命名规则(服务器 ip 地址的最后一位+本 MySQL 服务的端口号)。


解决方法:在主从两台机器上设置不同的 server-id。


Last_SQL_Errno: 1032(从库少数据,主库更新的时候,从库报错)


Last_SQL_Error:


Could not execute Update_rows event on table test.t; Can’t find record


in ‘t’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the


event’s master log mysql-bin.000014, end_log_pos 1708


解决问题的办法:根据报错信息,我们可以获取到报错日志和position号,然后就能找到主库执行的哪条sql,导致的主从报错。


在主库执行:


/usr/local/mysql/bin/mysqlbinlog –no-defaults -v -v –-output=decode-rows /data/mysql/mysql-bin.000014 |grep -A 10 1708 > 1.log


cat 1.log


#170720 14:20:15 server id 3 end_log_pos 1708 CRC32 0x97b6bdec Update_rows: table id 113 flags: STMT_END_F


### UPDATE `test`.`t`


### WHERE


### @1=4 /* INT meta=0 nullable=0 is_null=0 */


### @2=’dd’ /* VARSTRING(60) meta=60 nullable=1 is_null=0 */


### SET


### @1=4 /* INT meta=0 nullable=0 is_null=0 */


### @2=’ddd’ /* VARSTRING(60) meta=60 nullable=1 is_null=0 */


# at 1708


#170720 14:20:15 server id 3 end_log_pos 1739 CRC32 0xecaf1922 Xid = 654


COMMIT/*!*/;


DELIMITER ;


# End of log file


ROLLBACK /* added by mysqlbinlog */;


/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;


/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


获取到SQL语句之后,就可以在从库反向执行SQL语句。把从库缺少的SQL语句补全,解决报错信息。


在从库依次执行:


mysql> insert into t (b) values (‘ddd’);


Query OK, 1 row affected (0.01 sec)


mysql> stop slave;


Query OK, 0 rows affected (0.00 sec)


mysql> exit


Bye


[root@node4 bin]# ./pt-slave-restart -uroot -proot123


2017-07-20T14:31:37 p=…,u=root node4-relay-bin.000005 283 1032


案例三 MySQL安装过程中的报错

[root@zs data]# /usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf &[1] 3758


[root@zs data]# 170720 14:41:24 mysqld_safe Logging to ‘/data/mysql/error.log’.


170720 14:41:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql170720


14:41:25 mysqld_safe mysqld from pid file /data/mysql/node4.pid ended


170720 14:41:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql2017-07-20


14:41:25 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.


Please use –explicit_defaults_for_timestamp server option


(see documentation for more details)./usr/local/mysql/bin/mysqld:


File ‘/data/mysql/mysql-bin.index’ not found (Errcode: 13 – Permission denied)


2017-07-20 14:41:25 4388 [ERROR] Aborting


解决思路:遇到这样的报错信息,我们要学会时时去关注错误日志 error log 里面的内容。看见了关键的报错点Permission denied,证明当前 MySQL 数据库的数据目录没有权限。


解决方法:


[root@zs data]# chown mysql:mysql -R mysql


[root@zs data]# /usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf &


[1] 4402


[root@zs data]# 170720 14:45:56 mysqld_safe Logging to ‘/data/mysql/error.log’.


170720 14:45:56 mysqld_safe Starting mysqld daemon with databases from /data/mysql


启动成功。


如何避免这类问题,个人建议在安装 MySQL 初始化的时候,一定加上–user=mysql,这样就可以避免权限问题。


./mysql_install_db –basedir=/usr/local/mysql/ –datadir=/data/mysql/ –defaults-file=/etc/my.cnf –user=mysql


案例四 数据库密码忘记的问题

[root@zs ~]# mysql -uroot -p


Enter password:


ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)


[root@zs ~]# mysql -uroot -p


Enter password:


ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)


我们有可能刚刚接手别人的 MySQL 数据库,而且没有完善的交接文档。root 密码可以丢失或者忘记了。


解决思路:目前是进入不了数据库的情况,所以我们要考虑是不是可以跳过权限。因为在数据库中,MySQL 数据库中 user 表记录着我们用户的信息。


解决方法:启动 MySQL 数据库的过程中,可以这样执行:


/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf –skip-grant-tables &


这样启动,就可以不用输入密码,直接进入 MySQL 数据库了。然后在修改你自己想要改的 root 密码即可。


update mysql.user set password=password(‘root123′) where user=’root’;


案例五 truncate 删除数据,导致自动清空自增 ID,前端返回报错 not found

这个问题的出现,就要考虑下 truncate 和 delete 的区别了,看下实验演练:


首先先创建一张表:


CREATE TABLE `t` (


`a` int(11) NOT NULL AUTO_INCREMENT,


`b` varchar(20) DEFAULT NULL,


PRIMARY KEY (`a`),


KEY `b` (`b`)


) ENGINE=InnoDB AUTO_INCREMENT=300 DEFAULT CHARSET=utf8


插入三条数据:


mysql> insert into t (b) values (‘aa’);


Query OK, 1 row affected (0.00 sec)


mysql> insert into t (b) values (‘bb’);


Query OK, 1 row affected (0.00 sec)


mysql> insert into t (b) values (‘cc’);


Query OK, 1 row affected (0.00 sec)


mysql> select * from t;


+—–+——+


| a | b |


+—–+——+


| 300 | aa |


| 301 | bb |


| 302 | cc |


+—–+——+


3 rows in set (0.00 sec)


先用 delete 进行删除全表信息,再插入新值。


结果发现 truncate 把自增初始值重置了,自增属性从 1 开始记录了。当前端用主键 id 进行查询时,就会报没有这条数据的错误。


个人建议不要使用 truncate 对表进行删除操作,虽然可以回收表空间,但是会涉及自增属性问题。这些坑,我们不要轻易钻进去。


今后还会继续总结 MySQL 中的各种报错处理思路与方法,希望跟各位老铁们,同学们一起努力。多沟通多交流!


上一篇: 没有了

下一篇: MySQL分布式数据库适用于飞机订票系统吗

相关推荐

产品试用 产品试用
400-820-6580 免费电话