mysql binlog二进制日志详解

发布时间:2019-05-30  栏目:MySQL  评论:0 Comments

4,重启mysql服务

[[email protected] mysql]# service mysqld56 restart
Shutting down MySQL..                                      [确定]
Starting MySQL.....                                        [确定]
[[email protected] mysql]# 

基本概念
定义:
2进制日志包蕴了颇具更新了数据或许已经秘密更新了数量(比如,未有相称任何行的1个DELETE)的全部语句。

mysql5.陆.12切换binlog2进制日志路线

前言:
有一个mysql学生说她们因为binlog爆发太大了,须求把日志路线放到此外的磁盘上面去,问作者有何时机的操作方案,share弄了3个mysql的binlog的日记路线切换的例子给他。正好今天有空,就拿mysql5.陆.1贰来做个实例,给我们演示一下。

工具介绍:
shell>>mysqlbinlog [option] binlogFile> newfile
如: D:\mysql\log>mysqlbinlog binlog.000001 > 1.txt
三个例子:
log-bin=”D:/mysql/log/binlog”
那么,在该文件夹下就能有文件D:/mysql/log/binlog.00000一等

5,验证新的binlog

翻看生成的日记,有新的如下所示:

[[email protected] mysql]# cd /home/data/mysql/binlog_new/
[[email protected] binlog_new]# ll
总用量 12
-rw-rw----. 1 mysql mysql 143 12月 10 21:09 mysql-bin.000001
-rw-rw----. 1 mysql mysql 399 12月 10 21:10 mysql-bin.000002
-rw-rw----. 1 mysql mysql  90 12月 10 21:10 mysql-bin.index
[[email protected] binlog_new]# 

创制新表,录入数据:

[[email protected] binlog_new]# mysql
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.12-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table z3 select 3 as a;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into z3 select 4;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into z3 select 5;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from z4;
ERROR 1146 (42S02): Table 'test.z4' doesn't exist
mysql> select * from z3;
+---+
| a |
+---+
| 3 |
| 4 |
| 5 |
+---+
3 rows in set (0.00 sec)

mysql> 

再去查看binlog,mysql-bin.00000②从39玖附加到十8伍,表示有新的二进制日志产生了:

[[email protected] binlog_new]# ll
总用量 12
-rw-rw----. 1 mysql mysql  143 12月 10 21:09 mysql-bin.000001
-rw-rw----. 1 mysql mysql 1085 12月 10 21:11 mysql-bin.000002
-rw-rw----. 1 mysql mysql   90 12月 10 21:10 mysql-bin.index
[[email protected] binlog_new]# 

再利用mysqlbinlog工具去看下产生的新日志是还是不是刚在确立的z三表记录,看到有全部关于test库建设构造的z三表的操作记录,如下所示:

[[email protected] binlog_new]# /usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v  mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#151210 21:10:05 server id 72  end_log_pos 120 CRC32 0xa723f142     Start: binlog v 4, server v 5.6.12-log created 151210 21:10:05 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 120
#151210 21:10:07 server id 72  end_log_pos 206 CRC32 0x447f5733     Query   thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1449753007/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 206
#151210 21:10:07 server id 72  end_log_pos 274 CRC32 0xde0b7250     Table_map: `access_log`.`access_log` mapped to number 70
# at 274
#151210 21:10:07 server id 72  end_log_pos 368 CRC32 0xa03a9659     Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `access_log`.`access_log`
### SET
###   @1=10534
###   @2=1
###   @3=1449753007
###   @4='[email protected]'
###   @5='[email protected]%'
# at 368
#151210 21:10:07 server id 72  end_log_pos 399 CRC32 0x3ccf3c72     Xid = 3
COMMIT/*!*/;
# at 399
#151210 21:10:58 server id 72  end_log_pos 471 CRC32 0xef9ce950     Query   thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1449753058/*!*/;
BEGIN
/*!*/;
# at 471
#151210 21:10:58 server id 72  end_log_pos 593 CRC32 0x92e79f36     Query   thread_id=2 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1449753058/*!*/;
CREATE TABLE `z3` (
  `a` int(1) NOT NULL DEFAULT '0'
)
/*!*/;
# at 593
#151210 21:10:58 server id 72  end_log_pos 638 CRC32 0x65f13b58     Table_map: `test`.`z3` mapped to number 107
# at 638
#151210 21:10:58 server id 72  end_log_pos 678 CRC32 0xaa7fb7e1     Write_rows: table id 107 flags: STMT_END_F
### INSERT INTO `test`.`z3`
### SET
###   @1=3
# at 678
#151210 21:10:58 server id 72  end_log_pos 709 CRC32 0x218a319c     Xid = 60
COMMIT/*!*/;
# at 709
#151210 21:11:04 server id 72  end_log_pos 781 CRC32 0x9662b95e     Query   thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1449753064/*!*/;
BEGIN
/*!*/;
# at 781
#151210 21:11:04 server id 72  end_log_pos 826 CRC32 0x46f32822     Table_map: `test`.`z3` mapped to number 107
# at 826
#151210 21:11:04 server id 72  end_log_pos 866 CRC32 0xafb27f1e     Write_rows: table id 107 flags: STMT_END_F
### INSERT INTO `test`.`z3`
### SET
###   @1=4
# at 866
#151210 21:11:04 server id 72  end_log_pos 897 CRC32 0x351c7718     Xid = 63
COMMIT/*!*/;
# at 897
#151210 21:11:10 server id 72  end_log_pos 969 CRC32 0x76931e05     Query   thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1449753070/*!*/;
BEGIN
/*!*/;
# at 969
#151210 21:11:10 server id 72  end_log_pos 1014 CRC32 0xe7e8947b    Table_map: `test`.`z3` mapped to number 107
# at 1014
#151210 21:11:10 server id 72  end_log_pos 1054 CRC32 0xbdafa096    Write_rows: table id 107 flags: STMT_END_F
### INSERT INTO `test`.`z3`
### SET
###   @1=5
# at 1054
#151210 21:11:10 server id 72  end_log_pos 1085 CRC32 0x831695c0    Xid = 64
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET [email protected]_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[[email protected] binlog_new]# 

那标记大家的binlog路线切换操作成功做到了。

http://www.bkjia.com/Mysql/1079715.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1079715.htmlTechArticlemysql5.6.12切换binlog二进制日志路径 前言:
有三个mysql学生说她们因为binlog发生太大了,要求把日记路线放到其余的磁盘上边去,问小编有啥时…

调换计策:
使用索引来循环文件,在偏下规则将循环至下一个目录
一。服务重视启
二。服务器被更新
叁。日志达到了最大日志长度 max_binlog_size
4。日志被刷新 mysql> flush logs;

三,去修改binlog日志路径

树立新的binlog日志路线:

[[email protected] binlog]# mkdir -p /home/data/mysql/binlog_new
[[email protected] binlog]# 
[[email protected] binlog]# chown -R mysql.mysql /home/data/mysql/binlog_new
[[email protected] binlog]# 

然后修改my.cnf,设置新的log-bin路径:

[[email protected] binlog]# vim /usr/local/mysql/my.cnf
log-bin =/home/data/mysql/binlog_new/mysql-bin

翻开配置文件的binlog路线:

[[email protected] mysql]# more /usr/local/mysql/my.cnf |grep log-bin
log-bin =/home/data/mysql/binlog_new/mysql-bin
[[email protected] mysql]# 

实比方下:
mysql> purge master logs to ‘binlog.000004′;
Query OK, 0 rows affected (0.01 sec)

1,查看binlog地址

[[email protected] ~]# more /usr/local/mysql/my.cnf |grep log-bin
log-bin =/home/data/mysql/binlog/mysql-bin.log
[[email protected] ~]# 

http://www.bkjia.com/Mysql/342521.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/342521.htmlTechArticle基本概念 定义:
二进制日志包括了具有更新了数量恐怕曾经秘密更新了数码(举例,未有相称任何行的2个DELETE)的具备语句。
成效:…

二,验证binlog的常规使用

[[email protected] binlog]# pwd
/home/data/mysql/binlog
[[email protected] binlog]# mysql
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 390217
Server version: 5.6.12-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table z2 select 2 as a;
ERROR 1046 (3D000): No database selected
mysql> create table test.z2 select 2 as a;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> exit
Bye
[[email protected] binlog]# ll
总用量 6240204
-rw-rw----. 1 mysql mysql 1073742187 6月   8 2015 mysql-bin.000048
-rw-rw----. 1 mysql mysql 1073741968 6月   8 2015 mysql-bin.000049
-rw-rw----. 1 mysql mysql 1073742063 6月   8 2015 mysql-bin.000050
-rw-rw----. 1 mysql mysql 1073741957 6月   8 2015 mysql-bin.000051
-rw-rw----. 1 mysql mysql 1073742142 6月   8 2015 mysql-bin.000052
-rw-rw----. 1 mysql mysql 1021194604 12月 10 20:44 mysql-bin.000053
-rw-rw----. 1 mysql mysql        615 6月   8 2015 mysql-bin.index
[[email protected] binlog]# 

来看binlog日志更新了,在20:4四光阴处,binlog日志mysql-bin.0000五叁有立异记录。然后洗涤mysql服务,看看binlog是还是不是会重新生成:

[[email protected] binlog]# service mysqld56 restart
Shutting down MySQL.................                       [确定]
Starting MySQL.....                                        [确定]
[[email protected] binlog]# 
[[email protected] binlog]# ll
总用量 997276
-rw-rw----. 1 mysql mysql 1021194627 12月 10 20:46 mysql-bin.000053
-rw-rw----. 1 mysql mysql        399 12月 10 20:47 mysql-bin.000054
-rw-rw----. 1 mysql mysql         82 12月 10 20:46 mysql-bin.index
[[email protected] binlog]# 

果不其然,有新的mysql-bin.000052十四日志生成了。

原csdn的blog地址:http://blog.csdn.net/mchdba/article/details/50254903,未经过原作者黄杉(mchdba)允许,不得转载


>>>或利用命令
RESET MASTER

二.记录到贰进制日志知的剧情安插
binlog-do-db=sales 只记录sales库
binlog-ignore-db=sales 除sales库不记录,其余都记录

大面积难题
一.怎么清除binlog
>>>使用上面包车型客车多个指令
PURGE {MASTER | BINARY} LOGS TO ‘log_name’ //log_name不会被排除
PU安德拉GE {MASTE本田CR-V | BINALacrosseY} LOGS BEFORE ‘date’ //date不会被破除

三.二进制日志不规范的管理
暗中同意景况下,并不是历次写入时都将二进制日志与硬盘同步。因而若是操作系统或机器(不唯有是MySQL服务器)崩溃,有希望二进制日志中最终的讲话丢失。
要想幸免这种景色,你能够选拔sync_binlog全局变量(一是最安全的值,但也是最慢的),使2进制日志在每N次二进制日志写入后与硬盘同步。
即使sync_binlog设置为壹,出现崩溃时,也会有希望表内容和贰进制日志内容之间存在不一致性。

剔除以前全部的binlog,并再次生成新的binlog
后缀从000001开始

注:假如您有1个活性的隶属服务器,该服务器当前正在读取您正在试图删除的日记之壹,
则本语句不会起成效,而是会败北,并伴随贰个谬误。
可是,借使从属服务器是终止的,并且您刚刚清理了其想要读取的日志之一,则从属服务器运转后无法复制。
当从属服务器正在复制时,本语句能够安全运会转。您不供给结束它们。

唯独壹旦在操作数据库在此之前,不行使use $dbname 那么富有的SQL都不会记录
一旦利用了use
$dbname,那么推断规则取决于这里的$dbname,而不是SQL中操作的库

怎么运行:
由此 –log-bin=file选项能够启用
(更改my.ini文件)
日记地点
>>假使未有一点名文件名,则Mysql使用hostname-bin文件.
>>借使钦赐了相对路线,则只要该路径相对于数据目录
>>Mysql在文件名后加多了数字索引.所以该公文最终的款型为filename.number
若果您在日记名中提供了扩张名(比如,–log-bin=file_name.extension),则扩张名被悄悄除掉并忽略。

若是崩溃恢复生机时MySQL服务器开采二进制日志变短了(即至少贫乏三个打响交付的InnoDB事务),
如果sync_binlog
=一并且硬盘/文件系统的确能依照要求进行共同(某些没有必要)则不会发出,则输出错误新闻(“贰进制日志<名>比期望的要小”)。
在这种气象下,②进制日志不准确,复制应从主服务器的数码快照发轫。

作用:
一。贰进制日志的要害目标是在平复使能够最大大概地换代数据库,因为贰进制日志包罗备份后进行的保有更新。
2。二进制日志还用于在主复制伏务器上记录全体将发送给从服务器的语句。
不良影响:
运维服务器时若启用2进制日志则品质差很少慢1%。

mysql> purge master logs before ‘2009-09-22 00:00:00′;
Query OK, 0 rows affected (0.05 sec)

留下评论

网站地图xml地图