生命不息
折腾不止

实战mysql双机实时热备份

以前数据库备份采用的方式一般都是每天定时使用 mysqldump 到处 sql 文件,然后传到异地备份。不过这种定时备份有个问题就是如果服务器出问题了,那么这个备份和实际的业务数据总有一段时间的差异,丢失一部分数据。对于我这样的博客基本每天发一两篇还好,大不了丢一些评论数据,但是像一些 whmcs 做商业业务的,那可就意味着可能丢失一部分商业数据,比如付费数据啥的,那就比较头大了。

所以对于对数据实时性要求比较高的业务, mysql 的数据库的双机热备份就显得比较重要。我也是这两天才摸索着搞起来,把步骤总结下,希望对有需要的同学会有作用。

准备工作

  1. 两台服务器。建议异地。
  2. 装了 相同版本 的 mysql (为了避免各种不必要的麻烦,强烈建议 mysql 版本务必一致)
  3. Iptables 确保两台服务器的 3306 端口都是通的。
  4. 下面为了说明方便,我把要备份的服务器称为“主服务器 A ”,把备份服务器称为“从服务器 B ”
  5. 强烈建议不要直接在业务服务器上操作,先拿测试服务器做好测试,确定走通流程再对业务服务器进行操作

主服务器 A 的相关设置:

第一步:停止所有业务,避免有新的数据库写入

比如 whmcs 和 wordpress 的维护模式

第二步:导出数据库,以便待会导入从服务器

mysqldump -u 用户  -p 密码   数据库  > backup.sql

以下是一个事例(假设用户是 root ,密码是 1234 ,需要备份的数据库是 91yun ):

mysqldump -uroot-p1234 91yun > backup.sql

如果你有多个数据库需要备份,就按这命令依序备份。

第三步:修改主服务器 A 的 mysql 配置文件

一般情况,配置 mysql 的位置文件在 /etc/my.cnf

[mysqld] 标签下增加以下内容

log-bin=mysql-bin
binlog_format=mixed
server-id	= 1

read-only=0
binlog-do-db=91yun
auto-increment-increment=10
auto-increment-offset=1

log-bin=mysql-bin     # 一般配置文件已经自带了,就是开启日志
binlog_format=mixed # 一般配置文件自带了
server-id = 1 # 这个需要特别注意下,每个服务器都需要配置不同的 ID ,这里我们把主服务器设成 1 ,后面的从服务器我们就要设置成 2 了

read-only=0
binlog-do-db=91yun   # 这里是要备份的数据库,如果要备份多个数据库可以重复多行 , 这里的意思就是我要备份 91yun  这个数据库
auto-increment-increment=10 # 这个数字是假设有 10 台服务器进行互相备份,影响一些自增长字段的自增长量
auto-increment-offset=1 # 这个 确保每个服务器不同 ,并不高于上面这个参数,影响自增长字段的自增长量

设置完后请重启 mysql 服务,以便设置生效 service mysql restart

第四步:在主服务器 A 连上 mysql

mysql -u 用户  -p 密码 

mysql 的连接命令为 mysql -uroot   -p1234   ( 这里假设用户是 root ,密码是 1234) 需要注意的是, u 和用户, p 和密码之间并没有空格。

第五步:在主服务器 A 上创建用于备份的用户

该命令需要在上步连接 mysql 后,运行于 mysql 的命令行模式

grant replication slave on *.* to ' 用户名 '@' 从服务器 B 的 IP' identified by ' 用户密码 ';

需要在把上面命令的用户名,密码和从服务器的 IP 换成你自己的。下面是一个例子:

grant replication slave on *.* to 'slave1'@'192.168.3.2' identified by 'IHEnZ1y6J6';

该命令的意思就是:创建一个用于备份的用户,用户名是 slave1 ,密码是 IHEnZ1y6J6 ,这个用户只允许从 192.168.3.2 这个 IP 的机子上登陆

需要注意的是, mysql 的所有命令都是以分号结尾的

第六步:取得主服务器 A 的 mysql 目前的状态

该命令也是运行于 mysql 的命令行模式

show master status\G

请记下这个 File 和 Position 的内容!

下面开始修改从服务器 B 相关的设置

第一步:导入刚才 A 服务器到处的 Sql

这一步主要是确保两台服务器数据库的初始状态是一样的,如果这两个数据库已经一样了,那就可以略过了。

一般导入的数据库的命令:

mysql -u 用户  -p 密码   数据库  < backup.sql

下面是一个例子(假设用户是 root ,密码是 1234 ,导入的数据库是 ss100 )

mysql -uroot -p1234 ss100 < backup.sql

第二步:修改从服务器 B 的 mysql 配置

从这一步开始,就开始修改从服务器 B 的相关设置了

一样的, mysql 的配置文件一般在 /etc/my.cnf

[mysqld] 标签下,增加以下代码:

log-bin=mysql-bin
binlog_format=mixed
server-id       = 2

replicate-do-db=91yun
relay_log=mysqld-relay-bin
log-slave-updates=ON

log-bin=mysql-bin # 一般默认都有了,开启 log
binlog_format=mixed # 一般默认也有了,格式
server-id = 2 # 需要注意这个,因为主服务器设置了 1 ,所以这里我们就设成 2

replicate-do-db=91yun # 这个是需要复制的数据库
relay_log=mysqld-relay-bin # 这行照抄,不用改
log-slave-updates=ON

第三步:连接上从服务器 B 的 mysql 命令行模式

mysql -u 用户  -p 密码 

进入 mysql 命令行模式

第四步:给从服务器 B 设置主服务器的相关信息

在命令行模式下运行下列命令:

CHANGE MASTER TO MASTER_HOST = ' 主服务器 A 的 IP', MASTER_USER = ' 备份的用户名 ', MASTER_PASSWORD = ' 用户密码 ', MASTER_LOG_FILE=' 主服务器的 File',MASTER_LOG_POS= 主服务器 A 的 Position;

MASTER_HOST # 主服务器 A 的 IP

MASTER_USER # 刚才在从服务器 A 上设置过的用于备份的用户名

MASTER_PASSWORD # 刚才在服务器 A 上设置过的用户的密码

MASTER_LOG_FILE # 刚才主服务器 A 上最后一步让大家记住的主服务器 A 状态的 File

MASTER_LOG_POS # 刚才主服务器 A 上最后一步让大家记住的主服务器 A 状态的 Position;

以下是一个例子:

CHANGE MASTER TO MASTER_HOST = '192.168.3.1', MASTER_USER = 'slave1', MASTER_PASSWORD = 'IHEnZ1y6J6', MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=449;

第五步:启动 slave

在 mysql 的命令行模式下输入:

start slave;

第六步:查看从服务器是否正常运行

在 mysql 的命令行模式下,运行以下命令:

show slave status\G

请务必确保 Slave_IO_Running 和 Slave_SQL_Running 的值都是 yes ,就表示成功了,如果任何一个是 NO 那就说明没有成功。

比较常见的错误是防火墙的设置导致 3306 连不上。请确保 iptables 设置正确或者关闭。

第七步:测试是否同步

你现在可以通过 phpmyadmin 在服务器 A 里面随便改个字段或者加个内容,看看从服务器 B 是否有相应的变化。

 

如果你需要 B 同时也具有写入功能,那你就把服务器 B 作为 master 主服务器, A 作为从服务器,按上面步骤从新再来一遍。那就可以确保 A 和 B 处于互相备份状态。

 

赞(0)
未经允许不得转载:91云(91yun.co) » 实战mysql双机实时热备份

留言 9

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
  1. #0

    这个早就会了啊。。

    大屌哥4年前 (2017-03-26)回复
  2. #0

    对可用性要求高的,可以来看看这个
    https://github.com/SOHUDBA/SOHU-DBProxy

    北树4年前 (2017-03-24)回复
    • 66666

      91yun4年前 (2017-03-24)回复
  3. #0

    这应该只有实时增量备份,如果a挂了要手动换到b
    加上keepalive就要注意脑裂

    James4年前 (2017-03-23)回复
  4. #0

    求一个mysql读写分离的教程,谢谢

    桔仔4年前 (2017-03-23)回复
  5. #0

    学习一个,顺带这样热备份大概占用多少内存?

    Yandex4年前 (2017-03-23)回复
    • 和内存没什么关系

      91yun4年前 (2017-03-23)回复
  6. #0

    myphpadmin 纠正为 phpMyAdmin

    雨落无声4年前 (2017-03-23)回复
    • 感谢,已更正。

      91yun4年前 (2017-03-23)回复