Home > Database > Mysql Tutorial > 两mysql数据库同步实现异步备份方法_MySQL

两mysql数据库同步实现异步备份方法_MySQL

WBOY
Release: 2016-06-01 13:44:42
Original
1112 people have browsed it

bitsCN.com

1.服务器状态
服务器A:192.168.1.1
服务器B:192.168.1.2

2.创建同步用户
主机域A:192.168.1.2  用户名A:sync_a  密码A:aaa
主机域B:192.168.1.1  用户名B:sync_b  密码B:bbb
至少分配以下权限grant replication slave

3.执行flush privileges

4.停止MySQL

5.配置my.cnf(my.ini)

服务器A 服务器B
user = mysql教程
log-bin = mysql-bin
server-id = 1
binlog-do-db = test
binlog-ignore-db = mysql
replicate-do-db = test
replicate-ignore-db = mysql
log-slave-updates
slave-skip-errors = all
sync_binlog = 1 user = mysql
log-bin = mysql-bin
server-id = 2
binlog-do-db = test
binlog-ignore-db = mysql
replicate-do-db = test
replicate-ignore-db = mysql
log-slave-updates
slave-skip-errors = all
sync_binlog = 1

server-id必须唯一
binlog-do-db和replicate-do-db表示需要同步的数据库教程
binlog-ignore-db和replicate-ignore-db表示不需要同步的数据库
请不要加入以下命令,该命令并不会解决uid跳号的问题,恰恰相反以下两行命令才是导致uid跳号的罪魁祸首
auto_increment_increment = 2
auto_increment_offset = 1

6.重新启动MySQL

7.进入MySQL控制台

服务器A:
show master status G
flush tables with read lock;
服务器B:
show master status G
flush tables with read lock;

同时记录下两台服务器的File和Position,此处假设:

A: File: mysql-bin.000001
  Position: 001
B: File: mysql-bin.000002
  Position: 002

服务器A:
change master to
    -> master_host='192.168.1.2',
    -> master_user='sync_b',
    -> master_password='bbb',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=002;
服务器B:
change master to
    -> master_host='192.168.1.1',
    -> master_user='sync_a',
    -> master_password='aaa',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=001;

此处填写的数据完全相反

8.执行show processlist G查看是否同步成功

方法二

两台服务器
192.168.1.1(A)
192.168.1.2(B)
先保证这mysql的版本是一致的,参考http://dev.mysql.com/doc/refman/5.1/zh/replication.html#replication-implementation-details,否则复制中的异常情况很折腾人。


1.在两台mysql上创建用户,设置权限
A上添加:

#grant replication slave,replication client,reload,super on *.* to 'sync_user'@'192.168.1.2' identified by '123456' with grant option;//用于B访问

B上:

#grant replication slave,replication client,reload,super on *.* to 'sync_user'@'192.168.1.1' identified by '123456' with grant option;//用于A访问

执行  #flush privileges; 更新数据库使用户生效。

2.在/etc/my.cnf上进行相关配置

A B
server-id         = 1

master-host     =192.168.1.2

master-user     =sync_user

master-pass     =123456

master-port     =3306

master-connect-retry=60

replicate-do-db =db1

replicate-do-db =db2

replicate-ignore-db=mysql  server-id         = 2

master-host     =192.168.1.1

master-user     =sync_user

master-pass     =123456

master-port     =3306

master-connect-retry=60

replicate-do-db =db1

replicate-do-db =db2

replicate-ignore-db=mysql
 
  


注意

1.server_id必须为唯一.

2.如果想要同时同步多个库,添加多行replicate-do-db,每行指定一个数据库。不能使用replicate-do-db=db1,db2的形式

3.replicate-ignore-db:指定不进行同步的数据库。

保存后,重启mysql

#mysqladmin -u root -p shutdown

#mysqld_safe --user=mysql

3.把两台服务器上需要同步的数据库进行拷贝,保证这两台数据库初始状态一致。

4.进行双向同步


双向同步就是把单向同步反过来在做一遍,但一定要注意操作的顺序,这是成功的关键

step1.在A上mysql shell中执行

#show master status;


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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000054 |    35 |              |                  |

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

1 row in set (0.00 sec)

记录下 mysql-bin.000054,和35

step2.在B上执行:

#stop slave;//停止同步

#


CHANGE MASTER TO  MASTER_HOST='192.168.1.1', MASTER_PORT=3306, MASTER_USER='sync_user', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000054', MASTER_LOG_POS=35;

 

#start slave;//开始同步

step3,执行show slave statusG;如显示如下内容,表示同步设置成功。

Slave_IO_State: Waiting for master to send event

 

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

step4:上一步没有问题。则在B上继续执行show master status;


#show master status;


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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000005 |    6854 |              |                  |

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

1 row in set (0.00 sec)

step5:在A上执行


#stop slave;//停止同步

#


CHANGE MASTER TO  MASTER_HOST='192.168.1.2', MASTER_PORT=3306, MASTER_USER='sync_user', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=6854;

 


#start slave;//开始同步

 


step6:执行show slave statusG;如显示如下内容,表示同步设置成功。

 

Slave_IO_State: Waiting for master to send event

 

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如上述没有啥问题。到此双向同步设置完成。


方法三

一、准备服务器
由于MySQL不同版本之间的(二进制日志)binlog格式可能会不一样,因此最好的搭配组合是Master的MySQL版本和Slave的版本相同或者更低,Master的版本肯定不能高于Slave版本。
more.. | less.. | 本文中,我们假设主服务器(以下简称Master)和从服务器(以下简称Slave)的版本都是5.0.27,操作系统是RedHat Linux 9。
假设同步Master的主机名为:master(IP:192.168.1.123),Slave主机名为:slave(IP:192.168.1.124),2个MySQL的basedir目录都是/usr/local/mysql,datadir都是:/var/lib/mysql。
二、设置同步服务器
1、设置同步Master
修改 my.cnf 文件,在
# Replication Master Server (default)
# binary logging is required for replication
添加如下内容:
#log-bin=/var/log/mysql/updatelog
server-id = 1
binlog-do-db=discuz
binlog-ignore-db=mysql
重启MySQL,创建一个MySQL帐号为同步专用
# /usr/local/mysql/bin/mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO [email=]'back'@'%'[/email] IDENTIFIED BY 'back';
如果想要在Slave上有权限执行 "LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER" 语句的话,必须授予全局的 FILE 和 SELECT 权限:
mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO  [email=]'back'@'%'[/email] IDENTIFIED BY 'back';
mysql> FLUSH PRIVILEGES ;

2、设置同步Slave
修改my.cnf文件,添加
server-id = 2
master-host = 192.168.1.123
master-user = back
master-password = back
master-port = 3306
replicate-ignore-db=mysql
replicate-do-db=discuz

重启MySQL

3、启动同步
在主服务器master MySQL命令符下:
# /usr/local/mysql/bin/mysql -u root -p
mysql> show master status;
显示(当然这个是我机器的情况,你的不可能跟我一样哈,只是个例子):
+------------------+----------+-------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+-------------------+------------------+
| mysql-bin.000009 | 98 | discuz | mysql |
+------------------+----------+-------------------+------------------+

在从服务器master MySQL命令符下:
# /usr/local/mysql/bin/mysql -u root -p
mysql> slave stop;
mysql> change master to master_host='192.168.1.123', master_user='back', master_password='back', master_log_file='mysql-bin.000009', master_log_pos=98;
mysql> slave start;

用show slave statusG;看一下从服务器的同步情况
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果都是yes,那代表已经在同步
往表里面写点数据测试一下看是否同步成功,如果不成功,绝对不是你的RP问题,再检查一下操作步骤!

4、设置双向同步

修改slave服务器的my.cnf,添加
log-bin=/var/log/mysql/updatelog
binlog-do-db=discuz
binlog-ignore-db=mysql

重启MySQL,创建一个MySQL帐号为同步专用
mysql> GRANT REPLICATION SLAVE ON *.* TO [email=]'back'@'%'[/email] IDENTIFIED BY 'back';
mysql> GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO  [email=]'back'@'%'[/email] IDENTIFIED BY 'back';
mysql> FLUSH PRIVILEGES ;

修改master服务器的my.cnf,添加
master-host = 192.168.1.124
master-user = back
master-password = back
master-port = 3306
replicate-ignore-db=mysql
replicate-do-db=discuz

重启MySQL

在主服务器slave MySQL命令符下:
show master status;
+------------------+----------+-------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+-------------------+------------------+
| mysql-bin.000013 | 98 | discuz | mysql |
+------------------+----------+-------------------+------------------+

在服务器A MySQL命令符下:
mysql> slave stop;
mysql> change master to master_host='192.168.1.124', master_user='back', master_password='back', master_log_file='mysql-bin.000013', master_log_pos=98;
mysql> slave start;

其实也就是A->B单向同步的反向操作!双向同步,就这么简单啦!


提示:如果修改了主服务器的配置,记得删除从服务器上的master.info文件。否则从服务器使用的还是老配置,可能会导致错误。
-----------------------------------------------------------------------------------
注意:关于要复制多个数据库时,binlog-do-db和replicate-do-db选项的设置,网上很多人说是用半角逗号分隔,经过测试,这样的说法是错误的,MySQL官方文档也明确指出,如果要备份多个数据库,只要重复设置相应选项就可以了。
比如:
binlog-do-db=a
binlog-do-db=b
replicate-do-db=a
replicate-do-db=b

bitsCN.com
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template