做单挣钱的网站,芭嘞seo,百度指数的功能,重庆景点门票价格一览表基本概念binlog是Mysql sever层维护的一种二进制日志#xff0c;与innodb引擎中的redo/undo log是完全不同的日志#xff1b;其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句#xff0c;记录了所有的DDL和DML(除了数据查询语句)语句#xff0c;并以事务的形式保存…基本概念binlog是Mysql sever层维护的一种二进制日志与innodb引擎中的redo/undo log是完全不同的日志其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句记录了所有的DDL和DML(除了数据查询语句)语句并以事务的形式保存在磁盘中还包含语句所执行的消耗的时间MySQL的二进制日志是事务安全型的。一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。作用主要有复制MySQL Replication在Master端开启binlogMaster把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的数据恢复通过mysqlbinlog工具恢复数据增量备份二进制日志包括两类文件二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句)语句事件。日志管理开启binlog修改配置文件 my.cnf配置 log-bin 和 log-bin-index 的值如果没有则自行加上去。log-binmysql-binlog-bin-indexmysql-bin.index这里的 log-bin 是指以后生成各 Binlog 文件的前缀比如上述使用master-bin那么文件就将会是master-bin.000001、master-bin.000002 等。log-bin-index 则指 binlog index 文件的名称这里我们设置为master-bin.index可以不配置。命令查看配置binlog开启后可以在配置文件中查看其位置信息也可以在myslq命令行中查看mysql show variables like %log_bin%;------------------------------------------------------------------------------| Variable_name | Value |------------------------------------------------------------------------------| log_bin | ON || log_bin_basename | D:\Program Files\MySQL\data\mysql-bin || log_bin_index | D:\Program Files\MySQL\data\mysql-bin.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |------------------------------------------------------------------------------6 rows in set (0.07 sec)查看binlog文件列表mysql show binary logs;----------------------------------------| Log_name | File_size | Encrypted |----------------------------------------| mysql-bin.000001 | 202 | No || mysql-bin.000002 | 2062 | No |----------------------------------------2 rows in set (0.07 sec)binlog文件开启binlog后会在数据目录(默认)生产host-bin.n(具体binlog信息)文件及host-bin.index索引文件(记录binlog文件列表)。当binlog日志写满(binlog大小max_binlog_size默认1G),或者数据库重启才会生产新文件但是也可通过手工进行切换让其重新生成新的文件(flush logs)另外如果正使用大的事务由于一个事务不能横跨两个文件因此也可能在binlog文件未满的情况下刷新文件。查看日志状态mysql show master status;-------------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-------------------------------------------------------------------------------| mysql-bin.000002 | 2062 | | | |-------------------------------------------------------------------------------1 row in set (0.08 sec)显示正在写入的二进制文件及当前position刷新日志mysql flush logs;Query OK, 0 rows affected (0.12 sec)mysql show binary logs;----------------------------------------| Log_name | File_size | Encrypted |----------------------------------------| mysql-bin.000001 | 202 | No || mysql-bin.000002 | 2109 | No || mysql-bin.000003 | 155 | No |----------------------------------------3 rows in set (0.07 sec)自此刻开始产生一个新编号的binlog日志文件每当mysqld服务重启时会自动执行此命令刷新binlog日志在mysqldump备份数据时加 -F 选项也会刷新binlog日志重置(清空)所有binlog日志mysql reset master;常用命令mysqlbinlog查看日志D:\Program Files\MySQL$ bin\mysqlbinlog data\mysql-bin.000002在MySQL5.5以下版本使用mysqlbinlog命令时如果报错就加上 “--no-defaults”选项mysqlbinlog是mysql官方提供的一个binlog查看工具也可使用–read-from-remote-server从远程服务器读取二进制日志还可使用--start-position --stop-position、--start-time --stop-time精确解析binlog日志内容BINLOG K3L4XBMBAAAARQAAAHEGAAAAAJoCAAAAAAEACmxvbmdodWJhbmcABXRoZW1lAAUDDwUREQWWAAgAAAABAQACASGhIgQLK3L4XB4BAAAAPQAAAK4GAAAAAJoCAAAAAAEAAgAF/wA0AQAABGFhYWEAAAAAAMBYQFz4citcHIrsXjMIA/*!*/;# at 1710#190606 9:53:47 server id 1 end_log_pos 1741 CRC32 0xddb08f33 Xid 216COMMIT/*!*/;# at 1741#190606 9:53:47 server id 1 end_log_pos 1820 CRC32 0x166b4128 Anonymous_GTID last_committed5 sequence_number6 rbr_onlyyes original_committed_timestamp1559786027387679 immediate_commit_timestamp15597860273transaction_length321/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;# original_commit_timestamp1559786027387679 (2019-06-06 09:53:47.387679 ?D1ú±ê×?ê±??)# immediate_commit_timestamp1559786027387679 (2019-06-06 09:53:47.387679 ?D1ú±ê×?ê±??)/*!80001 SET session.original_commit_timestamp1559786027387679*//*!*/;/*!80014 SET session.original_server_version80016*//*!*/;/*!80014 SET session.immediate_server_version80016*//*!*/;SET SESSION.GTID_NEXT ANONYMOUS/*!*/;# at 1820#190606 9:53:47 server id 1 end_log_pos 1901 CRC32 0x47def222 Query thread_id10 exec_time0 error_code0SET TIMESTAMP1559786027/*!*/;BEGIN/*!*/;# at 1901#190606 9:53:47 server id 1 end_log_pos 1970 CRC32 0x5a235198 Table_map: longhubang.theme mapped to number 666# at 1970#190606 9:53:47 server id 1 end_log_pos 2031 CRC32 0x62dc1928 Write_rows: table id 666 flags: STMT_END_Fshow binlog events查看binlog日志A.查询第一个(最早)的binlog日志mysql show binlog events;B.指定查询 mysql-bin.000021 这个文件mysql show binlog events in mysql-bin.000021;C.指定查询 mysql-bin.000021 这个文件从pos点:8224开始查起mysql show binlog events in mysql-bin.000021 from 8224;D.指定查询 mysql-bin.000021 这个文件从pos点:8224开始查起查询10条mysql show binlog events in mysql-bin.000021 from 8224 limit 10;E.指定查询 mysql-bin.000021 这个文件从pos点:8224开始查起偏移2行查询10条mysql show binlog events in mysql-bin.000021 from 8224 limit 2,10;内容mysql show binlog events in mysql-bin.000002 from 1710 limit 10;------------------------------------------------------------------------------------------------------| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |------------------------------------------------------------------------------------------------------| mysql-bin.000002 | 1710 | Xid | 1 | 1741 | COMMIT /* xid216 */ || mysql-bin.000002 | 1741 | Anonymous_Gtid | 1 | 1820 | SET SESSION.GTID_NEXT ANONYMOUS || mysql-bin.000002 | 1820 | Query | 1 | 1901 | BEGIN || mysql-bin.000002 | 1901 | Table_map | 1 | 1970 | table_id: 666 (longhubang.theme) || mysql-bin.000002 | 1970 | Write_rows | 1 | 2031 | table_id: 666 flags: STMT_END_F || mysql-bin.000002 | 2031 | Xid | 1 | 2062 | COMMIT /* xid223 */ || mysql-bin.000002 | 2062 | Rotate | 1 | 2109 | mysql-bin.000003;pos4 |------------------------------------------------------------------------------------------------------7 rows in set (0.14 sec)数据恢复完全备份D:\Program Files\MySQL$ bin\mysqldump -h127.0.0.1 -p3306 -uroot -phongda$123456 -lF -B longhubang D:\data\backup\longhubang.dumpmysqldump: [Warning] Using a password on the command line interface can be insecure.注意要创建好D:\data\backup文件夹。这里使用了-lF注意必须大写F,当备份工作刚开始时系统会刷新log日志产生新的binlog日志来记录备份之后的数据库“增删改”操作。查看一下mysql show binary logs;----------------------------------------| Log_name | File_size | Encrypted |----------------------------------------| mysql-bin.000001 | 202 | No || mysql-bin.000002 | 2109 | No || mysql-bin.000003 | 374 | No || mysql-bin.000004 | 155 | No |----------------------------------------4 rows in set (0.10 sec)也就是说 mysql-bin.000004 是用来记录完全备份命令时间之后对数据库的所有“增删改”操作。Linux数据备份命令/usr/local/mysql/bin/mysqldump -uroot -p123456 -lF --log-error/root/myDump.err -B zyyshop /root/BAK.zyyshop.sql数据恢复经过一段时间数据库出现问题需要恢复mysql flush logs;此时执行一次刷新日志索引操作重新开始新的binlog日志记录文件理论说 mysql-bin.000004 这个文件不会再有后续写入了(便于我们分析原因及查找pos点)以后所有数据库操作都会写入到下一个日志文件查看binlog日志mysql show binlog events in mysql-bin.000004;最后一段日志内容| mysql-bin.000004 | 3976 | Xid | 1 | 4007 | COMMIT /* xid2375 */ || mysql-bin.000004 | 4007 | Anonymous_Gtid | 1 | 4086 | SET SESSION.GTID_NEXT ANONYMOUS || mysql-bin.000004 | 4086 | Query | 1 | 4167 | BEGIN || mysql-bin.000004 | 4167 | Table_map | 1 | 4236 | table_id: 666 (longhubang.theme) || mysql-bin.000004 | 4236 | Delete_rows | 1 | 4505 | table_id: 666 flags: STMT_END_F || mysql-bin.000004 | 4505 | Xid | 1 | 4536 | COMMIT /* xid2393 */ || mysql-bin.000004 | 4536 | Anonymous_Gtid | 1 | 4613 | SET SESSION.GTID_NEXT ANONYMOUS || mysql-bin.000004 | 4613 | Query | 1 | 4736 | drop database longhubang /* xid2411 */ || mysql-bin.000004 | 4736 | Rotate | 1 | 4783 | mysql-bin.000005;pos4 |----------------------------------------------------------------------------------------------------------70 rows in set (0.21 sec)通过分析造成数据库破坏的pos点区间是介于4613--4736 之间只要恢复到4613前就可。先进行完全备份恢复D:\Program Files\MySQL$ bin\mysql -h127.0.0.1 -p3306 -uroot -phongda$123456 -v binlog日志恢复D:\Program Files\MySQL$ bin\mysqlbinlog --stop-position4613 data\mysql-bin.000004 | bin\mysql -h127.0.0.1 -p3306 -uroot -phongda$123456 longhubangmysql: [Warning] Using a password on the command line interface can be insecure.增量数据恢复语法格式mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名常用选项 --start-position953 起始pos点 --stop-position1437 结束pos点 --start-datetime2013-11-29 13:18:54 起始时间点 --stop-datetime2013-11-29 13:21:53 结束时间点 --databasezyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库只限本地log日志)不常用选项 -u --username Connect to the remote server as username.连接到远程主机的用户名 -p --password[name] Password to connect to remote server.连接到远程主机的密码 -h --hostname Get the binlog from server.从远程主机上获取binlog日志 --read-from-remote-server Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志小结实际是将读出的binlog日志内容通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径上面的binlog恢复语句也可以拆分D:\Program Files\MySQL$ bin\mysqlbinlog --stop-position4613 data\mysql-bin.000004 D:\data\backup\004.sqlD:\Program Files\MySQL$ bin\mysql -h127.0.0.1 -p3306 -uroot -phongda$123456 longhubangmysql: [Warning] Using a password on the command line interface can be insecure........mysql source D:\data\backup\004.sql所谓恢复就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。主从复制复制是mysql最重要的功能之一mysql集群的高可用、负载均衡和读写分离都是基于复制来实现的从5.6开始复制有两种实现方式基于binlog和基于GTID(全局事务标示符)本文接下来将介绍基于binlog的一主一从复制其复制的基本过程如下Master将数据改变记录到二进制日志(binary log)中Slave上面的IO进程连接上Master并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容Master接收到来自Slave的IO进程的请求后负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息返回给Slave的IO进程。返回信息中除了日志所包含的信息之外还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置Slave的IO进程接收到信息后将接收到的日志内容依次添加到Slave端的relay-log文件的最末端并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中以便在下一次读取的时候能够清楚的告诉Master从某个bin-log的哪个位置开始往后的日志内容Slave的Sql进程检测到relay-log中新增加了内容后会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容并在自身执行写入机制我们一起来看一下MySQL执行事物的过程中 binlog 的落盘机制。MySQL是如何保证你使用的binlog是安全的binlog的高速缓存首先为大家介绍一个概念binlog的高速缓存意思是所有未commit的事物产生的binlog都会被先记录到binlog的高速缓存中。等该事物被commit时再将缓存中的数据写入binlog日志文件中。高速缓存的大小可以由参数binlog_chache_size 默认大小为32768 并且每个session都有自己的独立的缓存。多个会话指间彼此不影响。binlog_chache_size不能设置太大否则大量事物打来后肯定会造成宝贵的内存资源被浪费。但是也别太小因为当一个事物产生的日志足够大超过该参数设置的值时MySQL会将缓存中的binlog数据写到临时文件中去。mysql show variables like %binlog_cache%;---------------------------------------------| Variable_name | Value |---------------------------------------------| binlog_cache_size | 32768 || max_binlog_cache_size | 18446744073709547520 |---------------------------------------------2 rows in set (0.00 sec)刷盘机制其实binlog写入磁盘的机制由参数sync_binlog控制。策略1:sync_binlog 0当设置sync_binlog 0时表示innodb不会主动控制将binlog落盘innodb仅仅会将binlog写入到OS Cache中至于什么时间将binlog刷入磁盘中完全依赖于操作系统。选这种策略一旦操作系统宕机OS Cache中的binlog就会丢失。策略2:sync_binlog 1设置sync_binlog 1时表示事物commit时将binlog落盘这样哪怕机器宕机了也能确保binlog会被写入到磁盘中。策略3:sync_binlogN这里的N不是0也不是1。当N大于1时表示开启组提交也就是group commit如果你之前不层了解组提交的话你可以这样理解它比如N5那MySQL就会等收集5个binlog后再将这5个binlog一口气同步到磁盘上。好处很明显一次IO可以往磁盘上刷入N个binlogIO效率会有所提升。坏处也很明显比如N5那当MySQL收集了4个binlog时服务器宕机这4个binlog就会丢失。官方推荐策略2方式运维中查看binlog日志mysql二进制日志有三种格式statement、row、mixed一般我们在主从复制过程中采用的格式都是mixed。这种格式兼具了基于行(row)和基于语句(statement)的优点速度和效率是最高的。运维给出二进制的binlog文件将其转换成utf8文件mysqlbinlog mysql-bin.000345 mysql-bin.000345.txt输出SET SESSION.GTID_NEXT db939aab-1291-11ea-a147-6c92bf6083de:12067013/*!*/;# at 71904493#201130 14:44:16 server id 3341167209 end_log_pos 71904556 CRC32 0x55bf687f Querythread_id11827220exec_time0error_code0SET TIMESTAMP1606718656/*!*/;BEGIN/*!*/;# at 71904556#201130 14:44:16 server id 3341167209 end_log_pos 71904647 CRC32 0xccce734d Table_map: invstone.t_sso_ticket_info mapped to number 95# at 71904647#201130 14:44:16 server id 3341167209 end_log_pos 71904923 CRC32 0xc72e8724 Update_rows_v1: table id 95 flags: STMT_END_FBINLOG wJTEXxNpKibHWwAAAIctSQQAAF8AAAAAAAEACGludnN0b25lABF0X3Nzb190aWNrZXRfaW5mbwAN/gMPDwMDDxISAxIS/g7YJYAlgCWAAAAAADYEwQTXPOzAwJTEXxhpKibHFAEAAJsuSQQAAF8AAAAAAAEADf8A4CA2YzZmZGQ3NGMyYzE0YTA3ODY4NTk3MTBlOWNmZDk4YigAAAAABmZhbmdia/8CAAAAB2pzeS13ZWKZp/zrCZmn/POKAAAAAJmn/OsJmaf86wogNjk2Mjg0ODFmY2ViNDk3NjhhNWZlMDdiNDI1OWM1YzkA4CA2YzZmZGQ3NGMyYzE0YTA3ODY4NTk3MTBlOWNmZDk4YigAAAAABmZhbmdia/8DAAAAB2pzeS13ZWKZp/zrCZmn/PORAAAAAJmn/OsJmaf86xAgNjk2Mjg0ODFmY2ViNDk3NjhhNWZlMDdiNDI1OWM1Yzkkhy7H/*!*/;# at 71904923#201130 14:44:16 server id 3341167209 end_log_pos 71904954 CRC32 0xa4f47fbd Xid 103880476COMMIT/*!*/;# at 71904954在使用mysqlbinlog查看混合模式的二进制文件时基于行的部分默认是以base64编码显示将其中的base64转换成sql:mysqlbinlog --base64-outputdecode-rows -v mysql-bin.000345 mysql-bin.0003452.txt输出# at 81880980#201130 15:16:15 server id 3341167209 end_log_pos 81881102 CRC32 0xbeafaa0c Table_map: huishi-server.hs_capital_attachment_sync_info mapped to number 55827# at 81881102#201130 15:16:15 server id 3341167209 end_log_pos 81889094 CRC32 0x35905375 Update_rows_v1: table id 55827# at 81889094#201130 15:16:15 server id 3341167209 end_log_pos 81897122 CRC32 0x7181fac7 Update_rows_v1: table id 55827 flags: STMT_END_F### UPDATE huishi-server.hs_capital_attachment_sync_info### WHERE### 19669### 21### 3AJXT### 4AJXT20201130150240564### 5BO194471256404393984### 6qydafj-1000019989/qynz### 7浼佷笟妗闄勪欢-1000019989/鍐呰处### 8211812### 9http://hs-statics.shitou.com/huishi_pro/archiveManager/20骞?鏈堜唤寮€绁╛1605617655326_gHL.jpeg### 100### 111606719762### 12guosiying### 131606719762### 14guosiying### 150### 16qynz-30### 171### 180### SET### 19669### 21### 3AJXT### 4AJXT20201130150240564### 5BO194471256404393984### 6qydafj-1000019989/qynz### 7浼佷笟妗闄勪欢-1000019989/鍐呰处### 8211812### 9http://hs-statics.shitou.com/huishi_pro/archiveManager/20骞?鏈堜唤寮€绁╛1605617655326_gHL.jpeg### 101### 111606719762### 12guosiying### 131606720575### 14guosiying### 150### 16qynz-30### 171### 180### UPDATE huishi-server.hs_capital_attachment_sync_info### WHERE### 19670### 21参考
