快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

MYSQL管理之主从同步管理 和MySQL binlogmysqlbinlog 文件名|mysql -uroot -p 113156 进行数据恢复

请联系QQ:1793040 索取软件

类别:Mysql/postgreSQL

MYSQL管理的主从同步管理

MYSQL主从同步体系结构是当前最常用的数据库体系结构之一,尤其是负载相对较大的网站,因此对主从同步的管理也非常重要。当主从同步错误发生时,新手通常不会这样做。知道如何入门,本文基于他自己的经验来详细介绍mysql主服务器和从服务器的管理。

MYSQL主从同步的作用

(1)?数据分配

(2)?负载平衡(负载平衡)

(3)?备份

(4)?高可用性(高可用性)和容错能力

MYSQL主从同步的原理

关于MYSQL的主从同步,最重要的是了解MYSQL的主从同步的工作原理,即主从同步的原理。下图可以清楚地指导其工作过程:

通常描述该过程:从属服务器的IO线程从主服务器获取二进制日志并将其保存为本地的中继日志,然后通过SQL线程在从属服务器上执行中继日志的内容,因此从库和主库保持一致。主从同步的详细过程如下:

1.主服务器验证连接。

2.主服务器为从服务器启动线程。

3.从服务器将主服务器日志的偏移量告知主服务器。

4.主服务器检查该值是否小于当前的二进制日志偏移量。

5.如果少于,请通知服务器以获取数据。

6.从服务器继续从主服务器获取数据,直到完成为止。此时,从属服务器线程进入睡眠状态,而主服务器线程同时进入睡眠状态。

7.更新主服务器后,将激活主服务器线程,并将二进制日志推送到从服务器,并通知从服务器线程进入工作状态。

8.从服务器SQL线程执行二进制日志,然后进入睡眠状态。

MYSQL主从同步构造实战

主从同步的构建是一项相对详细的技术工作。早期完成的某些工作会减少将来的工作量。构建时需要注意一些问题,构建时会一旁。构建一个侧面来介绍需要注意的问题,以便初学者可以在一开始就有效地避免一些潜在的问题(此处未介绍MYSQL安装):

1.主从同步环境介绍

操作系统环境:linux

MYSQL版本:MYSQL 5.5

主服务器的IP:10.112.142.5

服务器的IP:10.112.142.6

2.?在主服务器上创建一个同步帐户

授权复制从站,文件在*上。 *至” pay_center” @” 10.112.142.6″由” 123456″标识;

冲洗特权;

注意: 设置权限时,密码不要设置得太简单!

3.?服务器配置文件中的更改

服务器ID = 2

复制野生忽略表= mysql。%

log-slave-updates? #必要时可以打开

注意:

1) 服务器ID 此项目需要仔细检查,并且不得与主服务器冲突,否则将出现一个奇怪的问题,因为同步将基于 服务器ID 判断是否 服务器ID 相同的对象将不会被同步,否则可能会导致无限循环 当主-主同步或振铃同步

2)? 有些人会感到奇怪,为什么我在这里使用它 复制野生-忽略表 参数,而不是使用 plicate-do-db 复制-忽略-数据库 筛选需要的数据库要同步,不需要同步的数据库。原因有以下几种:

A.复制野生-忽略表 参数可以同步所有跨数据库的更新,例如 plicate-do-db 复制-忽略数据库 类似的将不会同步

使用mysql;

更新test.aaa SET数量=数量+ 10;

B. plicate-wild-ignore-table = mysql。% 当您将来需要添加同步数据库时,可以轻松地添加它,而无需重新启动从属服务器的数据库。因为将来可能需要同步其他数据库。

3)? auto_increment_increment auto_increment_offset 参数,此 主-主同步中通常使用两个参数来错开自我增量值

防止键值冲突

4)-slave-skip-errors参数,除非您非常确定自己在做什么,否则不要乱用这些跳过错误参数。使用这些参数时,MYSQL将忽略这些错误,这将导致主服务器和从服务器之间的数据不一致。

4.从主服务器获取快照版本

如果您有MYISAM或MYISAM和INNODB都使用,请在主服务器上使用以下命令导出服务器的快照:

mysqldump-uroot-p–lock-tables–events–triggers–routines–flush-logs-master-data = 2-数据库测试db.sql

这句话很有用吗?–locl-tables?:导出的表,–events将创建所有事件。-triggers:这是一个事务,–routines:这是一个存储过程和存储方法,–flush。-log:表示在导出时先刷新binglog日志,–master-data = 2什么时候?在导出的文件中,注意更改主文件。当它等于1时,不监视它。这将根据您自己的要求进行更改。通常,您会选择2,因为它是从服务器获得的?

更改?关于?主服务器IP?港口?主服务器帐户?密码?

如果仅尝试使用INNODB,请使用以下命令:

mysqldump-uroot-p –单事务–events –triggers –routines –flush-logs –master-data = 2 –databases测试\ gt ; db.sql

在这里我们需要注意几个参数的使用:

-single-transaction此参数仅适用于innodb。

-databases后跟除mysql外的所有数据库的库名。我这里只有一个测试库。

-master-data参数将在导出快照时记录mysql二进制日志位置,稍后将使用它。

5.将快照版本还原到辅助服务器

mysqldump-uroot-p-h 10.1.1.76 test \ lt; db.sql

将快照版本还原到从属服务器后,从属服务器上的数据与主服务器上的数据相同。

6.使用从属服务器上的change master来与主服务器进行同步

使用grep命令查找二进制日志的名称和位置

[root @ ns1]#grep-i”更改主服务器” db.sql

-更改母版吗? MASTER_LOG_FILE =” mysql-bin.000006″,MASTER_LOG_POS = 106;

生成CHANGE MASTER语句,然后在从属服务器上执行

停止从站;?

将MASTER更改为MASTER_HOST =” 10.112.142.5″,MASTER_USER =” pay_cener”,MASTER_PASSWORD =” 123456″,MASTER_LOG_FILE =” mysql-bin.000006″,MASTER_LOG_POS = 106;

开始从动;

这样就完成了主从同步的构造。最后,使用SHOW SLAVE STATUS \\\\ G;检查Slave_IO_Running和Slave_SQL_Running的状态。如果两者都是,则完成。

注意 :请勿将同步信息写入配置文件,这很不方便管理,尤其是在需要重新启动更改的情况下。

MYSQL主从同步管理

以下是一些用于管理MYSQL主从同步的命令:

1.停止MYSQL同步

停止从IO_THREAD;? #STOP IO进程

停止从SQL_THREAD;? #停止SQL进程

停止从动; #停止IO和SQL进程

2.启动MYSQL同步

START SLAVE IO_THREAD;? #开始IO进程

开始从SQL_THREAD;? #启动SQL进程

开始从动; #启动IO和SQL进程

3.?重置MYSQL同步

重置从机;

用于使从属服务器忘记其在主服务器的二进制日志中的复制位置,它将删除master.info和relay-log.info文件以及所有中继日志并启动新的中继日志,当不需要主站和从站时,可以在从站上执行此操作。否则,它将在将来进行同步,并可能覆盖您的数据库。我以前遇到过这么愚蠢的事情。哈哈!

4.?检查MYSQL同步状态

显示从属状态;

该命令主要检查Slave_IO_Running,Slave_SQL_Running,Seconds_Behind_Master,Last_IO_Error,Last_SQL_Error的值以掌握复制状态。

5.暂时跳过MYSQL同步错误

当朋友mysql主从同步遇到错误(例如主键冲突等)时,那么我需要暂时跳过此错误以确保数据行是一致的,那么我需要使用SQL_SLAVE_SKIP_COUNTER = > n 命令,n表示跳过接下来的n个事件,例如,我跳过一个事件,如下所示:

停止从动;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

开始从动;

6.从指定位置重新同步

有时,当主从同步出现问题时,您需要从日志位置的下一个位置进行同步,这等效于跳过该错误。这时,只要找到相应的LOG位置,就可以使用CHANGE MASTER命令进行处理,例如:

将MASTER更改为MASTER_HOST =” 10.1.1.75″,MASTER_USER =”复制”,MASTER_PASSWORD =” 123456″,MASTER_LOG_FILE =” mysql-bin.000006″,MASTER_LOG_POS = 106;

开始从动;

介绍MYSQL主从同步的管理经验

1.请勿随意使用SQL_SLAVE_SKIP_COUNTER命令。

跳过此命令后,您的主从数据可能会不一致。确保首先记录指定的错误,然后检查数据是否一致,尤其是核心业务数据。

2.与percona-toolkit工具pt-table-checksum结合使用,以定期检查数据是否一致。

这是DBA必须定期执行的操作。呵呵,为什么没有合适的工具?另外,percona-toolkit还提供了解决数据库不一致性的方法,可以使用pt-table-sync,此工具不会更改主数据。您也可以使用pt-heartbeat查看从属服务器的向后复制。有关详细信息,请参见:http://blog.chinaunix.net/uid-20639775-id-3229211.html。

3.? 使用plicate-wild-ignore-table选项代替复制文件 复制-忽略数据库

上面已经解释了原因。

4。 将主服务器的日志模式调整为 混合

5.? 每个表都添加有一个主键,该主键会影响数据库的同步,尤其是在 R OW 复制模式。

1.前言

?日志是将数据库中的每个更改记录到一个特殊的文件,此文件称为日志文件。默认情况下,Mysql仅打开错误日志,因为太多的日志会影响系统的处理性能。

在5.0之前支持文本格式和二进制格式,在5.0以后仅支持二进制格式,因为二进制日志在性能和信息处理方面更具优势。
2.基础知识

? 2.1,启用二进制日志

?二进制日志由配置文件的log-bin选项启用。 Mysql服务器将在数据根目录中创建两个新文件XXX-bin.001和XXX-bin.index。如果配置选项未提供文件名,则Mysql将使用主机名来命名这两个文件,并且.index文件包含所有日志文件的列表。

? Mysql会将用户对所有数据库的内容和结构的修改记录到XXX-bin.n文件中,并且不会记录SELECT且没有实际的

? 2.2,更新了UPDATE语句。

?日志文件扩展名

当服务器停止或重新启动时,服务器会将日志文件记录到下一个日志文件中,Mysql在重新启动时将生成一个新的日志文件,文件序号递增。另外,如果日志文件超过了系统变量max_binlog_size所配置的上限,则会生成一个新的日志文件。

? 2.3,查看日志文件

? Mysql提供mysqlbinlog命令来查看日志文件,例如mysqlbinlog xxx-bin.001 |更多。记录每个更改日志时,日志文件将记录数据库恢复的当前时间。 ? [查看relaylog日志还使用mysqlbinlog吗?此文件是一个传输文件,请先从主文件读取,然后再写入此文件,然后从服务器读取以从该文件执行]

binlog的内容?和中继日志?都使用mysqlbinlog显示? [它们的内容非常相似,您可以自己查看]

中继日志记录是在复制过程中,从属服务器I/O线程读取主服务器的二进制日志并将其记录到从属服务器的本地文件,然后SQL线程将读取中继日志日志的内容并将其应用于从属服务器

例如:

VM_141_21_sles10_64:/数据/Chunbai/app/mysql/数据/mysql/3306/relaylog#/data/Chunbai/app/mysql/bin/mysqlbinlog https ://blog.csdn.net/superhosts/article/details/relaylog.000006 |更多

;

;

定界符;

#at 4

#140529 12:53:31服务器ID 2? End_log_pos 107开始:binlog v 4,服务器v 5.5.3-m3-log创建140529 12:53:31

BINLOG”

S72GUw8CAAAAZwAAAGsAAAAAAAQANS41LjMtbTMtbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAzzNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA ==

“;

#在107

#140529 12:53:31服务器ID 1? End_log_pos 0转到binlog.000013吗?位置:4

#在147

#140529 14:36:02服务器ID 1? End_log_pos 107开始:binlog v 4,服务器v 5.5.3-m3-log创建140529 14:36:02

BINLOG”

UtWGUw8BAAAAZwAAAGsAAAAAAAQANS41LjMtbTMtbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAzzNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA ==

“;

#在250

#140529 14:36:51服务器ID 1? End_log_pos 181查询thread_id = 18186 exec_time = 0 error_code = 0

SET TIMESTAMP = 1401345411;

设置@@session.pseudo_thread_id = 18186;

SET @@ session.foreign_key_checks = 1,@@ session.sql_auto_is_null = 0,@@ session.unique_checks = 1,@@ session.autocommit = 1;

SET @@ session.sql_mode = 0;

SET @@ session.auto_increment_increment = 1,@@ session.auto_increment_offset = 1;

;

SET @@ session.character_set_client = 33,@@ session.collation_connection = 33,@@ session.collation_server = 33;

SET @@ session.lc_time_names = 0;

SET @@ session.collation_database =默认;

? 2.4,禁用日志文件

?您可以使用 SET SQL_LOG_BIN = 0 命令停止使用日志文件,然后可以使用 SET SQL_LOG_BIN = 1 命令启用。

? 2.5,使用日志进行数据库恢复

?如果遇到灾难,则应使用最新的完整备份还原数据库,然后在备份后使用日志

?该文件将数据库还原到最接近的可用状态。

?使用日志进行恢复时,需要按顺序进行,也就是说,应首先恢复最早生成的日志文件:

? mysqlbinlog xxx-bin.00001 | mysql-u root-p

? mysqlbinlog xxx-bin.00002 | mysql-u root-p
3.日志替换策略

?使用索引来循环文件,以下情况将循环到下一个索引

服务器重启
? b。服务器已更新
? C。日志达到最大日志长度max_binlog_size
? d。日志刷新为mysql;冲洗日志;
4.日志格式

从官方网站文档中可以看出,以前的MySQL仅基于基于语句的复制模式,直到5.1.5版的MySQL才开始支持行级复制。从5.0开始,MySQL复制解决了许多旧版本无法正确复制的问题。但是,由于存储过程的出现,MySQL复制带来了更大的新挑战。另外,请参阅官方文档,该版本从5.1.8版开始,MySQL除了提供语句级别和行级别之外,还提供了第三种复制模式:实际上是前两种模式的组合。在混合模式下,MySQL将根据执行的每个特定sql语句区分要记录的日志格式,即在Statement和Row之间选择一个。新版本中的声明

级别与以前相同,仅记录执行的语句。 MySQL的新版本还针对行级别模式进行了优化。并非所有更改都将记录在行级别。例如,当表结构更改时,它将以语句模式记录。如果sql语句确实是update或delete或其他修改数据的语句,则将记录所有行。

-基于语句的复制(SBR),
?-基于行的复制(基于行的复制,RBR),
?-混合复制(MBR)。

?静态设置binlog格式:

  vi my.cnf

日志 - bin = mysql - bin

#binlog_format = " STATEMENT"

#binlog_format = " ROW"

binlog_format = " MIXED"

?动态修改binlog格式:

 mysql  \ gt;   SET  SESSION binlog_format  =  "  声明 "  ;

mysql \ gt; SET SESSION binlog_format = " ROW " ;

mysql \ gt; SET SESSION binlog_format = " 混合 " ;

mysql \ gt; SET GLOBAL binlog_format = " 声明 " ;

mysql \ gt; SET GLOBAL binlog_format = " ROW " ;

mysql \ gt; SET GLOBAL binlog_format = " 混合 " ;

5.与二进制日志相关的变量和参数

? 5.1。命令行参数

-log-bin [= file_name]

设置此参数意味着启用binlog功能并制定路径名。

?–log-bin-index [=文件]

设置此参数将指定二进制索引文件的路径和名称。

?–max_binlog_size

Binlog最大值,最大值和默认值为1GB,此设置并不严格控制Binlog的大小,尤其是当Binlog接近最大值并且遇到相对较大的事务时,

为了确保事务的完整性,不可能执行切换日志的操作,仅事务的所有SQL全部记录在当前日志中,直到事务结束。

?–binlog-do-db = db_name

此参数表示仅记录指定数据库的二进制日志

?–binlog-ignore-db = db_name

此参数意味着将不记录指定数据库的二进制日志

? 5.2,系统变量

log_bin

? binlog_cache_size

此参数指示binlog使用的内存量,这可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助。

? max_binlog_cache_size

此参数表示binlog所使用的最大内存大小

? binlog_cache_use

使用二进制日志缓存的事务数

? binlog_cache_disk_use

使用二进制日志缓存,但超过binlog_cache_size值,并使用临时文件将事务数保存在transaction语句中。

? binlog_do_db

? binlog_ignore_db

? sync_binlog

此参数直接影响mysql的性能和完整性。

? sync_binlog = 0:

提交事务后,Mysql只是将binlog_cache中的数据写入binlog文件,但不执行fsync对于一类磁盘,synchronization命令告诉文件系统将高速缓存刷新到磁盘,并让Filesystem决定何时进行同步。这是最好的表现。

sync_binlog = 0,在提交了n个事务后,Mysql将执行磁盘同步,如fsync指示文件系统将Binlog文件缓存刷新到磁盘。

? Mysql中的默认设置为sync_binlog = 0,即不执行强制磁盘刷新命令,则性能最佳,但风险也最大。一旦系统崩溃,文件系统缓存中的所有Binlog信息都将丢失。

6.常见问题解答

? 6.1。如何清除binlog

?-使用以下两个命令

?清除{主| BINARY}日志到” log_name”?//log_name将不会被清除

?清除{主| BINARY}”日期”之前的日志?//日期不会被清除

?示例如下:

复制代码

 mysql  \ gt;  显示主日志;

+ - --------------------+-----------+

| Log_name | File_size |

+ - ---------------------+-----------+

| mysql3306 - bin。 000001 | 107 |

+ - ---------------------+-----------+

1 in set 0.00 sec)

mysql \ gt; 刷新日志;

查询确定, 0 受影响的行( 0.11 秒)

mysql \ gt; 刷新日志;

查询确定, 0 受影响的行( 0.02 秒)

mysql \ gt; 刷新日志;

查询确定, 0 受影响的行( 0.01 秒)

mysql \ gt; 刷新日志;

查询确定, 0 受影响的行( 0.01 秒)

mysql \ gt; 显示主日志;

+ - --------------------+-----------+

| Log_name | File_size |

+ - ---------------------+-----------+

| mysql3306 - bin。 000001 | 154 |

| mysql3306 - bin。 000002 | 154 |

| mysql3306 - bin。 000003 | 154 |

| mysql3306 - bin。 000004 | 154 |

| mysql3306 - bin。 000005 | 107 |

+ - ---------------------+-----------+

5 in set 0.00 sec)

mysql \ gt; 清除主日志 " mysql3306-bin.000002 " ;

查询确定, 0 受影响的行( 0.01 秒)

mysql \ gt; 显示主日志;

+ - --------------------+-----------+

| Log_name | File_size |

+ - ---------------------+-----------+

| mysql3306 - bin。 000002 | 154 |

| mysql3306 - bin。 000003 | 154 |

| mysql3306 - bin。 000004 | 154 |

| mysql3306 - bin。 000005 | 107 |

+ - ---------------------+-----------+

4 in set 0.00 秒)

复制代码

复制代码

  [  root @ node4数据 ]  #日期

星期二 30 01 : 27 : 04 CST 2013

mysql \ gt; 刷新日志;

查询确定, 0 受影响的行( 0.01 秒)

mysql \ gt; 显示主日志;

+ - --------------------+-----------+

| Log_name | File_size |

+ - ---------------------+-----------+

| mysql3306 - bin。 000002 | 154 |

| mysql3306 - bin。 000003 | 154 |

| mysql3306 - bin。 000004 | 154 |

| mysql3306 - bin。 000005 | 154 |

| mysql3306 - bin。 000006 | 107 |

+ - ---------------------+-----------+

5 in set 0.00 sec)

mysql \ gt; 之前清除主日志。 2013-07-30 01:27:04 " ;

查询确定, 0 受影响的行( 0.02 秒)

mysql \ gt; 显示主日志;

+ - --------------------+-----------+

| Log_name | File_size |

+ - ---------------------+-----------+

| mysql3306 - bin。 000005 | 154 |

| mysql3306 - bin。 000006 | 107 |

+ - ---------------------+-----------+

2 in set 0.00 秒)

复制代码

?-或使用命令:

?重置主

?删除所有以前的二进制日志并重新生成新的二进制日志。后缀从000001开始。

?注意:如果您有一个活动的从属服务器,当前正在读取您要删除的日志之一,则此语句将不起作用,但会失败并显示错误。

?但是,如果从属服务器处于非活动状态,并且您碰巧要清理它要读取的日志之一,则从属服务器在启动后将无法复制。

在复制从属服务器时,可以安全地运行此语句。您无需停止它们。

? 6.2。内容配置记录在二进制日志中

binlog-do-db = sales仅记录销售库binlog-ignore-db = sales,但销售库未记录所有其他记录。

但是,如果在操作数据库之前不使用$ dbname,则不会记录所有SQL。如果使用use $ dbname,则判断规则取决于此处的$ dbname,而不取决于在SQL中操作的库

? 6.3。二进制日志的处理不正确

默认情况下,二进制日志在每次写入时都不与硬盘同步。因此,如果操作系统或计算机(不仅仅是MySQL服务器)崩溃,二进制日志中的最后一条语句可能会丢失。为避免这种情况,可以在每写入N个二进制日志后,使用sync_binlog全局变量(1是最安全的值,但也是最慢的值)将二进制日志与硬盘同步。即使将sync_binlog设置为1,当发生崩溃时,表的内容与二进制日志的内容之间也可能存在不一致。

如果MySQL服务器发现在恢复崩溃后二进制日志变得更短(也就是说,至少缺少一个成功的InnoDB事务),则如果sync_binlog = 1并且硬盘/文件系统确实可以根据需要进行同步(有些是不需要)输出错误消息(”二进制日志<名称>小于预期值”)。在这种情况下,二进制日志不准确,并且复制应从主服务器的数据快照开始。为了您的安全,请仅打开来自可靠来源的URL?

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1793040