mysql X00

alter 操作阻塞

在MySQL 5.5.3以后的版本中，未提交的事务一定会堵塞DDL请求（据说5.6.6以后的版本做了相关的优化，未做测试暂且不提）
DDL在等待metada lock的过程中肯定会堵塞后续的DML请求，也有可能堵塞后续的SELECT请求（需要看SELECT请求是否能够命中qcache）
怎么处理这种问题是老生常谈了，优化SQL避免出现大事务，请求结束立即执行commit关闭事务，千万千万不要把所有的请求甚至包括代码的逻辑处理都塞在一个事务里面去完成，否则一旦出现一个DDL那就是灾难的发生，很难想象这次要是没有过载保护，XX系统是不会直接宕机呢？
这次问题的处理是这样的，线上环境彻底实施读写分离，让master只能写，因为这次的问题是因为程序员设定的读也使用了事务，度的频率明显高于写。

权限

• 授予权限

grant select,insert,update on `testdata`.* to qf@'172.100.102.%' identified by '123456';
flush privileges;

• 取消授权

revoke insert,update on `testdata`.* from qf@'172.100.102.%';
flush privileges;

• 备份账户的最小权限

select,lock tables

表的 crud

1. 不同数据库A,B之间表的复制.

rename table A.table1 to B.table2

• 确保A,B在一个文件系统上,一般也都是在的.

2. 新建表.

本表的unionid与表idx_unionid的unionid字段建立外键.括号外是建立外键的对应表,括号内是对应表名,默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1,如下是从10001开始.

CREATE TABLE `user_list` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `unionid` varchar(64) NOT NULL COMMENT 'unionid',
  `app_id` int(11) NOT NULL COMMENT 'app id',
  `openid` varchar(32) NOT NULL COMMENT 'openid 长度为28',
  `status` tinyint(1) DEFAULT '1' COMMENT '1. 正常',
  `create_time` datetime NOT NULL COMMENT '注册时间',
  PRIMARY KEY (`id`),
  KEY `idx_unionid` (`unionid`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;

3. 重命名表名.

use databasename
rename table old_name to new_name;

• 对某个表执行rename的时候,不能锁表,不能有正在执行的事物.而且对old_table必须有alter,drop权限,对new_table有create,insert权限.

背景介绍

如今已经是公有云的时代了, 每一家创业公司基本上都会使用1-2家公有云提供的云服务, 公有云只是提供了基础资源, 如果想要跟自家公司的业务更好地融合, 还是需要同步云上的资源到公司内部的CMDB系统中, 这里说说同步云资源的过程中遇到的一些数据库的知识. 同步的几种场景:

• 新增资源
• 变更配置
• 删除资源

技术解析

这里主要使用了MySQL的语法INSERT ... ON DUPLICATE KEY UPDATE Syntax, 举一个实际的例子, 有一个用户表user, 其中有3个字段user_id, username, mobile. user_id字段是UNIQUE索引, 下面两个语句具有相同的效果:

INSERT INTO t1 (user_id, username, mobile) VALUES ('10001', 'rocky', '123456789') ON DUPLICATE KEY UPDATE mobile='123';

UPDATE user SET mobile='123' WHERE user_id='10001';

注意事项

• 表中涉及的3个字段中至少有一个(官方建议只有一个)是UNIQUE或者primary key索引, 并且不能是auto increment的, 所以对于自增主键不会执行update后面的语句.
• 实例中语句的含义是如果该用户不存在就新增该用户, 存在的话就更新mobile字段, 这里要注意如果存在的话只是更新mobile, 用户名是不更新的, 这里可以用于把要更新的字段放到后面就行了, 不用每次更新的字段比如created_at, 就不用写到后面了.

创建新的数据表的时候经常会遇到INT(2)和INT(4)的选择,那么这到底是啥意思呢?这里简单总结一下,参考了一下官方文档和一些资料,我把结论直接写在这里了.

对于整型(int、tinyint与bigint等)它们的存储的大小已经确定了, 括号里的数字只是影响显示长度, 而且括号里的数字要和zerofill结合使用才能生效, 加上zerofill以后会自动把该字段设置成unsign类型, 当存储的数据小于括号的数字的时候,默认显示的是用空格补全的,如果存储的数字长度大于括号里的数字的时候,显示就不受限制,所以平时创建表的时候给INT加上括号是没有必要的.