http://www.toutiao.com/a6348218076932538625/?tt_from=mobile_qq&utm_campaign=client_share&app=explore_article&utm_source=mobile_qq&iid=5840657922&utm_medium=toutiao_ios

Mysql 双机热备 设置策略

UI视频教程  2016-11-02 12:15:08

可以替换的配置变量

Master:219.245.87.10

slave的授权账号：backup

slave的授权账号密码：[email protected]

master的同步数据库：lian

---------------------------------------------------------------------------------------------------------------

/etc/init.d/iptalbes start 开启命令

/etc/init.d/iptables restart 重启命令

1.修改my.cnf

(1)在[mysqld]后加入:

server-id=1

#同步事件的日志记录文件，最好不要写参数，默认为 log-bin=MySQL-bin.000001

log-bin=

#提供数据同步服务的数据库,多个用逗号分开。在slave 端配置即可

binlog-do-db=lian

#要忽略的数据库

binlog-ignore-db=mysql

(2)屏蔽下面的设置，打开远程连接

#bind-address = 127.0.0.1

2.可以导出要同步的数据库给slave初始化用.

2.1 适用于两个已存在的数据库

mysqldump [OPTIONS] database [tables]

mysqldump -u root -p backup_db > backup_db.txt;//导出backup_db库

恢复时使用:

mysql>create database backup_db;

mysql>use backup_db;

mysql>source /var/www/db.txt //win下可能需要<符号

mysqldump -u root -p --all-database > db.txt //导出全部

mysqldump -u root -p --databases DB1 DB2 DB2_table1 >db.txt

2.2 适用于slave为新的数据库

适合于新建数据库的情况，特别适合于 master 与 slave 在 my.cnf 已经设置好 replication 关系 （但未指定 database 同步数据库 ) 的情况: 这就是使用　mysql 的 sql 语句 load table from master 与 load data from master;

load table from master 可以从 master 数据库把表结构复制到 slave 数据库中，这样可以建立同步的表。

load data from master 是从 master 数据库把数据导入到 slave 数据表中，条件是 master 从一开始安装运行就使用了 bin-log 参数而保存有二进制日志

3.给slave建立的授权登录帐号

mysql>GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* TO [email protected] IDENTIFIED BY '[email protected]';

mysql> flush privileges;

mysql> select user,host from user;

+——–+——————+

| user | host |

+——–+——————+

| root | 127.0.0.1 |

| backup | 192.168.254.2 |

| | localhost |

| root | localhost |

| | nod1.test.domain |

| root | nod1.test.domain |

+——–+——————+

6 rows in set (0.01 sec)

（为强安全选项，权限少。mysql>GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY '[email protected]';）

注意:backup,1234为slave的登录帐号

4.重启mysql，

mysql>show master status /G

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

File: mysql-bin.000001

Position: 98

Binlog_Do_DB: lian

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

Slave:10.10.10.22

slave做更新的数据库：lian

---------------------------------------------------------------------------------------------------------------

1.修改my.cnf

(1)在[mysqld]后加入:

server-id=2 # 如果有多个 slave 就改为不重复的 id 就好，在mysql 4.1 中，这个已经取消了

master-host=10.10.10.22

master-user=backup #同步用户帐号

[email protected]

master-port=3306

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

replicate-do-db=lian # 告诉slave只做 test 数据库的更新

bin-log =

(2)可选；双向备份时必须！屏蔽下面的设置，打开远程连接,

#bind-address = 127.0.0.1

2.导入目标数据库

2.1 恢复时使用:

mysql>create database backup_db;

mysql>use backup_db;

mysql>source /var/www/db.txt //win下可能需要<符号

2.2 适用于slave为新的数据库

适合于新建数据库的情况，特别适合于 master 与 slave 在 my.cnf 已经设置好 replication 关系 （但未指定 database 同步数据库 ) 的情况: 这就是使用　mysql 的 sql 语句 load table from master 与 load data from master;

load table from master 可以从 master 数据库把表结构复制到 slave 数据库中，这样可以建立同步的表。

load data from master 是从 master 数据库把数据导入到 slave 数据表中，条件是 master 从一开始安装运行就使用了 bin-log 参数而保存有二进制日志

3.重启mysql

mysql>slave start;

mysql>show slave status /G

=============确保===============

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

##############################################################################################################

1. master 与 slave 有相同的初始数据记录，保证同步操作开始后两者数据一致

2. master 必须使用 bin-log 二进制日志记录 ( 推荐 slave 也使用 bin-log )

3.当有错误产生时检查slave的data目录下*.err日志文件。此时同步的线程退出

4.如果你用 Linux / unix 操作系统，那一定要注意一下防火墙 firewall

有没有限制 mysql 远程访问，如果是，最好是打开远程访问端口，并作好访问 ip 限制

5. slave 是从 master 的 bin-log 是读取 sql 记录来同步，所以，从哪一条 log 开始读取很重要

这样即执行了手工同步

6.程序可能在slave上进行了写操作

7.也可能是slave机器重起后，事务回滚造成的.

手工同步方法

----------

1.首先停掉Slave服务：slave stop

2.到主服务器上查看主机状态：

记录File和Position对应的值。

mysql> show master status /G

File: log-test.000003

Position: 98

3.到slave服务器上执行手动同步：

mysql>change master to

master_host='202.117.113.138',

master_user='backup',

master_password='1234',

master_port=3306,

master_log_file='log-test.000003',

master_log_pos=98;

4.开启slave服务并测试:

mysql> slave start;

1 row in set (0.00 sec)

再次查看slave状态发现：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

...

Seconds_Behind_Master: 0