背景:
master:192.168.100.231 mysql5.0.19 linux
slave: 192.168.100.234 mysql5.0.18 linux
step1) 配置master
在/etc/init.d mysqld段加入
#(要同步的数据库)
binlog_do_db = test
并确保
server-id=1
log-bin=mysql-bin
step2) 授权slave
GRANT REPLICATION SLAVE ON *.* TO test@192.168.100.234 IDENTIFIED BY 123;
重启master
step3)配置slave
vi /etc/my.cnf
设置下面4行
server-id = 2
master-host = 192.168.100.231
master-user = test
master-password = 123
重启发现slave的I/O线程工作正常,而SQL线程不工作
报了很怪的错误
070307 16:30:27 [ERROR] Slave: Error Table passport.loginuser doesnt exist on query. Default database: passport. Query: delete from loginuser where tokenRefreshTime<=2006-07-03 07:00:00, Error_code: 1146
070307 16:30:27 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log mysql-bin.000001 position 98
因该是relay log中的数据有问题,于是在
将master机器上的数据拷到244,发现还是有问题。
于是在/etc/my.cnf中加入
replicate-do-db=test
重启slave,
show slave status发现两个线程工作正常
5)测试
在master的test数据的user表中添加了一条记录
然后在slave上通过show processlist看slave工作情况一切正常,
再查数据库数据似乎没有更新,查看slave错误日志
070307 16:45:16 [ERROR] Slave: Error Cant create database test; database exists on query. Default database: test. Query: create database test, Error_code: 1007
070307 16:45:16 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log mysql-bin.000040 position 70671
原来都是我当初拷贝数据惹的祸,因为主机日志在我建数据库的时候就开始了,所以我拷贝数据多此一举,于是
我删除拷贝的数据库,一切ok