mysql双机热备配置
数据库工具开发 2012-03-29
主服务器serverA
从服务器serverB
从广义上讲，就是对于重要的服务，使用两台服务器，互相备份，共同执行同一服务。当一台服务器出现故障时，可以由另一台服务器承担服务任务，从而在不需要人工干预的情况下，自动保证系统能持续提供服务。 从狭义上讲，双机热备特指基于active/standby方式的服务器热备。服务器数据包括数据库数据同时往两台或多台服务器写，或者使用一个共享的存储设备。在同一时间内只有一台服务器运行。当其中运行着的一台服务器出现故障无法启动时，另一台备份服务器会通过软件诊测（一般是通过心跳诊断）将standby机器激活，保证应用在短时间内完全恢复正常使用。
要想实现双机的热备首先要了解主从数据库服务器的版本的需求。要实现热备mysql的版本都要高于3.2，还有一个基本的原则就是作为从数据库的数据库版本可以高于主服务器数据库的版本，但是不可以低于主服务器的数据库版本。
首先要备份数据库，如果可能会有数据写入，则要锁定数据库。
以下操作均是以root用户登录
一、设置账户
在主服务器和从服务器上分别创建账户，
主服务器上：
Sql代码
mysql>grantreplicationslaveon*.*to'用户名'@'从服务器ip'identifiedby'密码';
从服务器上：
Sql代码
mysql>grantreplicationslaveon*.*to'用户名'@'主服务器ip'identifiedby'密码';
测试是否能够连接：
主服务器：mysql-h从服务器ip-u用户名(从服务器上创建)-p密码
如果能进到mysql下说明连接成功，如果报错，cann'tconnectto从服务器ip,大致是这样，是因为，从服务器上的
防火墙限制了连接，要在防火墙里设置例外，添加主服务器ip以及所访问从服务器mysql的端口号。
从服务器同样。
成功往下走
二、配置
编辑serverA的my.ini文件，在mysqld下添加如下信息：
Java代码
server-id=1（主服务器ID，不能重复）
binlog-do-db=labostest（要复制的库）
log-bin="C:/mysql/log"（log文件存放处，最后的参数表明日志文件名，replication是基于log来实现复制的）
#replicationslave
master-host=192.168.1.108（设置从服务器ip）
master-port=3306
master-user=backup（在从服务器上创建的用户名）
master-password=123456（对应密码）
replicate-do-db=labostest（要复制的库）
replicate-do-db=labostest1（要复制的另一个数据库）
master-connect-retry=60（连接时间）
然后编辑serverB的my.ini文件，
Java代码
server-id=2（从服务器ID，不能为1）
binlog-do-db=labostest（要复制的库）
log-bin="C:/mysql/log"（log文件存放处，最后的参数表明日志文件名，replication是基于log来实现复制的）
#replicationslave
master-host=192.168.1.103（设置从服务器ip）
master-port=3306
master-user=backup（在主服务器上创建的用户名）
master-password=123456（对应密码）
replicate-do-db=labostest（要复制的库）
replicate-do-db=labostest1（要复制的另一个数据库）
master-connect-retry=60（连接时间）
重新启动两台机器的mysql服务
三、查看服务器状态
重启服务之后，查看日志文件夹下是否生成了文件，如果是则已经加载成功，否则，并未加载以上的配置信息，
可能是修改的配置文件并不是启动mysql时要加载的配置文件，很可能在C:/windows下
修改之后，重启服务，直到日志文件夹下生成文件，否则showmasterstatus时，会一直是空集。
*************************主服务器****************************
Java代码
showmasterstatus\G
mysql>showmasterstatus\G;
Java代码
+---------------+----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+---------------+----------+--------------+------------------+
|mysql-bin.003|73|test|manual,mysql|
+---------------+----------+--------------+------------------+
切换到从服务器下，，通过下面命令来设置从服务器的参数
如果在配置文件里设置可以不用执行以下命令，直接slavestart;
首先停止slave服务
mysql>slavestop;//停止slave的服务
Java代码
mysql>CHANGEMASTERTO
->MASTER_HOST='master_host_name',//<spanstyle="color:#ff0000;">主服务</span>
器的IP地址
->MASTER_USER='replication_user_name',//同步数据库的用户
->MASTER_PASSWORD='replication_password',//同步数据库的密码
->MASTER_LOG_FILE='recorded_log_file_name',//主服务器二进制日志的文件名(前面要求记住的参数)
->MASTER_LOG_POS=recorded_log_position;//日志文件的开始位置(前面要求记住的参数)
启动同步数据库的线程
Java代码
mysql>slavestart;
*************************从服务器******************************
Java代码
showmasterstatus\G
mysql>showmasterstatus\G;
Java代码
+---------------+----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+---------------+----------+--------------+------------------+
|mysql-bin.003|73|test|manual,mysql|
+---------------+----------+--------------+------------------+
切换到主服务器下，通过下面命令来设置主服务器的参数
如果在配置文件里设置可以不用执行以下命令，直接slavestart;
首先停止slave服务
mysql>slavestop;//停止slave的服务
Java代码
mysql>CHANGEMASTERTO
->MASTER_HOST='master_host_name',//<spanstyle="color:#ff0000;">从服务</span>
器的IP地址
->MASTER_USER='replication_user_name',//同步数据库的用户
->MASTER_PASSWORD='replication_password',//同步数据库的密码
->MASTER_LOG_FILE='recorded_log_file_name',//<spanstyle="color:#ff0000;">从服务器</span>
二进制日志的文件名(上面的参数)
->MASTER_LOG_POS=recorded_log_position;//<spanstyle="color:#ff0000;">从服务器</span>
日志文件的开始位置(上面的参数)
***********************分别在从服务器和主服务器上查看************************
启动同步数据库的线程
Java代码
mysql>slavestart;
查看数据库的同步情况，直到成功。
Java代码
mysql>showslavestatus\G
***************************1.row**************************
Slave_IO_State:Waitingformastertosendevent
Master_Host:202.xx.xx.xx
Master_User:backup
Master_Port:3306
Connect_Retry:60
Master_Log_File:logbin.000002
Read_Master_Log_Pos:930
Relay_Log_File:idc-ojf9p55rkb5-relay-bin.000009
Relay_Log_Pos:1064
Relay_Master_Log_File:logbin.000002
<spanstyle="color:#ff0000;">Slave_IO_Running:Yes
Slave_SQL_Running:Yes</span>
Replicate_Do_DB:backuptest
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:0
Last_Error:
Skip_Counter:0
Exec_Master_Log_Pos:930
Relay_Log_Space:1064
Until_Condition:None
Until_Log_File:
Until_Log_Pos:0
Master_SSL_Allowed:No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:0
1rowinset(0.00sec)
注意红色的，如果是这样，代表成功了.
主要是
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
如果两个均为yes就代表成功了，否则查看配置文件或是用户创建是否有问题
通过命令来设置服务器的参数
三、数据导入
将当前数据导入从服务器数据库中，查看是否成功。
Java代码
showslavestatus\G
Java代码
showmasterstatus\G
==============================liunx双机热备=======================================
linuxmysql双机热备与winmysql双机热备略有不同，至少在win下面很快就OK.
本例使用的是mysql5.1.45编译安装
linux下热备修改/etc/my.cnf
具体修改如下（截图）
彩色部分内容为需要配置的地方
其中紫色本身配置文件中已写入，青色部分为后写入。红线为需要注意的地方，与另一台数据库的机器（库）不可以相同。
另一台机器配置与上相似，只需要修改server-id及ip即可。
二、在2个MYSQL上都要创建用于同步的账号
1、
Mysql>GRANTFILE,REPLICATIONSLAVEON*.*TObackup@'192.168.8.181'IDENTIFIEDBY'123456'
mysql>GRANTALLPRIVILEGESON*.*TObackup@'192.168.8.181'IDENTIFIEDBY'123456'
2.重启2个MYSQL服务
3.在2个MYSQL上都启动同步服务
Mysql>slavestart;
4.在2台服务器上查看备份线程状态
Mysql>showslavestatus;
*如果结果中Slave_IO_Running和Slave_SQL_Runing的值都是Yes，Seconds_Behind_Master的值是0说明配置成功
三、问题与解决
1.现象：Slave_SQL_Running为No,Seconds_Behind_Master为null
原因：a.程序可能在slave上进行了写操作
b.也可能是slave机器重起后，事务回滚造成的.
解决：
a.假设在MYSQL_1上出现这种情况
b.在MYSQL_2上查看主服务器状态:
Mysql>showmasterstatus;
+----------------------+----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+----------------------+----------+--------------+------------------+
|mysql-bin.000001|106|backup_db||
+----------------------+----------+--------------+------------------+
c.在MYSQL_1上停止同步线程：
Mysql>slavestop;
d.在MYSQL_1上手动执行同步
Mysql>changemasterto
>master_host=’192.168.8.181’,
>master_user=’backup’,
>master_password=’123456’,
>master_port=3307,
>master_log_file=’mysql-bin.000001’,
>master_log_pos=106;
e.启动同步服务
mysql>slavestart;
f.通过showslavestatus查看Slave_SQL_Running为Yes,Seconds_Behind_Master为0即为正常。
2.现象：Slave_IO_Running为No,Seconds_Behind_Master为null
解决：
a.假设在MYSQL_1上出现这种情况
b.重启主数据库（MYSQL_2）
c.在MYSQL_2上查看主服务器状态:
Mysql>showmasterstatus;
+----------------------+----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+----------------------+----------+--------------+------------------+
|mysql-bin.000001|106|backup_db||
+----------------------+----------+--------------+------------------+
d.在MYSQL_1上停止同步服务
Mysql>slavestop;
e.在MYSQL_1上执行下列命令
Mysql>changemasterto
>master_log_file=’mysql-bin.000001’,
>master_log_pos=106;
f.在MYSQL_1上重新启动同步服务
Mysql>slavestart;
=========================相关概念=========================
要想用好一个系统，理解其实现原理是非常重要的事情，只有理解了其实现原理，我们才能够扬长避短，合理的利用，才能够搭建出最适合我们自己应用环境的系统，才能够在系统实施之后更好的维护他。MySQLReplication可以说是在目前MySQL数据库的实际应用场景中最为常见的ScaleOut手段了，这里就针对其实现原理做一个简单的分析。
一、Replication线程
Mysql的Replication是一个异步的复制过程，从一个Mysqlinstace(我们称之为Master)复制到另一个Mysqlinstance(我们称之Slave)。在Master与Slave之间的实现整个复制过程主要由三个线程来完成，其中两个线程(Sql线程和IO线程)在Slave端，另外一个线程(IO线程)在Master端。
要实现MySQL的Replication，首先必须打开Master端的BinaryLog(mysql-bin.xxxxxx)功能，否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。打开MySQL的BinaryLog可以通过在启动MySQLServer的过程中使用“—log-bin”参数选项，或者在my.cnf配置文件中的mysqld参数组([mysqld]标识后的参数部分)增加“log-bin”参数项。
MySQL复制的基本过程如下：
1.Slave上面的IO线程连接上Master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;
2.Master接收到来自Slave的IO线程的请求后，通过负责复制的IO线程根据请求信息读取指定日志指定位置之后的日志信息，返回给Slave端的IO线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在Master端的BinaryLog文件的名称以及在BinaryLog中的位置;
3.Slave的IO线程接收到信息后，将接收到的日志内容依次写入到Slave端的RelayLog文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”
4.Slave的SQL线程检测到RelayLog中新增加了内容后，会马上解析该Log文件中的内容成为在Master端真实执行时候的那些可执行的Query语句，并在自身执行这些Query。这样，实际上就是在Master端和Slave端执行了同样的Query，所以两端的数据是完全一样的。
实际上，在老版本中，MySQL的复制实现在Slave端并不是由SQL线程和IO线程这两个线程共同协作而完成的，而是由单独的一个线程来完成所有的工作。但是MySQL的工程师们很快发现，这样做存在很大的风险和性能问题，主要如下：
首先，如果通过一个单一的线程来独立实现这个工作的话，就使复制Master端的，BinaryLog日志，以及解析这些日志，然后再在自身执行的这个过程成为一个串行的过程，性能自然会受到较大的限制，这种架构下的Replication的延迟自然就比较长了。
其次，Slave端的这个复制线程从Master端获取BinaryLog过来之后，需要接着解析这些内容，还原成Master端所执行的原始Query，然后在自身执行。在这个过程中，Master端很可能又已经产生了大量的变化并生成了大量的BinaryLog信息。如果在这个阶段Master端的
存储
系统出现了无法修复的故障，那么在这个阶段所产生的所有变更都将永远的丢失，无法再找回来。这种潜在风险在Slave端压力比较大的时候尤其突出，因为如果Slave压力比较大，解析日志以及应用这些日志所花费的时间自然就会更长一些，可能丢失的数据也就会更多。
所以，在后期的改造中，新版本的MySQL为了尽量减小这个风险，并提高复制的性能，将Slave端的复制改为两个线程来完成，也就是前面所提到的SQL线程和IO线程。最早提出这个改进方案的是Yahoo!的一位工程师“JeremyZawodny”。通过这样的改造，这样既在很大程度上解决了性能问题，缩短了异步的延时时间，同时也减少了潜在的数据丢失量。
当然，即使是换成了现在这样两个线程来协作处理之后，同样也还是存在Slave数据延时以及数据丢失的可能性的，毕竟这个复制是异步的。只要数据的更改不是在一个事务中，这些问题都是存在的。
如果要完全避免这些问题，就只能用MySQL的Cluster来解决了。不过MySQL的Cluster知道笔者写这部分内容的时候，仍然还是一个
内存
数据库的解决方案，也就是需要将所有数据包括索引全部都Load到内存中，这样就对内存的要求就非常大的大，对于一般的大众化应用来说可实施性并不是太大。当然，在之前与MySQL的CTODavid交流的时候得知，MySQL现在正在不断改进其Cluster的实现，其中非常大的一个改动就是允许数据不用全部Load到内存中，而仅仅只是索引全部Load到内存中，我想信在完成该项改造之后的MySQLCluster将会更加受人欢迎，可实施性也会更大。
