Mysql主主同步配置 一、环境 172.16.0.153 MysqlA5.0 172.16.0.154 MysqlB5.0 二,安装配置 1.安装mysql省略,MysqlA和MysqlB版本保持一致就可以了! 2.配置mysql 1)在两台机器上给对方授权 MysqlA GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO tongbu@172.16.0.154 IDENTIFIED BY 'tongbu_123'; flush privileges; MysqlB GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO tongbu@172.16.0.153 IDENTIFIED BY 'tongbu_123'; flush privileges; 2)配置主配置文件 在MysqlA上 [mysqld] server-id = 10 log-bin = mysql-bin log-slave-updates auto-increment-increment = 2 auto-increment-offset = 1 #要同步的库名 replicate-do-db = test 在MysqlB上 [mysqld] server-id = 20 log-bin = mysql-bin log-slave-updates auto-increment-increment = 2 auto-increment-offset = 2 #要同步的库名 replicate-do-db = test 注:二库都只有server-id不同和auto-increment-offset不同: auto-increment-offset是用来设定数据库中自动增长的起点的,回为这两能服务器都设定了一次自动增长值2,所以它们的起点必须得不同,这样才能避免两台服务器数据同步时出现主键冲突 replicate-do-db 指定同步的数据库,我们只在两台服务器间同步test数据库 另:auto-increment-increment的值应设为整个结构中服务器的总数,本案例用到两台服务器,所以值设为2 配置完成后,重启两台数据库 service mysqld restart 3)同步两台数据库 首先先在两台数据库上创建要同步的数据库 create database test; 分别在两台数据库中,执行下命令,查看potion,以及binlog。 MysqlA上面执行 mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 491 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MysqlB上面执行 mysql> show master status;
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 335 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
##在 MysqlA 上 stop slave; CHANGE MASTER TO master_host='172.16.0.154', master_user='tongbu', master_password='tongbu_123', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=199850134; start slave; ##在 MysqlB 上 stop slave; CHANGE MASTER TO master_host='172.16.0.153', master_user='tongbu', master_password='tongbu_123', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=8372; start slave; ##分别查看两台数据库的slave状态。 mysql> show slave status\G; Slave_IO_Running: Yes Slave_SQL_Running: Yes ##都为yes就ok了 4)验证mysql主主 ###好了现在我们来验证吧 在MysqlA的mysqldb数据库中创建表 create table t11_replicas (id int not null auto_increment primary key, str varchar(255) not null) engine myisam; 在MysqlB的mysqldb数据库中能够查看到该表 mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t11_replicas | +----------------+ 1 row in set (0.00 sec) 在MysqlB中向表test插入数据 insert into t11_replicas(str) values ('This is a master to master test table'); 在MysqlA中查看表test select * from t11_replicas; mysql> select * from t11_replicas; +----+---------------------------------------+ | id | str | +----+---------------------------------------+ | 1 | This is a master to master test table | +----+---------------------------------------+ 1 row in set (0.00 sec)
|