MySQL 双机互备配置文档
张赟玥 .2009-12-4
目录
1 文档简述
该文档主要是针对在两种不同的操作系统( Windows,Linux )下实现 MySQL 双机互备做的详细说明。文档对两种操作系统下的配置步骤分别做了详细讲述,并在做了对比和总结。另外 MySQL 双机互备必须具备以下几个前提条件:
1. 网络畅通。
2. 拥有访问和操作 MySQL 的权限。
详细步骤请参阅下文。
2 Windows 环境下的配置步骤
2.1 检查网络连接状态
2.1.1 获得主从机IP 地址
要配置 MySQL 双机互备,首先要有两个用来做备份的 MySQL 服务器,要实现同步更新数据,首先要知道对方的 IP 。
在安装有 MySQL 的连个服务器上进入命令行模式如下:
-- à 运行
-- à cmd
-- à ipconfig
依次执行完上面的命令将获得主从机两台服务器的 IP 地址,我的实例中主机 IP 为 10.1.1 .105 ,从机 IP 为 10.1.1.200 。
2.1.2 检查主从机网络连接状态
获得了两台 MySQL 服务器的IP地址还需要验证这两台服务器之间是否能够互相 ping 通,即要了解网络连接状态。在命令行中执行如下命令:
主机 master 端:
-- à ping 10.1.1.200
从机 slave 端:
-- à ping 10.1.1.105
如果可以互相 ping 通则可以继续下面的操作,如果出现一个 ping 不通的情况,需检查网络设置和防火墙设置,最佳是关闭防火墙,直到网络畅通。
2.2 创建要备份的数据库
创建需要同步的数据库,在主机和从机中分别使用帐号 root 登录 MySQL 服务器,如:
Mysql> create database testdb ;
2.3 创建用于同步的用户并赋予其操作权限
2.3.1 创建同步帐号
在主机和从机上分别执行下面的命令创建用于同步操作数据库的帐号,并赋予其操作同步数据库的权限。执行命令如下:
Mysql> grant all privileges on *.* to zhangyy@’%’ identified by ‘1234’;
语句解释: grant 是赋予权限的关键字
all privileges 是指对数据库操作的所有权限,这里主要功能是同步数据库即互相复制的权限,可将其修改为 replication 。
*.* 指的是 MySQL 服务器中所有的数据库下所有数据表,在此如果只需要同步数据库 testdb 下的所有表,可将其修改为 testdb.* 。
Zhangyy 是用于同步的帐号,其密码为 1234 。
% 表示的是所有 IP 地址,为了安全也可以指定某个 IP 地址,主机上写从机的 IP:10.1.1.200 ,从机上写主机的 IP 地址: 10.1.1.105 。
整句的意思就是:赋予密码为 1234 的帐号 zhangy 访问操作 % 上 MySQL 服务中所有数据库的数据的权限。
Mysql> flush privileges ;
---- 刷新权限,必须,否则在使用过程中,使用的是修改之前的权限。
2.3.2 测试同步帐号
测试同步帐号,在“运行” -- à cmd 中测试。
主机 master 上:
-- à mysql –h10.1.1.200 –u zhangyy –p
-- à 输入帐号 zhangyy 对应的密码 1234
从机 slave 上 :
-- à mysql –h10.1.1.105 –u zhangyy –p
-- à 输入帐号 zhangyy 对应的密码 1234
如果都能登录到对方的 MySQL 服务中并且能看到要同步的数据库,则帐号测试成功,否则需要检查各自的帐号是否存在以及其权限问题。可使用各自的 root 帐号登录 MySQL 服务器执行以下命令进行查看:
Mysql > use mysql ;
Mysql > select user,host from user; --- 执行后如图:
Mysql > show grants for zhangyy; --- 执行完毕如下图:
2.4 配置MySQL 的配置文件
网络连接状态正常,创建了同步数据库和同步帐号并且同步帐号测试成功之后,最核心的就是对配置文件的修改了。双机互备配置时可先设置单向的备份,即主机操作,从机同步更新。
2.4.1 master 配置
Master 中的配置比较简单,但也非常重要。首先打开其配置文件 my.ini ,找到其配置 [mysqld] ,在该模块中添加以下内容:
server-id =1
log-bin=
参数解释: server-id 是主机的标识,确定其为整数并且唯一,一般设置为 1 。
Log-bin 该项非常重要,也可为其指定值,即存放同步日志的文件的存放位置,并且要确保,该文件可读写。
以下两项参数也可以添加 ( 为避免出错了找不到原因,最好先不要添加,测试同步成功了再加上 ) :
#read-only =0 # 主机读写都可以
#binlog-do-db =testdb # 需要备份数据,多个写多行
#binlog-ignore-db=mysql # 不需要备份的数据库,多个写多行
配置文件修改完毕保存该配置并且删掉 mysql 的 data 目录下除数据库以外的文件。主要是 *.00000* 类似的文件以及 master.info ,为避免出错,一定要先删除。
2.4.2 slave 配置
Slave 中的配置相当重要,一着不慎就可能导致同步失败。同 slave 的配置一样,找到其配置文件 my.ini ,打开找到 [mysqld] 配置段,在里面添加如下内容:
#--B-slave---------------------------------------------------------------
server-id = 2
master-host=10.1.1.105
master-user=zhangyy
master-password=1234
master-port=3306
master-connect-retry=60
replicate-do-db=testdb
#===================================================================
参数说明:
server-id 是从机的标识,确定其为整数并且唯一,一般设定为 2 ;
Master-host 是指主机 master 的 IP 地址;
Master-user 是指用于同步的帐号;
Master-password 是指同步帐号的密码;
Master-port 是指同步的两台 MySQL 服务器使用的端口号,默认为 3306 ;
Master-connect-retry 从服务器发现主服务器断掉,重新连接的时间差 ( 秒 ) ; Replicate-do-db 指定要同步的数据库,有多个就写多行。
配置完毕保存后会发现多了个 master.info 文件以及 relay-log.info 等文件。停止 MySQL 服务,并删除 data 目录下除数据库以外的所有文件,尤其是 master.info 和 relay-log.info 这两个文件必须删除。注意:如果 master 端修改了 my.ini 时, master.info 和 relay-log.info 也必须删除并且重启服务。
2.5 重启MySQL 测试同步状态
配置完毕后,重启主从机,首先重启主机再重启从机。然后执行如下命令测试同步状态:
主机 master :
Mysql > show master status;
命令执行完毕效果如下图:
记住上图中的字段 file 和 position 的值,在 slave 状态中需要检验的。
从机 slave :
Mysql > show slave status/G;
特别提示:检查参数 Slave_IO_State 、 Slave_IO_Running 、 Slave_SQL_Running
Slave_IO_State 的状态值是由 Slave_IO_Running 的值决定的,如果 Slave_IO_Running 和 Slave_SQL_Running 的值都为 Yes ,并且 Slave_IO_State 的值为 :Waiting for master to send event ,那么说明配置是没问题的可以进行下一步同步尝试了。
一般情况下上面三个参数值都正确了其它的参数值也都是正确的。
2.6 问题要点及其解决办法
1.IP 变化导致数据不能同步问题
有时候在同步中,你会发现头一天同步正常,而第二天数据并没有同步的情况,这时就需要确认一下主从机的 IP 地址是否发生了变化。如果已经变更,则需要修改配置文件,并且删除 master.info 和 relay-log.info 之后重启服务。
2. 配置之后在查看 slave 状态时,三个关键参数值不对
Slave_IO_Running:No ----- 复制没有启动,执行命令 slave start io-thread 尝试启动 io 线程
Slave_SQL_Running:No----- 执行命令 slave start sql_thread
执行完后,如果状态值还是不对,检查一下 master 中的 file 和 position 的值和 slave 状态中的 master_log_file 、 master_log_pos 的值分别是否对应。如果不对应 , 执行以下命令修改 slave 的状态值:
Mysql > stop slave;
Mysql >change master to master_log_file=’ mysql-bin.000001 ’,master_log_pos= 106 ;
如其他值也有问题,可在后面累加,以逗号隔开,命令结束时用分号结尾。
2.7 同步数据测试
配置成功后,可进行如下测试:
在 master 上使用帐号 zhangyy 登录,并执行命令:
Mysql >use testdb;
Mysql > create table test(id int);
Mysql >show tables;
在 Slave 机器上使用 zhangyy 登录,并执行命令:
Mysql > use testdb;
Mysql >show tables;
如果从机上可以查看到主机上创建的 table ,则同步成功。
2.8 双机互备配置
上面的配置只是实现了单向的同步,如果在从机上更新数据,主机上就无法看到从机上做的修改,要实现真正的双机互备还需做如下修改:
在 master 的配置文件 my.ini 中 [mysqld] 段加上如下配置:
#--A-slave---------------------------------------------------------------
master-host=10.1.1.200
master-user=zhangyy
master-password=1234
master-port=3306
master-connect-retry=60
replicate-do-db=testdb
#===================================================================
在 slave 的配置文件中 my.ini 中 [mysqld] 段加上如下配置:
#----B--master---------------------------------------------------------------------------------
Log-bin=
#-------------------------------------------------------------------------------------------------
这样就实现真正的双机互备了,你可以 down 其中一台机器或者 MySQL 服务器,然后在另外一个 MySQL 服务器上进行插入、修改或者删除等操作,然后再重启另外一台机器,会发现里面数据也随着更新了。
3 Linux 环境下的配置步骤
Linux 下的配置步骤和 Widows 下基本相同,要注意的主要是防火墙的设置及其文件存放位置。
3.1 检查网络连接状态
3.1.1 获得主从机IP 地址
要配置 MySQL 双机互备,首先要有两个用来做备份的 MySQL 服务器,要实现同步更新数据,首先要知道对方的 IP 。
在安装有 MySQL 的连个服务器上进入命令行模式执行如下命令:
[root@localhost ~]# ifconfig
依次执行完上面的命令将获得主从机两台服务器的 IP 地址,我的实例中主机 masterIP 为 10.1.1 .187 ,从机 slave 的 IP 为 10.1.1.183 。
3.1.2 检查主从机网络连接状态
获得了两台 MySQL 服务器的IP地址还需要验证这两台服务器之间是否能够互相 ping 通,即要了解网络连接状态。在命令行中执行如下命令:
主机 master 端:
[root@localhost~]#ping 10.1.1.187
从机 slave 端:
[root@localhost~]#ping 10.1.1.183
如果可以互相 ping 通则可以继续下面的操作,如果出现一个 ping 不通的情况,需检查网络设置和防火墙设置,最佳是关闭防火墙,直到网络畅通。
3.2.3 修改防火墙
修改主从机的防火墙,执行如下命令:
[root@localhost~]# vim /etc/sysconfig/iptables;
在文件中拷贝设置端口 22 的语句,粘贴在该语句下面,并将 22 修改为 3306 ,即开放 3306 端口:
-A RH –Firewall -1 -INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
修改完毕保存并且执行下面的语句重启 firewall 服务:
[root@localhost~]# service iptables restart
3.2 创建要备份的数据库
创建需要同步的数据库,在主机和从机中分别使用帐号 root 登录 MySQL 服务器,如:
Mysql> create database testdb ;
3.3 创建用于同步的用户并赋予其操作权限
3.3.1 创建同步帐号
在主机和从机上分别执行下面的命令创建用于同步操作数据库的帐号,并赋予其操作同步数据库的权限。执行命令如下:
Mysql> grant all privileges on *.* to zhangyy@’%’ identified by ‘1234’;
语句解释: grant 是赋予权限的关键字
all privileges 是指对数据库操作的所有权限,这里主要功能是同步数据库即互相复制的权限,可将其修改为 replication 。
*.* 指的是 MySQL 服务器中所有的数据库下所有数据表,在此如果只需要同步数据库 testdb 下的所有表,可将其修改为 testdb.* 。
Zhangyy 是用于同步的帐号,其密码为 1234 。
% 表示的是所有 IP 地址,为了安全也可以指定某个 IP 地址,主机上写从机的 IP:10.1.1.183 ,从机上写主机的 IP 地址: 10.1.1.187 。
整句的意思就是:赋予密码为 1234 的帐号 zhangy 访问操作 % 上 MySQL 服务中所有数据库的数据的权限。
Mysql> flush privileges ;
---- 刷新权限,必须,否则在使用过程中,使用的是修改之前的权限。
3.3.2 测试同步帐号
测试同步帐号:
主机 master 上:
[root@localhost~]# mysql –h10.1.1.183 –u zhangyy –p
-- à 输入帐号 zhangyy 对应的密码 1234
从机 slave 上 :
[root@localhost~]# mysql –h10.1.1.187 –u zhangyy –p
-- à 输入帐号 zhangyy 对应的密码 1234
如果都能登录到对方的 MySQL 服务中并且能看到要同步的数据库,则帐号测试成功,否则需要检查各自的帐号是否存在以及其权限问题。可使用各自的 root 帐号登录 MySQL 服务器执行以下命令进行查看:
Mysql > use mysql ;
Mysql > select user,host from user; --- 执行后如图:
Mysql > show grants for zhangyy; --- 执行完毕如下图:
3.4 配置MySQL 的my.cnf 文件
Linux 中 MySQL 的配置文件存放位置与 Windows 下不一样。可以执行下列命令寻找: [root@localhost tool]# rpm -ql MySQL-server-community-5.1.22-0.rhel42.4.1
可看到如下信息:
/etc/my.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf
如果 /etc/ 目录下没有 my.cnf 文件,可将 /usr/share/mysql/my-medium.cnf 拷贝至 /etc/ 下命名为 my.cnf 。
3.4.1 master 配置
执行以下命令,找到 my.cnf 配置文件:
[root@localhost ~]# vim /etc/my.cnf
在 my.cnf 的配置 [mysqld] ,在该模块中添加以下内容 (master 端一般无需配置, Linux 下默认已经配置了下面的内容 ) :
server-id =1
log-bin=
配置文件修改完毕保存该配置并且删掉 mysql 的 data 目录下除数据库以外的文件。主要是 *.00000* 类似的文件以及 master.info ,为避免出错,一定要先删除。
3.4.2 slave 配置
同 slave 的配置一样,找到其配置文件 my.cnf ,打开找到 [mysqld] 配置段,在里面添加如下内容: (Linux 中有注释的样例,另外要注意的是配置 slave 时首先将配置文件中默认的 server-id=1 和 log-bin 注释掉 )
#--B-slave---------------------------------------------------------------
#Server-id=1
#log-bin=
server-id = 2
master-host=10.1.1.183
master-user=zhangyy
master-password=1234
master-port=3306
master-connect-retry=60
replicate-do-db=testdb
#===================================================================
配置完毕保存后会发现多了个 master.info 文件以及 relay-log.info 等文件。停止 MySQL 服务,并删除 data 目录下除数据库以外的所有文件,尤其是 master.info 和 relay-log.info 这两个文件必须删除。注意:如果 master 端修改了 my.ini 时, master.info 和 relay-log.info 也必须删除并且重启服务。
3.5 重启MySQL 测试同步状态
配置完毕后,重启主从机,首先重启主机再重启从机。然后执行如下命令测试同步状态:
主机 master :
Mysql > show master status;
命令执行完毕效果如下图:
记住上图中的字段 file 和 position 的值,在 slave 状态中需要检验的。
从机 slave :
Mysql > show slave status/G;
特别提示:检查参数 Slave_IO_State 、 Slave_IO_Running 、 Slave_SQL_Running
Slave_IO_State 的状态值是由 Slave_IO_Running 的值决定的,如果 Slave_IO_Running 和 Slave_SQL_Running 的值都为 Yes ,并且 Slave_IO_State 的值为 :Waiting for master to send event ,那么说明配置是没问题的可以进行下一步同步尝试了。
一般情况下上面三个参数值都正确了其它的参数值也都是正确的。
3.6 问题要点及其解决办法
1.IP 变化导致数据不能同步问题
有时候在同步中,你会发现头一天同步正常,而第二天数据并没有同步的情况,这时就需要确认一下主从机的 IP 地址是否发生了变化。如果已经变更,则需要修改配置文件,并且删除 master.info 和 relay-log.info 之后重启服务。
2. 配置之后在查看 slave 状态时,三个关键参数值不对
Slave_IO_Running:No ----- 复制没有启动,执行命令 slave start io-thread 尝试启动 io 线程
Slave_SQL_Running:No----- 执行命令 slave start sql_thread
执行完后,如果状态值还是不对,检查一下 master 中的 file 和 position 的值和 slave 状态中的 master_log_file 、 master_log_pos 的值分别是否对应。如果不对应 , 执行以下命令修改 slave 的状态值:
Mysql > stop slave;
Mysql >change master to master_log_file=’ mysql-bin.000001 ’,master_log_pos= 106 ;
如其他值也有问题,可在后面累加,以逗号隔开,命令结束时用分号结尾。
3.7 同步数据测试
配置成功后,可进行如下测试:
在 master 上使用帐号 zhangyy 登录,并执行命令:
Mysql >use testdb;
Mysql > create table test(id int);
Mysql >show tables;
在 Slave 机器上使用 zhangyy 登录,并执行命令:
Mysql > use testdb;
Mysql >show tables;
如果从机上可以查看到主机上创建的 table ,则同步成功。
3.8 双机互备配置
上面的配置只是实现了单向的同步,如果在从机上更新数据,主机上就无法看到从机上做的修改,要实现真正的双机互备还需做如下修改:
在 master 的配置文件 my.ini 中 [mysqld] 段加上如下配置:
#--A-slave---------------------------------------------------------------
master-host=10.1.1.187
master-user=zhangyy
master-password=1234
master-port=3306
master-connect-retry=60
replicate-do-db=testdb
#===================================================================
在 slave 的配置文件中 my.ini 中 [mysqld] 段加上如下配置:
#----B--master---------------------------------------------------------------------------------
Log-bin=
#-------------------------------------------------------------------------------------------------
这样就实现真正的双机互备了,你可以 down 其中一台机器或者 MySQL 服务器,然后在另外一个 MySQL 服务器上进行插入、修改或者删除等操作,然后再重启另外一台机器,会发现里面数据也随着更新了。