MySQL管理中遇到的问题总结

目录

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 问题

原因：

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在主库上生成出来。
各个从库做完之后再主从切换，然后再在原来的主库上同样做一次。