Mysqlデータベースの詳しい説明 Binlogログの使用コードまとめ
- 黄舟オリジナル
- 2017-03-23 13:34:151866ブラウズ
以下のエディターは、MysqlデータベースのBinlogログの使用状況の概要を提供します(必読の記事)。編集者はこれが非常に良いものだと思ったので、皆さんの参考として今から共有します。エディターに従って、binlog バイナリ ログが mysql データベースにとってどれほど重要であるかを見てみましょう。ここでは詳細には触れません。以下は、私の日々の操作経験に基づき、オンライン参考資料と組み合わせた binlog ログの使用の概要です:
1. binlog ログの概要 binlog ログは次の目的で使用されます。すべての更新データ、またはデータを更新した可能性のあるすべてのステートメント (例: どの行とも一致しなかった DELETE
) を記録します。ステートメントは、データの変更を記述する「イベント」として保存されます。
データ更新用のバイナログがあるため、リアルタイムバックアップに使用したり、マスター/スレーブのマスタースレーブレプリケーションと組み合わせたりすることができます。
log_bin
このパラメータを設定すると、binlog 機能を有効にしてパス名を指定することになります
このパラメータを設定すると、バイナリのパスと名前を指定することになりますインデックスファイル
このパラメータは、指定されたデータベースのバイナリログのみが記録されることを示します
このパラメータは、指定されたデータベースのバイナリログが記録されないことを示します
cache _size
this このパラメータは、binlog によって使用されるメモリの最大サイズを示します
このパラメータは、binlog によって使用されるメモリ サイズを示します。
変数binlog_cache_useおよびbinlog_cache_disk_use。
バイナリログキャッシュを使用するトランザクションの数
バイナリログキャッシュを使用するが、binlog_cache_size 値を超え、トランザクション内のステートメントを保存するために一時ファイルを使用するトランザクションの数
max_binlog_size Binlog の最大値とデフォルト値は 1GB です。特に、Binlog が最大値に近く、比較的大きなトランザクションが発生した場合、整合性を確保するために、この設定は Binlog のサイズを厳密に制御できません。トランザクションのログを切り替えることは不可能です。アクションは、トランザクションが終了するまで、トランザクションのすべての SQL を現在のログに記録することしかできません
sync_binlog
このパラメータは、mysql のパフォーマンスと整合性に直接影響します。
sync_binlog=0
トランザクションが送信されると、Mysql は binlog_cache 内のデータを Binlog ファイルに書き込むだけで、
ファイル システム
ファイルシステムが同期を行うタイミングを決定できるようにします。これにより、最高のパフォーマンスが得られます。
sync_binlog=n
Mysql のデフォルト設定は sync_binlog=0 です。これは、現時点では、必須のディスク更新命令が実行されないことを意味しますが、リスクも最大になります。システムがクラッシュすると、ファイル システム キャッシュ内のすべての Binlog 情報が失われます
4) Binlog の削除
a) Binlog の自動削除
binlog パラメータ (expire_logs_days) を使用して、mysql による binlog の自動削除を実現します
mysql> show binary logs; mysql> show variables like 'expire_logs_days'; //该参数表示binlog日志自动删除/过期的天数，默认值为0，表示不自动删除 mysql> set global expire_logs_days=3; //表示日志保留3天，3天后就自动过期。b) binlog の手動削除
mysql> reset master; //删除master的binlog，即手动删除所有的binlog日志 mysql> reset slave; //删除slave的中继日志 mysql> purge master logs before '2012-03-30 17:20:00'; //删除指定日期以前的日志索引中binlog日志文件 mysql> purge master logs to 'binlog.000002'; //删除指定日志文件的日志索引中binlog日志文件 mysql> set sql_log_bin=1/0; //如果用户有super权限，可以启用或禁用当前会话的binlog记录 mysql> show master logs; //查看master的binlog日志列表 mysql> show binary logs; //查看master的binlog日志文件大小 mysql> show master status; //用于提供master二进制日志文件的状态信息 mysql> show slave hosts; //显示当前注册的slave的列表。不以--report-host=slave_name选项为开头的slave不会显示在本列表中 mysql> flush logs; //产生一个新的binlog日志文件
5) binlog をクリアするときの mysql への影響 如果有一个活跃的slave从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误；不过如果slave从属服务器是关闭的（或master-slave主从关系关闭），并且碰巧清理了其想要读取的日志之一，则slave从属服务器启动后不能复制；当从属服务器正在复制时，本语句可以安全运行，不需要停止它们。 6）binglog的查看 通过mysqlbinlog命令可以查看binlog的内容 解析binlog格式： 位置 位于文件中的位置，“at 196”说明“事件”的起点，是以第196字节开始；“end_log_pos 294”说明以第294字节结束 事件发生的时间戳：“120330 17:54:46” 事件执行时间 事件执行花费的时间:"exec_time=28" 错误码 错误码为：“error_code=0” 服务器的标识 服务器的标识id：“server id 1” 注意下面几点： 1.mysql的日志切不可想象是可以恢复到任何时间的状态，这个恢复是有前提的！ 既然是回放操作，那么就得注意了，如果是执行了两次恢复那就相当于是回放了两次，后果可想而知。 所以： 1）恢复前务必先备份数据. 2）由于二进制文件多,并且需要恢复的数据跨度大,可以考虑将日志文件合并在恢复. 2. 开启binlog日志功能 要想通过日志恢复数据库，必须首先在my.cnf文件里定义，log-bin=mysql-bin，这样产生的binlog日志名就是以mysql-bin命名的 3.什么时候会生成新的binlog文件 1）在备份的时候加入--flush-logs 2）重新启动mysql服务的时候 特别提示，mysql每次启动都会重新生成一个类似mysql-bin.00000n的文件，如果你的mysql每天都要重新启动一次的话，这时候你就要特别注意不要选错日志文件了。 二、binlog日志格式介绍 （1）Mysql binlog日志有三种格式，分别是Statement、MiXED、ROW 1）Statement：每一条会修改数据的sql都会记录在binlog中 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。) 缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题). 使用以下函数的语句也无法被复制： * LOAD_FILE() 同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁 2）Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改 优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题 缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。 3）Mixedlevel: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。 Mixed日志说明： 在slave日志同步过程中，对于使用now这样的时间函数，MIXED日志格式，会在日志中产生对应的unix_timestamp()*1000的时间字符串，slave在完成同步时，取用的是sqlEvent发生的时间来保证数据的准确性。另外对于一些功能性函数slave能完成相应的数据同步，而对于上面指定的一些类似于UDF函数，导致Slave无法知晓的情况，则会采用ROW格式存储这些Binlog，以保证产生的Binlog可以供Slave完成数据同步。 （2）binlog基本配制与格式设定 1）基本配制 binlog日志格式可以通过mysql的my.cnf文件的属性binlog_format指定。如以下： binlog-do-db=需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可 2）Binlog日志格式选择 Mysql默认是使用Statement日志格式，推荐使用MIXED. 3）mysqlbinlog格式选择 mysql对于日志格式的选定原则:如果是采用 INSERT，UPDATE，DELETE 等直接操作表的情况，则日志格式根据 binlog_format 的设定而记录,如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何 都采用 SBR 模式记录 （3）Mysql Binlog日志分析 通过MysqlBinlog指令查看具体的mysql日志，如下: 1.开始事物的时间: SET TIMESTAMP=1350355892/*!*/; BEGIN 2.sqlevent起点 #at 1643330 :为事件的起点，是以1643330字节开始。 3.sqlevent 发生的时间点 #121016 10:51:32:是事件发生的时间， 4.serverId server id 1 :为master 的serverId 5.sqlevent终点及花费时间，错误码 end_log_pos 1643885:为事件的终点，是以1643885 字节结束。 execTime 0: 花费的时间 error_code=0:错误码 Xid:事件指示提交的XA事务 三、mysql日志（重点binlog日志）的优化说明 MySQL系统的伸缩性很强，既可以在充足的硬件资源环境下高效运行，也可以在极少资源环境下很好的运行， 下面着重分析一下MySQL的日志（主要是Binlog）对系统性能的影响，并根据日志的相关特性得出相应的优化思路。 1）日志产生的性能影响 MySQL的日志主要包括错误日志（ErrorLog），更新日志（UpdateLog），二进制日志（Binlog），查询日志（QueryLog），慢查询日志（SlowQueryLog）等。 在默认情况下，系统仅仅打开错误日志，关闭了其他所有日志，以达到尽可能减少IO损耗提高系统性能的目的。 一般情况下，在生产系统中很少有系统会打开查询日志。因为查询日志打开之后会将MySQL中执行的每一条Query都记录到日志中，会该系统带来比较大的IO负担，而带来的实际效益却并不是非常大。一般只有在开发测试环境中，为了定位某些功能具体使用了哪些SQL语句的时候，才会在短时间段内打开该日志来做相应的分析。 2）Binlog 相关参数及优化策略 我们首先看看Binlog的相关参数，通过执行如下命令可以获得关于Binlog的相关参数。 “binlog_cache_size"：在事务过程中容纳二进制日志SQL语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin选项)的前提下为每个客户端分配的内存，注意，是每个Client都可以分配设置大小的binlogcache空间。如果读者朋友的系统中经常会出现多语句事务的华，可以尝试增加该值的大小，以获得更有的性能。当然，我们可以通过MySQL的以下两个状态变量来判断当前的binlog_cache_size的状况：Binlog_cache_use和Binlog_cache_disk_use。 “max_binlog_cache_size”：和"binlog_cache_size"相对应，但是所代表的是binlog能够使用的最大cache内存大小。当我们执行多语句事务的时候，max_binlog_cache_size如果不够大的话，系统可能会报出“Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage”的错误。 “max_binlog_size”：Binlog日志最大值，一般来说设置为512M或者1G，但不能超过1G。该大小并不能非常严格控制Binlog大小，尤其是当到达Binlog比较靠近尾部而又遇到一个较大事务的时候，系统为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进入当前日志，直到该事务结束。这一点和Oracle的Redo日志有点不一样，因为Oracle的Redo日志所记录的是数据文件的物理位置的变化，而且里面同时记录了Redo和Undo相关的信息，所以同一个事务是否在一个日志中对Oracle来说并不关键。而MySQL在Binlog中所记录的是数据库逻辑变化信息，MySQL称之为Event，实际上就是带来数据库变化的DML之类的Query语句。 “sync_binlog”：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下： sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。 sync_binlog=n、n 回のトランザクション送信ごとに、MySQL は fsync などのディスク同期命令を実行して、binlog_cache 内のデータを強制的にディスクに書き込みます。 MySQL では、システムのデフォルト設定は sync_binlog=0 です。これは、現時点でのパフォーマンスは最高ですが、リスクも最大です。システムがクラッシュすると、binlog_cache 内のすべての binlog 情報が失われるためです。 「1」に設定すると、最も安全な設定になりますが、パフォーマンスの損失が最も大きくなります。 1 に設定すると、システムがクラッシュしても、実際のデータに実質的な影響を与えることなく、binlog_cache 内の最大 1 つの未完了のトランザクションが失われるためです。過去の経験と関連テストから判断すると、同時トランザクションが多いシステムでは、「sync_binlog」を 0 に設定したシステムと 1 に設定したシステム間の書き込みパフォーマンスの差が 5 倍以上になる可能性があります。 もう 1 つ: MySQL レプリケーション (レプリケーション) は、実際にはマスター側の Binlog を IO スレッドを介してネットワーク経由でスレーブ側にコピーし、SQL スレッドを介して Binlog 内のログを解析し、それをデータベースが実現しました。したがって、Binlog のサイズは、IO スレッドと、Msater とスレーブ間のネットワークに直接影響します。 MySQL で生成される Binlog の量は変更できません。クエリがデータベース内のデータを変更する限り、クエリに対応するイベントは Binlog に記録される必要があります。では、レプリケーションを最適化する方法はないのでしょうか?もちろん違います。MySQL レプリケーション環境では、実際には、コピーする必要がある DB またはテーブルを制御するためのパラメーターが 8 つあります: Binlog_Do_DB: どのデータベースを設定するか (スキーマ) が必要です。 Binlog を記録する; Binlog_Ignore_DB: Binlog を記録しないデータベース (スキーマ) を設定します。 Replicate_Do_DB: 複数の DB をカンマ (",") で区切って設定します。 ; Replicate_Ignore_DB: 無視できるデータベース (スキーマ) を設定します。 Replicate_Do_Table: 無視できるテーブルを設定します。 : Replicate_Do_Table と同じですが、wildcard で設定できます。 、私たちはとてもできます実際のニーズに応じてマスターを適切に制御することで、エンドからスレーブエンドへの Binlog の量が可能な限り少なくなり、マスターエンドからスレーブエンドへのネットワークトラフィックが減少し、IO スレッドの IO 量が削減されます。また、SQL を解析して適用する SQL スレッドの数も削減され、最終的にはスレーブでのデータ遅延が改善されます。 実際には、上記の 8 つのパラメーターのうち最初の 2 つはマスター側で設定され、最後の 6 つのパラメーターはスレーブ側で設定されます。最初の 2 つのパラメータと最後の 6 つのパラメータは機能的にはあまり直接的な関係はありませんが、これらはすべて MySQL レプリケーションを最適化するための同様の機能を有効にすることができます。もちろん、いくつかの違いがあります。主な違いは次のとおりです。 マスター側で最初の 2 つのパラメーターを設定すると、マスター側での Binlog 記録によって発生する IO の量が削減されるだけでなく、マスター側の IO スレッドを減らすと、読み取り量、つまりスレーブ側の IO スレッドに渡される Binlog の量が自然に減ります。この利点は、ネットワーク IO を削減し、スレーブ側 IO スレッドの IO 量を削減し、スレーブ側 SQL スレッドのワークロードを削減できるため、レプリケーションのパフォーマンスを最大限に最適化できることです。もちろん、イベントをコピーする必要があるかどうかの MySQL の判断は、イベントを生成した 而如果是在Slave端设置后面的六个参数，在性能优化方面可能比在Master端要稍微逊色一点，因为不管是需要还是不需要复制的Event都被会被IO线程读取到Slave端，这样不仅仅增加了网络IO量，也给Slave端的IO线程增加了RelayLog的写入量。但是仍然可以减少Slave的SQL线程在Slave端的日志应用量。虽然性能方面稍有逊色，但是在Slave端设置复制过滤机制，可以保证不会出现因为默认Schema的问题而造成Slave和Master数据不一致或者复制出错的问题。 3）慢查询日志Query Log 相关参数及使用建议 “log_slow_queries”参数显示了系统是否已经打开SlowQueryLog功能，而“long_query_time”参数则告诉我们当前系统设置的SlowQuery记录执行时间超过多长的Query。在MySQLAB发行的MySQL版本中SlowQueryLog可以设置的最短慢查询时间为1秒，这在有些时候可能没办法完全满足我们的要求，如果希望能够进一步缩短慢查询的时间限制，可以使用Percona提供的microslow-patch（件成为mslPatch）来突破该限制。mslpatch不仅仅能将慢查询时间减小到毫秒级别，同时还能通过一些特定的规则来过滤记录的SQL，如仅记录涉及到某个表的SlowQuery等等附加功能。 打开SlowQueryLog功能对系统性能的整体影响没有Binlog那么大，毕竟SlowQueryLog的数据量比较小，带来的IO损耗也就较小，但是，系统需要计算每一条Query的执行时间，所以消耗总是会有一些的，主要是CPU方面的消耗。如果大家的系统在CPU资源足够丰富的时候，可以不必在乎这一点点损耗，毕竟他可能会给我们带来更大性能优化的收获。但如果我们的CPU资源也比较紧张的时候，也完全可以在大部分时候关闭该功能，而只需要间断性的打开SlowQueryLog功能来定位可能存在的慢查询。 MySQL的其他日志由于使用很少（QueryLog）或者性能影响很少，在此就不做过多分析了。[root@localhost ~]# mysqlbinlog /home/mysql/binlog/binlog.000003 | more
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#120330 16:51:46 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.45-log created 120330 1
6:51:46
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
# at 196
#120330 17:54:15 server id 1 end_log_pos 294 Query thread_id=3 exec_time=2 error_code=0
SET TIMESTAMP=1333101255/*!*/;
insert into tt7 select * from tt7/*!*/;
# at 294
#120330 17:54:46 server id 1 end_log_pos 388 Query thread_id=3 exec_time=28 error_code=0
SET TIMESTAMP=1333101286/*!*/;
alter table tt7 engine=innodb/*!*/;
至少得有一个从日志记录开始后的数据库备份，通过日志恢复数据库实际上只是一个对以前操作的回放过程而已，不用想得太复杂。
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)binlog_format = MIXED
//binlog日志格式
log_bin =目录/mysql-bin.log
//binlog日志名
expire_logs_days = 7
//binlog过期清理时间
max_binlog_size 100m
//binlog每个日志文件大小
binlog-ignore-db=不需要备份的数据库苦命，如果备份多个数据库，重复设置这个选项即可
由于一些特殊使用，可以考虑使用ROWED，如自己通过binlog日志来同步数据的修改，这样会节省很多相关操作。对于binlog数据处理会变得非常轻松,相对mixed，解析也会很轻松(当然前提是增加的日志量所带来的IO开销在容忍的范围内即可)。///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SET TIMESTAMP=1350355892/*!*/;
BEGIN
/*!*/;
# at 1643330
#121016 10:51:32 server id 1 end_log_pos 1643885 Query thread_id=272571 exec_time=0 error_code=0
SET TIMESTAMP=1350355892/*!*/;
Insert into T_test….)
/*!*/;
# at 1643885
#121016 10:51:32 server id 1 end_log_pos 1643912 Xid = 0
COMMIT/*!*/;
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
但不管怎样，尽可能充足的硬件资源对MySQL的性能提升总是有帮助的。
由于日志的记录带来的直接性能损耗就是数据库系统中最为昂贵的IO资源。
特别注意：更新日志是老版本的MySQL才有的，目前已经被二进制日志替代。
但是在一般稍微重要一点的实际应用场景中，都至少需要打开二进制日志，因为这是MySQL很多存储引擎进行增量备份的基础，也是MySQL实现复制的基本条件。
有时候为了进一步的mysql性能优化，定位执行较慢的SQL语句，很多系统也会打开慢查询日志来记录执行时间超过特定数值（由我们自行设置）的SQL语句。
所以，在MySQL系统中，会对性能产生影响的MySQL日志（不包括各存储引擎自己的日志）主要就是Binlog了。
当然，其中也显示出了“innodb_locks_unsafe_for_binlog”这个Innodb存储引擎特有的与Binlog相关的参数：mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 16777216 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | IGNORE_ERROR |
| binlog_format | MIXED |
| binlog_gtid_simple_recovery | OFF |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlogging_impossible_mode | IGNORE_ERROR |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| simplified_binlog_gtid_recovery | OFF |
| sync_binlog | 1 |
+-----------------------------------------+----------------------+
19 rows in set (0.00 sec)
再来看看SlowQueryLog的相关参数配置。有些时候，我们为了定位系统中效率比较地下的Query语句，则需要打开慢查询日志，也就是SlowQueryLog。我们可以如下查看系统慢查询日志的相关设置：mysql> show variables like 'log_slow%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| log_slow_queries | ON |
+------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'long_query%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 1 |
+-----------------+-------+
1 row in set (0.01 sec)
以上がMysqlデータベースの詳しい説明 Binlogログの使用コードまとめの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。