学⼀点mysql双机异地热备份----快速理解mysql主从，主主备
份原理及实践
双机热备的概念简单说⼀下，就是要保持两个数据库的状态⾃动同步。对任何⼀个数据库的操作都⾃动应⽤到另外⼀个数据库，始终保持两个数据库数据⼀致。这样做的好处多。 1. 可以做灾备，其中⼀个坏了可以切换到另⼀个。 2. 可以做负载均衡，可以将请求分摊到其中任何⼀台上，提⾼⽹站吞吐量。对于异地热备，尤其适合灾备。废话不多说了。我们直接进⼊主题。我们会主要介绍两部分内容： ⼀， mysql 备份⼯作原理
⼆，备份实战
我们开始。
我使⽤的是mysql 5.5.34，
⼀， mysql 备份⼯作原理
简单的说就是把⼀个服务器上执⾏过的sql语句在别的服务器上也重复执⾏⼀遍，这样只要两个数据库的初态是⼀样的，那么它们就能⼀直同步。 当然这种复制和重复都是mysql⾃动实现的，我们只需要配置即可。 我们进⼀步详细介绍原理的细节，这有⼀张图：
上图中有两个服务器，演⽰了从⼀个主服务器（master）把数据同步到从服务器（slave）的过程。
这是⼀个主-从复制的例⼦。主-主互相复制只是把上⾯的例⼦反过来再做⼀遍。所以我们以这个例⼦介绍原理。
对于⼀个mysql服务器，⼀般有两个线程来负责复制和被复制。当开启复制之后。
1. 作为主服务器Master，会把⾃⼰的每⼀次改动都记录到⼆进制⽇志 Binarylog 中。（从服务器会负责来读取这个log，然后在⾃⼰那⾥再执⾏⼀遍。）
珊瑚姜2. 作为从服务器Slave，会⽤master上的账号登陆到 master上，读取master的Binarylog, 写⼊到⾃⼰的中继⽇志 Relaylog，然后⾃⼰的sql 线程会负责读取这个中继⽇志，并执⾏⼀遍。到这⾥主服务器上的更改就同步到从服务器上了。
在mysql上可以查看当前服务器的主，从状态。其实就是当前服务器的 Binary（作为主服务器⾓⾊）状态和位置。以及其RelayLog（作为从服务器）的复制进度。
例如我们在主服务器上查看主状态：
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000014
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema,amh
1 row <span class="kwrd">in</span> set (0.00 sec)
稍微解释⼀下这⼏⾏的意思：
1. 第⼀⾏表明当前正在记录的 binarylog⽂件名是： mysql-bin.000014.
我们可以在mysql数据⽬录下，到这个⽂件：
2. 第⼆⾏， 107. 表⽰当前的⽂件偏移量，就是写⼊在mysql-bin.000014 ⽂件的记录位置。
这两点就构成了主服务器的状态。配置从服务器的时候，需要⽤到这两个值。告诉从服务器从哪读取主服务器的数据。（从服务器会登录之后，到这个⽇志⽂件，并从这个偏移量之后开始复制。）
3. 第三⾏，和第四⾏，表⽰需要记录的数据库和需要忽略的数据库。只有需要记录的数据库，其变化才会被写⼊到mysql-bin.000014⽇志⽂件中。后⾯会再次介绍这两个参数。
我们还可以在从服务器上，查看从服务器的复制状态。
<span class="lnum"> 1: </span>mysql> show slave status\G
2: *************************** 1. row ***************************
<span class="lnum"> 3: </span> Slave_IO_State: Waiting <span class="kwrd">for</span> master to send <span class="kwrd">event</span>
4: Master_Host: 198.**.***.***
<span class="lnum"> 5: </span> Master_User: r*******
性蚀
6: Master_Port: 3306
<span class="lnum"> 7: </span> Connect_Retry: 60
8: Master_Log_File: mysql-bin.000014
<span class="lnum"> 9: </span> Read_Master_Log_Pos: 107
10: Relay_Log_File: mysqld-relay-bin.000013
<span class="lnum"> 11: </span> Relay_Log_Pos: 253
12: Relay_Master_Log_File: mysql-bin.000014
还原剂加药装置<span class="lnum"> 13: </span> Slave_IO_Running: Yes
14: Slave_SQL_Running: Yes
<span class="lnum"> 15: </span> Replicate_Do_DB:
16: Replicate_Ignore_DB: mysql,information_schema,amh,performance_schema
<span class="lnum"> 17: </span> Replicate_Do_Table:
18: Replicate_Ignore_Table:
<span class="lnum"> 19: </span> Replicate_Wild_Do_Table:
20: Replicate_Wild_Ignore_Table:
<span class="lnum"> 21: </span> Last_Errno: 0
22: Last_Error:
<span class="lnum"> 23: </span> Skip_Counter: 0
24: Exec_Master_Log_Pos: 107
<span class="lnum"> 25: </span> Relay_Log_Space: 556
26: Until_Condition: None
<span class="lnum"> 27: </span> Until_Log_File:
28: Until_Log_Pos: 0
<span class="lnum"> 29: </span> Master_SSL_Allowed: No
我们还是来重点解释途中的红圈的部分：
1. Master_host 指的是主服务器的地址。
2. Master_user 指的是主服务器上⽤来复制的⽤户。从服务器会⽤此账号来登录主服务。进⾏复制。
3. Master_log_file 就是前⾯提到的，主服务器上的⽇志⽂件名.
4. Read_Master_log_pos 就是前⾯提到的主服务器的⽇志记录位置，从服务器根据这两个条件来选择复制的⽂件和位置。
5. Slave_IO_Running: 指的就是从服务器上负责读取主服务器的线程⼯作状态。从服务器⽤这个专门的线程链接到主服务器上，并把⽇志拷贝回来。
6. Slave_SQL_Running: 指的就是专门执⾏sql的线程。它负责把复制回来的Relaylog执⾏到⾃⼰的数据库中。这两个参数必须都为Yes 才表明复制在正常⼯作。
5g怎么做
其他的参数之后再介绍。
⼆， mysql 双机热备实战
了解了上⾯的原理之后，我们来实战。这⾥有两个重点，要想同步数据库状态，需要相同的初态，然后配置同步才有意义。当然你可以不要初态，这是你的⾃由。我们这⾥从头开始配置⼀遍。
我们先以A服务器为起点，配置它的数据库同步到B。这就是主-从复制了。之后再反过来做⼀次，就可以互相备份了。
1，第⼀步，
在A上⾯创建专门⽤于备份的⽤户：
grant replication slave on *.* to <span class="str"><a target=_blank target="_blank" href="mailto:'repl_user'@'192.***.***.***'" >'repl_user'</a></span>@<span class="str">'192.***.***.***'</span> identified by <span class="str">'hj34$%&mnkb'</span>;
上⾯把ip地址换成B机器的ip地址。只允许B登录。安全。
⽤户名为： repl_user
密码为： hj34$********nkb
这个等会在B上⾯要⽤。
2. 开启主服务器的 binarylog。
很多服务器是默认开启的，我们这⾥检查⼀下：
打开 /etc/myf
我来解释⼀下红框中的配置：
前⾯三⾏，你可能已经有了。
binlog-do-db ⽤来表⽰，只把哪些数据库的改动记录到binary⽇志中。可以写上关注hello数据库。但是我把它注释掉了。只是展⽰⼀下。可以写多⾏，表⽰关注多个数据库。
binlog-ignore-db 表⽰，需要忽略哪些数据库。我这⾥忽略了其他的4个数据库。
后⾯两个⽤于在双主（多主循环）互相备份。因为每台数据库服务器都可能在同⼀个表中插⼊数据，如果表有⼀个⾃动增长的主键，那么就会在多服务器上出现主键冲突。解决这个问题的办法就是让每个数据库的⾃增主键不连续。上图说是，我假设需要将来可能需要10台服务器做备份，所以auto-increment-increment 设为10. ⽽ auto-increment-offset=1 表⽰这台服务器的序号。从1开始，不超过auto-increment-increment。
这样做之后，我在这台服务器上插⼊的第⼀个id就是 1，第⼆⾏的id就是 11了，⽽不是2.
（同理，在第⼆台服务器上插⼊的第⼀个id就是2，第⼆⾏就是12，这个后⾯再介绍）这样就不会出现主键冲突了。后⾯我们会演⽰这个id的效果。
3. 获取主服务器状态，和同步初态。
假设我现在有这些数据库在A上⾯。
如果你是全新安装的，那么不需要同步初态，直接跳过这⼀步，到后⾯直接查看主服务器状态。
这⾥我们假设有⼀个 hello 数据库作为初态。
先锁定 hello数据库：
轮胎脱模剂
FLUSH TABLES WITH READ LOCK;
然后导出数据：
我这⾥只需要导出hello数据库，如果你有多个数据库作为初态的话，需要导出所有这些数据库：
然后查看A服务器的binary⽇志位置：
记住这个⽂件名和位置，等会在从服务器上会⽤到。
主服务器已经做完了，可以解除锁定了：
4. 设置从服务器 B 需要复制的数据库
打开从服务器 B 的 /etc/myf ⽂件：点火加热装置
解释⼀下上⾯的内容。
server-id 必须保证每个服务器不⼀样。这可能和循环同步有关。防⽌进⼊死循环。
replicate-do-db 可以指定需要复制的数据库，我这⾥注掉了。演⽰⼀下。
replicate-ignore-db 复制时需要排除的数据库，我使⽤了，这个。除开系统的⼏个数据库之外，所有的数据库都复制。
relay_log 中继⽇志的名字。前⾯说到了，复制线程需要先把远程的变化拷贝到这个中继⽇志中，在执⾏。
log-slave-updates 意思是，中继⽇志执⾏之后，这些变化是否需要计⼊⾃⼰的binarylog。当你的B服务器需要作为另外⼀个服务器的主服务器的时候需要打开。就是双主互相备份，或者多主循环备份。我们这⾥需要，所以打开。
保存，重启mysql。
5. 导⼊初态，开始同步。
把刚才从A服务器上导出的 hello.sql 导⼊到 B的hello数据库中，如果B现在没有hello数据库，请先创建⼀个，然后再导⼊：
创建数据库：
mysql> create database hello <span class="kwrd">default</span> charset utf8;
把hello.sql 上传到B上，然后导⼊：
如果你刚才导出了多个数据库，需要把他们都⼀⼀上传导⼊。
开启同步, 在B服务器上执⾏：
CHANGE MASTER TO
MASTER_HOST=<span class="str">'192.***.***.***'</span>,
MASTER_USER=<span class="str">'repl_user'</span>,
MASTER_PASSWORD=<span class="str">'hj3****'</span>,
MASTER_LOG_FILE=<span class="str">'mysql-bin.000004'</span>,
MASTER_LOG_POS=7145;
上⾯⼏个参数我就不解释了。前⾯说过了。
重启mysql，然后查看slave线程开启了没：
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议