mysql多主一从同步配置,mysql主从同步
MySql主-主(主-从)同步配置详情简单。生活
MySql主-主(主-从)同步配置的详细说明
类别：工作学习/标签:mysql，同步/日期：2011 . 05 . 28/2287查看次数/
一、MySQL复制概述
MySQL支持单向和异步复制，其中一台服务器充当主服务器，一台或多台其他服务器充当从服务器。MySQL复制基于主服务器跟踪所有更改(更新、删除等。)到二进制日志中的数据库。因此，要进行复制，必须在主服务器上启用二进制日志记录。每个从服务器从主服务器接收记录在主服务器上的二进制日志中的保存的更新。当从服务器连接到主服务器时，它会通知主服务器在日志中查找从服务器读取的最后一次成功更新。从服务器接收此后发生的任何更新，并在本地计算机上执行相同的更新。然后阻塞并等待主服务器通知新的更新。从服务器备份不会干扰主服务器，并且主服务器可以在备份过程中继续处理更新。
第二，复制实现细节
MySql使用三个线程来执行复制功能，其中两个线程(Sql线程和IO线程)在从服务器，另一个线程(IO线程)在主服务器。当发出START SLAVE命令时，从从属服务器创建一个I/O线程来连接到主服务器，并让它发送记录在其二进制日志中的语句。主服务器创建一个线程，将二进制日志的内容发送到从服务器。该线程可以是主服务器上SHOW PROCESSLIST输出中的Binlog转储线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容，并将数据复制到从服务器数据目录中的本地文件，即中继日志。第三个线程是SQL线程，由从属服务器创建，用于读取中继日志并执行日志中包含的更新。在从属服务器上，读取和执行update语句被分成两个独立的任务。当从服务器启动时，它的I/O线程可以快速地从主服务器请求所有二进制日志内容，即使SQL线程在更新方面远远落后。
1.复制线程状态
您可以通过show slave status和show master status查看复制线程的状态。的常见线程状态有：
(1)主服务器Binlog转储线程
已将所有binlog发送到从机；正在等待更新binlog
线程已经从二进制日志中读取了所有主要的更新，并将它们发送到从属服务器。现在线程是空闲的，等待由主服务器上的新更新引起的二进制日志中出现的新事件。
(2)从服务器I/O线程状态
等待主机发送事件
该线程已经连接到主服务器，正在等待二进制日志事件到达。如果主服务器空闲，会持续很长时间。如果您等待slave_read_timeout秒，就会发生超时。此时，线程认为连接被中断，并试图重新连接。
(3)从服务器的SQL线程状态
从中继日志中读取事件
线程已经从中继日志中读取了一个事件，并且可以处理该事件。
已读取所有中继日志；等待从I/O线程更新它
该线程已经处理了中继日志文件中的所有事件，现在正在等待I/O线程将新事件写入中继日志。
2.复制过程中使用的转移和状态文件
默认情况下，中继日志使用host_name-relay-bin.nnnnnn格式的文件名，其中host_name是从属服务器的主机名，nnnnnn是序列号。中继日志的格式与二进制日志的格式相同，可以被mysqlbinlog读取。
在服务器的数据目录中创建另外两个小文件。这些状态文件的默认名称是master.info和relay-log.info。状态文件保存在硬盘上，从服务器关闭时不会丢失。当从属服务器下次启动时，读取这些文件以确定它从主服务器读取了多少二进制日志，以及它处理了多少自己的中继日志。
如果要备份从服务器的数据，还应备份这两个小文件以及中继日志文件。它们用来在恢复从服务器的数据后继续进行复制。如果丢失了中继日志但仍然有中继日志信息文件，可以通过检查该文件来确定结构化查询语言线程已经执行的主服务器中二进制日志的程度。然后可以用主日志文件和主日志位置选项执行将母版更改为来告诉从服务器重新从该点读取二进制日志。
三、MySQL建立主从服务器配置方法
A、环境描述
服务器一个（主) 192.168.1.106
服务器乙（从) 192.168.1.107
关系型数据库版本：5.5.11
系统操作系统：CentOS 5.6 X64
主从需同步的数据库内容保持一致。
乙、主从配置过程
主服务器
答创建同步用户
在主服务器上为从服务器建立一个连接帐户，该帐户必须授予复制奴隶权限。
服务器答：
？查看代码尝试
一
2
在*上授予复制从属服务器。*到复制 @192.168.1。"%"由"000000"标识；
刷新权限；
b)修改关系型数据库配置文件
？查看代码尝试
一
2
3
四
5
[mysqld]
服务器id=1
log-bin=/data 0/MySQL/bin log/bin log
binlog-do-db=test
binlog-ignore-db=mysql
c)重启关系型数据库服务
？查看代码尝试
一
2
3
四
5
# /data0/mysql/mysql重启
重启关系型数据库.
停止关系型数据库.
启动关系型数据库.
d)、在在查看主服务器状态
？查看代码尝试
一
2
3
四
5
6
七
8
9
10
带读锁的关系型数据库刷新表；
关系型数据库显示主状态\G
*************************** 1.第*************************行
文件：binlog.000006
位置：107
Binlog_Do_DB:测试
Binlog_Ignore_DB: mysql
集合中的一行(0.00秒)
关系型数据库解锁表；
注：这里锁表的目的是为了生产环境中不让进新的数据，好让从服务器定位同步位置。初次同步完成后，记得解锁。
从服务器
答修改关系型数据库配置文件
？查看代码尝试
一
2
3
四
5
[mysqld]
服务器id=2
log-bin=/data 0/MySQL/bin log/bin log
replicate-do-db=测试
复制-忽略-db=MySQL，信息_模式
b)重启关系型数据库服务
？查看代码尝试
一
2
3
四
5
# /data0/mysql/mysql重启
重启关系型数据库.
停止关系型数据库.
启动关系型数据库.
c)用更改主控形状语句指定同步位置
？查看代码尝试
一
关系型数据库把掌握改成master_host=192.168.1.106 ，master_user=replication ，master_password=000000 ，master_log_file=binlog.000006 ，master _ log _ pos=107
注：主日志文件，主日志位置由上面主服务器查出的状态值中确定主日志文件对应文件，主日志位置对应位置。
mysql 5.x以上版本已经不支持在配置文件中指定主服务器相关选项。
d)启动从服务器线程
？查看代码尝试
一
关系型数据库启动从机；
e)查看从服务器状态
？查看代码尝试
一
2
3
四
关系型数据库显示从状态\ G；
从属_IO_Running:是
从属_SQL_Running:是
查看以上两项的值，均为是则表示状态正常。
丙、测试主从同步
先在主库中插入一条新的数据
？查看代码尝试
一
2
3
四
5
6
七
8
9
10
11
12
13
14
15
16
关系型数据库使用测试
数据库已更改
MySQL desc WP _ terms；
- - - - - -
Field Type Null Key Default Extra
- - - - - -
term _ id bigint(20)unsigned NO PRI NULL auto _ increment
name varchar(200) NO MUL
slug varchar(200) NO UNI
term_group bigint(10) NO 0
- - - - - -
一组四行(0.02秒)
关系型数据库插入wp_terms值(1116， MikeTestMySQL ， Mike-Test-Mysql-Repli ，0)；
查询正常，1行受影响(0.03秒)
在从库中查询，看到之前主库新增的数据就成功了。
？查看代码尝试
一
2
3
四
5
6
七
mysql select * from test.wp_terms其中术语标识=1116
- - - -
term_id name slug term_group
- - - -
1116 MikeTestMySQL Mike-Test-Mysql-Repli 0
- - - -
集合中的一行(0.00秒)
四、MySQL建立主主服务器配置方法
关系型数据库主主服务器的思路和主从差不多，本质就是让多台关系型数据库服务器间互为主从。本来只想写差异的地方，但文章逻辑不太好组织。
下面的是完整配置过程，如已按上面配置了主从架构，只需调整差异部分就好了。
A、环境描述
服务器一个（主) 192.168.1.106
服务器乙（主) 192.168.1.107
关系型数据库版本：5.5.11
系统操作系统：CentOS 5.6 X64
主从需同步的数据库内容保持一致。
乙、主主配置过程
答创建同步用户
在主服务器上为从服务器建立一个连接帐户，该帐户必须授予复制奴隶权限。
这里服务器A和服务器B互为主从，所以都要分别建立一个同步用户。
服务器A和乙：
？查看代码尝试
一
2
在*上授予复制从属服务器。*到复制 @192.168.1。"%"由"000000"标识；
刷新权限；
b)修改关系型数据库配置文件
服务器A
？查看代码尝试
一
2
3
四
5
6
七
8
9
10
11
12
[mysqld]
服务器id=1
log-bin=/data 0/MySQL/bin log/bin log
binlog-do-db=test
binlog-ignore-db=mysql
#主主需加入的部分
日志从属更新
sync_binlog=1
自动增量偏移=1
auto_increment_increment=2
replicate-do-db=测试
复制-忽略-db=MySQL，信息_模式
服务器B
？查看代码尝试
一
2
3
四
5
6
七
8
9
10
11
12
[mysqld]
服务器id=2
log-bin=/data 0/MySQL/bin log/bin log
replicate-do-db=测试
复制-忽略-db=MySQL，信息_模式
#主主需要加入部分
binlog-do-db=test
binlog-ignore-db=mysql
日志从属更新
sync_binlog=1
自动增量偏移=2
auto_increment_increment=2
c)分别重启服务器甲、乙上的关系型数据库服务
？查看代码尝试
一
2
3
四
5
# /data0/mysql/mysql重启
重启关系型数据库.
停止关系型数据库.
启动关系型数据库.
d)、在在分别在服务器甲、乙上查看做为主服务器状态
A服务器
？查看代码尝试
一
2
3
四
5
6
七
8
9
10
带读锁的关系型数据库刷新表；
关系型数据库显示主状态\G
*************************** 1.第*************************行
文件：binlog.000007
位置：107
Binlog_Do_DB:测试
Binlog_Ignore_DB: mysql
集合中的一行(0.00秒)
关系型数据库解锁表；
B服务器
？查看代码尝试
一
2
3
四
5
6
七
关系型数据库显示主状态\G
*************************** 1.第*************************行
文件：binlog.000005
位置：107
Binlog_Do_DB:测试
Binlog_Ignore_DB: mysql
集合中的一行(0.00秒)
注：这里锁表的目的是为了生产环境中不让进新的数据，好让从服务器定位同步位置。初次同步完成后，记得解锁。
c)分别在服务器甲、乙上用更改主控形状语句指定同步位置
A、服务器
？查看代码尝试
一
关系型数据库把掌握改成master_host=192.168.1.107 ，master_user=replication ，master_password=000000 ，master_log_file=binlog.000005 ，master _ log _ pos=107
乙、服务器
？查看代码尝试
一
关系型数据库把掌握改成master_host=192.168.1.106 ，master_user=replication ，master_password=000000 ，master_log_file=binlog.000007 ，master _ log _ pos=107
注：主日志文件，主日志位置由上面主服务器查出的状态值中确定主日志文件对应文件，主日志位置对应位置。
mysql 5.x以上版本已经不支持在配置文件中指定主服务器相关选项。
d)分别在服务器甲、乙上启动从服务器线程
？查看代码尝试
一
关系型数据库启动从机；
e)分别在服务器甲、乙上查看从服务器状态
？查看代码尝试
一
2
3
四
关系型数据库显示从状态\ G；
从属_IO_Running:是
从属_SQL_Running:是
查看以上两项的值，均为是则表示状态正常。
丙、测试主主同步
测试服务器A
在服务器A中插入一条新的数据
？查看代码尝试
一
2
3
四
5
6
七
8
9
10
11
12
13
14
15
16
关系型数据库使用测试
数据库已更改
MySQL desc WP _ terms；
- - - - - -
Field Type Null Key Default Extra
- - - - - -
term _ id bigint(20)unsigned NO PRI NULL auto _ increment
name varchar(200) NO MUL
slug varchar(200) NO UNI
term_group bigint(10) NO 0
- - - - - -
一组4行(0.02秒)
mysql插入wp_terms值(NULL， MikeTestMySQLMM106 ， Mike-Test-Mysql-MM ，0)；
查询正常，1行受影响(0.03秒)
在B服务器查询，看到之前A服务器新增的数据就成功了。
？查看代码BASH
一个
2
三
四
五
六
七
mysql select * from test.wp_terms其中name= MikeTestMySQLMM106
- - - -
term_id name slug term_group
- - - -
1121 miketestmysqlmm 106 Mike-Test-Mysql-MM 0
- - - -
集合中的1行(0.00秒)
测试服务器b
在服务器b中插入一条新数据。
？查看代码BASH
一个
2
mysql插入wp_terms值(NULL， MikeTestMySQLMM107 ， Mike-Test-Mysql-MM-107 ，0)；
查询正常，1行受影响(0.03秒)
在服务器A中查询，看到之前服务器B新增的数据就成功了。
？查看代码BASH
一个
2
三
四
五
六
七
mysql select * from test.wp_terms其中name= MikeTestMySQLMM107
- - - -
term_id name slug term_group
- - - -
1122 miketestmysqlmm 107 Mike-Test-Mysql-MM-107 0
- - - -
集合中的1行(0.00秒)
动词（verb的缩写）配置参数描述
服务器id
ID值唯一标识复制群集中的主服务器和从服务器，因此它们必须是不同的。Master_id必须是介于1和2321之间的正整数，slave_id必须是介于2和2321之间的正整数。
原木仓
打开binlog，意味着可以通过I/O写入Slave的relay-log，这也是复制的前提。
有一些规则
表示需要记录二进制日志的数据库。如果可以用逗号分隔多个数据库，或者使用多个binlog-do-db选项
二进制日志中忽略数据库
表示不需要记录二进制日志的数据库。如果可以用逗号分隔多个数据库，或者使用多个binlog-do-db选项
复制数据库
指示需要同步的数据库。如果有多个用逗号分隔的数据库，或者使用了多个replicate-do-db选项。
replicate-ignore-db=mysql
指示不需要同步的数据库。如果有多个用逗号分隔的数据库，或者使用多个replicate-ignore-db=mysql选项。
日志从属更新
配置是否将从属库上的更新操作写入二进制文件。如果该从库必须是其他从库的主库，则需要设置该参数，以便从库的从库可以执行日志同步。
从属跳跃错误
在复制过程中，binlog中的sql错误是由各种原因造成的。默认情况下，从库将停止复制并需要用户干预。您可以设置从跳过错误来定义错误号。如果复制过程中遇到的错误号是定义的错误号，可以跳过。如果从库用于备份，设置该参数时会出现数据不一致。不要用。如果是为了分担主库的查询压力，可以考虑。
sync_binlog=1或N
sync_binlog的默认值为0。在这种模式下，MySQL不会同步到磁盘。这样MySQL就依靠操作系统来刷新二进制日志，就像操作系统刷其他文件一样。所以如果操作系统或者机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后一条语句丢失。为了防止这种情况，您可以使用sync_binlog全局变量让binlog在每n次binlog写入后与硬盘同步。当sync_binlog变量设置为1时，是最安全的，因为在崩溃的情况下，你的二进制日志binary log最多只能丢失一条语句或者一个事务。但是，这也是最慢的方式(除非磁盘有带电池备份电源的缓存，这使得同步到磁盘的操作非常快)。
即使sync_binlog设置为1，在崩溃的情况下，表内容和binlog内容之间也可能存在不一致。如果使用InnoDB表，MySQL服务器将处理COMMIT语句，该语句将整个事务写入binlog并将事务提交给InnoDB。如果两个操作之间发生崩溃，事务在重启时会被InnoDB回滚，但它仍然存在于binlog中。您可以使用InnoDB-safe-binlog选项来提高InnoDB表格内容和bin log之间的一致性。(注意：innodb-safe-binlog在MySQL 5.1中不需要；由于引入了XA事务支持，此选项无效)。该选项可以提供更高程度的安全性，以便每个事务的binlog(sync_binlog=1)和(默认为true)InnoDB日志与硬盘同步。此选项的效果是，当您在崩溃后重新启动时，MySQL server会在回滚事务后从binlog中删除回滚的InnoDB事务。这样保证了binlog反馈InnoDB表等的准确数据。并使从属服务器与主服务器保持同步(不接收回滚的语句)。
自动增量偏移和自动增量增量
Auto_increment_increment和auto_increment_offset用于主到主复制，可用于控制Auto_increment列的操作。这两个变量都可以设置为全局变量或局部变量，并且假设每个值都可以是1到65，535之间的整数值。将其中一个变量设置为0将使其为1。
这两个变量影响AUTO_INCREMENT列的方式：auto_increment_increment控制列中值的增量值，auto_increment_offset决定auto_increment列值的起始点。
如果auto_increment_offset的值大于auto_increment_increment的值，则忽略auto_increment_offset的值。例如，如果表中有一些数据，最大自我增量将被用作初始值。
六、二进制日志清理
同步服务器生成的二进制日志会占用大量的磁盘空间，所以应该定期删除过期的二进制日志。
a、通过清除删除主日志
如果您有一个正在使用的从属服务器，该服务器当前正在读取您试图删除的一个日志，则该语句将不起作用，但会失败并出现一个错误。但是，如果辅助服务器被停止，并且您碰巧清理了它想要读取的一个日志，则在辅助服务器启动后，它将无法被复制。当辅助服务器正在复制时，此语句可以安全运行。你不需要阻止他们。
要清理日志，请按照下列步骤操作：
1.在每个从属服务器上，使用SHOW SLAVE STATUS检查它正在读取哪个日志。
2.使用SHOW MASTER LOGS获取主服务器上的一系列日志。
3.确定所有从属服务器中最早的日志。这是目标日志。如果所有从属服务器都已更新，则这是列表中的最后一个日志。
4.备份所有要删除的日志。(建议备份)
5.清理所有日志，但不清理目标日志。
清除语法
将{主二进制}日志清除到“日志名称”
在“日期”之前清除{主二进制}日志
用于删除指定日志或日期之前日志索引中列出的所有二进制日志。这些日志也将从日志索引文件中记录的列表中删除，以便给定的日志成为第一个日志。
BEFORE变量的日期参数可以采用“YYYY-MM-DD hh:mm:ss”的格式。主二进制是同义词。
例如：
？查看代码BASH
一个
2
三
四
五
六
七
八
九
#删除binlog.000002之前的一个，不带binlog.00002。
mysql将主日志清除到“bin log . 000002”；
# 2011年5月28日1: 35: 00之前删除
在“2011-05-28 1:35:00”之前清除mysql主日志；
#清除3天前的binlog
mysql清除DATE_SUB之前的主日志(现在( )，间隔3天)；
b、设置expire-logs-days参数
默认情况下，Expire-logs-days为30天。这里设置为7天，可以根据自己的情况调整。
？查看代码BASH
一个
2
[mysqld]
过期日志天数=7
七。用于控制主服务器和从服务器的SQL语句
A.用于控制主服务器的SQL语句
清除主日志
用于删除指定日志或日期之前日志索引中列出的所有二进制日志。这些日志也将从日志索引文件中记录的列表中删除，以便给定的日志成为第一个日志。
重置主机
您可以删除索引文件中列出的所有二进制日志，将二进制日志索引文件重置为空，并创建新的二进制日志文件。
设置SQL_LOG_BIN
如果客户端使用具有超级权限的帐户进行连接，您可以为当前连接禁用或启用二进制日志记录。如果客户端没有此权限，该语句将被拒绝，并显示一个错误。
显示BINLOG事件
用于显示二进制日志中的事件。如果不指定 log_name ，将显示第一个二进制日志。
显示主日志
列出了服务器中使用的二进制日志文件。
显示主状态
提供主服务器的二进制日志文件的状态信息。
显示从属主机
用于显示当前向主服务器注册的复制从属服务器的列表。
B.用于控制从属服务器的SQL语句
将母版更改为
您可以更改从属服务器用来与主服务器连接和通信的参数。
从主机加载数据
用于拍摄主服务器的快照，并将其复制到辅助服务器。
从主服务器加载表tbl_name
用于将表的副本从主服务器传输到从服务器。
MASTER_POS_WAIT()
这其实是一个函数，不是一个语句。它用于确认从服务器已经读取并执行了主服务器的二进制日志的给定位置。
重置从机
用于使辅助服务器忘记其在主服务器的二进制日志中的复制位置。
设置全局SQL_SLAVE_SKIP_COUNTER
跳过主服务器中接下来的n个事件。这对于撤消由语句导致的复制中止非常有用。
显示从属状态
用于提供关于从属服务器线程的关键参数的信息。
启动从机
用于启动从属服务器线程。
停止从属
用于中止从属服务器线程。
以上内容摘自MySQL官方手册。有关详细信息，请参见：http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html #复位-主控。
八、如何提高主从复制的可靠性
主从单向复制，从服务器只实时保存主服务器的副本。当主服务器出现故障时，可以切换到从服务器继续查询，但是不能更新。
如果采用双向复制，即两台mysql服务器既是主服务器又是从服务器。那么两者都可以进行更新操作，实现负载均衡，当一方出现故障时，另一方不受影响。但是，除非能够保证任何更新操作的顺序是安全的，否则双向复制会导致失败。
为了提高可靠性和可用性，当主服务器不可用时，有必要使从服务器成为主服务器。原始主服务器被设置为从服务器，并从新主服务器同步更新。现在有这样一个开源的解决方案【MySQL Master-Master Replication Manager】，后面我会写一篇关于MySQL MMM架构的文章，敬请关注！
九。参考文件
http://www.google.com
http://blogold.chinaunix.net/u3/93755/showart.php?id=2213538
http://hahaxiao.techweb.com.cn/archives/465.html
http://博客. . net/Libra worm/archive/2007/07/23/1703365 . aspx
http://dev . MySQL . com/doc/ref man/5.1/zh/SQL-syntax . html #重置-主控
无相关文章插件，快速增加流量。
郑重声明：本文由网友发布，不代表盛行IT的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。