|
1、环境描述。
主机：192.168.1.90 （A）
主机：192.168.1.130（B）
MYSQL 版本为5.0.45
mysql> select version();
+------------+
| version() |
+------------+
| 5.0.45-log |
+------------+
1 row in set (0.00 sec)
2、2、授权用户 主(A)
mysql> grant replication slave,file on *.* to identified by '1234';
Query 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 *.* to identified by '1234';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
4、停止你主和从的MYSQL 服务器
[root@backup1 ~]# service mysqld stop
Stopping MySQL: [ OK ]
5、配置文件。
在两个机器上的my.cnf里面都开启二进制日志. 主(A)
[root@backup1 ~]# vim /etc/my.cnf
user = mysql
log-bin=mysql-bin
server-id= 1 //此ID必须唯一
binlog-do-db=test //你要更新的数据库
binlog-ignore-db=mysql
replicate-do-db=test //你要更新的数据库
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1从(B)
[root@backup2 ~]# vim /etc/my.cnf
user = mysql
log-bin=mysql-bin
server-id= 2 //此ID必须唯一
binlog-do-db=test //你要更新的数据库
binlog-ignore-db=mysql
replicate-do-db=test //你要更新的数据库
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=
26、重新启动MYSQL服务器。在A和B上执行相同的步骤
[root@backup1 ~]# service mysqld restart
Stopping MySQL: [ OK ]7、进入MYSQL 主(A)
mysql> flush tables with read lock\G
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000007
Position: 528
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 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.000004
Position: 595
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 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: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 200
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 80
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 10
User: backup
Host: 192.168.1.130:44915
db: NULL
Command: Binlog Dump
Time: 17
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 4. row ***************************
Id: 11
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
4 rows in set (0.00 sec)从(B)
mysql> show processlist\G
*************************** 1. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 199
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 12
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 8
User: backup
Host: 192.168.1.90:59666
db: NULL
Command: Binlog Dump
Time: 77
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 4. row ***************************
Id: 10
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
4 rows in set (0.04 sec)
11.释放掉各自的锁，然后进行插数据测试
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
插入之前两个机器表的对比
mysql> use test
Database changed
mysql> 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)
| 