一、 安装环境
RedHat AS4.0
MySQL 5.1.42 :MySQL-server-community-5.1.42-0.rhel4.i386.rpm
MySQL-client-community-5.1.42-0.rhel4.i386.rpm
Rl01(作为master server):16.173.241.51
R03(作为slave server):16.173.241.53
二、安装
由于是rpm包,则都用rpm–vihfullfilename命令来安装。
三、配置
在两台server上通过更改文件/etc/my.cnf来进行配置,若没有此文件,可从/usr/share/mysql/中根据主机配置的不同选择相应文件拷贝过去,这里选择拷贝my-huge.cnf并更名为my.cnf。
Master server配置:
在my.cnf文件中修改:
log-bin=mysql-bin
server-id= 1
binlog-do-db= rep
注:
1.log-bin表示打开binlog,打开该选项才可以通过I/O写到Slave的relay-log,也是可以进行replication的前提
2.一般master server的server-id设为1
3.binlog-do-db=rep表示需要备份的数据库是rep这个数据库
Slave server配置:
在my.cnf文件中修改:
server-id= 2
master-host=16.173.241.51
master-user=rep
master-password =rep
master-port=3306
replicate-do-db =rep
log-bin=mysql-bin
注:
1.master-host表示本机做slave时master server的地址
2.master-user表示master上开放的一个有权限的用户,使其可以从slave连接到master并进行复制
3.master-password表示授权用户的密码
4. replicate-do-db表示同步master server的rep数据库
5. log-bin打开logbin选项以能写到slave的I/O线程
配置完后重启两台机器的mysql服务,使配置生效
四、 在master server上分配权限
登陆master server的mysql,执行如下命令:
grant all privileges on rep.* to rep@16.173.241.53 identified by ‘rep’;
这条命令表示赋予主机:16.173.241.53上的账号rep/rep对本机上的数据库rep的所有操作权限。这里为了方便,赋予了所有权限,也可将all privileges替换为replication slave,file 。
然后执行命令flush privileges来更新权限
注:
1.这条命令里的账号rep/rep也设置在slave server的my.cnf文件中。
2.配置完后如果在slave server上碰到访问权限的问题,可重复分配权限的过程来设置其他的账号,并在slave server的/var/lib/mysql目录中,删除master.info文件和relay-log.info,relay-log.index等相关relay文件,并重启slave server的mysql服务,一般问题都能解决。
五、验证
在master server上创建数据库rep,并创建表test
mysql> create database rep;
Query OK, 1 row affected (0.00 sec)
mysql> use rep
Database changed
mysql> create table test (a int(10),b int(10));
Query OK, 0 rows affected (0.01 sec)
返回slave server查看:
mysql> show databases;
+--------------------+
| Database|
+--------------------+
| information_schema |
| mysql|
| rep|
| test|
+--------------------+
4 rows in set (0.00 sec)
mysql> use rep
Database changed
mysql> show tables;
+---------------+
| Tables_in_rep |
+---------------+
| test|
+---------------+
1 row in set (0.00 sec)
这里就说明两台server同步成功。
六、常用查看命令
Master server上:
show master status
mysql> show master status;
+------------------+----------+--------------+------------------+
| File| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |288 | rep,rep||
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
这里的position不能为0,如果为0则表示有问题,可检查/etc/my.cnf中的配置,server-id是否正确和是否打开了log-bin
show processlist
mysql> show processlist;
+----+------+---------------------------+------+-------------+------+----------------------------------------------------------------+------------------+
| Id | User | Host| db| Command| Time | State| Info|
+----+------+---------------------------+------+-------------+------+----------------------------------------------------------------+------------------+
| 27 | rep| tssavl03.chn.hp.com:55699 | NULL | Binlog Dump |935 | Has sent all binlog to slave; waiting for binlog to be updated | NULL|
| 28 | root | localhost| NULL | Query|0 | NULL| show processlist |
+----+------+---------------------------+------+-------------+------+----------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
若replication成功了,则这里会有如上两个进程
Slave server上:
show processlist
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User| Host| db| Command | Time | State| Info|
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
|1 | system user || NULL | Connect | 5167 | Waiting for master to send event| NULL|
|2 | system user || NULL | Connect | 2634 | Has read all relay log; waiting for the slave I/O thread to update it | NULL|
|4 | root| localhost | NULL | Query|0 | NULL| show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
Replication成功后slave server上也应该有如上三个进程。
查看/var/lib/mysql下的错误日志文件,这里在slave server上的日志文件是R03.err,一般的错误都能在这里查出。