mysql双机热备份的实现步骤

MySQL 提供了数据库的同步功能,这对我们实现数据库的冗灾、备份、恢复、负载均衡等都是有极大帮助的。本文描述了常见的同步设置方法。

同步有两种形式:

“主-从”,和“主-主”。

一:“主-从”方式

IXDBA.NET社区论坛

1、准备服务器

由于MySQL不同版本之间的(二进制日志)binlog格式可能会不一样,因此最好的搭配组合是Master的MySQL版本和Slave的版本相同或者更低,Master的版本肯定不能高于Slave版本。

本文中,我们假设主服务器(以下简称Master)和从服务器(以下简称Slave)的版本都是4.1.24,操作系统是Redhat Linux AS4.0

假设

同步Master的主机ip为:192.168.60.219,Slave主机ip为:192.168.60.132,2个MySQL的basedir目录都是/usr/local/mysql,datadir都是:/usr/local/mysql/data(即为mysql数据库的默认数据目录)。

2、设置同步服务器

(1)、设置同步Master

每个同步服务器都必须设定一个唯一的编号,否则同步就不能正常运行了。接下来配置数据库同步配置文件,mysql数据库的配置文件默认名为my.cnf,数据库启动的时候从默认的data目录下读取my.cnf的信息,如果data下没有这个文件,可以建立一个,my.cnf的模板默认一般在mysql/support-files目录下,在此目录下有各种情况下的cnf文件,如my-huge.cnf,my-large.cnf,my-medium.cnf,my-small.cnf等等,根据情况,选择my-medium.cnf即可,然后拷贝到data下,改名为my.cnf.开始修改 my.cnf,增加以下几行:

log-bin

server-id = 1 (标识为master库)

sql-bin-update-same

binlog-ignore-db=mysql

set-variable=binlog-ignore-db=mysql #指定需要日志的数据库

然后在Master上增加一个账号专门用于同步,如下:

mysql>GRANT REPLICATION SLAVE ON *.* TO backup@192.168.60.132 IDENTIFIED BY '1234';

如果想要在Slave上有权限执行 "LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER" 语句的话,必须授予全局的 FILE 和 SELECT 权限:

mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO backup@192.168.60.132 IDENTIFIED BY '1234';

上面my.cnf文件中第四行表示不记录数据库mysql的更新日志,这就避免了Master上的权限设置等被同步到Slave上,如果对这方面没有限制,就可以不设置这个参数。

最后设置完成my.cnf,设置完成后,首先检查mysql/data目录下是否有mysql的启动关闭日值,类似的为*.info,localhost*,ib*之类的日值信息。如果有删除,然后在master服务器上启动mysql。可以通过查看data下的localhost.localdomain.err文件,测试master于Slave的连通情况。,同时用show slave status看同步配置情况。

接下来备份Master上的数据,首先执行如下SQL语句:

mysql>FLUSH TABLES WITH READ LOCK;

不要退出这个终端,否则这个锁就不生效了;接着导出数据,可以直接打包压缩数据文件,也可以使用mysqldump工具来做,推荐前者的方法,这样更为快捷简便。

root$cd /usr/local/mysql

root$tar zcf data.tar.gz ./data (在这里也可能是 "var" 等其它实际存放数据文件的目录,根据实情而定)

然后将这些数据拷贝到Slave服务器上,解开,设置好正确的权限及属主等之后, 启动Slave数据库,然后用"UNLOCK TABLES" 语句来释放锁。然后关闭数据库。

(2)、设置Slave

同主数据库一样,找到my.cnf的模板,修给成my.cnf,增加如下几行:

server-id = 2 (Slave服务器标识)

master-host = 192.168.60.219 #主服务器名

master-user = backup #同步账户名,默认是test

master-password =1234 #同步帐户密码,默认是空

master-port = 3306 #主服务器的 TCP/IP 端口号,默认是3306

set-variable=replicate-ignore-db=mysql #略过同步的数据库名,如果有多个,请设置多次

set-variable=replicate-do-db=cicro #想要同步的数据库名,如果有多个,请设置多次。

master-connect-retry=10 预设重试间隔60秒

设置完成后,首先检查mysql/data目录下是否有mysql的启动关闭日值,类似的为*.info,localhost*,ib*之类的日值信息。如果有删除,然后在Slave服务器上启动mysql,然后在Slave上检验一下是否能正确连接到Master上,并且具备相应的权限。

可以通过查看data下的localhost.localdomain.err文件,测试Slave与master的连通情况。

root$mysql –h192.168.60.219 –ubackup –p 1234

mysql>SHOW GRANTS;

+--------------------------------------------------------------------------------------------------------------------------------------+

| Grants for backup@192.168.60.132 |

+--------------------------------------------------------------------------------------------------------------------------------------+

| GRANT SELECT, FILE, REPLICATION SLAVE ON *.* TO 'backup'@'192.168.60.132' IDENTIFIED BY PASSWORD '*9FF2C222F44C7BBA5CC7E3BE8573AA4E1776278C' |

+--------------------------------------------------------------------------------------------------------------------------------------+

现在,重新启动Slave。启动成功后,登录Slave,查看一下同步状态:

mysql -hlocalhost -uroot

mysql>SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.60.132

Master_User: backup

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: localhost-bin.000002

Read_Master_Log_Pos: 1556

Relay_Log_File: localhost-relay-bin.000004

Relay_Log_Pos: 51

Relay_Master_Log_File: localhost-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: cicro,cicro

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: 1556

Relay_Log_Space: 51

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

1 row in set (0.00 sec)

可以看到,Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 "Yes",这表明 Slave 的 I/O 和 SQL 线程都在正常运行。

至此,主-从同步设定成功。

二:“主-主”方式:

如果在master中加入slave设置,在Slave加入master设置,则就成了“主-主”同步方式。

1:配置master服务器

接着上面的配置,在master的my.cnf中加入一下配置信息:

master-host=192.168.60.132

master-user=backup

master-password=1234

replicate-do-db=cicro

master-connect-retry=10 预设重试间隔60秒

2:配置slave服务器:

首先在slave上增加一个账号专门用于同步,如下:

mysql> GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO backup@192.168.60.219 IDENTIFIED BY '1234';

在slave的配置文件my.cnf中添加以下信息:

binlog-do-db=cicro

注意:当有错误产生时检查slave的data目录下*.err日志文件。此时同步的线程退出,当纠正错误后要让同步机制进行工作,运行slave start。

同样,首先删除两台服务器data下的日志信息,然后依次启动slave,master。实现双机互备。

测试:

  向slave批量插入大数据量表AA(1872000)条,master数据库每秒钟可以更新2500条数据。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java帮帮-微信公众号-技术文章全总结

Mysql锁机制分析【面试+工作】

数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则;对于任何一种数据库来说都需要有相应的锁定机制,My...

1262
来自专栏漏斗社区

工具| 诸神之眼nmap定制化之NSE进阶

上一期斗哥跟大家介绍了Nmap中NSE脚本和常见的NSE的API,本期将为大家介绍Nmap的库文件以及如何利用Nmap的自身库将nmap的扫描结果保存在数据中。...

3447
来自专栏MasiMaro 的技术博文

数据更新接口与延迟更新

title: 数据更新接口与延迟更新 tags: [OLEDB, 数据库编程, VC++, 数据库] date: 2018-02-12 14:29:35 ...

1482
来自专栏杨建荣的学习笔记

通过shell分析表依赖的层级关系(r3笔记第97天)

在平时的工作环境中,总会有一些表会存在依赖关系,比如我们有三张表customer,用户表subscriber,账户表account 其中客户可以有多个用户或者账...

31710
来自专栏bboysoul

磁盘分析工具Ncdu的简单使用和体验

因为我使用的是120g的固态硬盘,所以我的本地就不能存很多东西,经常要删除很多东西,所以看每个目录的大小就很重要,下面推荐一个磁盘分析工具Ncdu,这个工具是用...

731
来自专栏杨建荣的学习笔记

dataguard备库的数据文件的迁移实战(r8笔记第24天)

在前几天也花了一点时间测试了一下关于备库数据文件的迁移,这部分的工作看起来还是比较常规的,当然方法也很多。但是在实际工作中就更不能掉以轻心,所有 的操作都要有理...

35311
来自专栏Porschev[钟慰]的专栏

Nodejs学习笔记(十五)--- Node.js + Koa2 构建网站简单示例

前言   前面一有写到一篇Node.js+Express构建网站简单示例https://cloud.tencent.com/developer/article/...

2.2K9
来自专栏沃趣科技

ASM 翻译系列第三十二弹:自制数据抽取小工具

原作者:Bane Radulovic 译者: 邱大龙 审核: 魏兴华 DBGeeK社区联合出品 Find block in ASM 在本系列文章【 ...

46910
来自专栏码字搬砖

Hbase常用命令

6692
来自专栏Java架构师历程

mysql 谈谈innodb存储引擎

5.7版本引入了模式自动转换的功能,但该语法依然保留了。 另外一个有趣的点是,在5.7版本中,你可以通过设置session_track_transactio...

3632

扫码关注云+社区

领取腾讯云代金券