MySQL管理中遇到的问题总结

本是古典何须时尚 2022-05-10 06:50 470阅读 0赞

**目录**

1.表中有主外键导致不能truncate的问题

2.DRDS中不能进行全表扫描

3.主从复制导入数据时

4.安装gcc时出现glibc版本冲突的问题

5.应用侧做压测，发现会有段时间发生卡顿

6.GTID复制

7.主从复制

8.auto\_increment

9.DRDS and MYSQL

10.too many open files 问题

11.timestamp 和 datetime 区别

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

# 1.表中有主外键导致不能truncate的问题 #

两种方法：

（1）关闭检查约束

>       set FOREIGN\_KEY\_CHECKS=0;
> 
>       truncate table  table\_name;
> 
>       set FOREIGN\_KEY\_CHECKS=1;

（2）

>          使用delete from table\_name

# 2.DRDS中不能进行全表扫描 #

> 解决方法：
> 
>      delete from  table\_name where 1=1;

# 3.主从复制导入数据时 #

防止生成binlog： set @@session.sql\_log\_bin=OFF;source \*.sql

# 4.安装gcc时出现glibc版本冲突的问题 #

CentOS6.5 的机器，但使用的并不是6.5标准镜像，也有可能将glibc升级了。

1.使用标准镜像搭建yum源之后，yum install gcc，发现需要glibc-\*132\* ，但是本机器中已经安装了glibc-\*209\*。

2.glibc这个包不能删，也很难降级，这就很难受。

3.试了各种方法无解之后，准备强制安装gcc：rpm -ivh gcc\* --nodeps

4.但是使用这种方法虽然能强制安装上gcc，但是在编译（./configure）的时候，会出各种漏包的错误。

5.漏的包（大体为）：

glibc-devel libnl libnl-devel glibc-headers openssl-devel krb5-devel zlib-devel
    cpp pcre pcre-devel keyutils-libs-devel libcom_err-devel libselinux-devel libnfnetlink libnfnetlink-devel kernel-headers libtool
    注：能用yum按的就用yum，不能用yum的就用rpm 强制安装

# 5.应用侧做压测，发现会有段时间发生卡顿 #

压测：一共是7个前端应用，7个后端应用，每个后端应用开了600个数据库连接

目前的java程序连接参数：  
        properties.setProperty("maxWait", "3000");  
        properties.setProperty("timeBetweenEvictionRunsMillis", "60000");  
        properties.setProperty("minEvictableIdleTimeMillis", "300000");  
        properties.setProperty("validationQuery", "SELECT now()");  
        properties.setProperty("testWhileIdle", "true");  
        properties.setProperty("testOnBorrow", "false");  
        properties.setProperty("testOnReturn", "false");  
        properties.setProperty("poolPreparedStatements", "false");  
        properties.setProperty("maxPoolPreparedStatementPerConnectionSize", "200");  
        properties.setProperty("initialSize", “10”);  
        properties.setProperty("maxActive", “100”);

发现：当卡顿的时候CPU利用率下降，但是show processlist；中基本上全是sleep。

解决：增大 max\_connections为12000。

其实这应用那边为了压测，将java程序连接参数调的有些极端了。调稳点：

hss.jdbc.maxActive=30  
                            hss.jdbc.initialSize=15  
                            hss.jdbc.minIdle=15

# 6.GTID复制 #

Slave has more GTIDs than the master has, using the master's SERVER\_UUID；

原因：从库中(show slave status;)Executed\_Gtid\_Set：master\_uuid:tid 大于了主库中（show master status;）Executed\_Gtid\_Set：master\_uuid:tid的值。

解决方法：让主库的GTID的值跟上从库(注意下面这个master\_uuid:tid 是在从库上查找出来的值)

set gtid\_next='master\_uuid:tid'；begin ; commit; set gtid\_next='automatic';

# 7.主从复制 #

Last\_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '5d52e9bc-dca8-11e8-8f03-48df371d2499:500' at master log mysql-bin.000004, end\_log\_pos 6687849. See errorlog and/or performance\_schema.replication\_applier\_status\_by\_worker table for more details about this failure or others, if any.

原因：主键冲突

解决方法：

mysql> set gtid\_next='5d52e9bc-dca8-11e8-8f03-48df371d2499:500';  
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> set gtid\_next='automatic';  
Query OK, 0 rows affected (0.00 sec)

stop slave sql\_thread;start slave sql\_thread;

# 8.auto\_increment #

innodb\_autoinc\_lock\_mode = 1

指定id自增列插入后，之后auto\_increment变为该插入的值+1；

CREATE TABLE test\_auto (id INT PRIMARY KEY AUTO\_INCREMENT ,NAME VARCHAR(20),age INT );

AUTO\_INCREMENT : 1

INSERT INTO test\_auto VALUES (1,'a',1);  
INSERT INTO test\_auto VALUES (2,'a',2);  
INSERT INTO test\_auto VALUES (3,'a',3);

查看当前 AUTO\_INCREMENT 的值：  
SELECT AUTO\_INCREMENT FROM information\_schema.tables WHERE table\_schema='wang' AND table\_name='test\_auto';  
AUTO\_INCREMENT:4  
测试：  
INSERT INTO test\_auto VALUES (6,'a',6);  
SELECT \* FROM test\_auto  
id: 1 2 3 6

INSERT INTO test\_auto(NAME,age) VALUES ('a',4);  
id:1 2 3 6 7  
INSERT INTO test\_auto(NAME,age) VALUES ('a',5);  
id:1 2 3 6 7 8

# 9.DRDS and MYSQL #

DRDS: SHOW FULL PROCESSLIST WHERE COMMAND!='Sleep';

MySQL：show processlist 后不可以跟 where 条件。

# 10.too many open files 问题 #

![20190415110611295.png][]

原因：

1.程序打开的文件句柄/socket已达到用户上限

解决方法：

在提高open files的同时，要找到open files的瓶颈，并找到句柄达到上限的原因。

1.查看当前句柄最大打开数

切换到启动进程的用户

ulimit -a ： 查看open files

ulimit -p PID : 查看某个进程的句柄数量上限

2.临时修改open files

ulimit -n 65536

3.永久修改

vim /etc/security/limits.conf

\* soft nofile 65536

\* hard nofile 65536

4.查看系统中进程占用的句柄数量

第一列为句柄数，第二列为进程ID

lsof -n |awk '\{print $2\}'|soft|uniq -c |sort -nr|more

5.查看某个进程占用了什么类型的句柄

lsof |grep PID

文件类型：

DIR：表示目录。

CHR：表示字符类型。

BLK：块设备类型。

UNIX： UNIX 域套接字。

FIFO：先进先出 (FIFO) 队列。

IPv4：网际协议 (IP) 套接字。

DEVICE：指定磁盘的名称

SIZE：文件的大小

NODE：索引节点（文件在磁盘上的标识）

NAME：打开文件的确切名称

# 11.timestamp 和 datetime 区别 #

1.两者的存储方式不一样

timestamp按 时区 存储
        它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。
    datetime 按 时间 存储
        插入是什么值，查询就是什么值

2.两者的存储范围不一样

timestamp ：
        timestamp所能存储的时间范围为：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。
    datetime：
        datetime所能存储的时间范围为：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。

3.自动初始化，自动更新

自动初始化指的是如果对该字段（譬如上例中的hiredate字段）没有显性赋值，则自动设置为当前系统时间。
    
    自动更新指的是如果修改了其它字段，则该字段的值将自动更新为当前系统时间。
    它与“explicit_defaults_for_timestamp”参数有关。
    默认情况下，该参数的值为OFF，如下所示：
        mysql> show variables like '%explicit_defaults_for_timestamp%';
        +---------------------------------+-------+
        | Variable_name                   | Value |
        +---------------------------------+-------+
        | explicit_defaults_for_timestamp | OFF   |
        +---------------------------------+-------+
        row in set (0.00 sec)
    很多时候，这并不是我们想要的，如何禁用呢？
    
    1. 将“explicit_defaults_for_timestamp”的值设置为ON。
    2. “explicit_defaults_for_timestamp”的值依旧是OFF，也有两种方法可以禁用
         1> 用DEFAULT子句该该列指定一个默认值
         2> 为该列指定NULL属性。
    
    在MySQL 5.6.5版本之前，Automatic Initialization and Updating只适用于TIMESTAMP，而且一张表中，最多允许一个TIMESTAMP字段采用该特性。从MySQL 5.6.5开始，Automatic Initialization and Updating同时适用于TIMESTAMP和DATETIME，且不限制数量。

# 12.MySQL5.6.10中一个bug #

mysql5.6.12修复，bug内容：

有些操作比如replace into或者在做增删改操作前没有加use schema而是直接在引用表前加schema.table，就不会在binlog中有记录，从而导致主从不一致

# 13.大表DDL方案 #

> 1.从库--关闭log-bin；执行DDL。
> 
> 2.执行完成后，等主从无延迟后进行主从切换。主从切换期间，应用应暂停对数据库的写操作
> 
> 3.切换成功后，开启新主（原slave）的log-bin
> 
> 4.应用连接新主，开启写操作

> 1.从库上执行DDL；将从库上执行DDL产生的GTID在主库上利用生成一个空事务GTID的方式将这个GTID在主库上生成出来。  
> 各个从库做完之后再主从切换，然后再在原来的主库上同样做一次。

[20190415110611295.png]: /images/20220510/0e703006265b4d3aa2e0b4dadca61d84.png