1、环境描述。主机:192.168.1.90(A)主机:192.168.1.130(B)MYSQL 版本为5.0.45mysql> select version();+------------+| version() |+------------+| 5.0.45-log |+------------+1 row in set (0.00 sec)
2、2、授权用户 主(A)
mysql> grant replication slave,file on *.* toQuery OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)
3、授权用户 从(B)
mysql> grant replication slave,file on *.* toQuery OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)
4、停止你主和从的MYSQL服务器
[root@backup1 ~]# service mysqld stopStopping MySQL: [ OK ]
5、配置文件。在两个机器上的my.cnf里面都开启二进制日志.主(A)
[root@backup1 ~]# vim /etc/my.cnfuser = mysqllog-bin=mysql-binserver-id= 1 //此ID必须唯一binlog-do-db=test //你要更新的数据库binlog-ignore-db=mysqlreplicate-do-db=test //你要更新的数据库replicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allsync_binlog=1auto_increment_increment=2auto_increment_offset=1从(B)
[root@backup2 ~]# vim /etc/my.cnfuser = mysqllog-bin=mysql-binserver-id= 2 //此ID必须唯一binlog-do-db=test //你要更新的数据库binlog-ignore-db=mysqlreplicate-do-db=test //你要更新的数据库replicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allsync_binlog=1auto_increment_increment=2auto_increment_offset=
26、重新启动MYSQL服务器。在A和B上执行相同的步骤
[root@backup1 ~]# service mysqld restartStopping MySQL: [ OK ]7、进入MYSQL主(A)
mysql> flush tables with read lock\GQuery OK, 0 rows affected (0.00 sec)mysql> show master status\G*************************** 1. row ***************************File: mysql-bin.000007Position: 528Binlog_Do_DB: testBinlog_Ignore_DB: mysql1 row in set (0.00 sec)从(B)
mysql> flush tables with read lock;Query OK, 0 rows affected (0.00 sec)mysql> show master status\G*************************** 1. row ***************************File: mysql-bin.000004Position: 595Binlog_Do_DB: testBinlog_Ignore_DB: mysql1 row in set (0.00 sec)
8、然后备份自己的数据,保持两个机器的数据一致。这里我就不写了9、在各自机器上执行CHANGE MASTER TO命令 主(A)
mysql> change master to-> master_host='192.168.1.130', //这里填的都是对方的-> master_user='backup1',-> master_password='1234',-> master_log_file='mysql-bin.000004',-> master_log_pos=595;Query OK, 0 rows affected (0.01 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)从(B)
mysql> change master to-> master_host='192.168.1.90',-> master_user='backup2',-> master_password='1234',-> master_log_file='mysql-bin.000007',-> master_log_pos=528;Query OK, 0 rows affected (0.01 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)
10、查看各自机器上的IO进程和SLAVE进程是否都开启。主(A)
mysql> show processlist\G*************************** 1. row ***************************Id: 3User: system userHost:db: NULLCommand: ConnectTime: 200State: Waiting for master to send eventInfo: NULL*************************** 2. row ***************************Id: 4User: system userHost:db: NULLCommand: ConnectTime: 80State: Has read all relay log; waiting for the slave I/O thread to update itInfo: NULL*************************** 3. row ***************************Id: 10User: backupHost: 192.168.1.130:44915db: NULLCommand: Binlog DumpTime: 17State: Has sent all binlog to slave; waiting for binlog to be updatedInfo: NULL*************************** 4. row ***************************Id: 11User: rootHost: localhostdb: NULLCommand: QueryTime: 0State: NULLInfo: show processlist4 rows in set (0.00 sec)从(B)
mysql> show processlist\G*************************** 1. row ***************************Id: 4User: system userHost:db: NULLCommand: ConnectTime: 199State: Waiting for master to send eventInfo: NULL*************************** 2. row ***************************Id: 5User: system userHost:db: NULLCommand: ConnectTime: 12State: Has read all relay log; waiting for the slave I/O thread to update itInfo: NULL*************************** 3. row ***************************Id: 8User: backupHost: 192.168.1.90:59666db: NULLCommand: Binlog DumpTime: 77State: Has sent all binlog to slave; waiting for binlog to be updatedInfo: NULL*************************** 4. row ***************************Id: 10User: rootHost: localhostdb: NULLCommand: QueryTime: 0State: NULLInfo: show processlist4 rows in set (0.04 sec)
11.释放掉各自的锁,然后进行插数据测试
mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)
插入之前两个机器表的对比
mysql> use testDatabase changedmysql> show tables;Empty set (0.00 sec)
显然是什么都没有,那我们来插入一张表,再插2个值 主(A)
mysql> CREATE TABLE userinfo (-> userid int(11) NOT NULL auto_increment,-> username varchar(200) NOT NULL,-> friend int(11) NOT NULL default '0',-> PRIMARY KEY (userid)-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.04 sec)mysql> show tables;+----------------+| Tables_in_test |+----------------+| userinfo |+----------------+1 row in set (0.01 sec)mysql> insert into userinfo values(1,'1',1);Query OK, 1 row affected (0.01 sec)mysql> insert into userinfo values(2,'2',2);Query OK, 1 row affected (0.03 sec)mysql> select * from userinfo;+--------+----------+--------+| userid | username | friend |+--------+----------+--------+| 1 | 1 | 1 || 2 | 2 | 2 |+--------+----------+--------+2 rows in set (0.00 sec)
现在来看B机器:从(B)显然是同步过来了
mysql> show tables;+----------------+| Tables_in_test |+----------------+| userinfo |+----------------+1 row in set (0.00 sec)mysql> select * from userinfo;+--------+----------+--------+| userid | username | friend |+--------+----------+--------+| 1 | 1 | 1 || 2 | 2 | 2 |+--------+----------+--------+2 rows in set (0.00 sec)
现在反过来从B机器上插入数据 从(B)
mysql> insert into userinfo values(3,'3',3);Query OK, 1 row affected (0.01 sec)mysql> insert into userinfo values(4,'4',4);Query OK, 1 row affected (0.01 sec)mysql> select * from userinfo;+--------+----------+--------+| userid | username | friend |+--------+----------+--------+| 1 | 1 | 1 || 2 | 2 | 2 || 3 | 3 | 3 || 4 | 4 | 4 |+--------+----------+--------+4 rows in set (0.00 sec)
我们来看A
mysql> select * from userinfo;+--------+----------+--------+| userid | username | friend |+--------+----------+--------+| 1 | 1 | 1 || 2 | 2 | 2 || 3 | 3 | 3 || 4 | 4 | 4 |+--------+----------+--------+4 rows in set (0.00 sec)