大家好:
我使用root用户搭建的MySQL主从复制(centos),并且自己使用root用户测试时完全没有问题的,为什么开发人员那边使用base_crm用户连接该库(这个base_crm用户只有对要实现主从同步的数据库有权限),一旦有了更新,同步就会错误,出现1146报错,表不存在呢?我怀疑是不是因为 不同用户的原因呀?求教
Replicate_Ignore_Table: base_crm.tb_sys_operlog
Replicate_Wild_Do_Table: base_crm.%,test.%
Replicate_Wild_Ignore_Table:
Last_Errno: 1146
Last_Error: Error 'Table 'base_crm.TB_SYS_USER' doesn't exist' on query. Default database: 'base_crm'. Query: 'UPDATE TB_SYS_USER SysUser
SET
SysUser.lastLoginTime = '2013-09-13 10:37:48'
WHERE SysUser.id = 75'
Skip_Counter: 0
Exec_Master_Log_Pos: 107043
Relay_Log_Space: 17935
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1146
Last_SQL_Error: Error 'Table 'base_crm.TB_SYS_USER' doesn't exist' on query. Default database: 'base_crm'. Query: 'UPDATE TB_SYS_USER SysUser
SET
SysUser.lastLoginTime = '2013-09-13 10:37:48'
WHERE SysUser.id = 75'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 34
错误日志报错:
2013-09-13 10:37:50 24245 [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.000006' position 107043
2013-09-13 10:59:37 24245 [ERROR]Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
我排除了夸库操作的原因,感觉应该是root和base_crm这两个用户权限的问题。请问,我如何设置base_crm的用户权限,才可以正常同步数据呢?
主服务器的配置参数如下:
server_id = 34
read-only = 0
binlog-do-db = base_crm
从服务器的配置参数如下:
server_id = 32
replicate-wild-do-table = base_crm.%
replicate-ignore-table = base_crm.tb_sys_operlog
使用change master to语句,开始slave:
mysql>changemaster to master_host='172.16.100.133',master_user='slaveuser',master_password='slaveuser',master_log_file='mysql-bin.000012',master_log_pos=106;