A-A+

Mysql主主(双向)同步配置方案

2012年04月28日 MYSQL, 数据库 评论 4 条 阅读 4,028 次

A服务器:192.168.1.1
B服务器:192.168.1.2

A服务器上的设置:
编辑my.cnf,添加或修改如下内容
#双向同步
server-id=1
log-bin=mysql-bin
relay-log = relay-bin
replicate_do_db=testtongbu
auto-increment-increment=2 #每次增长2
auto-increment-offset=1 #设置自动增长的字段的偏移量,即初始值为2

重启mysql
service mysqld restart
进入Mysql命令行模式,执行下面的命令
GRANT RELOAD,SUPER,REPLICATION SLAVE,REPLICATION CLIENT on *.* to backup@'192.168.1.2' identified by '123456';
flush privileges;
show master status;

#记下File和Position的值 log1

B服务器上的设置:
编辑my.cnf,添加或修改如下内容
#双向同步
server-id=2
log-bin=mysql-bin
relay-log = relay-bin
replicate_do_db=testtongbu
auto-increment-increment=2 #每次增长2
auto-increment-offset=2 #设置自动增长的字段的偏移量,即初始值为2

重启mysql
service mysqld restart
进入Mysql命令行模式,执行下面的命令
GRANT RELOAD,SUPER,REPLICATION SLAVE,REPLICATION CLIENT on *.* to backup@'192.168.1.1' identified by '123456';
flush privileges;
show master status;

#记下File和Position的值 log2

#执行(问号分别替换为刚才log1里面记下的两个值)
CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='backup', master_password='123456', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=106;
start slave;

#回到A服务器,执行(问号分别替换为刚才log2里面记下的两个值)
CHANGE MASTER TO MASTER_HOST='192.168.1.2', MASTER_USER='backup', master_password='123456', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=106;
start slave;

正在运行的数据库导出数据时,先锁定库
flush tables with read lock;
导出后解锁
unlock tables;

遇到的错误:
ERROR 1219 (HY000): Error running query on master: Access denied; you need the RELOAD privilege for this operation
在另外一台服务器上执行
mysql> grant RELOAD on *.* to repl@’%’ identified by ‘your_password’;

Error reading packet from server: Access denied; you need the REPLICATION SLAVE privilege for this operation (server_errno=1227)
在另外一台服务器上执行
mysql> grant REPLICATION SLAVE on *.* to repl@’%’ identified by ‘your_password’;

标签:

4 条留言  访客:0 条  博主:0 条


    Warning: A non-numeric value encountered in /data/wwwroot/lxy.me/wp-content/themes/Ality/inc/functions/comment-template.php on line 16
  1. avatar 你好ZZB

    Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /data/wwwroot/lxy.me/wp-content/themes/Ality/functions.php on line 249
    • avatar 恋香缘

      Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /data/wwwroot/lxy.me/wp-content/themes/Ality/functions.php on line 249
      • avatar 你好ZZB

        Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /data/wwwroot/lxy.me/wp-content/themes/Ality/functions.php on line 249
        • avatar 恋香缘

          Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /data/wwwroot/lxy.me/wp-content/themes/Ality/functions.php on line 249

给我留言