MySQL使用中遇到的问题及日常操作累积（持续更新）

DSP

MySQL使用中遇到的问题及日常操作累积（持续更新）

2019-07-13 16:53发布生成海报

站内文章 / DSP

10798 0

【20151012】TIMESTAMP列字段说明

create table test(
    aaa char(32),
    createtime TIMESTAMP
);
show create table test;
CREATE TABLE `test` (
  `aaa` char(32) COLLATE latin1_bin DEFAULT NULL,
  `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin

这里的 createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 意味更新记录时，createtime字段都会被设置为当前时间可以单独设置：

CREATE TABLE `test` (
  `aaa` char(32) COLLATE latin1_bin DEFAULT NULL,
  `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin

这样更新记录时就不会自动更改这个字段值了。

【20150910】ERROR 1449 (HY000): The user specified as a definer (‘root’@’%’) does not exist

一个表好久没插入数据了，今天业务插入数据时竟然提示：
ERROR 1449 (HY000): The user specified as a definer (‘root’@’%’) does not exist 检查了好长时间，发现没有用 (‘root’@’%’)连接mysql的地方，最后发现这张表有一个trigger，建立时是用(‘root’@’%’)建立的。这时想起来，mysql一开始是没有限制权限的，默认都是(‘root’@’%’)连接，后来加了用户访问权限，删除了(‘root’@’%’)，结果导致没有权限执行这个触发器。处理方法：删除并重建触发器就可以了。

20150626 mysql master重启后slave延迟问题

今天mysql master重启后，slave一直延迟，show slave status一直显示：

Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 158782

但master上log明明到了mysql-bin.000007，原来是slave_net_timeout参数的问题修改my.cnf，增加：

slave_net_timeout = 30
#当slave从主数据库读取log数据失败后，等待多久重新建立连接并获取数据。
#MySQL主从复制的时候， 当Master和Slave之间的网络中断，但是Master和Slave无法察觉的情况下（比如防火墙或者路由问题）。Slave会等待slave_net_timeout设置的秒数后，才能认为网络出现故障，然后才会重连并且追赶这段时间主库的数据。默认是3600秒，建议设置：30

修改MASTER_CONNECT_RETRY参数：

change master to MASTER_CONNECT_RETRY=15;
#当重新建立主从连接时，如果连接建立失败，间隔多久后重试。默认设置为 60秒，建议设置：15

20150605 Replicate_Do_DB

该参数如果想匹配多个数据库，要在配置文件中写多行Replicate_Do_DB=XXX，而不能在一行中以’,’分隔写多个，这样写一个也不生效。

20150601：slave_net_timeout 问题一则

在一个已经建立主从复制关系的系统里面,正常情况下,由从库向主库发送一个 COM_BINLOG_DUMP 命令后,主库有新的binlog event，会向备库发送binlog。
但是如果主库长时间(大于slave_net_timeout)没有向从库发送binlog，从库就会认为主从连接中断向主库发起重连请求。 slave_net_timeout是设置在多少秒没收到主库传来的Binary Logs events之后,从库认为网络超时,Slave IO线程会重新连接主库。
该参数的默认值是3600s ，时间太久会造成数据库延迟或者主备库直接的链接异常不能及时发现
将 slave_net_timeout 设得很短会造成 Master 没有数据更新时频繁重连。
一般线上设置为5s 。

20141128：关于mysql字符集

| character_set_client     | gb2312                     |
| character_set_connection | gb2312                     |
| character_set_database   | latin1                     |
| character_set_results    | gb2312                     |
| character_set_server     | latin1                     |

请求转换逻辑是：character_set_client -> character_set_connection -> character_set_database(table,column)/character_set_server
返回转换逻辑是：character_set_database(table,column)/character_set_server -> character_set_results
但是需要注意，如果表、数据库、服务是latin1的字符集，除非character_set_client、character_set_connection、character_set_results的编码都是latin1，不然是无法转换成功的，因为latin1本身并不关心是不是汉字，它只是按字节流存储

20141126：mysql的权限设置

最近在mysql的权限设置时，grant select on test.* to voip@’%’，生效后用voip登录，发现test库仍然可以有其它create,drop操作，后来发现默认安装后test库是对所有用户都有所有权限的，select * from mysql.db可以看到；

历史记录

1、Starting MySQL.The server quit without updating PID file (/var/lib/mysql/localhost.localdomain.pid).[失败]
      最后原因竟然是my.cnf配置错误，有两条重复内容，innodb_flush_method  = O_DRIECT 
2、[root@localhost bin]# ./mysqlbinlog /var/lib/mysql/mysql-bin.000053
./mysqlbinlog: unknown variable 'default-character-set=latin1'
原因是my.cnf配置文件中，最后两行mysqlbinlog程序不能识别
[client]
user      = root
password  = cpyf
port      = 3306
socket    = /tmp/mysql.sock
default-character-set=latin1
tee = /home/jfy/tmp/mysql_opt.sql

解决办法：一是修改my.cnf屏蔽这两行，二是在mysqlbinlog命令中加入--no-defaults 选项就不会读取my.cnf中client段的默认的配置了

这个错误还会出现在一些象mysqladmin这种客户端工具上。

3、如何回收InnoDB表空间
大家知道InnoDB表，插入大量数据后，表文件大小会变大，然后即使删除了数据，表空间大小也不缩小
可以按以下几种方法来做。

truncate table 
analyze table
optimize table
alter table xx engine=innodb

如果还不行，那么只有先备份数据，然后drop table，再导入数据了 

4、MYSQL交互式与非交互式连接会话有什么不同？
研究wait_timeout参数，牵扯出交互式和非交互式连接，那么到底什么是交互式连接，什么是非交互连接呢，就这么一个小问题，找遍所有网站也没有一个明确答案

自己总结一下：
交互式，用tcp连接上去，象mysql自带的客户端工具，还有象mysql_real_connect库函数这样的连接MYSQL的程序
非交互式，象ODBC与JDBC这种连接方式，mysql < ins.sql，这种方法，其实也是一种非交互式的

那么mysql_real_connect中的“CLIENT_INTERACTIVE”选项，带这个参数与不带这个参数有什么不同呢？MYSQL服务器什么做什么特殊处理呢？我一直迷惑

5、MySQL的read-only选项：
今天在从机上开起read-only选项，insert和update还是可以成功，原来我用的是super权限，super权限在read-only状态是允许写操作的。
read-only选项：对所有的非临时表进行只读控制。但是有两种情况例外：
1. 对replication threads例外，以保证slave能够正常的进行replication。
2. 对于拥有super权限的用户，可以ignore这个选项。

SUPER 权限 ：
1. 可以有change master to, kill其他用户的线程的权限。
2. Purge binary logs 来删除binary log, set global来动态设置变量的权限。
3. 执行mysqladmin debug命令，开启或者关闭log，在read-only打开时执行update/insert操作。
4. 执行start slave, stop slave.
5. 当连接数已经达到max_connections的最大值时，也可以连接到server。

6、MySQL服务启动后怎么执行SQL语句：修改my.cnf，在mysqld段中加入：init-file = /usr/local/mysql/data/init-file.sql，就可以了

IP address could not be resolved: Temporary failure in name resolution
关于Relay Log无法自动删除的问题（Neither --relay-log nor --relay-log-index were used）