MySQL server time synchronization problem

1, adjusting the time of database server for an app shake activities find out dated 15 minutes, can also continue to shake, the root of the problem lies in the database server time than the application server time quite the 18 minutes, and app is database now), so that led to. The 2 solution, 1 is to modify the program code, not through the database now () to get but through the application server access; 2 is the time to modify the database directly. After the adoption of the balance of the second programs. 1.1, modify the Linux server time by modifying the -s date time -s date...
read(1629) comment(2)

MySQL user permissions detailed summary

1, MySQL permissions system MySQL system is generally divided into 5 levels: Global hierarchy: Global permissions apply to all databases in a given server. These permissions are stored in the mysql.user table. ALL ON GRANT. And ALL ON REVOKE. Only granting and revocation of global permissions. Database level: Database permissions apply to all targets in a given database. These permissions are stored in the mysql.db table. ALL ON db_name. GRANT and REVOKE...
read(2507) comment(1)

MySQL s --Innodb storage engine lock

Recently encountered many lock problem, so to solve the, again careful to read the books on the lock, are summarized as follows: 1, lock types InnoDB storage engine realized following two standard row level lock:? Shared locks (s lock) allow transactions to read a row of data. ? exclusivelock (X lock), allowing a transaction to delete or update a row. When a transaction acquires a row r shared locks, then another transaction can also immediately acquire share locks R, because read did not change the data row R, this is lock compatible. But if a transaction wants to get a row of R row it locks...
read(2215) comment(3)

MySQL database design preliminary specification V1.0

Database design specifications: 1, Table 1.1 table design design specification on a) table name, column name must be annotated. B) named should use the rich meaning of English words or abbreviations, consisting of more than one word, all caps, to "separated, use only English letters, digits, and the underscore character, leaving no space. Such as USER_DETALL, do not allow the use of the keyword TYPE or STATUS as a field name. C) name does not exceed 15 characters in length is appropriate (to avoid more than 20), should reflect the data...
read(2461) comment(0)

MySQL traversal of the tree traversal (on the multi-level menu bar and multi-level query problem)

Objective: on multi level menu bar or the permissions system departments on the lower level of the tree traversal and Oracle are connect by to achieve MySQL does not such a convenient way, so MySQL traversing the data table is headache problems we often encounter below by stored procedures to achieve. 1, set up the test table and data: DROP TABLE IF EXISTS csdn.channel; CREATE TABLE csdn.channel (ID INT (1...
read(9589) comment(8)

A more complex number of times the memory of the split string

Write your own special split function is as follows: to create or replace function fn split STR 2 (VaR STR in VARCHAR2) return varchar2/* have a T1 table, which has a field called C3, the deposit is shop all the little bits of information. Now need a stored procedure, will all the records of C3 field coordinate values are reduced 3 times, write C field for instance 220.25 257220.25 269.75229...
read(1491) comment(0)

MySQL5.6.12 causes the use of CPU 2000% reasons

Top that CPU utilization rate high: 184 days of top - 16:41:04 up. 21:31, 2 users, load average: 19.97 and 18.38. 584 total the 18.36Tasks:, running, 583 sleeping, 0 stopped, 0 zombieCpu (s): 0.0%us, 0.0%sy, 41.2%ni, 58....
阅读(1726) 评论(3)

MySQL分组排序取前N条记录以及生成自动数字序列的SQL

——MySQL分组排序取前N条记录的最简洁的单条sql.use试验;如果表下降存在测试;测试表( ID主关键字, CID int, 作者varchar(30))= MyISAM引擎;插入测试值 (1,1,'test1 '),(2,1,'test1…
阅读(2653) 评论(0)

MySQL DBA面试题目小结

从阿里巴巴面试DBA归来后,总结出来的招聘MySQL DBA的第一轮基础面试题目,欢迎大家补充: 1、MySQL的复制原理以及流程。   (1)先问基本原理流程,3个线程以及之间的关联。   (2)再问一致性延时性,数据恢复。   (3)再问各种工作遇到的复制bug的解决方法 2、MySQL中MyISAM与InnoDB的区别,至少5点。    (1)…
阅读(11794) 评论(16)

关于MySQL的查询缓存的一些交流心得

今天线上MySQL出现内存使用率报警,就去查了下MySQL内存使用的参数,重点是缓存,关于innodb_buffer_pool_size以及查询缓存的使用。 query_cache_type 默认是打开的,而且缓存区query_cache_size默认大小是32m,通常建议不超过256M大小,可以用过查询缓存参数来看具体值: MySQL >显示变量…
阅读(1745) 评论(0)

MySQL存储过程-->通过游标遍历和异常处理迁移数据到历史表

—— 大表数据迁移,每天凌晨1点到5点执行,执行间隔时间10分钟,迁移旧数据到历史表。 分隔符$   使用` dbx `美元   下降过程是否存在` pro_xx `美元   创建  程序` pro_xx `() 开始      宣布p_oalid int默认0;    宣言…
阅读(2282) 评论(0)

MySQL存储过程-->长字符串扯分

分隔符——分隔符美元 使用测试$ $ - 下降过程是否存在` pro_split `美元--如果已经存在则删除过程 创建测试程序` `。` pro_split `(VARCHAR RID) 开始 声明一点,定义一个位置点默认的0; 声明…
阅读(1347) 评论(0)

杀掉MySQL中所有睡眠的客户端线程

写了一个脚本,运行这个脚本,就可以杀掉MySQL客户端线程的中所有睡眠 #是用来杀死MySQL睡眠进程表 #!/垃圾桶 同时: 做  n = ` mysqladmin列表- uadmin - pxxxxx | grep -我睡| WC L `  日期= `大…
阅读(1550) 评论(0)

自增字段auto_commit的研究分析

MySQL自增字段,自增字段计数器在主存储里面,不在硬盘上(此计数器只存储在内存中,而不是在磁盘上)。 1、添加表,设立自增主键字段 创建表(ID为主键auto_increment,NAME varchar(3000))= InnoDB引擎; 2、可以让…
阅读(1598) 评论(0)

清理8组节点中表的历史数据,平均每个节点中的表有1.5亿条记录,需要根据date_created字段清理8000W数据记录,这个字段没有索引。

——清理8组节点中表的历史数据,平均每个节点中的表有1.5亿条记录,需要根据date_created字段清理8000W数据记录,这个字段没有索引。 环境介绍  线上磁盘空间不足,截断了许多能动的表,磁盘空间还是占据了87%,我们的Nagios报警阀值是80%。所以不断的还会收到以及短信报警需要根据时间字段date_created清理电子邮件, 但是在设计之初由于当…
阅读(1320) 评论(0)

MemSQL学习笔记-类似MySQL的数据库

http://gigaom.com/cloud/ex-facebookers-launch-memsql-to-make-your-database-fly/ ——多主-从 http://www.mysqlops.com/2012/02/14/diy_multi_master_replication.html HTTP:/ / www.cnblogs。COM /刘昊/档案…
阅读(3035) 评论(0)

MySQL存储过程带在和出参数以及PHP,铅如何调用的小例子

最简单的例子: MySQL >分隔符$ MySQL >使用测试美元 数据库更改 MySQL >删除过程是否存在` sp_add `美元 查询确定,0行受影响(0秒) MySQL >创建程序sp_add(一个int,int b,出…
阅读(1700) 评论(0)

手机在线更新系统MySQL数据库服务器参数优化mycnf,16G内存8核CPU,

业务场景:后台支持手机在线更新系统,DB服务器内存16G,8核,戴尔的PC服务器。 旺达:200个左右 TPS:1个左右一分钟50几个 sort_buffer_size = 32M大了,8M即可 read_buffer_size = 32M大了,8M即可 read_rnd_buffer_size = 16m  大了,8M即可 table_ope…
阅读(1631) 评论(0)

修改MySQL的时区,涉及参数time_zone

首先需要查看MySQL的当前时区,用time_zone参数 MySQL >显示变量''%1 time_zone %; ------------------ -------- + + + | variable_name |价值| ------------------ -------- + + + | system_time_zone | CST…
阅读(4142) 评论(0)

MySQL分区表分区线上修改分区字段,后续进一步学习分区(2)-->子分区以及对录入空值的处理情况。

——MySQL分区,子分区以及对录入值的处理情况 看完官方文档做的笔记空。 -关键分区 分区的关键是类似于分区的哈希,除了哈希分区采用了用户定义的表达式,有…
阅读(1946) 评论(1)
57条数据共3页 下一页 尾页
    个人资料
    • 访问:1028038次
    • 积分:一万六千四百零六
    • 等级:
    • 排名:314名第
    • 原创:443篇
    • 转载:3篇
    • 译文:2篇
    • 评论:529条
    我的课程
    博客专栏
    技术网址连接
    http://dev.mysql.com/doc/
    文章分类
    文章存档
    Latest comments