AB复制

最新推荐文章于 2024-07-09 20:02:32 发布

weixin_34319374

最新推荐文章于 2024-07-09 20:02:32 发布

阅读量117

点赞数

文章标签：数据库开发工具 shell

原文链接：http://blog.51cto.com/zhang59585/1077382

版权

1、AB复制

需要三台主机，IP分别为192.168.31.1、192.168.31.2和192.168.31.3

master；

vim /etc/my.cnf

server_id = 1
log-bin=binlog
log-bin-index=binlog.index
重启mysql

slave1:

vim /etc/my.cnf

server_id = 2
relay_log = /var/lib/mysql/mysql-relay-bin
relay_log_index=/var/lib/mysql/mysql-relay-bin.index
重启

master；

进入到mysql中，创建用户：

GRANT replication slave ON *.* TO ' zhang '@'%' identified by '123';
GRANT replication slave ON *.* TO ' nan '@'%' identified by '123';

flush privileges;

slave1:

将/etc/lib/mysql 下面的所有东西删除

重启mysql

master；

备份数据到/tmp下面

mysqldump -A -x > /tmp/back.sql

再推送到B1主机上

scp /tmp/back.sql root@192.168.18.2:/tmp/

slave1:

导回数据

mysql < /tmp/full.sql

master；

mysql> flush tables with read lock;

将表给锁上，自己做实验时候可以省略这一步，生产环境下必须执行
mysql> show master status;
查看主状态

mysql> show master status;

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

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

| binlog.000005 | 490 | | |

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

1 row in set (0.00 sec)

等slave1同步完成之后在将表解锁

mysql>unlock tables;

slave1:

mysql> change master to master_host='192.168.31.1', master_port=3306, master_user='zhang', master_password='123', master_log_file='binlog.000005',master_log_pos=490;

mysql> start slave;

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.31.1

Master_User: zhang

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000005

Read_Master_Log_Pos: 490

Relay_Log_File: mysql-relay-bin.000004

Relay_Log_Pos: 316

Relay_Master_Log_File: binlog.000005

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

slave2；

vim /etc/my.cnf

server_id = 3
relay_log = /var/lib/mysql/mysql-relay-bin
relay_log_index=/var/lib/mysql/mysql-relay-bin.index

重启mysql

进入到mysql

mysql> change master to master_host='192.168.31.1', master_port=3306, master_user='nan', master_password='123', master_log_file='binlog.000005',master_log_pos=490;
mysql> start slave;

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.31.1

Master_User: nan

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000005

Read_Master_Log_Pos: 490

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 232

Relay_Master_Log_File: binlog.000005

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

配置完成

ABC复制

master1（A）

vim /etc/my.cnf
server-id = 1
log-bin=binlog
log-bin-index=binlog.index
log_slave_updates = 1
auto_increment_increment=2
auto_increment_offset=1

mater2(B)

server-id = 2

log-bin=binlog

log-bin-index=binlog.index

log_slave_updates = 1

auto_increment_increment=2

auto_increment_offset=2

进入mysql

GRANT replication slave ON *.* TO 'zn'@'' identified by '123';

flush privileges;

slave （C）

vim /etc/my.cnf

user=mysql

server_id = 3

relay_log = /var/lib/mysql/mysql-relay-bin

relay_log_index=/var/lib/mysql/mysql-relay-bin.index

清空/var/lib/mysql/下的所有目录

重启mysql服务

mater2(B)

mysql> show master status;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 4

Current database: *** NONE ***

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

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

| binlog.000001 | 98 | | |

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

1 row in set (0.01 sec)

slave （C）

进入mysqsl

change master to master_host='192.168.31.2', master_port=3306, master_user='zn', master_password='123', master_log_file='binlog.000001',master_log_pos=98;

flush privileges;

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.31.2

Master_User: zn

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000001

Read_Master_Log_Pos: 98

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 313