摘要：If you’ve been reading enough database related forums, mailing lists or blogs you probably heard complains about MySQL being unable to handle more than 1.000.000 (or select any other number) rows by some of the users. On other hand it is well known with customers like Google, Yahoo, LiveJournal,Tech 阅读全文

摘要：Q:I read from many places that disabling index before loading a data table can significantly speed up the importing process. But innodb does not support "disable index". Particularly, I got a warning message after runningALTERTABLE mytable DISABLE KEYS;+-------+------+--------------------- 阅读全文

摘要：Q:I'm ready to move from MyISAM to InnoDB but wanted to know if there was a full list of things to look for? For example, I haven't seen any list mention that running DISABLE KEYS on an InnoDB table will throw a warning, except the manual page for ALTER TABLE. It's that kind of thing I n 阅读全文

摘要：Q:I read the following on this pagehttp://dev.mysql.com/doc/mysql/en/server-system-variables.htmlbulk_insert_buffer_size MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE. This variable limits the size o 阅读全文

摘要：There have been a few posts by Sheeri and Baron today on the MySQL sort_buffer_size variable. I wanted to add some more information about this buffer, what is impacted when it is changed, and what to do about it?The first thing you need to know is the sort_buffer_size is a per session buffer. That i 阅读全文

摘要：Q:I am MySQL on server with 6GB RAM. I need to know what is the difference between myisam_sort_buffer_size and sort_buffer_size?I have following size set to them:myisam_sort_buffer_size = 8Msort_buffer_size = 256MPlease also mention if these values are fine or need adjustments?ThanksA:sort_buffer_si 阅读全文

摘要：1.环境Mysql主从Mysql版本：5.1.49-log系统：RedHatEnterpriseLinuxServerrelease5.464bit2.表面现象数据库操作变慢，如用主键作为条件查询，有时也会超过1秒；主库IO使用率一直在90%以上（平常io比较低20%以内），dirty页占总数的90%左右，脏页刷不完；持续了几个小时~3.从系统上看主库的iostat的使用率一直在90%以上，Mysqldata所在目录的大小一直没变；主库的ib_logfile20分钟切换一次（主库上面的innodblog写得还是比较频繁），而从库的ib_logfile4小时切换一次。4.从mysql上看在主库B 阅读全文

摘要：We receive this question about the ibdata1 file in MySQL very often inPercona Support.The panic starts when the monitoring server sends an alert about the storage of the MySQL server – saying that the disk is about to get filled.After some research you realize that most of the disk space is used by 阅读全文

摘要：Wheninnodb_file_per_tableisOFFand all data is going to be stored inibdatafiles. If you drop some tables of delete some data then there is no any other way to reclaim that unused disk space exceptdump/reloadmethod.WhenInnodb_file_per_tableisON, each table stores data and indexes in it’s owntablespace 阅读全文

摘要：When working with InnoDB, you have two ways for managing the tablespace storage:Throw everything in one big file (optionally split).Have one file per table.I will discuss the advantages and disadvantages of the two options, and will strive to convince thatinnodb_file_per_tableis preferable.A single 阅读全文

摘要：innodb这种引擎，与MYISAM引擎的区别很大。特别是它的数据存储格式等.对于innodb的数据结构，首先要解决两个概念性的问题: 共享表空间以及独占表空间。什么是共享表空间和独占表空间共享表空间以及独占表空间都是针对数据的存储方式而言的。共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1 初始化为10M。独占表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储 阅读全文

摘要：Q:How do I see what the character set that a MySQL database, table and column are in? Is there something likeSHOW CHARACTER SETFOR mydatabase;andSHOW CHARACTER SETFOR mydatabase.mytable;andSHOW CHARACTER SETFOR mydatabase.mytable.mycolumn;A:How do I see what the character set that a MySQL database, 阅读全文

摘要：背景有同学问到这样一个问题：原来的binlog格式是statement，为什么执行了set global binlog_format='row'和set binlog_format='row'，在binlog里面还是会看到有生成statement格式的事件？变量分类很多文章都说到MySQL的按照可见性范围分成两类session和global。实际上是三类session_only, both, global_only.如下图见到的关系。session_only是仅线程级别意义的，比如last_insert_idglobal_only是仅全局级别有意义的，比如syn 阅读全文

摘要：正确了解MySQL的字符集问题，能够从根本上解决乱码的困扰。首先，MySQL的字符集问题主要是两个概念，一个是Character Sets，一个是Collations，前者是字符内容及编码，后者是对前者进行比较操作的一些规则。这两个参数集可以在数据库实例、单个数据库、表、列等四个级别指定。对于使用者来说，一般推荐使用utf8编码来存储数据。而要解决乱码问题，不单单是MySQL数据的存储问题，还和用户的程序文件的编码方式、用户程序和MySQL数据库的连接方式都有关系。首先，MySQL有默认的字符集，这个是安装的时候确定的，在编译MySQL的时候可以通过DEFAULT_CHARSET=utf8和D 阅读全文

摘要：背景：假设这么一个情况，你是某公司mysql-DBA，某日突然公司数据库中的所有被人为删了。尽管有数据备份，但是因服务停止而造成的损失上千万，现在公司需要查出那个做删除操作的人。但是拥有数据库操作权限的人很多，如何排查，证据又在哪？是不是觉得无能为力？mysql本身并没有操作审计的功能，那是不是意味着遇到这种情况只能自认倒霉呢？本文就将讨论一种简单易行的，用于mysql访问审计的思路。关键字：init—connect,binlog,trigger概述：其实mysql本身已经提供了详细的sql执行记录–general log ，但是开启它有以下几个缺点无论sql有无语法错误，只要执行了就会记录， 阅读全文

摘要：服务器为每个连接的客户端执行的字符串。字符串由一个或多个SQL语句组成。要想指定多个语句，用分号间隔开。例如，每个客户端开始时默认启用autocommit模式。没有全局服务器变量可以规定autocommit默认情况下应禁用，但可以用init_connect来获得相同的效果：SET GLOBAL init_connect='SET AUTOCOMMIT=0';还可以在命令行或选项文件中设置该变量。要想使用选项文件设置变量，应包括下述行：[mysqld]init_connect='SET AUTOCOMMIT=0'请注意init_connect的内容并不为拥有SUP 阅读全文

摘要：Q:MySQL supports running multiplemysqldon the same server. One of the ways is to usemysqld_multi.If the default MySQL server instance (as configured in the[mysqld]section inmy.cnf) useslog-bin, it enables the binary log for all the other instances ([mysqld1],[mysqld2], etc).How can we override the s 阅读全文