[SOLVED] MySQL Master Master Replication

Installing, Configuring, Troubleshooting server daemons such as Web and Mail
Post Reply
kbensch
Posts: 50
Joined: 2005/04/22 17:27:42
Location: Maidenhead, UK
Contact:

[SOLVED] MySQL Master Master Replication

Post by kbensch » 2011/12/29 16:26:28

Hi

I managed to setup master slave replicatrion, but now want to setup master-master.

The log files are all clean except for one error on master 1. I get: 111229 16:13:38 [ERROR] Slave I/O thread: error connecting to master 'test@10.10.10.62:3306': Error: 'Access denied for user 'test'@'10.10.10.60' (using password: YES)' errno: 1045 retry-time: 60 retries: 86400

I have nowhere used a test user.

Can naybody help me find where this comes from and how to fix it.

From the show master status c0mmand it appears to be running
show slave on node2 appears to be running

it is just node1 slave that gives me the error.

Thanks for your help and let me know if you need any other info..

User avatar
TrevorH
Forum Moderator
Posts: 26933
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: MySQL Master Master Replication

Post by TrevorH » 2011/12/29 19:48:26

On 10.10.10.60 you have told it to use master_user=test by issuing a `CHANGE MASTER to MASTER_USER=test` and it is trying to connect to the master server on 10.10.10.62. You either need to issue another CHANGE MASTER on 10.10.10.60 specifying the correct username or you need to grant replication_slave privileges to the 'test'@'10.10.10.60' user on the other master.

If you are using master-master replication you will need to add parameters to the my.cnf files on all servers to tell it how to handle auto_increment keys. These are

[quote]
auto_increment_increment should be equal to the number of masters you have, N;
auto_increment_offset should be different, from 1 to N, on each master.
[/quote]

kbensch
Posts: 50
Joined: 2005/04/22 17:27:42
Location: Maidenhead, UK
Contact:

Re: MySQL Master Master Replication

Post by kbensch » 2011/12/29 20:11:30

Hi Trevor
Thank you for the reply, but here are the configs I used:

[b]on 10.10.10.60:[/b]
/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
symbolic-links=0

log-bin=mysql-bin
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
expire_logs_days=30
max_binlog_size=500M

binlog-do-db=db1
binlog-do-db=db2
binlog-do-db=db3
binlog-do-db=db4
binlog-do-db=db5
binlog-do-db=db6

replicate-do-db=db1
replicate-do-db=db2
replicate-do-db=db3
replicate-do-db=db4
replicate-do-db=db5
replicate-do-db=db6

server-id=1
replicate-same-server-id=0
auto-increment-increment=2
auto-increment-offset=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

grant replication slave on *.* to 'replication'@'10.10.10.62' identified by 'ThisisthePasswordforReplication';

CHANGE MASTER TO MASTER_HOST='10.10.10.62';
CHANGE MASTER TO MASTER_USER='replication';
CHANGE MASTER TO MASTER_PASSWORD='ThisisthePasswordforReplication';
CHANGE MASTER TO MASTER_PORT = 3306;

[b]on 10.10.10.62:[/b]
/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
symbolic-links=0

log-bin=mysql-bin
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
expire_logs_days=30
max_binlog_size=500M

binlog-do-db=db1
binlog-do-db=db2
binlog-do-db=db3
binlog-do-db=db4
binlog-do-db=db5
binlog-do-db=db6

replicate-do-db=db1
replicate-do-db=db2
replicate-do-db=db3
replicate-do-db=db4
replicate-do-db=db5
replicate-do-db=db6

server-id=2
replicate-same-server-id=0
auto-increment-increment=2
auto-increment-offset=2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

grant replication slave on *.* to 'replication'@'10.10.10.62' identified by 'ThisisthePasswordforReplication';

CHANGE MASTER TO MASTER_HOST = '10.10.10.60';
CHANGE MASTER TO MASTER_USER = 'replication';
CHANGE MASTER TO MASTER_PASSWORD='ThisisthePasswordforReplication';
CHANGE MASTER TO MASTER_PORT = 3306;



Can you see anything from here?

kbensch
Posts: 50
Joined: 2005/04/22 17:27:42
Location: Maidenhead, UK
Contact:

Re: MySQL Master Master Replication

Post by kbensch » 2011/12/29 20:40:47

Not too worry i sorted it. Well when I say that I just reran the change to master commands on both and now it seems to be ok.

damn computers accepting finger trouble somewhere

pschaff
Retired Moderator
Posts: 18276
Joined: 2006/12/13 20:15:34
Location: Tidewater, Virginia, North America
Contact:

Re: [SOLVED] MySQL Master Master Replication

Post by pschaff » 2012/01/01 22:46:32

Thanks for reporting back. Marking this thread [SOLVED] for posterity.

Post Reply

Return to “CentOS 5 - Server Support”