随笔分类 -  Mysql 操作与管理

mysql 5.7修改密码
摘要:关闭正在运行的 MySQL : [root@www.woai.it ~]# service mysql stop 运行 [root@www.woai.it ~]# mysqld_safe --skip-grant-tables & 为了安全可以这样禁止远程连接: [root@www.woai.it 阅读全文

posted @ 2016-05-04 15:56 Still water run deep 阅读(10686) 评论(0) 推荐(0)

Using SHOW PROCESSLIST and mysqladmin debug Output in Conjunction with SHOW INNODB STATUS
摘要:When InnoDB appears hung, I know the natural reaction is to check SHOW ENGINE INNODB STATUS.In fact, it’s the first thing I check when InnoDB tables a... 阅读全文

posted @ 2015-01-05 00:58 Still water run deep 阅读(306) 评论(0) 推荐(0)

Concurrent inserts on MyISAM and the binary log
摘要:Recently I had an interesting surprise with concurrent inserts into a MyISAM table. The inserts were not happening concurrently with SELECT statements... 阅读全文

posted @ 2015-01-05 00:56 Still water run deep 阅读(278) 评论(0) 推荐(0)

Choosing proper innodb_log_file_size
摘要:If you’re doing significant amount of writes to Innodb tables decent size ofinnodb_log_file_sizeis important for MySQL Performance. However setting it... 阅读全文

posted @ 2014-12-08 02:13 Still water run deep 阅读(282) 评论(0) 推荐(0)

How to calculate a good InnoDB log file size
摘要:Peter wrote a post a while ago aboutchoosing a good InnoDB log file size. Not to pick on Peter, but the post actually kind of talks about a lot of th... 阅读全文

posted @ 2014-12-08 01:26 Still water run deep 阅读(267) 评论(0) 推荐(0)

MySQL Thread Pool: Problem Definition
摘要:A new thread pool plugin is now a part of the MySQL Enterprise Edition.In this blog we will cover the problem that the thread pool is solvingand some ... 阅读全文

posted @ 2014-07-06 02:53 Still water run deep 阅读(340) 评论(0) 推荐(0)

MySQL数据库InnoDB存储引擎多版本控制(MVCC)实现原理分析
摘要:文/何登成 导读: 来自网易研究院的MySQL内核技术研究人何登成,把MySQL数据库InnoDB存储引擎的多版本控制(简称:MVCC)实现原理,做了深入的研究与详细的文字图表分析,方便大家理解InnoDB存储引擎实现的多版本控制技术(简称:MVCC)。基本知识假设对于多版本控制(MVCC)的基础知... 阅读全文

posted @ 2014-06-15 14:25 Still water run deep 阅读(2030) 评论(0) 推荐(0)

MySQL exists的用法介绍
摘要:有一个查询如下:SELECT c.CustomerId, CompanyName FROM Customers c WHERE EXISTS( SELECT OrderID FROM Orders o WHERE o.CustomerID = cu.CustomerI... 阅读全文

posted @ 2014-05-25 21:18 Still water run deep 阅读(766) 评论(0) 推荐(0)

Don’t Assume – Per Session Buffers
摘要:MySQL has a number of global buffers, i.e. your SGA. There are also a number of per session/thread buffers that combined with other memory usage const... 阅读全文

posted @ 2014-05-15 02:29 Still water run deep 阅读(355) 评论(0) 推荐(0)

MySQL 5.5: InnoDB Change Buffering
摘要:To speed up bulk loading of data, InnoDB implements aninsert buffer, a special index in the InnoDB system tablespace that buffers modifications to sec... 阅读全文

posted @ 2014-05-13 16:21 Still water run deep 阅读(1064) 评论(0) 推荐(0)

Fixing Poor MySQL Default Configuration Values
摘要:I've recently been accumulating some MySQL configuration variables that have defaults which have proven to be problematic in a high-volume production ... 阅读全文

posted @ 2014-04-29 00:53 Still water run deep 阅读(298) 评论(0) 推荐(0)

A quest for the full InnoDB status
摘要:When running InnoDB you are able to dig into the engine internals, look at various gauges and counters, see past deadlocks and the list of all open tr... 阅读全文

posted @ 2014-04-28 23:48 Still water run deep 阅读(237) 评论(0) 推荐(0)

MySQL : interactive_timeout v/s wait_timeout
摘要:Most of the database intensive applications are worring about the default values of these variables obviously. Developers used to inform me that they ... 阅读全文

posted @ 2014-04-28 23:22 Still water run deep 阅读(451) 评论(0) 推荐(0)

InnoDB Plugin文件格式(概述)
摘要:本文将介绍InnoDB Plugin数据表格式的基本概念。1. 配置参数innodb_file_format这是一个很容易混淆的概念。目前,在InnoDB Plugin(1.0.6)配置文件中innodb_file_format支持两种:Antelope/ˈæntɪləʊp/、Barracuda/ˌbærəˈkjuːdə/。他们分别是两种文件格式的代号,在未来版本中,InnoDB将继续延续这种代号机制,它们会是Antelope, Barracuda, Cheetah, Dragon, Elk, Fox等等。Antelope是Built-in-InnoDB(MySQL内置的I 阅读全文

posted @ 2014-04-11 21:27 Still water run deep 阅读(547) 评论(0) 推荐(0)

adaptive hash index
摘要:An optimization for InnoDB tables that can speed up lookups using=andINoperators, by constructing ahash indexin memory. MySQL monitors index searches for InnoDB tables, and if queries could benefit from a hash index, it builds one automatically for indexpagesthat are frequently accessed. In a sense, 阅读全文

posted @ 2014-04-04 01:20 Still water run deep 阅读(572) 评论(0) 推荐(0)

InnoDB Double write
摘要:记得刚开始看InnoDB文档的时候,Double Write一节(其实只有一小段)就让我很困惑。无奈当时内力太浅,纠缠了很久也没弄明白。时隔几个月,重新来整理一下。涉及到的概念:Buffer Pool简称BP,Dirty Page,Log file,Flush,innodb tablespace。1. 什么是Double Write在InnoDB将BP中的Dirty Page刷(flush)到磁盘上时,首先会将Page刷到InnoDB tablespace的一个区域中,我们称该区域为Double write Buffer。在向Double write Buffer写入成功后,再择机将数据拷贝到 阅读全文

posted @ 2014-04-03 01:38 Still water run deep 阅读(270) 评论(0) 推荐(0)

MySQL库目录下db.opt文件的作用
摘要:细心的朋友可能会发现有时候在某些库目录下有个 db.opt 文件,那这个文件是干什么用的呢?如果你用vi等编辑器打开看的话,内容很简单,是用来记录该库的默认字符集编码和字符集排序规则用的。也就是说如果你创建数据库指定默认字符集和排序规则,那么后续创建的表如果没有指定字符集和排序规则,那么该新建的表将采用db.opt文件中指定的属性。/* Set table default charset, if not set SYNOPSIS set_table_default_charset() create_info Table create information DE... 阅读全文

posted @ 2014-04-03 00:28 Still water run deep 阅读(1239) 评论(0) 推荐(0)

MySQL:BlackHole
摘要:MySQL:BlackHole顾名思义BlackHole就是黑洞,只有写入没有输出.现在就来实验一下吧首先查看一下MySQL支持的存储引擎mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine | Support | Comment | Transactions | ... 阅读全文

posted @ 2014-04-02 23:15 Still water run deep 阅读(443) 评论(0) 推荐(0)

如何最快地实现 ALTER TABLE
摘要:如果您不了解ALTER TABLE的语法,可以先参考:http://dev.mysql.com/doc/refman/5.1/en/alter-table.html使用ALTER TABLE 可以修改一张表的结构。比如,添加或者删除一个字段,创建或者删除一个索引,修改一个字段的类型,或者重命名一个字段,或者表的一些元信息。我们还可以通过ALTER TABLE来修改表引擎。在大多数情况下,ALTER TABLE通过建立一个原表的拷贝,并在拷贝上修改,然后删除原表,最后修改拷贝的名称为原表名来实现。在执行ALTER TABLE的时候是不能更新(update or insert)数据的, 但是其他的 阅读全文

posted @ 2014-04-02 01:40 Still water run deep 阅读(644) 评论(0) 推荐(0)

Working with large data sets in MySQL
摘要:What does working with large data sets in mySQL teach you ? Of course you have to learn a lot about query optimization, art of building summary tables and tricks of executing queries exactly as you want. I already wrote about development and configuration side of the problem so I will not go to deta 阅读全文

posted @ 2014-03-17 00:12 Still water run deep 阅读(260) 评论(0) 推荐(0)

导航