MySQL Forums
Forum List  »  Replication

replication and the mysql database
Posted by: Tim Rosine
Date: August 30, 2005 03:17PM

Using mysql 4.1.10a, I'm setting up replication in dual master/slave setup:
s1 <-> s2

The main stuff is working just fine, but I have one concern - I am replicating ALL of mysql, including the mysql database (GRANT REPLICATION SLAVE ON *.*). It seems like this causes a problem with authentication when on one of the local servers.

Example:
mysql> select @@server_id,user,host from mysql.user where user='root';
+-------------+------+------------+
| @@server_id | user | host |
+-------------+------+------------+
| 2 | root | s1 |
| 2 | root | localhost |
+-------------+------+------------+

s2 /root# mysql -h s2 -u root -p mysql
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'s2' (using password: YES)

I'm sure I could get around the issue by creating an additional grant for 'root'@'s2' (or 'myapp'@'%' for applications), but I was wondering if it would be preferrable to exclude mysql.* from replication instead. I like the ability to create users once and have them show up on all servers.

I haven't found much information about recommendations online - probably because it's too hard to search for "mysql replication" or any of the other variants and get what I want.

Options: ReplyQuote


Subject
Views
Written By
Posted
replication and the mysql database
2681
August 30, 2005 03:17PM
2447
September 12, 2005 02:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.