当前位置:首页>软件介绍>MySQL之存储引擎 查询:
     
MySQL之存储引擎

        存储引擎的概念是MySQL的一个特性,可简单理解为表类型;每一个表都有一个存储引擎,可在创建时指定,也可之后使用ALTER TABLE语句修改,都是通过ENGINE关键字设置的;若创建时没有指定,则为默认存储引擎,默认存储引擎也可通过参数文件中default-table-type参数修改。 

        ----查看当前默认存储引擎,默认为MyISAM 

        mysql> show variables like 'table_type'; 

        ----查看当前数据库支持的存储引擎 

        mysql> show engines G; 

        下面介绍几个重要的存储引擎如下: 从输出结果可看到支持多个存储引擎,

        MyISAM 

        默认存储引擎,不支持事务、外键,但访问速度快,对事务完整性不要求,适合于以SELECT/INSERT为主的表;每个MyISAM物理上存储为3个文件,文件名与表名相同,扩展名分别为:.frm(存储表定义)、MYD(MYData存储数据)、MYI(MYIndex存储索引),其中数据文件和索引文件可以放置在不同目录,平衡I/O。 

        数据文件和索引文件的路径,需要在创建表时通过DATA DIRECTORY和INDEX DIRECTORY语句指定,需要绝对路径,且具有访问权限。 

        MySQL类型的表可能因各种原因损坏,可通过CHECK TABLE语句检查表的健康,使用REPAIR TABLE语句修改。 

        MySQL类型的表支持3中不同的存储格式,如下: 

        静态表:默认存储格式,字段长度固定,存储迅速,容易缓存,缺点是占用空间多。注意:字段存储按照

        宽度定义补足空格,应用访问时去掉空格,若字段本身就带有空格,也会去掉,这点特别注意。 动态表:变长字段,记录不是固定长度,优点是占用空间少,但频繁的更新删除操作会产生碎片,需要定期执行OPTIMIZE TABLE语句或myisamchk –r命令来改善,出现故障时难以恢复。 压缩表:由myisampack工具创建,每个记录单独压缩,访问开支小,占用空间小。 InnoDB具有提交、回滚、崩溃恢复的事务安全,相对MySQL来说,写处理能力差些,且会占用较多磁盘空间以保留数据和索引。具有以下不同于其他引擎的特点: 

        (1)自动增长列 

        InnoDB表可定义自动增长列,若插入的值为0或空时,则实际插入的值为自动增长后的值;可通过ALTER table语句强制设置自动增长列的初始值,默认从1开始,该值保存在内存中,若数据库重启,则会丢失;可使用LAST_INSERT_ID()查询当前线程最后插入记录使用的值,若一次插入多条记录,则返回第一条记录使用的自动增长值。 

        对于InnoDB表,自动增长列必须是索引,或者是组合索引的第一列;对于MyISAM表,可以为组合索引的其他列,插入记录后,自动增长列是按照组合索引的前面几列进行排序后递增的。 

        (2)外键约束 

        只有InnoDB引擎支持外键约束;创建索引时,可指定删除、更新父表时,对子表的相应操作;RESTRICT/NO ACTION相同,是指限制在子表有关联记录的情况下父表不能更新;CASCADE表示父表在更新或删除时,更新或删除子表对应记录;SET NULL表示父表在更新或删除时,子表对应字段被SET NULL。 (3)存储格式 

        InnoDB存储表和索引有两种方式: 

        使用共享表空间存储――表结构保存在.frm文件中;数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中,可以为多个文件使用多表空间存储――表结构仍然存储在.frm文件中,但每个表的数据和索引单独保存在.ibd中。若为分区表,则每个分区对应单独的.ibd文件,文件名为表名+分区名使用多表空间存储,需设置参数innodb_file_per_table,并重启服务才可生效,只对新建表有效 MEMORY该存储引擎使用存在于内存中的内容来创建表,每个表实际对应一个磁盘文件,格式为.frm;这类表因为数据在内存中,且默认使用HASH索引,所以访问速度非常快;但一旦服务关闭,表中的数据会丢失。 

        每个MEMORY表可以放置数据量的大小受max_heap_table_size系统变量的约束,初始值为16MB,可按需求增大;此外,在定义MEMORY表时可通过MAX_ROWS子句定义表的最大行数。 

        该存储引擎主要用于那些内容稳定的表,或者作为统计操作的中间表;对该类表需谨慎,因为数据并没有实际写入磁盘,一旦重启,则会丢失。 

        MERGE 

        该引擎是一组MyISAM表的组合,这些表必须结构相同,MERGE表本身没有数据;对MERGE表查询、更新、删除操作实际上是对内部实际MyISAM表进行的;对于MERGE表的插入,是通过INSERT_METHOD子句定义插入的表,可以有3个不同的值,使用FIRST/LAST值使得插入操作被相应地作用在第一个或最后一个表上,不定义这个子句或定义为NO表示不能对这个MERGE表执行插入操作。 

        DROP操作只是删除MERGE地定义,对内部表没有任何影响。 

        MERGE表在磁盘上保存为两个文件,文件名以表名开始,.frm文件存储表定义,.MRG文件包含组合表地信息,如:由那些表组成、插入新记录时的依据。 

        MERGE表不能智能的将记录插入对应的表中,而分区表可以,这是两者的区别。 

        MySQL: MyISAM和InnoDB的区别 

        InnoDB和MyISAM是在使用MySQL最常用的两个表类型,各有优缺点,视具体应用 而定。基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行数度比InnoDB 类型更快,但是不提供事务支持,而InnoDB提供事务支持已经外部键等高级数据库功能。 移值性: 

        MyISAM类型的二进制数据文件可以在不同操作系统中迁移。也就是可以直接从Windows系统拷贝到linux系统中使用。 

        MyIASM是IASM表的新版本,有如下扩展: 

        二进制层次的可移植性。 

        NULL列索引。 

        对变长行比ISAM表有更少的碎片。 

        支持大文件。 

        更好的索引压缩。 

        更好的键吗统计分布。 

        更好和更快的auto_increment处理。 

        支持全文搜索,不过它们是事务不安全的,而且也不支持外键。如果事务回滚将会造成不完全回滚,从而不具备原子性。 

        读锁和写锁是互斥的,从而读写操作是串行的,MyISAM表不太适合于有大量更新操作和查询操作应用的原因。因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。 

        数据行锁定: 不支持,只有表锁定 

        以下是一些细节和具体实现的差别: 

        1.InnoDB不支持FULLTEXT类型的索引。 

        2.InnoDB中不保存表的 具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的。 

        3.对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。 

        4.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。 

        5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。 

        如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,同样应该使用InnoDB表。 

        对于支持事务的InnoDB类型的表来说,影响速度的主要原因是AUTOCOMMIT默认设置是打开的,而且程序没有显式调用BEGIN 开始事务,导致每插入一条都自动提交,严重影响了速度。可以在执行sql前调用begin,多条sql形成一个事物(即使autocommit打开也可以),将大大提高性能。 

        查看autocommit:select @@autocommit; 

        设置autocommit:set autocommit=0; 

        MyISAM和InnoDB存储引擎性能差别并不是很大,针对InnoDB来说,影响性能的主要是 innodb_flush_log_at_trx_commit 这个选项,如果设置为1的话,那么每次插入数据的时候都会自动提交,导致性能急剧下降,应该是跟刷新日志有关系,设置为0效率能够看到明显提升,当然,同样你可以SQL中提交“SET AUTOCOMMIT = 0”来设置达到好的性能。 

        另外,InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”

        要想在创建表时指定存储引擎,可使用ENGINE参数: 

        CREATE TABLE engineTest( 

        id INT 

        ) ENGINE = MyISAM; 

        要想更改已有表的存储引擎,可使用ALTER TABLE语句: 

        ALTER TABLE engineTest ENGINE = ARCHIVE; 

        (1) 存储过程 

        存储过程是数据库服务器端的一段程序. 

        mysql的存储过程,只有版本是mysql5.0或以上的才有此特性.  

        (2)什么时候需要用存储过程 

        存储过程通常是一些经常要执行的任务,这些任务往往是针对大量的记录而进行的。在服务器上执行存储过程,可以 

        改善应用程序的性能。这是因为: 

        2.1. 服务器往往具有强大的计算能力和速度。 

        2.2 避免把大量的数据下载到客户端,减少网络上的传输量。 

        2.3 存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 

        2.4 存储过程可以重复使用,可减少数据库开发人员的工作量 

        2.5 安全性高,可设定只有某此用户才具有对指定存储过程的使用权 

        我们大家都知道MySQL 存储过程是从 MySQL 5.0 开始逐渐增加新的功能。存储过程在实际应用中也是优点大于缺点。不过最主要的还是执行效率和SQL 代码封装。特别是 SQL 代码封装功能,如果没有存储过程,在外部程序访问数据库时(例如 PHP),要组织很多 SQL 语句。特别是业务逻辑复杂的时候,一大堆的 SQL 和条件夹杂在 PHP 代码中,让人不寒而栗。现在有了 MySQL 存储过程,业务逻辑可以封装存储过程中,这样不仅容易维护,而且执行效率也高。 

        一、MySQL 创建存储过程 

        “pr_add” 是个简单的 MySQL 存储过程,这个MySQL 存储过程有两个 int 类型的输入参数 “a”、“b”,返回这两个参数的和。 

        1. drop procedure if exists pr_add; 

        计算两个数之和 

        1. create procedure pr_add 2. ( 

        3. a int, 

        4. b int 

        5. ) 

        6. begin 

        7. declare c int; 

        8. if a is null then 9. set a = 0; 

        10. end if; 

        11. if b is null then 12. set b = 0; 

        13. end if; 

        14. set c = a + b; 

        15. select c as sum; 

        16. /* 

        17. return c; 

        不能在 MySQL 存储过程中使用。return 只能出现在函数中。 

        1. */ 

        2. end; 

        二、调用 MySQL 存储过程 

        1. call pr_add(10, 20); 执行 MySQL 存储过程,存储过程参数为 MySQL 用户变量。 

        1. set @a = 10; 

        2. set @b = 20; 

        3. call pr_add(@a, @b); 三、MySQL 存储过程特点 

        创建 MySQL 存储过程的简单语法为: 1. create procedure 存储过程名字() 2. ( 

        3. [in|out|inout] 参数 datatype 4. ) 

        5. begin 

        6. MySQL 语句; 

        7. end; 

        MySQL 存储过程参数如果不显式指定“in”、“out”、“inout”,则默认为“in”。

        习惯上,对于是“in” 的参数,我们都不会显式指定。 

        1. MySQL 存储过程名字后面的“()”是必须的,即使没有一个参数,也需要“()” 2. MySQL 存储过程参数,不能在参数名称前加“@”,如:“@a int”。下面的创建存储过程语法在 MySQL 中是错误的(在 SQL Server 中是正确的)。 MySQL 存储过程中的变量,不需要在变量名字前加“@”,虽然 MySQL 客户端用户变量要加个“@”。 1. create procedure pr_add 

        2. ( 

        3. @a int, -- 错误 

        4. b int -- 正确 

        5. ) 

        3. MySQL 存储过程的参数不能指定默认值。 

        4. MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server 存储过程必须加 “as” 关键字。 

        1. create procedure pr_add 

        2. ( 

        3. a int, 

        4. b int 

        5. ) 

        6. as -- 错误,MySQL 不需要 “as” 

        7. begin 

        8. mysql statement ...; 

        9. end; 

        5. 如果 MySQL 存储过程中包含多条 MySQL 语句,则需要 begin end 关键字。 1. create procedure pr_add 

        2. ( 

        3. a int, 

        4. b int 

        5. ) 

        6. begin 

        7. mysql statement 1 ...; 

        8. mysql statement 2 ...; 

        9. end; 

        6. MySQL 存储过程中的每条语句的末尾,都要加上分号 “;” 1. ... 

        2. declare c int; 

        3. if a is null then 

        4. set a = 0; 

        5. end if; 

        6. ... 

        7. end; 

        7. MySQL 存储过程中的注释。 

        1. /* 

        2. 这是个 

        3. 多行 MySQL 注释。 

        4. */ 

        5. declare c int; -- 这是单行 MySQL 注释 (注意 -- 后至少要有一个空格) 

        6. if a is null then # 这也是个单行 MySQL 注释 7. set a = 0; 

        8. end if; 

        9. ... 

        10. end; 

        8. 不能在 MySQL 存储过程中使用 “return” 关键字。 1. set c = a + b; 

        2. select c as sum; 

        3. /* 

        4. return c; -- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。 

        5. */ 

        6. end; 

        9. 调用 MySQL 存储过程时候,需要在过程名字后面加“()”,即使没有一个参数,也需要“()” 

        1. call pr_no_param(); 

        10. 因为 MySQL 存储过程参数没有默认值,所以在调用 MySQL 存储过程时候,不能省略参数。可以用 null 来替代。 

        备份 

        备份MySQL数据库的命令 

        mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql 备份MySQL数据库为带删除表的格式 

        备份MySQL数据库为带删除表的格式,能够让该备份覆盖已有数据库而不需要手动删除原有数据库。 

        mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql 直接将MySQL数据库压缩备份 

        mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz 

        备份MySQL数据库某个(些)表 

        mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql 

        同时备份多个MySQL数据库 

        mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql 

        仅仅备份数据库结构 

        mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql 

        备份服务器上所有数据库 

        mysqldump –all-databases > allbackupfile.sql 

        还原MySQL数据库的命令 

        mysql -hhostname -uusername -ppassword databasename < backupfile.sql 还原压缩的MySQL数据库 

        gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename 将数据库转移到新服务器 

        mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename 

        Binlog 

        如果MySQL服务器启用了二进制日志,你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如,从你最后一次备份)直到现在或另一个指定的时间点的数据。关于启用二进制日志的信息,参见5.11.3节,“二进制日志”。对于 mysqlbinlog的详细信息,参见mysql

        手册8.6节,“mysqlbinlog:用于处理二进制日志文件的实用工具”。 

        要想从二进制日志恢复数据,你需要知道当前二进制日志文件的路径和文件名。一般可以从选项文件(即my.cnf or my.ini,取决于你的系统)中找到路径。如果未包含在选项文件中,当服务器启动时,可以在命令行中以选项的形式给出。启用二进制日志的选项为-- log-bin。要想确定当前的二进制日志文件的文件名,输入下面的MySQL语句: 

        SHOW BINLOG EVENTS G 

        你还可以从命令行输入下面的内容: 

        mysql --user=root -pmy_pwd -e 'SHOW BINLOG EVENTS G' 

        将密码my_pwd替换为服务器的root密码。 

        1. 指定恢复时间 

        对于MySQL 4.1.4,可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。举例说 明,假设在今天上午10:00(今天是2005年4月20

        日),执行SQL语句来删除一个大表。要想恢复表和数据,你可以恢复前晚上的备份,并输入: 

        mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 

        | mysql -u root -pmypwd 

        该命令将恢复截止到在--stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句,可能你想要恢复后面发生的活动。根据这些,你可以用起使日期和时间再次运行mysqlbinlog: 

        mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 

        | mysql -u root -pmypwd 

        在该行中,从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。 

        2. 指定恢复位置 

        也可以不指定日期和时间,而使用mysqlbinlog的选项--start-position和--stop-position来指定 日志位置。它们的作用与起止日选项相同,不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法,特别是当由于破坏性SQL语句同时发生许多事 务的时候。要想确定位置号,可以运行mysqlbinlog寻找执行了不期望的事务的时间范围,但应将结果重新指向文本文件以便进行检查。操作方法为: 

        mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00" 

        /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql 

        该命令将在/tmp目录创建小的文本文件,将显示执行了错误的SQL语句时的 SQL语句。你可以用文本编辑器打开该文件,寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作,应进行注释。用log_pos加 一个数字来标记位置。使用位置号恢复了以前的备份文件后,你应从命令行输入下面内容: 

        mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 

        | mysql -u root -pmypwd 

        mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 

        | mysql -u root -pmypwd 

        上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句,恢复的数据和相关MySQL日志将反应事务执行的原时间。 

        Binlog是mysql以二进制形式打印的日志,它默认不加密,不压缩。每个正常的binlog文件头部,有4个字节的标记,值为0xfe 0x62 0x69 0x6e。LOG_EVENT是binlog里的单位,即正常情况下binlog按照逐LOG_EVENT的形式增长。除去头部的标记,binlog就是一个LOG_EVENT的序列。每个LOG_EVENT都独立单元,没有互相引用的关系,它也有自己的二进制头部,主要是记录了时间戳、类型标记等描述信息。 

        Mysql把磁盘操作的实现封装在IO_CACHE结构里,这也方便了我们对binlog的研究和描述,后文如果没有特别说明,读写binlog与读写IO_CACHE的含义相同。为了解mysql写入binlog的过程,可以找一个sql语句的处理过程进行跟踪。以update为例,在最简单的情况下,mysql会先调用为存储引擎开放的接口ha_update_row,然而执行binlog_query对binlog进行写操作。 

        这样处理的原因是,在主从备份的场景下,如果主库先写入binlog成功、在执行update的过程中crash,从库有可能执行update成功,此时主库重启之后,与从库的数据不一致。如果update操作发生在事务性的表上,在写入binlog之后会执行开放接口ha_autocommit_or_rollback,由存储引擎判断操作结果。 

        在主从备份的场景下,主库相当于server,从库相当于client,双方采用tcp短连接。从库发出读取日志的请求,主库接收请求、读取本地binlog、然后发送给从库。从库接收日志,进行简单校验后写本地日志,称为relay log。此处从库的流程专门由一个线程负责,称为同步io线程。从库还有一个线程,称为同步sql线程。它的行为是,定期读取relay log,解析并执行同步过来的sql语句。 

        下面回答几个问题: 

        1.binlog的格式? 

        二进制顺序存储,不加密,不压缩。 

        2.binlog使用WAL吗? 

        No。 

        3.主库发送binlog,是使用内存里的copy吗? 

        无法确定,很有可能是先从磁盘上读一份,然后发送。 

        4.relaylog使用WAL吗? 

        Yes。从库接收到日志后,会先写relay log。 

        5.binlog和relaylog的SQL是否一致? 

        在网络传输正确性可靠的前提下,yes。 

        提一个问题:既然binlog不使用WAL,那么在主从场景下,mysql异常之后,主库和从库是否会不一致呢? 

        之前有个问题一直没弄明白:既然mysql是先做数据操作、再写binlog,如果写binlog的时候失败,mysql又crash,数据怎么办? 

        答案是由存储引擎决定数据。可以把mysql和它的存储引擎分开看,因为mysql只是一个框架,而不是一个实现。binlog是mysql自己的日志,而事务是由存储引擎本身保证的。 

        以update为例,mysql做的事情简单分为: 

        1. 修改数据update。 

        2. 写binlog。 

        3. 如果当前处理的表是一个事务性的表,则commit或rollback。 

        注意此处的update和commit/rollback都由存储引擎实现,mysql只是站在逻辑的高度上理解这些操作。 

        对于事务型的引擎innodb,它本身有日志保证数据的一致性。在innodb的实现中,update修改数据之前,会新建一个事务,并建立一个回滚点。而在innodb提供的commit/rollback接口会提交/回滚事务。 

        因此对innodb而言,每条SQL语句的事务,其实包含了binlog的写操作。然而即使是这样,innodb仍然无法保证binlog和数据的一致性,因为innodb在写commit成功后crash,回滚操作不会回滚binlog。按照手册上的说法,把--innodb-support-xa设置为1,同时保证sync_binlog=1,才能保证innodb的binlog和数据一致。 

        对于非事务型的引擎myisam,没有commit/rollback的机会,因此在异常情况下,数据会和binlog不一致。 

        一、简介 

        binlog又叫二进制日志文件,它会将mysql中所有修改数据库数据的Query以二进制的形式记录到日志文件中,如:create,insert,drop,update等;(对于select操作则不会被记录到binlog里,因为它并没有修改数据库的数据)。binlog一般存储在数据目录下,并且命名为:mysql-bin.***(这个可以在配置文件中修改my.cnf:log-bin=mysql-bin,就是文件名的前缀;mysqld在每个 binlog 名后面添加一个数字扩展名。每次启动服务器或刷新日志时增加文件的大小大于max_binlog_size,一个事务不会被拆分开)。 

        binlog主要是用于保证数据完整的,如主从备份,通过从binlog文件中读取操作来在salve机上进行同样的操作,保证主从备份,当然不可能每次都从开始的地方redo,所以每条记录都有一个时间截TIMESTAMP。 

        二、简单的使用binlog 

        show binary logs; #显示binlog文件 

        purge binary logsto 'mysql-bin.**' #删除到**文件 

        bin/mysqlbinlog binlogfile #解析binlog文件 

        利用binlog恢复数据: 

        bin/mysqlbinlog --start-datetime='2011-7-7 18:0:0'--stop-datetime='2011-7-7 20:07:13' 

        data/mysql-bin.000008 |mysql -u root 

        三、类型 

        binlog的格式有三种,这也反应了mysql的复制技术:基于SQL语句的复制(statement-based replication, SBR),基于行的复制(row-based replication, RBR),混合模式复制(mixed-based replication, MBR)。相应地,binlog的格式也有三种:STATEMENT,ROW,MIXED。 

        mysql>showvariables like 'binlog_format' #查看binlog的格式 

        使用mysqlbinlog解析的binlog: 

        MIXED(STATEMENT): 

        # at 193(开始位置) 

        #110708 10:03:06(时间截) server id(产生该事件的服务id) 1 end_log_pos(日志的结束位置) 280 Query(事件类型) thread_id=10 exec_time=0 error_code=0 SETTIMESTAMP=1310090586/*!*/; 

        insert into tvalues(17) 

        /*!*/; 

        ROW模式: 

        BEGIN 

        /*!*/; 

        # at 174 

        # at 214 

        #110708 10:49:22server id 1 end_log_pos 214 Table_map: `test`.`t` mapped to 

        number 14 

        #110708 10:49:22server id 1 end_log_pos 248 Write_rows: table id 14 flags: 

        STMT_END_F 

        BINLOG ' 

        MnAWThMBAAAAKAAAANYAAAAAAA4AAAAAAAEABHRlc3QAAXQAAQMAAQ== MnAWThcBAAAAIgAAAPgAAAAAAA4AAAAAAAEAAf/+MgAAAA== '/*!*/; 

        # at 248 

        #110708 10:49:22server id 1 end_log_pos 317 Query thread_id=1 exec_time=0 error_code=0 

        SETTIMESTAMP=1310093362/*!*/; 

        COMMIT 

        STATEMENT是基于sql语句级别的binlog,每一条修改数据的sql都会被保存到binlog里;ROW是基于行级别的,他会记录每一行记录的变化,就是将每一行的修改都记录到binlog里面,记录的非常详细,但sql语句并没有在binlog里,在replication里面也不会因为存储过程触发器等造成Master-Slave数据不一致的问题,但是有个致命的缺点日志量比较大.由于要记录每一行的数据变化,当执行update语句后面不加where条件的时候或alter table的时候,产生的日志量是相当的大。MIXED:在默认情况下是statement,但是在某些情况下会切换到row状态,如当一个DML更新一个ndb引擎表,或者是与时间用户相关的函数等。在主从的情况下,在主机上如果是STATEMENT模式,那么binlog就是直接写now(),然而如果这样的话,那么从机进行操作的时间,也执行now(),但明显这两个时间不会是一样的,所以对于这种情况就必须把STATEMENT模式更改为ROW模式,因为ROW模式会直接写值而不是写语句(该案例是错误的,即使是STATEMENT模式也可以使用now()函数,具体原因以后再分析)。同样ROW模式还可以减少从机的相关计算,如在主机中存在统计写入等操作时,从机就可以免掉该计算把值直接写入从机。 

        四、binlog记录 

        每个binlog的开始都是由4个字节:fe 62 69 6e,组成的魔数(后面三个字节就是bin)。 然后接下来的就是一条记录的内容它包括:Common-Header,这部分不同版本的大小不一样,4.0以上的都是19个字节。在这个之后就是BODY。 

        Common-Header格式:(单位:字节) 

        Timestamp(4) Type(1) Server_id(4) Total_size(4) End_log_pos(4) Flag(2) Timestamp:从1970开始 

        Type:此log event type如FORMAT_DESCRIPTION_EVENT、QUERY、LOAD_EVENT等,其中每个binlog的第一条记录的类型都是FORMAT_DESCRIPTION_EVENT,它记录了该binlog的相关信息,如版本,这些信息对于后序分析binlog记录是有用的,所以对于任务要读取binlog的内容的工具都必须先读取第一条记录。QUERY包括我们经常操作的如:create,drop,update,insert等。 

        Server_id:创建这个事件的server id。防止循环主从导致的主机被从写。The master's server id (is preserved in therelay log; used to prevent from infinite loops in circular replication). 

        Total_size:该记录的大小,包括common_header及body。 

        End_log_pos:此下一条记录的开始位置。也是此条记录结束位置的上一个字节。 

        Flag:标志位。 

        QUERY类型的记录: 

        QUERY类型的记录除了开始的common-header之外,在body的开头是一个Post-header,然后之后才是真正的body内容。 

        Query Post-Header:(单位字节) 

        Thread_id(4) Exec_time(4) Db_len(1) Error_code(2) Status_var_len(2) Thread_id:is used to distinguish temporary tables that belong to differentclients. 

        Exec_time:The time from whenthe query started to when it was logged in the binlog, in 

        seconds.QUERY到达到这个binlog事件生成的时间间隔。 

        Db_len:当前数据库的名称长度。 

        Error_code:执行出错的错误号。 

        --binlog 以一种更有效的格式,并且是事务安全的方式包含更新日志中可用的所有信息。 --binlog 包含了所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个DELETE)的所有语句。语句以“事件”的形式保存,它描述数据更改。 

        --binlog 还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果你想要记录所有语句(例如,为了识别有问题的查询),你应使用一般查询日志。 

        --binlog 的主要目的是在恢复使能够最大可能地更新数据库,因为 binlog 包含备份后进行的所有更新。 

        --binlog 还用于在主复制服务器上记录所有将发送给从服务器的语句。 --运行服务器时若启用 binlog 则性能大约慢1%。但是, binlog 的好处,即用于恢复并允许设置复制超过了这个小小的性能损失。 

        --当--log-bin[=file_name]选项启动时,mysqld写入包含所有更新数据的SQL命令的日志文件。如果未给出file_name值, 默认名为-bin后面所跟的主机名。如果给出了文件名,但没有包含路径,则文件被写入数据目录。建议指定一个文件名。如果你在日志名中提供了扩展名(例如,--log-bin=file_name.extension),则扩展名被悄悄除掉并忽略。 --mysqld在每个 binlog 名后面添加一个数字扩展名。每次你启动服务器或刷新日志时该数字则增加。如果当前的日志大小达到max_binlog_size,还会自动创建新的 binlog 。如果你正使用大的事务, binlog 还会超过max_binlog_size:事务全写入一个 binlog 中,绝对不要写入不同的 binlog 中。 

        --为了能够知道还使用了哪个不同的 binlog 文件,mysqld还创建一个 binlog 索引文件,包含所有使用的 binlog 文件的文件名。默认情况下与 binlog 文件的文件名相同,扩展名为'.index'。你可以用--log-bin-index[=file_name]选项更改 binlog 索引文件的文件名。当mysqld在运行时,不应手动编辑该文件;如果这样做将会使mysqld变得混乱。 --binlog 格式有一些已知限制,会影响从备份恢复。 

        --默认情况下,并不是每次写入时都将 binlog 与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃,有可能 binlog 中最后的语句丢失了。要想防止这种情况,你可以使用sync_binlog全局变量(1是最安全的值,但也是最慢的),使 binlog 在每N次 binlog 写入后与硬盘同步。 

        如何管理 MySQL 的 binlog

        1、在 my.ini 中增加下述参数,指定保存更新到 binlog 的数据库:db_name,未在此指定的数据库将不记录 binlog 

        --binlog-do-db=db_name 

        2、在 my.ini 中增加下述参数,指定不保存更新到 binlog 的数据库:db_name --binlog-ignore-db=db_name 

        3、如果 binlog 已经产生,可以通过 SQL 命令行清除: 

        /* 

        * 要清理日志,需按照以下步骤: 

        * 1 在每个从属服务器上,使用SHOW SLAVE STATUS来检查它正在读取哪个日志。 

        * 2 使用SHOW MASTER LOGS获得主服务器上的一系列日志。 

        * 3 在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是更新的,这是清单上的最后一个日志。 

        * 4 制作您将要删除的所有日志的备份。(这个步骤是自选的,但是建议采用。) 

        * 5 清理所有的日志,但是不包括目标日志。 

        * 

        */ 

        /* 

        * 清除 binlog 

        * 

        * 为了执行RESET,您必须拥有RELOAD权限。 

        * 以下命令将删除列于索引文件中的所有 binlog,把 binlog 索引文件重新设置为空,并创建一个新的 binlog。 

        * (在以前版本的MySQL中,被称为FLUSH MASTER。) 

        */ 

        RESET MASTER; 

        /* 

        * 清除指定的 binlog 

        * 

        */ 

        PURGE MASTER LOGS TO 'mysql-bin.010'; /* 

        * 清除日期为 2006-06-06 06:06:06 以前的 binlog 

        * 

        * BEFORE变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式。MASTER和BINARY是同义词。 

        */ 

        PURGE MASTER LOGS BEFORE '2006-06-06 06:06:06'; 

        /* 

        * 清除3天前的 binlog 

        * 

        */ 

        PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); 

        


MySQL数据库同步MySQL常用语句
常用基本MySQL语句MySQL创建用户
MySQL学习分享MySQL常用命令用法
MySQL的部分用法MySQL读写分离Amoeba的实现
MySQL的分页的优化MySQL正则表达式的描述
MySQL的命名规则MySQL的information_schema数据库介绍
MySQL数据库知识详解MySQL LAST_INSERT_ID详解
MySQL安装与常用命令MySQL配置文件详细
信息发布:广州名易软件有限公司 http://www.myidp.net
  • 名易软件销售服务
  • 名易软件销售服务
  • 名易软件技术服务

  • MySQL之存储引擎