关闭

MySQL 互备份master-master

388人阅读 评论(0) 收藏 举报
机器信息

MySQL 5.1.17
A. 192.168.0.2
B. 192.168.0.9

创建用户并授权

A.

01
02
03
04
05
06
07
08
09
10
11
mysql> GRANT REPLICATION SLAVE, FILE on *.* TO 'slave_2' @ '192.168.0.9' IDENTIFIED BY '123456' ;
Query OK, 0 rows affected (0.00 sec)
 
mysql> FLUSH PRIVILEGES ;
Query OK, 0 rows affected (0.00 sec)
B.
mysql> GRANT REPLICATION SLAVE, FILE on *.* TO 'slave_9' @ '192.168.0.2' IDENTIFIED BY '123456' ;
Query OK, 0 rows affected (0.00 sec)
 
mysql> FLUSH PRIVILEGES ;
Query OK, 0 rows affected (0.00 sec)
修改配置文件

A.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
[mysqld]
server- id                  = 1
user                      = mysql
log_bin                   = mysql-bin
binlog_do_db              = example
binlog_ignore_db          = mysql
binlog_ignore_db          = test
replicate_do_db           = example
replicate_ignore_db       = mysql
replicate_ignore_db       = test
log_slave_updates
slave_skip_errors         = all
sync_binlog               = 1
auto_increment_increment  = 2
auto_increment_offset     = 1
master_host               = 192.168.0.9
master_port               = 3306
master_user               = slave_9
master_password           = 123456
master_connect_retry      = 60
report_host               = 192.168.0.5

B.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
[mysqld]
server- id                  = 2
user                      = mysql
log_bin                   = mysql-bin
binlog_do_db              = example
binlog_ignore_db          = mysql
binlog_ignore_db          = test
replicate_do_db           = example
replicate_ignore_db       = mysql
replicate_ignore_db       = test
log_slave_updates
slave_skip_errors         = all
sync_binlog               = 1
auto_increment_increment  = 2
auto_increment_offset     = 1
master_host               = 192.168.0.2
master_port               = 3306
master_user               = slave_2
master_password           = 123456
master_connect_retry      = 60
report_host               = 192.168.0.5
启动MySQL服务,在A和B上执行如下相同的步骤
1
[root@localhost ~] # /usr/local/mysql/bin/mysqld_safe --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/log/localhost.localdomain.pid --skip-external-locking &
查看备份是否成功
1
mysql> SHOW SLAVE STATUS /G

当看到Slave_IO_Running,Slave_SQL_Running 都是Yes,就说明备份成功 :

1
2
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如下是手动指定Master
A.

01
02
03
04
05
06
07
08
09
10
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW MASTER STATUS /G
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 520
     Binlog_Do_DB: example
Binlog_Ignore_DB: mysql,test
1 row in set (0.00 sec)

B.

01
02
03
04
05
06
07
08
09
10
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW MASTER STATUS /G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 519
     Binlog_Do_DB: example
Binlog_Ignore_DB: mysql,test
1 row in set (0.00 sec)

A.

1
2
3
4
5
6
7
8
9
mysql> CHANGE MASTER TO
     -> master_host= '192.168.0.9' ,
     -> master_user= 'slave_9' ,
     -> master_password= '123456' ,
     -> master_log_file= 'mysql-bin.000003' ,
     -> master_log_pos=519;
Query OK, 0 rows affected (0.01 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

B.

1
2
3
4
5
6
7
8
9
mysql> CHANGE MASTER TO
     -> master_host= '192.168.0.2' ,
     -> master_user= 'slave_2' ,
     -> master_password= '123456' ,
     -> master_log_file= 'mysql-bin.000004' ,
     -> master_log_pos=520;
Query OK, 0 rows affected (0.01 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

查看各自机器上的IO进程和 SLAVE进程是否都开启。

1
mysql> SHOW SLAVE STATUS /G

释放掉各自的锁

1
mysql> UNLOCK TABLES;
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:497268次
    • 积分:7219
    • 等级:
    • 排名:第3062名
    • 原创:258篇
    • 转载:85篇
    • 译文:0篇
    • 评论:33条
    最新评论
    PHP图表连接
    翻译网站