Tim Rosine wrote:
> 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.
Tim,
It's best to create teh additional grant, and have that replicated over. Excluding the mysql database is always a possibility, but do you want to always remember to do double work on both the servers?
Regards,
Partha
Partha Dutta
Senior Consultant, MySQL Inc.
http://www.mysql.com